Hologres：外部表格自動載入（Auto Load）

應用情境

Hologres與雲原生MaxCompute、阿里雲資料湖構建（Data Lake Formation，DLF）和阿里雲Object Storage Service（Object Storage Service，OSS）深度相容，無需資料搬遷，即可通過外部表格加速查詢儲存於MaxCompute或OSS的資料。當需要加速的外部表格較多時，您可以通過自動載入功能自動同步MaxCompute和DLF中繼資料，自動建立Hologres外部表格，降低手動建立外部表格的成本。

• 外部表格按需載入：主要適用於資料來源表數量較少且需要加速查詢的情境。當此功能開啟後，Hologres在查詢MaxCompute或OSS中的同名表時，會自動建立相應的Hologres外部表格，以加速資料查詢。

  說明

  • 當Hologres自動載入相應MaxCompute或OSS的外部表格時，如果Hologres內部已經存在同名的Schema和Table，自動載入功能將不會觸發，而是會查詢Hologres的內部表。

  • 由於自動載入時會建立相應的外部表格，因此要求查詢的帳號必須具備在對應資料庫中建立和刪除Schema及Table的許可權。但如果外部表格已經通過自動載入建立完成，那麼只需要查詢許可權就能進行後續的操作。

  • 該功能僅在查詢時觸發外部自動載入，不會周期性載入。

• 外部表格全量載入：主要適用於資料來源表數量較多或多個資料來源，且需要加速查詢的情境。在此功能開啟後，查詢時系統會自動建立與資料來源匹配的外部表格，從而實現所有資料來源表的全面映射。此外，一旦資料全量載入完成，可通過參數設定定期檢查，確保在查詢時能自動建立新添加的外部表格。這最佳化了對大量外部表格的管理，特別適用於需要提升BI查詢效率的環境。

功能簡介

• 自Hologres V1.1.43版本開始，支援MaxCompute外部表格自動載入，此時僅支援MaxCompute兩層模型資料來源。

• 自Hologres V1.3.28版本開始，為了降低自動巡檢的系統資源消耗，外部表格全量載入的周期性巡檢hg_experimental_load_all_foreign_table_interval_time參數預設值由5 min變為30 min。

• 自Hologres V2.2.1版本開始，支援以下功能。若您的執行個體為V2.1或以下版本，可聯絡Hologres支援人員升級執行個體。

  • 外部表格自動載入新增hg_experimental_load_foreign_table_mode = ['query' | 'period']來控制外部表格自動載入的模式。

  • 外部表格自動載入支援MaxCompute三層模型專案資料。MaxCompute三層模型詳情請參見Schema操作。

  • 外部表格自動載入支援MaxCompute外部表格的Schema Evolution（如增加列、刪除列、修改列名及列順序）。

    針對已載入的Hologres外部表格，如果外部資料源有表結構變更，可使用如下GUC來開啟外部表格Schema Evolution檢查。 開啟後，在查詢時會自動更新MaxCompute外部表格中繼資料。該GUC建議僅在Session層級按需開啟，不要DB層級開啟，否則會產生大量表結構變更檢查作業，增大系統壓力。

    set hg_experimental_enable_auto_load_check_schema_evolution = on;

    說明

    外部表格自動載入暫不支援OSS外部表格的Schema Evolution。

  • 外部表格自動載入支援通過DLF中繼資料自動載入，來加速查詢儲存於OSS的資料，詳情請參見OSS資料湖加速。

使用限制

• 使用外部表格自動載入時，MaxCompute和OSS資料來源映射參數不能以hg_或者holo_開頭，這些是Hologres的保留關鍵字。

• 每次查詢最多隻能自動載入6張表，即一個SQL中最多包含6張表。若超過6張表，自動載入將失敗，需要重新查詢以便自動載入外部表格。

• 外部表格自動載入時，如果MaxCompute表中包含Hologres外部表格目前不支援的資料類型，那麼該外部表格將無法自動建立，您需要使用CREATE FOREIGN TABLE手動選擇支援的欄位來建立外部表格。

• 如使用Auto Load自動載入OSS資料，需通過執行CREATE USER MAPPING命令，為訪問帳號（包括阿里雲帳號）指定訪問DLF和OSS的Access Key、Access Secret。

• 如您同時載入多個外部資料源的表到同一個Hologres Schema，後建立的外部表格會覆蓋先建立的同名外部表格，請將不同的資料來源關聯到不同的Hologres Schema。

• 通過Auto Load自動載入外部表格時，指定了在Hologres中不存在的Schema，系統可自動建立該Schema，但要求使用者需具有db_admin許可權。

