Функции для работы со встроенными словарями

Документация по функциям для работы со встроенными словарями

Чтобы приведенные ниже функции работали, в конфигурации сервера должны быть указаны пути и адреса для получения всех встроенных словарей. Словари загружаются при первом вызове любой из этих функций. Если словари не удается загрузить, будет сгенерировано исключение.

Поэтому примеры, приведенные в этом разделе, по умолчанию сгенерируют исключение в ClickHouse Fiddle, а также в развертываниях quick release и production, если их предварительно не настроить.

Сведения о создании словарей см. в разделе “Словари”.

​

Несколько геобаз

ClickHouse поддерживает одновременную работу с несколькими альтернативными геобазами (региональными иерархиями), чтобы учитывать различные точки зрения на принадлежность тех или иных регионов к странам.

В конфигурации ‘clickhouse-server’ указывается файл с региональной иерархией:

<path_to_regions_hierarchy_file>/opt/geo/regions_hierarchy.txt</path_to_regions_hierarchy_file>

Помимо этого файла, также выполняется поиск расположенных рядом файлов, в имени которых есть символ _ и любой суффикс, добавленный к имени (перед расширением файла). Например, если файл /opt/geo/regions_hierarchy_ua.txt существует, он тоже будет найден. Здесь ua называется ключом словаря. Для словаря без суффикса ключом является пустая строка.

Все словари перезагружаются во время работы (через определённое число секунд, как задано параметром конфигурации builtin_dictionaries_reload_interval, или раз в час по умолчанию). Однако список доступных словарей определяется только один раз — при запуске сервера.

Все функции для работы с регионами имеют необязательный аргумент в конце — ключ словаря. Он называется геобазой.

Пример:

regionToCountry(RegionID) – Использует словарь по умолчанию: /opt/geo/regions_hierarchy.txt
regionToCountry(RegionID, '') – Использует словарь по умолчанию: /opt/geo/regions_hierarchy.txt
regionToCountry(RegionID, 'ua') – Использует словарь для ключа 'ua': /opt/geo/regions_hierarchy_ua.txt

​

regionToName

Принимает идентификатор региона и геобазу и возвращает строку с названием региона на соответствующем языке. Если региона с указанным идентификатором не существует, возвращается пустая строка.

Синтаксис

regionToName(id\[, lang\])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. раздел Несколько геобаз. String. Необязательный.

Возвращаемое значение

• Название региона на языке, соответствующем значению geobase. String.
• В противном случае — пустая строка.

Пример

Query

SELECT regionToName(number::UInt32,'en') FROM numbers(0,5);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┐
│                                            │
│ World                                      │
│ USA                                        │
│ Colorado                                   │
│ Boulder County                             │
└────────────────────────────────────────────┘

​

regionToCity

Принимает идентификатор региона из геобазы. Если этот регион является городом или частью города, возвращает идентификатор соответствующего города. В противном случае возвращает 0.

Синтаксис

