Skip to content

Custom Dictionaries

Overview⚓︎

Dictionaries are a ClickHouse feature that provides a convenient mechanism to store and use additional key-value data. For example, use dictionaries with geographic lookups or other metadata commonly used across tables.

This page covers the hashed and complex_key_hashed layouts for exact-key lookups. To match input strings against regular expressions instead, see Regexp Tree Dictionaries.

Prerequisites⚓︎

Make sure you have these RBAC permissions to perform operations on dictionaries:

  • add_dictionary
  • change_dictionary
  • delete_dictionary
  • view_dictionary
  • dictGet_sql

If you need to modify the dictionary files, these permissions are needed:

  • add_dictionaryfile
  • change_dictionaryfile
  • delete_dictionaryfile
  • view_dictionaryfile

For more information, see Account Permissions (RBAC).

Create a dictionary⚓︎

Creating a dictionary takes two API calls that must happen in order. The definition refers to the uploaded file by name, so the file has to be on the cluster before the definition can be created.

  1. Upload a dictionary file to the cluster.
  2. Use the Create a dictionary endpoint, including the filename and the ClickHouse format of the contents.

For the structure of a definition, see Dictionary format. For a worked example of both calls, see Example dictionary lookup.

Dictionary format⚓︎

A dictionary has two parts: a JSON definition and a source file containing the data. Everything needed to read the file goes in the definition's settings object.

Setting Definition
filename The name the source file was uploaded under.
format The format the source file is written in. This must be one of the formats ClickHouse supports.
layout How the key is structured. Hydrolix recommends hashed for a single key to a single value, or complex_key_hashed for multiple keys to a single value. For more information, see ClickHouse Dictionaries.
primary_key The columns that form the lookup key. A transform marks its timestamp column by setting "primary": true inside that column's datatype. A dictionary column never sets primary, and primary_key names the key columns instead.
lifetime_seconds How often ClickHouse refreshes the dictionary from the local file. 5 seconds is the recommended time, since Hydrolix checks every 5 seconds whether the file in the cloud bucket has changed, and synchronizes it locally.
dictionary_load_level Whether the dictionary loads for ingest, for query, or both. Defaults to ALL. See Dictionary load level.
output_columns The name and type of every column in the source file. See Output column data types.

Column names in output_columns must match the field names in the source file exactly, including casing. Mismatched names produce an empty dictionary.

In this example, a dictionary called country_dict reads from a file named country_dict.csv. Its layout is complex_key_hashed because primary_key names a composite key of id and country_short_name, and its lifetime_seconds refreshes the data every five seconds.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
  "name": "country_dict",
  "settings": {
    "filename": "country_dict.csv",
    "layout": "complex_key_hashed",
    "lifetime_seconds": 5,
    "primary_key": ["id", "country_short_name"],
    "format": "CSVWithNames",
    "output_columns": [
      {
        "name": "id",
        "datatype": { "type": "uint64" }
      },
      {
        "name": "country_short_name",
        "datatype": { "type": "string" }
      },
      {
        "name": "country_name",
        "datatype": { "type": "string" }
      }
    ]
  }
}

1
2
3
4
id,country_short_name,country_name
1,ARG,Argentina
2,USA,United States
3,ESP,Spain

This definition sets format to CSVWithNames, which requires a header row in the source file. That header row is what matches the fields in the file to the names in output_columns.

A dictionary keyed on a single column sets layout to hashed instead, with one column named in primary_key. The rest of the definition is unchanged.

The dictionary file doesn't define types. Types are specified in the output_columns section of the dictionary JSON.

For details on the file formats themselves, see the ClickHouse Formats documentation.

Set custom delimiters⚓︎

Each of the three supported CSV formats expects fields delimited only by commas and rows delimited by newlines. To read a source file that uses other separators, choose a custom format and declare the separators explicitly.

  1. Set format to one of the custom formats:

    • CustomSeparated
    • CustomSeparatedWithNames
    • CustomSeparatedWithNamesAndTypes

  2. Set these fields to the separators the file uses:

    • custom_column_delimiter: Characters that separate fields in each row. Defaults to , (comma).
    • custom_row_delimiter: Characters that separate rows. Defaults to a newline.

