Enterprise Edition, Basic Edition, and Data Lakehouse Edition

Create an OSS bucket and directory in the same region as the AnalyticDB for MySQL cluster. For more information, see Activate OSS, Create a bucket, and Manage directories.

Create an OSS Hudi external table.

1. In the left-side navigation pane of the cluster details page, click Job Development > SQL Development > Job Development.

2. In the SQL Console window, select the Spark engine and a Job-based or Interactive resource group.

3. Run the following statement to create an external database named spark_external_db to store Hudi data. This example uses the oss://testBucketName/adb-test-1/ path. To run the statement, click Execute SQL (F8).

   CREATE DATABASE spark_external_db
   LOCATION 'oss://testBucketName/adb-test-1/';

4. In the spark_external_db database, create an external table named spark_hudi_table to store Hudi data. This example uses the oss://testBucketName/adb-test-1/spark_external_db/ path.

   CREATE TABLE spark_external_db.spark_hudi_table
   (id int,
    name string,
    score int,
    city string
   )
   using hudi
   partitioned by (id)
   tblproperties
   (primaryKey = 'id', 
   preCombineField = 'city')
   LOCATION 'oss://testBucketName/adb-test-1/spark_external_db/';

5. Insert data.

   INSERT OVERWRITE spark_external_db.spark_hudi_table PARTITION(id) VALUES
   (001,'Anna',99,'London'),
   (002,'Bob',67,'USA'),
   (003,'Cindy',78,'Spain'),
   (004,'Dan',100,'China');

Query data from the OSS Hudi external table.

1. Run the following statement to query data from the spark_hudi_table external table. To run the statement, click Execute SQL (F8).

   SELECT * FROM spark_external_db.spark_hudi_table;

   Note

   If a Data Lakehouse Edition cluster has reserved compute resources or an Enterprise Edition cluster has reserved resources, you can use XIHE MPP SQL in online mode to query data from the external table. To do this, select the XIHE engine and an Interactive resource group.

2. On the Job Development > Spark JAR Development > Job Development page, find the target SQL query in the Applications and click Logs in the Actions column to view the table data in the logs.

Create an OSS bucket and directory in the same region as the AnalyticDB for MySQL cluster. For more information, see Activate OSS, Create a bucket, and Manage directories.

Upload the sample data.

This example uploads the xihe_oss.txt data file to the oss://adb-test-1/test_xihe/ OSS path. It is a text file where columns are delimited by commas (,) and rows by newlines:

001,Anna,99,London
002,Bob,67,USA
003,Cindy,78,Spain
004,Dan,100,China

Create an OSS external table.

1. In the left-side navigation pane of the cluster details page, click Job Development > SQL Development > Job Development.

2. In the SQL Console window, select a Job-based resource group and the XIHE engine.

3. Run the following statement to create an external database named xihe_external_db. To run the statement, click Execute SQL (F8).

   CREATE EXTERNAL DATABASE xihe_external_db;

