You need to enable JavaScript to run this app.
ByteHouse Cloud Data Warehouse Version

ByteHouse Cloud Data Warehouse Version

  • Documentation
    • ByteHouse Cloud Data Warehouse VersionUser guideData import/exportStation Relocation ToolMigrate ClickHouse to ByteHouse
Copy page
Download PDF
Station Relocation Tool
Migrate ClickHouse to ByteHouse
Copy page
Download PDF
Migrate ClickHouse to ByteHouse

ByteHouse Cloud Data Warehouse supports using script tools to rapidly migrate your data stored in ClickHouse to ByteHouse Cloud Data Warehouse offline, enabling immediate use and facilitating unified data management and development. This article provides a detailed introduction to the methods for migrating ClickHouse database tables and data to the ByteHouse cloud data warehouse.

Function overview

Migrating ClickHouse data to the ByteHouse cloud data warehouse depends on migration scripts provided by ByteHouse: the ClickHouse data export script and the data import script. During migration,

  1. Use the ClickHouse data export script to export data from ClickHouse to object storage.
  2. Use the data import script developed based on the ByteHouse S3 external table feature to complete the import of data from object storage.

Script name

Capability description

ByteHouse in-house ClickHouse data export script

  • Resumable transfer: Accurately records the import status, prevents re-execution of tasks that have already been successfully imported, and supports rollback operations in case of task failure, ensuring stability and continuity throughout the migration process.
  • Concurrent export: Supports concurrent export to improve export efficiency.
  • General export capabilities: Provides general export capabilities and adapts to various data scenarios.
  • Progress bar: The progress bar provides a visual indication of export progress.

A ByteHouse proprietary data import script

  • Resumable transfer: Accurately records the import status, does not repeat import tasks that have already been completed successfully, and supports rollback operations in case of task failure, ensuring stability and continuity throughout the migration process.
  • Concurrent execution: Supports concurrent processing for external table creation and SQL import, which effectively improves migration efficiency and shortens overall migration time.
  • Multi-table and multi-database support: Enables migration operations on multiple tables across multiple databases simultaneously, meeting the requirements of complex data environments.
  • Partition-filtered import for external tables: Supports setting partition filtering strategies for individual external tables, importing only data from partitions that meet the criteria to reduce unnecessary data processing.
  • External table storage format configuration: Supports customizing the storage format at the single-table level, allowing you to select an appropriate storage format based on actual business requirements.
  • The import retry capability: When an import task encounters an exception, you can retry automatically or manually to improve the task success rate.
  • Single table operation support: Supports insert into (insert) and insert overwrite (overwrite) operations at the single-table level, meeting different data import requirements.
  • dry-run feature: Allows scripts to be run in advance for checking and validation, ensuring script correctness and preventing migration failures caused by script issues.
  • An N-level partitioned external table: For Hive partition tables, you can configure granular partition rules for individual tables based on the Hive partition table schema. This allows flexible adaptation to various data partitioning scenarios.

Migration process

The process of migrating from ClickHouse to ByteHouse mainly includes the following three main steps. You can follow the detailed instructions below to migrate your data.

  1. Prepare the target table by creating the table required for migration in ByteHouse.
  2. Export data from ClickHouse to the object storage service, and check whether the data and directories in the object storage service meet expectations.
  3. Import data from the object storage service to ByteHouse and check the data consistency

Image

Usage restrictions

If your migration scenario meets any of the following conditions, please contact the ByteHouse team for assistance:

  • The size of a single table exceeds 1 billion rows;
  • The compute group specification is small (specification is M or lower), and the table size exceeds 100 million rows.

Cautions
  • During migration, the target database, source database, and object storage service must be able to communicate with each other, and the network environment must be stable.
    • Under normal circumstances, data transmission between ClickHouse, the object storage service, and ByteHouse must occur over the public network. Please ensure that the network environment is stable. If you have set up a dedicated line, you can switch to private network transmission. In this case, make sure that the dedicated line is fully configured and the network environment remains stable.
    • When transferring data from Volcengine Object Storage Service (TOS) to ByteHouse, you must ensure that TOS and the ByteHouse instance are located in the same region to maintain data transfer efficiency and avoid additional costs caused by cross-region access.
  • Using data services during the migration process may incur charges. Actual charges are determined by the product's billing.

Preparation
  1. Obtain the connection information for ClickHouse, the object storage service, and ByteHouse, including domain name, port number, account, password, Access Key ID (AK), Secret Access Key (SK), and more.
  2. Configure full read and write permissions for the object storage service for your ClickHouse and ByteHouse accounts.
  3. Confirm that you have configured the default write compute group in ByteHouse, which is used for data merge operations. Log in to the ByteHouse console, click the top Tenant management, and under the Parameter settings tab, set the default write compute group.
    If your ByteHouse compute group needs to configure specific compatibility configurations, please contact the ByteHouse team.
    Image
  4. If you have special requirements regarding time zones, you can contact the ByteHouse team to set the session timezone at the tenant level to the specified time zone. By default, the ByteHouse service you use on VolcEngine uses the GMT+8 time zone, while the ByteHouse service you use on AWS uses the UTC time zone.

Step 1: Prepare the target table

Please ensure that the ClickHouse source database or table is ready, and that the corresponding target database or table has been created in ByteHouse. For reference, see Database Management. ByteHouse also provides best practices for table creation. For more details, please refer to ByteHouse Table Creation Best Practices and Best practices for ByteHouse unique key tables.
If you need to create a large number of tables, you may submit a support ticket or contact the ByteHouse team.

Step 2: Export data from ClickHouse to object storage service

  1. Download the ByteHouse proprietary ClickHouse data export script required for migration to the object storage service. After downloading, store the script file in your custom path and make a note of this path, as you will need it for configuration when executing the script later.

    data-migrator-clickhouse-to-s3.py
    Unknown size

  2. In the same directory as the ClickHouse data export script, create and configure an ini file. In the example in this article, the ini file is named config-template-export.ini.
    Replace the placeholders or example parameter values in the example with the actual ClickHouse connection information, the target storage path for object storage, and the data migration parameter values to ensure that the script correctly performs the data export operation. For detailed configuration information, see the parameter description.

    The ClickHouse connection information
   Select ClickHouse host nodes by separating them with commas
   clickhouse_host=127.0.0.1
   clickhouse_port=9000
   clickhouse_user=default
   clickhouse_password={your_clickhouse_password}
   
   Object storage connection information
   s3_access_key={tos_access_key}
   s3_secret_key={tos_secret_access_key}
   s3_url=https://{bucket_name}.tos-s3-{region}.volces.com/{dir_name}
   
   Information about exported data tables
   database_name={database_name}
   table_names={table_name1},{table_name2},...
   ;field_rules=
   ;export_field_rules=
   
   Segmentation methods
   Partition-level separation parameters
   split_in_partition=1
   selected_partitions=
   # Global or partition split parameter; batch export can then be performed based on partition or entire table
   split_count=-1
   Multiple columns can be configured for hash partitioning, separated by commas. Columns present in the table are selected in the following order of priority: discrete columns in the orderby configuration, followed by columns of int or string type.
   split_column=
   
   Additional query conditions
   where=
   limit=
   
   Breakpoint resume capability, with control to prevent the SQL export from running again after a successful export
   Whether to clear the state. 1: Clean; default is 0 (do not clean)
   empty_status=0
   
   Additional parameters
   Maximum retry attempts
   max_retries=1
   A retry delay, in seconds (s)
   retry_delay_seconds=1
   # SQL settings
   It is currently recommended to configure some settings related to timeout. The unit is seconds (s).
   You can also configure s3_truncate_on_insert=true as needed, which means that if there are existing files, the system will delete them, overwriting the original files.
   s3_insert_settings=send_timeout=3600, receive_timeout=3600, max_execution_time=3600,output_format_json_quote_64bit_integers=0
   File formats and compression configuration
   file_format=Parquet
   data_compression_method=none
   ClickHouse table engines that need to be exported
   filter_tables_by_engine=MergeTree
   
   Concurrency parameters for controlling the concurrency of export SQL execution in ByteHouse
   parallelism=8

    Configure a key

    Type

    Description

    dryrun

    int

    The default value is 0. Setting it to 1 means that only the exported SQL statements are printed, without actually being executed.

    clickhouse_host

    string

    You can select multiple shared nodes by separating ClickHouse addresses with commas.

    clickhouse_port

    string

    The port for ClickHouse is configured as 9000.

    clickhouse_user

    string

    ClickHouse username.

    clickhouse_password

    string

    The ClickHouse password.

    s3_access_key

    string

    Access Key ID of the object storage service that stores exported files.
    If you are using Volcengine TOS, you can refer to Access Key (密钥) Management for information on how to obtain it.

    s3_secret_key

    string

    The Secret Access Key for the object storage service that stores exported files.
    If you are using Volcengine TOS, refer to Access Key (Secret Key) Management for the method of obtaining it.

    s3_url

    string

    Target storage directory for exported files in object storage. An example format is as follows:

    • Export to TOS:

      Public network
https://{bucket_name}.tos-s3-{region}.volces.com/some-dir
Private network
https://{bucket_name}.tos-s3-{region}.ivolces.com/some-dir

    • Export to other object storage services:

      # AWS S3
