使用Spark SQL讀寫OSS外表-雲原生資料倉儲AnalyticDB-阿里雲

本文以OSS Parquet格式為例介紹如何通過Spark SQL讀寫OSS外表。

前提條件

叢集的產品系列為企業版、基礎版或湖倉版。
已建立建立Job型資源群組。
已建立AnalyticDB for MySQL叢集的資料庫帳號。
- 如果是通過阿里雲帳號訪問，只需建立高許可權帳號。
- 如果是通過RAM使用者訪問，需要建立高許可權帳號和普通帳號並且將RAM使用者綁定到普通帳號上。

步驟一：進入資料開發

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

步驟二：建立外庫與OSS外表

說明

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

執行以下語句，建立外庫。
```
CREATE DATABASE IF NOT exists test_db comment 'demo db' 
location 'oss://<bucket_name>/test'   /*用於在該路徑中建立表，請替換為自己的OSS路徑。*/
WITH dbproperties(k1='v1', k2='v2');
```
說明
您也可以在dbproperties中配置'auto.create.location'='true'自動建立OSS路徑。如果未指定該參數並且OSS中不存在該路徑，請先建立對應的OSS路徑。

執行以下語句，建立OSS外表。

-- 建立非分區表。
CREATE TABLE IF NOT exists test_db.test_tbl(id int, name string, age int) 
using parquet 
location 'oss://<bucket_name>/test/test_tbl/' 
tblproperties ('parquet.compress'='SNAPPY');

-- 建立分區表。
CREATE TABLE IF NOT exists test_db.test_tbl_partitioned(id int, name string, age int) 
using parquet partitioned by (location string) 
location 'oss://<bucket_name>/test/test_tbl_partitioned/' 
tblproperties ('parquet.compress'='SNAPPY');

重要

OSS路徑中的Bucket需與建立資料庫所選的Bucket相同。
建立外表時選擇的OSS路徑需比建立資料庫時選擇的OSS路徑至少多一層目錄，且外表的路徑需在資料庫路徑下。
可以在tblproperties中配置'auto.create.location'='true'自動建立OSS路徑。如果未指定該參數並且OSS中不存在該路徑，請先建立對應的OSS路徑。

步驟三：寫入OSS外表資料

重要

AnalyticDB for MySQL預設不支援多個Spark作業同時寫一張OSS外表的不同分區。如果需要多個Spark作業同時寫一張OSS外表的不同分區，請添加spark.hadoop.fs.aliyun.oss.upload.basedir=oss://<bucket_name>/test/upload;配置，且該配置中的OSS的Bucket需要與OSS外表所在的Bucket相同。多個Spark作業可以配置使用同一個OSS路徑。

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

執行以下語句，寫入資料。您可以選擇以下任意一種方式向OSS外表中寫入資料。

方式一：INSERT INTO寫入

INSERT INTO test_db.test_tbl VALUES(1, 'adb', 10);

方式二：INSERT OVERWRITE全表寫入

INSERT OVERWRITE test_db.test_tbl VALUES(2, 'spark', 10);

方式三：INSERT INTO靜態分區寫入

INSERT INTO TABLE test_db.test_tbl_partitioned PARTITION(location='hangzhou') VALUES(1, 'adb', 10);

方式四：INSERT OVERWRITE靜態分區寫入

INSERT OVERWRITE TABLE test_db.test_tbl_partitioned PARTITION(location='hangzhou') VALUES(1, 'adb', 10);

方式五：INSERT OVERWRITE動態分區覆蓋寫

INSERT OVERWRITE TABLE test_db.test_tbl_partitioned PARTITION(location) VALUES(1, 'adb', 10, 'beijing');

步驟四：查詢資料

說明

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

執行以下語句，查詢OSS外表資料。

SELECT * FROM test_db.test_tbl;
SELECT * FROM test_db.test_tbl_partitioned;

（可選）步驟五：刪除外表

測試完成後，您可以執行以下語句，刪除表的中繼資料。

DROP TABLE if exists test_db.test_tbl;
DROP TABLE if exists test_db.test_tbl_partitioned;

只會刪除表的中繼資料，不會刪除OSS中的資料。