• 外部表格全量自動載入情境下：

  • 不建議設定自動載入時間小於5分鐘。

  • 不建議對錶超過1000張的外部資料源開啟全量自動載入。

操作步驟

開啟自動載入

• 文法

  -- 開啟自動載入
  ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = on;

• 參數說明

  database name：Hologres資料庫名稱。

（可選）設定自動載入模式

從Hologres V2.2.1版本開始，您可以根據實際業務需求設定自動載入模式，若您未設定自動載入模式，則預設採用外部表格按需載入模式。Hologres V2.2.1以下版本無需設定。

• 文法

  SET hg_experimental_load_foreign_table_mode = ['query' | 'period']

• 取值說明如下：

  • query（預設值）：外部表格按需載入。

    -- 設定外部表格載入模式為按需載入
    SET hg_experimental_load_foreign_table_mode = 'query';

  • period：外部表格全量載入。

    -- 設定外部表格載入模式為全量載入
    SET hg_experimental_load_foreign_table_mode = 'period';

配置映射關係

• 文法

  • 配置MaxCompute映射關係

    • Hologres V2.2.1及以上版本

      --MaxCompute專案為三層模型
      ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<odps_foreign_server>.<mc_project>.<mc_schema>, [...]';
      --MaxCompute專案為兩層模型
      ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<odps_foreign_server>.<mc_project>, [...]';

    • Hologres V2.2.1以下版本

      ALTER DATABASE <database> SET hg_experimental_default_odps_project_list='<odps_project_name_1>,<odps_project_name_2>...';

  • 配置DLF映射關係

    --DLF Default Catalog
    ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<dlf_foreign_server>.<dlf_database> [...]';
    -- 自訂Catalog
    ALTER DATABASE <database> SET hg_experimental_auto_load_foreign_schema_mapping = '<hologres_schema>:<dlf_foreign_server>.<dlf_catalog>.<dlf_database>, [...]';

• 參數說明

  參數名稱	說明
  database	Hologres資料庫名稱。
  hologres_schema	Hologres Schema名稱。
  mc_project	需要自動載入的MaxCompute專案名稱。您可以登入MaxCompute控制台，在工作區 > 專案管理頁面擷取MaxCompute專案名稱。
  odps_foreign_server	訪問MaxCompute專案使用的server，固定為odps_server。
  odps_project_name	odps_project_name為MaxCompute的專案名稱，支援設定多重專案，中間使用逗號隔開即可。 預設值為空白，即不周期性地載入任何MaxCompute專案中表的中繼資料。 說明 僅Hologres V2.2.1以下版本需配置此參數。
  mc_schema	MaxCompute的Schema名稱。 說明 僅已開啟三層模型的專案需配置此參數，關於Schema詳情，請參見Schema操作。
  dlf_foreign_server	基於DLF_FDW建立的外部伺服器。您可以執行以下命令，擷取外部伺服器名稱。詳情請參見OSS資料湖加速。 SELECT * FROM pg_foreign_server;
  dlf_catalog	需要自動載入的DLF資料庫所在的資料目錄名。您可以登入資料湖管理主控台，選擇中繼資料 > 中繼資料管理，然後在資料目錄頁簽，擷取資料目錄名稱。
  dlf_database	需要自動載入的DLF資料庫名稱。您可以登入資料湖管理主控台，選擇中繼資料 > 中繼資料管理，然後在資料庫頁簽，擷取資料庫名稱。

查詢資料

您可直接查看相應MaxCompute和DLF中的資料。

--查詢MaxCompute專案資料
SELECT * FROM <hologres_schema>.<mc_table>;
--查詢DLF資料
SELECT * FROM <hologres_schema>.<dlf_table>;

更多操作

關閉自動載入

• 文法

  ---- 關閉自動載入
  ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = off;

  說明

  關閉自動載入後，系統將停止自動同步外部資料源的中繼資料和建立新的外部表格。您僅可在Hologres查看已經載入的外部表格和資料。如需增加更多外部表格，需要手動執行import命令，詳情請參見 IMPORT FOREIGN SCHEMA。

• 參數說明

  database name：Hologres資料庫名稱。

查看已開啟自動載入的資料來源

-- 2.2版本以前，查看已設定的MaxCompute資料來源
SHOW hg_experimental_default_odps_project_list;

-- 2.2版本以後，查看已設定的MaxCompute和DLF資料來源
SHOW hg_experimental_auto_load_foreign_schema_mapping;

設定巡檢周期

在配置指定資料來源進行全量載入後，若資料來源中新增了表，可以通過以下時間參數進行周期性巡檢。當超過設定的巡檢時間後，系統在查詢對應外部表格時會自動載入新增的表為Hologres外部表格，實現增量外部表格載入。預設間隔時間為30分鐘，即30分鐘內有新增表，在30分鐘之後查詢發起時，會自動將新增的表載入為Hologres外部表格。

