DocsICP FilingConsole
官方文档
首页

Import data from OSS

更新时间：
复制 MD 格式
产品详情
我的收藏

You can use a table engine or a table function to import data from Object Storage Service (OSS) to Alibaba Cloud ClickHouse to query, analyze, and process logs. This topic describes how to import data from OSS to Alibaba Cloud ClickHouse.

Prerequisites

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

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

  • Ensure 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. When you import data, the default column separator supported by Alibaba Cloud ClickHouse is a comma (,). For specific instructions on how to upload data, 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

This procedure uses a Community Edition cluster as an example.

  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.

    • If your cluster is a Community Edition cluster, select the appropriate CREATE TABLE statement based on its replica configuration. You can view the replica configuration on the Cluster Information page in the Cluster Properties section of the console. If your cluster is an Enterprise Edition cluster, see CREATE TABLE for the required statement.

    • 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.

    • You do not need to create a distributed table for Enterprise Edition clusters.

    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 provides 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>');

      The following table describes the parameters.

      Parameter

      Description

      table_name

      The name of the table.

      ON CLUSTER 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;

    The query returns the following 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

The following are examples of the oss-file-path parameter:

  • 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

Previous：Synchronize ApsaraDB RDS for MySQL to ApsaraDB for ClickHouse with zero-ETLNext：Access OSS data with an external table