ApsaraDB for ClickHouse:Access OSS data with an external table

Enterprise or Community-compatible (22.8 or later)

Community-compatible (21.8 or earlier)

1. Log on to the database.

   1. Log on to the ApsaraDB for ClickHouse console.

   2. On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the target cluster.

   3. On the Cluster Information page, click Log On to Database in the upper-right corner.

   4. In the Connect to Instance dialog box, enter the database account and password, and then click Log On.

2. Create an OSS external table.

   The syntax is as follows:

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

   The following table describes the parameters.

   Parameter	Description	Example
   table_name	The name of the table.	oss_test_tb
   col_name1,col_name2	The names of the columns.	user_name
   col_type1,col_type2	The data types of the columns. Important The schema of the OSS external table must be consistent with the data in OSS.	String
   oss-endpoint	The internal endpoint for accessing the OSS bucket from an ECS instance over a VPC. For more information, see Regions and endpoints. Important Ensure that the OSS bucket and your ApsaraDB for ClickHouse cluster are in the same region.	oss-cn-hangzhou-internal.aliyuncs.com
   access-key-id	The AccessKey ID of the RAM user for accessing OSS data.	yourAccessKeyID
   access-key-secret	The AccessKey secret of the RAM user for accessing OSS data. You can use an existing AccessKey or create a new one. For more information, see Create an AccessKey. Important To reduce the risk of an AccessKey leak, the AccessKey secret is displayed only when you create it and cannot be retrieved later. Make sure that you store the AccessKey secret in a secure manner.	yourAccessKeySecret
   oss-file-path	The storage path of the object, which is typically in the oss://<bucket-name>/<path-to-file> format. Note The oss-file-path parameter supports fuzzy matching with wildcards. For more information, see Use wildcards for fuzzy matching of storage paths in OSS.	oss://ck-test-oss/test.csv
   file-format-name	The format of the object. Important The value must be a case-sensitive file format supported by ClickHouse. For more information, see file formats supported by ClickHouse.	CSV

   Example:

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

3. Query OSS data.

   SELECT * FROM oss_test_tb;

   The following result is returned:

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1           | tick                | 32            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+
   Returned rows: [1], Time elapsed: [183ms]

Enterprise or Community-compatible (22.8 or later)

1. Log on to the database.

   1. Log on to the ApsaraDB for ClickHouse console.

   2. On the Clusters page, click the Clusters of Community-compatible Edition or Enterprise Edition Clusters tab, and then click the ID of the target cluster.

   3. On the Cluster Information page, click Log On to Database in the upper-right corner.

   4. In the Connect to Instance dialog box, enter the database account and password, and then click Log On.

2. Use a table function to query data.

   The syntax is as follows:

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

   The parameters are the same as those for creating an OSS external table. For details, see Parameter descriptions.

   Example:

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

   The following result is returned:

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1           | tick                | 32            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+
   Returned rows: [1], Time elapsed: [183ms]

Community-compatible (21.8 or earlier)

1. Log on to the database.

   1. Log on to the ApsaraDB for ClickHouse console.

   2. On the Clusters page, click the Clusters of Community-compatible Edition tab, and then click the ID of the target cluster.

   3. On the Cluster Information page, click Log On to Database in the upper-right corner.

   4. In the Connect to Instance dialog box, enter the database account and password, and then click Log On.

2. Use a table function to query data.

   The syntax is as follows:

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

   The parameters are the same as those for creating an OSS external table. For details, see Parameter descriptions.

   Example:

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

   The following result is returned:

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1           | tick                | 32            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+
   Returned rows: [1], Time elapsed: [183ms]

Enterprise Edition or Community-compatible Edition 22.8 or later

When you export or write data to OSS, you must set s3_truncate_on_insert=1 or s3_create_new_file_on_insert=1. If neither parameter is set or both are set to 0, the operation fails when a file already exists in the destination path.

• s3_truncate_on_insert=1: ApsaraDB for ClickHouse checks if a file exists in the destination path.

  • If a file exists, ClickHouse overwrites the existing file with new data.

  • This method is suitable for full overwrite exports or writes. However, proceed with caution because this operation deletes the original data.

• s3_create_new_file_on_insert=1: ClickHouse checks if a file exists in the destination path.

  • If a file exists, ClickHouse creates a new file in the directory and writes data to it. The new file is named using the following pattern: target file name + a sequence number (starting from 0 and incrementing by 1) + file format name.

    For example, if the target file is test.csv, the first operation creates test0.csv and the second operation creates test1.csv.

  • This method is suitable for incremental exports or writes and avoids overwriting existing data.

Community-compatible Edition 21.8 or earlier

When exporting data or writing data, only a single OSS file is used. The new data overwrites the content of the original file. Proceed with caution.

Enterprise Edition or Community-compatible Edition 22.8 or later

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

CREATE TABLE oss_test_tb ON cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('http://ck-test-oss.oss-cn-hangzhou-internal.aliyuncs.com/test.csv', 'LTAI****************', 'yourAccessKeySecret','CSV')

Community-compatible Edition 21.8 or earlier

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

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