Hologres：使用SQL匯入MaxCompute的資料至Hologres

注意事項

通過SQL匯入MaxCompute資料至Hologres時，您需要注意如下內容：

• MaxCompute的分區與Hologres無強映射關係，MaxCompute的分區欄位對應至Hologres為普通欄位，因此支援MaxCompute分區資料匯入Hologres非分區或者分區。

• Hologres僅支援一級分區，MaxCompute多級分區匯入Hologres分區表時，只需要映射一個分區，其餘分區映射成Hologres的普通欄位。

• 如果匯入資料時需要更新覆蓋原有資料，您需要使用INSERT ON CONFLICT(UPSERT)文法。

• MaxCompute與Hologres的資料類型映射，請參見資料類型匯總。

• MaxCompute的表資料更新之後，在Hologres存在緩衝延遲（一般為10分鐘內），建議在匯入資料前使用IMPORT FOREIGN SCHEMA文法更新外部表格以擷取最新資料。

• 匯入MaxCompute資料至Hologres時，建議使用SQL匯入，不建議使用Data Integration匯入，因為使用SQL匯入效能表現更優。

匯入MaxCompute的非分區表資料至Hologres並查詢

1. 準備MaxCompute的非分區表資料。

   在MaxCompute中建立一張非分區來源資料表，或直接選用已建立的非分區表。

   樣本選取MaxCompute公用資料集public_data的customer表，您可以參照使用公開資料集描述，登入並查詢資料集。其表DDL以及資料如下。

   --MaxCompute公用資料集的表DDL
   CREATE TABLE IF NOT EXISTS public_data.customer(
     c_customer_sk BIGINT,
     c_customer_id STRING,
     c_current_cdemo_sk BIGINT,
     c_current_hdemo_sk BIGINT,
     c_current_addr_sk BIGINT,
     c_first_shipto_date_sk BIGINT,
     c_first_sales_date_sk BIGINT,
     c_salutation STRING,
     c_first_name STRING,
     c_last_name STRING,
     c_preferred_cust_flag STRING,
     c_birth_day BIGINT,
     c_birth_month BIGINT,
     c_birth_year BIGINT,
     c_birth_country STRING,
     c_login STRING,
     c_email_address STRING,
     c_last_review_date STRING,
     useless STRING);
   
   --在MaxCompute中查詢表是否有資料
   SELECT * FROM public_data.customer;

   部分資料內容顯示如下圖所示。

2. 在Hologres中建立外部表格。

   在Hologres中建立一張用於映射MaxCompute來源資料表的外部表格。樣本SQL語句如下。

   CREATE FOREIGN TABLE foreign_customer (
       "c_customer_sk" int8,
       "c_customer_id" text,
       "c_current_cdemo_sk" int8,
       "c_current_hdemo_sk" int8,
       "c_current_addr_sk" int8,
       "c_first_shipto_date_sk" int8,
       "c_first_sales_date_sk" int8,
       "c_salutation" text,
       "c_first_name" text,
       "c_last_name" text,
       "c_preferred_cust_flag" text,
       "c_birth_day" int8,
       "c_birth_month" int8,
       "c_birth_year" int8,
       "c_birth_country" text,
       "c_login" text,
       "c_email_address" text,
       "c_last_review_date" text,
       "useless" text
   )
   SERVER odps_server
   OPTIONS (project_name 'public_data', table_name 'customer');

   參數	描述
   Server	您可以直接調用Hologres底層已建立的名為odps_server的外部表格伺服器。詳細原理請參見Postgres FDW。
   Project_Name	MaxCompute表所在的專案名稱。
   Table_Name	需要查詢的MaxCompute表名稱。

   外部表格欄位的資料類型與MaxCompute表欄位的資料類型保持一致，資料類型的映射關係請參見MaxCompute與Hologres的資料類型映射。

3. 在Hologres中建立儲存表。

   在Hologres中建立一張用於接收MaxCompute源表資料的儲存表。

   本樣本是初步的DDL樣本，實際匯入資料時建立表請根據業務情況設定表結構並給表設定合適的索引，以達到更優的查詢效能，更多關於表屬性的描述，請參見CREATE TABLE。

   --樣本建一張列存表
   BEGIN;
   CREATE TABLE public.holo_customer (
    "c_customer_sk" int8,
    "c_customer_id" text,
    "c_current_cdemo_sk" int8,
    "c_current_hdemo_sk" int8,
    "c_current_addr_sk" int8,
    "c_first_shipto_date_sk" int8,
    "c_first_sales_date_sk" int8,
    "c_salutation" text,
    "c_first_name" text,
    "c_last_name" text,
    "c_preferred_cust_flag" text,
    "c_birth_day" int8,
    "c_birth_month" int8,
    "c_birth_year" int8,
    "c_birth_country" text,
    "c_login" text,
    "c_email_address" text,
    "c_last_review_date" text,
    "useless" text
   );
   CALL SET_TABLE_PROPERTY('public.holo_customer', 'orientation', 'column');
   CALL SET_TABLE_PROPERTY('public.holo_customer', 'bitmap_columns', 'c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag,c_birth_country,c_login,c_email_address,c_last_review_date,useless');
   CALL SET_TABLE_PROPERTY('public.holo_customer', 'dictionary_encoding_columns', 'c_customer_id:auto,c_salutation:auto,c_first_name:auto,c_last_name:auto,c_preferred_cust_flag:auto,c_birth_country:auto,c_login:auto,c_email_address:auto,c_last_review_date:auto,useless:auto');
   CALL SET_TABLE_PROPERTY('public.holo_customer', 'time_to_live_in_seconds', '3153600000');
   CALL SET_TABLE_PROPERTY('public.holo_customer', 'storage_format', 'segment');
   COMMIT;

