电子商务

业务场景和需求

本例中简化的业务场景包含三个数据表：客户信息表、订单表、商品类型表（维度表）。

表的逻辑设计

• 客户信息表（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	商品编号
  price	double	单价
  class	bigint	类型
  name	varchar	名称
  update_time	timestamp	更新时间

表的物理设计

物理设计的目的是获得最佳性能，在进行表的物理设计前，必须了解表的查询SQL，才能决策表的最佳分片策略。假设本例中大部分查询都需要关联查询客户表和订单表，那么我们优先考虑以customer_id进行一级分区，每个节点的计算都是本地数据。

• 创建表组ads_demo

  表组为ads_demo，两个副本，SQL查询超时为30秒。创建表组的SQL语句如下：

  create tablegroup ads_demo;

• 创建表
  • 创建t_fact_customers表
    每个分区数据量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 '';

  • 创建t_fact_orders表
    二级分区键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 '';

  • 创建t_dim_goods商品信息表（维度表）

    建表语句如下：

    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');

查询SQL

按年龄段统计在一段时间内订单的客户数量、订单销售总额。

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;