如何使用SparkSQL進行作業開發 - AnalyticDB

本文介紹如何使用Spark SQL作業開發，將ODS層表資料通過DWD層過濾後，寫入ADS層。

前提條件

叢集的產品系列為企業版、基礎版或湖倉版。
說明
- 企業版叢集預留資源不能為0 ACU。
- 湖倉版叢集至少有16 ACU的計算預留資源和24 ACU的儲存預留資源。

已建立Job型資源群組或Spark引擎的Interactive型資源群組。

步驟一：建立ODS層資料表

登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在企業版、基礎版或湖倉版頁簽下，單擊目的地組群ID。
在左側導覽列，單擊作業開發 > SQL開發。
在SQLConsole視窗，選擇Spark引擎，並選擇Job型或Interactive型資源群組。
輸入以下語句，並單擊執行SQL（F8），建立一個名為test_spark_db的庫。
```
CREATE DATABASE test_spark_db;
```

在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層表中為例。

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

（可選）查詢離線熱資料前，在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語句執行失敗。

切換至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;

查詢adb_spark_dwd表資料。
```
SELECT * FROM test_spark_db.adb_spark_dwd;
```
說明
使用Spark SQL執行查詢語句，返回結果不展示表資料。如果您需要查看錶資料，請按照步驟5操作。
可選：在Spark Jar開發頁面的應用列表中，單擊目標SQL查詢語句操作列的日誌，在日誌中可以查看錶資料。

步驟三：建立ADS層資料表

ADS層表資料是對DWD層資料做了更精細的過濾，可直接用於業務分析，對查詢速率有一定的要求，因此建立ADS層資料表時需添加索引。本教程從DWD層資料表adb_spark_dwd中讀取age列大於15的資料並寫入ADS層資料表adb_spark_ads。

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

從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層表資料為例。

在SQLConsole視窗，選擇XIHE引擎和（Interactive）user_default資源群組。

執行以下語句，查詢ADS層表資料。

SELECT * FROM test_spark_db.adb_spark_ads;

返回結果如下：

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