Contact UsGet started
© 2025 Direct Cursus Technology L.L.C.

Connecting external dictionaries in Managed Service for ClickHouse®

Written by
Updated at December 24, 2024

You can add external dictionaries to your cluster and remove them. Read more about dictionaries in the ClickHouse® documentation.

Managed Service for ClickHouse® supports several types of dictionary sources:

  • ClickHouse®
  • HTTP(s);
  • MongoDB
  • MySQL®
  • PostgreSQL.

You can manage dictionaries either via SQL (recommended) or via Yandex Cloud cloud interfaces.

Warning

Changing dictionary settings will restart ClickHouse® servers on the cluster hosts.

Getting a list of dictionaries

  1. In the management console, go to the folder page and select Managed Service for ClickHouse.
  2. Click the cluster name and open the Dictionaries tab.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To get a list of external dictionaries in a ClickHouse® cluster:

  1. View a description of the CLI command for getting detailed cluster information:

    yc managed-clickhouse cluster get --help

  2. Run this command:

    yc managed-clickhouse cluster get <cluster_name>

The added dictionaries are displayed in the dictionaries: section of the command output.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Cluster.ListExternalDictionaries method and send the following request, e.g., via cURL:

    curl \
    --request GET \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>/externalDictionaries'

    You can get the cluster ID with a list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.ListExternalDictionaries call and send the following request, e.g., via gRPCurl:

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
            "cluster_id": "<cluster_ID>"
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.ClusterService.ListExternalDictionaries

    You can get the cluster ID with a list of clusters in the folder.

  4. View the server response to make sure the request was successful.

  1. Connect to the required database of the Managed Service for ClickHouse® cluster using clickhouse-client.
  2. Run the SHOW DICTIONARIES query.

Creating a dictionary

Warning

If the dictionary was created in the console, it cannot be managed via SQL.

  1. In the management console, go to the folder page and select Managed Service for ClickHouse.
  2. Click the cluster name and open the Dictionaries tab.
  3. In the top-right corner, click Create dictionary.
  4. Specify the dictionary settings.
  5. Click Save.

Warning

If the dictionary is added via the CLI, it cannot be managed via SQL.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To create an external dictionary in a ClickHouse® cluster:

  1. View a description of the CLI command for adding dictionaries:

    yc managed-clickhouse cluster add-external-dictionary --help

  2. Run the add dictionary command and specify dictionary settings:

    yc managed-clickhouse cluster add-external-dictionary \
   --name=<ClickHouse®>_cluster_name \
   --dict-name=<dictionary_name> \
   ...

Warning

If the dictionary is added via the API, it cannot be managed via SQL.

To create an external dictionary in a ClickHouse® cluster:

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Cluster.CreateExternalDictionary method and send the following request, e.g., via cURL:

    1. Create a file named body.json and add the following contents to it:

      {
  "externalDictionary": {
    "name": "<dictionary_name>",
    "structure": {
      "id": {
        "name": "<dictionary_key_column_name>"
      },
      "key": {
        "attributes": [
          <dictionary_data_column_array>
        ]
      },
      "rangeMin": {<RANGE_HASHED_start_column>},
      "rangeMax": {<RANGE_HASHED_end_column>},
      "attributes": [
         <field_description_array>
      ]
    },
    "layout": {<memory_storage_method>},
    "fixedLifetime": "<fixed_interval_between_updates>",
    "lifetimeRange": {<range_for_selecting_interval_between_updates>},
    "httpSource": {<HTTP(s)_source_settings>},
    "mysqlSource": {<MySQL®>_source_settings},
    "clickhouseSource": {<ClickHouse®>_source_settings},
    "mongodbSource": {<MongoDB>_source_settings},
    "postgresqlSource": {<PostgreSQL>_source_settings}
  }
}

      Where:

      • externalDictionary.name: Dictionary name.

      • externalDictionary.structure: Dictionary structure:

        • id.name: Dictionary key column name.
        • key.attributes: Array for description of the dictionary's composite key.
        • rangeMin: Description of the start column, required if using RANGE_HASHED as the memory layout type.
        • rangeMax: Description of the end column, required if using RANGE_HASHED as the memory layout type.
        • attributes: Array of descriptions of the fields available for database queries.

        Warning

        The structure.id.name and structure.key.attributes fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • externalDictionary.layout: Memory layout type for the dictionary.

      • externalDictionary.fixedLifetime: Fixed interval between dictionary updates in seconds.

      • externalDictionary.lifetimeRange: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers.

        Warning

        The fixedLifetime and lifetimeRange fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • externalDictionary.***Source: Dictionary data source settings. Select one of the sources and specify its settings:

        • httpSource: HTTP(s) source.
        • mysqlSource: MySQL® source.
        • clickhouseSource: ClickHouse® source.
        • mongodbSource: MongoDB source.
        • postgresqlSource: PostgreSQL source.

      For a detailed description of the dictionary attributes and other settings, see below.

    2. Run this request:

      curl \
  --request POST \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>:createExternalDictionary' \
  --data '@body.json'

      You can get the cluster ID with a list of clusters in the folder.

  3. View the server response to make sure the request was successful.

