ApsaraDB for ClickHouse:Import data from OSS

Prerequisites

• OSS is activated. For more information about how to activate OSS, see Activate OSS.

• An OSS bucket is created in the region where the ApsaraDB for ClickHouse cluster is deployed. For more information about how to create an OSS bucket, see Create buckets.

• The Alibaba Cloud account that you use to access the OSS bucket is granted the required permissions to read objects in the bucket. For more information about how to grant permissions, see Overview.

Precautions

Step 1: Upload test data to OSS

1. Prepare test data. Store the following test data in a CSV file named test.csv.

   1,yang,32,shanghai,http://example.com
   2,wang,22,beijing,http://example.com
   3,xiao,23,shenzhen,http://example.com
   4,jess,45,hangzhou,http://example.com
   5,jack,14,shanghai,http://example.com
   6,tomy,25,hangzhou,http://example.com
   7,lucy,45,shanghai,http://example.com
   8,tengyin,26,shanghai,http://example.com
   9,wangli,27,shenzhen,http://example.com
   10,xiaohua,37,shanghai,http://example.com

2. Log on to the OSS console.

3. In the left-side navigation pane, click Buckets.

4. On the Buckets page, find the OSS bucket that you want to manage and click the bucket name.

5. In the left-side navigation pane, click Files. On the page that appears, click Upload.

6. On the Upload page, upload the CSV file named test.csv.

   Note

   For more information about parameter settings, see Upload objects.

Step 2: Create a table in ApsaraDB for ClickHouse

1. Log on to the ApsaraDB for ClickHouse console.

2. On the Clusters page, click the Default Instances tab, and click the ID of the cluster that you want to manage.

3. In the upper-right corner of the cluster details page, click Log On to Database.

4. In the Log On to Database Instance dialog box, specify the Database Account and Database password parameters, and then click Login.

5. Create a local table.

   Note

   • The schema of the ApsaraDB for ClickHouse table must correspond to the schema of the CSV file that contains the data in OSS.

   • You need to execute a statement based on the edition of the cluster to create a local table. You can click Cluster Information in the left-side navigation pane, and view the edition of the cluster in the Cluster Properties section.

   Statement used to create a local table for a cluster of the Single-replica Edition

   create table oss_test_tbl_local on cluster default
   (
   id UInt8,
   user_name String,
   age UInt16,
   city String,
   ac
   cess_url String
   )
   engine = MergeTree()
   order by id;

   Statement used to create a local table for a cluster of the 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;

6. Create a distributed table.

   Note

   If you want to import the data from OSS only to the local table, skip this step.

   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());

Step 3: Import data from OSS to ApsaraDB for ClickHouse

You can use a table engine or a table function to import data from OSS to ApsaraDB for ClickHouse.

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>(,...)');

insert into oss_test_tbl_distributed select * from oss('https://<BucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

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

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

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='|';

Step 4: Query the OSS data that is imported to ApsaraDB for ClickHouse

Execute the following statement to query the data.

select * from oss_test_tbl_distributed; 

The following result is returned:

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

Use wildcard characters to perform fuzzy match for storage paths in OSS

In most cases, multiple small files in OSS are created based on the same naming conventions. To simplify the analysis of small files, you can use the following wildcard characters when you configure the oss-file-path parameter to perform fuzzy match:

• *: matches all files or directories. For example, /dir/* matches all files in the /dir directory.

• {x, y, z}: matches one of the values that are enclosed in the brace {}. For example, file_{x, y, z} matches file_x, file_y, or file_z.

• {num1..num2}: matches the smallest value, the largest value, or a value between them. For example, file_{1..3} matches file_1, file_2, or file_3.

• ?: matches a random character. For example, file_? matches file_a, file_b, file_c, or other files whose names are in the file_Variable format.

Example

Files can be uploaded to a directory that uses the following 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 list provides values of the oss-file-path parameter for reference:

• 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