Both delimiter fields apply only to the custom formats. They're discarded if the dictionary is changed to any other format.

In this example, the file format is CustomSeparatedWithNames, the custom_column_delimiter is ; (semicolon), and the custom_row_delimiter is | (pipe).

Settings with Custom Delimiters
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
"settings": {
    "filename": "country_dict.csv",
    "layout": "hashed",
    "lifetime_seconds": 5,
    "primary_key": ["id"],
    "format": "CustomSeparatedWithNames",
    "custom_column_delimiter": ";",
    "custom_row_delimiter": "|",
    "output_columns": [
        {
            "datatype": {
                "type": "string"
            },
            "name": "country"
        },
        {
            "datatype": {
                "type": "uint64"
            },
            "name": "id"
        }
    ]
}

Two delimiter combinations are rejected:

  • The column and row delimiters can't be the same character.
  • Neither delimiter can contain a double quote.

Output column data types⚓︎

Dictionary output columns accept these types:

  • uint8, uint32, uint64
  • int8, int32, int64
  • double
  • string
  • datetime, datetime64
  • array

This set isn't the same as the one transform output columns accept. Dictionaries don't support map, json, ip, uuid, bool, or epoch, so a column of one of those types is valid in a transform but rejected in a dictionary definition.

Column definition otherwise resembles transform output columns, with two differences:

  • A dictionary can use any column as its key and doesn't need any timestamp column.
  • Each column specifies only name and datatype, and datatype carries only type. An array column also carries elements.

Array output columns⚓︎

Use an array when one key needs to return several values. An array output column takes an elements object that declares the type the array holds, the same way arrays work in a transform. For more information on the elements object, see Complex Data Types.

This example adds a major_cities array to the country dictionary, with a matching field in the source file:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
"output_columns": [
  // the other column definitions
  {
    "name": "major_cities",
    "datatype": {
      "type": "array",
      "elements": [
        { "type": "string" }
      ]
    }
  }
]

1
2
3
4
id,country_short_name,country_name,major_cities
1,ARG,Argentina,"['Buenos Aires', 'Cordoba', 'Rosario']"
2,USA,United States,"['New York', 'Los Angeles', 'Chicago']"
3,ESP,Spain,"['Madrid', 'Barcelona', 'Valencia']"

In a CSV source file, wrap the whole array in double quotes so that the commas between elements aren't read as column separators. Put square brackets around the elements and single-quote each string element. Numeric elements need no inner quoting, as in "[343, 415, 541]".

For this column in a complete definition, see Create the dictionary definition.

Dictionary load level⚓︎

Dictionaries are usable in two different places. At ingest, a transform can call dictGet to enrich incoming rows. At query time, a SQL statement can look values up or read the dictionary as a table. Limit a dictionary to one of them using dictionary_load_level:

Value Where the dictionary loads
ALL Both ingest and query. This is the default.
QUERY Query only.
INTAKE Ingest only.

Set the level to match where the dictionary is actually used. A dictionary set to QUERY isn't available to a transform, so a dictGet call at ingest can't resolve it. A dictionary set to INTAKE isn't loaded into the query system, so querying it fails as though the dictionary doesn't exist.

Leaving the setting out gives ALL, which is why the definitions on this page omit it. ALL takes precedence over the other values, so a list containing ALL is stored as ALL alone.

Example dictionary lookup⚓︎

This example builds a dictionary that maps a composite key of numeric ID and three-letter country code to a country name and a list of major cities. It takes five steps:

  1. Create the source file.
  2. Upload the source file to the cluster.
  3. Create the dictionary definition.
  4. Confirm the dictionary is available.
  5. Look up a value.

Replace hostname, {org_id}, and {project_id} with values for your own cluster, and set HDX_TOKEN in your shell to an authorization token. The examples use myproject as the project name.

Create the source file⚓︎

Save this content as country_dict.csv. The major_cities column holds an array, so each value is wrapped in double quotes to keep its internal commas from splitting the row into extra columns.

Dictionary Source File
1
2
3
4
id,country_short_name,country_name,major_cities
1,ARG,Argentina,"['Buenos Aires', 'Cordoba', 'Rosario']"
2,USA,United States,"['New York', 'Los Angeles', 'Chicago']"
3,ESP,Spain,"['Madrid', 'Barcelona', 'Valencia']"

