在Hologres中基於DLF讀取OSS資料 - Hologres

基於阿里雲資料湖構建（Data Lake Formation，DLF）和Object Storage Service（Object Storage Service，OSS）推出的Hologres資料湖加速服務，提供了靈活的資料訪問和分析能力以及高效的資料處理能力，顯著加快了對OSS資料湖的查詢和分析過程。本文將為您介紹在Hologres中如何通過DLF讀寫OSS資料。

前提條件

您已開通DLF資料湖構建，詳情請參見快速入門。支援開通DLF的地區請參見已開通的地區和訪問網域名稱。
您已開通OSS並準備好資料，詳情請參見開通OSS服務。
您已進行OSS授權操作，通過外部表格方式訪問OSS資料，需要訪問的帳號有OSS的相關存取權限，否則即使建立外表成功了，也無法查詢資料，OSS授權請參見Bucket Policy。
（可選）如果您需要使用OSS-HDFS功能，請開通OSS-HDFS服務，詳情請參見開通OSS-HDFS服務。

注意事項

Hologres資料匯出至OSS時僅支援執行INSERT INTO命令，不支援執行INSERT ON CONFLICT、UPDATE和DELETE命令。
僅Hologres V1.3及以上版本支援回寫資料至OSS，且僅支援ORC、Parquet、CSV、SequenceFile格式檔案。
Hologres暫不支援從引擎側自動建立OSS外部表格，如需使用INSERT INTO命令回寫資料至OSS，請在DLF中提前建立好對應的外部表格，如需匯出資料至分區表，請提前建立好對應的分區。
唯讀從執行個體暫不支援開啟資料湖加速能力。
IMPORT FOREIGN SCHEMA語句支援匯入儲存於OSS上的分區表，當前Hologres最多支援查詢512個分區，請添加分區過濾條件，使得一次查詢不超過512個分區。
湖資料查詢的原理是在運行時將外表資料的特定查詢分區載入到Hologres的記憶體和緩衝中完成計算，為不影響查詢體驗，Hologres一次查詢支援的資料量不超過200GB（經分區過濾後命中的資料量）。
不支援對外表執行UPDATE、DELETE及TRUNCATE等命令。

使用說明

環境配置

在Hologres執行個體中開啟DLF_FDW後台配置。
前往Hologres管控台執行個體列表或執行個體詳情頁，單擊目標執行個體操作列的資料湖加速並確認，後台將自動設定DLF_FDW並重啟執行個體，重啟完成即可使用該服務。
說明
Hologres控制台自助開啟DLF_FDW後台配置功能陸續開放中，如果您暫時看不到資料湖加速按鈕，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。
開啟DLF_FDW後預設使用當前系統資源（目前規格是1Core 4GB），無需額外購買資源。
建立Extension。
在Hologres中由Superuser在DB中執行以下語句建立Extension，用於開啟通過DLF讀取OSS資料的功能。該操作針對整個DB生效，一個DB只需執行一次。
```
CREATE EXTENSION IF NOT EXISTS dlf_fdw;
```

建立外部伺服器。

Hologres支援DLF資料目錄（Multi-Catalog）功能，如果您只有一個EMR叢集，則使用DLF預設資料目錄（DLF Default Catalog）即可，如果您有多個EMR叢集，可以使用自訂資料目錄來控制Hologres執行個體連結到不同的EMR叢集。同時，您也可以選擇原生OSS或者OSS-HDFS作為資料來源，具體配置如下。

使用DLF預設資料目錄和原生OSS儲存建立Server，樣本文法如下。

--查看現有server（其中meta_warehouse_server，odps_server是系統內建server，不可以修改和刪除）
SELECT * FROM pg_foreign_server;

--刪除現有server
DROP SERVER <server_name> CASCADE;

--建立server
CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_region '<region id>',
    dlf_endpoint 'dlf-share.<region id>.aliyuncs.com',
    oss_endpoint 'oss-<region id>-internal.aliyuncs.com'
);

使用OSS-HDFS作為資料湖儲存。

