使用Spark SQL进行作业开发

步骤一：创建ODS层数据表

1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，在企业版、基础版或湖仓版页签下，单击目标集群ID。

2. 在左侧导航栏，单击作业开发 > SQL开发。

3. 在SQLConsole窗口，选择Spark引擎，并选择Job型或Interactive型资源组。

4. 输入以下语句，并单击执行SQL（F8），创建一个名为test_spark_db的库。

   CREATE DATABASE test_spark_db;

5. 在test_spark_db库中创建ODS层的无索引、有分区的表adb_spark_ods，并插入数据。

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

步骤二：创建DWD层数据表

本教程以从ODS层表中读取并过滤数据后写入DWD层表中为例。

1. 在test_spark_db库中创建一张无索引、有分区的表adb_spark_dwd。

   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. （可选）查询离线热数据前，在SQLConsole窗口，选择XIHE引擎和（Interactive）user_default资源组，执行以下配置项。

   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;

   重要

   查询离线热数据时，若未正确配置上述参数，将导致后续SQL语句执行失败。

3. 切换至Spark引擎，从ODS层表adb_spark_ods中读取id列不为002的数据并写入DWD层数据表。

   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. 查询adb_spark_dwd表数据。

   SELECT * FROM test_spark_db.adb_spark_dwd;

   说明

   使用Spark SQL执行查询语句，返回结果不展示表数据。如果您需要查看表数据，请按照步骤5操作。

5. 可选：在Spark Jar开发页面的应用列表中，单击目标SQL查询语句操作列的日志，在日志中可以查看表数据。

步骤三：创建ADS层数据表

ADS层表数据是对DWD层数据做了更精细的过滤，可直接用于业务分析，对查询速率有一定的要求，因此创建ADS层数据表时需添加索引。本教程从DWD层数据表adb_spark_dwd中读取age列大于15的数据并写入ADS层数据表adb_spark_ads。

1. 在test_spark_db库中创建一张有索引，有分区的表adb_spark_ads。

   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. 从DWD层数据表adb_spark_dwd中读取age列大于15的数据并写入ADS层数据表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;

步骤四：查询ADS层表数据

AnalyticDB for MySQL企业版、基础版及湖仓版集群既支持通过Spark SQL或XIHE BSP SQL离线方式查询数据，也支持通过XIHE MPP SQL在线实时查询数据。为保证数据的实时性，本教程以XIHE MPP SQL在线方式查询ADS层表数据为例。

1. 在SQLConsole窗口，选择XIHE引擎和（Interactive）user_default资源组。

2. 执行以下语句，查询ADS层表数据。

   SELECT * FROM test_spark_db.adb_spark_ads;

   返回结果如下：

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