Hologres:Access a Paimon catalog using DLF

Scenarios

The SLR mode is suitable for scenarios where you use your current identity to access data in an external database. By default, Hologres uses SLRs and identity pass-through to implement cross-service access.

Procedure

1. For newly purchased instances or instances upgraded to Hologres V2.2 or later, the system automatically creates an SLR. You can skip this step.

   Note

   For Hologres instances earlier than V2.2, go to the Quick Authorization for Access Control page to create an SLR.

2. Connect to the Hologres instance and create an external database.

   CREATE EXTERNAL DATABASE EXT_DATABASE_NAME WITH
     catalog_type 'paimon',
     metastore_type 'dlf-rest',
     dlf_catalog 'DLF_CATALOG_NAME',
     comment 'paimon catalog on dlf';

3. View the schemas and tables in the external database.

   -- Query the schemas in the external database.
   SELECT * FROM hologres.hg_external_schemas('<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>'</span>);
   
   -- Query the tables in a schema.
   SELECT * FROM hologres.hg_external_tables ('<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>'</span>, '<span class="var-span" contenteditable="true" data-var="EXT_SCHEMA_NAME"><ext_schema_name>'</span>);

4. Query the table data.

   SELECT * FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME;

Scenarios

The STS mode is suitable for scenarios where you use cross-account access or a Hologres custom account (BASIC account) to access data in an external database.

Procedure

1. Log on to the RAM console, create a RAM role, and grant the AliyunDLFFullAccess or AliyunDLFReadOnlyAccess permission to the role. For more information, see Create a RAM role and grant permissions to it.

2. Add a trust policy to allow the RAM role to be assumed by the trusted Alibaba Cloud service, Hologres. For more information, see Modify the trust policy of a RAM role.

   {
     "Statement": [
       {
         "Action": "sts:AssumeRole",
         "Effect": "Allow",
         "Principal": {
           "Service": [
             "hologres.aliyuncs.com"
           ]
         }
       }
     ],
     "Version": "1"
   }

3. Log on to the Data Lake Formation console. In the destination catalog, grant the RAM role the permissions to access the database and tables.

4. In Hologres, create an external database and specify the preceding RAM role in the authentication policy.

   CREATE EXTERNAL DATABASE <ext_database_name> WITH
     catalog_type 'paimon'
     metastore_type 'dlf-rest'
     dlf_catalog 'paimon_catalog' 
     rolearn 'acs:ram::106380604****:role/***-ramrole'
     comment 'paimon catalog on dlf'
     ;

5. In Hologres, create a user mapping to associate the RAM user or BASIC account that needs to access the DLF catalog with the RAM role.

   CREATE USER MAPPING FOR "<RAM user | basic account>"
   EXTERNAL DATABASE EXT_DATABASE_NAME
   OPTIONS
   (
      rolearn 'acs:ram::10638060***:role/***ramrole'
   );

   Note

   • For more information about user mappings, see CREATE USER MAPPING.

   • For more information about how to create a RAM user, see Create a RAM user.

   • For more information about how to create a BASIC account, see User management.

6. Log on to the Hologres management console as a RAM user or reconnect to the Hologres instance as a BASIC account. Then, execute the following SQL query statement.

   SELECT * FROM <ext_database_name>.<ext_schema_name>.<ext_table_name>;

Collect statistics for external tables

The ANALYZE and AUTO ANALYZE commands collect statistics about the content of tables in a database. The optimizer uses these statistics to generate optimal query plans and improve query efficiency. For more information, see ANALYZE and AUTO ANALYZE.

-- Update the statistics of a table. By default, the statistics of all columns in the table are collected.
analyze <ext_database_name>.<ext_schema_name>.<ext_table_name>;

-- Update the statistics of specific columns. More data is sampled than when you update the statistics of the entire table, which makes the statistics more accurate. This is mainly used to update conditional columns.
analyze <ext_database_name>.<ext_schema_name>.<ext_table_name>(<colname>, <colname>);

-- Enable Auto Analyze.
ALTER EXTERNAL DATABASE <ext_database_name> WITH enable_auto_analyze 'true';

Query the Paimon Snapshots system table

SELECT * FROM hologres.hg_list_snapshots('EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME');

The following table describes the mapping between the fields in the Snapshots system table and the fields in the Paimon system table.

You can also use the hologres.hg_list_versions function to query the Paimon Snapshots system table. For Paimon table queries, a version corresponds to a snapshot ID.

SELECT * FROM hologres.hg_list_versions('EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME');

Query the Paimon Tag system table

SELECT * FROM hologres.hg_list_tags('<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>.<ext_schema_name>.<ext_table_name>'</span>);

The following table describes the mapping between the fields in the Tag system table and the fields in the Paimon system table.

Query a historical Paimon snapshot based on a timestamp

SELECT * FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME FOR TIMESTAMP AS OF 'TIMESTAMP';

Query a historical Paimon snapshot based on a version

For Paimon tables, a version corresponds to a snapshot ID.

SELECT * FROM '<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>.<ext_schema_name>.<ext_table_name>'</span> FOR VERSION AS OF '<span class="var-span" contenteditable="true" data-var="VERSION"><version>'</span>;

Query a historical Paimon snapshot based on a tag

SELECT * FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME FOR TAG AS OF 'TAG';

Query the Branch system table

SELECT * FROM hologres.hg_list_branches('<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>.<ext_schema_name>.<ext_table_name>'</span>);

The following table describes the mapping between the fields in the Branch system table and the fields in the Paimon system table.

Query data from a specified branch

SELECT * FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME FOR branch AS OF 'TAG';

Query historical snapshot data from a specified branch

-- Query based on a timestamp.
SELECT * FROM '<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>.<ext_schema_name>.<ext_table_name>'</span> FOR branch AS OF '<span class="var-span" contenteditable="true" data-var="BRANCH_NAME"><branch_name>'</span> TIMESTAMP AS OF '<span class="var-span" contenteditable="true" data-var="TIMESTAMP"><timestamp>'</span>;

-- Query based on a tag.
SELECT * FROM '<span class="var-span" contenteditable="true" data-var="EXT_DATABASE_NAME"><ext_database_name>.<ext_schema_name>.<ext_table_name>'</span> FOR branch AS OF '<span class="var-span" contenteditable="true" data-var="BRANCH_NAME"><branch_name>'</span> TAG AS OF '<span class="var-span" contenteditable="true" data-var="TAG_NAME"><tag_name>'</span>;