Dataphin中如何使用Hologres外表查询MaxCompute

背景信息

大数据计算服务（MaxCompute）是一种快速、完全托管的EB级数据仓库，致力于批量结构化数据的存储和计算，提供海量数据仓库的解决方案及分析建模服务。Hologres是兼容PostgreSQL协议的实时交互式分析引擎，与MaxCompute存储原生对接，支持使用创建外部表的方式实现MaxCompute加速查询，无冗余存储，无需导入导出数据，即可快速获取查询结果，采用标准PostgreSQL协议，无缝对接几乎所有主流BI工具。

在 Dataphin 产品中，支持 2 种 Hologres 外表查询的方式，1、通过创建外表语句，将 MaxCompute 注册到 Hologres 中进行查询；2、将 MaxCompute 指定 project 的表全量加载到 Hologres 中，然后进行查询。

准备工作

已开通 MaxCompute 和 Hologres，创建了 MaxCompute 项目并为用户授予访问 MaxCompute 项目和表权限。同时，在 Dataphin 产品中已创建项目，并将指定 Hologres 项目 schema 指定为项目计算源。

方法一：通过创建外表语句，进行查询

在Hologres中创建一张用于映射MaxCompute数据的外部表。示例语句如下

CREATE FOREIGN TABLE weather1 (
 city text,
 temp_lo int4,
 temp_hi int4
)
SERVER odps_server
OPTIONS (project_name '<projectname>',table_name 'weather');

参数说明如下表所示

注意：这里的 project_name 是 MaxCompute 的 project 名称，而不是用户在 Dataphin 产品中创建的项目名称。Hologres的字段类型必须与MaxCompute的字段类型保持一致。Hologres仅支持加速查询MaxCompute的内部表数据，不支持加速查询MaxCompute的外部表和视图。

成功创建外部表后，您可以直接查询外部表，即可查询到MaxCompute的数据。示例语句如下。

SELECT * FROM weather1;

示例：

参考文献：通过创建外部表加速查询MaxCompute数据

方法二：将 MaxCompute 指定 project 的表全量加载到 Hologres 中

• 外部表全量自动加载场景下：

• 不建议设置自动加载时间小于5分钟。
  
• 不建议将表超过1000张的MaxCompute的项目设置为默认Project。
  
• 针对已加载的Hologres外表，如果MaxCompute中该表有表结构改变，指定Project全量加载将不会自动对该外表结构更新，需要使用import命令手动更新该表。
  

• 开启指定Project全量自动加载。

若是需要加速的MaxCompute表较多，可以开启Project全量数据自动加载。该功能需要指定MaxCompute Project名称，同时如果Project中有新增MaxComute外表，可以通过参数周期性巡检，并在查询外表时自动加载，实现Project的全量和增量加速。

使用步骤如下：

1）开启外部表自动加载。

在Project全量加载前，需要先使用如下命令开启外部表自动加载。

ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = on;

database name为需要开启MaxCompute外部表自动加载的数据库名称。

2）开启指定Project全量自动加载。

开启外部表自动加载后，通过如下参数自动将指定MaxCompute Project的所有表加载为Hologres的外部表，当查询外部表时，系统将会自动将Project中的所有表加载为Hologres外部表，实现全量加载。

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

参数说明如下。

MaxCompute项目名称为空，即不周期性的加载任何Project中表的元数据。

3）开启周期性巡检。

配置指定project全量加载后，如果Project中有新增MaxCompute表，可以通过如下时间参数进行周期性巡检。在超过设置的巡检时间后，系统会在查询对应外部表时，自动加载新增的MaxCompute为Hologres外部表，实现增量外部表加速。

解释-- 查看数据库的自动加载间隔时间
show hg_experimental_load_all_foreign_table_interval_time;
-- 修改数据库的自动加载间隔时间
ALTER DATABASE <database name> SET hg_experimental_load_all_foreign_table_interval_time = '5min';

database name为需要设置MaxCompute外部表自动加载的数据库名称。默认间隔时间5分钟，即5分钟内有新增MaxCompute表，在5分钟之后查询发起时，会自动将新增的MaxCompute表加载为Hologres外表。

4）查看自动加载Project。

您可以使用如下SQL查看已设置自动加载的Project。

show hg_experimental_default_odps_project_list;

示例：

参考文献：外部表自动加载（Auto Load）