Hologres:Access MaxCompute Delta tables

Limitations

Prerequisites

Before you begin, ensure that you have:

• An active Hologres instance. See Purchase a Hologres instance.

• An active MaxCompute project. See Activate MaxCompute.

• The required permissions on the MaxCompute project and tables. See Manage user permissions using commands.

Step 1: Create a Delta table in MaxCompute

Create a Delta table in MaxCompute using one of the following methods:

• In DataWorks: attach a MaxCompute data source, then create the table from the DataWorks console.

• Using the MaxCompute local client: run the SQL command directly.

The following example creates a partitioned Delta table named mf_dt:

CREATE TABLE IF NOT EXISTS mf_dt (
  pk  BIGINT NOT NULL PRIMARY KEY,
  val BIGINT NOT NULL
)
PARTITIONED BY (dd STRING, hh STRING)
TBLPROPERTIES ("transactional"="true");

The "transactional"="true" property marks the table as a Delta table.

Step 2: Write data to the Delta table

MaxCompute supports two methods for loading data into a Delta table:

• Batch: Use INSERT INTO or INSERT OVERWRITE. See Insert or overwrite data.

• Near-real-time: Use Flink to stream data. See Use Flink to write data to MaxCompute.

The following example uses batch inserts to load data into the mf_dt table:

-- Append rows to the dd='01', hh='01' partition
INSERT INTO TABLE mf_dt PARTITION (dd='01', hh='01')
VALUES (3, 30), (4, 4), (5, 5);

-- Overwrite the dd='01', hh='01' partition with new data
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='01')
VALUES (1, 1), (2, 2), (3, 3);

Step 3: Map the MaxCompute project in Hologres

Create an external data source mapping in Hologres so it can discover and query the MaxCompute tables. Two mapping methods are available:

• External database: maps all tables in a MaxCompute project with a single command. See CREATE EXTERNAL DATABASE.

• Foreign table: maps individual schemas and tables for fine-grained control. See IMPORT FOREIGN SCHEMA and CREATE FOREIGN TABLE.

The following example creates an external database mapping for a MaxCompute project:

CREATE EXTERNAL DATABASE ext_database_mc WITH
  metastore_type 'maxcompute'
  mc_project      'mc_3_layer_project'
  comment         'mc three layer project';

After creating the mapping, verify that the schemas and tables are discoverable:

-- List all schemas in the external database
SELECT * FROM hologres.hg_external_schemas('ext_database_mc');

-- List all tables in the default schema
SELECT * FROM hologres.hg_external_tables('ext_database_mc', 'default');

Step 4: Query the Delta table

Before querying a Delta table, set the following GUC parameter at the session level:

SET hg_experimental_external_catalog_routing = 'odps:common_table';

Then query the table using the three-part name external_database.schema.table:

SELECT * FROM ext_database_mc.mc_schema_name.mf_dt;

What's next

• To update or refresh the external database metadata after schema changes in MaxCompute, see CREATE EXTERNAL DATABASE.

• To control table-level access, use foreign tables instead of an external database. See CREATE FOREIGN TABLE.

• To manage user permissions on the MaxCompute side, see Manage user permissions using commands.