本节主要介绍如何在云数据库ClickHouse(内核版本为20.3.10.75)中使用阿里云MaxCompute外表方式导入数据。

基本语法

登录ClickHouse目标数据库,创建外部表。语法如下:
CREATE TABLE <table_name> [on cluster default]
(
'col_name' col_type,[col_name col_type]
)
ENGINE = MaxCompute('<tunnel-endpoint>', '<project-name>', '<table-name>', '<partition-spec>', '<access-key-id>', '<access-key-secret>', '<read-thread-num>');
参数名 描述
table_name 自定义外部表名。
'col_name' col_type 列名,列类型。建表时,ClickHouse的列类型,应该与MaxCompute列类型具有对应关系,请参见类型映射
ENGINE = MaxCompute 表示该表是外部表,使用的存储引擎是MaxCompute。
tunnel-endpoint MaxCompute的EndPoint(域名节点)。
说明 为了确保ClickHouse后端节点与您的MaxCompute服务之间网络通畅, tunnel-endpoint必须是VPC网络类型的endpoint,且MaxCompute必须与您的ClickHouse实例在同一地域,比如都在上海Region。查看MaxCompute tunnel endpoint地址请参见 配置Endpoint
project-name MaxCompute中数据源所在的项目名。
说明 必须完全与MaxCompute中的名称保持一致。
table-name MaxCompute中数据源所在的表名。
说明 必须完全与MaxCompute中的名称保持一致。
partition-spec 希望访问的MaxCompute分区。需要指定特定分区,在多个分区表达式之间不能有空格,且不要包含单引号,示例: sale_date=2020-09-01,region=beijing 。
access-key-id 您在访问MaxCompute上的数据源时所持有的AccessKey ID。
说明 必须对MaxCompute中目标数据相应的项目、表有读取权限。
access-key-secret 您在访问MaxCompute上的数据源时所持有的AccessKey Secret。
read-thread-num(可选) 读取MaxCompute表时单个shard的并发数,默认是1。

示例

例如MaxCompute中有如下类型的表结构:

CREATE TABLE IF NOT EXISTS data
(
    v1  TINYINT                  ,
    v2  SMALLINT                 ,
    v3  INT                      ,
    v4  BIGINT                   ,
    v5  FLOAT                    ,
    v7  DOUBLE                   ,
    v8  DECIMAL(38,18)           ,
    v9  VARCHAR(20)              ,
    v10 CHAR(20)                 ,
    v11 STRING                   ,
    v13 DATETIME                 ,
    v14 DATE                     ,
    v15 TIMESTAMP                ,
    v16 BOOLEAN                  ,
    v18 ARRAY<STRING>            ,
    v19 ARRAY<INT>               ,
    v20 MAP<STRING,INT>          ,
    v21 STRUCT<A1:STRING, A2:INT>
) 
PARTITIONED BY
(
    v17 STRING                   
)
LIFECYCLE 100;

对应可以创建ClickHouse中的外表:

CREATE TABLE default.odpsTable ON CLUSTER default
(
    `v1` Int8, 
    `v2` Nullable(Int16), 
    `v3` Nullable(Int32), 
    `v4` Nullable(Int64), 
    `v5` Nullable(Float32), 
    `v7` Nullable(Float64), 
    `v8` Nullable(String), 
    `v9` Nullable(String), 
    `v10` Nullable(String), 
    `v11` Nullable(String), 
    `v13` Nullable(Datetime), 
    `v14` Nullable(Date), 
    `v16` Nullable(UInt8), 
    `v18` Array(Nullable(String)), 
    `v19` Array(Nullable(Int32)), 
    `v20` Nested(
    aa Nullable(String), 
    bb Nullable(Int32)), 
    `v21` Tuple(Nullable(String), Nullable(Int32))
)
ENGINE = MaxCompute('http://dt.cn-hangzhou.maxcompute.aliyun-inc.com', '*', 'data', 'v17=2020', '*', '*', 3)

创建目标表(及目标的分布式表):

CREATE TABLE default.odpsTableData ON CLUSTER default
(
    `v1` Int8, 
    `v2` Nullable(Int16), 
    `v3` Nullable(Int32), 
    `v4` Nullable(Int64), 
    `v5` Nullable(Float32), 
    `v7` Nullable(Float64), 
    `v8` Nullable(String), 
    `v9` Nullable(String), 
    `v10` Nullable(String), 
    `v11` Nullable(String), 
    `v13` Nullable(Datetime), 
    `v14` Nullable(Date), 
    `v16` Nullable(UInt8), 
    `v18` Array(Nullable(String)), 
    `v19` Array(Nullable(Int32)), 
    `v20` Nested(
    aa Nullable(String), 
    bb Nullable(Int32)), 
    `v21` Tuple(Nullable(String), Nullable(Int32))
)
ENGINE = MergeTree ORDER BY v1;


CREATE TABLE default.odpsTableData_dist ON CLUSTER default as odpsTableData ENGINE = Distributed(default, default, odpsTableData, rand());

将外部表中数据导入目标表:

insert into odpsTableData_dist select * from odpsTable;

类型映射

MaxCompute类型 ClickHouse类型
boolean UInt8
tinyint UInt8, Int8
smalllint UInt16, Int16
int UInt32, Int32
bigint UInt64, Int64
float Float32
double Float64
char String
varchar String
binary String
string String
date Date
datetime Datetime
UUID 暂不支持
INTERVAL 暂不支持
Decimal String
Timestamp 暂不支持
map Nested(Nullable(keyType), Nullable(valueType))目前只支持一层非嵌套的map结构。

例如MaxCompute中map(string, string)对应ClickHouse中的类型是:Nested(k: Nullable(String), v Nullable(String))。这里的k、v可以是任意名称。

说明 Nested内部的类型必须是Nullable,否则建表会报错。
array Array(Nullable(Type)) 目前只支持一层非嵌套的array结构。
例如MaxCompute中array(int)对应ClickHouse中的类型是:Array(Nullable(Int32))。
说明 Array内部的类型必须是Nullable,否则建表会报错。
struct Tuple(Nullable(Type)) 目前只支持一层非嵌套的struct结构,也就是说struct内部必须是基本数据类型。
例如MaxCompute中struct<x:int, y:string>对应ClickHouse中的类型是:Tuple(Nulable(Int32), Nullable(String))。这里丢失了原MaxCompute中属性的名字,但是可以按照字段顺序通过ClickHouse中的Tuple的用法一一取得。比如有字段va是Tuple类型,可以通过va.1取第一个属性,va.2取第二个属性,其他属性也可以依次取得。
说明 Tuple内部的类型必须是Nullable,否则建表会报错。