regionToCity(id [, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. раздел Несколько геобаз. String. Необязательный параметр.

Возвращаемое значение

• идентификатор региона для соответствующего города, если он существует. UInt32.
• 0, если такого региона нет.

Пример

Query

SELECT regionToName(number::UInt32, 'en'), regionToCity(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToCity(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                          │
│ World                                      │  0 │                                                          │
│ USA                                        │  0 │                                                          │
│ Colorado                                   │  0 │                                                          │
│ Boulder County                             │  0 │                                                          │
│ Boulder                                    │  5 │ Boulder                                                  │
│ China                                      │  0 │                                                          │
│ Sichuan                                    │  0 │                                                          │
│ Chengdu                                    │  8 │ Chengdu                                                  │
│ America                                    │  0 │                                                          │
│ North America                              │  0 │                                                          │
│ Eurasia                                    │  0 │                                                          │
│ Asia                                       │  0 │                                                          │
└────────────────────────────────────────────┴────┴──────────────────────────────────────────────────────────┘

​

regionToArea

Преобразует регион в область (тип 5 в геобазе). Во всех остальных отношениях эта функция совпадает с ‘regionToCity’.

Синтаксис

regionToArea(id [, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательный.

Возвращаемое значение

• Идентификатор региона для соответствующей области, если такой существует. UInt32.
• 0, если такого нет.

Пример

Query

SELECT DISTINCT regionToName(regionToArea(toUInt32(number), 'ua'))
FROM system.numbers
LIMIT 15

Response

┌─regionToName(regionToArea(toUInt32(number), \'ua\'))─┐
│                                                      │
│ Moscow and Moscow region                             │
│ St. Petersburg and Leningrad region                  │
│ Belgorod region                                      │
│ Ivanovsk region                                      │
│ Kaluga region                                        │
│ Kostroma region                                      │
│ Kursk region                                         │
│ Lipetsk region                                       │
│ Orlov region                                         │
│ Ryazan region                                        │
│ Smolensk region                                      │
│ Tambov region                                        │
│ Tver region                                          │
│ Tula region                                          │
└──────────────────────────────────────────────────────┘

​

regionToDistrict

Преобразует регион в федеральный округ (тип 4 в геобазе). Во всём остальном эта функция аналогична ‘regionToCity’.

Синтаксис

regionToDistrict(id [, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательный.

Возвращаемое значение

• Идентификатор региона для соответствующего города, если он существует. UInt32.
• 0, если такого региона нет.

Пример

Query

SELECT DISTINCT regionToName(regionToDistrict(toUInt32(number), 'ua'))
FROM system.numbers
LIMIT 15

Response

┌─regionToName(regionToDistrict(toUInt32(number), \'ua\'))─┐
│                                                          │
│ Central federal district                                 │
│ Northwest federal district                               │
│ South federal district                                   │
│ North Caucases federal district                          │
│ Privolga federal district                                │
│ Ural federal district                                    │
│ Siberian federal district                                │
│ Far East federal district                                │
│ Scotland                                                 │
│ Faroe Islands                                            │
│ Flemish region                                           │
│ Brussels capital region                                  │
│ Wallonia                                                 │
│ Federation of Bosnia and Herzegovina                     │
└──────────────────────────────────────────────────────────┘

​

regionToCountry

Преобразует регион в страну (тип 3 в геобазе). Во всём остальном эта функция аналогична ‘regionToCity’.

Синтаксис

regionToCountry(id [, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. раздел Несколько геобаз. String. Необязательный.

Возвращаемое значение

• Идентификатор региона для соответствующей страны, если такой существует. UInt32.
• 0, если такого нет.

Пример

Query

SELECT regionToName(number::UInt32, 'en'), regionToCountry(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToCountry(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                             │
│ World                                      │  0 │                                                             │
│ USA                                        │  2 │ USA                                                         │
│ Colorado                                   │  2 │ USA                                                         │
│ Boulder County                             │  2 │ USA                                                         │
│ Boulder                                    │  2 │ USA                                                         │
│ China                                      │  6 │ China                                                       │
│ Sichuan                                    │  6 │ China                                                       │
│ Chengdu                                    │  6 │ China                                                       │
│ America                                    │  0 │                                                             │
│ North America                              │  0 │                                                             │
│ Eurasia                                    │  0 │                                                             │
│ Asia                                       │  0 │                                                             │
└────────────────────────────────────────────┴────┴─────────────────────────────────────────────────────────────┘

​

regionToContinent

Преобразует регион в континент (тип 1 в геобазе). Во всех остальных отношениях эта функция совпадает с ‘regionToCity’.

Синтаксис

regionToContinent(id [, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательный параметр.

Возвращаемое значение

• Идентификатор региона для соответствующего континента, если он существует. UInt32.
• 0, если такого континента нет.

Пример

Query

SELECT regionToName(number::UInt32, 'en'), regionToContinent(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToContinent(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                               │
│ World                                      │  0 │                                                               │
│ USA                                        │ 10 │ North America                                                 │
│ Colorado                                   │ 10 │ North America                                                 │
│ Boulder County                             │ 10 │ North America                                                 │
│ Boulder                                    │ 10 │ North America                                                 │
│ China                                      │ 12 │ Asia                                                          │
│ Sichuan                                    │ 12 │ Asia                                                          │
│ Chengdu                                    │ 12 │ Asia                                                          │
│ America                                    │  9 │ America                                                       │
│ North America                              │ 10 │ North America                                                 │
│ Eurasia                                    │ 11 │ Eurasia                                                       │
│ Asia                                       │ 12 │ Asia                                                          │
└────────────────────────────────────────────┴────┴───────────────────────────────────────────────────────────────┘

​

regionToTopContinent

Возвращает континент верхнего уровня в иерархии для региона.

Синтаксис

regionToTopContinent(id[, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательный параметр.

Возвращаемое значение

• Идентификатор континента верхнего уровня (последнего при подъеме по иерархии регионов). UInt32.
• 0, если такого нет.

Пример

Query

SELECT regionToName(number::UInt32, 'en'), regionToTopContinent(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─id─┬─regionToName(regionToTopContinent(CAST(number, 'UInt32')), 'en')─┐
│                                            │  0 │                                                                  │
│ World                                      │  0 │                                                                  │
│ USA                                        │  9 │ America                                                          │
│ Colorado                                   │  9 │ America                                                          │
│ Boulder County                             │  9 │ America                                                          │
│ Boulder                                    │  9 │ America                                                          │
│ China                                      │ 11 │ Eurasia                                                          │
│ Sichuan                                    │ 11 │ Eurasia                                                          │
│ Chengdu                                    │ 11 │ Eurasia                                                          │
│ America                                    │  9 │ America                                                          │
│ North America                              │  9 │ America                                                          │
│ Eurasia                                    │ 11 │ Eurasia                                                          │
│ Asia                                       │ 11 │ Eurasia                                                          │
└────────────────────────────────────────────┴────┴──────────────────────────────────────────────────────────────────┘

​

regionToPopulation

Возвращает численность населения региона. Численность населения может быть указана в файлах геобазы. См. раздел “Словари”. Если для региона численность населения не указана, возвращается 0. В геобазе численность населения может быть указана для дочерних регионов, но не для родительских.

Синтаксис

regionToPopulation(id[, geobase])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательно.

Возвращаемое значение

• Численность населения региона. UInt32.
• 0, если данные отсутствуют.

Пример

Query

SELECT regionToName(number::UInt32, 'en'), regionToPopulation(number::UInt32) AS id, regionToName(id, 'en') FROM numbers(13);

Response

┌─regionToName(CAST(number, 'UInt32'), 'en')─┬─population─┐
│                                            │          0 │
│ World                                      │ 4294967295 │
│ USA                                        │  330000000 │
│ Colorado                                   │    5700000 │
│ Boulder County                             │     330000 │
│ Boulder                                    │     100000 │
│ China                                      │ 1500000000 │
│ Sichuan                                    │   83000000 │
│ Chengdu                                    │   20000000 │
│ America                                    │ 1000000000 │
│ North America                              │  600000000 │
│ Eurasia                                    │ 4294967295 │
│ Asia                                       │ 4294967295 │
└────────────────────────────────────────────┴────────────┘

​

regionIn

Проверяет, входит ли регион lhs в регион rhs. Возвращает число типа UInt8: 1, если входит, и 0, если не входит.

Синтаксис

regionIn(lhs, rhs\[, geobase\])

Параметры

• lhs — идентификатор региона lhs из геобазы. UInt32.
• rhs — идентификатор региона rhs из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательный параметр.

Возвращаемое значение

• 1, если принадлежит. UInt8.
• 0, если не принадлежит.

Подробности реализации

Отношение рефлексивно — любой регион также принадлежит самому себе.

Пример

Query

SELECT regionToName(n1.number::UInt32, 'en') || (regionIn(n1.number::UInt32, n2.number::UInt32) ? ' is in ' : ' is not in ') || regionToName(n2.number::UInt32, 'en') FROM numbers(1,2) AS n1 CROSS JOIN numbers(1,5) AS n2;

Response

World is in World
World is not in USA
World is not in Colorado
World is not in Boulder County
World is not in Boulder
USA is in World
USA is in USA
USA is not in Colorado
USA is not in Boulder County
USA is not in Boulder    

​

regionHierarchy

Принимает число типа UInt32 — идентификатор региона из геобазы. Возвращает массив идентификаторов регионов, состоящий из переданного региона и всех вышестоящих регионов по цепочке.

Синтаксис

regionHierarchy(id\[, geobase\])

Параметры

• id — идентификатор региона из геобазы. UInt32.
• geobase — ключ словаря. См. Несколько геобаз. String. Необязательный параметр.

Возвращаемое значение

• Массив идентификаторов регионов, включающий переданный регион и всех его родителей по цепочке. Array(UInt32).

Пример

Query

SELECT regionHierarchy(number::UInt32) AS arr, arrayMap(id -> regionToName(id, 'en'), arr) FROM numbers(5);

Response

┌─arr────────────┬─arrayMap(lambda(tuple(id), regionToName(id, 'en')), regionHierarchy(CAST(number, 'UInt32')))─┐
│ []             │ []                                                                                           │
│ [1]            │ ['World']                                                                                    │
│ [2,10,9,1]     │ ['USA','North America','America','World']                                                    │
│ [3,2,10,9,1]   │ ['Colorado','USA','North America','America','World']                                         │
│ [4,3,2,10,9,1] │ ['Boulder County','Colorado','USA','North America','America','World']                        │
└────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────┘