確定OSS-HDFS Endpoint（地區節點）
通過DLF_FDW訪問儲存在OSS-HDFS上資料，需要配置OSS-HDFS的服務網域名稱；網域名稱地址可以在OSS控制台中，已開通OSS-HDFS服務的Bucket概覽頁擷取。

建立外部Server並配置Endpoint資訊

確認Bucket網域名稱後即可在Hologres中配置DLF_FDW OSS_Endpoint選項，樣本文法如下。

CREATE EXTENSION IF NOT EXISTS dlf_fdw;

CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    dlf_region '<region id>',
    dlf_endpoint 'dlf-share.<region id>.aliyuncs.com',
    oss_endpoint '<bucket_name>.<region id>.oss-dls.aliyuncs.com' -- OSS-HDFS Bucket Endpoint 網域名稱節點
);

參數說明。

參數	說明	樣本
server_name	自訂的server名稱。	dlf_server
dlf_region	串連DLF所在的地區，請您根據地區進行選擇。華北2（北京）：`cn-beijing`。華東1（杭州）：`cn-hangzhou`。華東2（上海）：`cn-shangha`i。華南1（深圳）：`cn-shenzhen`。華北3（張家口）：`cn-zhangjiakou`。新加坡：`ap-southeast-1`。德國（法蘭克福）：`eu-central-1`。美國（維吉尼亞）：`us-east-1`。印尼（雅加達）：`ap-southeast-5`。	cn-hangzhou
dlf_endpoint	推薦使用DLF的對內服務Endpoint，可以獲得更好的訪問效能。華北2（北京）：`dlf-share.cn-beijing.aliyuncs.com`。華東1（杭州）：`dlf-share.cn-hangzhou.aliyuncs.com` 華東2（上海）：`dlf-share.cn-shanghai.aliyuncs.com`。華南1（深圳）：`dlf-share.cn-shenzhen.aliyuncs.com`。華北3（張家口）：`dlf-share.cn-zhangjiakou.aliyuncs.com`。新加坡：`dlf-share.ap-southeast-1.aliyuncs.com`。德國（法蘭克福）：`dlf-share.eu-central-1.aliyuncs.com`。美國（維吉尼亞）：`dlf-share.us-east-1.aliyuncs.com`。印尼（雅加達）：`dlf-share.ap-southeast-5.aliyuncs.com`。	`dlf-share.cn-shanghai.aliyuncs.com`
oss_endpoint	原生OSS儲存推薦使用OSS的內網Endpoint,以獲得更好的訪問效能。 OSS-HDFS目前僅支援內網訪問。	OSS `oss-cn-shanghai-internal.aliyuncs.com` OSS-HDFS `cn-hangzhou.oss-dls.aliyuncs.com`

（可選）建立使用者映射。

Hologres支援通過CREATE USER MAPPING命令指定其他使用者身份來訪問DLF和OSS，如：foreign server的Owner可以通過CREATE USER MAPPING指定RAM使用者123xxx來訪問OSS外部資料。

查詢時請確保該帳號有對應外部資料的查詢許可權。詳細原理請參見postgres create user mapping。

CREATE USER MAPPING FOR <帳號uid> SERVER <server_name> OPTIONS 
(
    dlf_access_id 'accessid', 
    dlf_access_key 'accesskey',
    oss_access_id 'accessid', 
    oss_access_key 'accesskey'
);

使用樣本：

--為目前使用者建立使用者映射
CREATE USER MAPPING FOR current_user SERVER <server_name> OPTIONS
(
    dlf_access_id 'LTAI5txxx', 
    dlf_access_key 'y8LUUyyy',
    oss_access_id 'LTAI5txxx', 
    oss_access_key 'y8LUUyyy'
);

-- 為RAM使用者123xxx建立使用者映射
CREATE USER MAPPING FOR "p4_123xxx" SERVER <server_name> OPTIONS
(
    dlf_access_id 'LIlY5txxx', 
    dlf_access_key 'KsjkXKyyy',
    oss_access_id 'LIlY5txxx', 
    oss_access_key 'KsjkXKyyy'
);

--刪除使用者映射
DROP USER MAPPING FOR current_user SERVER <server_name>;
DROP USER MAPPING FOR "p4_123xxx" SERVER <server_name>;

