基于Foreign Table加速查询MaxCompute数据

文章目录

    • 1、简介
    • 2、通过CREATE FOREIGN TABLE加速查询
    • 3、通过IMPORT FOREIGN SCHEMA加速查询
    • 4、通过Auto Load加速查询

1、简介

Hologres支持通过创建外部表来加速MaxCompute数据的查询，此方法允许您直接在Hologres环境中访问和分析存储在MaxCompute中的数据，从 s而提高查询效率并简化数据处理流程。

权限说明

加速查询MaxCompute数据，需要为用户授予访问MaxCompute项目和表的权限。

数据类型映射

MaxCompute与Hologres数据类型一一映射。

方案介绍和选择

方案适用场景技术特点
通过CREATE FOREIGN TABLE加速查询MaxCompute数据少量表加速、部分列查询、表结构稳定。手动建表，灵活定义列和注释。
通过IMPORT FOREIGN SCHEMA加速查询MaxCompute数据批量映射整个Schema/DB级别表。自动同步整个Schema下的表结构。
通过Auto Load加速查询MaxCompute数据表数量多、表结构频繁变更（增删改列）。自动检测源表变更，支持按需/全量加载。

2、通过CREATE FOREIGN TABLE加速查询

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

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

  1. 在MaxCompute中创建一张非分区表并导入数据。本例直接选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.tpcds_10t下的customer表，作为示例数据。

    点击查看该表的DDL语句。

    -- MaxCompute公共数据集的表DDLCREATETABLEIFNOTEXISTSpublic_data.customer(c_customer_skBIGINT,c_customer_id STRING,c_current_cdemo_skBIGINT,c_current_hdemo_skBIGINT,c_current_addr_skBIGINT,c_first_shipto_date_skBIGINT,c_first_sales_date_skBIGINT,c_salutation STRING,c_first_name STRING,c_last_name STRING,c_preferred_cust_flag STRING,c_birth_dayBIGINT,c_birth_monthBIGINT,c_birth_yearBIGINT,c_birth_country STRING,c_login STRING,c_email_address STRING,c_last_review_date_sk STRING);

    运行如下命令查看示例表数据。

    --在MaxCompute中查询表是否有数据SETodps.namespace.schema=true;SELECT*FROMBIGDATA_PUBLIC_DATASET.tpcds_10t.customer;

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

    SEThg_enable_convert_type_for_foreign_table=true;CREATEFOREIGNTABLEcustomer("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的数据。示例语句如下。

    SELECT*FROMcustomerLIMIT10;

    重要

    若查询报错，请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。

示例二：查询MaxCompute分区表数据

  1. 在MaxCompute中准备一张分区表并导入数据。本例选用MaxCompute公开数据集BIGDATA_PUBLIC_DATASET.finance下的ods_enterprise_share_trade_h表，作为示例数据。

    点击查看该表的DDL语句。

    --公共数据集下表的DDLCREATETABLEIFNOTEXISTSpublic_data.ods_enterprise_share_trade_h(code STRINGCOMMENT'代码',name STRINGCOMMENT'名称',industry STRINGCOMMENT'所属行业',area STRINGCOMMENT'地区',pe STRINGCOMMENT'市盈率',outstanding STRINGCOMMENT'流通股本',totals STRINGCOMMENT'总股本(万)',totalassets STRINGCOMMENT'总资产(万)',liquidassets STRINGCOMMENT'流动资产',fixedassets STRINGCOMMENT'固定资产',reserved STRINGCOMMENT'公积金',reservedpershare STRINGCOMMENT'每股公积金',eps STRINGCOMMENT'每股收益',bvps STRINGCOMMENT'每股净资',pb STRINGCOMMENT'市净率',timetomarket STRINGCOMMENT'上市日期',undp STRINGCOMMENT'未分利润',perundp STRINGCOMMENT'每股未分配',rev STRINGCOMMENT'收入同比(%)',profit STRINGCOMMENT'利润同比(%)',gpr STRINGCOMMENT'毛利率(%)',npr STRINGCOMMENT'净利润率(%)',holders_num STRINGCOMMENT'股东人数')PARTITIONEDBY(ds STRING)STOREDASALIORC TBLPROPERTIES('comment'='数据导入日期');

    运行如下命令查看示例表数据。

    --在MaxCompute中查询某个分区的数据SETodps.namespace.schema=true;SELECT*FROMBIGDATA_PUBLIC_DATASET.finance.ods_enterprise_share_trade_hWHEREds='20170113';

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

    CREATEFOREIGNTABLEpublic.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');commentonforeigntablepublic.foreign_ods_enterprise_share_trade_his'股票历史交易信息';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."code"is'代码';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."name"is'名称';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."industry"is'所属行业';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."area"is'地区';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."pe"is'市盈率';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."outstanding"is'流通股本';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."totals"is'总股本(万)';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."totalassets"is'总资产(万)';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."liquidassets"is'流动资产';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."fixedassets"is'固定资产';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."reserved"is'公积金';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."reservedpershare"is'每股公积金';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."eps"is'每股收益';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."bvps"is'每股净资';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."pb"is'市净率';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."timetomarket"is'上市日期';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."undp"is'未分利润';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."perundp"is'每股未分配';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."rev"is'收入同比(%)';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."profit"is'利润同比(%)';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."gpr"is'毛利率(%)';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."npr"is'净利润率(%)';commentoncolumnpublic.foreign_ods_enterprise_share_trade_h."holders_num"is'股东人数';

  3. 通过Hologres查询MaxCompute分区表数据。

    • 查询前10条数据，SQL语句如下：

      SELECT*FROMforeign_ods_enterprise_share_trade_hlimit10;

    • 查询分区数据，示例SQL如下：

      SELECT*FROMforeign_ods_enterprise_share_trade_hWHEREds='20170113';

重要

若查询报错，请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。

3、通过IMPORT FOREIGN SCHEMA加速查询

若您需要批量创建MaxCompute外部表，可通过IMPORT FOREIGN SCHEMA方式。

  • 示例1：为public Schema新建一张外部表，若表存在则更新表。
IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer)FROMserver odps_serverINTOPUBLICoptions(if_table_exist'update');
  • 示例2：为public Schema批量新建外部表。
IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer,customer_address,customer_demographics,inventory,item,date_dim,warehouse)FROMserver odps_serverINTOPUBLICoptions(if_table_exist'update');
  • 示例3：新建一个testdemo Schema并批量新建外部表。
CREATEschematestdemo;IMPORTFOREIGNSCHEMApublic_dataLIMITTO(customer,customer_address,customer_demographics,inventory,item,date_dim,warehouse)FROMserver odps_serverINTOtestdemo options(if_table_exist'update');SETsearch_pathTOtestdemo;
  • 示例4：在public Schema批量创建外部表，已有外表则报错。
IMPORTFOREIGNSCHEMApublic_dataLIMITto(customer,customer_address)FROMserver odps_serverINTOPUBLICoptions(if_table_exist'error');
  • 示例5：在public Schema中批量创建外部表，已有外表则跳过该外部表。
IMPORTFOREIGNSCHEMApublic_dataLIMITto(customer,customer_address)FROMserver odps_serverINTOPUBLICoptions(if_table_exist'ignore');

4、通过Auto Load加速查询

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

应用场景

Hologres与云原生大数据计算服务MaxCompute、阿里云数据湖构建（Data Lake Formation，DLF）和阿里云对象存储（Object Storage Service，OSS）深度兼容，无需数据搬迁，即可通过外部表加速查询存储于MaxCompute或OSS的数据。当需要加速的外部表较多时，您可以通过自动加载功能自动同步MaxCompute和DLF元数据，自动创建Hologres外部表，降低手动创建外部表的成本。

  • 外部表按需加载：主要适用于数据源表数量较少且需要加速查询的场景。当此功能开启后，Hologres在查询MaxCompute或OSS中的同名表时，会自动创建相应的Hologres外部表，以加速数据查询。

    说明

    • 当Hologres自动加载相应MaxCompute或OSS的外部表时，如果Hologres内部已经存在同名的Schema和Table，自动加载功能将不会触发，而是会查询Hologres的内部表。

    • 由于自动加载时会创建相应的外部表，因此要求查询的账号必须具备在对应数据库中创建和删除Schema及Table的权限。但如果外部表已经通过自动加载创建完成，那么只需要查询权限就能进行后续的操作。

    • 该功能仅在查询时触发外部自动加载，不会周期性加载。

  • 外部表全量加载：主要适用于数据源表数量较多或多个数据源，且需要加速查询的场景。在此功能开启后，查询时系统会自动创建与数据源匹配的外部表，从而实现所有数据源表的全面映射。此外，一旦数据全量加载完成，可通过参数设置定期检查，确保在查询时能自动创建新添加的外部表。这优化了对大量外部表的管理，特别适用于需要提升BI查询效率的环境。