All Products
Search

This Product

    ApsaraDB for ClickHouse:Import data from OSS

    Document Center

    ApsaraDB for ClickHouse:Import data from OSS

    Last Updated:Jul 17, 2026

    Import data from Object Storage Service (OSS) to Alibaba Cloud ClickHouse by using a table engine or a table function to query, analyze, and process logs.

    Prerequisites

    • Activate Object Storage Service (OSS). For more information, see Activate OSS.

    • A bucket is created in the same region as Alibaba Cloud ClickHouse. For instructions on how to create a bucket, see Create a bucket.

    • The account used to access OSS has read permissions on the objects in the bucket. For more information, see Overview.

    Data preparation

    Save the following test data as test.csv and upload it to OSS. The default column separator is a comma (,). For upload instructions, see Upload files.

    1,yang,32,shanghai,http://example1.com
2,wang,22,beijing,http://example2.com
3,xiao,23,shenzhen,http://example3.com
4,jess,45,hangzhou,http://example4.com
5,jack,14,shanghai,http://example5.com
6,tomy,25,hangzhou,http://example6.com
7,lucy,45,shanghai,http://example7.com
8,tengyin,26,shanghai,http://example8.com
9,wangli,27,shenzhen,http://example9.com
10,xiaohua,37,shanghai,http://example10.com

    Steps

    1. Connect to the Alibaba Cloud ClickHouse cluster. For more information, see Connect to a cluster.

    2. Create a local table named oss_test_tbl_local.

      Important

      • The schema of the Alibaba Cloud ClickHouse table must match the schema of the OSS external table and the format of the data in OSS. To prevent parsing failures and cluster exceptions, handle null fields carefully.

      • Select the appropriate CREATE TABLE statement based on the replica configuration of your cluster. You can view the replica configuration on the Cluster Information page in the Cluster Properties section of the console.

      • You can select a table engine based on your business requirements. For more information, see Table engines.

      Single-replica edition

      CREATE TABLE oss_test_tbl_local ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = MergeTree()
ORDER BY id;

      Double-replica edition

      CREATE TABLE oss_test_tbl_local ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
