实时数仓Hologres：基于Foreign Table加速查询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中查询表是否有数据
SELECT * FROM BIGDATA_PUBLIC_DATASET.tpcds_10t.customer;

部分数据如下：

在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');

参数说明如下表所示。

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

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

SELECT * FROM customer LIMIT 10;

准备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';

部分数据如下：

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

通过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';