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

背景信息

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

Hologres是兼容PostgreSQL协议的实时交互式分析数据仓库,在底层与MaxCompute无缝连接,支持您使用创建外部表的方式实现MaxCompute加速查询,无冗余存储,无需导入导出数据,即可快速获取查询结果。

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

您可以根据业务特性和场景,选择查询方式:
  • 在Hologres中直接查询MaxCompute的数据。
    该方式适用于查询数据量小于200 GB的场景。
    说明 数据量小于200 GB指经过分区过滤后,命中分区的数据量大小,与查询相关字段的大小无关。
  • 导入MaxCompute的数据至Hologres后再进行查询。

    该方式适用于单张表数据量大于200 GB的查询、复杂查询、包含索引的查询和涉及UPDATE及INSERT等操作的场景。

注意事项

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

  • Hologres只能加速查询MaxCompute的内部表,不能查询MaxCompute的外部表和VIEW。
  • 通过外部表方式加速查询MaxCompute数据,一次Query命中的数据量大小不超过200 GB,一次Qeury命中的分区数不超过512个。通过导入数据至Hologres内部表的方式则没有此限制。
  • MaxCompute的表数据更新之后,在Hologres存在缓存(一般为5分钟内)才能加速更新后的数据,如果您需要实时查询更新后的数据,可以使用IMPORT FOREIGN SCHEMA语法更新外部表,就能实时查询更新后的数据。
  • MaxCompute的Schema更新之后,Hologres不会自动更新,需要手动更新。
  • MaxCompute的分区与Hologres无强映射关系,映射至hologres之后均为普通字段。
  • MaxCompute与Hologres数据类型一一映射,建表时您可以查看映射关系,详情请参见数据类型汇总
  • 可以跨区域查询MaxCompute的数据,但是国内区域不能加速查询中国以外区域MaxCompute的数据,中国以外区域间不能相互加速查询。
  • 外部表不存储数据,数据还是存储在MaxCompute中。

查询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 int8,
     temp_hi int8
    )
    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 int8,
     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. 新建外部表页面,配置各项参数。单击提交表新建外部表
    参数 描述
    实例名 已登录的实例名称。
    数据库 Hologres当前已登录实例的数据库名称。
    表名 新建的Hologres外部表名称。

    输入目标MaxCompute表名后,将会自动创建同名外部表。在创建时不支持更改表名,如果您需要更改表名,可以在外部表创建成功后,在已登录实例列表中右键单击目标表进行修改。

    描述 新建的Hologres外部表描述。
    模式 模式名称。

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

    类型 外部表类型。

    目前仅支持MaxCompute。

    服务器列表 您可以直接调用Hologres底层已创建的名为odps_server的外部表服务器。详细原理请参见Postgres FDW
    MaxCompute的项目名和表名。

    格式为project.table_name

    说明
    • 目前暂不支持跨地域查询外部表数据。
    • 输入表名称后,会显示外部源表的所有字段,创建外部表时也将会默认创建所有字段。如果您需要创建部分字段,请使用SQL语句创建外部表,请参见CREATE FOREIGN TABLE
    说明 创建外部表同步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数据出现ERROR:Request denied, may caused by server busy.报错。

    • 问题原因:

      外部表资源占满,CPU用量严重超出。

    • 解决方法:
      1. 使用如下命令语句查看当前外部表的配置。
        show hg_experimental_foreign_table_executor_max_dop;
      2. 使用如下命令语句调低并发度,推荐并发度设置为当前配置的一半。
        set hg_experimental_foreign_table_executor_max_dop = <并发度>;

        并发度是指外部表单个执行节点读取外部表数据的并发度,取值范围为0 ~ 1024,默认值为256。

        说明 修改并发度可能会存在如下风险。
        • 并发度太大可能造成实例OOM ,导入、查询失败,甚至实例重启,以至于服务不可用。
        • 并发度太小会导致外部表查询、外部表导入内部表性能较差。
  • 查询分区表数据时报错。
    • 问题现象:

      使用外部表的方式加速查询MaxCompute数据,出现Build desc failed: Exceeds the partition limitation of 512, current match 586 partitions.报错。

    • 问题原因:

      查询MaxCompute分区表数据,一次Qeury命中的分区数超过最高限制512个。

    • 解决方法:
      • 使用如下命令设置分区数量为512,512是经过实践的最优的分区限制,扩大会影响查询性能。
        set hg_experimental_max_scan_partition_limit = 512;
        说明 修改最高命中分区数量可能会存在无法查询过多分区的风险。
      • 将MaxCompute分区表数据导入至Hologres再查询,将不会有分区数限制问题,详情请参见导入MaxCompute的分区表数据至Hologres并查询
  • 查询数据时报错。
    • 问题现象:

      使用外部表的方式加速查询MaxCompute数据,出现Get result failed: exec sql failed => status:7 msg:ERROR: External table size 268911MB exceeded the maximum cache size 204800MB报错。

    • 问题原因:

      查询MaxCompute数据,一次Qeury命中的数据量大小超过最高限制200 GB。

    • 解决方法: