MaxCompute:Hologres external tables

External table creation rules

• If you enable the IP address whitelist for Hologres, you must create the Hologres external table in double-signature mode. If you create it in STS mode, MaxCompute access to Hologres is blocked by the IP address whitelist.

• For parent and child tables in Hologres, the table name is specified in the Hologres external table and SQL statements are executed. Both parent and child tables can be mapped to a Hologres external table, but parent tables are read-only.

• When you write data to a Hologres external table, the INSERT ON CONFLICT (UPSERT) mechanism of Hologres is not supported. If the Hologres source table has a primary key, ensure that the data you write does not violate primary key uniqueness constraints in the source table.

• Table names and field names are case-insensitive. You cannot force case conversion when creating, querying, or referencing tables and fields.

Schema compatibility

If the schema of the Hologres source table differs from that of the external table:

• Inconsistent number of columns: If the Hologres source table has fewer columns than the Data Definition Language (DDL) statement of the external table, an error occurs when reading data from Hologres. For example, the error message column "xxx" does not exist appears. If the Hologres source table has more columns than the DDL statement, the extra columns are ignored.

• Inconsistent column types: MaxCompute does not support using the INT type to receive STRING data from a Hologres source table. You can use the STRING type to receive INT data, but this is not recommended.

Create a Hologres external table in STS mode

For sample code, see Create a Hologres external table (STS mode).

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 a Hologres external table in double-signature mode

For sample code, see Create a Hologres external table (double-signature mode).

A Hologres external table created in double-signature mode allows the same RAM user to access authorized tables in MaxCompute and Hologres without manual authorization. This mode also supports the IP address whitelist feature of Hologres.

-- Enable the double-signature mode.
SET odps.sql.common.table.planner.ext.hive.bridge=true;
-- 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>,...']
);

Create a Hologres database

1. Log on to the Hologres Management Console, and select a region in the top-left corner.

2. In the left navigation pane, select Instances.

3. If you do not have a Hologres instance, you must first purchase a Hologres instance.

   On the Instances page, click the name of the target instance.

4. On the instance details page, click Connect To Instance.

5. Click the Metadata Management tab above.

   Click Create Database. In the dialog box that appears, enter a Database Name and keep the default values for other parameters.

Create a Hologres table

1. On the instance details page, click Log On To Instance.

2. Click the SQL Editor tab.

3. Enter the following statements:

   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;

Prerequisites

1. Install and configure the MaxCompute client.

2. Prepare a MaxCompute project for creating the Hologres external table.

   For more information about how to create a MaxCompute project, see Create a MaxCompute project.

3. For information about how to create and grant permissions to a RAM role, see Authorize a regular RAM role in STS mode for Hologres.

Create an external table in MaxCompute

1. Log on to the MaxCompute client and enter the destination MaxCompute project.

2. Run the following command to create a Hologres external table.

   The following Hologres instance information is based on the steps in the Prepare data section:

   • Hologres database name: holo_external_test.

   • Hologres database schema: public.

   • Hologres table name: holo.

   • Classic network endpoint of the Hologres database: 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. Run the following command to query the Hologres source table using the new Hologres external table.

   -- The following properties must be added to access the Hologres external table.
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; -- Enable the JDBC direct read mode. 
   -- Query data from the Hologres external table.
   SELECT * FROM my_table_holo_jdbc limit 10;
   -- The following result is returned.
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 6          | mark       | 
   | 11         | lucy       | 
   +------------+------------+

4. Exchange data and perform join analysis with Hologres using the Hologres external table.

   • Use the Hologres external table to write MaxCompute-processed data to Hologres for accelerated analysis and online services. The following sample command is provided.

     -- The following properties must be added to access the Hologres external table.
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     SET odps.table.api.enable.holo.table=true; -- Enable the JDBC direct read mode. 
     -- Insert data into the Hologres external table.
     INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
     -- Query data from the Hologres external table.
     SELECT * FROM my_table_holo_jdbc;
     -- The following result is returned.
     +------------+------------+
     | 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        | 
     +------------+------------+

   • Store frequently updated dimension tables in Hologres to meet real-time dynamic update requirements. MaxCompute accesses these dimension tables using external tables and performs join analysis with fact tables in MaxCompute. The following sample command is provided.

     -- The following properties must be added to access the Hologres external table.
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     -- Create a MaxCompute internal table.
     CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc;
     -- Perform a join analysis on the MaxCompute internal table and the Hologres external table.
     SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.id=t2.id;
     -- The following result is returned.
     +------------+------------+------------+------------+
     | 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        | 
     +------------+------------+------------+------------+

Prerequisites

1. Install and configure the MaxCompute client.

2. Prepare a MaxCompute project for creating the Hologres external table.

   For more information about how to create a MaxCompute project, see Create a MaxCompute project.

3. An account with the same name as the MaxCompute account exists in Hologres, and this account has read and write permissions on the corresponding table in Hologres.

4. Only Hologres V1.3 and later support creating Hologres external tables in double-signature mode from MaxCompute. Currently, this mode supports only reading data from Hologres external tables. Writing data to Hologres external tables is not supported.

Create an external table in MaxCompute

1. Log on to the MaxCompute client and enter the destination MaxCompute project.

2. Run the following command to create a Hologres external table.

   The following Hologres instance information is based on the steps in the Prepare data section:

   • Hologres database name: holo_external_test.

   • Hologres database schema: public.

   • Hologres table name: holo.

   • Classic network endpoint of the Hologres database: hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80.

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

   -- Query the external table. This command must be executed together with the command that enables the double-signature mode.
   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   SELECT * FROM holo_mc_external_dbl;
   
   -- The following result is returned.
   +------------+------------+
   | 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        | 
   +------------+------------+