MaxCompute：Hologres外部表格

注意事項

文法說明

在建立外部表格時，需要在建表DDL語句中指定StorageHandler，並配置STS認證資訊（或開啟服務關聯角色授權模式開關）、JDBC串連地址，以實現訪問Hologres資料來源。

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>,...'
);

-- 建立外部表格
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資料

如果已有Hologres資料庫、Hologres表及測試資料，可忽略該步驟。

步驟二：建立Hologres外部表格

使用說明

使用樣本

1. 資料準備。在Hologres執行個體中建立測試表並插入資料。

   CREATE TABLE test_multi_type_ppd (
     c_id INT NOT NULL,
     c_boolean BOOLEAN,
     c_int INT,
     c_bigint BIGINT,
     c_double DOUBLE precision,
     c_float REAL,
     c_string TEXT,
     c_date DATE,
     c_timestamp_tz TIMESTAMPTZ,
     c_timestamp_ntz TIMESTAMP
   )
   WITH (SEGMENT_KEY = 'c_id');
   -- 插入 10 條資料
   INSERT INTO test_multi_type_ppd VALUES
   (1,  true,   10,  1000,   1.25,  1.5,  'alpha',   '2023-01-01', '2023-01-01 10:00:00+08', '2023-01-01 02:00:00'),
   (2,  false,  20,  2000,  -2.50,  2.5,  'beta',    '2023-01-02', '2023-01-02 10:00:00+08', '2023-01-02 02:00:00'),
   (3,  true,   30,  3000,   3.75, -3.5,  'gamma',   '2023-01-03', '2023-01-03 10:00:00+08', '2023-01-03 02:00:00'),
   (4,  false,  40,  4000,   4.00,  4.5,  'delta',   '2023-01-04', '2023-01-04 10:00:00+08', '2023-01-04 02:00:00'),
   (5,  true,   50,  5000,   5.50,  5.5,  'epsilon', '2023-01-05', '2023-01-05 10:00:00+08', '2023-01-05 02:00:00'),
   (6,  false,  60,  6000,   6.60,  6.0,  'zeta',    '2023-01-06', '2023-01-06 10:00:00+08', '2023-01-06 02:00:00'),
   (7,  true,   70,  7000,   7.70, -7.0,  'eta',     '2023-01-07', '2023-01-07 10:00:00+08', '2023-01-07 02:00:00'),
   (8,  false,  80,  8000,   8.80,  8.0,  'theta',   '2023-01-08', '2023-01-08 10:00:00+08', '2023-01-08 02:00:00'),
   (9,  true,   90,  9000,  -9.90,  9.0,  'iota',    '2023-01-09', '2023-01-09 10:00:00+08', '2023-01-09 02:00:00'),
   (10, false, 100, 10000,  10.10, 10.0,  'kappa',   '2023-01-10', '2023-01-10 10:00:00+08', '2023-01-10 02:00:00');
   SELECT * FROM test_multi_type_ppd;

2. 在MaxCompute中建立Hologres外部表格。

   CREATE EXTERNAL TABLE test_multi_type_ppd
   (
     c_id int,
     c_boolean boolean,
     c_int int,
     c_bigint bigint,
     c_double double,
     c_float float,
     c_string string,
     c_date date,
     c_timestamp_tz timestamp,
     c_timestamp_ntz timestamp_ntz
   )
   STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
   LOCATION 'jdbc:postgresql://<holo-endpoint>/<db>?currentSchema=public&table=test_multi_type_ppd'
   TBLPROPERTIES (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo'
   );

3. 查詢外部表格資料。

   -- 啟用 Holo 直讀
   SET odps.table.api.enable.holo.table=true;
   -- 設定 PPD 模式（off/on/debug）
   SET odps.external.holo.read.table.rules="{predicate_pushdown_mode:on}";
   -- 類型系統設定
   SET odps.sql.type.system.odps2=true;
   SET odps.sql.decimal.odps2=true;
   -- 允許運算式下推到 TableScan，一般情況下，預設是開啟的
   SET odps.use.builtin.function.input.range=true;
   -- set odps.mcqa.disable=true;
   SELECT * FROM test_multi_type_ppd WHERE c_id = 3;
   -- 返回結果如下。
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   | c_id | c_boolean | c_int | c_bigint   | c_double   | c_float | c_string | c_date | c_timestamp_tz | c_timestamp_ntz |
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   | 3    | true      | 30    | 3000       | 3.75       | -3.5    | gamma    | 2023-01-03 | 2023-01-03 10:00:00 | 2023-01-03 02:00:00 |
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   -- 查看log view中Summary明細，會出現如下提示
   external holo tables:
   	**.default.test_multi_type_ppd:Optimized
     Holo-side PPD enabled -->   Filter pushdown applied successfully
     Snapshot Id: 56-1-100***
     With-filter file count: 2
     With-filter create snapshot duration: 19ms

