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

本文涉及的产品
简介: 数据存储在OSS后,可以有多种方式查询分析OSS数据,如自建Spark/Presto/Impala(阿里云EMR以及Hadoop社区官方已支持OSS), 使用MaxCompute、DataLakeAnalytics等。

基于OSS的数据查询分析和可视化BI报表

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

实验步骤( OSS+DataLakeAnalytics+QuickBI)

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

服务开通

OSS服务:

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

DataLakeAnalytics服务:

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

操作步骤

准备数据

登录控制台, 并创建目录

image

创建华东1区域的OSS测试Bucket。
image
备注:Bucket名称是全局唯一,如果提示名称已存在,请更换一个Bucket名称。

创建目录(建议目录如下):

创建目录workshop_sh/trade, workshop_sh/user
image
image

下载模拟数据(该数据本次实验的模拟数据)

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

将下载的将交易记录和开户信息数据,分别上传到trade、user目录

image
image

登录Data Lake Analytics控制台

点击“登录数据库”,输入开通服务时分配的用户名和密码,登录Data Lake Analytics控制台。
image
公测期间的分配的用户名、密码,开通服务的消息中可以查看
image

创建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张表
image

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;

显示执行结果
image

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;

image

SQL查询(异步执行)

异步执行查询,将查询结果,以CSV格式,输出到OSS上

image

点击“执行状态”,可看到该异步查询任务的执行状态

主要分为:“RUNNING”,“SUCCESS”,“FAILURE”。
点击“刷新”,当STATUS变为“SUCCESS”时,可以查看到查询结果输出到OSS的文件路径。
image

查看导出OSS的结果文件

image

基于QuickBI制作数据报表

创建数据源

说明

  • 目前QuickBI只有专业版支持DataLakeAnalytics作为数据源
  • 数据源-数据库地址、端口,可在DataLakeAnalytics控制台查看
    image
  • 数据源-数据库名称,使用在DataLakeAnalytics创建的SCHEMA名称
  • 数据源-用户名、密码,即登录DataLakeAnalytics数据库使用的用户名/密码(初始化服务时,发送到站内信中)
    image

创建数据集

说明

  • 点击创建数据集
  • 选择数据源-自定义SQL
    image
  • 输入SQL(输入前述实验步骤的SQL),并保存(如保存为“citygender”)
    image

image

创建仪表盘

说明

  • 点击创建仪表板
  • 选择数据集(如citygender)
  • 分别选择 值轴、类别轴、颜色分类
  • 点击“刷新”
    这里显示了各城市男性、女性投资者购买基金的总额

image

更多信息

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

相关实践学习
基于函数计算一键部署掌上游戏机
本场景介绍如何使用阿里云计算服务命令快速搭建一个掌上游戏机。
建立 Serverless 思维
本课程包括: Serverless 应用引擎的概念, 为开发者带来的实际价值, 以及让您了解常见的 Serverless 架构模式
目录
相关文章
|
4天前
|
监控 Serverless 测试技术
Serverless 应用引擎常见问题之生成的图片的oss地址配成自定义的域名如何解决
Serverless 应用引擎(Serverless Application Engine, SAE)是一种完全托管的应用平台,它允许开发者无需管理服务器即可构建和部署应用。以下是Serverless 应用引擎使用过程中的一些常见问题及其答案的汇总:
16 0
|
28天前
|
BI
Quick BI V5.0发布:一键解锁智能小Q等全新智能商业分析能力
Quick BI V5.0发布:一键解锁智能小Q等全新智能商业分析能力
|
1月前
|
数据采集 数据可视化 数据挖掘
数据分析案例-BI工程师招聘岗位信息可视化分析
数据分析案例-BI工程师招聘岗位信息可视化分析
46 0
|
2月前
|
关系型数据库 MySQL Serverless
高顿教育:大数据抽数分析业务引入polardb mysql serverless
高顿教育通过使用polardb serverless形态进行数据汇总,然后统一进行数据同步到数仓,业务有明显高低峰期,灵活的弹性伸缩能力,大大降低了客户使用成本。
|
3月前
|
关系型数据库 BI 分布式数据库
PolarDB NL2BI解决方案,让你不懂SQL也能进行数据查询分析并生成BI报表
无需创建和开通资源,在预置环境中免费体验PolarDB MySQL及其NL2BI解决方案
PolarDB NL2BI解决方案,让你不懂SQL也能进行数据查询分析并生成BI报表
|
5月前
|
存储 监控 数据挖掘
系统分析师:商业智能和业务流程管理的集成分析
系统分析师:商业智能和业务流程管理的集成分析
39 0
|
1月前
|
存储 监控 BI
OSS日志查询
实时日志查询功能将OSS与日志服务SLS相结合,允许您在OSS控制台直接查询OSS的访问日志
19 1
|
1月前
|
SQL 数据可视化 数据建模
大数据分析利器之Power BI,你是否已经掌握?
大数据分析利器之Power BI,你是否已经掌握?
41 0
|
2月前
|
Kubernetes 前端开发 Serverless
Serverless 应用引擎报错问题之部署报错如何解决
Serverless部署是指将应用程序部署到无服务器架构中,该架构允许开发者专注于代码而无需关心底层服务器的运行和维护;针对Serverless部署过程中可能遇到的挑战,本合集提供全面的指南和最佳实践,帮助开发者顺利实现应用的无服务器化部署。
125 1
|
2月前
|
SQL 存储 分布式计算
Hologres实时湖仓能力增强,挑战5分钟加速分析OSS数据
5分钟快速使用Hologres实时湖仓能力,无需移动数据,直接加速读取存储于数据湖OSS上的Hudi、Delta、Paimon等格式类型的数据

相关产品

  • 函数计算