通过SQL导出数据至MaxCompute - 实时数仓Hologres

本文将为您介绍如何在Hologres中通过SQL方式将数据导出至MaxCompute。

前提条件

开通MaxCompute并连接开发工具。
开通Hologres并连接开发工具，请参见连接HoloWeb并执行查询。

使用限制

仅Hologres V0.9及以上版本支持使用SQL导出数据至MaxCompute，如果您的实例是V0.9以下版本，请您使用自助升级或加入Hologres钉钉交流群反馈，详情请参见如何获取更多的在线支持？。
Hologres的数据类型与MaxCompute的数据类型逐一对应，但Hologres当前不支持DATE、ARRAY、MAP、STRUCT等复杂数据类型，从Hologres V1.3版本开始，支持将ARRAY和DATE类型回写至MaxCompute。其余数据类型映射请参见数据类型汇总。
Hologres从V1.1版本开始，默认支持导出至MaxCompute，如果您的Hologres实例版本低于V1.1，请在insert语句前添加以下语句。
```
--V0.9和V0.10版本需要使用以下参数
set hg_experimental_enable_write_maxcompute = on;
```
暂不支持使用SQL将数据导出至MaxCompute的Transactional表。

注意事项

在Hologres中使用SQL导出数据至MaxCompute需要注意如下事项：

支持跨区域导出至MaxCompute，但因为网络原因，同一个区域导出的性能会更好。
当前Hologres仅支持一级分区，但是可以导出至MaxCompute的二级分区，但是需要MaxCompute的分区值与Hologres字段逐一对应。同时Hologres的分区表也可以导入至MaxCompute的非分区表。
MaxCompute写入服务器数量有限，建议您避开凌晨生产作业高峰期，以获得更佳性能。
如果存在TIMESTAMPTZ类型的字段，字段的取值范围为1677-09-21 00:00:00到2262-04-12 00:00:00。

操作流程

在Hologres准备一张Hologres内部表（例如：holo_source），用于导出数据至MaxCompute。
在MaxCompute准备一张MaxCompute表用于接收数据（例如mc_sink）。
在Hologres新建一张外部表，用于映射MaxCompute表（例如 mapping_foreign_table）。
在Hologres通过SQL语句导出数据至MaxCompute。
说明
Hologres从V2.1.17版本起支持Serverless Computing能力，针对大数据量离线导入、大型ETL作业、外表大数据量查询等场景，使用Serverless Computing执行该类任务可以直接使用额外的Serverless资源，避免使用实例自身资源，无需为实例预留额外的计算资源，显著提升实例稳定性、减少OOM概率，且仅需为任务单独付费。Serverless Computing详情请参见Serverless Computing概述，Serverless Computing使用方法请参见Serverless Computing使用指南。
```
-- （可选）推荐使用Serverless Computing执行大数据量离线导入和ETL作业
SET hg_computing_resource = 'serverless';

--导出部分字段
INSERT INTO mapping_foreign_table
SELECT x,x,x FROM holo_soruce;--x,x,x可以替换为您需要导出的字段名

--导出全部字段
INSERT INTO mapping_foreign_table
SELECT * FROM holo_soruce;

-- 重置配置，保证非必要的SQL不会使用serverless资源。
RESET hg_computing_resource;
```

下面将会分非分区表和分区表两种情况来说明具体操作。

非分区表导出至MaxCompute非分区表

准备Hologres数据源表。

在Hologres中准备一张数据表，用于将数据导出至MaxCompute。示例选择已有表其DDL如下：

BEGIN;
CREATE  TABLE "public"."bank" (
 "age" int8,
 "job" text,
 "marital" text,
 "education" text,
 "card" text,
 "housing" text,
 "loan" text,
 "contact" text,
 "month" text,
 "day_of_week" text,
 "duration" text,
 "campaign" int8,
 "pdays" float8,
 "previous" float8,
 "poutcome" text,
 "emp_var_rate" float8,
 "cons_price_idx" float8,
 "cons_conf_idx" float8,
 "euribor3m" float8,
 "nr_employed" float8,
 "y" int8
);
COMMIT;

