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

External tables using Spark SQL

External tables let you query data stored in OSS directly, without loading it into AnalyticDB for MySQL. This example uses the Hudi table format.

1. Create an OSS bucket and directory in the same region as your cluster. See Enable OSS, Create a bucket, and Create a directory.

2. Create an OSS Hudi external table.

   1. Go to Job Development > SQL Development in the left navigation pane.

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

   3. Create a database to store Hudi data. This example uses the OSS path oss://testBucketName/adb-test-1/. ``sql CREATE DATABASE spark_external_db LOCATION 'oss://testBucketName/adb-test-1/'; ``

   4. Create a Hudi external table named spark_hudi_table in the database. 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 sample 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 the external table.

   If your cluster has compute reserved resources (Data Lakehouse Edition or Enterprise Edition), switch to the Xihe engine with an Interactive resource group to query the external table using Xihe MPP SQL online mode.

   SELECT * FROM spark_external_db.spark_hudi_table;

   To view results, go to Job Development > Spark JAR Development. In the Applications list, click Actions next to your SQL query, then click Logs.

External tables using Xihe BSP SQL

1. Create an OSS bucket and directory in the same region as your cluster. See Enable OSS, Create a bucket, and Create a directory.

2. Upload sample data to OSS. This example uploads the file xihe_oss.txt to oss://adb-test-1/test_xihe/. Rows are separated by line feeds; columns are separated by commas.

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

3. Create an OSS external table.

   1. Go to Job Development > SQL Development in the left navigation pane.

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

   3. Create an external database. ``sql CREATE EXTERNAL DATABASE xihe_external_db; ``

   4. Create an external table that reads from the uploaded file. ``sql 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 external table.

   If your Data Lakehouse Edition cluster has compute reserved resources, switch to an Interactive resource group to query using Xihe MPP SQL online mode.

   SELECT * FROM xihe_external_db.xihe_oss_table;

Internal tables using Spark SQL

Internal tables store data inside AnalyticDB for MySQL. This example follows a three-layer data warehouse architecture:

• ODS (Operational Data Store) layer: Raw source data ingested via extract, transform, and load (ETL).

• DWD (Data Warehouse Detail) layer: Cleaned and filtered ODS data.

• ADS (Application Data Service) layer: Business-ready data for analytics and reporting. ADS tables require indexes for fast query performance.

1. Create the ODS layer table

1. Go to Job Development > SQL Development in the left navigation pane.

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

3. Create a database named test_spark_db.

   CREATE DATABASE test_spark_db;

4. Create an index-free, hash-partitioned ODS table and insert sample 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 the DWD layer table

This step reads ODS data, filters out records where id = 002, and writes the result to the DWD layer.

1. Create an index-free DWD table with the same schema.

   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, switch to the Xihe engine with the (Interactive) user_default resource group and run the following configuration statements. Skipping this step causes subsequent SQL queries to fail.

   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;

3. Switch back to the Spark engine. Filter ODS records where id != 002 and write to the DWD 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 the DWD table.

   Spark SQL SELECT results do not display table data in the SQLConsole. To view results, go to Spark JAR development, click Actions next to your query in the Applications list, and click Logs.

   SELECT * FROM test_spark_db.adb_spark_dwd;

3. Create the ADS layer table

The ADS table stores filtered DWD data for direct use in business analytics. ADS tables require indexes (indexAll='true') to meet fast query requirements.

1. Create an indexed ADS table.

   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. Write records from the DWD table where age > 15 to the ADS table.

   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 data in real time

AnalyticDB for MySQL supports both offline queries (Spark SQL or Xihe BSP SQL) and online real-time queries (Xihe MPP SQL). For real-time results, use Xihe MPP SQL.

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

2. Query the ADS table.

   SELECT * FROM test_spark_db.adb_spark_ads;

   Expected results:

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

Internal tables using Xihe BSP SQL

This example follows the same three-layer architecture (ODS → DWD → ADS) using Xihe BSP SQL.

1. Create the ODS layer table

1. Go to Job Development > SQL Development in the left navigation pane.

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

3. Create a database named test_xihe_db.

   CREATE DATABASE test_xihe_db;

4. Create an index-free ODS table and insert sample 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 the DWD layer table

This step filters out records where id = 002 from the ODS table and writes the result to the DWD table.

1. Create an index-free DWD table.

   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. Read from the ODS table, filter, and write to the DWD table.

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

3. Query the DWD table.

   SELECT * FROM test_xihe_db.adb_xihe_dwd;

   Expected results:

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

3. Create the ADS layer table

1. Create an indexed ADS table.

   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. Write records from the DWD table where age > 15 to the ADS table.

   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 data in real time

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). For real-time results, use Xihe MPP SQL.

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

2. Query the ADS table.

   SELECT * FROM test_xihe_db.adb_xihe_ads;

   Expected results:

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