基于Foreign Table加速查询MaxCompute数据_实时数仓 Hologres(Hologres)-阿里云帮助中心

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

权限说明

加速查询MaxCompute数据，需要为用户授予访问MaxCompute项目和表的权限，详情请参见通过命令管理用户权限。

数据类型映射

MaxCompute与Hologres数据类型一一映射，建表时您可以查看MaxCompute与Hologres的数据类型映射。

方案介绍和选择

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

通过`CREATE FOREIGN TABLE`加速查询

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

您也可以使用HoloWeb可视化建表，详情请参见基于HoloWeb创建MaxCompute外部表。

示例一：查询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中查询表是否有数据
SET odps.namespace.schema=true;
SELECT * FROM BIGDATA_PUBLIC_DATASET.tpcds_10t.customer;

返回部分数据如下：

在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表名称。

外部表创建成功后，直接在Hologres中查询外部表，即可查询到MaxCompute的数据。示例语句如下。
```
SELECT * FROM customer LIMIT 10;
```
重要
若查询报错，请确保执行账号拥有MaxCompute表的Select等相关权限。详情请参见权限说明。

示例二：查询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中查询某个分区的数据
SET odps.namespace.schema=true;
SELECT * FROM BIGDATA_PUBLIC_DATASET.finance.ods_enterprise_share_trade_h WHERE ds = '20170113';

返回部分数据如下：

在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 '股东人数';

通过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表的Select等相关权限。详情请参见权限说明。

通过`IMPORT FOREIGN SCHEMA`加速查询

若您需要批量创建MaxCompute外部表，可通过IMPORT FOREIGN SCHEMA方式。更多详情介绍，请参见IMPORT FOREIGN SCHEMA。

通过`Auto Load`加速查询

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