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 Doris to ByteHouse
Copy page
Download PDF
Station Relocation Tool
Migrate Doris to ByteHouse
Copy page
Download PDF
Migrate Doris to ByteHouse

ByteHouse cloud data warehouse supports fast offline migration of your data stored in Doris to ByteHouse using script tools. Once migrated, the data can be immediately put into use, enabling unified data management and development. This article will provide a detailed introduction to the methods for migrating Doris database tables and data to the ByteHouse cloud data warehouse.

Function overview

Migrating Doris data to the ByteHouse cloud data warehouse relies on ByteHouse's proprietary migration scripts: the Doris data export script and the Doris data import script. During migration,

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

Script name

Capability description

ByteHouse self-developed script for exporting Doris data

  • 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 capability: Provides general export capability and supports various data scenarios.
  • Progress bar display: Visually display export progress using a progress bar.

ByteHouse proprietary data import script

  • Resumable upload: Accurately records the import status, avoids re-executing tasks that have already been successfully imported, and supports rollback operations in case of task failure, ensuring the stability and continuity of the migration process.
  • Concurrent execution: Supports concurrent processing for external table creation and SQL import, which can effectively improve migration efficiency and reduce overall migration time.
  • Multi-table, multi-database support: Allows migration operations on multiple tables across several databases simultaneously, meeting migration requirements in complex data environments.
  • External table partition filtering import: Supports configuring partition filtering strategies for individual tables, so that only partition data meeting the criteria is imported, reducing unnecessary data processing.
  • External table storage format configuration: Supports customizing the storage format at the single-table level, allowing you to select the appropriate storage format based on business requirements.
  • Import retry capability: When an import task encounters an error, automatic or manual retry is supported to improve the success rate of the task.
  • Single-table operation support: Supports insert into (insert) and insert overwrite (overwrite) operations at the single-table level to meet various data import requirements.
  • dry-run functionality: Allows you to run scripts in advance to check and validate their correctness, helping to prevent migration failures caused by script issues.
  • N-level partitioned external table: For Hive partition tables, it supports configuring granular partitioning rules for individual tables according to the Hive partitioning model, enabling flexible adaptation to different data partitioning scenarios.

Migration process

The process of migrating from Doris to ByteHouse consists of the following three steps:

  1. Prepare the target tables. Create the tables required for migration in ByteHouse.
  2. Export data from Doris to the object storage service, and check whether the data and directories in the object storage service meet expectations.
  3. Import data from object storage into Bytehouse and check 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 has a relatively small specification (specification is M or lower), and at the same time, the table size exceeds 100 million rows.

Cautions
  • During the migration process, there is network connectivity and a stable network environment between the target database, the source database, and the S3 protocol object storage service (hereafter referred to as the object storage service).
    • Under normal circumstances, transmission between Doris, the object storage service, and ByteHouse occurs 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. At this time, make sure that the dedicated line configuration is completed and that 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 from cross-region access.
  • Data migration may incur charges for transmission traffic and storage services. The specific billing rules are subject to each product's official documentation and actual invoices. Please confirm the billing model in advance.

Preparation
  1. Obtain the connection information for Doris, Object Storage Service, and ByteHouse, including the domain name, port number, account, password, Access Key ID (AK), Secret Access Key (SK), and more.
  2. Please ensure that the Doris and ByteHouse accounts you use have full read and write permissions for the object storage service.
  3. Confirm that you have configured a default write compute group in ByteHouse, which is used for data merge operations. You can log in to the ByteHouse console, click Tenant management at the top, and under the Parameter settings tab, set the default write compute group.
    If your ByteHouse computing group needs to configure specific compatibility settings, please contact the ByteHouse team.
  4. If you have special requirements for time zones, you can contact the ByteHouse team to set the tenant-level session timezone to a specified time zone. By default, the ByteHouse service you use in Volcano Engine operates in China Standard Time (UTC+8), the ByteHouse service you use in AWS operates in the UTC time zone, and the BytePlus ByteHouse service you use operates in China Standard Time (UTC+8).

Step 1: Prepare the target table

