MaxCompute：Hologres外部表格

前提條件

使用限制

Hologres外部表格的使用限制如下：

• MaxCompute不支援對建立的Hologres外部表格執行更新（UPDATE）、刪除（DELETE）操作。

• Hologres的分區表和MaxCompute的分區表沒有對應關係。Hologres外部表格不支援分區。

• 當您需要向Hologres外部表格寫入大量資料時，採用並行多進程寫入方式，會小機率出現某個寫入進程資料重寫情況，導致資料重複。

• MaxCompute裡建的Hologres外部表格，其中DECIMAL資料類型預設小數18位，不能修改，只能建成decimal(38,18)。如果小數位元比較少，可以在MaxCompute中建外部表格時資料類型選擇String，使用的時候再使用cast函數強制轉換後使用。

• MaxCompute建立的Hologres外部表格，不支援Array，Map，Struct複雜資料類型。

• 在MaxCompute建立Hologres外部表格時，Hologres中有的JSON、JSONB、MONEY等資料類型，目前在MaxCompute沒有對應的資料類型，暫時不支援。

• Hologres外部表格不支援cluster屬性。

注意事項

建立Hologres外部表格文法

建立Hologres外部表格（STS模式）

通過STS模式建立Hologres外部表格的步驟如下：

1. 建立RAM角色

   建立RAM角色擷取ARN資訊，用於建立外部表格時填寫STS認證資訊。

   1. 登入RAM存取控制建立RAM角色。

      建立RAM角色的可信實體類型根據實際需求選擇阿里雲帳號或者身份供應商。

      • 阿里雲帳號：

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

      • 身份供應商：

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

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

      1. 在角色頁面，單擊已建立完成的RAM角色名稱。

      2. 單擊信任策略頁簽。

      3. 在信任策略頁簽，單擊編輯信任策略。

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

         修改信任策略配置內容與選擇的可信實體類型相關。

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

           {
             "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"
           }

           說明

           <UID>即阿里雲帳號ID，可在使用者資訊頁面擷取。

      5. 單擊確定。

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

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

   • 通過Hologres管理主控台授權。

     1. 登入Hologres管理主控台。

     2. 在左側導覽列單擊執行個體列表，單擊需要授權的Hologres執行個體名稱。

     3. 在執行個體詳情頁面，單擊帳號管理。

     4. 在使用者管理頁面單擊新增使用者添加RAM角色至Hologres執行個體。

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

        說明

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

        

   • 通過SQL方式授權。

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

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

3. 建立Hologres外部表格

   完成上述步驟後，基於已準備好的資料資訊，您即可登入MaxCompute用戶端，結合建立Hologres外部表格文法建立Hologres外部表格。

   1. 安裝並登入MaxCompute本地用戶端，進入目標MaxCompute專案。

      進入目標專案命令資訊，請參見專案空間操作。

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

      命令樣本如下。

      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/aliyunodpsholorole')
      location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80/mc_test?ApplicationName=MaxCompute&currentSchema=public&useSSL=false&table=holo/'
      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;
      --查詢Hologres外部表格資料。
      SELECT * FROM my_table_holo_jdbc limit 10;

      返回結果如下。

      +------------+------------+
      | id         | name       |
      +------------+------------+
      | 1          | kate       |
      | 2          | mary       |
      | 3          | bob        |
      | 4          | tom        |
      | 5          | lulu       |
      | 6          | mark       |
      | 7          | haward     |
      | 8          | lilei      |
      | 9          | hanmeimei  |
      | 10         | lily       |
      +------------+------------+

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

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

      --訪問Hologres外部表格需要添加如下屬性。
      SET odps.sql.split.hive.bridge=true;
      SET odps.sql.hive.compatible=true;
      --向Hologres外部表格插入資料。
      INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
      --查詢Hologres外部表格資料。
      SELECT * FROM my_table_holo_jdbc;

      返回結果如下。

      +------------+------------+
      | id         | name       |
      +------------+------------+
      | 12          | alice      |
      | 1          | kate       |
      | 2          | mary       |
      | 3          | bob        |
      | 4          | tom        |
      | 5          | lulu       |
      | 6          | mark       |
      | 7          | haward     |
      | 8          | lilei      |
      | 9          | hanmeimei  |
      | 10         | lily       |
      | 11         | lucy       |
      +------------+------------+

      頻繁更新的維度資料表儲存在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      |
      +------------+------------+------------+------------+
      | 1          | kate       | 1          | kate       |
      | 2          | mary       | 2          | mary       |
      | 3          | bob        | 3          | bob        |
      | 4          | tom        | 4          | tom        |
      | 5          | lulu       | 5          | lulu       |
      | 6          | mark       | 6          | mark       |
      | 7          | harward    | 7          | harward    |
      | 8          | lilei      | 8          | lilei      |
      | 9          | hanmeimei  | 9          | hanmeimei  |
      | 10         | lily       | 10         | lily       |
      | 11         | lucy       | 11         | lucy       |
      | 12         | alice      | 12         | alice      |
      +------------+------------+------------+------------+

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

雙簽名是MaxCompute和Hologres共同研發的認證及鑒權協議，在MaxCompute側使用帳號登入資訊加簽名後，把認證資料傳遞給Hologres側，Hologres根據MaxCompute底層達成的協議，進行同名認證及鑒權。這樣只需要在MaxCompute和Hologres有相同的帳號，就可以直接進行外部表格訪問，不需要額外設定認證資訊。

• 前提條件。

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

• 使用限制。

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

• 命令樣本。

  您可直接登入MaxCompute用戶端，結合建立Hologres外部表格文法通過雙簽名模式建立Hologres外部表格。

  --建立外部表格
  CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
  (
    id int,
    name string,
    ds string
  )
  STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
  location 'jdbc:postgresql://hgprecn-cn-zvp2o6aq****-cn-beijing-internal.hologres.aliyuncs.com:80/mc_test?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=mf_holo_mc_up/'
  TBLPROPERTIES (
    'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
    'odps.federation.jdbc.target.db.type'='holo',
    'odps.federation.jdbc.colmapping'='id:id,name:name,ds:ds'
  );
  
  --查詢外部表格（必須與開啟雙簽名開關命令一起執行）
  SET odps.sql.common.table.planner.ext.hive.bridge=true;
  SELECT * FROM holo_mc_external_dbl;

開啟Hologres外部表格儲存直讀功能

背景介紹

使用限制

開啟方式

SET odps.table.api.enable.holo.table=true;

直讀驗證

可以在Logview裡查看日誌，判斷查詢是否走了直讀模式，Logview使用詳情請參見使用Logview 2.0查看作業運行資訊。

<project_name>.<table_name>:<訪問方式>[<(回退原因)>]