Warning

If the dictionary is added via the API, it cannot be managed via SQL.

To create an external dictionary in a ClickHouse® cluster:

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.CreateExternalDictionary call and send the following request, e.g., via gRPCurl:

    1. Create a file named body.json and add the following contents to it:

      {
  "cluster_id": "<cluster_ID>",
  "external_dictionary": {
    "name": "<dictionary_name>",
    "structure": {
      "id": {
        "name": "<dictionary_key_column_name>"
      },
      "key": {
        "attributes": [<dictionary_data_column_array>]
      },
      "range_min": {<RANGE_HASHED_start_column>},
      "range_max": {<RANGE_HASHED_end_column>},
      "attributes": [<field_description_array>]
    },
    "layout": {<memory_storage_method>},
    "fixed_lifetime": "<fixed_interval_between_updates>",
    "lifetime_range": {<range_for_selecting_interval_between_updates>},
    "http_source": {<HTTP(s)_source_settings>},
    "mysql_source": {<MySQL_source_settings>},
    "clickhouse_source": {<ClickHouse®_source_settings>},
    "mongodb_source": {<MongoDB_source_settings>},
    "postgresql_source": {<PostgreSQL_source_settings>}
  }
}

      Where:

      • external_dictionary.name: Dictionary name.

      • external_dictionary.structure: Dictionary structure.

        • id.name: Dictionary key column name.
        • key.attributes: Array of descriptions for columns with dictionary data.
        • range_min: Description of the start column, required if using RANGE_HASHED as the memory layout type.
        • range_max: Description of the end column, required if using RANGE_HASHED as the memory layout type.
        • attributes: Array of descriptions of the fields available for database queries.

        Warning

        The structure.id.name and structure.key.attributes fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • external_dictionary.layout: Memory layout type for the dictionary.

      • external_dictionary.fixed_lifetime: Fixed interval between dictionary updates in seconds.

      • external_dictionary.lifetime_range: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers.

        Warning

        The fixed_lifetime and lifetime_range fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • external_dictionary.***_source: Dictionary data source settings. Select one of the sources and specify its settings:

        • http_source: HTTP(s) source.
        • mysql_source: MySQL® source.
        • clickhouse_source: ClickHouse® source.
        • mongodb_source: MongoDB source.
        • postgresql_source: PostgreSQL source.

      For a detailed description of the dictionary attributes and other settings, see below.

      You can get the cluster ID with a list of clusters in the folder.

    2. Run this request:

      grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d @ \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.clickhouse.v1.ClusterService.CreateExternalDictionary \
  < body.json

  4. View the server response to make sure the request was successful.

Warning

If the dictionary is added via SQL, management using the console, the CLI, and the API is not available for it.

  1. Connect to the required database of the Managed Service for ClickHouse® cluster using clickhouse-client.

  2. Execute DDL request:

    CREATE DICTIONARY <dictionary_name>(
  <data_columns>
)
PRIMARY KEY <name_of_column_with_keys>
SOURCE(<source>(<source_configuration>))
LIFETIME(<update_interval>)
LAYOUT(<memory_storage_method>());

    Where:

    • <dictionary_name>: Name of the new dictionary.
    • <data_columns>: List of columns with dictionary entries and their type.
    • PRIMARY KEY: Dictionary key column name.
    • SOURCE: Source and its parameters.
    • LIFETIME: Dictionary update frequency.
    • LAYOUT: Memory layout type for the dictionary. Supported options:
      • flat,
      • hashed,
      • cache,
      • range_hashed,
      • complex_key_hashed,
      • complex_key_cache.

For more information about the settings, see the ClickHouse® documentation.

