Hologres external tables

Considerations

Syntax

When you create an external table, you must specify the StorageHandler in the DDL statement, and configure the JDBC connection string and STS authentication information (or enable the service-linked role authorization mode) to access the Hologres data source.

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 an external table
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>,...']
);

Parameters

Step 1: Prepare Hologres data

You can skip this step if you have a Hologres database, table, and test data.

Step 2: Create a Hologres external table

Notes

Examples

1. Prepare the data. In your Hologres instance, create a test table and insert data.

   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');
   -- Insert 10 rows of data.
   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. Create a Hologres external table in MaxCompute.

   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. Query the external table.

   -- Enable Hologres direct read.
   SET odps.table.api.enable.holo.table=true;
   -- Set the PPD mode (off, on, or debug).
   SET odps.external.holo.read.table.rules="{predicate_pushdown_mode:on}";
   -- Configure the type system.
   SET odps.sql.type.system.odps2=true;
   SET odps.sql.decimal.odps2=true;
   -- Allow expressions to be pushed down to TableScan. This is typically enabled by default.
   SET odps.use.builtin.function.input.range=true;
   -- set odps.mcqa.disable=true;
   SELECT * FROM test_multi_type_ppd WHERE c_id = 3;
   -- The query returns the following result.
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   | 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 |
   +------+-----------+-------+------------+------------+---------+----------+--------+----------------+-----------------+
   -- Check the Summary section in Logview. The following information is displayed:
   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

ODPS-0130071 error when directly reading Hologres data

• Problem

  When you read data from Hologres in direct read mode, the job fails with an error similar to ODPS-0130071 Failed to split to equal size...max count: 7777. For example:

  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

• Cause

  When MaxCompute reads data from Hologres, its default data splitting strategy can cause the number of mapper instances to exceed the 7,777 concurrency limit.

  Note

  This limit prevents jobs with an excessive number of mappers from running, which protects the stability of Hologres files and network connections.

• Solution

  You can resolve this error by setting the following parameters.

  SET odps.external.holo.mapper.instances=10000; -- Increase the concurrency limit. The maximum value is 10,000.
  SET odps.sql.mapper.split.size=512; -- Adjust the job concurrency. The maximum value is 512 MB.

Slow SQL jobs on Hologres external tables

• Problem

  Performance is slow when you use the SDK to search for data in a MaxCompute external table.

• Solution

  External tables perform slowly because they only support full table scans. For better performance, use a MaxCompute internal table instead.

Error when a column name is a keyword

• Problem

  If a column name in your Hologres table is a keyword, queries may fail with the following errors without special configuration:

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

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

• Solution

  Add the odps.federation.jdbc.colmapping parameter to map the columns of the Hologres source table to the columns of the Hologres external table.

  For example, if the Hologres source table contains the keyword "offset" as a column name, add the 'odps.federation.jdbc.colmapping'='offset:"offset"' parameter when you create the Hologres external table.

The password authentication failed for user "mcslr$STS.******" error in double-signature mode

• Problem

  When a RAM user or RAM role reads a Hologres external table by using the service-linked role authorization mode (double-signature mode), the job may fail with the password authentication failed for user "mcslr$STS.******" error. The following provides an error message example:

  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.*********"

• Solution

  This error indicates that the user lacks the required read permissions for the table in Hologres. To resolve this, log on to the Hologres console with your Alibaba Cloud account or an administrator account. In the console, go to Security Center, add the user to User Management, and grant the required database permissions.

The Holo-side PPD disabled --> The Holo snapshot PPD mode is enabled, but FAILED to push down the filter into Holo error in direct read mode

• Error message

  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

• Solution

  Ensure that your Hologres internal table meets the requirements in Usage notes.