基於MaxCompute建立面向Hologres的中繼資料映射和資料同步實踐 - MaxCompute

本文介紹如何基於MaxCompute建立面向Hologres的中繼資料映射和資料同步。

背景資訊

傳統數倉架構將上遊即時或批量資料寫入數倉，並使用OLAP引擎進行分析（圖示上半部分）。但部分情境需要在MaxCompute中讀取Hologres的資料（圖示下半部分），例如：

即時資料透出與歸檔：資料需要從即時資料源快速透出到業務，完成即時數倉業務需求後，資料歸檔到企業級數倉對應分層和主題域。
業務先行與資料迴流：沒有經過數倉統一處理，先滿足業務需求，但是資料集市穩定後，仍然需要迴流到企業級數倉，和DWD、DWS等融合。

這兩種情境的資料訪問方式包括：

數倉模型迭代完善過程中瀏覽即時數倉中的資料。
即時數倉或資料集市的資料定期歸檔入企業級數倉。
企業級數倉加工後的資料寫入Hologres ADS層，供業務消費。

功能介紹

本教程構建了從MaxCompute到Hologres的中繼資料映射鏈路，具體功能如下：

說明

MaxCompute和Hologres之間的資料類型映射存在差異，Hologres部分資料類型不支援同步至MaxCompute。

Schema層級中繼資料映射：可基於RAMRole許可權認證方式，通過外部Schema即時讀取Hologres中繼資料和資料，進行Schema層級的資料訪問。
單表級中繼資料映射：可在Hologres資料目錄中選擇目標表，一鍵自動建立映射Hologres表的MaxCompute外部表格。
資料同步：可將需要周期同步的表一鍵配置為資料同步任務，滿足周期性地向企業級數倉同步的需求。

前提條件

業務情境操作流程

本教程基於使用Flink+Hologres搭建的即時數倉，在Hologres即時數倉的DWD層，增加了MaxCompute映射Hologres Schema、Table，並從DWD層表中同步資料的流程。具體如下：

支援：

通過External Schema映射Hologres的Schema；
通過External Table映射Hologres的Table；
指定Hologres的表進行一次性或周期性資料同步。

上遊資料準備

準備工作

重要

RDS MySQL執行個體、Hologres執行個體和Flink執行個體必須位於同一地區的同一可用性區域，且使用的VPC相同。可以在Realtime Compute控制台，使用網路探測功能檢查Flink工作空間與RDS MySQL、Hologres執行個體之間的網路是否連通。詳情請參見如何進行網路探測。

建立RDS MySQL執行個體並準備資料來源。本教程使用DMS登入RDS MySQL執行個體，其中重點參數配置如下：
配置項
描述
執行個體名稱
test_dbs_rds
MySQL資料庫名稱
test_dbs_mysqldb
資料庫帳號
test_dbs_mysqluser
帳號類型
選擇高許可權帳號。

建立Hologres執行個體和計算群組。本教程中重點參數配置如下：

配置項		描述
購買Hologres	商品類型	獨享執行個體（隨用隨付）
	執行個體類型	計算群組型
	計算群組預留計算資源	64 CU
	執行個體名稱	test_dbs_holo
建立資料庫	資料庫名稱	order_dw
建立資料庫	預設Schema	public
新增計算群組	read_warehouse_1	預留計算資源：配置為32 CU。

建立Flink工作空間和Catalog。本教程中建立的Flink工作空間名稱為test-dbs-flink。

在Flink中建立RDS MySQL整庫同步任務（ODS層表）

在Flink中建立RDS MySQL整庫同步任務，將RDS MySQL中的資料同步至Hologres資料庫order_dw的public Schema下，並使用Hologres的read_warehouse_1計算群組查詢ODS資料。

重要

MySQL需要提前開啟Binlog，可以在RDS MySQL的資料庫中執行show variables like "log_bin";命令，查看Binlog是否開啟。詳情請參見MySQL伺服器配置要求。

