通过创建外部表加速查询MaxCompute数据 - 实时数仓Hologres

本文为您介绍在Hologres中如何通过创建外部表的方式，实现MaxCompute加速查询，帮助您快速查看MaxCompute的数据。

背景信息

大数据计算服务（MaxCompute）是一种快速、完全托管的EB级数据仓库，致力于批量结构化数据的存储和计算，提供海量数据仓库的解决方案及分析建模服务。

Hologres是兼容PostgreSQL协议的实时交互式分析引擎，与MaxCompute存储原生对接，支持使用创建外部表的方式实现MaxCompute加速查询，无冗余存储，无需导入导出数据，即可快速获取查询结果，采用标准PostgreSQL协议，无缝对接几乎所有主流BI工具。

您也可以导入数据至Hologres后，再进行查询。相比其他非大数据生态产品，Hologres导入导出数据的速度性能更佳。

您可以根据业务特性和场景，选择查询方式：

在Hologres中直接查询MaxCompute的数据。
该方式适用于单次查询所需扫描底层数据量小于200 GB，且一次查询命中的分区数少于512个分区的场景。
说明
数据量小于200 GB指经过分区过滤后，命中分区的数据量大小，与查询字段的大小无关。设定扫描数据量的限制，是为了保障查询的稳定性。外表查询的原理是在运行时将MaxCompute数据的特定查询分区加载到Hologres的内存和缓存中完成计算，如果加载数据过多，会消耗更多网络带宽和计算资源，进而影响查询并发体验。
导入MaxCompute的数据至Hologres后再进行查询。
该方式无扫描数据量限制，支持复杂查询，支持索引，支持UPDATE、INSERT、DELETE等操作。

前提条件

已开通Hologres实例，详情请参见购买Hologres。
已开通MaxCompute并创建项目，详情请参见开通MaxCompute。
已为用户授予访问MaxCompute项目和表的权限，详情请参见通过命令管理用户权限。

注意事项

通过创建外部表加速查询MaxCompute数据时，您需要注意如下内容：

Hologres只能加速查询MaxCompute的内部表，不能查询MaxCompute的外部表和VIEW。
通过外部表方式加速查询MaxCompute数据，一次Query命中的数据量大小不超过200 GB，一次Query命中的分区数不超过512个。通过导入数据至Hologres内部表的方式则没有此限制。
MaxCompute的表数据更新之后，在Hologres存在缓存（一般为5分钟内）才能加速更新后的数据，如果您需要实时查询更新后的数据，可以使用IMPORT FOREIGN SCHEMA语法更新外部表元数据，就能实时查询更新后的数据。如果是数据导入场景，在V1.1.25+版本，无需手动重新刷新外部表元数据，导入语句会自动获取最新元数据。
MaxCompute的Schema更新之后，Hologres不会自动更新，需要手动更新。
MaxCompute的分区与Hologres无强映射关系，映射至Hologres之后均为普通字段。
MaxCompute与Hologres数据类型一一映射，建表时您可以查看映射关系，详情请参见数据类型汇总。
可以跨区域查询MaxCompute的数据，但是中国区域不能加速查询中国以外区域MaxCompute的数据，中国以外区域间不能相互加速查询。不建议使用跨区域查询加速，由于跨区域存在较多网络不可靠因素，查询稳定性无法保证，请保持Hologres和MaxCompute处于同一区域。
当访问MaxCompute加密表时，仅支持采用BYOK方式加密的表，详情请参见查询MaxCompute加密数据（BYOK模式）
外部表不存储数据，数据存储在MaxCompute中。
Hologres从V1.3版本开始支持MaxCompute的Transactional表作为外部表进行查询。
当使用Streaming Tunnel写入MaxCompute表时，表会首先处于streaming状态，后台异步merge为ORC格式，Hologres不支持读取处于streaming状态的表，可以暂停写入并手动执行merge后读取。
Hologres从V1.3版本开始支持MaxCompute的MaxCompute Schema Evolution状态表（在MaxCompute侧执行过删除列、修改列顺序和修改列类型等操作的表）。
Hologres从V1.3版本开始支持读取MaxCompute的三层模型模式（即在原先的Project和Table之间增加了一层Schema的概念），暂不支持写入，更多描述请参见Schema操作。
从V1.3版本开始，Hologres和MaxCompute之间交互支持双签名认证，详情请参见创建Hologres外部表（双签名模式）。

查询MaxCompute非分区表数据

准备MaxCompute非分区表数据。
创建MaxCompute非分区表并导入数据，详情请参见创建表。您也可以选择已创建的MaxCompute非分区表。
本实验选用已创建的MaxCompute表，示例SQL语句如下。
```
CREATE TABLE weather (
    city            STRING ,
    temp_lo         int,           --最低温度
    temp_hi         int           --最高温度
);
INSERT INTO weather VALUES 
('beijing',40,50),
('hangzhou',46,55);
```

