Connecting external dictionaries in Managed Service for ClickHouse®

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

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

1. See the 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>
   

You can find the connected dictionaries in the dictionaries: section of the command output.

1. Get an IAM token for API authentication and save it as an 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 request the cluster ID with the list of clusters in the folder.

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

1. Get an IAM token for API authentication and save it as an 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 the list of clusters in the folder.

4. View the server response to make sure your 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.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To create an external dictionary in a ClickHouse® cluster:

1. See the description of the CLI command for adding dictionaries:

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

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

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

To create an external dictionary in a ClickHouse® cluster:

1. Get an IAM token for API authentication and save it as an 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 paste the following code into it:

      {
        "externalDictionary": {
          "name": "<dictionary_name>",
          "structure": {
            "id": {
              "name": "<dictionary_key_column_name>"
            },
            "key": {
              "attributes": [
                <dictionary_data_column_array>
              ]
            },
            "rangeMin": {<RANGE_HASHED_initial_column>},
            "rangeMax": {<RANGE_HASHED_final_column>},
            "attributes": [
               <field_description_array>
            ]
          },
          "layout": {<memory_layout>},
          "fixedLifetime": "<fixed_update_interval>",
          "lifetimeRange": {<range_for_selecting_update_interval>},
          "httpSource": {<HTTP(s)_source_settings>},
          "mysqlSource": {<MySQL®>_source_settings},
          "clickhouseSource": {<ClickHouse®>_source_settings},
          "mongodbSource": {<Yandex_StoreDoc_source_settings>},
          "postgresqlSource": {<PostgreSQL>_source_settings}
        }
      }
      

      Where:

      • externalDictionary.name: Dictionary name.

      • externalDictionary.structure: Dictionary structure:

        • id.name: Dictionary key column name.
        • key.attributes: Array describing the dictionary's composite key.
        • rangeMin: Description of the initial column required when using the RANGE_HASHED memory layout.
        • rangeMax: Description of the final column required when using the RANGE_HASHED memory layout.
        • attributes: Array describing 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 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 helps distribute the dictionary source load when updating across many 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 following sources and specify its settings:

        • httpSource: HTTP(s) source.
        • mysqlSource: MySQL® source.
        • clickhouseSource: ClickHouse® source.
        • mongodbSource: Yandex StoreDoc 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 the list of clusters in the folder.

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

To create an external dictionary in a ClickHouse® cluster:

