通過SQL匯出資料至MaxCompute - Hologres

本文將為您介紹如何在Hologres中通過SQL方式將資料匯出至MaxCompute。

前提條件

開通MaxCompute並串連開發工具。
開通Hologres並串連開發工具，請參見串連HoloWeb並執行查詢。

使用限制

僅Hologres V0.9及以上版本支援使用SQL匯出資料至MaxCompute，如果您的執行個體是V0.9以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
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>;