ApsaraDB for ClickHouse:Import data from a MaxCompute external table (versions 20.3 and later)

Usage notes

• Your Alibaba Cloud ClickHouse Community-Compatible Edition cluster must run kernel version 20.3 or later.

• MaxCompute is a paid service. For billing details, see MaxCompute billing methods.

• Alibaba Cloud ClickHouse and MaxCompute must be connected through a VPC and deployed in the same region.

Prerequisites

• MaxCompute is activated. For more information, see Activate MaxCompute.

• You have created a MaxCompute project in the same region as your Alibaba Cloud ClickHouse cluster. For more information, see Create a MaxCompute project.

• If a RAM user needs to access MaxCompute, grant the required permissions to the RAM user. For more information, see Prepare a RAM user.

Procedure

1. Create a MaxCompute external table.

   1. Log on to the MaxCompute console.

   2. In the left-side navigation pane, click DataWorks > Data Development.

   3. On the Data Development page, hover over the icon and choose Create Table > MaxCompute > Tables.

   4. In the Create Table dialog box, enter a value for Table Name. This topic uses maxcompute as an example.

   5. Click New.

   6. In the Basic Properties section, configure the parameters.

      

      Parameters:

      Parameter	Description
      Display name	The display name of the table.
      Theme	In DataWorks, themes function like folders, allowing you to categorize tables by business purpose. You can define level-1 and level-2 themes to organize tables. Note These themes help you organize and find tables on the Table Management page. If no suitable themes are available, you can create one. For more information, see Define table themes.

   7. In the toolbar, click DDL.

   8. 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                   
      );

   9. Click Commit to Development Environment and then Commit to Production Environment.

2. Write data to the MaxCompute external table.

   1. On the Data Development page, click Ad Hoc Query in the left-side navigation pane.

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

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

   4. Click Confirm.

   5. On the node's editor page, enter the following statement.

      insert into maxcompute PARTITION (v3='2021') values (1,2),(2,3);

   6. Click the Run icon in the toolbar.

   7. In the Parameter dialog box, select Shared Resource Groups for Scheduling, and click OK.

   8. In the MaxCompute calculation cost estimate dialog box, confirm the Estimated cost and click Run.

3. Create an Alibaba Cloud ClickHouse table.

   1. Log on to the Alibaba Cloud ClickHouse console.

   2. On the Clusters page, click the ID of the target cluster.

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

   4. Enter the table creation statement and click Execute(F8).

      Syntax:

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

      Note

      If you use the MaxComputeRaw table engine, replace tunnel-endpoint with odps-endpoint in the syntax. The odps-endpoint parameter specifies the endpoint of the VPC. To view the VPC endpoint, see Endpoints.

      Parameters:

      Parameter	Description
      table_name	The name of the table.
      col_name1,col_name2	The names of the columns.
      col_type1,col_type2	The data types of the columns. Note The data types of the columns in the Alibaba Cloud ClickHouse table must map to the data types in the MaxCompute table. For more information, see Data type mappings.
      tunnel-endpoint	The endpoint of the Tunnel service. Alibaba Cloud ClickHouse and MaxCompute must communicate through a VPC in the same region. Note To view the tunnel-endpoint for a VPC, see Endpoints.
      project-name	The name of the MaxCompute project.
      table-name	The name of the MaxCompute external table.
      partition-spec	The partition of the MaxCompute table.
      access-key-id	The AccessKey ID used to access the MaxCompute project.
      access-key-secret	The AccessKey Secret used to access the MaxCompute project.
      read-thread-num (Optional)	The number of concurrent threads for reading a single shard of the MaxCompute table. Default value: 1.
      quota (Optional)	The computing resource unit in MaxCompute. By default, a shared quota is used.

      Example statement:

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

4. Query the Alibaba Cloud ClickHouse table.

   1. Log on to the Alibaba Cloud ClickHouse console.

   2. On the Clusters page, click the ID of the target cluster.

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

   4. Enter a query statement and click Execute(F8).

      SELECT * FROM maxcomputetest;

      Sample query result:

Data type mappings