1. Get an IAM token for API authentication and save it as an 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 paste the following code into 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_initial_column>},
            "range_max": {<RANGE_HASHED_final_column>},
            "attributes": [<field_description_array>]
          },
          "layout": {<memory_layout>},
          "fixed_lifetime": "<fixed_update_interval>",
          "lifetime_range": {<range_for_selecting_update_interval>},
          "http_source": {<HTTP(s)_source_settings>},
          "mysql_source": {<MySQL®>_source_settings},
          "clickhouse_source": {<ClickHouse®>_source_settings},
          "mongodb_source": {<Yandex_StoreDoc_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 describing columns with dictionary data.
        • range_min: Description of the initial column required when using the RANGE_HASHED memory layout.
        • range_max: Description of the final column required when using the RANGE_HASHED memory layout.
        • attributes: Array describing 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 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 helps distribute the dictionary source load when updating across many 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 following sources and specify its settings:

        • http_source: HTTP(s) source.
        • mysql_source: MySQL® source.
        • clickhouse_source: ClickHouse® source.
        • mongodb_source: Yandex StoreDoc 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 the list of clusters in the folder.

   2. Run this query:

      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 your request was successful.

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

2. Run this DDL query:

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

   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 interval.
   • LAYOUT: Memory layout for the dictionary. The supported layout types include:
     • flat
     • hashed
     • cache
     • range_hashed
     • complex_key_hashed
     • complex_key_cache

For more information about the settings, see this ClickHouse® article.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To update an external dictionary in a ClickHouse® cluster, do the following:

1. View the description of the CLI command for updating dictionaries:

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

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

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

1. Get an IAM token for API authentication and save it as an 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 paste the following code into it:

      {
        "externalDictionary": {
          "name": "<dictionary_name>",
          "structure": {
            "id": {
              "name": "<dictionary_key_column_name>"
            },
            "key": {
              "attributes": [
                <dictionary_data_column_array>
              ]
            },
            "rangeMin": {<RANGE_HASHED_initial_column>},
            "rangeMax": {<RANGE_HASHED_final_column>},
            "attributes": [
               <field_description_array>
            ]
          },
          "layout": {<memory_layout>},
          "fixedLifetime": "<fixed_update_interval>",
          "lifetimeRange": {<range_for_selecting_update_interval>},
          "httpSource": {<HTTP(s)_source_settings>},
          "mysqlSource": {<MySQL®>_source_settings},
          "clickhouseSource": {<ClickHouse®>_source_settings},
          "mongodbSource": {<Yandex_StoreDoc_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 describing the dictionary's composite key.
        • rangeMin: Description of the initial column required when using the RANGE_HASHED memory layout.
        • rangeMax: Description of the final column required when using the RANGE_HASHED memory layout.
        • attributes: Array describing 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 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 helps distribute the dictionary source load when updating across many 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 following sources and specify its settings:

        • httpSource: HTTP(s) source.
        • mysqlSource: MySQL® source.
        • clickhouseSource: ClickHouse® source.
        • mongodbSource: Yandex StoreDoc 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 the list of clusters in the folder.

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

1. Get an IAM token for API authentication and save it as an 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 paste the following code into 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_initial_column>},
            "range_max": {<RANGE_HASHED_final_column>},
            "attributes": [<field_description_array>]
          },
          "layout": {<memory_layout>},
          "fixed_lifetime": "<fixed_update_interval>",
          "lifetime_range": {<range_for_selecting_update_interval>},
          "http_source": {<HTTP(s)_source_settings>},
          "mysql_source": {<MySQL®>_source_settings},
          "clickhouse_source": {<ClickHouse®>_source_settings},
          "mongodb_source": {<Yandex_StoreDoc_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 describing columns with dictionary data.
        • range_min: Description of the initial column required when using the RANGE_HASHED memory layout.
        • range_max: Description of the final column required when using the RANGE_HASHED memory layout.
        • attributes: Array describing 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 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 helps distribute the dictionary source load when updating across many 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 following sources and specify its settings:

        • http_source: HTTP(s) source.
        • mysql_source: MySQL® source.
        • clickhouse_source: ClickHouse® source.
        • mongodb_source: Yandex StoreDoc 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 the list of clusters in the folder.

   2. Run this query:

      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 your request was successful.

If you do not have the Yandex Cloud CLI installed yet, install and initialize it.

By default, the CLI uses the folder specified when creating the profile. To change the default folder, use the yc config set folder-id <folder_ID> command. You can also set a different folder for any specific command using the --folder-name or --folder-id parameter.

To delete an external dictionary:

1. See the description of the CLI command for deleting a dictionary:

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

2. Run this command to delete 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 save it as an 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 request the cluster ID with the list of clusters in the folder.

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

1. Get an IAM token for API authentication and save it as an 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 the list of clusters in the folder.

4. View the server response to make sure your 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.

• --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: Source database username.

  • password: Password to access the source database.

  • table: Source table name.

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

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

  --mongodb-source: Yandex StoreDoc 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: Source database username.
  • password: Password to access the source database.
  • connection: Source collection name.
  --mysql-source: MySQL® source

  • db: Source database name.
  • user: Source database username.
  • password: Password to access the source database.
  • table: Source table name.
  • where: Condition for selecting rows to build a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL clause.
  • share-connection: Determines whether to share the connection between multiple queries.
  • close-connection: Determines whether to close the connection after each query.
  --postgresql-source: PostgreSQL source

  • table: Source table name.
  • ssl-mode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. You can set it to disable, allow, prefer, verify-ca, or 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 this ClickHouse® article.

• --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. When attempting to connect, ClickHouse® follows the replica priority order. The lower the number, the higher the priority.
  • port: Port for connecting to the replica.
  • user: DB username.
  • password: Password to access the database.

• --mysql-invalidate-query: Query to check for MySQL® dictionary changes. ClickHouse® will update 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 to check for PostgreSQL dictionary changes. ClickHouse® will update the dictionary only if the results of this query change.

• --layout-type: Memory layout for the dictionary. The supported layout types include flat, hashed, complex_key_hashed, range_hashed, cache, complex_key_cache, sparse_hashed, complex_key_sparse_hashed, complex_key_range_hashed, direct, complex_key_direct, and ip_trie. For more information about dictionary layouts, see the this ClickHouse® article.

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

• --layout-allow-read-expired-keys: Determines whether to allow reading expired keys. This setting is used for the cache and complex_key_cache layouts. For more information, see this ClickHouse® article.

• Settings of the update queue for cache update issues, if keys are not found in the dictionary. The settings are used for the cache and complex_key_cache layouts.

  • --layout-max-update-queue-size: Maximum number of update issues per queue. The default value is 100000.
  • --layout-update-queue-push-timeout-milliseconds: Maximum update issue queuing timeout, in milliseconds. The default value is 10.
  • --layout-query-wait-timeout-milliseconds: Maximum update issue completion timeout, in milliseconds. The default value is 60000 (one minute).
  • --layout-max-threads-for-updates: Maximum number of threads for a cache dictionary update. The default value is 4.

  For more information, see this ClickHouse® article.

• Flat array size settings. They are used for the flat layout.

  • --layout-initial-array-size: Initial dictionary key size. The default value is 1024.
  • --layout-max-array-size: Maximum dictionary key size. It determines the memory size used by the dictionary, this size being proportional to the largest key value. The default value is 500000.

  For more information, see this ClickHouse® article.

• --layout-access-to-key-from-attributes: Gets the name of the composite key using the dictGetString function. This setting is used for the ip_trie layout. With this setting enabled, you get higher load on RAM.

• --structure-id: Dictionary key column name. The key column must have the UInt64 data type. This setting is used for the flat, hashed, range_hashed, cache, sparse_hashed, and direct layouts: For more information about keys, see this ClickHouse® article.

• --structure-key: Description of the dictionary's composite key. The key may consist of one or more elements. This setting is used for the complex_key_* and ip_trie layouts.

  • 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, so using one means you cannot 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 helps distribute the dictionary source load when updating across many 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, so using one means you cannot 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 build a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL clause.

    • 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: Source database username.

    • password: Password to access the source database.

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

    mongodbSource: Yandex StoreDoc 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: Source database username.
    • password: Password to access the source database.
    • collection: Source collection name.
    mysqlSource: MySQL® source

    • db: Source database name.
    • table: Source table name.
    • where: Condition for selecting rows to build a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL clause.
    • user: Source database username.
    • password: Password to access the source database.
    • replicas: Source replica settings:
      • host: Replica host name. The host must be in the same network as the ClickHouse® cluster.
      • priority: Replica priority. When attempting to connect, ClickHouse® follows the replica priority order. The lower the number, the higher the priority.
      • port: Port for connecting to the replica.
      • user: DB username.
      • password: Password to access the database.
    • invalidateQuery: Query to check for MySQL® dictionary changes. ClickHouse® will update the dictionary only if the results of this query change.
    • shareConnection: Determines whether to share the connection between multiple queries.
    • closeConnection: Determines whether to close the connection after each query.
    postgresqlSource: PostgreSQL source

    • db: Source database name.
    • table: Source table name.
    • port: Port for connecting to the source.
    • user: Source database username.
    • password: Password to access the source database.
    • sslMode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. You can set it to DISABLE, ALLOW, PREFER, VERIFY_CA, or 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: Query to check for dictionary changes. ClickHouse® will update the dictionary only if the results of this query change.
    httpSource: HTTP(s) source

    • url: HTTP(s) source URL.
    • format: File format for the HTTP(s) source. Read more about formats in this ClickHouse® article.
    • headers: Special HTTP headers for the request to the source:
      • name: Header name.
      • value: Header value.

  • layout.type: Memory layout for the dictionary. The supported layout types include FLAT, HASHED, COMPLEX_KEY_HASHED, RANGE_HASHED, CACHE, COMPLEX_KEY_CACHE, SPARSE_HASHED, COMPLEX_KEY_SPARSE_HASHED, COMPLEX_KEY_RANGE_HASHED, DIRECT, COMPLEX_KEY_DIRECT, and IP_TRIE. For more information about dictionary layouts, see the this ClickHouse® article.

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

  • layout.allowReadExpiredKeys: Determines whether to allow reading expired keys. This setting is used for the CACHE and COMPLEX_KEY_CACHE layouts. For more information, see this ClickHouse® article.

  • Settings of the update queue for cache update issues, if keys are not found in the dictionary. They are used for the CACHE and COMPLEX_KEY_CACHE layouts.

    • layout.maxUpdateQueueSize: Maximum number of update issues per queue. The default value is 100000.
    • layout.updateQueuePushTimeoutMilliseconds: Maximum update issue queuing timeout, in milliseconds. The default value is 10.
    • layout.queryWaitTimeoutMilliseconds: Maximum update issue completion timeout, in milliseconds. The default value is 60000 (one minute).
    • layout.maxThreadsForUpdates: Maximum number of threads for a cache dictionary update. The default value is 4.

    For more information, see this ClickHouse® article.

  • Flat array size settings. They are used for the FLAT layout.

    • layout.initialArraySize: Initial dictionary key size. The default value is 1024.
    • layout.maxArraySize: Maximum dictionary key size. It determines the memory size used by the dictionary, this size being proportional to the largest key value. The default value is 500000.

    For more information, see this ClickHouse® article.

  • layout.accessToKeyFromAttributes: Gets the name of the composite key using the dictGetString function. This setting is used for the IP_TRIE layout. With this setting enabled, you get higher load on RAM.

  • structure.id.name: Dictionary key column name. The key column must have the UInt64 data type. This setting is used for the FLAT, HASHED, RANGE_HASHED, CACHE, SPARSE_HASHED, and DIRECT layouts: For more information about keys, see the this ClickHouse® article.

  • structure.key.attributes: Description of the dictionary's composite key. The key may consist of one or more elements. This setting is used for the COMPLEX_KEY_* and IP_TRIE layouts.

    • 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 settings, see this ClickHouse® article.

    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 helps distribute the dictionary source load when updating across many servers. To specify the range boundaries, use these settings:

    • 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 build a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL clause.

    • 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: Source database username.

    • password: Password to access the source database.

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

    mongodb_source: Yandex StoreDoc 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: Source database username.
    • password: Password to access the source database.
    • collection: Source collection name.
    mysql_source: MySQL® source

    • db: Source database name.
    • table: Source table name.
    • where: Condition for selecting rows to build a dictionary from. For example, the id=10 selection condition is the same as the WHERE id=10 SQL clause.
    • user: Source database username.
    • password: Password to access the source database.
    • replicas: Source replica settings:
      • host: Replica host name. The host must be in the same network as the ClickHouse® cluster.
      • priority: Replica priority. When attempting to connect, ClickHouse® follows the replica priority order. The lower the number, the higher the priority.
      • port: Port for connecting to the replica.
      • user: DB username.
      • password: Password to access the database.
    • invalidate_query: Query to check for MySQL® dictionary changes. ClickHouse® will update the dictionary only if the results of this query change.
    • share_connection: Determines whether to share the connection between multiple queries.
    • close_connection: Determines whether to close the connection after each query.
    postgresql_source: PostgreSQL source

    • db: Source database name.
    • table: Source table name.
    • port: Port for connecting to the source.
    • user: Source database username.
    • password: Password to access the source database.
    • ssl_mode: Mode of secure SSL TCP/IP connection to the PostgreSQL database. You can set it to DISABLE, ALLOW, PREFER, VERIFY_CA, or 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: Query to check for dictionary changes. ClickHouse® will update the dictionary only if the results of this query change.
    http_source: HTTP(s) source

    • url: HTTP(s) source URL.
    • format: File format for the HTTP(s) source. Read more about formats in this ClickHouse® article.
    • headers: Special HTTP headers for the request to the source:
      • name: Header name.
      • value: Header value.

  • layout.type: Memory layout for the dictionary. The supported layout types include FLAT, HASHED, COMPLEX_KEY_HASHED, RANGE_HASHED, CACHE, COMPLEX_KEY_CACHE, SPARSE_HASHED, COMPLEX_KEY_SPARSE_HASHED, COMPLEX_KEY_RANGE_HASHED, DIRECT, COMPLEX_KEY_DIRECT, and IP_TRIE. For more information about dictionary layouts, see the this ClickHouse® article.

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

  • layout.allow_read_expired_keys: Determines whether to allow reading expired keys. This setting is used for the CACHE and COMPLEX_KEY_CACHE layouts. For more information, see this ClickHouse® article.

  • Settings of the update queue for cache update issues, if keys are not found in the dictionary. The settings are used for the CACHE and COMPLEX_KEY_CACHE layouts.

    • layout.max_update_queue_size: Maximum number of update issues per queue. The default value is 100000.
    • layout.update_queue_push_timeout_milliseconds: Maximum update issue queuing timeout, in milliseconds. The default value is 10.
    • layout.query_wait_timeout_milliseconds: Maximum update issue completion timeout, in milliseconds. The default value is 60000 (one minute).
    • layout.max_threads_for_updates: Maximum number of threads for a cache dictionary update. The default value is 4.

    For more information, see this ClickHouse® article.

  • Flat array size settings. They are used for the FLAT layout.

    • layout.initial_array_size: Initial dictionary key size. The default value is 1024.
    • layout.max_array_size: Maximum dictionary key size. It determines the memory size used by the dictionary, this size being proportional to the largest key value. The default value is 500000.

    For more information, see this ClickHouse® article.

  • layout.access_to_key_from_attributes: Gets the name of the composite key using the dictGetString function. This setting is used for the IP_TRIE layout. With this setting enabled, you get higher load on RAM.

  • structure.id.name: Dictionary key column name. The key column must have the UInt64 data type. This setting is used for the FLAT, HASHED, RANGE_HASHED, CACHE, SPARSE_HASHED, and DIRECT layouts: For more information about keys, see the this ClickHouse® article.

  • structure.key.attributes: Description of the dictionary's composite key. The key may consist of one or more elements. This setting is used for the COMPLEX_KEY_* and IP_TRIE layouts.

    • 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 settings, see this ClickHouse® article.

    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 helps distribute the dictionary source load when updating across many servers. To specify the range boundaries, use these settings:

    • 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.

1. Get an IAM token for API authentication and save it as an environment variable:

   export IAM_TOKEN="<IAM_token>"
   

2. Create a file named body.json and paste the following code into 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": "table1",
         "port": "5432",
         "user": "user1",
         "password": "user1user1",
         "sslMode": "VERIFY_FULL",
         "hosts": ["c-c9qash3nb1v9********.rw.mdb.yandexcloud.net"]
       }
     }
   }
   

3. Run this request 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 save it as an 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 paste the following code into 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": "table1",
         "port": "5432",
         "user": "user1",
         "password": "user1user1",
         "ssl_mode": "VERIFY_FULL",
         "hosts": ["c-c9qash3nb1v9********.rw.mdb.yandexcloud.net"]
       }
     }
   }
   

4. Run this request 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
   

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

2. Run this DDL query:

   CREATE DICTIONARY mychdict(
     `id` UInt64,
     `field1` String
   )
   PRIMARY KEY id
   SOURCE(POSTGRESQL(
      port 5432
      host 'c-c9qash3nb1v9********.rw.mdb.yandexcloud.net'
      user 'user1'
      password 'user1user1'
      db 'db1'
      table 'table1'
   ))
   LIFETIME(300)
   LAYOUT(CASHE(SIZE_IN_CELLS 1024));