背景
本文以基金交易資料處理為例,介紹將資料存放區在OSS,使用DataLakeAnalytics進行Severless的互動式查詢分析。
Object Storage Service
Object Storage Service提供標準、低頻、Archive Storage類型,能夠覆蓋從熱到冷的不同儲存場景。同時,OSS能夠與Hadoop開源社區及EMR、批次運算、MaxCompute、機器學習服務PAI、DatalakeAnalytics、Function Compute等阿里雲計算產品進行深度結合。
用戶可以打造基於OSS的資料分析應用,如MapReduce、HIVE/Pig/Spark等批處理(如日誌離線計算)、互動式查詢分析(Imapla、Presto、DataLakeAnalytics)、深度學習訓練(阿里雲PAI)、基因渲染計算交付(批次運算)、大數據應用(MaxCompute)及串流(Function Compute)等。
DataLakeAnalytics
Data Lake Analytics是無伺服器(Serverless)化的雲上互動式查詢分析服務。無需ETL,就可通過此服務在雲上通過標準JDBC直接對阿里雲OSS、TableStore的數據輕鬆進行查詢和分析,以及無縫整合商務分析工具。
服務開通
- OSS服務:
- DataLakeAnalytics服務:
數據匯出到OSS
以基金交易數據為例:
假設在OSS上儲存了以下trade\user檔案夾,並儲存相應的交易數據和開戶資訊:


下載類比資料(該數據為以下實驗中使用的類比資料。)
登入Data Lake Analytics控制台
點擊登入資料庫,使用方法可以參考 DataLakeAnalytics 產品幫助文檔。

建立Schema和Table
- 建立Schema
輸入建立SCHEMA的語句,點擊同步執行。
CREATE SCHEMA sh_trade
CREATE SCHEMA sh_trade(注意:同一個阿里雲region,schema名全域唯一,建議根據業務定義。如有重名schema,在建立時會提示報錯,則需換另一個schema名。)
- 建立Table
在資料庫的下拉框中,選擇剛剛建立的schema。
说明 建立交易記錄表及建立開戶資訊表時: - LOCATION ‘oss://Bucket名稱/交易記錄表目錄/‘
- Location替換為您的Bucket和測試數據的路徑
- 建立交易記錄表:
在SQL文字框中輸入建表語句如下:
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://testdatasample/workshop_sh/trade/';
- 建立開戶資訊表:
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://testdatasample/workshop_sh/user/';
建表完畢後,刷新頁面,在左邊導航條中能看到schema下的2張表。
SQL查詢(同步執行)
- 查詢交易機構SXS_0010,在0603至0604的100條交易記錄
- 查詢SQL
SELECT * FROM tradelist_csv WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' limit 100;
- 顯示執行結果
- 查詢SQL
- 查詢各城市、男性女性人群,購買的基金總額(多表Join查詢)
- 查詢SQL
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;
- 顯示執行結果
- 查詢SQL
SQL查詢(非同步執行)
- 非同步執行查詢,將查詢結果以CSV格式輸出到OSS。
- 點擊執行狀態,可查看該非同步查詢任務的執行狀態。
说明 執行狀態主要分為RUNNING、SUCCESS 和 FAILURE三種。點擊刷新 ,當STATUS變為SUCCESS時,可以查看查詢結果輸出到OSS的檔案路徑。 - 查看匯出OSS的結果檔案。