ApsaraDB for ClickHouse:Import from MaxCompute (V 20.3+) via external table

Log on to the MaxCompute console.

In the left navigation pane, choose DataWorks > Data Studio.

On the Data Studio page, hover over the icon, and choose New Table > MaxCompute > Table.

In the New Table dialog box, enter a Table Name. This example uses maxcompute.

Click Create.

In the Basic Properties section, configure the parameters.

In the toolbar, click DDL.

In the DDL dialog box, enter the following statement and click Generate Table Schema.

CREATE TABLE IF NOT EXISTS maxcompute
(
    v1 INT,
    v2 INT
)
PARTITIONED BY
(
    v3 STRING
);

Click Submit to Development Environment, then click Submit to Production Environment.

On the Data Studio page, click Ad-hoc Query in the left navigation pane.

Hover over the icon, and choose New > ODPS SQL.

In the New Node dialog box, select a Path and enter a Name.

Click Confirm.

On the node editing page, enter the following statement to insert data into the partition v3='2021':

INSERT INTO maxcompute PARTITION (v3='2021') VALUES (1, 2), (2, 3);

In the toolbar, click the icon.

In the Parameters window, select shared resource groups for scheduling and click OK.

In the MaxCompute Compute Cost Estimate window, confirm the Estimated Fee and click Run.

Log on to the ApsaraDB for ClickHouse console.

On the Clusters page, click the ID of your cluster.

In the upper-right corner, click Log On to Database.

Enter the following statement and click Execute (F8):

CREATE TABLE <table_name> [ON CLUSTER default]
(
    '<col_name1>' col_type1,
    '<col_name2>' col_type2,
    ...
)
ENGINE = MaxCompute('<tunnel-endpoint>', '<project-name>', '<table-name>', '<partition-spec>', '<access-key-id>', '<access-key-secret>', <read-thread-num>, '<quota>');

The following table describes the ENGINE parameters. The following example creates a ClickHouse table that maps to the maxcompute table created in Step 1:

CREATE TABLE default.maxcomputetest ON CLUSTER default
(
    v1 Int32,
    v2 Int32
)
ENGINE = MaxCompute('http://dt.cn-hangzh******.aliyun-inc.com', 'ckfwt', 'maxcompute', 'v3=2021', 'LTAI****************', 'yourAccessKeySecret');

Log on to the ApsaraDB for ClickHouse console.

On the Clusters page, click the ID of your cluster.

In the upper-right corner, click Log On to Database.

Enter the following query and click Execute (F8):

SELECT * FROM maxcomputetest;

The query returns the data written to the MaxCompute table in Step 2.