目标
通过这个案例,希望你能掌握下面几个知识点并灵活运用:
1.业务场景描述
假设你是一家国内大型食品电商公司的CEO,你们公司的愿景就是让天下的吃货都吃得好。
但中国之大,众口难调,每个地方都有自己独特的饮食文化:从南到北,口味由咸转淡,从西到东,口味由辣转甜,从陆到海,口味由重转轻……
作为CEO,你自然十分关心一个问题:自己有没有服务好全国各地的吃货?同时又作为数据时代的弄潮儿,你并不屑于去做用户调研,而是准备用数据化的方式来解决这个问题:统计收货人的地理分布,看看哪个多,哪个少,少的是不是因为网站上缺少符合这个地方口味的食品?是的话尽快调整品类分布。
如果能快速的完成这一连串的动作,那么众口不再难调。
动机有了,思路有了,剩下的就让阿里云流计算来帮你吧。
2.流计算解决方案
2.1数据流
系统订单是实时产生的,数据格式如下:
(注:为了聚焦核心逻辑,订单数据格式做了大量精简,只保留了与案例有关的属性)
CREATE TABLE source_order (
id VARCHAR,-- 订单ID
seller_id VARCHAR, --卖家ID
account_id VARCHAR,--买家ID
receive_address_id VARCHAR,--收货地址ID
total_price VARCHAR,--订单金额
pay_time VARCHAR --订单支付时间
) WITH (
type='datahub',
endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
project='xxx',--你的project
topic='xxx',--你的topic
roleArn='xxx',--你的roleArn
batchReadSize='500'
);
AI 代码解读
订单是一种流数据,源源不断的产生,本案例把这份数据发送到datahub中。
在电商系统里,订单与订单地址一般都是分开存储的(下单人可以给多个地址下单),所以在订单创建时并没有收货地址,只有在订单提交时才真正的知道收货地址。
所以订单地址也是一种流数据,其随着订单的创建而不断增加,同样,这份数据也发送到datahub中。
假设订单地址的数据格式如下:
CREATE TABLE source_order_receive_address (
id VARCHAR,--收货地址ID
full_name VARCHAR,--收货人全名
mobile_number VARCHAR,--收货人手机号
detail_address VARCHAR,--收货详细地址
province VARCHAR,--收货省份
city_id VARCHAR,--收货城市
create_time VARCHAR --创建时间
) WITH (
type='datahub',
endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
project='xxx',--你的project
topic='xxx',--你的topic
roleArn='xxx',--你的roleArn
batchReadSize='500'
);
AI 代码解读
订单地址里保存的是城市的id(city_id),为了获取地理信息,我们还需要一张城市表,这张表存储着城市的地理信息。城市的地理信息是不会变化的,静态的。
假设这张表存储在rds中,格式如下:
CREATE TABLE dim_city (
city_id varchar,
city_name varchar,--城市名
province_id varchar,--所属省份ID
zip_code varchar,--邮编
lng varchar,--经度
lat varchar,--纬度
PRIMARY KEY (city_id),
PERIOD FOR SYSTEM_TIME --定义为维表
) WITH (
type= 'rds',
url = 'xxxx',--你的数据库url
tableName = 'xxx',--你的表名
userName = 'xxx',--你的用户名
password = 'xxx'--你的密码
);
AI 代码解读
三份数据都有了,其中有两份流式数据,一份静态数据。
我们的目标是按日统计不同地域订单(总销售额)的分布情况,假设结果的数据格式如下:
CREATE TABLE result_order_city_distribution (
summary_date bigint,--统计日期
city_id bigint,--城市ID
city_name varchar,--城市名
province_id bigint,--所属省份ID
gmv double,--总销售额
lng varchar,--经度
lat varchar,--纬度
primary key (summary_date,city_id)
) WITH (
type= 'rds',
url = 'xxxx',--你的数据库url
tableName = 'xxx',--你的表名
userName = 'xxx',--你的用户名
password = 'xxx'--你的密码
);
AI 代码解读
这份数据存于RDS中,并且实时更新。
为了完成我们的目标,设计下面的数据流:
计算分三个步骤:
- 订单数据流与订单收货地址数据流join,得到订单的收货地址id;
- 根据城市ID和日期统计销售额;
- 统计结果与城市信息join,补齐城市name和地理位置信息,得到最终数据;
2.2 数据开发
根据上边的数据流,我们开始进行开发,从模式上讲,这个任务是一个典型的【双流join然后维表join】任务。
代码如下:
--定义输入表:订单数据
CREATE TABLE source_order (
id VARCHAR,-- 订单ID
seller_id VARCHAR, --卖家ID
account_id VARCHAR,--买家ID
receive_address_id VARCHAR,--收货地址ID
total_price VARCHAR,--订单金额
pay_time VARCHAR --订单支付时间
) WITH (
type='datahub',
endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
project='xxx',--你的project
topic='xxx',--你的topic
roleArn='xxx',--你的roleArn
batchReadSize='500'
);
AI 代码解读
--定义输入表:订单收货地址
CREATE TABLE source_order_receive_address (
id VARCHAR,--收货地址ID
full_name VARCHAR,--收货人全名
mobile_number VARCHAR,--收货人手机号
detail_address VARCHAR,--收货详细地址
city_id VARCHAR,--收货城市
create_time VARCHAR --创建时间
) WITH (
type='datahub',
endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
project='xxx',#你的project
topic='xxx',#你的topic
roleArn='xxx',#你的roleArn
batchReadSize='500'
);
AI 代码解读
--定义维表:城市信息
CREATE TABLE dim_city (
city_id varchar,
city_name varchar,--城市名
province_id varchar,--所属省份ID
zip_code varchar,--邮编
lng varchar,--经度
lat varchar,--纬度
PRIMARY KEY (city_id),
PERIOD FOR SYSTEM_TIME --定义为维表
) WITH (
type= 'rds',
url = 'xxxx',--你的数据库url
tableName = 'xxx',--你的表名
userName = 'xxx',--你的用户名
password = 'xxx'--你的密码
);
AI 代码解读
--定义结果表:销售额城市分布表
CREATE TABLE result_order_city_distribution (
summary_date varchar,--统计日期
city_id bigint,--城市ID
city_name varchar,--城市名
province_id bigint,--所属省份ID
gmv double,--总销售额
lng varchar,--经度
lat varchar,--纬度
primary key (summary_date,city_id)
) WITH (
type= 'rds',
url = 'xxxx',--你的数据库url
tableName = 'xxx',--你的表名
userName = 'xxx',--你的用户名
password = 'xxx'--你的密码
);
AI 代码解读
--完成计算,通过SQL这种方式,只用下面几行代码就完成了整个过程,其优越性可见一斑。
insert into result_order_city_distribution
select
d.summary_date
,cast(d.city_id as BIGINT)
,e.city_name
,cast(e.province_id as BIGINT)
,d.gmv
,e.lng
,e.lat
,e.lnglat
from
(
select
DISTINCT
DATE_FORMAT(a.pay_time,'yyyyMMdd') as summary_date
,b.city_id as city_id
,round(sum(cast(a.total_price as double)),2) as gmv
from source_order as a
join source_order_receive_address as b on a.receive_address_id =b.id
group by DATE_FORMAT(a.pay_time,'yyyyMMdd'),b.city_id
--双流join,并根据日期和城市ID得到销售额分布
)d join dim_city FOR SYSTEM_TIME AS OF PROCTIME() as e on d.city_id = e.city_id
-- join维表,补齐城市地理信息,得到最终结果
;
AI 代码解读
2.3 数据准备&调试&上线&运维
这部分可以参考文档,本案例不在赘述。
3.总结
整个案例从逻辑上说非常简单,其覆盖的知识点和各知识点的作用如下:
相信你早就掌握了单个的知识点,所以我们把不同的知识点结合起来做了一个案例,通过这个案例,你一定有了不小的提高。
一定要注意体会维表join的应用场景,什么样的数据通过维表来加载,什么样的数据通过流数据来加载,为什么订单收货地址是流式数据。
SQL很简单,流计算很简单,一旦你学会灵活使用,却能展示出强大的能力。