实时数仓Hologres：外部表自动加载（Auto Load）

应用场景

Hologres与云原生大数据计算服务MaxCompute、阿里云数据湖构建（Data Lake Formation，DLF）和阿里云对象存储（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;