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

Notes

• The kernel version of your ApsaraDB for ClickHouse Community-Compatible cluster must be 20.3 or later.

• MaxCompute is a paid service. For more information about billing methods, see MaxCompute billing methods.

• To ensure a stable network connection between ApsaraDB for ClickHouse and MaxCompute, use an Alibaba Cloud VPC. The MaxCompute service and your ApsaraDB for ClickHouse cluster must be in the same region.

Prerequisites

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

• A MaxCompute project is created in the same region as your ApsaraDB for ClickHouse cluster. For more information, see Create a MaxCompute project.

• If a Resource Access Management (RAM) user needs to use MaxCompute, an Alibaba Cloud account holder must 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 navigation pane on the left, choose DataWorks > Data Studio.

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

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

   5. Click Create.

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

      

      The following table describes the parameters.

      Configuration item	Description
      Display Name	The display name of the table.
      Theme	A subject in DataWorks is equivalent to a folder. Define the level-1 and level-2 folders for the table. You can classify tables based on business purposes and attach tables of the same type to the same folder. Note The level-1 and level-2 Theme are presented as folders for table management in DataWorks to help you better manage tables. You can quickly find the current table by subject on the Table Management page. If no subject is available, you can create one. For more information, see Define table subjects.

   7. In the toolbar, click DDL.

   8. In the DDL dialog box, enter the following statement to create the table, and then click Generate Table Schema.

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

   9. Click Submit to Development Environment and Submit to Production Environment.

2. Write data to the MaxCompute external table.

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

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

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

   4. Click Confirm.

   5. On the node editing page, enter the following statement to write data to the MaxCompute external table.

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

   6. In the toolbar, click the icon.

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

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

3. Create an ApsaraDB for ClickHouse table.

   1. Log on to the ApsaraDB for ClickHouse console.

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

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

   4. Enter a statement to create the table and click Execute (F8).

      The syntax for creating a table is as follows.

      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, change tunnel-endpoint to odps-endpoint in the syntax. The odps-endpoint parameter specifies the VPC endpoint. For more information about how to obtain the VPC endpoint, see Endpoint.

      The following table describes the parameters.

      Parameter name	Description
      table_name	The table name.
      col_name1,col_name2	The column names.
      col_type1,col_type2	The column types. Note The schema of the ApsaraDB for ClickHouse table must correspond to the MaxCompute table. For more information about the data type mappings, see Data type mapping.
      tunnel-endpoint	The Tunnel Endpoint of the Tunnel service. To ensure a stable network connection between ApsaraDB for ClickHouse and MaxCompute, use an Alibaba Cloud VPC. The MaxCompute service and your ApsaraDB for ClickHouse cluster must be in the same region. Note For more information about how to view the tunnel-endpoint for the VPC, see Endpoint.
      project-name	The name of the MaxCompute project.
      table-name	The name of the MaxCompute external table.
      partition-spec	The MaxCompute partition.
      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 a MaxCompute table. The default value is 1.
      quota (Optional)	The unit of MaxCompute compute resources. By default, a shared quota is used.

      The statement to create the table is as follows.

      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 ApsaraDB for ClickHouse table.

   1. Log on to the ApsaraDB for ClickHouse console.

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

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

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

      SELECT * FROM maxcomputetest;

      The query results are as follows.

Data type mapping