ORDER BY id;

    3. (Optional) Create a distributed table named oss_test_tbl_distributed.

      Note

      Create a distributed table if you want to distribute data across all local tables in the cluster.

      CREATE TABLE oss_test_tbl_distributed ON CLUSTER default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = Distributed(default, default, oss_test_tbl_local, rand());

    4. Import data from OSS to Alibaba Cloud ClickHouse.

      Alibaba Cloud ClickHouse supports two methods to import data from OSS: table engines and table functions.

      Important

      Select the appropriate statement based on your cluster's version. You can view the version on the Cluster Information page in the Cluster Properties section of the console.

      Method 1: Use a table engine

      1. Create an OSS external table named oss_test_tbl.

        Syntax for versions earlier than 22.8

        CREATE TABLE <table_name> [ON CLUSTER cluster]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>');

        Syntax for versions 22.8 and later

        CREATE TABLE <table_name> [ON CLUSTER cluster]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('https://<BucketName>.<oss-endpoint>/<file-name>', '<access-key-id>', '<access-key-secret>', '<file-format-name>');

        Parameters:

        Parameter

        Description

        table_name

        The name of the table.

        ON CLUSTER

        Creates a local table on each node. Set this parameter to ON CLUSTER default.

        col_name1,col_name2

        The names of the columns.

        col_type1,col_type2

        The data types of the columns.

        Important

        The schema of the OSS external table must match the data in OSS.

        BucketName

        The name of the bucket.

        oss-endpoint

        The endpoint used to access OSS. For more information, see Regions and endpoints.

        Important

        Ensure the bucket and your Alibaba Cloud ClickHouse cluster are in the same region, and access OSS over a Virtual Private Cloud (VPC).

        file-name

        The name of the file.

        access-key-id

        The AccessKey ID used to access the data in OSS. For information about how to obtain an AccessKey ID, see Obtain an AccessKey pair.

        access-key-secret

        The AccessKey Secret used to access the data in OSS. For information about how to obtain an AccessKey Secret, see Obtain an AccessKey pair.

        oss-file-path

        The storage path of the file. The path is typically in the oss://<bucket-name>/<path-to-file> format.

        Note

        The oss-file-path parameter supports fuzzy matching by using wildcards. For more information, see Wildcard matching for OSS storage paths.

        file-format-name

        The format of the file. This topic uses CSV as an example.

        Example statements:

        Example for versions earlier than 22.8

        CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://testBucketName/test.csv', 'CSV');

        Example for versions 22.8 and later

        CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'STS.****************', 'STS.****************','CSV')

      2. Import data from the OSS external table oss_test_tbl to the distributed table oss_test_tbl_distributed.

        Note

        If you need to import data only to a local table, replace the distributed table name with the local table name in the INSERT statement.

        INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl;

        If your CSV file uses a delimiter other than a comma (,), use the format_csv_delimiter setting in the INSERT statement to specify it. For example, if the file uses a vertical bar (|) as the delimiter, run the following statement:

        INSERT INTO oss_test_tbl_distributed SELECT * FROM oss_test_tbl settings format_csv_delimiter='|';

      Method 2: Use a table function

      Syntax for versions earlier than 22.8

      INSERT INTO <table_name> SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');

      Syntax for versions 22.8 and later

      INSERT INTO <table_name> SELECT * FROM oss('https://<BucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

      For more information about the parameters, see Parameter descriptions.

      Example statements:

      Example for versions earlier than 22.8

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://testBucketName/test.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

      Example for versions 22.8 and later

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'STS.****************', 'STS.****************', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

      If your CSV file uses a delimiter other than a comma (,), use the format_csv_delimiter setting in the INSERT statement to specify it. For example, if the file uses a vertical bar (|) as the delimiter, run the following statement:

      INSERT INTO oss_test_tbl_distributed SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>',  '<col_name> <col_type>(,...)') settings format_csv_delimiter='|';

    5. Query the distributed table oss_test_tbl_distributed to verify the data import.

      SELECT * FROM oss_test_tbl_distributed;

      Expected result:

      ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│  1 │  yang     │   32  │  shanghai  │  http://example1.com  │
│  2 │  wang     │   22  │  beijing   │  http://example2.com  │
│  3 │  xiao     │   23  │  shenzhen  │  http://example3.com  │
│  4 │  jess     │   45  │  hangzhou  │  http://example4.com  │
│  5 │  jack     │   14  │  shanghai  │  http://example5.com  │
│  6 │  tomy     │   25  │  hangzhou  │  http://example6.com  │
│  7 │  lucy     │   45  │  shanghai  │  http://example7.com  │
│  8 │  tengyin  │   26  │  shanghai  │  http://example8.com  │
│  9 │  wangli   │   27  │  shenzhen  │  http://example9.com  │
│ 10 │  xiaohua  │   37  │  shanghai  │  http://example10.com │
└────┴───────────┴───────┴────────────┴───────────────────────┘

    Wildcard matching for OSS storage paths

    To simplify the analysis of multiple small files that share a naming convention, the oss-file-path parameter supports the following wildcards:

    • *: Matches any file or directory name. For example, /dir/* matches all files in the /dir directory.

    • {x,y,z}: Matches any value enclosed in the braces. For example, file_{x,y,z} matches file_x, file_y, or file_z.

    • {num1..num2}: Matches any value in the expanded range from num1 to num2. For example, file_{1..3} matches file_1, file_2, and file_3.

    • ?: Matches any single character. For example, file_? matches file_a, file_b, file_c, and so on.

    Example

    The uploaded files use the following directory structure.

    oss://testBucketName/
               doc-data/
                    oss-import/
                        small_files/
                            access_log_csv_1.txt
                            access_log_csv_2.txt
                            access_log_csv_3.txt

    Examples:

    • oss://testBucketName/doc-data/oss-import/small_files/*

    • oss://testBucketName/doc-data/oss-import/small_files/access*

    • oss://testBucketName/doc-data/oss-import/small_files/access_log_csv_{1,2,3}.txt

    • oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1,2,3}.txt

    • oss://testBucketName/doc-data/oss-import/*/*

    • oss://testBucketName/doc-data/oss-import/*/access_log_csv_{1..3}.txt

    • oss://testBucketName/doc-data/oss-import/*/access_log_csv_?.txt