TableStore实战:DLA+SQL实时分析TableStore

一、实战背景

什么是DLA(DataLake Analytics数据湖)?他是无服务器化(Serverless)的云上交互式查询分析服务。作为分布式交互式分析服务,是表格存储计算生态的重要组成之一。为了使用户更好的了解DLA的功能、使用方式,创建了这一实战样例。
基于DLA可以不用做任何ETL、数据搬迁等前置过程, 实现跨各种异构数据源进行大数据关联分析,并且支持数据回流到各个异构数据源,从而极大的节省成本、 降低延时和提升用户体验。

TableStore实战:DLA+SQL实时分析TableStore

基于JDBC,表格存储的控制台将SQL查询直接做了集成,数据为公共实例,用户不用开通服务也可免费体验表格存储的实时SQL分析、查询功能,样例如下所示:__官网控制台地址:__项目样例

TableStore实战:DLA+SQL实时分析TableStore


需求场景:黑五交易数据

本实战案例中,我们从 https://www.kaggle.com/mehdidag/black-friday 上获取数据, 存储到TableStore,然后基于DLA做分析,带你切身感受下数据的价值!

"Black Friday",即“黑色星期五”,是美国人一年中购物最疯狂的日子,类似于中国的“双十一”购物狂欢节。
一般黑色星期五的活动主要在线下,但逐渐也有往线上发展的趋势,比如Amazon就有针对黑色星期五做的线上销售活动, 与天猫双十一很相似。同样的,这样的活动会产生大量有意义的商业数据。
我们在DLA中定义了一个叫blackfriday50w的表,映射到TableStore中的一个表,用来描述用户购买商品的。

如下为示例数据的表结构、与真实数据截图

TableStore实战:DLA+SQL实时分析TableStore


二、表格存储(TableStore)方案


准备工作

若您对于DLA实时在线分析TableStore的功能感兴趣,希望开始自己系统的搭建之旅,只需按照如下步骤便可以着手搭建了:

1、开通表格存储

通过控制台开通表格存储服务,表格存储即开即用(后付费),采用按量付费方式,已为用户提供足够功能测试的免费额度。表格存储官网控制台免费额度说明

2、创建实例

通过控制台创建表格存储实例。

TableStore实战:DLA+SQL实时分析TableStore


3、导入数据

该数据共有53.8万行,12个列,我们通过SDK将全量数据存储在TableStore的表。用户可通过控制台插入2条测试数据;

TableStore实战:DLA+SQL实时分析TableStore


开通DLA服务

  • DLA服务开通
    用户进入产品介绍页,选择开通服务:https://www.aliyun.com/product/datalakeanalytics
  • 通过DLA控制台开通TableStore数据源
  • 开通数据源后创建服务访问点(择经典网络,若已有vpc,可选择vpc)
  • 登录CMS(账密会在开通服务后发送站内消息,消息中查看)

TableStore实战:DLA+SQL实时分析TableStore


创建DLA外表

1)创建自己的DLA库(相关信息从上述过程中查找):

mysql> create database hangzhou_ots_test with dbproperties (
  catalog = 'ots',
  location = 'https://instanceName.cn-hangzhou.ots-internal.aliyuncs.com',
  instance = 'instanceName'
);

Query OK, 0 rows affected (0.23 sec)

#hangzhou_ots_test             ---请注意库名,允许字母、数字、下划线
#catalog = 'ots',              ---指定为ots,是为了区分其他数据源,比如oss、rds等
#location = 'https://xxx'      ---ots的endpoint,从实例上可以看到
#instance = 'hz-tpch-1x-vol'

2)查看自己创建的库:

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| hangzhou_ots_test            |
+------------------------------+
1 rows in set (0.22 sec)

3)查看自己的DLA表:

mysql> use hangzhou_ots_test;
Database changed

mysql> show tables;
Empty set (0.30 sec)

4)创建DLA表,映射到OTS的表:

mysql> CREATE EXTERNAL TABLE `tableName` (
  `pk1` varchar(100) not NULL ,
  `pk2` int not NULL ,
  `col1` varchar(100) NULL ,
  `col2` varchar(100) NULL ,
  PRIMARY KEY (`pk1`, `pk2`)
);
Query OK, 0 rows affected (0.36 sec)

## `tableName`                  ---- TableStore中对应的表名(dla中会转换成小写后做映射)
## `pk2` int not NULL           ---- 如果是主键的话,必须要not null
## PRIMARY KEY (`pk1`, `pk2`)   ---- 务必与ots中的主键顺序相同;名称的话也要对应

5)查看自己创建的表和相关的DDL语句:

mysql> show tables;
+------------+
| Table_Name |
+------------+
| tablename  |
+------------+
1 row in set (0.35 sec)

6)开始查询和分析(用户可以分析自己的数据,符合mysql的语法)

mysql> select count(*) from tablename;
+-------+
| _col0 |
+-------+
|    25 |
+-------+
1 row in set (1.19 sec)

这样,一个TableStore在DLA中的关联外表创建成功,用户便可以通过JDBC、或者CMS控制台,根据自己的需求实时分析自己的TableStore表了。

三、表格存储控制台展示

如下为控制提供的SQL场景,用户可以仿照控制台中实例自己写一些需求SQL,开来尝试吧!

  • 最畅销的top10产品和销售量

TableStore实战:DLA+SQL实时分析TableStore

  • 中高端产品占总体GMV的比例

TableStore实战:DLA+SQL实时分析TableStore

  • 不同年龄段的消费客单价趋势

TableStore实战:DLA+SQL实时分析TableStore

  • 高消费人群的性别和年龄趋势

TableStore实战:DLA+SQL实时分析TableStore


四、欢迎加入

这样,基于DLA+SQL实现的TableStore实时在线分析已经完成,是不是很简单?
对表格存储(TableStore)感兴趣的用户,欢迎加入【表格存储公开交流群】,群号:11789671。

TableStore实战:DLA+SQL实时分析TableStore

上一篇:hadoop-HA集群搭建,启动DataNode,检测启动状态,执行HDFS命令,启动YARN,HDFS权限配置,C++客户端编程,常见错误


下一篇:apache三种工作模式介绍