文档

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

更新时间:

Hologres支持通过创建外部表(包括手动创建外部表方式和外部表自动加载功能)来加速MaxCompute数据的查询,此方法允许您直接在Hologres环境中访问和分析存储在MaxCompute中的数据,从而提高查询效率并简化数据处理流程。

前提条件

注意事项

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

  • 由于跨地域查询存在较多网络不可靠因素,查询稳定性无法保障,请确保Hologres和MaxCompute处于同一地域。

  • Hologres只能加速查询MaxCompute的内部表,不能查询MaxCompute的外部表和VIEW。

  • MaxCompute的分区与Hologres无强映射关系,映射至Hologres之后均为普通字段,但可通过分区条件过滤查询。

  • 通过外部表方式加速查询MaxCompute数据,一次Query命中的数据量大小不超过200 GB,一次Query命中的分区数不超过512个。通过导入数据至Hologres内部表的方式则没有此限制。

  • 暂不支持读取MaxCompute中Map、Struct类型的数据。

  • 暂不支持读取MaxCompute侧使用DATETIME、TIMESTAMP、DECIMAL类型作为Cluster列的Range Cluster表数据。

数据类型映射

MaxCompute与Hologres数据类型一一映射,建表时您可以查看映射关系,详情请参见MaxCompute与Hologres的数据类型映射

使用说明

您可通过手动创建外部表方式或外部表自动加载功能实现MaxCompute数据的加速查询。

  • 手动创建外部表方式:适用于需要加速的外部表数量较少、且MaxCompute源表结构变更不频繁的场景。

  • 外部表自动加载功能:适用于需要加速的外部表数量较多或外部表结构变更比较频繁的场景,您可根据需要选择按需自动加载或全量自动加载。

方案一:通过手动创建外部表实现MaxCompute数据的加速查询

Hologres支持使用IMPORT FOREIGN SCHEMA命令批量创建MaxCompute外部表,也支持使用CREATE FOREIGN TABLE方式灵活创建MaxCompute外部表(可自定义表名称、自由选择列、自定义comments信息等),此处以CREATE FOREIGN TABLE方式为例为您介绍通过Hologres查询MaxCompute非分区表和分区表数据的操作步骤。

说明

批量创建MaxCompute外部表详情请参见IMPORT FOREIGN SCHEMA。同时,您也可以使用HoloWeb可视化建表,详情请参见创建Hologres外部表(映射到MaxCompute)

示例一:查询MaxCompute非分区表数据

  1. 准备MaxCompute非分区表数据。

    在MaxCompute中创建一张非分区表并导入数据。示例数据选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.tpcds_10t下的customer表,其表DDL及部分数据如下:

    --MaxCompute公共数据集的表DDL
    CREATE TABLE IF NOT EXISTS public_data.customer(
      c_customer_sk BIGINT,
      c_customer_id STRING,
      c_current_cdemo_sk BIGINT,
      c_current_hdemo_sk BIGINT,
      c_current_addr_sk BIGINT,
      c_first_shipto_date_sk BIGINT,
      c_first_sales_date_sk BIGINT,
      c_salutation STRING,
      c_first_name STRING,
      c_last_name STRING,
      c_preferred_cust_flag STRING,
      c_birth_day BIGINT,
      c_birth_month BIGINT,
      c_birth_year BIGINT,
      c_birth_country STRING,
      c_login STRING,
      c_email_address STRING,
      c_last_review_date_sk STRING);
    
    --在MaxCompute中查询表是否有数据
    SELECT * FROM BIGDATA_PUBLIC_DATASET.tpcds_10t.customer;

    部分数据如下:image

  2. 在Hologres中创建外部表。

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

    SET hg_enable_convert_type_for_foreign_table = true;
    CREATE FOREIGN TABLE customer (
        "c_customer_sk" int8,
        "c_customer_id" text,
        "c_current_cdemo_sk" int8,
        "c_current_hdemo_sk" int8,
        "c_current_addr_sk" int8,
        "c_first_shipto_date_sk" int8,
        "c_first_sales_date_sk" int8,
        "c_salutation" text,
        "c_first_name" text,
        "c_last_name" text,
        "c_preferred_cust_flag" text,
        "c_birth_day" int8,
        "c_birth_month" int8,
        "c_birth_year" int8,
        "c_birth_country" text,
        "c_login" text,
        "c_email_address" text,
        "c_last_review_date_sk" text)
    SERVER odps_server
    OPTIONS (project_name 'BIGDATA_PUBLIC_DATASET.tpcds_10t', table_name 'customer');
    

    参数说明如下表所示。

    参数

    描述

    SERVER

    外部表服务器。

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

    project_name

    • 如果您MaxCompute的Project是三层模型模式:project_name为MaxCompute的项目名称和Schema名称,格式为odps_project_name#odps_schema_name

    • 如果您MaxCompute的Project是两层模型模式:project_name为MaxCompute的项目名称。

    三层模型详情请参见Schema操作

    table_name

    需要查询的MaxCompute表名称。

  3. 通过Hologres加速查询MaxCompute表数据。

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

    SELECT * FROM customer limit 10;

示例2:查询MaxCompute分区表数据

  1. 准备MaxCompute分区表数据

    在MaxCompute中准备一张分区表并导入数据,示例数据选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.finance下的ods_enterprise_share_trade_h表,其DDL及部分数据如下:

    --公共数据集下表的DDL
    CREATE TABLE IF NOT EXISTS public_data.ods_enterprise_share_trade_h(
      code STRING COMMENT '代码',
      name STRING COMMENT '名称',
      industry STRING COMMENT '所属行业',
      area STRING COMMENT '地区',
      pe STRING COMMENT '市盈率',
      outstanding STRING COMMENT '流通股本',
      totals STRING COMMENT '总股本(万)',
      totalassets STRING COMMENT '总资产(万)',
      liquidassets STRING COMMENT '流动资产',
      fixedassets STRING COMMENT '固定资产',
      reserved STRING COMMENT '公积金',
      reservedpershare STRING COMMENT '每股公积金',
      eps STRING COMMENT '每股收益',
      bvps STRING COMMENT '每股净资',
      pb STRING COMMENT '市净率',
      timetomarket STRING COMMENT '上市日期',
      undp STRING COMMENT '未分利润',
      perundp STRING COMMENT '每股未分配',
      rev STRING COMMENT '收入同比(%)',
      profit STRING COMMENT '利润同比(%)',
      gpr STRING COMMENT '毛利率(%)',
      npr STRING COMMENT '净利润率(%)',
      holders_num STRING COMMENT '股东人数') 
      PARTITIONED BY (ds STRING) STORED AS ALIORC TBLPROPERTIES ('comment'='数据导入日期');
      
    --在MaxCompute中查询某个分区的数据
     SELECT * FROM BIGDATA_PUBLIC_DATASET.finance.ods_enterprise_share_trade_h WHERE ds = '20170113';

    部分数据如下:image

  2. 在Hologres中创建外部表。

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

    CREATE FOREIGN TABLE public.foreign_ods_enterprise_share_trade_h (
        "code" text,
        "name" text,
        "industry" text,
        "area" text,
        "pe" text,
        "outstanding" text,
        "totals" text,
        "totalassets" text,
        "liquidassets" text,
        "fixedassets" text,
        "reserved" text,
        "reservedpershare" text,
        "eps" text,
        "bvps" text,
        "pb" text,
        "timetomarket" text,
        "undp" text,
        "perundp" text,
        "rev" text,
        "profit" text,
        "gpr" text,
        "npr" text,
        "holders_num" text,
        "ds" text
    )
    SERVER odps_server
    OPTIONS (project_name 'BIGDATA_PUBLIC_DATASET#finance', table_name 'ods_enterprise_share_trade_h');
    comment on foreign table public.foreign_ods_enterprise_share_trade_h is '股票历史交易信息';
    comment on column public.foreign_ods_enterprise_share_trade_h."code" is '代码';
    comment on column public.foreign_ods_enterprise_share_trade_h."name" is '名称';
    comment on column public.foreign_ods_enterprise_share_trade_h."industry" is '所属行业';
    comment on column public.foreign_ods_enterprise_share_trade_h."area" is '地区';
    comment on column public.foreign_ods_enterprise_share_trade_h."pe" is '市盈率';
    comment on column public.foreign_ods_enterprise_share_trade_h."outstanding" is '流通股本';
    comment on column public.foreign_ods_enterprise_share_trade_h."totals" is '总股本(万)';
    comment on column public.foreign_ods_enterprise_share_trade_h."totalassets" is '总资产(万)';
    comment on column public.foreign_ods_enterprise_share_trade_h."liquidassets" is '流动资产';
    comment on column public.foreign_ods_enterprise_share_trade_h."fixedassets" is '固定资产';
    comment on column public.foreign_ods_enterprise_share_trade_h."reserved" is '公积金';
    comment on column public.foreign_ods_enterprise_share_trade_h."reservedpershare" is '每股公积金';
    comment on column public.foreign_ods_enterprise_share_trade_h."eps" is '每股收益';
    comment on column public.foreign_ods_enterprise_share_trade_h."bvps" is '每股净资';
    comment on column public.foreign_ods_enterprise_share_trade_h."pb" is '市净率';
    comment on column public.foreign_ods_enterprise_share_trade_h."timetomarket" is '上市日期';
    comment on column public.foreign_ods_enterprise_share_trade_h."undp" is '未分利润';
    comment on column public.foreign_ods_enterprise_share_trade_h."perundp" is '每股未分配';
    comment on column public.foreign_ods_enterprise_share_trade_h."rev" is '收入同比(%)';
    comment on column public.foreign_ods_enterprise_share_trade_h."profit" is '利润同比(%)';
    comment on column public.foreign_ods_enterprise_share_trade_h."gpr" is '毛利率(%)';
    comment on column public.foreign_ods_enterprise_share_trade_h."npr" is '净利润率(%)';
    comment on column public.foreign_ods_enterprise_share_trade_h."holders_num" is '股东人数';
    
  3. 通过Hologres查询MaxCompute分区表数据。

    • 查询前10条数据,SQL语句如下:

      SELECT * FROM foreign_ods_enterprise_share_trade_h limit 10;
    • 查询分区数据,示例SQL如下:

      SELECT * FROM foreign_ods_enterprise_share_trade_h 
      WHERE ds = '20170113';

方案二:通过外部表自动加载功能实现MaxCompute数据的加速查询

当实例中需要加速的外部表较多或外部表结构变更比较频繁(如在MaxCompute侧执行过删除列、修改列顺序、修改列类型等操作的表)时,您可以直接使用外部表自动加载(Auto Load)功能实现MaxCompute数据的按需自动加载以及全量自动加载,而无需手动改变外部表的结构,从而提高查询效率。详情请参见外部表自动加载