AnalyticDB:Getting started with Enterprise Edition, Basic Edition, and Data Lakehouse Edition

Develop external-table jobs using Spark SQL

Follow these steps:

1. Create an OSS bucket and directory in the same region as your AnalyticDB for MySQL cluster. For details, see Enable OSS, Create an OSS bucket in the console, and Create a directory.

2. Create an OSS Hudi external table.

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

   2. In the SQLConsole window, select the Spark engine, a Job resource group, or an Interactive resource group with Spark engine.

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

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

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

      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');

3. Query OSS Hudi external table data.

   1. Enter the following statement and click Run SQL (F8) to query data in the external table spark_hudi_table.

      SELECT * FROM spark_external_db.spark_hudi_table;

      Note

      If your Data Lakehouse Edition cluster has compute reserved resources, or your Enterprise Edition cluster has reserved resources, you can use Xihe MPP SQL online mode (select Xihe engine and Interactive resource group) to query external table data.

   2. On the Job Development > Spark JAR Development page, in the Applications list, click Actions next to your SQL query, then click Logs. View the table data in the logs.

Develop external-table jobs using Xihe BSP SQL

Follow these steps:

1. Create an OSS bucket and directory in the same region as your AnalyticDB for MySQL cluster. For details, see Enable OSS, Create an OSS bucket in the console, and Create a directory.

2. Upload sample data.

   This example uploads the data file xihe_oss.txt to the OSS path oss://adb-test-1/test_xihe/. Rows are separated by line feeds. Columns are separated by commas (,). Sample data:

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

3. Create an OSS external table.

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

   2. In the SQLConsole window, select a Job resource group and the Xihe engine.

   3. Enter the following statement and click Run SQL (F8) to create an external database named xihe_external_db.

      CREATE EXTERNAL DATABASE xihe_external_db;

   4. Enter the following statement and click Run SQL (F8) to create an external table named xihe_oss_table.

      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';

4. Query the OSS external table.

   Enter the following statement and click Run SQL (F8) to query data in the external table xihe_oss_table.

   SELECT * FROM xihe_external_db.xihe_oss_table;

   Note

   If your Data Lakehouse Edition cluster has compute reserved resources, you can use Xihe MPP SQL online mode (select Xihe engine and Interactive resource group) to query external table data.

Develop internal-table jobs using Spark SQL

1. Create an ODS layer data table.

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

   2. In the SQLConsole window, select the Spark engine and a Job resource group.

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

      CREATE DATABASE test_spark_db;

   4. Create an index-free, partitioned table named adb_spark_ods in the test_spark_db database for the ODS layer, then insert data.

      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);

2. Create a DWD layer data table.

   This tutorial reads and filters data from the ODS layer table, then writes it to the DWD layer table.

   1. Create an index-free, partitioned table named adb_spark_dwd in the test_spark_db database.

      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 querying offline hot data, in the SQLConsole window, select the Xihe engine and the (Interactive) user_default resource group, then run these 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 do not set these parameters correctly before querying offline hot data, subsequent SQL statements will fail.

   3. Switch to the Spark engine, read data from the ODS layer table adb_spark_ods where the id column is not 002, 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 in the adb_spark_dwd table.

      SELECT * FROM test_spark_db.adb_spark_dwd;

      Note

      When you run a SELECT statement using Spark SQL, results do not display table data. To view table data, follow the optional step below.

   5. Optional: On the Spark JAR development page, in the Applications list, click Actions next to your SQL query, then click Logs. View the table data in the logs.

3. Create ADS layer data.

   Data in the ADS layer is derived from the DWD layer through refined filtering and is ready for direct use in business analysis. Because ADS layer tables require fast query performance, you must add an index when creating an ADS layer table. This tutorial reads data from the DWD layer table adb_spark_dwd where the value in the `age` column is greater than 15, and writes the data to the ADS layer table adb_spark_ads.

   1. Create an indexed, partitioned table named adb_spark_ads in the test_spark_db database.

      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 with an age greater than 15 from the DWD layer data table adb_spark_dwd and write it to the ADS layer data 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;

4. Query ADS layer table data.

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

   1. In the SQLConsole window, select the Xihe engine and the (Interactive) user_default resource group.

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

      SELECT * FROM test_spark_db.adb_spark_ads;

      Results:

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

Develop jobs using Xihe BSP SQL

1. Create an ODS layer data table.

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

   2. In the SQLConsole window, select the Xihe engine and a Job resource group.

   3. Enter the following statement and click Run SQL (F8). Create a database named test_xihe_db.

      CREATE DATABASE test_xihe_db;

   4. Enter the following statement and click Run SQL (F8). In the test_xihe_db database, create an index-free, partitioned table named adb_xihe_ods for the ODS layer, then insert 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);

2. Create a DWD layer data table.

   This tutorial reads and filters data from the ODS layer table, then writes it to the DWD layer table.

   1. Enter the following statement and click Run SQL (F8). In the test_xihe_db database, create an index-free, partitioned table named adb_xihe_dwd.

      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 reads data from the Operational Data Store (ODS) layer table adb_xihe_ods where the id column is not 002 and writes it to the DWD layer 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 Run SQL (F8). Query data in the adb_xihe_dwd table.

      SELECT * FROM test_xihe_db.adb_xihe_dwd;

      Results:

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

3. Create an ADS layer data table.

   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 tutorial, you will read data from the DWD layer table adb_xihe_dwd where the age column is greater than 15 and write it to the ADS layer table adb_xihe_ads.

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

      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. You can enter the following statement and click Run SQL (F8). Read data where the age column is greater than 15 from the DWD-layer data table adb_xihe_dwd and write it 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;

4. Query ADS layer data.

   AnalyticDB for MySQL Data Lakehouse Edition clusters support both offline data queries (using Spark SQL or Xihe BSP SQL) and online real-time queries (using Xihe MPP SQL). To ensure real-time results, this tutorial uses Xihe MPP SQL online mode to query ADS layer table data.

   1. In the SQLConsole window, select the Xihe engine and the (Interactive) user_default resource group.

   2. Enter the following statement and click Run SQL (F8). Query ADS layer table data.

      SELECT * FROM test_xihe_db.adb_xihe_ads;

      Results:

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