登入Realtime Compute控制台，在左上方選擇地區。
建立CDAS同步作業ODS。
1. 在資料開發 > ETL頁面，建立名為ODS的SQL流作業，並將如下代碼拷貝到SQL編輯器。
```
CREATE DATABASE IF NOT EXISTS dw.order_dw   -- 建立catalog時設定了table_property.binlog.level參數，因此通過CDAS建立的所有表都開啟了binlog。
AS DATABASE mysqlcatalog.test_dbs_mysqldb INCLUDING all tables -- 可以根據需要選擇上遊資料庫需要入倉的表。
/*+ OPTIONS('server-id'='8001-8004') */ ;   -- 指定mysql-cdc執行個體server-id範圍。
```
  說明
  - 本樣本預設將資料同步到資料庫order_dw的public Schema下。您也可以將資料同步到Hologres目標庫的指定Schema中，詳情請參見作為CDAS的目標端Catalog，指定後使用Catalog時的表名格式也會發生變化，詳情請參見使用Hologres Catalog。
  - 如果源表的資料結構發生變化，則需要等待源表的資料出現變更（刪除、插入、更新），結果表的資料結構才會看到變化。
2. 單擊右上方的部署，進行作業部署。
3. 單擊左側導覽列的營運中心 > 作業營運，單擊剛剛部署的ODS作業操作列的啟動，選擇無狀態啟動後單擊啟動。
向計算群組載入資料。
Table Group是Hologres中資料的載體。使用read_warehouse_1查詢order_dw資料庫中Table Group（本樣本為order_dw_tg_default）的資料時，為計算群組read_warehouse_1載入order_dw_tg_default，以實現使用init_warehouse計算群組寫入資料，使用read_warehouse_1計算群組進行服務查詢。
在HoloWeb開發頁單擊SQL編輯器，確認執行個體名和資料庫名稱後，執行如下命令。更多詳情請參見建立新計算群組執行個體。載入後，可以查看到read_warehouse_1已經載入了order_dw_tg_default Table Group的資料。
```
--查看當前資料庫有哪些Table Group
SELECT tablegroup_name FROM hologres.hg_table_group_properties GROUP BY tablegroup_name;

--為計算群組載入Table Group
CALL hg_table_group_load_to_warehouse ('order_dw.order_dw_tg_default', 'read_warehouse_1', 1);

--查看計算群組載入Table Group的情況
SELECT * FROM hologres.hg_warehouse_table_groups;
```
在右上方切換計算群組為read_warehouse_1，後續使用read_warehouse_1進行查詢分析。

在HoloWeb中執行如下命令，查看MySQL同步到Hologres的3張表資料。

---查orders中的資料。
SELECT * FROM orders;

---查orders_pay中的資料。
SELECT * FROM orders_pay;

---查product_catalog中的資料。
SELECT * FROM product_catalog;

在Flink中建立DWD層表

通過Flink Catalog功能在Hologres中建DWD層的寬表dwd_orders。

在資料開發 > 資料查詢頁面的查詢指令碼頁簽，將如下代碼拷貝到查詢指令碼後，選中目標片段後單擊左側程式碼上的運行。

-- 寬表欄位要nullable，因為不同的流寫入到同一張結果表，每一列都可能出現null的情況。
CREATE TABLE dw.order_dw.dwd_orders (
  order_id bigint not null,
  order_user_id string,
  order_shop_id bigint,
  order_product_id bigint,
  order_product_catalog_name string,
  order_fee numeric(20,2),
  order_create_time timestamp,
  order_update_time timestamp,
  order_state int,
  pay_id bigint,
  pay_platform int comment 'platform 0: phone, 1: pc', 
  pay_create_time timestamp,
  PRIMARY KEY(order_id) NOT ENFORCED
);

-- 支援通過catalog修改Hologres物理表屬性。
ALTER TABLE dw.order_dw.dwd_orders SET (
  'table_property.binlog.ttl' = '604800' --修改binlog的逾時時間為一周。
);

實現即時消費ODS層orders、orders_pay表的binlog。