Hologres创建外部表。

在Hologres中创建一张用于映射MaxCompute数据的外部表。您可以选择查询部分字段或全部字段。示例语句如下。

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

参数说明如下表所示。

参数	描述
SERVER	外部表服务器。您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。详细原理请参见Postgres FDW。
project_name	MaxCompute表所在的项目名称。
table_name	需要查询的MaxCompute表名称。

说明

：

Hologres的字段类型必须与MaxCompute的字段类型保持一致，数据类型的映射关系请参见MaxCompute与Hologres的数据类型映射。
Hologres支持使用IMPORT FOREIGN SCHEMA语句批量创建外部表，详情请参见IMPORT FOREIGN SCHEMA。
Hologres仅支持加速查询MaxCompute的内部表数据，不支持加速查询MaxCompute的外部表和视图。

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

查询MaxCompute分区表数据

准备MaxCompute分区表数据。
创建一张MaxCompute分区表并导入数据，详情请参见分区和列操作。您也可以选择已创建的MaxCompute分区表。
本实验选用数据地图已创建的分区表，示例语句如下。
```
create table odps_test
(
    shop_name     string,
    customer_id   string,
    total_price   INT 
)
partitioned by (sale_date string);
```
Hologres创建外部表。
在Hologres中创建一张用于映射MaxCompute源数据表的外部表。示例语句如下。
```
CREATE FOREIGN TABLE table_odps (
 shop_name text,
 customer_id text,
 total_price int4,
 sale_date text
)
SERVER odps_server
OPTIONS (project_name '<projectname>', table_name 'odps_test');
```
说明
Hologres的外部表仅用于字段映射，不存储数据。MaxCompute中的分区字段映射为Hologres的普通字段。
查询分区表数据。
查询整张表数据，示例SQL语句如下。
```
SELECT * FROM table_odps;
```
查询分区表数据，示例SQL语句如下。
```
SELECT * FROM table_odps 
WHERE sale_date = '2013';
```

批量创建外部表

如果您需要加速查询大批量的MaxCompute表，可以通过批量创建外部表的方式来实现。在Hologres您可以使用SQL语句或者管理控制台可视化的方式批量创建外部表。

Hologres支持使用IMPORT FOREIGN SCHEMA语句批量创建外部表，详情请参见IMPORT FOREIGN SCHEMA。
通过HoloWeb批量创建外部表，详情请参见批量创建外部表。

HoloWeb可视化创建外部表

HoloWeb提供可视化一键创建外部表功能，无需写SQL命令就能创建外部表和查看数据，步骤如下。

进入HoloWeb页面，详情请参见连接HoloWeb。
在HoloWeb开发页面的顶部菜单栏，单击元数据管理 > MaxCompute加速，单击创建外部表。
您也可以在元数据管理界面的已登录实例列表。单击目标数据库，鼠标右击数据库下已创建的目标模式，选择新建外部表。

在新建外部表页面，配置各项参数。

参数	描述
模式	模式名称。您可以选择默认创建的public模式，也可以选择新建的模式名称。
表名	新建的Hologres外部表名称。输入目标MaxCompute表名后，将会自动创建同名外部表。在创建时不支持更改表名，如果您需要更改表名，可以在外部表创建成功后，在已登录实例列表中右键单击目标表进行修改。
描述	新建的Hologres外部表描述。
类型	外部表类型。目前仅支持MaxCompute。
服务器列表	您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。详细原理请参见Postgres FDW。
表	MaxCompute的项目名和表名。格式为project.table_name。说明目前暂不支持跨地域查询外部表数据。输入表名称后，会显示外部源表的所有字段，创建外部表时也将会默认创建所有字段。如果您需要创建部分字段，请使用SQL语句创建外部表，请参见CREATE FOREIGN TABLE。

说明

创建外部表同步MaxCompute表的数据时，会将数据库中表字段的Comment和列的Comment一并同步至Hologres。

单击提交，完成外部表的创建。提交之后，您可以在左侧对应模式下，刷新出新建的外部表。
（可选）表数据预览。
1. 在已登录实例列表，双击目标表。
2. 进入表信息页签，单击数据预览，则可以预览表数据。
（可选）DDL预览。
在目标表信息页签，单击DDL语句，则可以预览DDL语句。

外部表查询性能优化

当外部表查询性能不满足当前查询时，您可以通过MaxCompute合并小文件，优化Hologres SQL等标准手段进行优化，以提升查询性能。从Hologres V0.10版本开始，Hologres采用全新外部表加速引擎，相比低于V0.10版本实例，查询MaxCompute表性能提升30%~100%左右。详情请参见优化MaxCompute外部表的查询性能。

常见问题

通过外部表查询MaxCompute数据时遇到的权限相关问题请参见MaxCompute权限相关。

通过外部表查询MaxCompute数据的相关常见问题请参见对接MaxCompute常见问题与诊断。