创建MaxCompute目标表。

在MaxCompute中创建一张目标表，用于接收数据，字段顺序与字段类型需要与Hologres表逐一对应。其建表语法，请参见表操作。

CREATE TABLE IF NOT EXISTS mc_bank
(
 age             BIGINT COMMENT '年龄',
 job             STRING COMMENT '工作类型',
 marital         STRING COMMENT '婚否',
 education       STRING COMMENT '教育程度',
 card            STRING COMMENT '是否有信用卡',
 housing         STRING COMMENT '房贷',
 loan            STRING COMMENT '贷款',
 contact         STRING COMMENT '联系途径',
 month           STRING COMMENT '月份',
 day_of_week     STRING COMMENT '星期几',
 duration        STRING COMMENT '持续时间',
 campaign        BIGINT COMMENT '本次活动联系的次数',
 pdays           DOUBLE COMMENT '与上一次联系的时间间隔',
 previous        DOUBLE COMMENT '之前与客户联系的次数',
 poutcome        STRING COMMENT '之前市场活动的结果',
 emp_var_rate    DOUBLE COMMENT '就业变化速率',
 cons_price_idx  DOUBLE COMMENT '消费者物价指数',
 cons_conf_idx   DOUBLE COMMENT '消费者信心指数',
 euribor3m       DOUBLE COMMENT '欧元存款利率',
 nr_employed     DOUBLE COMMENT '职工人数',
 y               BIGINT COMMENT '是否有定期存款'
);

准备一张映射到MaxCompute的Hologres外部表。

在Hologres中准备一张外表，用于将字段映射至MaxCompute，也可以通过Import Foreign Table导入MaxCompute外表。创建外表示例DDL如下：

BEGIN;
CREATE FOREIGN TABLE "public"."mapping_bank" (
 "age" int8,
 "job" text,
 "marital" text,
 "education" text,
 "card" text,
 "housing" text,
 "loan" text,
 "contact" text,
 "month" text,
 "day_of_week" text,
 "duration" text,
 "campaign" int8,
 "pdays" float8,
 "previous" float8,
 "poutcome" text,
 "emp_var_rate" float8,
 "cons_price_idx" float8,
 "cons_conf_idx" float8,
 "euribor3m" float8,
 "nr_employed" float8,
 "y" int8
)
SERVER odps_server
OPTIONS (project_name 'xxx',table_name 'mc_bank');--project_name为MaxCompute的project名，table_name为MaxCompute接收数据的表名
COMMIT;

参数说明如下。

参数	说明
project_name	MaxCompute的Project名称
table_name	MaxCompute接收数据的表名称。

导出Hologres数据至MaxCompute。

在Hologres中执行SQL语句将数据导出至MaxCompute，您可以选择导出部分字段，也可以选择导出全部字段，导出部分字段时需要保证字段顺序一致。

-- （可选）推荐使用Serverless Computing执行大数据量离线导入和ETL作业
SET hg_computing_resource = 'serverless';

--导出部分字段数据
INSERT INTO mapping_bank 
SELECT age,job FROM bank;

--导出全部字段数据
INSERT INTO mapping_bank 
SELECT * FROM bank;

-- 重置配置，保证非必要的SQL不会使用serverless资源。
RESET hg_computing_resource;

分区表导出至MaxCompute分区表

准备Hologres数据源表。

在Hologres中准备一张分区源表，用于将数据导出至MaxCompute。示例选择已有表其DDL如下：

