背景

本文以基金交易資料處理為例,介紹將資料存放區在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的結果檔案。