Updating a dictionary

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Cluster.UpdateExternalDictionary method and send the following request, e.g., via cURL:

    1. Create a file named body.json and add the following contents to it:

      {
  "externalDictionary": {
    "name": "<dictionary_name>",
    "structure": {
      "id": {
        "name": "<dictionary_key_column_name>"
      },
      "key": {
        "attributes": [
          <dictionary_data_column_array>
        ]
      },
      "rangeMin": {<RANGE_HASHED_start_column>},
      "rangeMax": {<RANGE_HASHED_end_column>},
      "attributes": [
         <field_description_array>
      ]
    },
    "layout": {<memory_storage_method>},
    "fixedLifetime": "<fixed_interval_between_updates>",
    "lifetimeRange": {<range_for_selecting_interval_between_updates>},
    "httpSource": {<HTTP(s)_source_settings>},
    "mysqlSource": {<MySQL®>_source_settings},
    "clickhouseSource": {<ClickHouse®>_source_settings},
    "mongodbSource": {<MongoDB>_source_settings},
    "postgresqlSource": {<PostgreSQL>_source_settings}
  },
  "updateMask": "externalDictionary.<setting_1>,...,externalDictionary.<setting_N>"
}

      Where:

      • updateMask: List of parameters to update as a single string, separated by commas.

        In this case, list all the dictionary settings to update.

      • externalDictionary.name: Dictionary name.

      • externalDictionary.structure: Dictionary structure:

        • id.name: Dictionary key column name.
        • key.attributes: Array for description of the dictionary's composite key.
        • rangeMin: Description of the start column, required if using RANGE_HASHED as the memory layout type.
        • rangeMax: Description of the end column, required if using RANGE_HASHED as the memory layout type.
        • attributes: Array of descriptions of the fields available for database queries.

        Warning

        The structure.id.name and structure.key.attributes fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • externalDictionary.layout: Memory layout type for the dictionary.

      • externalDictionary.fixedLifetime: Fixed interval between dictionary updates in seconds.

      • externalDictionary.lifetimeRange: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers.

        Warning

        The fixedLifetime and lifetimeRange fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • externalDictionary.***Source: Dictionary data source settings. Select one of the sources and specify its settings:

        • httpSource: HTTP(s) source.
        • mysqlSource: MySQL® source.
        • clickhouseSource: ClickHouse® source.
        • mongodbSource: MongoDB source.
        • postgresqlSource: PostgreSQL source.

      For a detailed description of the dictionary attributes and other settings, see below.

    2. Run this request:

      curl \
  --request POST \
  --header "Authorization: Bearer $IAM_TOKEN" \
  --header "Content-Type: application/json" \
  --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>:updateExternalDictionary' \
  --data '@body.json'

      You can get the cluster ID with a list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.UpdateExternalDictionary call and send the following request, e.g., via gRPCurl:

    1. Create a file named body.json and add the following contents to it:

      {
  "cluster_id": "<cluster_ID>",
  "external_dictionary": {
    "name": "<dictionary_name>",
    "structure": {
      "id": {
        "name": "<dictionary_key_column_name>"
      },
      "key": {
        "attributes": [<dictionary_data_column_array>]
      },
      "range_min": {<RANGE_HASHED_start_column>},
      "range_max": {<RANGE_HASHED_end_column>},
      "attributes": [<field_description_array>]
    },
    "layout": {<memory_storage_method>},
    "fixed_lifetime": "<fixed_interval_between_updates>",
    "lifetime_range": {<range_for_selecting_interval_between_updates>},
    "http_source": {<HTTP(s)_source_settings>},
    "mysql_source": {<MySQL_source_settings>},
    "clickhouse_source": {<ClickHouse®_source_settings>},
    "mongodb_source": {<MongoDB_source_settings>},
    "postgresql_source": {<PostgreSQL_source_settings>}
  },
  "update_mask": "externalDictionary.<setting_1>,...,externalDictionary.<setting_N>"
}

      Where:

      • update_mask: List of parameters to update as a single string, separated by commas.

        In this case, list all the dictionary settings to update.

      • external_dictionary.name: Dictionary name.

      • external_dictionary.structure: Dictionary structure.

        • id.name: Dictionary key column name.
        • key.attributes: Array of descriptions for columns with dictionary data.
        • range_min: Description of the start column, required if using RANGE_HASHED as the memory layout type.
        • range_max: Description of the end column, required if using RANGE_HASHED as the memory layout type.
        • attributes: Array of descriptions of the fields available for database queries.

        Warning

        The structure.id.name and structure.key.attributes fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • external_dictionary.layout: Memory layout type for the dictionary.

      • external_dictionary.fixed_lifetime: Fixed interval between dictionary updates in seconds.

      • external_dictionary.lifetime_range: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers.

        Warning

        The fixed_lifetime and lifetime_range fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

      • external_dictionary.***_source: Dictionary data source settings. Select one of the sources and specify its settings:

        • http_source: HTTP(s) source.
        • mysql_source: MySQL® source.
        • clickhouse_source: ClickHouse® source.
        • mongodb_source: MongoDB source.
        • postgresql_source: PostgreSQL source.

      For a detailed description of the dictionary attributes and other settings, see below.

      You can get the cluster ID with a list of clusters in the folder.

    2. Run this request:

      grpcurl \
  -format json \
  -import-path ~/cloudapi/ \
  -import-path ~/cloudapi/third_party/googleapis/ \
  -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
  -rpc-header "Authorization: Bearer $IAM_TOKEN" \
  -d @ \
  mdb.api.cloud.yandex.net:443 \
  yandex.cloud.mdb.clickhouse.v1.ClusterService.UpdateExternalDictionary \
  < body.json

  4. View the server response to make sure the request was successful.

