通過XIHE SQL和Spark SQL訪問OSS資料 -

AnalyticDB MySQL湖倉版（3.0）支援通過XIHE SQL和Spark SQL訪問OSS資料。本文以基金交易資料處理為例，介紹通過XIHE SQL和Spark SQL訪問OSS資料的具體步驟。

前提條件

已開通OSS服務並建立儲存空間和目錄。具體操作，請參見開通OSS服務、建立儲存空間和建立目錄。
已建立AnalyticDB MySQL湖倉版（3.0）叢集，詳情請參見建立叢集。
已建立Job型資源群組，且計算預留資源需大於等於8 ACU。建立資源群組，詳情請參見建立資源群組。
已建立湖倉版（3.0）叢集的資料庫帳號。
- 如果是通過阿里雲帳號訪問，只需建立高許可權帳號。具體操作，請參見建立高許可權帳號。
- 如果是通過RAM使用者訪問，需要建立高許可權帳號和普通帳號並且將RAM使用者綁定到普通帳號上。具體操作，請參見建立資料庫帳號和綁定或解除綁定RAM使用者與資料庫帳號。
已建立AliyunADBSparkProcessingDataRole角色並授權。具體操作，請參見帳號授權。

操作步驟

準備資料。
1. 下載類比資料並解壓。
2. 將類比資料中的交易資訊表和使用者資料表分別上傳至OSS中的<bucketname>/adb/trade/和<bucketname>/adb/user/目錄。具體操作，請參見上傳檔案。
  說明
  - 類比資料中，交易資訊表的表名以dc_trade_final_dd開頭，使用者資料表的表名為user_info。
  - 您需將<bucketname>替換為實際的Bucket名稱。

訪問資料。

通過XIHE SQL訪問OSS資料

登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在湖倉版(3.0)頁簽，單擊目的地組群ID。
在左側導覽列，單擊作業開發>SQL開發。
在SQLConsole視窗，選擇Job型資源群組和XIHE引擎。
建立資料庫。如果有已建立的資料庫，可以忽略本步驟。樣本如下：
```
CREATE DATABASE adb_demo; 
```

建立OSS外表。

建立交易資訊表

