基于OSS的資料查詢分析和可視化BI報表

資料存儲在OSS後，有多種查詢分析的方法，包括阿裡雲MaxCompute、DataLakeAnalytics産品等Severless查詢分析服務，也可以自建Spark、Presto、Imapla應用來分析OSS上的資料。

本文，介紹基于OSS+DataLakeAnalytics+QuickBI，實作對資料的存儲、Serverless化的Ad-hoc查詢、可視化BI的實驗。

實驗步驟（ OSS+DataLakeAnalytics+QuickBI）

本次實驗，主要介紹，如何使用OSS+DataLakeAnalytics+QuickBI，實作對資料的存儲、互動式查詢分析、輸出BI報表的整體資料處理流程。适用于，日志、交易記錄查詢分析和報表輸出等場景。

服務開通

OSS服務：

1.開通OSS服務

https://www.aliyun.com/product/oss

DataLakeAnalytics服務：

申請DataLakeAnalytics的試用資格

備注：目前DataLakeAnalytics公測中，需送出公測申請，開通試用。DataLakeAnalytics支援查詢分析OSS上的CSV、JSON、Parquet、ORC、AVRO、RCFile等資料檔案。

操作步驟

準備資料

登入控制台，并建立目錄

建立華東1區域的OSS測試Bucket。

備注：Bucket名稱是全局唯一，如果提示名稱已存在，請更換一個Bucket名稱。

建立目錄workshop_sh/trade, workshop_sh/user

下載下傳模拟資料（該資料本次實驗的模拟資料）

http://testdatasample.oss-cn-hangzhou.aliyuncs.com/workshop_sh/workshop_sh.zip

将下載下傳的将交易記錄和開戶資訊資料，分别上傳到trade、user目錄

登入Data Lake Analytics控制台

點選“登入資料庫”

，輸入開通服務時配置設定的使用者名和密碼，登入Data Lake Analytics控制台。

公測期間的配置設定的使用者名、密碼，開通服務的消息中可以檢視

建立Schema和Table

建立Schema

輸入建立SCHEMA的語句，點選“同步執行”。

CREATE SCHEMA my_schema_name WITH DBPROPERTIES (
    CATALOG = 'oss', 
    LOCATION = 'oss://Bucket名稱/測試資料目錄/'
  );

注意：

您的OSS LOCATION位址，請務必以’/‘結尾以表示目錄(如LOCATION=‘oss://workshopsh20180608100/workshop_sh/’）。後續建表的LOCATION所指向的資料檔案，必須在這個OSS目錄或者其子目錄下。
同一個阿裡雲region，schema名全局唯一，實驗時，請将“my_schema_name”替換為，您自定義schema名稱。建議根據業務定義，如已有重名schema，在建立時會提示報錯，則請換一個schema名字。

建立表

在“資料庫”的下拉框中，選擇剛剛您建立的schema。

然後在SQL文本框中輸入建表語句如下，并點選同步執行。其中，Location替換為您的Bucket和測試資料的路徑

1.建立交易記錄表：

說明：LOCATION 'oss://Bucket名稱/交易記錄表目錄/'

實驗中，替換LOCATION 'oss://您的OSS存儲空間名稱/workshop_sh/user/'。如：oss://workshopsh20180608100/workshop_sh/user

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/';

2.建立開戶資訊表：

說明：LOCATION 'oss://Bucket名稱/開戶資訊表目錄/'

實驗中，替換LOCATION 'oss://您的OSS存儲空間名稱/workshop_sh/user/'。如：oss://workshopsh20180608100/workshop_sh/user

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/';

3.建表完畢後，重新整理頁面，在左邊導覽列中能看到schema(實驗時選擇您建立的schema)下的2張表

SQL查詢(同步執行)

1.查詢交易機構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;

顯示執行結果

2.查詢各城市、男性女性人群，購買的基金總額（多表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;

SQL查詢(異步執行)

異步執行查詢，将查詢結果，以CSV格式，輸出到OSS上

點選“執行狀态”，可看到該異步查詢任務的執行狀态

主要分為：“RUNNING”，“SUCCESS”，“FAILURE”。

點選“重新整理”，當STATUS變為“SUCCESS”時，可以檢視到查詢結果輸出到OSS的檔案路徑。

檢視導出OSS的結果檔案

基于QuickBI制作資料報表

建立資料源

說明：

目前QuickBI隻有專業版支援DataLakeAnalytics作為資料源
資料源-資料庫位址、端口，可在DataLakeAnalytics控制台檢視

基于OSS+DataLakeAnalytics+QuickBI的Serverless的查詢分析和可視化BI基于OSS的資料查詢分析和可視化BI報表實驗步驟（ OSS+DataLakeAnalytics+QuickBI）
資料源-資料庫名稱，使用在DataLakeAnalytics建立的SCHEMA名稱
資料源-使用者名、密碼，即登入DataLakeAnalytics資料庫使用的使用者名/密碼（初始化服務時，發送到站内信中）

基于OSS+DataLakeAnalytics+QuickBI的Serverless的查詢分析和可視化BI基于OSS的資料查詢分析和可視化BI報表實驗步驟（ OSS+DataLakeAnalytics+QuickBI）

建立資料集

說明

點選建立資料集
選擇資料源-自定義SQL

基于OSS+DataLakeAnalytics+QuickBI的Serverless的查詢分析和可視化BI基于OSS的資料查詢分析和可視化BI報表實驗步驟（ OSS+DataLakeAnalytics+QuickBI）
輸入SQL（輸入前述實驗步驟的SQL），并儲存（如儲存為“citygender”）

基于OSS+DataLakeAnalytics+QuickBI的Serverless的查詢分析和可視化BI基于OSS的資料查詢分析和可視化BI報表實驗步驟（ OSS+DataLakeAnalytics+QuickBI）

建立儀表盤

點選建立儀表闆
選擇資料集（如citygender）
分别選擇值軸、類别軸、顔色分類
點選“重新整理”

這裡顯示了各城市男性、女性投資者購買基金的總額

基于OSS+DataLakeAnalytics+QuickBI的Serverless的查詢分析和可視化BI基于OSS的資料查詢分析和可視化BI報表實驗步驟（ OSS+DataLakeAnalytics+QuickBI）