讀取OSS湖資料

以DLF資料來源為例，您需要在DLF中準備中繼資料表，並保證該表中已抽取資料。在Hologres中以外部表格方式通過DLF訪問OSS的資料操作步驟如下：

在Hologres執行個體中建立外部表格。

Server建立完成後，您可以在Hologres中使用CREATE FOREIGN TABLE建立外部表格或IMPORT FOREIGN SCHEMA文法單獨或者大量建立外部表格，用於讀取DLF抽取的OSS資料。

說明

如果OSS外部表格存在和Holo內表同名的表，IMPORT FOREIGN SCHEMA會跳過該外部表格的建立，請使用CREATE FOREIGN TABLE來定義一個非重複表格名來建立。

Hologres支援讀取OSS中的分區表，支援作為分區鍵的資料類型為TEXT、VARCHAR和INT。CREATE FOREIGN TABLE方式因只做欄位對應，不實際儲存資料，將分區欄位作為普通欄位來建立即可；IMPORT FOREIGN SCHEMA方式無需關心表欄位，會自動處理表欄位對應。

文法樣本

-- 方式一
CREATE FOREIGN TABLE [ IF NOT EXISTS ] oss_table_name ( [
{ column_name data_type }
  [, ... ]
  ] )
SERVER <server_name>
OPTIONS
(
  schema_name '<dlf_database_name>',
  table_name '<dlf_table_name>'
);

-- 方式二
IMPORT FOREIGN SCHEMA schema_name
    [ { limit to | except } ( table_name [, ...] ) ]
    from server <server_name>
    into local_schema
    [ options ( option 'value' [, ... ] ) ]

參數說明

參數	說明
schema_name	DLF中建立的中繼資料庫名。
table_name	DLF中建立的中繼資料表名。
server_name	Hologres中建立的Server名。
local_schema	Hologres中的Schema名。
options	IMPORT FOREIGN SCHEMA中的option參數取值，詳情請參見IMPORT FOREIGN SCHEMA。

使用樣本。

單獨建立。

建立一張外部表格映射DLF中繼資料庫dlfpro中中繼資料表dlf_oss_test的資料，該表位於Hologres中的public schema，並且檢驗是否存在該外部表格，若存在，則對已有表更新。

-- 方式一
CREATE FOREIGN TABLE dlf_oss_test_ext
(
  id text,
  pt text
)
SERVER <server_name>
OPTIONS
(
  schema_name 'dlfpro',
  table_name 'dlf_oss_test'
);

-- 方式二
IMPORT FOREIGN SCHEMA dlfpro LIMIT TO
(
  dlf_oss_test
)
FROM SERVER <server_name> INTO public options (if_table_exist 'update');

大量建立。

將DLF中繼資料庫dlfpro中所有的表都映射至Hologres的public Schema，將會在Hologres中大量建立同名外部表格。

整庫匯入。

IMPORT FOREIGN SCHEMA dlfpro
FROM SERVER <server_name> INTO public options (if_table_exist 'update');

多表匯入。

IMPORT FOREIGN SCHEMA dlfpro
(
  table1,
  table2,
  tablen
)
FROM SERVER <server_name> INTO public options (if_table_exist 'update');

資料查詢。
建立外部表格成功後，可以直接查詢外部表格讀取OSS中的資料。
- 非分區表
```
SELECT * FROM dlf_oss_test;
```
- 分區表
```
SELECT * FROM partition_table where dt = '2013';
```

下一步

若您需要將OSS資料匯入Hologres內部表直接查詢，以獲得更好的效能體驗，請參見使用SQL從資料湖匯入。
若您要將Hologres內部表資料回寫至OSS資料湖，並使用外部引擎查詢，請參見匯出至資料湖。

常見問題

建立DLF外部表格時，提示報錯ERROR: babysitter not ready,req:name:"HiveAccess"。

問題原因。
未添加後台配置。
解決方案。
請您在管理主控台的執行個體列表頁單擊資料湖加速以開啟後台配置。

樣本教程

資料湖加速查詢