Deleting a dictionary

  1. In the management console, go to the folder page and select Managed Service for ClickHouse.
  2. Click the cluster name and open the Dictionaries tab.
  3. Click next to the dictionary you want to delete and select Delete dictionary.

If you do not have the Yandex Cloud command line interface yet, install and initialize it.

The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name or --folder-id parameter.

To remove an external dictionary:

  1. View a description of the CLI command for removing a dictionary:

    yc managed-clickhouse cluster remove-external-dictionary --help

  2. Run the command to remove a dictionary:

    yc managed-clickhouse cluster remove-external-dictionary \
   --name=<cluster_name> \
   --dict-name=<dictionary_name>

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Use the Cluster.DeleteExternalDictionary method and send the following request, e.g., via cURL:

    curl \
    --request POST \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --header "Content-Type: application/json" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/<cluster_ID>:deleteExternalDictionary' \
    --data '{
              "externalDictionaryName": "<dictionary_name>"
            }'

    Where externalDictionaryName is the name of the dictionary you need to delete. You can request the dictionary name with a list of external dictionaries in the cluster.

    You can get the cluster ID with a list of clusters in the folder.

  3. View the server response to make sure the request was successful.

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Use the ClusterService.DeleteExternalDictionary call and send the following request, e.g., via gRPCurl:

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d '{
            "cluster_id": "<cluster_ID>",
            "external_dictionary_name": "<dictionary_name>"
        }' \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.ClusterService.DeleteExternalDictionary

    Where external_dictionary_name is the name of the dictionary you need to delete. You can request the dictionary name with a list of external dictionaries in the cluster.

    You can get the cluster ID with a list of clusters in the folder.

  4. View the server response to make sure the request was successful.

  1. Connect to the required database of the Managed Service for ClickHouse® cluster using clickhouse-client.
  2. Run the DROP DICTIONARY<DB_name>.<dictionary_name> query.

