全部产品
云市场

物流快递

更新时间:2018-12-02 20:40:16

本章节以简化的业务场景来介绍物流快递的最佳实践。

业务场景和要求

  • 本例中简化的业务场景:仅包括邮件状态表用于实时监控和更新邮件包裹状态,包裹从订单、收单、装车、…、投递分为不同的状态。

  • 业务要求:每个机构实时统计当前不同状态的邮件数量。

  • 业务性能要求:

    • 实时统计新增数据,且要求一秒以内返回结果(即RT<1s)。

    • 查询并发量:QPS>300 。

表的逻辑设计

邮件状态表(t_fact_mail_status)

要求:

  • 单日数据量约一亿记录;
  • 有15万机构或快递员,每个机构的邮件量为100到3000不等;
  • 存储30天的数据。

为满足高QPS,从设计上采用大宽表、冗余字段,并且避免表关联。

字段名 字段类型 说明
mail_id varchar 邮件订单唯一码
scan_timestamp timestamp 收件时间
biz_date bigint 日期(二级分区)
org_code varchar 机构编码
org_name varchar 机构名称
dlv_person_name varchar 投递员名称
receiver_name varchar 收件人名
receiver_phone varchar 收件人电话
receiver_addr 收件人地址 varchar 收件人地址
product_no 产品编码 varchar 产品编码
mag_no varchar 包裹编号
op_1_timestamp bigint 状态1操作时间
op_2_timestamp bigint 状态2操作时间
op_3_timestamp bigint 状态3操作时间
op_4_timestamp bigint 状态4操作时间
op_5_timestamp bigint 状态5操作时间

表的物理设计

创建邮件状态表 t_fact_mail_status ,需要规划表的一级分区、二级分区。业务查询主要按机构进行查询,QPS要求高。综上所述, 我们选择按org_code进行一级分区。

  1. CREATE TABLE t_fact_mail_status (
  2. mail_id varchar COMMENT '',
  3. scan_timestamp timestamp COMMENT '',
  4. biz_date bigint COMMENT '',
  5. org_code varchar COMMENT '',
  6. org_name varchar COMMENT '',
  7. dlv_person_name varchar COMMENT '',
  8. receiver_name varchar COMMENT '',
  9. receiver_phone varchar COMMENT '',
  10. receiver_addr varchar COMMENT '',
  11. product_no varchar COMMENT '',
  12. mag_no varchar COMMENT '',
  13. op_1_timestamp bigint COMMENT '',
  14. op_2_timestamp bigint COMMENT '',
  15. op_3_timestamp bigint COMMENT '',
  16. op_4_timestamp bigint COMMENT '',
  17. op_5_timestamp bigint COMMENT '',
  18. PRIMARY KEY (mail_id,org_code,biz_date)
  19. )
  20. PARTITION BY HASH KEY (org_code) PARTITION NUM 128
  21. SUBPARTITION BY LIST KEY (biz_date)
  22. SUBPARTITION OPTIONS (available_partition_num = 30)
  23. TABLEGROUP ads_demo
  24. OPTIONS (UPDATETYPE='realtime')
  25. COMMENT '';

查询SQL

要求:

  • 查询当天某机构号

  • 不同状态的邮件数据量

  • 核查需要操作的邮件量

  • RT<1s 并且高QPS

综上所述,须利用分区裁剪,使单个查询在不同的节点上运行。SQL语句示例如下:

  1. select sum(case when t.op_1_timestamp >= 20171128000000 and t.
  2. op_1_timestamp <= 20171128235900
  3. and(( t.op_2_timestamp is null or t.op_2_timestamp >
  4. 20171128235900 or t.op_2_timestamp < 20171128000000)
  5. and( t.op_3_timestamp is null or t.op_3_timestamp >
  6. 20171128235900 or t.op_3_timestamp < 20171128000000)
  7. and( t.op_4_timestamp is null or t.op_4_timestamp >
  8. 20171128235900 or t.op_4_timestamp < 20171128000000))
  9. then 1 else 0 end ) as cn
  10. from t_fact_mail_status t
  11. where t.org_code = '21111101' and t.biz_date = 20171128;