使用Hologres訪問DLF Catalog的Paimon資料-資料湖構建-阿里雲

Hologres自V3.2版本開始，支援通過Paimon REST訪問DLF Catalog資料，為使用者提供統一介面，提升中繼資料管理效率。

特色功能

支援通過External Database訪問DLF Catalog，詳情請參見EXTERNAL DATABASE。
- 支援通過Create/Drop External Schema實現級聯建立/刪除Catalog中的Database。
- 支援通過Create/Drop External Table實現級聯建立/刪除Catalog中的表。
- 支援查看Catalog中的所有Database和表。
支援使用Dynamic Table全量、增量讀取Paimon表資料，詳情請參見CREATE DYNAMIC TABLE。
支援使用ANALYZE、AUTO ANALYZE收集外部表格統計資訊，協助最佳化器產生最佳的查詢計劃，詳情請參見ANALYZE和AUTO ANALYZE。
支援外部表格資料鏡像。
TimeTravel：支援基於Timestamp、Version、Tag查詢Paimon表歷史快照。
分支查詢：支援基於Branch查詢Paimon分支資料及歷史快照。
支援查詢Paimon系統資料表，如Snapshots、Tags。

前提條件

已開通DLF服務並建立Catalog。詳情請參見授權並開通DLF和資料目錄。
已購買Hologres執行個體並開通資料湖加速服務，且要求與DLF服務位於同一地區。詳情請參見環境配置。

使用限制

不支援寫入 Paimon 表（包括 INSERT、UPDATE、DELETE、TRUNCATE 等操作）。
僅通過 External Database 映射的 Paimon 外表支援 TimeTravel 查詢、Branch 查詢和資料鏡像功能。
不支援對發生 Schema Evolution 的表或 Dynamic Table 執行 TimeTravel 查詢。
外部表格鏡像不支援 Paimon 主鍵表。
不支援讀取 Paimon 表中分區值為 NULL 或空的預設分區資料。
不支援通過 HoloWeb 的 OSS 資料湖加速功能可視化建立 DLF Catalog。
唯讀從執行個體不支援開啟資料湖加速能力。

使用External Database方式映射Catalog

SLR模式

服務關聯角色（SLR）是一種可信實體為阿里雲服務的RAM角色，旨在解決跨雲端服務的授權訪問問題。多數情況下，當您使用特定功能時，關聯的雲端服務會自動建立或刪除服務關聯角色，不需要您手動建立或刪除。通過服務關聯角色可以更好地配置雲端服務正常操作所必需的許可權，避免誤操作帶來的風險。詳情請參見服務關聯角色。

適用情境

SLR模式適用於使用目前使用者身份訪問External Database資料的情境，Hologres預設基於SLR和身份透傳實現跨雲產品訪問。

操作步驟

串連Hologres執行個體並建立External Database。

CREATE EXTERNAL DATABASE <ext_database_name> WITH
  catalog_type 'paimon'
  metastore_type 'dlf-rest'
  dlf_catalog '<dlf_catalog_name>' 
  comment 'Catalog on dlf'
  ;

查看External Database下的Schema和表。

-- 查詢External Database下的Schema
SELECT * FROM hologres.hg_external_schemas('<ext_database_name>');

-- 查詢Schema下的表
SELECT * FROM hologres.hg_external_tables ('<ext_database_name>', '<ext_schema_name>');

查詢表資料。

SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

STS模式

阿里雲STS（Security Token Service）是阿里雲提供的一種臨時存取權限管理服務。RAM提供RAM使用者和RAM角色兩種身份。其中，RAM角色不具備永久身份憑證，而只能通過STS擷取可以自訂時效和存取權限的臨時身份憑證，即安全性權杖（STS Token），詳情請參見什麼是STS。

適用情境

STS模式適用於跨帳號訪問和Hologres自訂帳號（BASIC帳號）訪問External Database資料的情境。

操作步驟

登入RAM控制台，建立RAM角色，並授予DLF存取權限AliyunDLFFullAccess或AliyunDLFReadOnlyAccess。詳情請參見建立RAM角色並授權。

增加信任策略，允許該RAM角色被可信雲端服務Hologres扮演。詳情請參見修改RAM角色的信任策略。

{
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "hologres.aliyuncs.com"
        ]
      }
    }
  ],
  "Version": "1"
}

登入資料湖構建控制台，在目標Catalog中對RAM角色授予DB和表的存取權限。詳情請參見授權管理。

在Hologres中建立External Database，認證策略指定上述RAM角色。

CREATE EXTERNAL DATABASE <ext_database_name> WITH
  catalog_type 'paimon'
  metastore_type 'dlf-rest'
  dlf_catalog 'paimon_catalog' 
  rolearn 'acs:ram::106380604****:role/***-ramrole'
  comment 'Catalog on dlf'
  ;

