背景

本文以基金交易資料處理為例，介紹將資料存放區在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條交易記錄
    1. 查詢SQL
      SELECT * FROM tradelist_csv 
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' 
limit 100;
    2. 顯示執行結果

  • 查詢各城市、男性女性人群，購買的基金總額（多表Join查詢）
    1. 查詢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;
    2. 顯示執行結果

SQL查詢(非同步執行)

  1. 非同步執行查詢，將查詢結果以CSV格式輸出到OSS。

  2. 點擊執行狀態，可查看該非同步查詢任務的執行狀態。
    说明
    執行狀態主要分為RUNNING、SUCCESS 和 FAILURE三種。點擊刷新 ，當STATUS變為SUCCESS時，可以查看查詢結果輸出到OSS的檔案路徑。


  3. 查看匯出OSS的結果檔案。