Upload the source file⚓︎

Uploading stores the file on the cluster under a name that the dictionary definition refers to. This name is separate from the local filename, and the filename setting in the next step must match it.

Upload the Dictionary File
1
2
3
4
5
curl --request POST \
  --url "https://hostname.hydrolix.live/config/v1/orgs/{org_id}/projects/{project_id}/dictionaries/files/" \
  --header "Authorization: Bearer ${HDX_TOKEN}" \
  --form "file=@country_dict.csv" \
  --form-string "name=country_dict.csv"

Create the dictionary definition⚓︎

The definition carries the settings described in Dictionary format. Here id and country_short_name together form the key, which is why layout is complex_key_hashed.

Create the Dictionary
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
POST https://hostname.hydrolix.live/config/v1/orgs/{org_id}/projects/{project_id}/dictionaries/
Authorization: Bearer ${HDX_TOKEN}
Content-Type: application/json

{
  "name": "country_dict",
  "settings": {
    "filename": "country_dict.csv",
    "layout": "complex_key_hashed",
    "lifetime_seconds": 5,
    "primary_key": ["id", "country_short_name"],
    "format": "CSVWithNames",
    "output_columns": [
      {
        "name": "id",
        "datatype": { "type": "uint64" }
      },
      {
        "name": "country_short_name",
        "datatype": { "type": "string" }
      },
      {
        "name": "country_name",
        "datatype": { "type": "string" }
      },
      {
        "name": "major_cities",
        "datatype": {
          "type": "array",
          "elements": [
            { "type": "string" }
          ]
        }
      }
    ]
  }
}

Confirm the dictionary is available⚓︎

Uploading the file and creating the definition used the Config API. This step and the next one use SQL instead, which runs from any interface that reaches the query system, such as:

For the full set of options, see Search Tools.

Check that the query system can see the dictionary. In SQL, a dictionary name carries its project as a prefix, so country_dict in the myproject project becomes myproject_country_dict. This query returns 1 when the dictionary exists:

Confirm the Dictionary Exists
1
EXISTS myproject_country_dict

To list every dictionary available instead:

List All Dictionaries
1
SHOW DICTIONARIES

Look up a value⚓︎

Retrieve an attribute for a key with dictGet. Because the key is composite, pass its parts as a tuple in the same order as primary_key:

Look Up a Country Name
1
SELECT dictGet('myproject_country_dict', 'country_name', (1, 'ARG'))

An array attribute comes back as an array:

Look Up an Array Attribute
1
SELECT dictGet('myproject_country_dict', 'major_cities', (1, 'ARG'))
Result
1
['Buenos Aires','Cordoba','Rosario']

This dictionary is now available at both ingest and query time, which is the default. The dictionary_load_level setting can restrict a dictionary to one or the other.

Query dictionaries⚓︎

A dictionary can be read as a table, which is useful for inspecting its contents:

Read a Dictionary as a Table
1
SELECT * FROM myproject_country_dict

Dictionary Names are Different from Table Names

To refer to a dictionary in a SQL statement, prepend its name with the project name, separated by an underscore. That's why the country_dict dictionary in the myproject project is queried as myproject_country_dict.

To look up a single attribute for a key, use dictGet, which names the dictionary, the attribute, and the key directly. Its variants differ in how they treat a key that isn't in the dictionary:

  • dictGetOrDefault returns a default value supplied in the call.
  • dictGetOrNull returns null.
  • dictHas reports whether the key exists at all.

For full descriptions of these functions, see the ClickHouse documentation.

Expand an array attribute into rows⚓︎

Wrap dictGet in arrayJoin to return one row per element of an array attribute:

Expand an Array Attribute
1
SELECT arrayJoin(dictGet('myproject_country_dict', 'major_cities', (1, 'ARG'))) AS city
Result
1
2
3
Buenos Aires
Cordoba
Rosario

To enrich rows from a table, pass the table's own columns as the key instead of literal values.

Request array attributes one at a time

dictGet also accepts several attribute names at once, as in dictGet(dict, ('country_name', 'major_cities'), key). That form returns a tuple rather than an array, and arrayJoin rejects a tuple. To expand an array, request that attribute in its own dictGet call and retrieve any other attributes separately.