• 文法

  --設定指定Hologres資料庫的巡檢周期為600 s
  ALTER DATABASE <database name> SET hg_experimental_load_all_foreign_table_interval_time = 600;

• 參數說明

  database name：Hologres資料庫名稱。

刪除已載入的外部表格。

如果不再需要加速查詢外部表格資料，執行DROP語句刪除外部表格即可，詳情請參見DROP FOREIGN TABLE。

使用樣本

樣本1：外部表格按需載入

• 兩層模型

  1. 開啟自動載入。

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  2. 配置映射關係。

     ---V2.2.1及以上版本
     ALTER DATABASE holo_demo SET hg_experimental_auto_load_foreign_schema_mapping = 'hologres_schema:odps_server.mc_project';
     ---V2.2.1以下版本
     ALTER DATABASE holo_demo SET hg_experimental_default_odps_project_list='mc_project';

  3. 查詢外部表格。

     SELECT * FROM hologres_schema.mc_table;

• 三層模型

  1. 開啟自動載入。

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  2. 配置映射關係。

     ALTER DATABASE holo_demo SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema_3layer:odps_server.mc_3_layer_project.mc_schema';

  3. 查詢外部表格。

     SELECT * FROM holo_schema_3layer.mc_table;

樣本2：外部表格全量自動載入

以DLF資料來源為例，可以通過綁定DLF Default Catalog或自訂Catalog來自動載入DLF外部表格。

• DLF Default Catalog

  為DLF Default Catalog下dlf_db資料庫設定自動載入，設定後會將該資料庫中的全部表自動建立為Hologres外部表格。同時系統會根據hg_experimental_load_all_foreign_table_interval_time配置的周期性巡檢時間，自動周期性載入這個資料庫中新增的表。

  1. 建立外部服務dlf_server並配置Endpoint資訊，詳情請參見OSS資料湖加速。

     --建立server
     CREATE SERVER IF NOT EXISTS dlf_server FOREIGN data wrapper dlf_fdw options (
         dlf_endpoint 'dlf-share.cn-beijing.aliyuncs.com',
         oss_endpoint 'oss-cn-beijing-internal.aliyuncs.com'
     );

  2. 建立使用者映射。

     Auto Load需要通過CREATE USER MAPPING命令指定訪問DLF和OSS的AccessKey ID和AccessKey Secret，詳情請參見OSS資料湖加速。

     CREATE USER MAPPING FOR <帳號uid> server <server_name> options
     (
         dlf_access_id 'accessid', 
         dlf_access_key 'accesskey',
         oss_access_id 'accessid', 
         oss_access_key 'accesskey'
     );

  3. 開啟自動載入。

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  4. 設定映射關係及載入模式。

     SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema:dlf_server.dlf_db';
     SET hg_experimental_load_foreign_table_mode = 'period'; 
     SET hg_experimental_load_all_foreign_table_interval_time = 10;

  5. 查詢外部表格資料。

     SELECT * FROM holo_schema.dlf_table;

• DLF自訂Catalog

  1. 建立外部Server並配置Endpoint資訊，詳情請參見OSS資料湖加速。

     CREATE SERVER IF NOT EXISTS DLF_server FOREIGN data wrapper dlf_fdw options (
         dlf_endpoint 'dlf-share.cn-beijing.aliyuncs.com',
         oss_endpoint 'oss-cn-beijing-internal.aliyuncs.com'
     );

  2. 建立使用者映射。

     Auto Load需要通過CREATE USER MAPPING命令指定訪問DLF和OSS的AccessKey ID和AccessKey Secret，詳情請參見OSS資料湖加速。

     CREATE USER mapping FOR <帳號uid> server <server_name> options
     (
         dlf_access_id 'accessid', 
         dlf_access_key 'accesskey',
         oss_access_id 'accessid', 
         oss_access_key 'accesskey'
     );

  3. 開啟自動載入。

     ALTER DATABASE holo_demo SET hg_experimental_enable_auto_load_foreign_table = on;

  4. 配置映射關係、載入模式以及巡檢周期。

     --配置映射關係
     SET hg_experimental_auto_load_foreign_schema_mapping = 'holo_schema:DLF_server.dlf_catalog.dlf_db';
     --載入模式為外部表格全量載入
     SET hg_experimental_load_foreign_table_mode = 'period'; 
     --巡檢周期為600 s
     SET hg_experimental_load_all_foreign_table_interval_time = 600;

  5. 查詢外部表格資料。

     SELECT * FROM holo_schema.dlf_table;