电商场景实战之多类目成交总额管理

背景信息

以下是天猫工程师利用实时计算实现实时多类目成交总额管理的案例。

多类目成交总额管理案例

SQL结构图

1. 创建数据总线（DataHub）源表

   系统订单是实时产生的，源表语句如下。

   CREATE TABLE source_order (
   id VARCHAR,-- 商品ID。
   buyer_id VARCHAR, --买家ID。
   site VARCHAR,--商品类别。
   pay_time VARCHAR,--订单支付时间。
   buy_amount DOUBLE, --订单金额。
   wap VARCHAR--购买方式。
   ) WITH (
   type='datahub',
   endPoint='http://dh-cn-hangzhou.aliyun-inc.com',
   project='yourProjectName',--DataHub中Poject的名称。
   topic='yourTopicName',--DataHub Poject中Topic的名称。
   roleArn='yourRoleArn',--指定角色的roleArn。
   batchReadSize='500'
   );

2. 创建RDS结果表

   RDS结果表语句如下。

   CREATE TABLE ads_site_block_trd_pay_ri ( 
   id VARCHAR,
   site VARCHAR,
   data_time VARCHAR,
   all_alipay BIGINT,
   all_ord_cnt BIGINT,
   primary key(id,site,data_time)
   ) WITH (
   type= 'rds',
   url = 'yourRDSDatabaseURL',--RDS数据库URL。
   tableName = 'yourDatabaseTableName',--RDS数据库中的表名。
   userName = 'yourDatabaseUserName',--登录数据库的的用户名。
   password = 'yourDatabasePassword'--登录数据库的密码。
   ); 

3. 创建一个View视图

   如下根据商品ID、商品类别、买家ID和订单支付时间做分组操作。

   CREATE VIEW tmp_ads_site_block_trd_pay_ri AS
   SELECT
   id id,
   mod(HASH_CODE(`a`.buyer_id),4096) hash_id,
   site site,
   date_format(`a`.pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMddHH') data_time,
   SUM(cast(buy_amount as bigint)) all_alipay,
   count(distinct `a`.buyer_id) all_ord_cnt
   FROM
   source_order `a`
   GROUP BY id , site , mod(HASH_CODE(`a`.buyer_id),4096) , date_format(`a`.pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMddHH') ;

4. 数据聚合后插入RDS结果表

   把分组好的数据聚合后插入RDS结果表中，语句如下。

   INSERT INTO ads_site_block_trd_pay_ri
   SELECT
   id,
   site,
   `a`.data_time,
   CAST(sum(all_alipay) AS BIGINT) as all_alipay,
   CAST(sum(all_ord_cnt) AS BIGINT) as all_ord_cnt
   FROM
   tmp_ads_site_block_trd_pay_ri `a`
   GROUP BY id , site , `a`.data_time ;

常见问题

Q：怎么解决数据倾斜？

A：假如您的ID数据非常大，根据您的ID进行分组计算可能会造成机器热点从而导致数据倾斜，计算性能会很差。

mod(HASH_CODE(`a`.buyer_id),4096) hash_id

使用HASH_CODE这个离散函数来分离数据热点，接下来使用MOD函数对哈希值进行分组操作。这样做的优点是规范每个节点数据的数量，避免大量数据的堆积导致数据倾斜（4096指的是分组的数量，可以根据数据的大小进行分组操作）。HASH_CODE函数详情请参见HASH_CODE。

Q：您得到的UV量是否准确？

A：直接使用count(distinct,buyer_id)会出现计算不准的问题。用GROUP BY mod(HASH_CODE(a.buyer_id),4096)，把相同buyer_id过滤去重后，再做SUM就可以避免这样的错误产生。

CREATE VIEW tmp_ads_site_block_trd_pay_ri AS 
 SELECT 
   id id, 
   mod(HASH_CODE(`a`.buyer_id),4096) hash_id, 
   site site, 
   date_format(`a`.pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMddHH') data_time, 
   SUM(cast(buy_amount as bigint)) all_qty_cnt, 
   count(distinct `a`.buyer_id) all_ord_cnt
     FROM 
 source_order `a` 
  GROUP BY id , site , mod(HASH_CODE(`a`.buyer_id),4096) , date_format(`a`.pay_time , 'yyyy-MM-dd HH:mm:ss' , 'yyyyMMddHH') ;

DEMO示例以及源代码