ClickHouse History Migration Guide

Step 1. Install ClickHouse DBMS following the official instructions.

note

The minimum version required for the platform is >24.8.

Step 2. Create a new user in /etc/clickhouse-server/users.d/altcraft.xml (see the documentation for details — User settings).

<yandex>
    <users>
        <altcraft>
            <password>abcdefghiasdwqedwqjklmnopqrstuvwxyzABCDEF</password>
            <networks>
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
                <database>altcraft_system</database>
            </allow_databases>
        </altcraft>
    </users>
</yandex>

caution

The password in this example is the string "abcdefghiasdwqedwqjklmnopqrstuvwxyzABCDEF".
Make sure to generate a stronger password of at least 32 characters.

Step 3. Enable the input_format_import_nested_json option to insert JSON data with nested objects (see the documentation for details — Settings profiles).

<?xml version="1.0"?>
<yandex>
    <!-- Profiles of settings. -->
    <profiles>
        <!-- Default settings. -->
        <default>
            <!-- Maximum memory usage for processing single query, in bytes. -->
            <max_memory_usage>8589934592</max_memory_usage>
            <max_memory_usage_for_user>8589934592</max_memory_usage_for_user>
            <max_bytes_before_external_group_by>4294967296</max_bytes_before_external_group_by>
            <max_bytes_before_external_sort>4294967296</max_bytes_before_external_sort>
            <receive_timeout>3600</receive_timeout>
            <send_timeout>3600</send_timeout>
            <max_threads>2</max_threads>
            <max_partitions_per_insert_block>10000</max_partitions_per_insert_block>
            <!-- Use cache of uncompressed blocks of data. Meaningful only for processing many of very short queries. -->
            <use_uncompressed_cache>0</use_uncompressed_cache>

            <!-- How to choose between replicas during distributed query processing.
                 random - choose random replica from set of replicas with minimum number of errors
                 nearest_hostname - from set of replicas with minimum number of errors, choose replica
                  with minimum number of different symbols between replica's hostname and local hostname
                  (Hamming distance).
                 in_order - first live replica is chosen in specified order.
                 first_or_random - if first replica one has higher number of errors, pick a random one from replicas with minimum number of errors.
            -->
            <load_balancing>random</load_balancing>
            <input_format_import_nested_json>1</input_format_import_nested_json>
        </default>

Step 4. In the main main.json configuration file, set the parameters for connecting to and working with the ClickHouse DBMS.

"CLICKHOUSE_SYSTEM": {
    "HOST": "localhost",
    "PORT": 9000,
    "USER": "altcraft",
    "MAX_CONNECTION": 100,
    "CONN_TIMEOUT": 15,
    "PASSWORD": "abcdefghiasdwqedwqjklmnopqrstuvwxyzABCDEF",
    "IS_DEBUG": false
}, 

Step 5. Run the migration. A dedicated utility, actions_migrate, is provided for this purpose. The full list of available parameters:

Parameter	Default	Description
--accounts string	All active accounts	List of accounts to import, comma-separated Example: "1, 2, 3"
--appname bool	false	Show additional information about the utility
--batch_timeout int	5	Time in seconds after which the insert is executed
--ch_batch int	100000	Minimum batch of events for insertion
--ch_debug bool	false	Enable an additional logging level
--ch_host string	Taken from the main main.json configuration	Host to connect to
--ch_password string	Taken from the main main.json configuration	Password for connection
--ch_port int	Taken from the main main.json configuration	Port for connection
--ch_user string	Taken from the main main.json configuration	User for connection
--config string	/opt/MarketingPlatform/config/main.json	Path to the main main.json configuration file
--exclude string	""	List of accounts to exclude from the import, comma-separated Example: "1, 2, 3"
--level_log string	INFO	Logging level: TRACE, DEBUG, INFO, WARN, ERROR, CRITICAL, FATAL
--log_path string	./actions_migrate.log	Log file location
--range string	""	Time range for selecting existing history Format: "MM.YY-MM.YY", "-MM.YY", "MM.YY-"
--rmq bool	false	Additional option for insertion via RabbitMQ
--version bool	false	Show the utility version
--worker_size int	-1	Number of workers (defaults to the number of vCPUs).

tip

Migration may take a long time, up to 3 days or more. To continue the migration after disconnecting from the server, you can use the screen utility to work in a separate session; see a screen usage example at https://access.redhat.com/articles/5247.

Example of migrating all historical data through September 2021:

    
/opt/akd/ak/bin/actions_migrate --accounts "1" --config "/opt/akd/config/main.json" --batch_timeout 60 --ch_batch 500000 --range "-09.21"

Example of migrating all historical data for November 2021:

    
/opt/akd/ak/bin/actions_migrate --accounts "1" --config "/opt/akd/config/main.json" --batch_timeout 60 --ch_batch 500000 --range "11.21-11.21"

Example of migrating all historical data from January 2021 to the present:

    
/opt/akd/ak/bin/actions_migrate --accounts "1" --config "/opt/akd/config/main.json" --batch_timeout 60 --ch_batch 500000 --range "01.21-"