4. 匯入資料至Hologres。

   說明

   Hologres從V2.1.17版本起支援Serverless Computing能力，針對巨量資料量離線匯入、大型ETL作業、外表巨量資料量查詢等情境，使用Serverless Computing執行該類任務可以直接使用額外的Serverless資源，避免使用執行個體自身資源，無需為執行個體預留額外的計算資源，顯著提升執行個體穩定性、減少OOM機率，且僅需為任務單獨付費。Serverless Computing詳情請參見Serverless Computing概述，Serverless Computing使用方法請參見Serverless Computing使用指南。

   使用INSERT語句將MaxCompute源頭表中的資料匯入至Hologres，您可以選擇部分欄位匯入（欄位順序需要一一對應），也可以選擇全部欄位匯入，樣本DDL如下。

   -- （可選）推薦使用Serverless Computing執行巨量資料量離線匯入和ETL作業
   SET hg_computing_resource = 'serverless';
   
   --部分欄位資料匯入
   INSERT INTO holo_customer (c_customer_sk,c_customer_id,c_email_address,c_last_review_date,useless)
   SELECT 
       c_customer_sk,
       c_customer_id,
       c_email_address,
       c_last_review_date,
       useless
   FROM foreign_customer;
   
   --全部欄位資料匯入
   INSERT INTO holo_customer
   SELECT * FROM foreign_customer;
   
   -- 重設配置，保證非必要的SQL不會使用serverless資源。
   RESET hg_computing_resource;

5. Hologres查詢MaxCompute表資料。

   在Hologres中查詢匯入的MaxCompute表資料。樣本SQL語句如下。

   SELECT * FROM holo_customer;

匯入MaxCompute的分區表資料至Hologres並查詢

詳情請參見MaxCompute分區表資料匯入。

INSERT OVERWRITE最佳實務

詳情請參見INSERT OVERWRITE。

通過視覺化檢視或周期性調度同步資料

如果您需要一次性同步大量資料，可以通過視覺化檢視或者調度任務來實現。

