通過Spark SQL讀寫C-Store資料 - AnalyticDB

AnalyticDB for MySQL叢集支援通過Spark SQL線上或離線讀寫C-Store表（即表引擎是XUANWU的表）資料。本文主要介紹通過Spark SQL線上或離線讀寫C-Store表資料的具體方法。

前提條件

叢集的產品系列為企業版、基礎版或湖倉版。
說明
- 湖倉版叢集儲存預留資源需大於0 ACU。
- 企業版叢集預留資源需大於0 ACU。
- 基礎版叢集預留資源需大於0 ACU。
已在企業版、基礎版或湖倉版叢集中建立Job型資源群組。
已建立企業版、基礎版或湖倉版叢集的資料庫帳號。

步驟一：進入資料開發

登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，然後單擊目的地組群ID。
在左側導覽列，單擊作業開發 > SQL開發。
在SQLConsole視窗，選擇Spark引擎和Job型資源群組。

步驟二：建立庫和C-Store表

說明

您可以選擇批處理或互動式執行任意一種方式執行以下SQL語句。詳情請參見Spark SQL執行方式。

執行以下語句，建立資料庫。

CREATE DATABASE spark_create_adb_db_test;

執行以下語句，建立C-Store表。Spark SQL建表文法詳情請參見Spark SQL建立內表。

CREATE TABLE spark_create_adb_db_test.test_adb_tbl (
  id int,
  name string COMMENT 'test_name',
  age int
) using adb TBLPROPERTIES('primaryKey'='id,age',
                'distributeType'='HASH', 
                'distributeColumns'='id', 
                'partitionType'='value', 
                'partitionColumn'='age',   
                'partitionCount'='120',
                'storagePolicy'='COLD'   
               );

步驟三：讀寫C-Store表資料

說明

您可以選擇批處理或互動式執行任意一種方式執行以下SQL語句。詳情請參見Spark SQL執行方式。
執行Spark SQL語句，只返回執行成功或者失敗，不返回資料。您可以在Spark Jar開發頁面應用列表頁簽中的日誌查看錶資料。詳情請參見查看Spark應用資訊。

離線讀寫資料（INSERT OVERWRITE）時，僅需選擇Spark引擎和Job型資源群組，無需配置其他參數；線上讀寫資料（INSERT INTO）時，需通過SET命令設定執行SQL語句的資源群組，且資源群組類型必須為Interactive型。

離線讀寫資料（INSERT OVERWRITE）

注意事項

離線讀寫資料需注意以下內容：

僅支援通過Spark SQL讀取（SELECT）和寫入（INSERT）C-Store分區表中的資料，不支援讀寫非分區表資料。分區表建立方法，詳情請參見CREATE TABLE。
不支援通過Spark SQL更新（UPDATE）和刪除（DELETE）C-Store表資料（包括分區表和非分區表）。
查詢熱資料時，需要先修改ELASTIC_ENABLE_HOT_PARTITION_HAS_HDD_REPLICA配置項，並手動BUILD。待BUILD完成後，再執行SQL查詢語句，否則會導致SQL執行失敗。
1. 使用XIHE引擎執行如下SQL，修改配置項。
```
SET adb_config ELASTIC_ENABLE_HOT_PARTITION_HAS_HDD_REPLICA=true;
```
2. 手動BUILD。
  - 對指定分區BUILD：
    重要
    僅3.1.6.0及以上版本的叢集支援指定分區BUILD。
    請在雲原生資料倉儲AnalyticDB MySQL控制台集群資訊頁面的配寘資訊地區，查看和升級核心版本。
    BUILD TABLE <table_name> force partitions='partition1,partition2';
  - 全表BUILD：
    重要
    該功能預設關閉，且強制全表BUILD時會對全表所有分區的資料重新構建索引，耗時較久。建議您使用指定分區BUILD功能。若需使用強制全表BUILD功能，請謹慎評估系統風險後提交工單開啟該功能。
    BUILD TABLE <table_name> force = true;
3. 觸發BUILD任務後，執行如下語句查看BUILD任務狀態：
```
SELECT table_name, schema_name, status FROM INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK ORDER BY create_time DESC LIMIT 10;
```

寫入C-Store表資料。
AnalyticDB for MySQL支援通過INSERT OVERWRITE語句離線寫入資料到C-Store表。您可以選擇以下任意一種方式向C-Store表寫入資料：
- 方式一：INSERT OVERWRITE靜態分區寫入
```
INSERT OVERWRITE spark_create_adb_db_test.test_adb_tbl partition(age=10) VALUES (1, 'bom');
```
- 方式二：INSERT OVERWRITE動態分區寫入
```
INSERT OVERWRITE spark_create_adb_db_test.test_adb_tbl partition (age) VALUES (1, 'bom', 10);
```

讀取C-Store表資料。

SELECT * FROM spark_create_adb_db_test.test_adb_tbl;

線上讀寫資料（INSERT INTO）

AnalyticDB for MySQL支援通過INSERT INTO語句線上寫入資料到C-Store表。您可以通過JDBC方式或View方式讀寫資料。

JDBC方式