CREATE EXTERNAL TABLE tradelist_csv (
    t_userid STRING COMMENT '使用者ID',
    t_dealdate STRING COMMENT '申請時間', 
    t_businflag STRING COMMENT '業務代碼', 
    t_cdate STRING COMMENT '確認日期', 
    t_date STRING COMMENT '申請日期',
    t_serialno STRING COMMENT'申請序號', 
    t_agencyno STRING COMMENT'銷售商編號', 
    t_netno STRING  COMMENT'網點編號',
    t_fundacco STRING COMMENT'基金帳號',
    t_tradeacco STRING COMMENT'交易帳號',
    t_fundcode STRING  COMMENT'基金代碼',
    t_sharetype STRING COMMENT'份額類別',
    t_confirmbalance DOUBLE  COMMENT'確認金額',
    t_tradefare DOUBLE COMMENT'交易費',
    t_backfare DOUBLE COMMENT'後收手續約',
    t_otherfare1 DOUBLE COMMENT'其他費用1',
    t_remark STRING COMMENT'備忘'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/trade/';

文法說明，請參見OSS外表。其中，LOCATION為交易資訊表所在的OSS路徑。

建立使用者資訊表

CREATE EXTERNAL TABLE userinfo (
    u_userid STRING COMMENT '使用者ID',
    u_accountdate STRING COMMENT '開戶時間', 
    u_gender STRING COMMENT '性別', 
    u_age INT COMMENT '年齡', 
    u_risk_tolerance INT COMMENT '風險承受能力，1-10，10為最進階',
    u_city STRING COMMENT'所在城市', 
    u_job STRING COMMENT'工作類別， A-K', 
    u_income DOUBLE  COMMENT'年度營收（萬）'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/user/';

文法說明，請參見OSS外表。其中，LOCATION為使用者資訊表所在的OSS路徑。

查詢外表資料。

您可以選擇通過同步執行或非同步執行查詢資料，同步執行的結果會直接顯示，非同步執行會返回job_id，您可以通過job_id查詢非同步任務狀態，判斷任務是否執行成功。

同步查詢

樣本一：查詢交易機構SXS_0010，在0603至0604的100條交易記錄。

SELECT * FROM tradelist_csv 
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' 
limit 100;

返回結果：

+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| t_userid  |     t_dealdate      | t_businflag   |   t_cdate    |   t_date     |  t_serialno       |  t_agencyno  |  t_netno   |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00041972  | 2018-06-03 09:11:57 |  保本基金B    |  2018-06-03  |  2018-06-03  | 2018-06-03-000846 |  SXS_0010S   | STORE-0135 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00051912  | 2018-06-03 09:28:20 |  保本基金D    |  2018-06-03  |  2018-06-03  | 2018-06-03-001046 |  SXS_0010S   | STORE-0397 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00120370  | 2018-06-03 11:21:39 |  保本基金B    |  2018-06-03  |  2018-06-03  | 2018-06-03-002393 |  SXS_0010S   | STORE-0800 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00165308  | 2018-06-03 12:35:35 |  保本基金D    |  2018-06-03  |  2018-06-03  | 2018-06-03-003281 |  SXS_0010S   | STORE-0340 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+

說明

本樣本為部分返回結果，實際結果請以控制台為準。

樣本二：查詢各城市、男性女性人群，購買的基金總額（多表Join查詢）。

SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM tradelist_csv , userinfo  
WHERE u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;

返回結果：

+-----------+------------+----------------+
| u_city    | u_gender   | sum_balance    |
+-----------+------------+----------------+
| Beijing   |  male      | 2445539161     |
+-----------+------------+----------------+
| Guangzhou |  male      | 1271999857     |
+-----------+------------+----------------+
| Qingdao   |  male      | 1266748660     |
+-----------+------------+----------------+
| Wuhan     |  male      | 12641688475    |
+-----------+------------+----------------+

說明

本樣本為部分返回結果，實際結果請以控制台為準。

非同步查詢

查詢各城市、男性女性人群，購買的基金總額（多表Join查詢）。

SUBMIT JOB SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM tradelist_csv , userinfo  
WHERE u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;

返回結果：

+---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2023091410365702101701713803151****** |
+---------------------------------------+

使用SUBMIT JOB提交非同步任務後，您可以通過job_id終止非同步任務或查詢非同步任務狀態，具體操作，請參見非同步提交任務。

通過Spark SQL訪問OSS資料

登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在湖倉版(3.0)頁簽，單擊目的地組群ID。
在左側導覽列，單擊作業開發>SQL開發。
在SQLConsole視窗，選擇Job型資源群組和Spark引擎。
建立資料庫。如果有已建立的資料庫，可以忽略本步驟。樣本如下：
```
CREATE DATABASE adb_test; 
```

建立OSS外表。

建立交易資訊表

CREATE EXTERNAL TABLE adb_test.tradelist_csv (
    t_userid STRING COMMENT '使用者ID',
    t_dealdate STRING COMMENT '申請時間', 
    t_businflag STRING COMMENT '業務代碼', 
    t_cdate STRING COMMENT '確認日期', 
    t_date STRING COMMENT '申請日期',
    t_serialno STRING COMMENT'申請序號', 
    t_agencyno STRING COMMENT'銷售商編號', 
    t_netno STRING  COMMENT'網點編號',
    t_fundacco STRING COMMENT'基金帳號',
    t_tradeacco STRING COMMENT'交易帳號',
    t_fundcode STRING  COMMENT'基金代碼',
    t_sharetype STRING COMMENT'份額類別',
    t_confirmbalance DOUBLE  COMMENT'確認金額',
    t_tradefare DOUBLE COMMENT'交易費',
    t_backfare DOUBLE COMMENT'後收手續約',
    t_otherfare1 DOUBLE COMMENT'其他費用1',
    t_remark STRING COMMENT'備忘'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/trade/';

文法說明，請參見OSS外表。其中，LOCATION為交易資訊表所在的OSS路徑。

建立使用者資訊表

CREATE EXTERNAL TABLE adb_test.userinfo (
    u_userid STRING COMMENT '使用者ID',
    u_accountdate STRING COMMENT '開戶時間', 
    u_gender STRING COMMENT '性別', 
    u_age INT COMMENT '年齡', 
    u_risk_tolerance INT COMMENT '風險承受能力，1-10，10為最進階',
    u_city STRING COMMENT'所在城市', 
    u_job STRING COMMENT'工作類別， A-K', 
    u_income DOUBLE  COMMENT'年度營收（萬）'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/user/';

文法說明，請參見OSS外表。其中，LOCATION為使用者資訊表所在的OSS路徑。

查詢資料。
說明
執行Spark SQL語句，只返回執行成功或者失敗，不返回查詢結果。您可以在Spark Jar開發頁面應用列表頁簽中的日誌查看查詢結果。詳情請參見查看Spark應用資訊。
- 樣本一：查詢交易機構SXS_0010，在0603至0604的100條交易記錄。
```
SELECT * FROM adb_test.tradelist_csv 
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' 
LIMIT 100;
```
- 樣本二：查詢各城市、男性女性人群，購買的基金總額（多表Join查詢）。
```
SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM adb_test.tradelist_csv , adb_test.userinfo  
WHERE u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;
```

：通過XIHE SQL或者Spark SQL訪問OSS資料

前提條件

操作步驟

通過XIHE SQL訪問OSS資料

建立交易資訊表

建立使用者資訊表

同步查詢

非同步查詢

通過Spark SQL訪問OSS資料

建立交易資訊表

建立使用者資訊表

相關文檔