Dictionary settings

  • Name: Name of the new dictionary.

  • Source: Dictionary source settings. Select one of the listed sources and specify its settings:

    ClickHouse®

    • Host: ClickHouse® host name. This is an optional parameter.

      The host must be in the same network as the ClickHouse® cluster.

    • Port: Port for connecting to the source. This is an optional parameter.

    • User: Name of source database user.

    • Password: Password to access the source database.

    • Database: Source database name.

    • Table: Source table name.

    • Selection condition: Condition for selecting rows to generate a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL command.

    • (Optional) Check dictionary status: SQL query to check for changes in a dictionary. ClickHouse® will update the dictionary only if the result of this query changes.

    MongoDB
    • Host: MongoDB host name. The host must be in the same network as the ClickHouse® cluster.
    • Port: Port for connecting to the source.
    • User: Name of source database user.
    • Password: Password to access the source database.
    • Database: Source database name.
    • Collection: MongoDB collection name.
    MySQL®
    • Replicas: List of MySQL® replicas that will be used as the dictionary source.
      For replicas, you can set general connection settings or set up a port, username and password.
    • Port: Port for connecting to the source.
    • User: Name of source database user.
    • Password: Password to access the source database.
    • Database: Source database name.
    • Table: Source table name.
    • Selection condition: Condition for selecting rows to generate a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL command.
    • (Optional) Check dictionary status: SQL query to check for changes in a dictionary. ClickHouse® will update the dictionary only if the result of this query changes.
    PostgreSQL
    • Hosts: Names of the PostgreSQL master host and its replicas that will be used as dictionary sources. The hosts must be in the same network as the ClickHouse® cluster.
    • Port: Port for connecting to the source.
    • User: Name of source database user.
    • Password: Password to access the source database.
    • Database: Source database name.
    • Table: Source table name.
    • (Optional) Check dictionary status: SQL query to check for changes in a dictionary. ClickHouse® will update the dictionary only if the result of this query changes.
    • SSL mode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. For more information, see the PostgreSQL documentation.
    HTTP(s)
    • URL: HTTP(s) source URL.
    • File format: File format for the HTTP(s) source. Read more about formats in the ClickHouse® documentation.

    For more information about dictionary sources and their connection parameters, see the ClickHouse® documentation.

  • Memory storage: Memory layout type for the dictionary. Supported methods: flat, hashed, cache, range_hashed, complex_key_hashed, and complex_key_cache. For more information about how to store dictionaries in memory, see the ClickHouse® documentation.

  • Cache size: Number of cache cells for the cache and complex_key_cache methods. For more information, see the ClickHouse® documentation.

  • Numeric key: Dictionary key column name. The key column must be the UInt64 data type. It is used for the flat, hashed, cache, and range_hashed methods. For more information, see the ClickHouse® documentation.

  • Data column: Description of the dictionary's composite key. A composite key may consist of one or more elements. It is used for the complex_key_hashed and complex_key_cache methods:

    • Name: Column name.
    • Data type: Column data type.
    • (Optional) Default value: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
    • (Optional) Expression Expression ClickHouse® applies to the column value.
    • Hierarchical: Hierarchical support flag.
    • Injective: Injective id → attribute mapping flag.

    For more information about composite key parameters, see the ClickHouse® documentation.

  • Memory storage: Dictionary update rate settings:

    • Period: Dictionary update frequency. Select the update interval type and settings:

      • fixed: Fixed period between dictionary updates:

        • Period duration: Update interval for dictionary data in seconds.

      • variable: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers:

        • Minimum: Minimum interval between dictionary updates in seconds.
        • Maximum: Maximum interval between dictionary updates in seconds.

    For more information about updating dictionaries, see the ClickHouse® documentation.

  • --dict-name: Name of the new dictionary.

  • --***-source: Dictionary source settings. Select one of the listed sources and specify its settings:

    --clickhouse-source: ClickHouse® source

    • host: Source host name. This is an optional parameter.

      The host must be in the same network as the ClickHouse® cluster.

    • port: Port for connecting to the source. This is an optional parameter.

    • db: Source database name.

    • user: Name of source database user.

    • password: Password to access the source database.

    • table: Source table name.

    • where: Condition for selecting rows to generate a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL command.

    • secure: Whether to use SSL when establishing the connection.

    --mongodb-source: MongoDB source
    • host: Source host name. The host must be in the same network as the ClickHouse® cluster.
    • port: Port for connecting to the source.
    • db: Source database name.
    • user: Name of source database user.
    • password: Password to access the source database.
    • connection: Name of the collection for the source.
    --mysql-source: MySQL® source
    • db: Source database name.
    • user: Name of source database user.
    • password: Password to access the source database.
    • table: Source table name.
    • where: Condition for selecting rows to generate a dictionary from. For example, the id=10 condition is the same as the WHERE id=10 SQL clause.
    • share-connection: Whether to make the connection shared across multiple requests.
    • close-connection: Whether to close the connection after each request.
    --postgresql-source: PostgreSQL source
    • table: Source table name.
    • ssl-mode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. Acceptable values: disable, allow, prefer, verify-ca, and verify-full.
    --http-source: HTTP(s) source
    • url: HTTP(s) source URL.
    • format: File format for the HTTP(s) source. Read more about formats in the ClickHouse® documentation.

  • --http-header: Special HTTP header for the request to the HTTP(s) source:

    • name: Header name.
    • value: Header value.

  • --mysql-replica: Settings of MySQL® source replicas:

    • host: Replica host name.
    • priority: Replica priority. During a connection attempt, ClickHouse® reads from replicas based on their priority. The lower the number, the higher the priority.
    • port: Port for connecting to the replica.
    • user: Database user name.
    • password: Password for access to the database.

  • --mysql-invalidate-query: Query for checking changes in a MySQL® dictionary. ClickHouse® updates the dictionary only if the results of this query change.

  • --postgresql-source-hosts: Names of the PostgreSQL master host and its replicas that will be used as PostgreSQL sources. The hosts must be in the same network as the ClickHouse® cluster.

  • --postgresql-invalidate-query: Query for checking changes in a PostgreSQL dictionary. ClickHouse® updates the dictionary only if the results of this query change.

  • --layout-type: Memory layout type for the dictionary. Supported methods: flat, hashed, cache, range_hashed, complex_key_hashed, and complex_key_cache. For more information about how to store dictionaries in memory, see the ClickHouse® documentation.

  • --layout-size-in-cells: Number of cache cells for the cache and complex_key_cache methods. For more information about the cache, see the ClickHouse® documentation.

  • --layout-max-array-size: Maximum key value for the flat method. Determines the memory size used by the dictionary, this size being proportional to the biggest key value. For more information about the key value, see the ClickHouse® documentation.

  • --structure-id: Dictionary key column name. The key column must be the UInt64 data type. It is used for the flat, hashed, cache, and range_hashed methods. For more information about keys, see the ClickHouse® documentation.

  • --structure-key: Description of the dictionary's composite key. A composite key may consist of one or more elements. It is used for the complex_key_hashed and complex_key_cache methods:

    • name: Column name.
    • type: Column data type.
    • null-value: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
    • expression: Expression ClickHouse® applies to the column value.
    • hierarchical: Hierarchical support flag.
    • injective: Injective id → attribute mapping flag.

    For more information about composite key parameters, see the ClickHouse® documentation.

    Warning

    The --structure-id and --structure-key settings are mutually exclusive, i.e., the use of one makes it impossible to use the other.

  • --structure-attribute: Description of the fields available for database queries.

    • name: Column name.
    • type: Column data type.
    • null-value: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
    • expression: Expression ClickHouse® applies to the column value.
    • hierarchical: Hierarchical support flag.
    • injective: Injective id → attribute mapping flag.

  • --fixed-lifetime: Fixed interval between dictionary updates in seconds.

  • --lifetime-range: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers.

    • min: Minimum interval between dictionary updates in seconds.
    • max: Maximum interval between dictionary updates in seconds.

    Warning

    The --fixed-lifetime and --lifetime-range settings are mutually exclusive, i.e., the use of one makes it impossible to use the other.

  • externalDictionary: New dictionary settings:

    • name: Name of the new dictionary.

    • ***Source: Dictionary data source. Select one of the listed sources and specify its settings:

      clickhouseSource: ClickHouse® source

      • db: Source database name.

      • table: Source table name.

      • where: Condition for selecting rows to generate a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL command.

      • host: Source host name. This is an optional parameter.

        The host must be in the same network as the ClickHouse® cluster.

      • port: Port for connecting to the source. This is an optional parameter.

      • user: Name of source database user.

      • password: Password to access the source database.

      • secure: Whether to use SSL to establish the connection.

      mongodbSource: MongoDB source
      • db: Source database name.
      • host: Source host name. The host must be in the same network as the ClickHouse® cluster.
      • port: Port for connecting to the source.
      • user: Name of source database user.
      • password: Password to access the source database.
      • collection: Name of the collection for the source.
      mysqlSource: MySQL® source
      • db: Source database name.
      • table: Source table name.
      • where: Condition for selecting rows to generate a dictionary from. For example, the id=10 condition is the same as the WHERE id=10 SQL clause.
      • user: Name of source database user.
      • password: Password to access the source database.
      • replicas: Settings for source replicas:
        • host: Replica host name. The host must be in the same network as the ClickHouse® cluster.
        • priority: Replica priority. During a connection attempt, ClickHouse® reads from replicas based on their priority. The lower the number, the higher the priority.
        • port: Port for connecting to the replica.
        • user: Database user name.
        • password: Password for access to the database.
      • invalidateQuery: Query for checking changes in a MySQL® dictionary. ClickHouse® updates the dictionary only if the results of this query change.
      • shareConnection: Whether to make the connection shared across multiple requests.
      • closeConnection: Whether to close the connection after each request.
      postgresqlSource: PostgreSQL source
      • db: Source database name.
      • table: Source table name.
      • port: Port for connecting to the source.
      • user: Name of source database user.
      • password: Password to access the source database.
      • sslMode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. Acceptable values: DISABLE, ALLOW, PREFER, VERIFY_CA, and VERIFY_FULL.
      • hosts: Names of the PostgreSQL master host and its replicas that will be used as dictionary sources. The hosts must be in the same network as the ClickHouse® cluster.
      • invalidateQuery: SQL query to check for changes in a dictionary. ClickHouse® will update the dictionary only if the result of this query changes.
      httpSource: HTTP(s) source
      • url: HTTP(s) source URL.
      • format: File format for the HTTP(s) source. Read more about formats in the ClickHouse® documentation.
      • headers: Special HTTP headers for the request to the source:
        • name: Header name.
        • value: Header value.

    • layout.type: Memory layout type for the dictionary. Supported methods: FLAT, HASHED, CACHE, RANGE_HASHED, COMPLEX_KEY_HASHED, and COMPLEX_KEY_CACHE. For more information about how to store dictionaries in memory, see the ClickHouse® documentation.

    • layout.sizeInCells: Number of cache cells for the CACHE and COMPLEX_KEY_CACHE methods. For more information about the cache, see the ClickHouse® documentation.

    • layout.maxArraySize: Maximum key value for the FLAT method. Determines the memory size used by the dictionary, this size being proportional to the biggest key value. For more information about the key value, see the ClickHouse® documentation.

    • structure.id.name: Dictionary key column name. The key column must be the UInt64 data type. It is used for the FLAT, HASHED, CACHE, and RANGE_HASHED methods. For more information about keys, see the ClickHouse® documentation.

    • structure.key.attributes: Description of the dictionary's composite key. A composite key may consist of one or more elements. It is used for the COMPLEX_KEY_HASHED and COMPLEX_KEY_CACHE methods:

      • name: Column name.
      • type: Column data type.
      • nullValue: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
      • expression: Expression ClickHouse® applies to the column value.
      • hierarchical: Hierarchical support flag.
      • injective: Injective id → attribute mapping flag.

      For more information about composite key parameters, see the ClickHouse® documentation.

      Warning

      The structure.id.name and structure.key.attributes fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

    • structure.attributes: Description of the fields available for database queries.

      • name: Column name.
      • type: Column data type.
      • nullValue: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
      • expression: Expression ClickHouse® applies to the column value.
      • hierarchical: Hierarchical support flag.
      • injective: Injective id → attribute mapping flag.

    • fixedLifetime: Fixed interval between dictionary updates in seconds.

    • lifetimeRange: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers. The settings for the range boundaries are as follows:

      • min: Minimum interval between dictionary updates in seconds.
      • max: Maximum interval between dictionary updates in seconds.

      Warning

      The fixedLifetime and lifetimeRange fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

  • external_dictionary: New dictionary settings:

    • name: Name of the new dictionary.

    • ***_source: Dictionary data source. Select one of the listed sources and specify its settings:

      clickhouse_source: ClickHouse® source

      • db: Source database name.

      • table: Source table name.

      • where: Condition for selecting rows to generate a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL command.

      • host: Source host name. This is an optional parameter.

        The host must be in the same network as the ClickHouse® cluster.

      • port: Port for connecting to the source. This is an optional parameter.

      • user: Name of source database user.

      • password: Password to access the source database.

      • secure: Whether to use SSL to establish the connection.

      mongodb_source: MongoDB source
      • db: Source database name.
      • host: Source host name. The host must be in the same network as the ClickHouse® cluster.
      • port: Port for connecting to the source.
      • user: Name of source database user.
      • password: Password to access the source database.
      • collection: Name of the collection for the source.
      mysql_source: MySQL® source
      • db: Source database name.
      • table: Source table name.
      • where: Condition for selecting rows to generate a dictionary from. For example, the id=10 condition is the same as the WHERE id=10 SQL clause.
      • user: Name of source database user.
      • password: Password to access the source database.
      • replicas: Settings for source replicas:
        • host: Replica host name. The host must be in the same network as the ClickHouse® cluster.
        • priority: Replica priority. During a connection attempt, ClickHouse® reads from replicas based on their priority. The lower the number, the higher the priority.
        • port: Port for connecting to the replica.
        • user: Database user name.
        • password: Password for access to the database.
      • invalidate_query: Query for checking changes in a MySQL® dictionary. ClickHouse® updates the dictionary only if the results of this query change.
      • share_connection: Whether to make the connection shared across multiple requests.
      • close_connection: Whether to close the connection after each request.
      postgresql_source: PostgreSQL source
      • db: Source database name.
      • table: Source table name.
      • port: Port for connecting to the source.
      • user: Name of source database user.
      • password: Password to access the source database.
      • ssl_mode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. Acceptable values: DISABLE, ALLOW, PREFER, VERIFY_CA, and VERIFY_FULL.
      • hosts: Names of the PostgreSQL master host and its replicas that will be used as dictionary sources. The hosts must be in the same network as the ClickHouse® cluster.
      • invalidate_query: SQL query to check for changes in a dictionary. ClickHouse® will update the dictionary only if the result of this query changes.
      http_source: HTTP(s) source
      • url: HTTP(s) source URL.
      • format: File format for the HTTP(s) source. Read more about formats in the ClickHouse® documentation.
      • headers: Special HTTP headers for the request to the source:
        • name: Header name.
        • value: Header value.

    • layout.type: Memory layout type for the dictionary. Supported methods: FLAT, HASHED, CACHE, RANGE_HASHED, COMPLEX_KEY_HASHED, and COMPLEX_KEY_CACHE. For more information about how to store dictionaries in memory, see the ClickHouse® documentation.

    • layout.size_in_cells: Number of cache cells for the CACHE and COMPLEX_KEY_CACHE methods. For more information about the cache, see the ClickHouse® documentation.

    • layout.max_array_size: Maximum key value for the FLAT method. Determines the memory size used by the dictionary, this size being proportional to the biggest key value. For more information about the key value, see the ClickHouse® documentation.

    • structure.id.name: Dictionary key column name. The key column must be the UInt64 data type. It is used for the FLAT, HASHED, CACHE, and RANGE_HASHED methods. For more information about keys, see the ClickHouse® documentation.

    • structure.key.attributes: Description of the dictionary's composite key. A composite key may consist of one or more elements. It is used for the COMPLEX_KEY_HASHED and COMPLEX_KEY_CACHE methods:

      • name: Column name.
      • type: Column data type.
      • null_value: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
      • expression: Expression ClickHouse® applies to the column value.
      • hierarchical: Hierarchical support flag.
      • injective: Injective id → attribute mapping flag.

      For more information about composite key parameters, see the ClickHouse® documentation.

      Warning

      The structure.id.name and structure.key.attributes fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

    • structure.attributes: Description of the fields available for database queries.

      • name: Column name.
      • type: Column data type.
      • null_value: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot put NULL in this field.
      • expression: Expression ClickHouse® applies to the column value.
      • hierarchical: Hierarchical support flag.
      • injective: Injective id → attribute mapping flag.

    • fixed_lifetime: Fixed interval between dictionary updates in seconds.

    • lifetime_range: Time range for ClickHouse® to randomly select the time for update. This is necessary for distributing the load on the dictionary source when upgrading on a large number of servers. The settings for the range boundaries are as follows:

      • min: Minimum interval between dictionary updates in seconds.
      • max: Maximum interval between dictionary updates in seconds.

      Warning

      The fixed_lifetime and lifetime_range fields are mutually exclusive, i.e., the use of one makes it impossible to use the other.