BEGIN;
CREATE TABLE "public"."par_bank" (
 "age" int8,
 "job" text,
 "marital" text,
 "education" text,
 "default" text,
 "housing" text,
 "loan" text,
 "contact" text,
 "month" text,
 "day_of_week" text,
 "duration" text,
 "campaign" int8,
 "pdays" float8,
 "previous" float8,
 "poutcome" text,
 "emp_var_rate" float8,
 "cons_price_idx" float8,
 "cons_conf_idx" float8,
 "euribor3m" float8,
 "nr_employed" float8,
 "y" int8,
 "ds" text
)
PARTITION BY list (ds);
COMMIT;

--需要有分区子表
CREATE TABLE "public"."par_bank_20190830" PARTITION OF "public"."par_bank" FOR VALUES IN ('20190830');

CREATE TABLE "public"."par_bank_20190901" PARTITION OF "public"."par_bank" FOR VALUES IN ('20190901');

创建MaxCompute目标表。

在MaxCompute中创建一张目标表，用于接收数据，表可以是一级分区，也可以是二级分区。字段顺序与字段类型需要与Hologres表逐一对应。其建表语法，请参见表操作。

--情况1:MaxCompute表是一级分区
CREATE TABLE IF NOT EXISTS mc_par_bank
(
    age            BIGINT COMMENT '年龄',
    job            STRING COMMENT '工作类型',
    marital        STRING COMMENT '婚否',
    education      STRING COMMENT '教育程度',
    default        STRING COMMENT '是否有信用卡',
    housing        STRING COMMENT '房贷',
    loan           STRING COMMENT '贷款',
    contact        STRING COMMENT '联系途径',
    month          STRING COMMENT '月份',
    day_of_week    STRING COMMENT '星期几',
    duration       STRING COMMENT '持续时间',
    campaign       BIGINT COMMENT '本次活动联系的次数',
    pdays          DOUBLE COMMENT '与上一次联系的时间间隔',
    previous       DOUBLE COMMENT '之前与客户联系的次数',
    poutcome       STRING COMMENT '之前市场活动的结果',
    emp_var_rate   DOUBLE COMMENT '就业变化速率',
    cons_price_idx DOUBLE COMMENT '消费者物价指数',
    cons_conf_idx  DOUBLE COMMENT '消费者信心指数',
    euribor3m      DOUBLE COMMENT '欧元存款利率',
    nr_employed    DOUBLE COMMENT '职工人数',
    y              BIGINT COMMENT '是否有定期存款'
) 
PARTITIONED BY
(
    ds             STRING
);
--为一级分区添加分区
alter table mc_par_bank add if not exists partition (ds='20190830');
alter table mc_par_bank add if not exists partition (ds='20190901');


--情况二：MaxCompute表是二级分区
CREATE TABLE IF NOT EXISTS mc_par_bank_2
(
    age            BIGINT COMMENT '年龄',
    job            STRING COMMENT '工作类型',
    marital        STRING COMMENT '婚否',
    education      STRING COMMENT '教育程度',
    default        STRING COMMENT '是否有信用卡',
    housing        STRING COMMENT '房贷',
    loan           STRING COMMENT '贷款',
    contact        STRING COMMENT '联系途径',
    month          STRING COMMENT '月份',
    day_of_week    STRING COMMENT '星期几',
    duration       STRING COMMENT '持续时间',
    campaign       BIGINT COMMENT '本次活动联系的次数',
    pdays          DOUBLE COMMENT '与上一次联系的时间间隔',
    previous       DOUBLE COMMENT '之前与客户联系的次数',
    poutcome       STRING COMMENT '之前市场活动的结果',
    emp_var_rate   DOUBLE COMMENT '就业变化速率',
    cons_price_idx DOUBLE COMMENT '消费者物价指数',
    cons_conf_idx  DOUBLE COMMENT '消费者信心指数',
    euribor3m      DOUBLE COMMENT '欧元存款利率',
    nr_employed    DOUBLE COMMENT '职工人数'
) 
PARTITIONED BY
(
    y              BIGINT, 
    ds             STRING
);

alter table mc_par_bank_2 add if not exists partition (y='1',ds='20190830');
alter table mc_par_bank_2 add if not exists partition (y='1',ds='20190901');