s3_url=https://{bucket_name}.s3.{region}.amazonaws.com/some-dir
# MinIO or other object storage
s3_url=http://{host}:{port}/{bucket_name}/some-dir

    database_name

    string

    Name of the ClickHouse database to export.

    table_names

    string

    Names of tables to be exported from the ClickHouse database. To export multiple tables, separate them with commas. If an asterisk (*) is used, it indicates that all tables in the database will be exported.

    field_rules

    string

    This is used for export preprocessing of special types of data. The current default value can be used; configuration is only necessary in special cases. If there are multiple parameters, use a semicolon to separate them.
    Default rule: For groupBitmap aggregate function types with different bit-widths, from AggregateFunction(groupBitmap, UInt8) to UInt256, bitmap data is consistently converted to array format using the bitmapToArray($Field$) function.
    The currently configured default values are as follows:
    AggregateFunction(groupBitmap, UInt8):bitmapToArray($Field$);AggregateFunction(groupBitmap, UInt16):bitmapToArray($Field$);AggregateFunction(groupBitmap, UInt32):bitmapToArray($Field$);AggregateFunction(groupBitmap, UInt64):bitmapToArray($Field$);AggregateFunction(groupBitmap, UInt128):bitmapToArray($Field$);AggregateFunction(groupBitmap, UInt256):bitmapToArray($Field$)

    export_field_rules

    string

    This is used to define the target type after exporting data of special types. The current default value can be used, and no configuration is required unless required for special cases. If there are multiple parameters, you can use a semicolon to separate them.
    Default rule: For different bit-widths of the groupBitmap aggregate function type (such as AggregateFunction(groupBitmap, UInt8) to UInt256), the exported data type is specified as an array of the corresponding bit-width (for example, Array(UInt8) to Array(UInt256)).
    The currently configured default values are as follows:
    AggregateFunction(groupBitmap, UInt8):Array(UInt8);AggregateFunction(groupBitmap, UInt16):Array(UInt16);AggregateFunction(groupBitmap, UInt32):Array(UInt32);AggregateFunction(groupBitmap, UInt64):Array(UInt64);AggregateFunction(groupBitmap, UInt128):Array(UInt128);AggregateFunction(groupBitmap, UInt256):Array(UInt256)

    split_in_partition

    int

    Set whether to export by partition. Can be set to 1 or 0.

    • The default setting is 1, and the system will split and export the data by partition.
    • When set to 0, the system exports the entire table instead of exporting by partition.

    selected_partitions

    string

    In most cases, this parameter does not need to be set.
    If some partitions fail to export, you can use this parameter to specify which partitions to export. When exporting multiple partitions, separate the partition names with commas.

    split_count

    int

    In general, it is recommended to set this parameter to -1, which indicates that no separation operation will be performed.
    If data export performance is poor, you can use this parameter to specify the number of files into which the exported table is split. Once enabled, it takes effect at both the table and partition levels and can be used to accelerate export.

    split_column

    string

    In most cases, this parameter does not need to be set.
    If data export performance is suboptimal, you can use this parameter to specify the column for data partitioning. When exporting large amounts of data, the script tool will use the hash values of columns to split the data into multiple parts, accelerating the export process. Multiple columns can be configured, with column names separated by commas. The system will select, in order, the data that exists in the table, according to the following priority:

    • Preferentially select discrete columns configured in Order By.
    • Priority 2: Next, select columns of type int or string;
    • Default column: If none of the above are present, id is selected by default.

    where

    string

    You can use this parameter to set filter conditions for a specific table.
    If you need to set different filter criteria for different tables, divide the tables according to table_names, generate a new configuration file for each table, and execute the export task for each configuration file.

    limit

    string

    You can use this parameter to configure restrictions for a table.
    If you need to set different filter criteria for different tables, categorize by table_names and generate a corresponding configuration file for each table, then execute the export task.

    empty_status

    int

    Resumable transfer capability switch, used to set whether to reference previous breakpoint information during export. The default value is 0, which means that previous breakpoint information will be referenced and resumable transfer will be enabled. When set to 1, the current export will not reference previous breakpoint information.

    max_retries

    int

    The maximum number of retry attempts after a failure is 10 by default.

    retry_delay_seconds

    int

    The delay between each retry. Default value: 5. Unit: seconds.

    file_format

    string

    The export file format defaults to Parquet, but you can also set it to JSONEachRow.

    data_compression_method

    string

    Compression method for exported files: set Parquet to none and JSONEachRow to gzip.

    s3_insert_settings

    string

    This setting is used to prevent export timeout. It is recommended to use the default value:

    send_timeout=3600, receive_timeout=3600, max_execution_time=3600,output_format_json_quote_64bit_integers=0,s3_truncate_on_insert=true

    The parameter descriptions are as follows:

    • send_timeout: Data transmission timeout setting. It is recommended to use the default value of 3600 (in seconds).
    • receive_timeout: Data reception timeout setting. It is recommended to use the default value of 3600 (unit: seconds).
    • max_execution_time: Maximum execution time for a single query. It is recommended to use the default value of 3600; the unit is seconds.
    • output_format_json_quote_64bit_integers: Controls whether ClickHouse encloses 64-bit integer values in double quotation marks when outputting in JSON format. When set to 0, output is not wrapped and is in numeric format; when set to 1, output is wrapped and is in string format. It is recommended to set the value to 0.
    • s3_truncate_on_insert=true: Whether to clear the object storage service directory during import. When set to true, the directory in the object storage service will be cleared each time an import is performed. It is recommended to set it to true.

    filter_tables_by_engine

    string

    Set the table engine for the ClickHouse tables to be exported. Supported engines include MergeTree and ReplacingMergeTree. The default value is ReplacingMergeTree.

    parallelism

    int

    Set the concurrent export parameter, which represents the number of concurrent operations. The default is 4.

  3. Run the Python script file to export data from ClickHouse to the object storage service.
    Before running, make sure that the current working directory is the directory where the script is located, or specify the script location using an absolute path.

    python3 data-migrator-clickhouse-to-s3.py config-template-export.ini

  4. After exporting data from ClickHouse, check whether the exported data and directories in object storage meet expectations.
    The data files exported using the steps above will be stored in object storage following the directory structure shown below. You can use the example path to check in your object storage service whether the directory and data meet your expectations. This example shows the path information for AWS S3. The paths for TOS and MinIO are similar.

    https://{bucket_name}.s3.{region}.amazonaws.com/ck-to-bytehouse/{database_name}/{table_name}/{database_name}_{table_name}_{partition_id}_{hash_num_order}.JSONEachRow.gz