Please ensure that the Doris source database and table are ready, and that the target database and table have been created in ByteHouse. For instructions on creating ByteHouse databases and tables, see Database Management. ByteHouse also provides best practices for table creation. For details, 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 ticket or contact the ByteHouse team.

Step 2: Export data from Doris to the object storage service

Doirs logic for exporting to object storage

  1. Perform serial export at the table level according to the database table configuration.
  2. Query all tablets of a table. This operation requires that the Doris account you use has permission to view the tablets of a table.
  3. Export all tablets of a table concurrently according to the configured parallelism.

warning

Because the current Doris export process lacks atomicity, the corresponding files may still be exported to object storage even if the export SQL execution fails. However, since the current script has retry capability, two different situations may occur.
Situation 1
Incomplete files may also occur in object storage. Therefore, during the export process, you need to check whether the Error executing export query on Doris error has occurred. For examples of common errors, refer to the code block below.

[INFO] data-migrator-doris-to-s3.py v1.0.1: Executing export query on Doris [SELECT * FROM store_sales.Sales1 TABLET(12757)] into outfile s3 beginning
[INFO] data-migrator-doris-to-s3.py v1.0.1: Error executing export query on Doris (attempt 1/10): (1105, 'errCode = 2, detailMessage = (172.20.80.6)[CANCELLED]Process memory not enough, cancel top memory used query: <Query#Id=18f73238313943d6-9b3b82b7c136cd40> consumption 35.25 MB, backend 172.20.80.6, process memory used 805.76 MB exceed limit 13.86 GB or sys available memory 675.82 MB less than low water mark 788.68 MB. Execute again after enough memory, details see be.INFO.')
[INFO] data-migrator-doris-to-s3.py v1.0.1: Error executing export query on Doris (attempt 2/10): (1105, 'errCode = 2, detailMessage = (172.20.80.6)[INTERNAL_ERROR]Failed to get query fragments context. Query may be timeout or be cancelled. host: 172.20.80.6')
[INFO] data-migrator-doris-to-s3.py v1.0.1: Error executing export query on Doris (attempt 3/10): (1105, 'errCode = 2, detailMessage = (172.20.80.6)[INTERNAL_ERROR]Failed to get query fragments context. Query may be timeout or be cancelled. host: 172.20.80.6')
[INFO] data-migrator-doris-to-s3.py v1.0.1: Executing export query on Doris [SELECT * FROM store_sales.Sales1 TABLET(12757)] into outfile s3 successful

If this error has occurred, you need to check whether the table is a Unique Table. The steps are as follows:

  • If the corresponding target table in ByteHouse is a Unique Table (唯一键表), you only need to check whether a SUCCESS file exists in the directory of the corresponding database table in object storage. For example, if a SUCCESS file exists for TABLET(12757) as shown in the diagram below, this error can be ignored because ByteHouse unique key tables support deduplication and will ignore exported duplicate files.
  • If the corresponding target table in ByteHouse is not a Unique Table, you need to delete files starting with 12757 and re-export the Tablet to ensure that the Error executing export query on Doris error does not occur during the next export.

Scenario 2
The default script will retry 10 times. If all retries ultimately fail, the script will report the error There are [xxx] tablets export failed in table [xxx], need to retry. In this case, you also need to re-export the tablet. In the error message, the first [xxx] is a string array of Tablet IDs separated by commas, and the second [xxx] is the table name. When re-exporting, you can individually specify which tablets to export by configuring tablets, and set export_level=tablet.

Operating steps

  1. Download the Python tool and the Doris data export script file developed by ByteHouse. You can download the corresponding script file based on the Python version you are using.
    After downloading, save the script to a custom path of your choice and record the path. You will need to specify this path when executing the script later.

    pip3 install pymysql
