AnalyticDB:Use XIHE BSP SQL to perform job development

Step 1: Create a Hudi table for the ODS layer

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 a job resource group and the XIHE engine.

7. Enter the following statement and click Execute (F8) to create a database named test_xihe_db:

   CREATE DATABASE test_xihe_db;

8. Enter the following statement and click Execute (F8) to create a table named adb_xihe_ods that has partitions but no indexes for the ODS layer in the test_xihe_db database and insert data into the table:

   CREATE TABLE test_xihe_db.adb_xihe_ods
     (id int,
      name string,
      age int)
   DISTRIBUTED BY HASH (id)
   PARTITION BY VALUE (age)
   LIFECYCLE 4
   INDEX_ALL='N';

   INSERT INTO test_xihe_db.adb_xihe_ods(id,name,age) VALUES
   (001,'Anna',18),
   (002,'Bob',22),
   (003,'Cindy',12),
   (004,'Dan',25);

Step 2: Create a table for the DWD layer

In this example, data is written from the table at the ODS layer to the DWD layer after being filtered.

1. Enter the following statement and click Execute (F8) to create a table named adb_xihe_dwd that has partitions but no indexes in the test_xihe_db database:

   CREATE TABLE test_xihe_db.adb_xihe_dwd(
     id int,
     name string,
     age int)
   DISTRIBUTED BY HASH (id)
   PARTITION BY VALUE (age)
   LIFECYCLE 4
   INDEX_ALL = 'N';

2. Enter the following statement and click Execute (F8) to write the data whose id column is not 002 from the adb_xihe_ods table at the ODS layer to the adb_xihe_dwd table at the DWD layer:

   INSERT INTO test_xihe_db.adb_xihe_dwd 
   SELECT 
     id,
     name,
     age 
   FROM test_xihe_db.adb_xihe_ods where id != 002;

3. Enter the following statement and click Execute (F8) to query data of the adb_xihe_dwd table:

   SELECT * FROM test_xihe_db.adb_xihe_dwd;

   The following information is returned:

   +------+-------+------+
   | id   | name  | age  |
   +------+-------+------+
   |    4 | Ban   |   25 |
   |    1 | Anna  |   18 |
   |    3 | Cindy |   12 |
   +------+-------+------+

Step 3: Create a table for the ADS layer

Table data at the ADS layer is written from the DWD layer after more fine-grained filtering and can be directly used for business analysis. Tables at the ADS layer must have indexes to meet requirements for fast query response. In this example, the data whose age column is greater than 15 is written from the adb_xihe_dwd table at the DWD layer to the adb_xihe_ads table at the ADS layer.

1. Enter the following statement and click Execute (F8) to create an AnalyticDB for MySQL table named adb_xihe_ads that has both indexes and partitions in the test_xihe_db database:

   CREATE TABLE test_xihe_db.adb_xihe_ads
      (id int,
      name string,
      age int)
   DISTRIBUTE BY HASH (id)
   PARTITION BY VALUE (age)
   LIFECYCLE 4; 

2. Enter the following statement and click Execute (F8) to write the data whose age column is greater than 15 from the adb_xihe_dwd table at the DWD layer to the adb_xihe_ads table at the ADS layer:

   INSERT INTO test_xihe_db.adb_xihe_ads 
   SELECT 
     id,
     name,
     age 
   FROM test_xihe_db.adb_xihe_dwd WHERE age > 15;

Step 4: Query table data at the ADS layer

AnalyticDB for MySQL Enterprise Edition, Basic Edition, and Data Lakehouse Edition allows you to query data by using Spark SQL or XIHE BSP SQL in batch mode, or by using XIHE MPP SQL in real-time mode. In this example, XIHE MPP SQL is used to query table data at the ADS layer in real-time mode to ensure data timeliness.

1. On the SQLConsole tab, select the XIHE engine and the interactive user_default resource group.

2. Enter the following statement and click Execute (F8) to query the table data at the ADS layer:

   SELECT * FROM test_xihe_db.adb_xihe_ads;

   The following information is returned:

   +------+-------+------+
   | id   | name  | age  |
   +------+-------+------+
   |    4 | Ban   |   25 |
   |    1 | Anna  |   18 |
   +------+-------+------+