通过MaxCompute外表方式导入数据（20.3.10.75）

基本语法

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

示例

例如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;

类型映射