pip3 install dbutils
pip3 install tqdm

    The Python version

    Migration script files

    Python 3.12.0

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

    Python 3.6.8

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

  2. In the same directory as the Doris data export script, create and configure the ini file. In this example, the ini file is named data-migrator-doris-to-s3.ini. Please replace the placeholders or example parameter values with your actual Doris connection information, the target path in object storage, and data migration parameter values. This ensures that the data export script can execute properly.
    This configuration file is divided into two export modes: table and tablet. For details, refer to the instructions in the configuration file.

    The Doris information
   doris_host={your_doris_ip}
   doris_port=9030
   doris_user={your_doris_user_name}
   doris_password={your_doris_password}
   
   Object storage connection information
   s3_access_key={tos_access_key}
   s3_secret_key={tos_secret_access_key}
   Do not include the trailing '/' character; format: s3://a bucket name/an output path
   s3_url=s3://{bucket_name}/{output_path}
   s3_endpoint=https://tos-s3-{your_region}.volces.com
   s3_region={your_region}
   
   # export mode: table or tablet, default value: table
   export_level=table
   # Name of the exported database, currently only single-database export is supported
   database_name=databases_name
   To export information about all table names in the database, enter '*'. To export specific tables, list their names separated by commas.
   table_names=*
   The Tablet information to be exported. This configuration is only effective when export_level is set to tablet. Format: table_name1:tabletid; table_name2:tabletid, tabletid, tabletid
   For each table, separate its tablet information using semicolons, and represent each table as table_name:tabletids.
   # tabletids should contain the IDs of the tablets to be exported from the table, separated by commas.
   tablets=test_bitmap:1940519
   Export matching rules for selecting different columns in SQL and keep the following configuration as the default
   field_rules=bitmap:bitmap_to_array($Field$)
   
   Optional configurations
   # Maximum number of retries after failure. Default value: 10
   max_retries=10
   The retry delay time for each retry, default value: 5
   retry_delay_seconds=5
   # Session configuration, for example: enable_parallel_outfile: enable parallel export, query_timeout: the timeout period for SQL execution,
   # Default values: enable_parallel_outfile = true; query_timeout = 3600; exec_mem_limit = 16G
   outfile_sets=enable_parallel_outfile = true;query_timeout = 3600;exec_mem_limit=16G
   # Export file format, default value: PARQUET
   file_format=PARQUET
   Other filter conditions (filters)
   where=
   The number of concurrent SQL exports. The default value is 4, which means that 4 tablet IDs are exported concurrently each time.
   parallelism=4
   max_file_size=128MB

    Configure key

    Type

    Description

    doris_host

    string

    Doris's address.

    doris_port

    string

    The port for Doris is configured as 9030.

    doris_user

    string

    Doris's username.

    doris_password

    string

    Doris's password.

    s3_access_key

    string

    Access Key ID of the object storage service that stores exported files.
    If you are using Volcengine TOS, refer to Access Key (Key) Management.

    s3_secret_key

    string

    Secret Access Key for the object storage service that stores exported files.
    If you are using Volcengine TOS, refer to Access Key Management for information on obtaining an Access Key.

    s3_url

    string

    The target storage directory for exported files in object storage does not need to have a trailing /. For example: s3://bucket_name/output.

    s3_endpoint

    string

    Access domain name (endpoint) of the object storage bucket for exported files. Examples of different object storage services are as follows:

    • TOS: You can refer to the S3 Endpoint column in the Region and access domain name (Endpoint) documentation.
      • Public network: https://tos-s3-{your_region}. volces.com
      • Private network: https://tos-s3-{your_region}. ivolces.com
    • AWS S3：https://s3.{your_aws_region}.amazonaws.com
    • Alibaba Cloud OSS: https://oss.{your_oss_region}. aliyuncs.com
    • Huawei Cloud OBS: https://obs.{your_obs_region}. myhuaweicloud.com
    • MinIO：http://{minio-host}:9000

    s3_region

    string

    The region where the object storage bucket for exported files is located (region).

    • If you are using Volcengine TOS, you can refer to the Region and access domain name (Endpoint) documentation.
    • If you are using MinIO, you do not need to specify a region; you may leave the field blank or enter a custom region identifier.

    export_level

    string

    The export mode. The available options are table or tablet**.**

    • table **** represents export at the table level.
    • tablet **** represents export at the tablet level.

    database_name

    string

    Name of the database to be exported in Doris.

    table_names

    string

    Table names to export from the Doris database. To export multiple tables, separate them with commas (,). If you use an asterisk (*), it indicates that all tables in the database will be exported. This configuration is only effective when export_level is set to table.

    field_rules

    string

    This parameter is used to define the column matching rules in exported SQL by modifying the query logic for the corresponding columns to accommodate the export requirements of special data types. The default value is bitmap:bitmap_to_array($Field$). For example, in Doris, the Bitmap type needs to be converted to the Array format in order to export correctly. You can specify the conversion logic using this parameter, as shown in the following example:

    bitmap:bitmap_to_array($Field$)

    The default value is null, which means that by default, the exported SQL is select col1,col2,col3 from xxx .... After configuring rules, the script will automatically replace the query logic for the corresponding columns.
    The rule configuration format is: column_type:function($Field$);column_type:function(function($Field$)), uses the key-value format, and multiple rules are separated by semicolons. When column_type is included in the type matching rules for a column, the query logic for that column in the exported SQL will be replaced with the configured function:

    • Key: The Doris column type to be matched, for example, BITMAP;
    • value: The Doris conversion function, which is used to convert columns of this type. $Field$ is a placeholder that is automatically replaced with the actual column name, such as field1 and field2.

    tablets

    string

    The tablet information to export. This configuration is only effective when export_level is set to tablet. The format is: table_name1:tabletid;table_name2:tabletid,tabletid,tabletid.
    Tablet information for each table is separated by semicolons, with each table formatted as table_name:tabletids. 'tabletids' should contain the IDs of the tablets to be exported from this table, separated by commas.

    file_format

    string

    The file format for export. Default value: PARQUET.

    max_retries

    int

    Maximum number of retries after failure; default value: 10.

    retry_delay_seconds

    int

    Retry delay for each attempt. Default: 5 seconds.

    outfile_sets

    string

    When connecting, certain session-level parameters must be set in advance and separated by semicolons. Default settings: enable_parallel_outfile = true; query_timeout = 3600; exec_mem_limit=16G, the parameters are described as follows:

    • enable_parallel_outfile: Controls whether the parallel export feature is enabled.
    • query_timeout: Sets the timeout period for the export task (unit: seconds).
    • exec_mem_limit: Limits the maximum memory usage for a single export task.

    where

    string

    You can use this parameter to set filter conditions for a specific table. It also supports exporting a data set and takes effect at the database level.
    If you need to set different filter criteria for different tables, subdivide according to table_names, and for each table, generate a new configuration file and execute the export task.

    parallelism

    string

    Number of concurrent SQL exports; the default is 4. Indicates that 4 tablet_ids are exported concurrently each time.

    max_file_size

    string

    The maximum size of exported files is 128 MB by default.

  3. Run the Python script file to export data from Doris to object storage.
    Before running, ensure that the current working directory is the same as the script's location, or specify the script location using an absolute path.

    python3 data-migrator-doris-to-s3.py data-migrator-doris-to-s3.ini

  4. After exporting Doris data, check whether the exported data and directories in object storage meet expectations.
    The data files exported according to the steps above will be stored in object storage using the following directory structure. You can use the example paths below to check in your object storage service whether the directories and data meet your expectations. Using the URL in the configuration file above as an example, the storage path is as follows:

    s3://bucket_name/output/{database_name}/{table_name}/{tablet_id}_{uuid}_{file_index}.parquet

