MaxCompute：Hologres外部表格

外部表格建立規範

• 如果Hologres開啟了IP白名單功能，使用者需要採用雙簽名模式建立Hologres外部表格。如果用STS模式建立Hologres外部表格，MaxCompute訪問Hologres時會被IP白名單攔截。

• 對於Hologres的父表、子表，Hologres外部表格中會指定表名，並執行SQL語句。父表、子表都可以映射至Hologres外部表格，但是父表只能讀不能寫。

• 向Hologres外部表格寫入資料時，暫不支援Hologres的INSERT ON CONFLICT(UPSERT)機制。如果Hologres源表有主鍵，請避免寫入的資料與Hologres源表中的資料產生主鍵唯一性衝突。

• 表名和欄位名大小寫不敏感，且在建立表、查詢表及欄位時不支援強制轉換大小寫。

Schema相容性

當Hologres源表中的Schema與外表Schema不一致時：

• 列數不一致：如果Hologres源表中的列數小於外表DDL的列數，則讀取RDS資料時，系統會報錯。例如column "xxx" does not exist。當Hologres源表中的列數大於外表DDL的列數時，系統會丟棄超出的列資料。

• 列類型不一致：MaxCompute不支援使用INT類型接收Hologres源表中的STRING類型資料，支援使用STRING類型接收INT類型資料（不推薦）。

STS模式建立Hologres外部表格

具體使用範例程式碼，詳情請參見建立Hologres外部表格（STS模式）。

CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name>(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
WITH serdeproperties (
  'odps.properties.rolearn'='<ram_arn>')
location '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
tblproperties (
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
  'odps.federation.jdbc.target.db.type'='holo',
  'odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...'
);

雙簽名模式建立Hologres外部表格

具體使用範例程式碼，詳情請參見建立Hologres外部表格（雙簽名模式）。

雙簽名Hologres外部表格支援相同RAM使用者可以訪問MaxCompute和Hologres對應有許可權的表，不需要再手工授權。同時支援HologresIP白名單能力，便於使用。

-- 開啟雙簽名開關
SET odps.sql.common.table.planner.ext.hive.bridge=true;
-- 建立外部表格
CREATE EXTERNAL TABLE [IF NOT EXISTS]  <table_name>(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
STORED BY '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
LOCATION '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
tblproperties (
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
  'odps.federation.jdbc.target.db.type'='holo',
  ['odps.federation.jdbc.colmapping'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...']
);

建立Hologres資料庫

1. 登入Hologres管理主控台，在左上方選擇地區。

2. 在左側導覽列選擇執行個體列表。

3. 若沒有執行個體，需要先購買Hologres執行個體。

   在執行個體列表頁面，單擊目標執行個體名稱。

4. 在執行個體詳情頁面，單擊登入執行個體。

5. 單擊上方中繼資料管理頁簽。

   單擊建立資料庫，在彈出的對話方塊中，填寫資料庫名稱，其他參數保持預設。

建立Hologres表

1. 在執行個體詳情頁面，單擊登入執行個體。

2. 單擊上方SQL編輯器頁簽。

3. 輸入如下語句

   CREATE TABLE IF NOT EXISTS holo (
       id   INT PRIMARY KEY,
       name TEXT
   );
   
   INSERT INTO holo (id, name) VALUES
       (1, 'kate'),
       (2, 'mary'),
       (3, 'bob'),
       (4, 'tom'),
       (5, 'lulu'),
       (6, 'mark'),
       (7, 'haward'),
       (8, 'lilei'),
       (9, 'hanmeimei'),
       (10, 'lily'),
       (11, 'lucy');
   
   SELECT * FROM holo ORDER BY id;

前置準備

1. 已安裝並配置MaxCompute用戶端

2. 已準備好待建立Hologres外部表格的目標MaxCompute專案。

   建立MaxCompute專案資訊，請參見建立MaxCompute專案。

RAM角色建立及授權

1. 建立RAM角色

   建立RAM角色擷取ARN資訊，用於建立外部表格時填寫STS認證資訊。建立RAM角色的信任主體類型根據實際需求選擇，本樣本中信任主體類型為雲帳號。

   • 雲帳號：阿里雲帳號下的RAM使用者可以通過扮演RAM角色來訪問雲資源，詳情請參見建立可信實體為阿里雲帳號的RAM角色。

   • 身份供應商：通過設定SSO實現從企業本地帳號系統登入至阿里雲控制台，解決企業的統一使用者登入認證要求。詳情請參見建立可信實體為身份供應商的RAM角色。

   操作步驟如下：

   1. 登入RAM控制台。

   2. 在左側導覽列選擇身份管理 > 角色。

   3. 在角色頁面，單擊建立角色。

   4. 在建立角色頁面，選擇信任主體類型為雲帳號。

   5. 信任主體名稱選擇當前雲帳號。

2. 修改信任策略配置內容

   1. 在角色頁面，單擊目標角色名稱，進入該角色詳情頁。

   2. 在信任策略頁簽，單擊編輯信任策略，在編輯信任策略頁面選擇指令碼編輯頁簽。

   3. 參照如下內容修改信任策略配置

      修改信任策略配置內容與選擇的可信實體類型相關。其中<UID>即阿里雲帳號ID，可在使用者資訊頁面擷取。

      可信實體類型為阿里雲帳號

      {
        "Statement": [
          {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
              "RAM": [
                "acs:ram::<UID>:root"
              ]
            }
          },
          {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
              "Service": [
                "<UID>@odps.aliyuncs.com"
              ]
            }
          }
        ],
        "Version": "1"
      }

      可信實體類型為身份供應商

      {
        "Statement": [
              {
                  "Action": "sts:AssumeRole",
                  "Condition": {
                      "StringEquals": {
                          "saml:recipient": "https://signin.aliyun.com/saml-role/sso"
                      }
                  },
                  "Effect": "Allow",
                  "Principal": {
                      "Federated": [
                          "acs:ram::<UID>:saml-provider/IDP"
                      ]
                  }
              },
              {
                  "Action": "sts:AssumeRole",
                  "Effect": "Allow",
                  "Principal": {
                      "Service": [
                          "<UID>@odps.aliyuncs.com"
                      ]
                  }
              }
          ],
          "Version": "1"
      }

3. 添加RAM角色至Hologres執行個體並授權

   RAM角色需要有Hologres執行個體的開發許可權，才能在許可權範圍內使用Hologres。由於RAM角色預設沒有Hologres管理主控台的查看和操作執行個體的許可權，因此需要阿里雲帳號完成RAM相關許可權授予才能進行後續操作。將RAM角色添加至Hologres執行個體。授權方式及步驟如下：

   通過Hologres管理主控台授權

   1. 登入Hologres管理主控台，在左上方選擇地區。

   2. 在左側導覽列選擇執行個體列表。

   3. 在執行個體列表頁面，單擊目標執行個體名稱。

   4. 在執行個體詳情頁面，單擊登入執行個體。

   5. 單擊上面資訊安全中心頁簽。

      在左側導覽列選擇使用者管理。

   6. 在使用者管理頁面，單擊右上方新增使用者，在彈出的新增使用者頁面，添加RAM角色至Hologres執行個體。

   7. 在DB授權頁簽，為該RAM角色授予執行個體的開發許可權。

      若資料庫的權限原則設定為專家模式，則需在使用者管理頁面將角色類型修改為SuperUser，後續將不再需要進行資料庫授權操作。

      1. 在左側導覽列選擇DB 授權。

      2. 在DB 授權頁面，單擊目標資料庫對應操作列的使用者授權。

      3. 在授權頁面，單擊右上方新增授權，在彈出的新增授權對話方塊，選擇RAM角色並配置Developer 使用者組。

   通過RAM存取控制台授權

   若是通過RAM使用者扮演RAM角色，RAM使用者預設沒有Hologres管理主控台的許可權，需要阿里雲帳號給RAM使用者在存取控制頁面授予AliyunRAMReadOnlyAccess許可權，否則RAM使用者無法在Hologres管理主控台進行任何操作。詳情請參見文檔授予RAM使用者權限。

   1. 登入RAM控制台。

   2. 在左側導覽列選擇身份管理 > 角色。

   3. 在角色頁面，單擊目標角色名稱所在行操作列的新增授權。

   4. 選擇AliyunRAMReadOnlyAccess許可權為當前角色授權。

   通過SQL方式授權

   通過SQL方式進行授權，授權SQL請參見Hologres許可權模型。

在MaxCompute中建立外部表格

1. 登入MaxCompute用戶端，進入目標MaxCompute專案。

2. 執行如下命令建立Hologres外部表格。

   按照準備資料步驟，準備好的Hologres執行個體資訊如下：

   • Hologres資料庫名稱：holo_external_test。

   • Hologres資料庫的Schema：public。

   • Hologres表名稱：holo。

   • Hologres資料庫傳統網路串連地址：hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80。

   CREATE EXTERNAL TABLE IF NOT EXISTS my_table_holo_jdbc
   (
    id bigint,
    name string
   )
   stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::139699392458****:role/<role name>')
   location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&useSSL=true&table=<table name>/'
   tblproperties (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. 執行如下命令基於建立的Hologres外部表格查詢Hologres源表資訊。

   -- 訪問Hologres外部表格需要添加如下屬性。
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; --開啟JDBC直讀方式 
   -- 查詢Hologres外部表格資料。
   SELECT * FROM my_table_holo_jdbc limit 10;
   -- 返回結果。
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 6          | mark       | 
   | 11         | lucy       | 
   +------------+------------+

4. 基於Hologres外部表格與Hologres進行資料交換、聯合分析。

   • 將MaxCompute加工後的資料利用Hologres外部表格，寫入Hologres實現加速分析、線上服務。命令樣本如下。

     -- 訪問Hologres外部表格需要添加如下屬性。
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     SET odps.table.api.enable.holo.table=true; --開啟JDBC直讀方式 
     -- 向Hologres外部表格插入資料。
     INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
     -- 查詢Hologres外部表格資料。
     SELECT * FROM my_table_holo_jdbc;
     -- 返回結果。
     +------------+------------+
     | id         | name       | 
     +------------+------------+
     | 9          | hanmeimei  | 
     | 4          | tom        | 
     | 7          | haward     | 
     | 2          | mary       | 
     | 5          | lulu       | 
     | 12         | alice      | 
     | 8          | lilei      | 
     | 10         | lily       | 
     | 1          | kate       | 
     | 11         | lucy       | 
     | 6          | mark       | 
     | 3          | bob        | 
     +------------+------------+

   • 頻繁更新的維度資料表儲存在Hologres，滿足即時動態更新需要。MaxCompute通過外部表格方式訪問維度資料表與MaxCompute中事實表進行關聯分析，命令樣本如下。

     -- 訪問Hologres外部表格需要添加如下屬性。
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     -- 建立MaxCompute內部表。
     CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc;
     -- MaxCompute內部表與Hologres外部表格進行關聯分析。
     SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.id=t2.id;
     -- 返回結果。
     +------------+------------+------------+------------+
     | id         | name       | id2        | name2      | 
     +------------+------------+------------+------------+
     | 9          | hanmeimei  | 9          | hanmeimei  | 
     | 4          | tom        | 4          | tom        | 
     | 7          | haward     | 7          | haward     | 
     | 2          | mary       | 2          | mary       | 
     | 5          | lulu       | 5          | lulu       | 
     | 12         | alice      | 12         | alice      | 
     | 8          | lilei      | 8          | lilei      | 
     | 10         | lily       | 10         | lily       | 
     | 1          | kate       | 1          | kate       | 
     | 11         | lucy       | 11         | lucy       | 
     | 6          | mark       | 6          | mark       | 
     | 3          | bob        | 3          | bob        | 
     +------------+------------+------------+------------+

前置準備

1. 已安裝並配置MaxCompute用戶端

2. 已準備好待建立Hologres外部表格的目標MaxCompute專案。

   建立MaxCompute專案資訊，請參見建立MaxCompute專案。

3. Hologres中存在跟MaxCompute相同名稱的帳號，並且該帳號具有Hologres中對應表的讀寫權限。

4. 僅Hologres V1.3及以上版本支援MaxCompute使用雙簽名模式建立Hologres外部表格，目前雙簽名模式只支援從Hologres外部表格中讀取資料，不支援寫入資料至Hologres外部表格。

在MaxCompute中建立外部表格

1. 登入MaxCompute用戶端，進入目標MaxCompute專案。

2. 執行如下命令建立Hologres外部表格。

   按照準備資料步驟，準備好的Hologres執行個體資訊如下：

   • Hologres資料庫名稱：holo_external_test。

   • Hologres資料庫的Schema：public。

   • Hologres表名稱：holo。

   • Hologres資料庫傳統網路串連地址：hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80。

   -- 建立外部表格
   CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
   (
     id int,
     name string
   )
   STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
   location 'jdbc:postgresql://hgpostcn-cn-****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=<table name>/'
   TBLPROPERTIES (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. 查詢外部表格

   -- 查詢外部表格（必須與開啟雙簽名開關命令一起執行）
   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   SELECT * FROM holo_mc_external_dbl;
   
   -- 返回結果。
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 12         | alice      | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 11         | lucy       | 
   | 6          | mark       | 
   | 3          | bob        | 
   +------------+------------+