• 通過視覺化檢視HoloWeb一鍵同步MaxCompute資料操作步驟如下。

  1. 進入HoloWeb頁面，詳情請參見串連HoloWeb並執行查詢。

  2. 在HoloWeb開發頁面的頂部功能表列，選擇中繼資料管理 > MaxCompute加速，單擊一鍵MaxCompute資料匯入。

  3. 配置建立MaxCompute資料匯入頁面的各項參數。參數說明如下表所示。

     說明

     SQL Script自動解析當前可視化操作對應的SQL語句。SQL Script內的SQL語句不支援修改，如果需要修改，請將SQL複製，手動更改後，使用SQL同步。

     類別	參數	描述
     選擇執行個體	執行個體名	已登入的執行個體名稱。
     MaxCompute 源表	專案名	MaxCompute的專案名稱。
     	Schema Name	MaxCompute的Schema名稱，對於使用兩層模型的MaxCompute專案，預設不展示；對於使用三層模型的MaxCompute專案，此處可下拉選擇當前專案下有許可權的所有Schema。
     	表名	MaxCompute的表名稱，支援基於首碼模糊搜尋。
     Hologres 目標表	資料庫名	選擇內部表所在的Hologres資料庫名稱。
     	Schema Name	Hologres的Schema名稱。 預設為public Schema，您也可以選擇其他有許可權的Schema。
     	表名	建立的Hologres內部表名稱。 選擇MaxCompute表後，將會自動填入MaxCompute表名稱，您也可以手動重新命名。
     	目標表描述	建立的Hologres內部表描述，可自訂修改。
     參數設定	GUC參數	輸入需要設定的GUC參數。GUC參數詳情請參見GUC參數。
     匯入設定	欄位	需要匯入的MaxCompute表欄位。 您可以選擇匯入部分或全部欄位。
     	分區配置	分區欄位 選擇分區欄位，Hologres將會預設將表建立為分區表。 Hologres僅支援一級分區。如果您需要匯入MaxCompute的多級分區，則在Hologres中設定一級分區即可，其餘分區自動對應為Hologres的普通欄位。 業務日期 如果MaxCompute表使用日期進行分區，則您可以選擇具體的分區日期，系統將會匯入指定日期的資料至MaxCompute表。
     	索引配置	儲存模式 列存，適用於各種複雜查詢。 行存，適用於基於主鍵的點查詢和Scan。 行列共存，支援行存和列存的所有情境，以及非主鍵點查的情境。 如果不指定儲存模式，則預設為列存。 表資料生命週期 表資料的生命週期。預設為永久儲存。 指定生命週期後，如果資料在指定時間內未被修改，則引擎將會在到期後的某一個時間段刪除資料。 Binlog 是否開啟Binlog，詳情請參見訂閱Hologres Binlog。 Binlog生命週期 Binlog的TTL，預設為30天，即預設值為2592000秒。 分布列 Hologres會按照分布列指定的列將資料shuffle到各個Shard，同樣的數值會在同樣的Shard中。以分布列做過濾條件時，可以大大提高執行效率。 分段列 您可以指定部分列作為分段鍵Segment_key。當查詢條件包含分段列時，您可以通過分段鍵快速尋找相應資料的儲存位置。 聚簇列 您可以指定部分列作為聚簇索引Clustering_key。索引的類型和列的順序密切相關。聚簇索引協助您加速執行索引列的Range和Filter查詢。 字典編碼列 Hologres支援為指定列的值構建字典映射。字典編碼可以將字串的比較轉換為數位比較，加速Group By和Filter查詢。 預設設定所有text列至字典編碼列中。 位元影像列 Hologres支援在位元影像列構建位元編碼。位元影像列可以根據設定的條件快速過濾欄位內部的資料。 預設設定所有text列至位元影像列中。

  4. 單擊提交，資料匯入完成後，可以在Hologres中查詢內部表資料。

• HoloWeb一鍵同步不支援周期性調度，若是需要同步大量歷史資料，或者周期性調度匯入，需要通過DataWorks的DataStudio，詳情請參見通過DataWorks周期性匯入MaxCompute資料最佳實務。

常見問題

匯入MaxCompute的資料至Hologres時發生OOM（Out Of Memory，記憶體溢出），提示超出記憶體限制異常。通常會產生Query executor exceeded total memory limitation xxxxx: yyyy bytes used報錯。導致報錯的四種可能原因及其對應的解決方案如下所示。

• 排查步驟一

  • 可能原因：

    當匯入Query包含查詢，但部分表沒有執行analyze命令；或者進行過analyze，但資料又有更新導致統計資訊不準確，最終導致查詢最佳化工具決策Join Order有誤，引起記憶體開銷過高。

  • 解決方案：

    對所有參與的內部表、外部表格執行analyze命令，更新表的統計元資訊，可以協助查詢最佳化工具產生更優的執行計畫，解決出現OOM的情況。

• 排查步驟二

  • 可能原因：

    當表的列數較多，單行資料量較大時，單次讀取的資料量會更大，引起記憶體開銷過高。

  • 解決方案：

    在SQL語句前增加以下參數來控制單次讀取資料行數，可以有效減少OOM情況。

    SET hg_experimental_query_batch_size = 1024;--預設為8192
    INSERT INTO holo_table SELECT * FROM mc_table;

• 排查步驟三

  • 可能原因：

    匯入資料的過程中，並發度高，CPU消耗大，影響內部表查詢。

  • 解決方案：

    在Hologres V1.1之前的版本中，可以通過並發度參數hg_experimental_foreign_table_executor_max_dop控制，預設為執行個體的Core數，在匯入時可設定更小的hg_experimental_foreign_table_executor_max_dop參數值，降低匯入的記憶體使用量，解決出現OOM的情況。該參數對外表執行的所有作業有效。程式碼範例如下所示。

    SET hg_experimental_foreign_table_executor_max_dop = 8;
    INSERT INTO holo_table SELECT * FROM mc_table;

• 排查步驟四

  • 可能原因：

    匯入資料的過程中，並發度高，CPU消耗大，影響內表查詢。

  • 解決方案：

    從Hologres V1.1版本開始，可以通過並發度參數hg_foreign_table_executor_dml_max_dop控制，預設為32，在匯入時設定更小的hg_foreign_table_executor_dml_max_dop參數值，降低執行DML語句的並發度（主要是資料匯入匯出情境），避免執行DML語句佔用過多資源。程式碼範例如下所示。

    SET hg_foreign_table_executor_dml_max_dop = 8;
    INSERT INTO holo_table SELECT * FROM mc_table;