在資料開發 > ETL頁面，建立名為DWD的SQL流作業，並將如下代碼拷貝到SQL編輯器後，部署並啟動作業。通過如下SQL作業，orders表會與product_catalog表進行維表關聯，將最終結果寫入dwd_orders表中，實現資料的即時打寬。

BEGIN STATEMENT SET;

INSERT INTO dw.order_dw.dwd_orders 
 (
   order_id,
   order_user_id,
   order_shop_id,
   order_product_id,
   order_fee,
   order_create_time,
   order_update_time,
   order_state,
   order_product_catalog_name
 ) SELECT o.*, dim.catalog_name 
   FROM dw.order_dw.orders as o
   LEFT JOIN dw.order_dw.product_catalog FOR SYSTEM_TIME AS OF proctime() AS dim
   ON o.product_id = dim.product_id;

INSERT INTO dw.order_dw.dwd_orders 
  (pay_id, order_id, pay_platform, pay_create_time)
   SELECT * FROM dw.order_dw.orders_pay;

END;

查看寬表dwd_orders資料。
在HoloWeb開發頁面串連Hologres執行個體並登入目標資料庫後，在SQL編輯器上執行如下命令。
```
SELECT * FROM dwd_orders;
```

在DataWorks中綁定MaxCompute和Hologres計算資源

您可在DataWorks工作空間中綁定MaxCompute和Hologres計算資源，並在資料目錄中查看相應的Catalog。

建立新版DataWorks工作空間，詳情請參見建立工作空間。本教程中工作空間名稱設定為DBS_DW_TEST。
添加計算資源。
綁定MaxCompute和Hologres計算資源，具體操作請參見綁定計算資源。本教程中設定的計算資源名稱如下：
計算資源類型
計算資源名稱
MaxCompute
dbs_mc
Hologres
order_dw

登入DataWorks控制台，左上方選擇地區。在左側導覽列，選擇資料開發與營運 > 資料開發。在右側頁面選擇工作空間，單擊進入Data Studio。在Data Studio頁面中，單擊左側導覽列的表徵圖，進入資料目錄，可查看已綁定的MaxCompute專案和Hologres執行個體。

在資料目錄中添加Hologres Catalog時，不同的添加方式，對應的行為不同。具體如下：

添加Hologres Catalog方式

適用情境

說明

通過DataWorks 資料來源模式添加

適用於Data Integration情境。

例如：從Hologres中定時同步某張表的資料至MaxCompute。

DataWorks資料來源指定到具體的Database，攜帶執行人的身份，不可越權查看其他Database。

說明

若您在建立DataWorks新版工作空間後，綁定了Hologres計算資源，則資料目錄中預設通過DataWorks資料來源模式添加Hologres Catalog。

通過Hologres - 執行個體模式添加

適用於跨Database瀏覽資料的情境。

可以使用計算資源攜帶身份查看有許可權的Database，此種方式便於跨Database瀏覽其他Database下的資料。
可建立中繼資料映射任務，但無法建立同步任務。

將滑鼠懸浮於MaxCompute專案名或Hologres執行個體名上，可查看資料來源或執行個體的詳細資料。

資料來源模式

Hologres-執行個體模式

建立映射Hologres Schema的MaxCompute外部Schema

外部Schema映射的表，中繼資料和資料即時從Hologres中擷取，無需在MaxCompute內建立含有DDL元資訊的表，Hologres源端表結構變化或資料變化，都可以即時感知並查詢擷取。

通過該方式，可以瀏覽即時數倉中ODS層、DWD層的表，實現按需查詢資料或為後續資料的周期同步做準備。

