Query and Export ClickHouse Data Using OSS External Tables - ApsaraDB for ClickHouse

ApsaraDB for ClickHouse can read and write data in Object Storage Service (OSS) without importing it into local storage, using either an OSS external table or a table function. This page covers both approaches for querying OSS data, and explains how to export or write data from ClickHouse back to OSS.

Prerequisites

Before you begin, ensure that you have:

An OSS bucket activated. See Activate OSS
A Resource Access Management (RAM) user with read and write permissions on OSS objects. See Overview of permissions and access control
A database account on your ApsaraDB for ClickHouse cluster. See Account Management

Preparations

Create an OSS bucket. See Create buckets in the console.
Important
The OSS bucket must be in the same region as your ApsaraDB for ClickHouse cluster. This page uses a bucket named ck-test-oss as an example.
Upload an object to the bucket. See Upload objects using the console. This page uses the test.csv file as an example. If you already have data in OSS, confirm that it uses a file format supported by ClickHouse — all formats are supported except Protobuf and CapnProto.

Log on to the database

Log on to the ApsaraDB for ClickHouse console.
On the Clusters page, select the Clusters of Community-compatible Edition tab or the Enterprise Edition Clusters tab, and click the ID of your cluster.
On the Cluster Information page, click Log On to Database in the upper-right corner.
In the Log On to Instance dialog box, enter the database account and password, and click Log On.

For Community-compatible Edition v21.8 or earlier, select only the Clusters of Community-compatible Edition tab in step 2.

Query OSS data

Two methods are available: an OSS external table (a persistent table definition that you create once and query repeatedly) or a table function (an inline query with no persistent table). Choose the method that fits your use case.

Method 1: Use an OSS external table

An OSS external table is a special table type in ApsaraDB for ClickHouse that lets you query objects stored in OSS directly without importing data into local storage. It separates storage from computing.

Create an OSS external table

The syntax differs depending on your cluster version.

Enterprise Edition or Community-compatible Edition v22.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>');

Community-compatible Edition v21.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>');

Parameters

Parameter	Description	Example
`table_name`	The table name.	`oss_test_tb`
`col_name`, `col_type`	Column name and type. The table schema must match the OSS data structure.	`user_name String`
`bucket-name`	The name of the OSS bucket. (v22.8+ only)	`ck-test-oss`
`oss-endpoint`	The VPC private network endpoint for the OSS bucket. The bucket and cluster must be in the same region. See Regions and endpoints.	`oss-cn-hangzhou-internal.aliyuncs.com`
`file-name`	The name of the object in the bucket. (v22.8+ only — included in the URL)	`test.csv`
`oss-file-path`	The object path in `oss://<bucket-name>/<path-to-file>` format. Supports wildcard characters for fuzzy matching. See Use wildcard characters. (v21.8 or earlier only)	`oss://ck-test-oss/test.csv`
`access-key-id`	The AccessKey ID of the RAM user used to access OSS.	`yourAccessKeyID`
`access-key-secret`	The AccessKey secret of the RAM user. Create or retrieve one at Create an AccessKey. The AccessKey secret is displayed only once at creation — store it securely.	`yourAccessKeySecret`
`file-format-name`	The file format. Must exactly match the name listed in ClickHouse file formats — names are case-sensitive.	`CSV`

Example (v22.8+)

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

Example (v21.8 or earlier)

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

Query the OSS data

SELECT * FROM oss_test_tb;

Expected output:

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

Method 2: Use a table function

Query OSS data inline without creating a persistent table. All parameters are the same as those for the OSS external table.

Enterprise Edition or Community-compatible Edition v22.8 or later

Syntax:

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

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

Community-compatible Edition v21.8 or earlier

Syntax:

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

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

Export ClickHouse data or write data to OSS

Use an OSS external table together with an INSERT INTO statement to write data to OSS. See INSERT INTO for syntax reference.

Write behavior by version

Before writing or exporting data, understand how each cluster version handles existing objects at the destination path.

Enterprise Edition or Community-compatible Edition v22.8 or later

You must set one of the following parameters. If neither is set (or both are set to 0) and an object already exists at the destination path, the operation fails.

Parameter	Behavior	Use when
`s3_truncate_on_insert=1`	Overwrites the existing object.	Full overwrite — use with caution, as the original data is deleted.
`s3_create_new_file_on_insert=1`	Creates a new object with an incremental name. The first new object is named `<original-name>0.<ext>`, the second `<original-name>1.<ext>`, and so on.	Incremental export or write — preserves existing data.

Community-compatible Edition v21.8 or earlier

Writing data to OSS always overwrites the existing object. Use this with caution.

Step 1: (Optional) Prepare data to export

Skip this step if you already have data in ClickHouse to export.

Create a local table. The following example creates a table on a single-replica cluster (Community-compatible Edition). For a dual-replica cluster (Enterprise Edition or Community-compatible Edition), see Create a table.
```
CREATE TABLE test_tb_local ON cluster default
(
  id UInt8,
  user_name String,
  age UInt16,
  city String,
  access_url String
)
ENGINE = MergeTree()
ORDER BY id;
```

Insert sample data.

INSERT INTO test_tb_local VALUES (13, 'tick', 25, 'shanghai', 'http://example.com');

(Optional) Verify the data was inserted.
```
SELECT * FROM test_tb_local;
```

Step 2: Create an OSS external table

Enterprise Edition or Community-compatible Edition v22.8 or later

Create an OSS external table that points to the destination path in OSS. Use the same syntax as described in Create an OSS external table.

Community-compatible Edition v21.8 or earlier

Create an OSS external table that points to the destination path in OSS. Use the same syntax as described in Create an OSS external table.

Step 3: Export or write data to OSS

Export ClickHouse data

Enterprise Edition or Community-compatible Edition v22.8 or later

Full overwrite (overwrites the existing object):

INSERT INTO oss_test_tb SETTINGS s3_truncate_on_insert = 1 SELECT * FROM test_tb_local;

Incremental export (preserves the existing object, creates a new one):
```
INSERT INTO oss_test_tb SETTINGS s3_create_new_file_on_insert = 1 SELECT * FROM test_tb_local;
```
After the operation completes, a new object appears in the bucket. If the destination object is test.csv, the first new object is named test0.csv, the second test1.csv, and so on. View the new object in the OSS console.

Community-compatible Edition v21.8 or earlier

Important

Exporting data overwrites the existing object. Use this with caution.

INSERT INTO oss_test_tb SELECT * FROM test_tb_local;

Write data directly to OSS

Enterprise Edition or Community-compatible Edition v22.8 or later

Overwrite the existing object:

INSERT INTO oss_test_tb SETTINGS s3_truncate_on_insert = 1 VALUES (14, 'tick', 25, 'shanghai', 'http://example.com');

Create a new object (incremental):

INSERT INTO oss_test_tb SETTINGS s3_create_new_file_on_insert = 1 VALUES (14, 'tick', 25, 'shanghai', 'http://example.com');

Community-compatible Edition v21.8 or earlier

Important

Writing data overwrites the existing object. Use this with caution.

INSERT INTO oss_test_tb SETTINGS s3_truncate_on_insert = 1 VALUES (14, 'tick', 25, 'shanghai', 'http://example.com');

What's next

If you use OSS to stage source data for migration into ClickHouse, import the data from OSS into the destination cluster. See Import data from OSS.