Step 3: Import from object storage service to ByteHouse

Use the ByteHouse self-developed data import script to import the exported data from the object storage service into ByteHouse. The proprietary import script developed by ByteHouse leverages the S3 external table feature to enable data migration: by creating an S3 external table that directly connects to object storage to read data, and then automatically synchronizing the data to the target internal table, thus completing the import.

Prerequisites

Ensure that the object storage AK/SK has read permissions for the corresponding object storage.

Operating steps

  1. Download the ByteHouse proprietary data import script. After downloading, please store the script file in a path of your choice.

    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 sample 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, refer to the parameter description below.

    #### Enable debug?
   0 means not enabled, 1 means enabled. When 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}
   
   Volcano Engine Object Storage TOS
   s3_access_key=s3_access_key
   s3_secret_key=s3_secret_key
   The global root path used by ByteHouse S3 external tables to access the object storage service. When importing data, the script will, by default, use the $s3_url/{database_name}/{table_name}{table_path}/* path.
   s3_url=s3://{bucket_name}/{output_path}
   
   Database for storing ByteHouse S3 external tables; recommended to keep separate from business database; created if not existing
   s3_database_name={database_name}
   # Specify the target databases and tables for migration, separating different databases with a semicolon and tables within each database with a comma. Use the format db1:table1,table2;db2:table1,table2
   database_table_name={db_name1}:{table_name1},{table_name2};{db_name2}:{table_name1},{table_name2}
   Mapping between ByteHouse S3 external table database and source database in object storage
   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 for importing data into the target table in ByteHouse
   When importing data into the target table in ByteHouse, you can choose between the 'into' and 'overwrite' insert methods. 'into' means inserting data, while 'overwrite' means overwriting data.
   If the value is set to into or nil, all table modes use insert into
   If the value is set to overwrite, all table modes will use insert overwrite mode.
   # 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 input 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,
   # Can be set at the db.table::Parquet table level
   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,
   Default format is %Y%m%d, which specifies the day level; maximum supported format is %Y%m%d%H%M%S
   s3_table_suffix=%Y%m%d
   # Specify the data compression method to be set during export; after setting, the script will decompress the data according to the set format
   data_compression_method=none
   
   Whether to clean up the state of resumable transfers on each execution
   The default value is 0, which means previous state information is not cleared and the import resumes from the previous state. Setting the value to 1 clears previous state information and starts the import from the beginning.
   empty_status=1
   
   Additional parameters
   Maximum retry attempts
   max_retries=1
   Delay for each retry attempt (in seconds)
   retry_delay_seconds=1
   # Insert SQL settings, currently configured according to best practices.
   If you encounter a memory limit, please lower min_insert_block_size_rows and also lower 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. Controls the concurrency of SQL import execution in ByteHouse. Caution: This parameter should not be set too high; excessive parameter settings will 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, or 1 to enable.
    After debug mode is enabled, 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 to ByteHouse public or private domain name; 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, Alibaba Cloud OSS, Huawei Cloud OBS, and MinIO, and no dedicated line has been set up.
    • 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, Alibaba Cloud OSS, Huawei Cloud OBS, and MinIO, and a dedicated line has been established.

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

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

    bytehouse_port

    19000

    Yes

    The ByteHouse port is fixed at 19000.

    bytehouse_username

    bytehouse

    Yes

    The ByteHouse username is always bytehouse.

    bytehouse_password

    None

    Yes

    To obtain the API Key for your ByteHouse account, please refer to Obtain API key.

    virtual_warehouse

    None

    Yes

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

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

    s3_access_key

    None

    Yes

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

    s3_secret_key

    None

    Yes

    The Secret Access Key (SK) required for a 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 information 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 (applicable to all databases) can be set to the export directory s3_url specified in the export steps above.
    When importing data, unless otherwise specified, the script will by default use the global root path defined in the s3_url parameter to read the databases and tables under it, using the path pattern $s3_url/{database_name To customize the mapping between ByteHouse S3 external tables and source tables in object storage, you can use the s3_database_path parameter.
    Here is an example of how to fill in the field:

    s3://{bucket_name}/{output_path}

    s3_database_name

    None

    Yes

    Specify the database name for storing the ByteHouse S3 external table. If the name does not exist, the system will create it. We recommend that you create a dedicated migration database and keep it separate from your business database that is currently in use.

    database_table_name

    None

    Yes

    Specify the target databases and tables for migration. You can configure multiple databases and tables; separate different databases with a semicolon (;) and separate different 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 relationship between the database to which the ByteHouse S3 external table is imported and the source database in object storage, to handle cases where their names are inconsistent. For example, the following 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 relationship, the script will read and import data into the ByteHouse S3 external table according to the path configured in the s3_url parameter. Specifically, s3_url/{database_name

    table_paths

    None

    No

    No entry required. This parameter applies only to Hive partition tables whose storage medium is TOS. If you wish to use it, please contact the ByteHouse team.

    table_insert_models

    None

    No

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

    • If the value is set to 'into' or 'nil', all table modes will be set to 'insert into'.
    • If the value is set to overwrite, all tables will be in insert overwrite mode.
    • You can configure parameters at the table level using the db.table:into,db.table:overwrite format. Tables with this parameter set will be executed in insert overwrite mode, while tables without this parameter will be executed in insert into mode.

    table_settings

    None

    No

    No entry required.

    s3_table_format

    Parquet

    No

    By default, a single format can be configured. Additionally, different formats can be configured for each table. Refer to the format description in Compute Group Audit for the currently supported format types.

    • Settings can be set at the table level according to db.table:Parquet,db.table:Parquet.
    • If certain tables do not have a format configured, the system will set the format of those tables to Parquet by default.

    s3_table_suffix

    %Y%m%d

    No

    This parameter controls the time format of the suffix in S3 external table names, which affects the validity period for resumable uploads. The breakpoint resume feature of the ByteHouse data import script works by checking whether the currently executed SQL statement is identical to the SQL recorded in the success file, which serves as a marker file for 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, resulting in failure to resume from breakpoint and triggering a full re-import.
    The default suffix format is %Y%m%d (day level), and the longest supported suffix format is %Y%m%d%H%M%S (second level).

    data_compression_method

    auto

    No

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

    empty_status

    0

    No

    Whether to clean up the status of resumable transfers each time. The default value is 0, which means no cleanup is performed and breakpoint resume is enabled based on the previous state. If set to 1, previous state information is cleared and the import starts from the beginning.

    max_retries

    1

    No

    Set the maximum number of retries.

    retry_delay_seconds

    5

    No

    Set the retry delay time.

    s3_insert_settings

    None

    No

    Settings for inserting data into the ByteHouse S3 external table 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 a memory limit issue, 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.

    The parameter descriptions are as follows:

    • optimize_unique_table_write: Unique key table optimization switch. Set to 1 to enable optimization; set to 0 to disable optimization.
    • max_parsing_threads: Sets the maximum number of threads for the data parsing phase.
    • max_insert_threads: Sets the maximum number of threads for the data insertion phase, which determines the concurrency of write operations. Setting this parameter appropriately can improve batch write performance.
    • exchange_source_pipeline_threads: Set the number of concurrent threads for reading data from object storage.
    • max_threads: Sets the maximum number of threads for execution to avoid resource contention caused by too many threads, such as excessively 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 inserted data block to improve batch write efficiency by controlling the number of rows per block.
    • input_format_parquet_allow_missing_columns: Controls whether missing columns in the source Parquet file are allowed when reading data into the target table. Set to 1 to enable this option and improve compatibility.
    • input_format_orc_allow_missing_columns: Controls whether the source file is allowed to lack columns that exist in the target table when reading ORC format files. When set to 1, this is permitted, which is suitable for scenarios where the source and target table structures differ.
    • input_format_arrow_allow_missing_columns: Controls whether the source file is allowed to lack columns that are present in the target table when reading Arrow format files. When set to 1, this is permitted, enhancing compatibility with different data formats.
    • max_execution_time: Limits the maximum execution time for inserted tasks (in seconds). In the example, 3600 means a maximum duration of 1 hour, which prevents tasks from running indefinitely.
    • max_partitions_per_insert_block: Controls the maximum number of partitions allowed per insert block to prevent frequent metadata operations caused by inserting into too many partitions at once.

    parallelism

    1

    No

    Sets 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.
    Make sure that the current working directory is the same as the directory where the script is located, or specify the script location using an absolute path.

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

    If you need to start the script in the background and view 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 in the output.log file to monitor task progress.

    View all logs
cat output.log

  4. Compare the count of the same table in ByteHouse and Doris, and check for data consistency.

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

# ByteHouse
SELECT COUNT() 
FROM `sample_db_bh`.`sample_table_bh`
Last updated: 2026.03.31 16:25:02