在MaxCompute控制台的租戶管理 > 外部資料源頁面，建立串連Hologres Database的外部資料源。具體操作請參見建立Hologres外部資料源。
本教程中建立的外部資料源名稱為dbs_holo_external。
重要
建立Hologres外部資料源時：
- 認證和鑒權方式需選擇阿里雲RAM角色。
- Host僅支援配置為傳統網路類型的Hologres執行個體網域名稱。
在即時數倉Hologres頁面，單擊左側前往HoloWeb，在上方功能表列單擊資訊安全中心，再單擊左側邊欄使用者管理，將RAMRole添加為Hologres執行個體的使用者，以確保其具備Hologres的表許可權。具體操作請參見使用者管理。
在Hologres的HoloWeb開發頁面，為該RAM角色授予執行個體的Developer許可權（僅支援SPM模式）。操作詳情請參見DB管理。

映射Hologres執行個體的Schema到MaxCompute專案的Schema，並在MaxCompute中瀏覽Hologres中的表。

登入DataWorks控制台，左上方選擇地區。
在左側導覽列，選擇資料開發與營運 > 資料開發。在右側頁面選擇工作空間，單擊進入Data Studio。
在Data Studio頁面中，單擊左側導覽列的表徵圖，進入資料目錄。
展開Hologres資料目錄，按右鍵目標執行個體的Schema（本教程中為public），選擇中繼資料映射至 MaxCompute。

在中繼資料映射至 MaxCompute頁面中配置Hologres源端和MaxCompute目標端的參數。

本教程中重點參數配置如下，其餘參數保持預設即可。

參數名	描述
專案尋找方式	選擇來自 DataWorks 資料來源。
資料來源	選擇已綁定至DataWorks的MaxCompute計算資源名稱。本教程中為`dbs_mc`。
外部 Schema 名稱	指定源端Hologres Schema下的中繼資料映射至目標MaxCompute的外部Schema名稱。本教程中配置為`public`。
外部資料源	選擇MaxCompute中已建立的Hologres聯邦資料來源名稱。本教程中為`dbs_holo_external`。

單擊Hologres 源端上方的運行。
運行成功後，可看到和Hologres Schema（public）同名的MaxCompute外部Schema。您可直接瀏覽Hologres中的表，並在MaxCompute中使用如下SQL命令查詢資料。
```
SET odps.namespace.schema=true;
SELECT * FROM public.dwd_orders;
```
返回結果如下：
重要
若Schema級映射運行成功，但在資料目錄的MaxCompute目錄下無法顯示映射後的表名，顯示查詢失敗，請確認您建立的RAM角色許可權是否配置正確。詳情請參見建立RAM角色。

建立映射Hologres Table的MaxCompute外部表格

不同於外部Schema，外部表格需要將Hologres表在MaxCompute內建為外部表格。外部表格支援RAMRole和雙簽名兩種認證方式：

RAMRole：支援跨帳號角色扮演。您需在Hologres側，完成以下操作：
- 將RAMRole添加為Hologres執行個體的使用者，詳情請參見使用者管理。
- 為該RAM角色授予執行個體的Developer許可權（僅支援SPM模式），詳情請參見DB管理。
雙簽名：使用當前執行任務的使用者身份鑒權。即目前使用者在Hologres中擁有哪些表的許可權，通過MaxCompute外部表格，也可使用此身份訪問Hologres資料。詳情請參見Hologres外部表格。

您可挑選部分或全部欄位進行映射，映射規則請參見參數說明的tblproperties參數部分。

操作步驟如下：

登入DataWorks控制台，左上方選擇地區。在左側導覽列，選擇資料開發與營運 > 資料開發。在右側頁面選擇工作空間，單擊進入Data Studio。在Data Studio頁面中，單擊左側導覽列的表徵圖，進入資料目錄。
展開Hologres資料目錄，按右鍵目標執行個體public Schema下的dwd_orders表，選擇中繼資料映射至 MaxCompute。

在中繼資料映射至 MaxCompute頁面中配置Hologres源端和MaxCompute目標端的參數。

本教程中重點參數配置如下，其餘參數保持預設即可。參數詳情請參見單表級中繼資料映射。