通過MaxCompute直讀Hologres資料時，報錯ODPS-0130071，如何解決？

• 問題現象

  直讀Hologres資料時，報錯ODPS-0130071 Failed to split to equal size...max count: 7777。例如：

  ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: storage/table/src/input_splits_builder.cpp(195): StorageException: Failed to split to equal size, total size: 2143570729934, min size: 268435456, max size: 272629760, max count: 7777, split size: 275629513, split count: 7777

• 產生原因

  MaxCompute直讀Hologres中的資料時，按照預設的拆分Mapper策略（輸入資料量/split.size 256MB），導致任務產生的Mapper並發數超過了最大限制數7777。

  說明

  該限制是防止使用者直接提交產生大量Mapper任務，從而對Hologres檔案或網路連接的穩定性產生影響。

• 解決措施

  您可以通過設定以下參數，解決報錯問題。

  SET odps.external.holo.mapper.instances=10000; -- 提高並發上限，最高不超過10000。
  SET odps.sql.mapper.split.size=512; -- 調整任務並發數，最高不超過512M

基於Hologres外部表格執行SQL作業時，運行慢，如何解決？

使用SDK搜尋MaxCompute外部表格資料速度慢。

• 問題現象

  使用SDK搜尋MaxCompute外部表格資料速度慢。

• 解決措施

  外部表格僅支援全量搜尋，所以較慢，建議您改用MaxCompute內部表。

當Hologres側使用關鍵字作為列名時報錯，如何解決？

• 問題現象

  當Hologres側使用關鍵字作為列名，如果不加特殊配置，會出現如下報錯：

  ODPS-0123131:User defined function exception - SQLException in nextKeyValue

  Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","

• 解決措施

  添加參數映射Hologres源表和Hologres外部表格欄位關係：odps.federation.jdbc.colmapping。

  例如：Hologres源表包含關鍵字欄位"offset"，則建立Hologres外部表格時需要添加參數為'odps.federation.jdbc.colmapping'='offset:"offset"'。

當通過雙簽名模式讀取外部表格時，報錯password authentication failed for user "mcslr$STS.******"，如何解決？

• 問題現象

  當子帳號或RamRole使用者通過服務關聯角色授權模式（雙簽名模式）讀取Hologres外表時，報錯password authentication failed for user "mcslr$STS.******"，詳情如下：

  ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: splitByCommonTable() failed, dataSourceId=[TableScan1], tableId=[*****.*****], location=[jdbc:postgresql://hgpostcn-cn-*****-cn-beijing-internal.hologres.aliyuncs.com:80/*****?ApplicationName=MaxCompute&currentSchema=public&useSSL=false&table=*****/], rootCause -> common/table/jni/utils/jni_helper.cpp(126): UnretryableException: Common table io jni exception - ExceptionType: com.aliyun.odps.hive.wrapper.thrift.RequestException
  Message: null
  StackTrace:
  RequestException(reason:java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeException: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "mcslr$STS.*********"

• 解決措施

  報錯顯示目前使用者沒有Hologres側的讀表許可權，需要主帳號或管理員帳號登入Hologres控制台，進入資訊安全中心，添加當前報錯使用者到使用者管理並進行DB授權操作。

直讀模式使用ppd報錯Holo-side PPD disabled --> The Holo snapshot PPD mode is enabled, but FAILED to push down the filter into Holo

• 報錯資訊

  Holo-side PPD disabled --> The Holo snapshot PPD mode is enabled, 
  but FAILED to push down the filter into Holo, 
  errorMsg[storage/table/src/holo/snapshot.cpp(319): 
  StorageException: Create/Get snapshot failed for table: test_multi_type_ppd partition:  
  Query:select holo_create_table_snapshot('public.test_multi_type_ppd', 259200, filter=>$$c_id = 3$$) with error ERROR:  
  internal error: Failed to build fragment dag desc: node type 65 is not supported

• 解決措施

  請排查Hologres內表是否符合使用說明中的要求。