基於Foreign Table加速查詢MaxCompute資料 - 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資料的按需自動載入以及全量自動載入，而無需手動改變外部表格的結構，從而提高查詢效率。詳情請參見外部表格自動載入。