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

Step 1: Create an external table

Log on to your ApsaraDB for ClickHouse cluster and run the following statement, replacing the placeholder values with your own:

CREATE TABLE default.odps_src_tbl ON CLUSTER default
(
    `shop_name`    String,
    `customer_id`  String,
    `total_price`  Float64
)
ENGINE = MaxCompute(
    'http://dt.cn-shanghai.maxcompute.aliyun-inc.com',  -- VPC tunnel endpoint
    'test_project',                                      -- MaxCompute project name
    'test_tbl',                                          -- MaxCompute table name
    'sale_date=2020-09-01,region=beijing',               -- partition spec
    '<your-access-key-id>',                              -- AccessKey ID
    '<your-access-key-secret>'                           -- AccessKey secret
);

The ENGINE = MaxCompute(...) parameters are positional and must appear in this order:

Column names and data types in the external table must match the corresponding columns in the MaxCompute table. For the supported type mappings, see Data type mapping.

Step 2: Create the destination table

Create a local MergeTree table and a distributed table on top of it:

-- Local table
CREATE TABLE default.odps_dst_tbl_local ON CLUSTER default
(
    `shop_name`    String,
    `customer_id`  String,
    `total_price`  Float64
)
ENGINE = MergeTree()
ORDER BY customer_id;

-- Distributed table (used for writes and queries across the cluster)
CREATE TABLE default.odps_dst_tbl_dist ON CLUSTER default
AS default.odps_dst_tbl_local
ENGINE = Distributed(default, default, odps_dst_tbl_local, rand());

Step 3: Insert the data

Insert data from the external table into the distributed table:

INSERT INTO odps_dst_tbl_dist SELECT * FROM odps_src_tbl;

After the insert completes, query the distributed table to verify the data was imported correctly.