在Hologres中建立USER MAPPING，將需要訪問DLF Catalog的RAM使用者或BASIC帳號關聯到RAM角色。
```
CREATE USER MAPPING FOR "<RAM使用者｜BASIC帳號>"
EXTERNAL DATABASE <ext_database_name>
OPTIONS
(
   rolearn 'acs:ram::10638060***:role/***ramrole'
);
```
說明
- 關於USER MAPPING介紹，詳情請參見CREATE USER MAPPING。
- 關於建立RAM使用者詳情，請參見建立RAM使用者。
- 關於建立BASIC帳號詳情，請參見使用者管理。
通過RAM使用者登入Hologres管理主控台或BASIC帳號重新串連Hologres執行個體後，執行以下查詢SQL。
```
SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;
```

湖表鏡像加速

Hologres自V3.2版本起，新增湖表鏡像功能，以加速湖上資料查詢。該功能支援中繼資料與資料鏡像，能夠近即時或按指定時間間隔將外部資料源的變更同步至Hologres。此外，還支援全表鏡像和分區表的部分鏡像，可通過參數靈活控制鏡像資料的範圍。更多介紹詳情，請參見湖表鏡像。

External Schema相關操作

建立External Schema至Catalog

建立EXTERNAL SCHEMA會在DLF Catalog中實際建立一個Database，代碼如下：

-- 建立EXTERNAL SCHEMA
CREATE EXTERNAL SCHEMA [IF NOT EXISTS] <ext_database_name>.<ext_schema_name>;

重新整理External Schema中繼資料

REFRESH CACHE FOR EXTERNAL SCHEMA <ext_database_name>.<ext_schema_name> WITH( cache_level = 'metadata');

刪除External Schema

刪除EXTERNAL SCHEMA會實際刪除Catalog下對應DB和DB下所有的表，請謹慎操作。

-- 刪除EXTERNAL SCHEMA
DROP EXTERNAL SCHEMA [IF NOT EXISTS] <ext_database_name>.<ext_schema_name>;

查看指定Schema下的表

SELECT * FROM hologres.hg_external_tables ('<ext_database_name>', '<ext_schema_name>');

External Table相關操作

建立External Table至Catalog

-- 建立非分區表

CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name>(
    id TEXT,                                        
    created_at BIGINT,                             
    type TEXT,                                      
    actor_id TEXT,                                  
    actor_login TEXT,                               
    repo_id TEXT,                                   
    repo_name TEXT,                                 
    org TEXT,                                       
    org_login TEXT,                                 
    PRIMARY KEY(id)
) WITH (
  "changelog-producer"='input',
  "bucket"=6,
  "bucket-key"='id'
);

-- 建立分區表

CREATE EXTERNAL TABLE <ext_database_name>.<ext_schema_name>.<ext_table_name>(
    id TEXT,                                        
    created_at BIGINT,                              
    type TEXT,                                      
    actor_id TEXT,                                  
    actor_login TEXT,                               
    repo_id TEXT,                                  
    repo_name TEXT,                                 
    org TEXT,                                       
    org_login TEXT                                
)
LOGICAL PARTITION BY LIST(created_at)
 WITH (
  "file_format" = 'orc',
  "bucket"=6,
  "bucket-key"='id'
);

重新整理External Table中繼資料

REFRESH CACHE FOR EXTERNAL TABLE <ext_db_name>.<ext_schema_name>.<ext_table_name> WITH( cache_level = 'metadata');

刪除External Table

刪除External Table會實際刪除DLF Catalog中的表，請謹慎操作。

-- 刪除External Table
DROP EXTERNAL TABLE IF EXISTS <ext_database_name>.<ext_schema_name>.<ext_table_name>;

收集外表統計資訊

Analyze和Auto Analyze命令用於收集資料庫中表內容的統計資訊，最佳化器會根據這些統計資訊產生最佳的查詢計劃，從而提高查詢效率，詳情請參見ANALYZE和AUTO ANALYZE。

-- 更新某個表的統計資訊，預設會收集表中所有列的統計資訊
analyze <ext_database_name>.<ext_schema_name>.<ext_table_name>;

-- 更新某個列的統計資訊，會比更新表時採樣的資料更多，更精準，主要用於更新管理條件的列
analyze <ext_database_name>.<ext_schema_name>.<ext_table_name>(<colname>, <colname>);

-- 開啟Auto Analyze
ALTER EXTERNAL DATABASE <ext_database_name> WITH enable_auto_analyze 'true';

TimeTravel查詢

Hologres自V3.2版本開始，支援基於Timestamp、Version、Tag對Paimon表進行歷史Snapshots查詢，從而實現對資料的TimeTravel功能，同時支援在Hologres側查詢Paimon Snapshots、Tags系統資料表，來擷取目標表的Snapshot詳情。關於系統資料表介紹詳情，請參見Paimon System Tables。

查詢Paimon Snapshots系統資料表

SELECT * FROM hologres.hg_list_snapshots('<ext_database_name>.<ext_schema_name>.<ext_table_name>;');

Snapshots系統資料表包含的欄位資訊與Paimon系統資料表的對應關係如下：

Snapshots系統資料表欄位	類型	描述	Paimon系統資料表對應欄位
branch_name	TEXT	分支名稱。	branch_name
snapshot_id	TEXT	快照ID。	snapshot_id
schema_id	TEXT	表Schema ID。	schema_id
commit_kind	TEXT	表資料提交類型。	commit_kind
commit_time	TIMESTAMPTZ	提交時間。	commit_time
extend_info	TEXT（JSON）	Paimon Snapshots系統資料表剩餘屬性。	Paimon Snapshots系統資料表剩餘屬性

