基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视

太过爱你忘了你带给我的痛 2022-01-12 23:45 188阅读 0赞

基于OSS的数据查询分析和可视化BI报表  
数据存储在OSS后，有多种查询分析的方法，包括阿里云MaxCompute、DataLakeAnalytics产品等Severless查询分析服务，也可以自建Spark、Presto、Imapla应用来分析OSS上的数据。  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless]  
本文，介绍基于OSS+DataLakeAnalytics+QuickBI，实现对数据的存储、Serverless化的Ad-hoc查询、可视化BI的实验。

实验步骤（ OSS+DataLakeAnalytics+QuickBI）  
本次实验，主要介绍，如何使用OSS+DataLakeAnalytics+QuickBI，实现对数据的存储、交互式查询分析、输出BI报表的整体数据处理流程。适用于，日志、交易记录查询分析和报表输出等场景。

![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 1]

服务开通  
OSS服务：  
1.开通OSS服务https://www.aliyun.com/product/oss

DataLakeAnalytics服务：  
1.申请DataLakeAnalytics的试用资格  
备注：目前DataLakeAnalytics公测中，需提交公测申请，开通试用。DataLakeAnalytics支持查询分析OSS上的CSV、JSON、Parquet、ORC、AVRO、RCFile等数据文件。

操作步骤  
准备数据  
登录控制台， 并创建目录  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 2]

创建华东1区域的OSS测试Bucket。  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 3]  
备注：Bucket名称是全局唯一，如果提示名称已存在，请更换一个Bucket名称。

创建目录（建议目录如下）：  
创建目录workshop\_sh/trade, workshop\_sh/user  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 4]  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 5]

下载模拟数据（该数据本次实验的模拟数据）  
[http://testdatasample.oss-cn-hangzhou.aliyuncs.com/workshop\_sh/workshop\_sh.zip][http_testdatasample.oss-cn-hangzhou.aliyuncs.com_workshop_sh_workshop_sh.zip]

将下载的将交易记录和开户信息数据，分别上传到trade、user目录  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 6]  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 7]

登录Data Lake Analytics控制台  
点击“登录数据库”，输入开通服务时分配的用户名和密码，登录Data Lake Analytics控制台。  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 8]  
公测期间的分配的用户名、密码，开通服务的消息中可以查看  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 9]

创建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张表  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 10]

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;  
显示执行结果  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 11]

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;  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 12]

SQL查询(异步执行)  
异步执行查询，将查询结果，以CSV格式，输出到OSS上  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 13]

点击“执行状态”，可看到该异步查询任务的执行状态  
主要分为：“RUNNING”，“SUCCESS”，“FAILURE”。  
点击“刷新”，当STATUS变为“SUCCESS”时，可以查看到查询结果输出到OSS的文件路径。  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 14]

查看导出OSS的结果文件  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 15]

基于QuickBI制作数据报表  
创建数据源  
说明：

目前QuickBI只有专业版支持DataLakeAnalytics作为数据源  
数据源-数据库地址、端口，可在DataLakeAnalytics控制台查看  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 16]  
数据源-数据库名称，使用在DataLakeAnalytics创建的SCHEMA名称  
数据源-用户名、密码，即登录DataLakeAnalytics数据库使用的用户名/密码（初始化服务时，发送到站内信中）  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 17]  
创建数据集  
说明

点击创建数据集  
选择数据源-自定义SQL  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 18]  
输入SQL（输入前述实验步骤的SQL），并保存（如保存为“citygender”）  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 19]  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 20]

创建仪表盘  
说明

点击创建仪表板  
选择数据集（如citygender）  
分别选择 值轴、类别轴、颜色分类  
点击“刷新”  
这里显示了各城市男性、女性投资者购买基金的总额  
![基于OSS+DataLakeAnalytics+QuickBI的Serverless的查询分析和可视][OSS_DataLakeAnalytics_QuickBI_Serverless 21]

更多信息  
关于更多如何使用DataLakeAnalytics+QuickBI，对存储在OSS上数据进行查询分析,以及输出BI报表，请参考DataLakeAnalytics、QuickBI的产品介绍。

转载于:https://blog.51cto.com/14031893/2375344

[OSS_DataLakeAnalytics_QuickBI_Serverless]: https://s1.51cto.com/images/blog/201904/08/64d93d26377e96aed229b05f78a40699.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 1]: https://s1.51cto.com/images/blog/201904/08/18c634a2ec4dec32bd5ab04d6fdebf77.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 2]: https://s1.51cto.com/images/blog/201904/08/869e4b8ea5306f11c28e02634795ba72.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 3]: https://s1.51cto.com/images/blog/201904/08/a25e1ba74083a1f89551f16f43ed861f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 4]: https://s1.51cto.com/images/blog/201904/08/341659874e5a43dce699ec3aa1b373b8.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 5]: https://s1.51cto.com/images/blog/201904/08/92844609424b7194310c4aa479fcd5cd.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[http_testdatasample.oss-cn-hangzhou.aliyuncs.com_workshop_sh_workshop_sh.zip]: http://testdatasample.oss-cn-hangzhou.aliyuncs.com/workshop_sh/workshop_sh.zip
[OSS_DataLakeAnalytics_QuickBI_Serverless 6]: https://s1.51cto.com/images/blog/201904/08/820f1cb31cba256ad1d325683f3030d4.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 7]: https://s1.51cto.com/images/blog/201904/08/188334623a7c7ce8ec0ba926be15c402.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 8]: https://s1.51cto.com/images/blog/201904/08/9d98200ae11aa8c5de58ac9781089eba.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 9]: https://s1.51cto.com/images/blog/201904/08/ad6c543c5c718ce9d7c22ab943bad93f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 10]: https://s1.51cto.com/images/blog/201904/08/0feb444fedf658b2de25446fa4e01fdc.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 11]: https://s1.51cto.com/images/blog/201904/08/3810b72eee20f5d1f5c3faf3acf07def.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 12]: https://s1.51cto.com/images/blog/201904/08/c2a66ec76094d7f2e31464d8325878f4.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 13]: https://s1.51cto.com/images/blog/201904/08/8af0d8f59c8777a7c3264810a8fb586f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 14]: https://s1.51cto.com/images/blog/201904/08/016fd393e69cb9d899ca8bb9b864e8aa.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 15]: https://s1.51cto.com/images/blog/201904/08/ce9892d54ff51e6fc5ea71c5ff5652ce.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 16]: https://s1.51cto.com/images/blog/201904/08/dad03e81c6cd91241f91de16e3cb5696.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 17]: https://s1.51cto.com/images/blog/201904/08/1800ba16a7ede9b93e2597766481f74d.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 18]: https://s1.51cto.com/images/blog/201904/08/4d017fc3854808be3f235810b65bb2ce.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 19]: https://s1.51cto.com/images/blog/201904/08/bed1fbfe5c53cfa905a3d180828f94fe.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 20]: https://s1.51cto.com/images/blog/201904/08/d238ee357709822bc4da6a447e575e45.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[OSS_DataLakeAnalytics_QuickBI_Serverless 21]: https://s1.51cto.com/images/blog/201904/08/4d0e9278de01288ece333aae11d35bcd.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=