Contact UsGet started

Connecting external dictionaries in Managed Service for ClickHouse®

Written by
Updated at October 17, 2024

You can add external dictionaries to your cluster and remove them. For more information about dictionaries, see 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 or via cloud interfaces. We recommend SQL.

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 command execution results.

To get a list of dictionaries, use the get REST API method for the Cluster resource or the ClusterService/Get gRPC API call.

  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 dictionary settings and 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 a dictionary, use the createExternalDictionary REST API method for the Cluster resource or the ClusterService/CreateExternalDictionary gRPC API call.

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. Run the DDL query and specify dictionary settings:

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

Removing 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> \

To delete a dictionary, use the deleteExternalDictionary REST API method for the Cluster resource or the ClusterService/DeleteExternalDictionary gRPC API call.

  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

SQL

  • <dictionary_name>: Name of new dictionary.
  • <data_columns>: List of columns with dictionary data and their type.
  • PRIMARY KEY: Dictionary key column name.
  • SOURCE: Source and its parameters.
  • LIFETIME: Dictionary update frequency.
  • LAYOUT: Method for storing dictionary in memory. The supported methods are flat, hashed, cache, range_hashed, complex_key_hashed, and complex_key_cache.

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

Management console

  • Name: Name of a new dictionary.

Source

  • 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.
  • 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 dictionary changes. ClickHouse® updates the dictionary only if there are changes in the query results.
    For more information, see the ClickHouse® documentation.
  • 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.
  • Database: Source database name.
  • Collection: Name of the collection for the source.
  • Replicas: List of MySQL® replicas to use 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 dictionary changes. ClickHouse® updates the dictionary only if there are changes in the query results.
    For more information, see the ClickHouse® documentation.
  • Hosts: Names of a PostgreSQL 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 dictionary changes. ClickHouse® updates the dictionary only if there are changes in the query results.
    For more information, see the ClickHouse® documentation.
  • SSL mode: Mode for establishing a secure SSL TCP/IP connection to the PostgreSQL database.
    For more information, see the PostgreSQL documentation.

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

Layout

  • Memory storage: Dictionary layout. The supported layouts are flat, hashed, cache, range_hashed, complex_key_hashed, and complex_key_cache. For more information about layouts, see the ClickHouse® documentation.

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

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

  • Data column: List of columns with dictionary data:

    • 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 use NULL in this field.
    • (Optional) Expression: Expression that ClickHouse® executes with the column value.
    • Hierarchical: Hierarchical support flag.
    • Injective: Injective id → attribute mapping flag.

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

Update rate

  • Period: Set how often the dictionary updates:

    • 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 dictionary updates, see the ClickHouse® documentation.

CLI

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

  • --clickhouse-source: Settings of a ClickHouse® source:

    • db: Source database name.
    • table: Source table 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.
    • 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.
  • --http-source-url: HTTP(s) source URL.
  • --http-source-format: File format for an HTTP(s) source. For more information about the formats, see the ClickHouse® documentation.

  • --mongodb-source: Settings of a MongoDB source:

    • db: Source database name.
    • connection: Name of the collection for the 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.
    • user: Name of source database user.
    • password: Password to access the source database.

  • --mysql-source: Settings of a MySQL® 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.
    • 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.

  • --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: Name of database user.
    • password: Password to access the database.

  • --mysql-invalidate-query: Query to check for dictionary changes. ClickHouse® updates the dictionary only if there are changes in the query results.

  • --postgresql-source: Settings of a 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 for establishing a secure SSL TCP/IP connection to the PostgreSQL database. Acceptable values: disable, allow, prefer, verify-ca, or verify-full.

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

  • --postgresql-invalidate-query: Query to check for dictionary changes. ClickHouse® updates the dictionary only if there are changes in the query results.

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

  • --structure-key: List of columns with dictionary data:

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

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

    • name: Column name.
    • type: Type of column data.
    • null-value: Default value for an empty element. When loading a dictionary, all empty elements are replaced with this value. You cannot use NULL in this field.
    • expression: Expression that ClickHouse® executes with 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.

  • --layout-type: Method of storing dictionary in memory. The supported layouts are flat, hashed, cache, range_hashed, complex_key_hashed, and complex_key_cache. For more information about layouts, 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.

API

For more information about the settings, see the description of the createExternalDictionary API method.

Examples

Add a dictionary with test characteristics:

  • Cluster: mych.

  • Name: mychdict.

  • Key column name: id.

  • Fields available for database queries:

    • id of the UInt64 type.
    • field1 of the String type.

  • Fixed period between dictionary updates 300 seconds.

  • Layout: cache.

  • PostgreSQL source:

    • Database: db1.
    • Table name: table.
    • Connection port: 5432.
    • Database user name: user1.
    • Password for access to the database: user1user1.
    • verify-full mode for establishing a secure SSL TCP/IP connection to the database.

  • Host name: rc1b-05vjbfhf********.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 \
   --postgresql-source db=db1,`
                      `table=table,`
                      `port=5432,`
                      `user=user1,`
                      `password=user1user1,`
                      `ssl-mode=verify-full \
   --postgresql-source-hosts=rc1b-05vjbfhf********.mdb.yandexcloud.net

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Adding your own geobase
Next
Managing data format schemas