您也可以通過hologres.hg_list_versions函數來查詢Paimon Snapshots系統資料表，對於Paimon表查詢來說，Version對應Snapshots ID。

SELECT * FROM hologres.hg_list_versions('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

查詢Paimon Tag系統資料表

SELECT * FROM hologres.hg_list_tags('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Tag系統資料表包含的欄位資訊與Paimon系統資料表的對應關係如下：

Tag系統資料表欄位	類型	描述	Paimon系統資料表對應欄位
branch_name	TEXT	分支名稱。	branch_name
tag_name	TEXT	標籤名稱。	tag_name
snapshot_id	TEXT	快照ID。	snapshot_id
schema_id	TEXT	表Schema ID。	schema_id
commit_time	TIMESTAMPTZ	提交時間。	commit_time
extend_info	TEXT（JSON）	Paimon Tag系統資料表剩餘屬性。	Paimon Tag系統資料表剩餘屬性

基於Timestamp查詢Paimon歷史快照

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR TIMESTAMP AS OF '<timestamp>';

基於Version查詢Paimon歷史快照

對於Paimon表來說，Version對應Snapshots ID。

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR VERSION AS OF '<version>';

基於Tag查詢Paimon歷史快照

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR TAG AS OF '<tag>';

Branch查詢

Hologres自V3.2.版本開始，支援基於Branch查詢Paimon表指定分支資料，並支援分支Fallback屬性，更多關於Paimon Branch介紹詳情，請參見Manage Branch。

查詢Branch系統資料表

SELECT * FROM hologres.hg_list_branches('<ext_database_name>.<ext_schema_name>.<ext_table_name>');

Branch系統資料表包含的欄位資訊與Paimon系統資料表的對應關係如下：

欄位	類型	描述	Paimon系統資料表對應欄位
branch_name	TEXT	分支名稱。	branch_name
create_time	TIMESTAMPTZ	分支建立時間。	create_time
extend_info	TEXT（JSON）	Paimon Branch系統資料表剩餘屬性。	Paimon Branch系統資料表剩餘屬性

查詢指定分支資料

SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<tag>';

說明

如果Paimon表設定了scan.fallback-branch屬性，查詢作業從當前分支讀取資料時，某個分區不存在，讀取器會嘗試從備用分支（Fallback Branch）讀取該分區的資料。更多關於Fallback Branch介紹詳情，請參見Manage Branch。

查詢指定分支的歷史Snapshots資料

-- 基於Timestamp查詢
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<branch_name>' TIMESTAMP AS OF '<timestamp>';

-- 基於Tag查詢
SELECT * FROM '<ext_database_name>.<ext_schema_name>.<ext_table_name>' FOR branch AS OF '<branch_name>' TAG AS OF '<tag_name>';

使用Foreign Table方式消費Paimon表

建立Foreign Server。

關於Foreign Server的更多操作詳情，請參見Foreign Server更多操作（SQL方式）。

-- create foreign server

CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
    catalog_type 'paimon',
    metastore_type 'dlf-rest', 
    dlf_catalog '<dlf_catalog_name>'
);

建立外部表格。

IMPORT FOREIGN SCHEMA方式

關於IMPORT FOREIGN SCHEMA使用詳情，請參見IMPORT FOREIGN SCHEMA。

-- 使用IMPORT FOREIGN SCHEMA建立外表
IMPORT FOREIGN SCHEMA <dlf_db_name>
FROM SERVER <server_name> 
INTO <holo+schema_name> 
options (if_table_exist 'update');

CREATE FOREIGN TABLE方式

關於CREATE FOREIGN TABLE使用詳情，請參見CREATE FOREIGN TABLE。

CREATE FOREIGN TABLE <foreign_table_name>
(
  { column_name data_type }
  [, ... ]
) 
SERVER <server_name>
options
(
  schema_name '<dlf_db_name>',
  table_name '<dlf_table_name>'
);

使用Dynamic Table消費Paimon表

Dynamic Table可以自動處理並儲存一個或者多個基表（Base Table）對象的資料彙總結果，內建不同的資料重新整理策略，業務可以根據需求設定不同的資料重新整理策略，實現資料從基表對象到Dynamic Table的自動流轉，滿足業務統一開發、資料自動流轉、處理時效性等訴求。Hologres自V3.0版本開始，支援把MaxCompute外部表格和DLF外部表格作為Dynamic Table的基表，詳見Dynamic Table。

全量重新整理

CREATE DYNAMIC TABLE paimon_dt_table
WITH (
      auto_refresh_mode='full',
      freshness='3 minutes'
)AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

增量重新整理

CREATE DYNAMIC TABLE paimon_dt_table_increamental
WITH (
      auto_refresh_mode='incremental',
      freshness='3 minutes'
)AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

自動重新整理

CREATE DYNAMIC TABLE paimon_dt_table_auto
WITH (
      auto_refresh_mode='auto',
      freshness='3 minutes'
)AS SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;