准备一张映射到MaxCompute的Hologres外部表。

在Hologres中准备一张外表，用于将字段映射至MaxCompute。创建外表示例DDL如下：

--映射MaxCompute的一级分区表
BEGIN;
CREATE FOREIGN TABLE "public"."mapping_par_bank" (
 "age" int8,
 "job" text,
 "marital" text,
 "education" text,
 "default" text,
 "housing" text,
 "loan" text,
 "contact" text,
 "month" text,
 "day_of_week" text,
 "duration" text,
 "campaign" int8,
 "pdays" float8,
 "previous" float8,
 "poutcome" text,
 "emp_var_rate" float8,
 "cons_price_idx" float8,
 "cons_conf_idx" float8,
 "euribor3m" float8,
 "nr_employed" float8,
 "y" int8,
 "ds" text
)
SERVER odps_server
OPTIONS (project_name 'xxx',table_name 'mc_par_bank');
COMMIT;


--映射MaxCompute的二级分区表
BEGIN;
CREATE FOREIGN TABLE "public"."mapping_par_bank_2" (
 "age" int8,
 "job" text,
 "marital" text,
 "education" text,
 "default" text,
 "housing" text,
 "loan" text,
 "contact" text,
 "month" text,
 "day_of_week" text,
 "duration" text,
 "campaign" int8,
 "pdays" float8,
 "previous" float8,
 "poutcome" text,
 "emp_var_rate" float8,
 "cons_price_idx" float8,
 "cons_conf_idx" float8,
 "euribor3m" float8,
 "nr_employed" float8,
 "y" int8,
 "ds" text
)
SERVER odps_server
OPTIONS (project_name 'xxx',table_name 'mc_par_bank_2');//project_name为MaxCompute的project名，table_name为MaxCompute接收数据的表名
COMMIT;

参数说明如下。

参数	说明
project_name	MaxCompute的Project名称
table_name	MaxCompute接收数据的表名称。

导出Hologres数据至MaxCompute。

在Hologres中执行SQL语句将数据导出至MaxCompute，您可以选择导出部分字段，也可以选择导出全部字段，导出部分字段时需要保证字段顺序一致。

-- （可选）推荐使用Serverless Computing执行大数据量离线导入和ETL作业
SET hg_computing_resource = 'serverless';

--导出至MaxCompute一级分区表
--示例1：加where条件的分区父表
INSERT INTO mapping_par_bank SELECT * FROM "public"."par_bank" WHERE ds='20190830';

--示例2：直接通过分区子表导出
insert into mapping_par_bank select * from "public"."par_bank_20190901";


--导出至MaxCompute二级分区表
--示例1：加where条件的分区父表
INSERT INTO mapping_par_bank_2 SELECT * FROM "public"."par_bank" WHERE y='1' and ds='20190830';

--示例2：直接通过Hologres分区子表导出
INSERT INTO mapping_par_bank_2 SELECT * FROM "public"."par_bank_20190901" where y='1';

-- 重置配置，保证非必要的SQL不会使用serverless资源。
RESET hg_computing_resource;

常见问题

问题现象
在数据导出至MaxCompute时报错：internal error: odps_writer_xxx，UploadId: xxx, Block 0 close odps writer failed: RequestId=xxx, ErrorCode=FlowExceeded, ErrorMessage= GlobalSlot Quota。
可能原因
Hologres通过SQL导出数据至MaxCompute时，为了实现高性能导出速度，接口使用MaxCompute Tunnel SDK服务，详情请参见Tunnel命令。但Tunnel接口在使用时，具有一定的并发和数据量限制，详情请参见使用限制。当Tunnel接口处于流量高峰期时，回写任务会受到一定的写入限制，从而出现报错。

解决方法

重试数据导出任务。

重试后仍然报错，使用如下命令控制回写速率。

--控制写入MC的并发度，建议设置为[0~shard数]之间
set hg_experimental_write_maxcompute_dop =<count>;