更新时间:2018-12-02 20:40
本章节以简化的业务场景来介绍电子商务的最佳实践。
本例中简化的业务场景包含三个数据表:客户信息表、订单表、商品类型表(维度表)。
业务场景要求如下:
统计不同客户群体在不同时间段的数据。
RT<60秒。
每秒查询率QPS( Query Per Second ):偶尔查询。
客户信息表(t_fact_customers)
数据量:约有五亿客户
字段名 | 数据类型 | 说明 |
---|---|---|
customer_id | varchar | 客户编号 |
customer_name | varchar | 客户姓名 |
phone_number | varchar | 电话 |
address | varchar | 地址 |
last_login_time | timestamp | 最近登录时间 |
age | int | 年龄 |
订单表(t_fact_orders)
数据量:每日订单量5000万,需要存储三年的数据,总数据量约550亿。
字段名 | 数据类型 | 说明 |
---|---|---|
order_id | varchar | 订单编号 |
customer_id | varchar | 客户编号 |
goods_id | bigint | 商品编号 |
numbers | bigint | 数量 |
total_price | double | 总价 |
order_time | timestamp | 订单时间 |
order_date | bigint | 订单日期(二级分区) |
商品类型表(t_dim_goods)
商品总数约100万
字段名 | 数据类型 | 说明 |
---|---|---|
goods_id | bigint | 商品id |
price | double | 单价 |
class | bigint | 类型 |
name | varchar | 名称 |
update_time | timestamp | 更新时间 |
物理设计的目的是获得最佳性能,在进行表的物理设计前,必须了解表的查询SQL,才能决策表的最佳分片策略。假设本例中大部分查询都需要关联查询客户表+订单表,那么我们优先考虑以customer_id进行一级分区,每个节点的计算都是本地数据。
表组为ads_demo,两个副本,SQL查询超时为30秒。创建表组的SQL语句如下:
create tablegroup ads_demo;
每个分区数据量390万=5亿/128个一级分区。创建t_fact_customers表的SQL语句如下。
CREATE TABLE t_fact_customers (
customer_id varchar COMMENT '',
customer_name varchar COMMENT '',
phone_number varchar COMMENT '',
address varchar COMMENT '',
last_login_time timestamp COMMENT '',
age int COMMENT '',
PRIMARY KEY (customer_id)
)
PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';
二级分区键order_date为bigint数据类型。根据数据量和存储总时间,按月(201712)间隔,每月一个二级分区,每个二级分区数据量为:1193万 = 550亿/(128个一级分区)/(3年*12个月)。创建表的SQL语句如下:
CREATE TABLE t_fact_orders (
order_id varchar COMMENT '',
customer_id varchar COMMENT '',
goods_id bigint COMMENT '',
numbers bigint COMMENT '',
total_price double COMMENT '',
order_time timestamp COMMENT '',
order_date bigint COMMENT '',
PRIMARY KEY (order_id,customer_id,order_date)
)
PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
SUBPARTITION BY LIST KEY (order_date)
SUBPARTITION OPTIONS (available_partition_num = 90)
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';
CREATE DIMENSION TABLE t_dim_goods (
goods_id bigint comment '',
price double comment '',
class bigint comment '',
name VARCHAR comment '',
update_time TIMESTAMP comment '',
primary key (goods_id)
)
OPTIONS (UPDATETYPE='realtime');
按年龄段统计在一段时间内订单的客户数量、订单销售总额。
SELECT
case when cus.age <= 30 then '<20' when cus.age>20
and cus.age <= 30 then '20-30' when cus.age>30
and cus.age <= 40 then '30-40' else '>40' end as age_range,
COUNT(distinct cus.customer_id),
SUM(total_price)
FROM
t_fact_customers cus LEFT JOIN t_fact_orders ord ON cus.customer_id =ord.customer_id
WHERE ord.order_time >= '2017-10-01 00:00:00' AND ord.order_time < '
2017-11-01 00:00:00'
AND ord.order_date = 201710
GROUP BY age_range;
可以增加二级分区条件,进行二级分区裁剪,提高查询效率。
在文档使用中是否遇到以下问题
更多建议
匿名提交