參數名	描述
執行個體尋找方式	選擇來自 DataWorks 資料來源。
資料來源	選擇已綁定至DataWorks的MaxCompute資料來源名稱。本教程中為`dbc_mc`。
Schema	指定源端Hologres Schema下的中繼資料映射至目標MaxCompute的外部Schema名稱。本教程中配置為`default`。
External Table	指定MaxCompute中新建立的外表名稱，源端表資料將會被映射至此表中，預設與Hologres中表名稱保持一致。說明建立外表為一次性動作，不會自動重新整理中繼資料，如需重新整理中繼資料，需要刪除當前外表並重新手動建立中繼資料映射。
MaxCompute 外表許可權	選擇雙簽名。說明選擇RamRole方式時，需要在Hologres側添加使用者，並進行DB授權。
生命週期	設定表的生命週期。

單擊Hologres 源端上方的運行。
運行成功後，即可在左側MaxCompute Schema下顯示建立的外部表格。您可使用如下語句在MaxCompute中查詢該外部表格的資料。
```
SET odps.namespace.schema=true;
SELECT * FROM dwd_orders;
```
返回結果如下：

建立同步Hologres Table的周期任務

若需要周期性地將Hologres即時數倉中的DWD表資料歸檔到MaxCompute雲數倉的一張內部表中，可以使用資料同步任務，並配置周期調度實現。

登入DataWorks控制台，左上方選擇地區。在左側導覽列，選擇資料開發與營運 > 資料開發。在右側頁面選擇工作空間，單擊進入Data Studio。在Data Studio頁面中，單擊左側導覽列的表徵圖，進入資料開發頁面，並建立專案目錄
單擊左側導覽列的表徵圖，在資料目錄頁面展開Hologres資料目錄，按右鍵目標執行個體public Schema下的dwd_orders表，選擇資料同步至 MaxCompute。

在選擇建立節點所屬的路徑彈框中，為雲數倉內的表命名dwd_holo_orders，並按下斷行符號鍵。進入同步Hologres資料至MaxCompute的配置頁面，配置Hologres源端和MaxCompute目標端的參數。

本教程中重點參數配置如下，更多參數說明請參見配置同步節點。

參數名	描述
資料來源	選擇已綁定至DataWorks的MaxCompute資料來源名稱。本教程中為`dbs_mc`。
Schema	選擇您想要儲存的Schema。
Table	自訂MaxCompute內部表名稱。本教程中配置為`dwd_holo_orders`。
生命週期	設定表的生命週期。
匯入方式	選擇資料寫入MaxCompute內表的方式：追加：當您需要刪除原有資料，將新資料寫入到目標表時，可以選擇覆蓋寫入的方式。覆寫：當您需要保留原有資料，將新資料追加到目標表時，可以選擇追加寫入的方式。
訪問 Hologres 許可權	您可以根據實際情況選擇以下方式訪問Hologres執行個體：雙簽名訪問方式：通過當前身份進行Hologres許可權校正。您需確保在MaxCompute專案下，擁有MaxCompute表讀取許可權的同時，也需要有該MaxCompute表對應的Hologres源表許可權。MaxCompute側許可權控制參見：湖倉一體2.0使用指南、Hologres側許可權控制參見：許可權管理概述。 RamRole訪問方式：通過指定RAM角色進行訪問身份校正。為RAM使用者授權AliyunSTSAssumeRoleAccess權限原則。詳情請參見RAM角色授權模式。授權完成後，在RamRole中配置您所指定的RAM角色。

單擊頁面右側的調度配置，在調度配置頁面中配置工作流程和調度周期，操作詳情請參見節點調度。
單擊Hologres 源端上方的運行。
運行成功後，即可在左側MaxCompute下顯示建立的內部表，您可使用如下SQL語句在MaxCompute中查詢該外部表格的資料。
```
SET odps.namespace.schema=true;
SELECT * FROM default.dwd_holo_orders;
```
返回結果如下：

計算資源類型	計算資源名稱
MaxCompute	dbs_mc
Hologres	order_dw

配置項	描述
執行個體名稱	test_dbs_rds
MySQL資料庫名稱	test_dbs_mysqldb
資料庫帳號	test_dbs_mysqluser
帳號類型	選擇高許可權帳號。