AnalyticDB:Use Spark SQL to read and write Delta external tables

Step 1: Go to the SQL Development page

1. Log on to the AnalyticDB for MySQL console.
2. In the upper-left corner of the page, select a region.
3. In the left-side navigation pane, click Clusters.
4. On the Data Lakehouse Edition (V3.0) tab, find the cluster that you want to manage and click the Cluster ID.

5. In the left-side navigation pane, choose Job Development > SQL Development.

6. On the SQLConsole tab, select the Spark engine and a job resource group.

Step 2: Create an external database and a Delta external table

1. Execute the following statement to create a database. If a database already exists, skip this step.

   add jar oss://<bucket_name>/path/to/delta-core_xx.jar;    /* A custom Delta package. You must manually upload the package to OSS. */
   
   add jar oss://<bucket_name>/path/to/delta-storage-xx.jar; /* A custom Delta package. You must manually upload the package to OSS. */
   
   SET spark.adb.connectors=oss;   /* The connector of AnalyticDB for MySQL Spark. Set the connector to OSS. */ 
   
   SET spark.sql.extensions=io.delta.sql.DeltaSparkSessionExtension;  /* An open source Spark parameter. */
   
   SET spark.sql.catalog.spark_catalog=org.apache.spark.sql.delta.catalog.DeltaCatalog;   /* An open source Spark parameter. */
   
   CREATE DATABASE if not exists external_delta_db
   location "oss://<bucket_name>/test/";          /* The location where you want to create the database. Replace the parameter value with your OSS path. */

2. Execute the following statement to create a Delta external table:

   CREATE TABLE if not exists external_delta_db.delta_test_tbl (
     id int, 
     name string, 
     age int
   ) using delta 
   partitioned by (age) 
   location "oss://<bucket_name>/test/delta_test_tbl";

Step 3: Write data to the Delta external table

INSERT

Use one of the following methods to write data to the Delta external table:

• Method 1: Execute the INSERT INTO statement

  INSERT INTO external_delta_db.delta_test_tbl values(1, 'lisa', 10),(2, 'jams', 10);

• Method 2: Execute the INSERT OVERWRITE statement

  INSERT OVERWRITE external_delta_db.delta_test_tbl values (2, 'zhangsan', 10), (4, 'lisi', 30);

• Method 3: Execute the INSERT OVERWRITE statement to write data to a static partition

  INSERT OVERWRITE external_delta_db.delta_test_tbl partition(age=17) values(3, 'anna');

• Method 4: Execute the INSERT OVERWRITE statement to write data to a dynamic partition

  INSERT OVERWRITE external_delta_db.delta_test_tbl partition (age) values (1, 'bom', 10);

UPDATE

Execute the following statement to update data. In this example, the data in the name column with its id value being 1 is updated to box.

UPDATE external_delta_db.delta_test_tbl set name = 'box' where id = 1;

DELETE

Execute the following statement to delete data. In this example, the row whose id value is 1 is deleted.

DELETE FROM external_delta_db.delta_test_tbl where id = 1;

Step 4: Query data

Execute the following statement to query data of the Delta external table:

SELECT * FROM external_delta_db.delta_test_tbl;