-- 叢集的資料庫帳號、密碼以及內網地址
conf spark.adb.username=user;
conf spark.adb.password=password;
conf spark.adb.endpoint=amv-bp1a74zh****.ads.aliyuncs.com:3306;
-- 開啟ENI訪問
SET spark.adb.eni.enabled=true;
SET spark.adb.eni.vswitchId=vsw-bp12ldm83z4zu9k4d****;
SET spark.adb.eni.securityGroupId=sg-bp1cdm3acizrgq6x****;
-- 使用JDBC模式
SET spark.adb.useJdbc = true;
-- 設定執行SQL的線上資源群組
SET spark.adb.resourceGroup=user_default;
-- 寫入資料
INSERT INTO spark_create_adb_db_test.test_adb_tbl VALUES (1, 'adb', 20);
--讀取資料
SELECT * FROM spark_create_adb_db_test.test_adb_tbl;

參數說明：

參數	說明
spark.adb.username	叢集的資料庫帳號。
spark.adb.password	資料庫帳號的密碼。
spark.adb.endpoint	叢集的內網地址和連接埠。格式為`amv-bp1a74zh****.ads.aliyuncs.com:3306`。
spark.adb.eni.enabled	開啟ENI訪問。訪問資料時，需將`spark.adb.eni.enabled`參數設定為true。
spark.adb.eni.vswitchId	叢集所屬的交換器ID。
spark.adb.eni.securityGroupId	叢集的安全性群組ID。您可以選擇已有安全性群組或建立安全性群組。重要安全性群組需和企業版、基礎版或湖倉版叢集屬於同一VPC。
spark.adb.useJdbc	是否使用JDBC方式訪問資料。取值： true：是。 false（預設值）：否。線上寫入資料時需配置為true。
spark.adb.resourceGroup	設定執行SQL語句的Interactive型資源群組。說明建立企業版、基礎版或湖倉版叢集時，會預設建立一個名為`user_default`的Interactive型資源群組，您可以選擇該資源群組或建立Interactive型資源群組。建立資源群組的具體方法，請參見建立和管理資源群組。

View方式

-- 開啟ENI訪問
SET spark.adb.eni.enabled=true;
SET spark.adb.eni.vswitchId=vsw-bp1sxxsodv28ey5dl****;   
SET spark.adb.eni.securityGroupId=sg-bp19mr685pmg4ihc****;    
-- 建立視圖
CREATE TEMPORARY VIEW table_tmp
USING org.apache.spark.sql.jdbc
OPTIONS (
  url 'jdbc:mysql://amv-bp1a74zh****.ads.aliyuncs.com:3306/spark_create_adb_db_test?useServerPrepStmts=false&rewriteBatchedStatements=true',     
  dbtable 'spark_create_adb_db_test.test_adb_tbl',    
  user 'user',       
  password 'password****'      
);
-- 寫入資料
INSERT INTO table_tmp VALUES (1, 'adb', 20);
-- 查詢資料
SELECT * FROM table_tmp;

參數說明：

參數	說明
spark.adb.eni.enabled	開啟ENI訪問。訪問資料時，需將`spark.adb.eni.enabled`參數設定為true。
spark.adb.eni.vswitchId	叢集所屬的交換器ID。
spark.adb.eni.securityGroupId	叢集的安全性群組ID。您可以選擇已有安全性群組或建立安全性群組。重要安全性群組需和企業版、基礎版或湖倉版叢集屬於同一VPC。
table_tmp	視圖名稱。本文以`table_tmp`為例。
USING org.apache.spark.sql.jdbc	參數取值固定為`USING org.apache.spark.sql.jdbc`。
url	叢集的JDBC URL。格式：`jdbc:mysql://<endpoint:port>/<db_name>?useServerPrepStmts=false&rewriteBatchedStatements=true`，其中： endpoint：叢集的內網串連地址。 port：叢集的連接埠號碼。固定為3306。 db_name：叢集的資料庫名稱。 useServerPrepStmts=false&rewriteBatchedStatements=true：批量寫入資料至的必填配置，用於提高寫入效能，以及降低對叢集的壓力。樣本：`jdbc:mysql://amv-bp1a74zh****.ads.aliyuncs.com:3306/spark_create_adb_db_test?useServerPrepStmts=false&rewriteBatchedStatements=true`。
dbtable	叢集的表名。格式為`db_name.table_name`。本文以`spark_create_adb_db_test.test_adb_tbl`為例。
user	叢集的資料庫的帳號。
password	叢集資料庫帳號的密碼。

Spark配置項

通過Spark SQL讀寫C-Store表時，支援配置如下配置項：

配置項	說明	預設值
spark.adb.write.batchSize	單批次寫入資料的條數。取值範圍為大於0的正整數。說明僅線上寫入資料時，支援配置該參數。	600
spark.adb.write.arrow.maxMemoryBufferSize	寫入最大記憶體緩衝大小。取值範圍為大於0的正整數，單位為MB。說明僅離線寫入資料時，支援配置該參數。	1024
spark.adb.write.arrow.maxRecordSizePerBatch	單批次寫入資料的最大記錄條數。取值範圍為大於0的正整數。說明僅離線寫入資料時，支援配置該參數。	500