Read and write data to Hudi external tables by using Spark SQL - AnalyticDB

Prerequisites

Before you begin, make sure you have:

An AnalyticDB for MySQL Data Lakehouse Edition cluster with more than 0 AnalyticDB compute units (ACUs) of reserved storage resourcesData Lakehouse Edition

The reserved storage resources for Data Lakehouse Edition clusters must be greater than 0 ACU.
A job resource group created for the cluster. For more information, see Data Lakehouse EditionCreate a resource group.Data Lakehouse Edition
A database account for the cluster

Step 1: Open SQL Development

Log on to the AnalyticDB for MySQL console. In the upper-left corner, select a region. In the left-side navigation pane, click ClustersData Lakehouse Edition. On the Data Lakehouse Edition tab, find your cluster and click the cluster ID.
In the left-side navigation pane, choose Job Development > SQL Development.
On the SQLConsole tab, select the Spark engine and your job resource group.

Step 2: Create a database and a Hudi external table

Run the following SQL statements in batch or interactive mode. For more information, see Spark SQL execution modes.

Create a database

Skip this step if a database already exists.

CREATE DATABASE adb_external_db_hudi
LOCATION 'oss://<bucket_name>/test/';  -- Replace with your OSS path

Create a Hudi external table

CREATE TABLE adb_external_db_hudi.test_hudi_tbl (
  `id`   int,
  `name` string,
  `age`  int
) USING hudi
TBLPROPERTIES (
  primaryKey      = 'id',
  preCombineField = 'age'
)
PARTITIONED BY (age)
LOCATION 'oss://<bucket_name>/test/table/';  -- Replace with your OSS path

Important

Follow these path constraints:

The database and the external table must use the same OSS bucket.
The external table's OSS path must be at least one directory level deeper than the database path, and must be nested within the database path.
primaryKey is required. preCombineField is optional, but if you omit it and run an UPDATE, an error is returned.

Step 3: Write data

Run the following SQL statements in batch or interactive mode. For more information, see Spark SQL execution modes.

INSERT

Use any of the following methods to insert data:

INSERT INTO — appends rows to the table:

INSERT INTO adb_external_db_hudi.test_hudi_tbl VALUES (1, 'lisa', 10), (2, 'jams', 10);

INSERT OVERWRITE — replaces all existing data:

INSERT OVERWRITE adb_external_db_hudi.test_hudi_tbl VALUES (1, 'lisa', 10), (2, 'jams', 20);

INSERT OVERWRITE with a static partition — replaces data in a specific partition:

INSERT OVERWRITE adb_external_db_hudi.test_hudi_tbl PARTITION (age=10) VALUES (1, 'anna');

INSERT OVERWRITE with a dynamic partition — replaces data and derives the partition value from the data:

INSERT OVERWRITE adb_external_db_hudi.test_hudi_tbl PARTITION (age) VALUES (1, 'bom', 10);

UPDATE

Update a specific row — in this example, set name to 'box' for the row where id = 2:

UPDATE adb_external_db_hudi.test_hudi_tbl SET name = 'box' WHERE id = 2;

DELETE

Delete a specific row — in this example, delete the row where id = 1:

DELETE FROM adb_external_db_hudi.test_hudi_tbl WHERE id = 1;

Concurrency control

Hudi external tables use a lock provider-based concurrency control mechanism. Multiple concurrent writes to different data ranges are allowed, but data ranges must not overlap to prevent write conflicts.

If you use an open-source Hudi JAR package, MdsBasedLockProvider is not available for concurrency control. Parameter names in the Hudi configuration use the hoodie.* prefix. This is the original internal naming convention for the Hudi project and is not a typo.

Set the following parameters before running concurrent DML operations. For details about the Hudi concurrency control mechanism, see Concurrency Control.

SET hoodie.cleaner.policy.failed.writes  = LAZY;
SET hoodie.write.concurrency.mode        = OPTIMISTIC_CONCURRENCY_CONTROL;
SET hoodie.write.lock.provider           = org.apache.hudi.sync.adb.MdsBasedLockProvider;

Parameter	Value	Required	Description
`hoodie.cleaner.policy.failed.writes`	`LAZY`	Yes	Dirty data cleanup policy. `LAZY` defers cleanup of incomplete writes until the heartbeat expires, which is required for concurrent write scenarios.
`hoodie.write.concurrency.mode`	`OPTIMISTIC_CONCURRENCY_CONTROL`	Yes	Concurrency mode. `OPTIMISTIC_CONCURRENCY_CONTROL` allows multiple writers and checks for conflicts before each write completes; if a conflict is detected, the write fails.
`hoodie.write.lock.provider`	`org.apache.hudi.sync.adb.MdsBasedLockProvider`	Yes	Lock provider class. Must be a subclass of `org.apache.hudi.common.lock.LockProvider`.

Step 4: Query data

Run the following SQL statement in batch or interactive mode. For more information, see Spark SQL execution modes.

Spark SQL returns a success or failure message, not query results. To view the data, go to the Spark JAR Development page, click the Applications tab, and click Logs in the Actions column for your application. For more information, see the View information about a Spark application section of the Spark editor topic.

SELECT * FROM adb_external_db_hudi.test_hudi_tbl;