基于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服务：

1. 申请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;