Step 3: Import from object storage service into ByteHouse

Use the ByteHouse proprietary data import script to import exported data from the object storage service into ByteHouse. The ByteHouse self-developed import script utilizes the S3 external table feature to perform data migration. It creates an S3 external table to directly connect to object storage, reads the data, and automatically synchronizes it to the target internal table to complete the import.

Prerequisites

Please ensure that the AK/SK of the object storage service you are using has the corresponding object storage read permissions.

Operating steps

  1. Download the ByteHouse in-house data import script. After downloading, please store the script file in your custom path.

    s3-to-bh.py
    Unknown size

  2. In the same directory as the ByteHouse custom data import script, create and configure the import file s3-to-bh.ini.
    Replace the placeholders or example parameter values in the example with the actual ByteHouse connection information, object storage connection information, and import parameter values, so that the Python script can correctly perform the data import operation. For detailed parameter configuration information, see the parameter description below.

    Whether to enable Debug: 0 means disabled, 1 means enabled. After debug is enabled, insert SQL statements will not be executed.
   debug=1  
   
   Set the connection information for ByteHouse
   bytehouse_host={Your_Bytehouse_Host}
   bytehouse_port=19000
   bytehouse_username=bytehouse
   bytehouse_password={Your_Bytehouse_Apikey}
   virtual_warehouse={Your_ByteHouse_Virtual_Warehouse_to_import_data}
   
   # Volcengine Object Storage TOS
   s3_access_key=s3_access_key
   s3_secret_key=s3_secret_key
   Global root path used by ByteHouse S3 external tables to access object storage services; when importing data, the script will by default use the $s3_url/{database_name}/{table_name}{table_path}/* path
   s3_url=https://{bucket_name}.tos-s3-{region}.volces.com/{dir_name}
   
   # The database used to store ByteHouse S3 external tables; it is recommended to keep it separate from the business database. If it does not exist, it will be created.
   s3_database_name={database_name}
   Specify the target databases and tables for migration. Use a semicolon to separate different database-table pairs, following the format db1:table1,table2;db2:table1,table2
   database_table_name={db_name1}:{table_name1},{table_name2};{db_name2}:{table_name1},{table_name2}
   Defines the mapping relationship between the database where the ByteHouse S3 external table resides and the source database in object storage (in the format: S3 external table database name:object storage database name and path). If not specified, the default path is used, which is s3_url/{database_name
   s3_database_path={db_name1}:{db1_test},{db_name2}:{db2_test}
   No need to fill in. This parameter applies only to Hive partition tables whose storage medium is TOS. If you need to use it, please contact the ByteHouse team.
   table_paths=
   # Insert methods available when importing data into a ByteHouse target table: 'into' inserts data, while 'overwrite' replaces existing data.
   If the value is set to "into" or "nil", all table modes are treated as "insert into" mode.
   If the value is set to overwrite, all table modes are set to insert overwrite.
   # You can configure settings at the table level using db.table:into and db.table:overwrite
   table_insert_models={db_name}.{table_name1}:overwrite,{db_name}.{table_name2}:overwrite
   No entry required.
   table_settings=
   By default, configure a single Format configuration Different Format settings can be configured at the table level. For specific Format types, refer to https://www.volcengine.com/docs/6517/1361714
   If certain formats are not configured, process as Parquet
   Set at db.table::Parquet table granularity
   s3_table_format={db_name}.{table_name1}:Parquet,{db_name}.{table_name2}:JSONEachRow
   # This parameter controls the time format of the suffix in S3 external table names, which affects the validity period for resumable uploads,
   The default format is %Y%m%d for daily granularity; the maximum format that can be set is %Y%m%d%H%M%S.
   s3_table_suffix=%Y%m%d
   # Specify the data compression method used during export; after setting, the script will decompress data according to the specified format
   data_compression_method=none
   
   Whether the resumable transfer status should be cleaned up each time.
   The default value is 0, which means no cleanup is performed and resumable transfer is enabled from the previous state. If set to 1, previous state information will be cleared and the import will start from the beginning.
   empty_status=1
   
   Additional parameters
   Maximum retry attempts
   max_retries=1
   # Retry delay per attempt (unit: s)
   retry_delay_seconds=1
   # Settings for SQL insert statements, currently configured according to best practices.
   # If you encounter a memory limit, reduce min_insert_block_size_rows and also reduce min_insert_block_size_bytes. min_insert_block_size_bytes=min_insert_block_size_rows*256
   s3_insert_settings=optimize_unique_table_write=1,max_parsing_threads=1,max_insert_threads=4,exchange_source_pipeline_threads=4,max_threads=8,min_insert_block_size_bytes=2684275200,min_insert_block_size_rows=1048545,input_format_parquet_allow_missing_columns=1,input_format_orc_allow_missing_columns=1,input_format_arrow_allow_missing_columns=1,max_execution_time=3600,max_partitions_per_insert_block=5000
   
   # Concurrency parameter, which controls the level of parallelism when importing SQL in ByteHouse. Caution: This parameter should not be set too high, as excessive values may result in significant memory consumption in ByteHouse.
   parallelism=2

    Parameter description

    Parameter name

    Default value

    Required field

    Description

    debug

    0

    No

    Whether to enable debug mode: set to 0 to disable, 1 to enable.
    After enabling debug mode, the script will not execute insert SQL statements and will only print logs. This can be used for testing before the official migration.

    bytehouse_host

    None

    Yes

    Set as ByteHouse's public or private network domain name. The usage rules are as follows:

    • Recommended scenarios for using public domain names:
      • The object storage service is TOS, but TOS and ByteHouse are not in the same region.
      • The object storage services are AWS S3 and MinIO, and no dedicated connection has been established.
    • Recommended scenarios for using private network domain names:
      • The object storage service is TOS, and TOS and ByteHouse are in the same region.
      • The object storage services are AWS S3 and MinIO, and a dedicated line has been set up.

    You can refer to Get ByteHouse connection information to obtain the connection address for your ByteHouse account. The Host format is typically as follows:

    # 公网
tenant-{account_id}-{REGION}-{env}-public.bytehouse.volces.com
# 私网
tenant-{account_id}-{REGION}-{env}.bytehouse.ivolces.com

    bytehouse_port

    19000

    Yes

    ByteHouse port, fixed at 19000.

    bytehouse_username

    bytehouse

    Yes

    The ByteHouse username is always bytehouse.

    bytehouse_password

    None

    Yes

    The API Key for your ByteHouse account. For instructions on how to obtain it, please refer to 获取 API Key.

    virtual_warehouse

    None

    Yes

    The computing group used for importing data.
    Log in to the ByteHouse console, click Compute Group at the top, and on that page, view and copy the required Compute Group ID. Format is as follows:

    vw-{environment_id}{account_id}-{virtual_warehouse_name}

    s3_access_key

    None

    Yes

    Access Key ID (AK) required for ByteHouse S3 external table to access the object storage bucket.
    If you are using Volcengine Object Storage Service (TOS), you can set this value to the Access Key (AK) of your IAM account. For information on how to obtain it, see Access Key (Secret) Management.

    s3_secret_key

    None

    Yes

    Secret Access Key (SK) required for ByteHouse S3 external table to access the object storage bucket.
    If you are using Volcengine Object Storage Service (TOS), this value can be set to the SK of your IAM account. For instructions on how to obtain it, see Access Key (Secret Key) Management.

    s3_url

    None

    Yes

    The global root path used by ByteHouse S3 external tables to access the object storage service applies to all databases and can be set according to the directory where the data is stored during export.
    When importing data, unless otherwise specified, the script will by default use the global root path you defined in the s3_url parameter to read the databases and tables within it. It constructs the path according to $s3_url/{database_name}/{table_name}{table_path}/* and uses this path to read and import the data. To customize the mapping between ByteHouse S3 external tables and source tables in object storage, use the s3_database_path parameter.
    Format examples for different object storage services are as follows:
    TOS：

    https://{bucket_name}.tos-s3-{region}.volces.com/{dir_name}

    AWS S3：

    https://{bucket_name}.s3.{region}.amazonaws.com/{dir_name}

    Alibaba Cloud OSS:

    https://{bucket_name}.oss.{region}.aliyuncs.com/{dir_name}

    Huawei Cloud OBS:

    https://{bucket_name}.obs.{region}.myhuaweicloud.com/{dir_name}

    MinIO：

    http://ip:port/{bucket_name}/{dir_name}

    s3_database_name

    None

    Yes

    Specify the database name for storing ByteHouse S3 external tables. If the name does not exist, the system will create it. It is recommended that you create a dedicated database for migration, separate from your current business database.

    database_table_name

    None

    Yes

    Migration supports specifying multiple target databases and tables. You can specify multiple databases and tables; separate different databases with a semicolon (;) and separate tables within a database with a comma (,). An example is as follows:

    database_table_name=my_db1:orders,products;my_db2:users

    You can execute the following commands in ByteHouse to quickly generate the strings corresponding to all database tables in the current environment.

    SELECT arrayStringConcat(groupArray(concat(database, ':', B_list)), ';') AS result
FROM
(
    SELECT
        database,
        arrayStringConcat(groupArray(name), ',') AS B_list
        -- You can add filtering conditions for database (database name) and name (table name) as needed
    FROM system.tables where database not in
    ('system','MYSQL','mysql','INFORMATION_SCHEMA','information_schema')
    GROUP BY database
)

    s3_database_path

    None

    No

    Defines the mapping between the database that contains the imported ByteHouse S3 external table and the original source database in object storage. This mapping is used to handle cases where the names of the two databases are inconsistent. For example, the following example shows that the imported database db1 corresponds to the database db1_test in the object storage service directory, and the imported database db2 corresponds to the database db2_test in the object storage service directory.

    db1:db1_test,db2:db2_test

    If you do not specify a mapping, the script will read and import data into the ByteHouse S3 external table according to the path configured in the s3_url parameter, that is, s3_url/{database_name}/{table_name}.

    table_paths

    None

    No

    No input required. This parameter is only applicable to Hive partition tables whose storage medium is TOS. To use, please contact the ByteHouse team.

    table_insert_models

    None

    No

    When importing data into a target table in ByteHouse, you can choose the insert mode: 'into' means to insert data, while 'overwrite' means to overwrite data.

    • If the value is set to "into" or "nil", this means that all table modes are set to "insert into".
    • If the value is set to overwrite, all table modes are in insert overwrite mode.
    • You can specify the db.table:into,db.table:overwrite format at the table level. Tables for which this parameter is set will execute in insert overwrite mode, while tables without this parameter will execute in insert into mode.

    table_settings

    None

    No

    No need to fill in.

    s3_table_format

    Parquet

    No

    By default, a single format (format) can be configured. It is also possible to configure different formats at the table level. Please refer to the format description in Compute Group Audit for the currently supported format types.

    • You can configure at the table level according to db.table:Parquet,db.table:Parquet.
    • If a format is not configured for certain tables, the system will use Parquet as the default format for those tables.

    s3_table_suffix

    %Y%m%d

    No

    This parameter controls the time format used in the suffix of S3 external table names, which in turn affects the effective period for resumable uploads. The breakpoint resume feature of ByteHouse data import scripts is implemented by checking whether the currently executed SQL statement is exactly the same as the SQL recorded in the success file, which is a marker file that records the execution status of the data import task. When a change in the time suffix causes the S3 external table name to change, the system treats it as a different SQL statement, which results in failure of resumable upload and triggers a full re-import.
    The default suffix format is %Y%m%d (day granularity), and the maximum supported format is %Y%m%d%H%M%S (second granularity).

    data_compression_method

    auto

    No

    Set the data compression format for export, such as none or gzip. After configuration, the script will decompress the data according to the format you specified.

    empty_status

    0

    No

    Whether to clear the breakpoint resume state each time. The default value is 0, which means no cleanup is performed and breakpoint resume is enabled based on the previous state. Setting the value to 1 clears previous state information and starts the import process from the beginning.

    max_retries

    1

    No

    Set the maximum number of retries.

    retry_delay_seconds

    5

    No

    Set the retry delay.

    s3_insert_settings

    None

    No

    Write settings for inserting data into ByteHouse S3 external tables to improve write speed. The following parameter configurations are best practices and can be used as-is.

    optimize_unique_table_write=1,max_parsing_threads=1,max_insert_threads=4,exchange_source_pipeline_threads=4,max_threads=8,min_insert_block_size_bytes=2684275200,min_insert_block_size_rows=1048545,input_format_parquet_allow_missing_columns=1,input_format_orc_allow_missing_columns=1,input_format_arrow_allow_missing_columns=1,max_execution_time=3600,max_partitions_per_insert_block=5000

    warning

    If you encounter memory limit issues, reduce the values of min_insert_block_size_rows and min_insert_block_size_bytes. The formula is min_insert_block_size_bytes=256*min_insert_block_size_rows.

    Parameter descriptions

    • optimize_unique_table_write: Unique key table optimization switch. Setting this to 1 enables optimization; setting it to 0 disables optimization.
    • max_parsing_threads: Sets the maximum number of threads for the data parsing stage.
    • max_insert_threads: Sets the maximum number of threads during the data insertion phase, determining the concurrency of write operations. Proper configuration can improve batch write performance.
    • exchange_source_pipeline_threads: Sets the number of concurrent threads for reading data from object storage.
    • max_threads: Sets the maximum number of threads to execute to avoid resource competition caused by too many threads, such as high CPU or memory usage.
    • min_insert_block_size_bytes: Sets the minimum number of bytes for inserted data blocks. Larger blocks improve batch processing efficiency, but increase memory usage.
    • min_insert_block_size_rows: Sets the minimum number of rows per data block when inserting data, improving batch write efficiency by controlling the number of rows per block.
    • input_format_parquet_allow_missing_columns: Controls whether the source file is allowed to lack columns that exist in the target table when reading Parquet files. When set to 1, such missing columns are permitted, which improves compatibility.
    • input_format_orc_allow_missing_columns: Controls whether columns that exist in the target table but are missing from the source file are allowed when reading ORC format files. Set to 1 to allow this, to adapt to scenarios with inconsistent column structures.
    • input_format_arrow_allow_missing_columns: Controls whether the source file is allowed to lack columns present in the target table when reading Arrow format files. When set to 1, this is allowed, which improves compatibility with different data formats.
    • max_execution_time: Limits the maximum execution time for inserted tasks (in seconds). For example, a value of 3600 means a maximum execution time of 1 hour, which prevents tasks from running indefinitely.
    • max_partitions_per_insert_block: Controls the maximum number of partitions that a single insert block can involve, helping to prevent frequent metadata operations caused by inserting into too many partitions at once.

    parallelism

    1

    No

    Used to set the concurrency for creating object storage external tables and performing insert operations to write data.

  3. Run the script to import data from object storage into ByteHouse.
    Please ensure that the current working directory is the directory where the script is located, or use an absolute path to specify the script location.

    python s3-to-bh.py s3-to-bh.ini

    If you need to start the script in the background and check the status of task execution, you can run the following command.

    nohup python s3-to-bh.py > output.log 2>&1 &

    After execution, you can view the logs and understand the task progress in the output.log file.

    View all logs
cat output.log

  4. Compare the row count of the same table in ByteHouse and ClickHouse, and check data consistency. You can also perform sum aggregation comparisons based on partial data.

    # ClickHouse
SELECT COUNT() 
FROM `sample_db`.`table_names=sample_table`

# ByteHouse
SELECT COUNT() 
FROM `sample_db_bh`.`sample_table_bh`

  5. After the migration is complete, you can create tables, and use and query system tables in ByteHouse. For the mapping between ClickHouse and ByteHouse table engines and system tables, see 相关参考：表引擎及系统表映射关系.

Reference: Table engine and system table mappings

The mapping relationship between ByteHouse and ClickHouse table engines and system tables is as follows:

Table engine mappings

Common table engines in ClickHouse include MergeTree, ReplicatedMergeTree and Distributed, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree, and ReplacingMergeTree. The ByteHouse table engine is the self-developed CnchMergeTree table engine. The mapping is as follows:

ClickHouse table engine

The ByteHouse table engine

MergeTree

Replace it directly with a CnchMergeTree without UniqueKey. Caution: CnchMergeTree currently supports only partition-level TTL and does not support row-level TTL. However, partition-level TTL is the preferred option for table creation in most cases.

ReplicatedMergeTree + Distributed

SummingMergeTree

It is recommended to switch to a regular table and then manually modify the query to calculate the sum.

AggregatingMergeTree

Option 1:
Replacement via synchronous or asynchronous materialized views

  1. Using synchronous materialized views and CnchAggregatingMergeTree: With streaming writes, each push generates a data block (part), which CnchAggregatingMergeTree merges during its merge process. This solution requires that the base table must not be a unique table.
  2. Asynchronous materialized view: partition-level aggregation. It is necessary to consider both the volume of data to be refreshed and the requirements for expected return time. In general, the partitions of the base table and the target table should be aligned. The base table should be imported offline, as real-time import incurs higher refresh costs. In join scenarios, the dimension table is updated infrequently.

Option 2:
Rewrite as a standard CnchMergeTree table. For queries, the business needs to proactively rewrite the SQL to add agg.

CollapsingMergeTree

It can be replaced by a CnchMergeTree table with a UniqueKey, that is, a unique table.
UniqueKey is the OrderBy Key of the original table.
Replace the sign column in the original table with the implicit delete_flag column from the unique table (no explicit definition required). In the original table, sign = 1 corresponds to delete_flag = 0 in the unique table (addition), while sign = -1 corresponds to delete_flag = 1 in the unique table (deletion).
In practice, when adding or updating rows based on UniqueKey, you do not need to explicitly specify the value of delete_flag because its default value is 0. If necessary, you may explicitly set delete_flag to 0. When deleting rows based on UniqueKey, you must explicitly set delete_flag to 1.

VersionedCollapsingMergeTree

It can be replaced with a CnchMergeTree table with a UniqueKey, that is, a unique table.
UniqueKey is the OrderBy Key of the original table.
The usage of the version column is as follows: CnchMergeTree(version).
The replacement and usage of the sign column are consistent with CollapsingMergeTree.

ReplacingMergeTree

It can be replaced with CnchMergeTree + Unique key. Original table ORDER BY as UNIQUE KEY You can use the optional parameters ver and is_deleted when creating a table.

  • ver: can be replaced by the version field in the "unique" table. Cnch Unique tables support designating a field in the table as the version field. The engine ensures that when writing data with the same key, overwriting occurs only if the data version is greater than or equal to the existing version. The version field supports all UInt types and Data/DateTime, and cannot be nullable. Reference document: ByteHouse Table Creation Best Practices.
  • is_deleted: Can be replaced by the delete_flag field in the unique table. In the original table, is_deleted=1 (deleted) corresponds to delete_flag=1, and is_deleted=0 (retained) corresponds to delete_flag=0.
    The is_deleted field in ClickHouse is an explicit column and can only be used together with the ver field. The "unique" table does not have this restriction. delete_flag is an implicit column.

warning

It is not recommended to use MySQL, MongoDB, and other engines in ByteHouse.

System table mappings

ByteHouse CDW is gradually adding support for ClickHouse system tables. Before using the system tables, please confirm that your ByteHouse engine version supports the corresponding system tables. You can view the engine version by navigating to ByteHouse Console > Tenant management > Basic information.
Image

ClickHouse system tables

ByteHouse system tables

ByteHouse engine version requirements

Note

system.query_log

bh_system.query_log

v2.1

You can use bh_system.query_log to query historical records. Before use, please enable detailed logging via the ByteHouse Console > Audit logs > Query history path.
Image

tip

If you cannot find the Enable detailed logging entry point in your interface, you may contact the ByteHouse team to have your account added to the whitelist.

system.databases

system.databases

v2.1

Includes information about all currently existing databases, such as database name, creation time, and more.

system.tables

system.cnch_tables

v2.2

Includes information about all tables in the database, such as table name, the database each table belongs to, engine type, and more.
It is not recommended to omit filter conditions for the database and name columns when querying (such as SELECT *), or to query columns that include total_rows and total_bytes. Such queries may require significantly more time to compute precise metadata.

system.columns

system.cnch_columns

v2.2

Includes information about the columns of all tables in the database, such as column name, data type, table name, and more.

system.functions

system.functions

v2.1

Contains information about all functions supported by ClickHouse, such as function names, parameter types, and more.

system.mutations

system.mutations

v2.2

Used to store information about ongoing data modification operations, such as "UPDATE", "DELETE", and "ALTER".
Only the mutation progress results of the server that has responded to the request can be returned. To return complete results, a redesign and modifications are required.

system.parts

system.cnch_parts_info

v2.2

Contains information about all data blocks (parts) in a MergeTree table.

Last updated: 2026.03.31 16:25:02