Hologres:access paimon data using dlf catalog

Scenarios

The SLR mode applies to scenarios where you access External Database data using your current user identity. Hologres uses an SLR and identity pass-through by default to achieve cross-cloud product access.

Procedure

1. For instances that are newly purchased or upgraded to Hologres V2.2 or later, the system automatically creates a service-linked role. You do not need to create it manually. You can skip this step.

   Note

   For Hologres instances that run versions earlier than V2.2, you must use the Resource Access Management Quick Authorization page to create a service-linked role.

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 'dlf catalog';
   

3. View the schemas and tables in the External Database.

   -- Query 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 tables in the 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 table data.

   SELECT * FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME;

Scenarios

The STS mode applies to scenarios that involve cross-account access and scenarios where Hologres custom accounts (BASIC accounts) access External Database data.

Procedure

1. Log on to the Resource Access Management (RAM) console, create a RAM role, and grant the AliyunDLFFullAccess or AliyunDLFReadOnlyAccess permission for DLF access. For more information, see Create a RAM Role and Grant Permissions.

2. Update the 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 and grant access permissions for databases and tables to the RAM role in the target Catalog.

4. Create an External Database in Hologres. Specify the previously created RAM role as 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 'dlf catalog'
     ;

5. Create a USER MAPPING in Hologres 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 MAPPING, 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. After you log on to the Hologres console using a RAM user or re-connecting to a Hologres instance using a BASIC account, you can execute the following SQL query.

   SELECT * FROM EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME;

Collect external table statistics

You can use the ANALYZE and AUTO ANALYZE commands to collect statistics about table content in your database. The optimizer uses these statistics to generate optimal query plans and improve query performance. For more information, see ANALYZE and AUTO ANALYZE.

-- Update statistics for a table. By default, this command collects statistics for all columns in the table.
analyze EXT_DATABASE_NAME.EXT_SCHEMA_NAME.EXT_TABLE_NAME;

-- Update statistics for specific columns. This command samples more data than a full-table update and provides more accurate results. Use this command mainly for columns that appear in filter conditions.
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 Paimon Snapshots System Table

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

The fields in the Snapshots system table correspond to the Paimon system table fields as follows:

You can also query the Paimon Snapshots system table using the hologres.hg_list_versions function. For Paimon table queries, Version corresponds to Snapshot ID.

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

Query Paimon Tag System Table

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

The fields in the Tag system table correspond to the Paimon system table fields as follows:

Query Paimon Historical Snapshots Based on Timestamp

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

Query Paimon Historical Snapshots Based on Version

For Paimon tables, Version corresponds to 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 Paimon Historical Snapshots Based on 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 TAG AS OF '<span class="var-span" contenteditable="true" data-var="TAG">TAG'</span>;

Query Branch System Table

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

The fields in the Branch system table correspond to the Paimon system table fields as follows:

Query Specified Branch Data

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="TAG">TAG'</span>;

Query Historical Snapshots of a Specified Branch

-- Query based on 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 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 '<span class="var-span" contenteditable="true" data-var="TAG_NAME">TAG_NAME'</span>;