MaxCompute:Delta Lake or Apache Hudi storage mechanism based on Hadoop clusters

Start Spark SQL

EMR Hudi 0.8.0 supports Spark SQL for read and write operations on Hudi tables. For more information, see Integrate Hudi with Spark SQL.

Run the following command to start Spark SQL:

spark-sql \
  --conf 'spark.serializer=org.apache.spark.serializer.KryoSerializer' \
  --conf 'spark.sql.extensions=org.apache.spark.sql.hudi.HoodieSparkSessionExtension'

Create a Hudi table

If no database exists, data is stored in the default database automatically.

-- Create a table
CREATE TABLE h0 (
  id    BIGINT,
  name  STRING,
  price DOUBLE,
  ts    LONG
) USING hudi
tblproperties (
  primaryKey      = 'id',
  preCombineField = 'ts'
);

-- Insert a row
INSERT INTO h0 VALUES (1, 'a1', 10, 1000);

-- Query the table
SELECT id, name, price, ts FROM h0;

Create an external project in DataWorks

1. Log on to the DataWorks console and select a region.

2. In the left-side navigation pane, choose Others > Lake and Warehouse Integration (Data Lakehouse).

3. On the Lake and Warehouse Integration (Data Lakehouse) page, click Start.

4. On the Create Data Lakehouse page, configure the parameters described in the following tables.

   Create Data Lakehouse step

   Parameter	Description
   External Project Name	The name of the external project. Example: test_extproject_ddd.
   MaxCompute Project	The name of the MaxCompute project. Example: test_lakehouse.

   Create Data Lake Connection step

   Parameter	Description
   Heterogeneous Data Platform Type	Select Alibaba Cloud E-MapReduce/Hadoop Cluster.
   Network Connection	Select an existing network connection. For more information, see Create an external data lake connection.
   External Data Source	Select an existing external data source. For more information, see Create an external data lake connection.

   Create Data Mapping step

   Parameter	Description
   External Data Source Object	Defaults to the value of External Data Source.
   Destination Database	The database in the Hadoop cluster.

5. Click Complete Creation and Preview, then click Preview. If the database tables in the Hadoop cluster appear in the preview, the external project is created successfully.

   To manage external projects using SQL statements instead, see Use SQL statements to manage an external project.

Query data from the external project

1. In the left-side navigation pane of the DataWorks console, choose Others > Lake and Warehouse Integration (Data Lakehouse).

2. On the Ad Hoc Query page, create an ODPS SQL node to view tables in the external project:

   For instructions on running ad hoc queries in DataWorks, see Use an ad hoc query node to execute SQL statements (Optional).

   SHOW TABLES IN test_extproject_ddd;

   Expected output:

   ALIYUN$***@test.aliyunid.com:h0

3. Query the table data:

   SELECT * FROM test_extproject_ddd.h0;

   The query returns the rows inserted in Step 2.

4. Log on to the Hadoop cluster using Workbench and go to the Spark SQL terminal. Insert a new row into the h0 table:

   INSERT INTO h0 VALUES (2, 'a2', 11, 1000);

5. Back on the Ad Hoc Query page in the DataWorks console, verify the update:

   SELECT * FROM test_extproject_ddd.h0 WHERE id = '2';

   The query returns the newly inserted row, confirming that MaxCompute reflects Hadoop cluster updates in real time.