Examples

Let's assume there is a ClickHouse® cluster named mych with the cat0adul1fj0******** ID, and you need to connect to it a dictionary with the following test characteristics:

  • Dictionary name: mychdict.
  • Key column name: id.
  • Fields available for database queries:
    • id, UInt64 type.
    • field1, String type.
  • Fixed interval between dictionary updates: 300 seconds.
  • Memory layout type for the dictionary: cache with cache size of 1024 cells.
  • PostgreSQL source:
    • Database: db1.
    • Table name: table1.
    • Port for connection: 6432.
    • Database user name: user1.
    • Password for access to the database: user1user1.
    • Mode of secure SSL TCP/IP connection to the database: verify-full.
    • Master host's special FQDN: c-c9qash3nb1v9********.rw.mdb.yandexcloud.net.

Run the following command:

yc managed-clickhouse cluster add-external-dictionary \
   --name=mych \
   --dict-name=mychdict \
   --structure-id=id \
   --structure-attribute name=id,`
                        `type=UInt64,`
                        `name=field1,`
                        `type=String \
   --fixed-lifetime=300 \
   --layout-type=cache \
   --layout-size-in-cells 1024 \
   --postgresql-source db=db1,`
                      `table=table1,`
                      `port=6432,`
                      `user=user1,`
                      `password=user1user1,`
                      `ssl-mode=verify-full \
   --postgresql-source-hosts=c-c9qash3nb1v9********.rw.mdb.yandexcloud.net

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Create a file named body.json and add the following contents to it:

    {
  "externalDictionary": {
    "name": "mychdict",
    "structure": {
      "id": {
        "name": "id"
      },
      "attributes": [
        {
          "name": "id",
          "type": "UInt64"
        },
        {
          "name": "field1",
          "type": "String"
        }
      ]
    },
    "layout": {
      "type": "CACHE",
      "sizeInCells": "1024"
    },
    "fixedLifetime": "300",
    "postgresqlSource": {
      "db": "db1",
      "table": "table",
      "port": "5432",
      "user": "user1",
      "password": "user1user1",
      "sslMode": "VERIFY_FULL",
      "hosts": ["c-c9qash3nb1v9********.rw.mdb.yandexcloud.net"]
    }
  }
}

  3. Run a query using cURL:

    curl \
    --request POST \
    --header "Authorization: Bearer $IAM_TOKEN" \
    --header "Content-Type: application/json" \
    --url 'https://mdb.api.cloud.yandex.net/managed-clickhouse/v1/clusters/cat0adul1fj0********:createExternalDictionary' \
    --data '@body.json'

  1. Get an IAM token for API authentication and put it into the environment variable:

    export IAM_TOKEN="<IAM_token>"

  2. Clone the cloudapi repository:

    cd ~/ && git clone --depth=1 https://github.com/yandex-cloud/cloudapi

    Below, we assume the repository contents are stored in the ~/cloudapi/ directory.

  3. Create a file named body.json and add the following contents to it:

    {
  "cluster_id": "cat0adul1fj0********",
  "external_dictionary": {
    "name": "mychdict",
    "structure": {
      "id": {
        "name": "id"
      },
      "attributes": [
        {
          "name": "id",
          "type": "UInt64"
        },
        {
          "name": "field1",
          "type": "String"
        }
      ]
    },
    "layout": {
      "type": "CACHE",
      "size_in_cells": "1024"
    },
    "fixed_lifetime": "300",
    "postgresql_source": {
      "db": "db1",
      "table": "table",
      "port": "5432",
      "user": "user1",
      "password": "user1user1",
      "ssl_mode": "VERIFY_FULL",
      "hosts": ["c-c9qash3nb1v9********.rw.mdb.yandexcloud.net"]
    }
  }
}

  4. Run a query using gRPCurl:

    grpcurl \
    -format json \
    -import-path ~/cloudapi/ \
    -import-path ~/cloudapi/third_party/googleapis/ \
    -proto ~/cloudapi/yandex/cloud/mdb/clickhouse/v1/cluster_service.proto \
    -rpc-header "Authorization: Bearer $IAM_TOKEN" \
    -d @ \
    mdb.api.cloud.yandex.net:443 \
    yandex.cloud.mdb.clickhouse.v1.ClusterService.CreateExternalDictionary \
    < body.json

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Adding your own geobase
Next
Managing data format schemas