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

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

前提條件

叢集的產品系列為湖倉版。
說明
湖倉版叢集至少有16 ACU的計算預留資源和24 ACU的儲存預留資源。
已建立Job型資源群組。詳情請參見建立資源群組。

步驟一：建立ODS層資料表

登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在湖倉版頁簽下，單擊目的地組群ID。
在左側導覽列，單擊作業開發 > SQL開發。
在SQLConsole視窗，選擇Job型資源群組和Spark引擎。
輸入以下語句，並單擊執行SQL（F8），建立一個名為test_spark_db的庫。
```
CREATE DATABASE test_spark_db;
```
在彈窗中，選擇繼續執行。
重要
- 選擇繼續執行時，SQL語句為批量執行，SQL語句運行在單獨的Spark App中，可以保證資源隔離和穩定性。本次入門教程推薦使用繼續執行。
- 選擇啟動ThriftServer時，SQL語句為互動式執行，資源隔離為線程級的，且需要配置Spark ThriftServer後才執行SQL語句。詳情請參見啟停ThriftServer。

使用繼續執行的方式，在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')

使用繼續執行的方式，從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執行查詢語句，返回結果不展示表資料。如果您需要查看錶資料，請按照步驟4操作。
可選：在Spark開發頁面的應用列表中，單擊目標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 | Ban   |   25 |
|    1 | Anna  |   18 |
+------+-------+------+