文档

通过创建外部表加速查询MaxCompute数据

更新时间:

Hologres支持通过创建外部表来加速MaxCompute数据的查询,此方法允许您直接在Hologres环境中访问和分析存储在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等操作。

前提条件

注意事项

通过创建外部表加速查询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加密数据

  • 外部表不存储数据,数据存储在MaxCompute中。

  • Hologres从V1.3版本开始支持通过外部表方式查询MaxCompute的Transactional表。

  • 当使用Streaming Tunnel写入MaxCompute表时,表会首先处于streaming状态,后台异步merge为ORC格式,Hologres不支持读取处于streaming状态的表,可以暂停写入并手动执行merge后读取。

  • Hologres从V1.3版本开始支持MaxCompute的MaxCompute Schema Evolution状态表(在MaxCompute侧执行过删除列、修改列顺序和修改列类型等操作的表)。但是如果MaxCompute表是ORC格式,Hologres查询Schema Evolution的表会从直读退化成Cfile格式的SQE处理链路,性能会下降。

  • Hologres从V1.3版本开始支持读取MaxCompute的三层模型模式(即在原先的Project和Table之间增加了一层Schema的概念),暂不支持写入,更多描述请参见Schema操作

  • 从V1.3版本开始,Hologres和MaxCompute之间交互支持双签名认证,详情请参见创建Hologres外部表(双签名模式)

查询MaxCompute非分区表数据

  1. 准备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);
  2. 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您也可以在数据开发中执行该语句,并配置调度,实现更新MaxCompute表时,Hologres外部表也同步更新,详情请参见Hologres开发:周期性调度

    • Hologres仅支持加速查询MaxCompute的内部表数据,不支持加速查询MaxCompute的外部表和视图。

  3. 查询外部表数据。

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

    SELECT * FROM weather1;

查询MaxCompute分区表数据

  1. 准备MaxCompute分区表数据。

    创建一张MaxCompute分区表并导入数据,详情请参见分区和列操作。您也可以选择已创建的MaxCompute分区表。

    本实验选用数据地图已创建的分区表,示例语句如下。

    create table odps_test
    (
        shop_name     string,
        customer_id   string,
        total_price   INT 
    )
    partitioned by (sale_date string);
  2. 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的普通字段。

  3. 查询分区表数据。

    查询整张表数据,示例SQL语句如下。

    SELECT * FROM table_odps;

    查询分区表数据,示例SQL语句如下。

    SELECT * FROM table_odps 
    WHERE sale_date = '2013';

批量创建外部表

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

HoloWeb可视化创建外部表

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

  1. 进入HoloWeb页面,详情请参见连接HoloWeb

  2. 在HoloWeb开发页面的顶部菜单栏,单击元数据管理 > MaxCompute加速,单击创建外部表

    您也可以在元数据管理界面的已登录实例列表。单击目标数据库,鼠标右击数据库下已创建的目标模式,选择新建外部表

  3. 新建外部表页面,配置各项参数。

    分类

    参数

    描述

    MaxCompute 源表

    项目名

    MaxCompute的项目(Project)名称。

    Schema Name

    MaxCompute的Schema名称,对于使用两层模型的MaxCompute项目,默认不展示;对于使用三层模型的MaxCompute项目,此处可下拉选择当前项目下有权限的所有Schema。

    表名

    MaxCompute的表名称,支持基于前缀模糊搜索。

    Hologres 目标表

    数据库名

    选择外部表所在的Hologres数据库名称。

    Schema Name

    模式名称。

    您可以选择默认创建的public模式,也可以选择新建的模式名称。

    表名

    新建的Hologres外部表名称。

    选择MaxCompute表后,将会自动填入MaxCompute表名称,您也可以手动重命名。

    目标表描述

    新建的Hologres外部表描述,可自定义修改。

    说明

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

  4. 单击提交,完成外部表的创建。提交之后,您可以在左侧对应模式下,刷新出新建的外部表。

  5. (可选)表数据预览。

    1. 已登录实例列表,双击目标表。

    2. 进入表信息页签,单击数据预览,则可以预览表数据。

      数据预览

  6. (可选)DDL预览。

    在目标表信息页签,单击DDL语句,则可以预览DDL语句。ddl语句

外部表查询性能优化

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

常见问题

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

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

相关文档

您也可以在Hologres中执行MaxCompute的SQL语句,快速操作MaxCompute,详情请参见执行MaxCompute SQL语句

  • 本页导读 (1)
文档反馈