4. Run the following statement to create an external table named xihe_oss_table. To run the statement, click Execute SQL (F8).

   CREATE EXTERNAL TABLE xihe_external_db.xihe_oss_table
   (
    id int ,
    name string ,
    score int,
    city string
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
   STORED AS TEXTFILE
   LOCATION 'oss://adb-test-1/test_xihe/xihe_oss.txt';

Query data from the OSS external table.

Run the following statement to query data from the xihe_oss_table external table. To run the statement, click Execute SQL (F8).

SELECT * FROM xihe_external_db.xihe_oss_table;

Create an ODS layer table.

1. In the left-side navigation pane of the cluster details page, click Job Development > SQL Development > Job Development.

2. In the SQL Console window, select the Spark engine and a Job-based resource group.

3. Run the following statement to create a database named test_spark_db. To run the statement, click Execute SQL (F8).

   CREATE DATABASE test_spark_db;

4. In the test_spark_db database, create the partitioned ODS layer table adb_spark_ods without an index, and then insert data into the table.

   CREATE TABLE test_spark_db.adb_spark_ods
     (id int,
      name string,
      age int)
   USING adb
   tblproperties (
   'distributeType' = 'HASH',
   'distributeColumns' = 'id',
   'partitionType' = 'VALUE',
   'partitionColumn' = 'age', 
   'partitionCount' = '200', 
   'indexAll' = 'false')

   INSERT OVERWRITE test_spark_db.adb_spark_ods PARTITION(age) VALUES
   (001,'Anna',18),
   (002,'Bob',22),
   (003,'Cindy',12),
   (004,'Dan',25);

Create a DWD layer table.

This tutorial demonstrates how to read data from the ODS layer table, filter the data, and then write the filtered data to the DWD layer table.

1. In the test_spark_db database, create a partitioned table named adb_spark_dwd without an index.

   CREATE TABLE test_spark_db.adb_spark_dwd (
     id int,
     name string,
     age int
   )
   USING adb
   TBLPROPERTIES(
     'distributeType'='HASH', 
     'distributeColumns'='id', 
     'partitionType'='value',
     'partitionColumn'='age',
     'partitionCount'='200',
     'indexAll'='false')

2. (Optional) Before you query offline hot data, select the XIHE engine and the user_default (Interactive) resource group in the SQL Console window, and then run the following statements.

   SET adb_config CSTORE_HOT_TABLE_ALLOW_SINGLE_REPLICA_BUILD=true;
   SET adb_config ELASTIC_ENABLE_HOT_PARTITION_HAS_HDD_REPLICA=true;
   SET adb_config ELASTIC_PRODUCT_ENABLE_MIXED_STORAGE_POLICY=true;

   Important

   If you query offline hot data without running these statements, subsequent SQL statements fail to execute.

3. Switch to the Spark engine. Read data where the value in the id column is not 002 from the ODS layer table adb_spark_ods, and write the data to the DWD layer table.

   INSERT OVERWRITE test_spark_db.adb_spark_dwd partition(age) 
   SELECT 
     id,
     name,
     age
   FROM test_spark_db.adb_spark_ods WHERE id != 002;

4. Query data from the adb_spark_dwd table.

   SELECT * FROM test_spark_db.adb_spark_dwd;

   Note

   When you use Spark SQL to run a query, the result does not display the table data. To view the table data, perform the next step.

5. Optional: On the Spark JAR Development page, find the target SQL query in the Applications and click Logs in the Actions column. You can view the table data in the logs.

Create ADS layer data.

The ADS layer contains refined data from the DWD layer for business analysis. To ensure query performance, you must index the ADS table. This tutorial demonstrates how to read data where the value in the age column is greater than 15 from the DWD layer table adb_spark_dwd and write it to the ADS layer table adb_spark_ads.

1. In the test_spark_db database, create a partitioned table named adb_spark_ads with an index.

   CREATE TABLE test_spark_db.adb_spark_ads (
     id int,
     name string,
     age int
   )
   USING adb
   TBLPROPERTIES(
     'distributeType'='HASH', 
     'distributeColumns'='id', 
     'partitionType'='value',
     'partitionColumn'='age',
     'partitionCount'='200',
     'indexAll'='true')

2. Read data where the value in the age column is greater than 15 from the DWD layer table adb_spark_dwd and write the data to the ADS layer table adb_spark_ads.

   INSERT OVERWRITE test_spark_db.adb_spark_ads partition(age) 
   SELECT 
     id,
     name,
     age 
   FROM test_spark_db.adb_spark_dwd WHERE age > 15;

Query ADS layer table data.

AnalyticDB for MySQL Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters support offline data queries by using Spark SQL or XIHE BSP SQL, and online real-time data queries by using XIHE MPP SQL. To ensure data timeliness, this tutorial uses XIHE MPP SQL in online mode to query ADS layer table data.

1. In the SQL Console window, select the XIHE engine and the user_default (Interactive) resource group.

2. Run the following statement to query data from the ADS layer table.

   SELECT * FROM test_spark_db.adb_spark_ads;

   The following result is returned:

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

Create an ODS layer table.

1. In the left-side navigation pane of the cluster details page, click Job Development > SQL Development > Job Development.

2. In the SQL Console window, select the XIHE engine and a Job-based resource group.

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

   CREATE DATABASE test_xihe_db;

4. Enter the following statement, and click Execute SQL (F8). This command creates the partitioned table adb_xihe_ods without an index in the ODS layer of the test_xihe_db database and inserts data.

   CREATE TABLE test_xihe_db.adb_xihe_ods (id int, name string, age int)DISTRIBUTED BY HASH (id)PARTITION BY VALUE (age)LIFECYCLE 4INDEX_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);

Create a DWD layer table.

This tutorial demonstrates how to read data from the ODS layer table, filter the data, and then write the filtered data to the DWD layer table.

1. Enter the following statement and click Execute SQL (F8) to create a non-indexed, partitioned table adb_xihe_dwd 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 4INDEX_ALL = 'N';

2. Enter the following statement and click Execute SQL (F8). This statement reads data from the ODS layer table adb_xihe_ods where the id column is not 002 and writes it to the DWD layer data table adb_xihe_dwd.

   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 SQL (F8) to query the data in the adb_xihe_dwd table.

   SELECT * FROM test_xihe_db.adb_xihe_dwd;

   The following result is returned:

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

Create an ADS layer table.

The ADS layer data is more finely filtered from the DWD layer data and can be used directly for business analysis. This requires a certain query speed. Therefore, you must add an index when you create the ADS layer table. This tutorial demonstrates how to read data where the value in the age column is greater than 15 from the DWD layer table adb_xihe_dwd and write it to the ADS layer table adb_xihe_ads.

1. Enter the following statement and click Execute SQL (F8) to create an indexed and partitioned AnalyticDB for MySQL table named adb_xihe_ads in the test_xihe_db database.

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

2. Enter the following statement, and click Execute SQL (F8). This statement reads data from the DWD layer data table adb_xihe_dwd where the value in the age column is greater than 15 and writes the data to the ADS layer data table adb_xihe_ads.

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

Query ADS layer data.

AnalyticDB for MySQL Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters support offline data queries by using Spark SQL or XIHE BSP SQL, and online real-time data queries by using XIHE MPP SQL. To ensure data timeliness, this tutorial uses XIHE MPP SQL in online mode to query ADS layer table data.

1. In the SQL Console window, select the XIHE engine and the user_default (Interactive) resource group.

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

   SELECT * FROM test_xihe_db.adb_xihe_ads;

   The following result is returned:

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