阿里推荐大赛:ODPS SQL 入门

第二季才是真正的开始,第一季的汗水、喜悦、纷争都通通过去了。第二季绝对是拼算法、拼模型、拼平台熟练度的比赛,相信坚持下来的话肯定收获颇丰。昨天也迫不及待地进入 「天池」 ,居然是 Windows 系统,一点都提升不了逼格啊。看文档的过程中,又是 ODPS SQL、MapReduce、XLab/XLib、Tunnel、ODPS SDK什么的,相信很多人都跟我一样看的眼花,茫然不知所措,更别提对 MR 不熟悉、SQL 忘地差不多、Java 不熟练的同学了。 到底先学哪个?到底哪个实现模型算法更快?

文档里推荐的开发流程是这样的:

  1. 首先看能否使用 ODPS SQL 搞定,能的话尽量用 SQL,简单,不容易出错;
  2. 编写 M/R 代码,使用本地调试功能进行基本的测试;
  3. 编写 M/R 单元测试用例,ODPS M/R 提供了 MRUnitTest 方便编写单元测试用例;
  4. 进行集群调试,验证结果。

所以从 SQL 入门应该是学习曲线最快的,而且也能方便进行分析,快速实现模型,快速提交结果。

如果您已经看过文档了,小弟觉得就可以跳过下面的啰嗦的内容了 :)

##提交推荐结果
首先,第二季的数据格式与第一季一样,仍是4月15日至8月15日的数据,只不过量更大了(5亿条用户行为记录,不知道是否是全集?)。

原始数据集存在t_alibaba_bigdata_user_brand_total_1表中,完成用户品牌偏好预测后,需将推荐结果需放入t_tmall_add_user_brand_predict_dh表中,格式与第一季一样。系统会每天凌晨自动评分排名。

在 Readme 中有一个通过训练数据集完成用户品牌购买预测的例子,所用的规则是将每个用户点击最多的 10 个品牌,作为预测结果提交。SQL 代码如下:

drop table t_tmall_add_user_brand_predict_dh;
create table t_tmall_add_user_brand_predict_dh as
select
user_id,
wm_concat(',',brand_id) as brand
from(
select
user_id,
brand_id,
row_number() over (partition by user_id order by num desc) as rank
from(
select
user_id,
brand_id,
count(brand_id) as num
from
t_alibaba_bigdata_user_brand_total_1
where type='0'
group by user_id,brand_id
)a
)b
where rank <= 10
group by user_id;
```

这段代码已经有了 sql 编程的感觉了。分析下这段代码,由三层`select`组成。最底层是统计用户对品牌产生点击行为的次数。第二层`select`中有一行代码比较陌生:

```sql
row_number() over (partition by user_id order by num desc) as rank

row_num()是 ODPS SQL 的一个内建窗口函数,功能是在指定开窗口列中按指定列排序后返回所在行号。在这个例子中就是:将 user_id 作为窗口列,按 num 递减排序后,返回对应 user_id 的所在行号。这里使用rank()函数的话,也能实现相同的功能。

所以第二层select就是统计用户对品牌产生点击次数由高到低的排名。最后一层select就是只取每个用户点击最多的 10 个品牌,并将这些品牌用逗号连接作为brand属性的值。wm_concat(string separator, string str)是 ODPS SQL 的一个内建聚合函数,功能是用指定的 spearator 做分隔符,链接str中的值。

最后用create table .. as select ..将查询出来的结果表直接复制给t_tmall_add_user_brand_predict_dh表,完成推荐。注意到每个内层子查询必须要有别名。

看,用 sql 来做写推荐也不难嘛。模仿上面的代码,应该能写出其他规则的推荐。比如将 type='0' 改成 type='1',就成了每个用户购买最多的 10 个品牌(有将近6的召回我是不会跟你说的)。

回顾第一季,最简单有效的一个规则应该是「8月份的用户购买记录」。下面是一个简单实现:

drop table t_tmall_add_user_brand_predict_dh;
create table t_tmall_add_user_brand_predict_dh as
select
user_id,
wm_concat(',',brand_id) as brand
from(
select
distinct user_id,brand_id
from
t_alibaba_bigdata_user_brand_total_1
where type='1' and visit_datetime like '08-%'
)a
group by user_id;

我们使用了visit_datetime like '08-%'来过滤属于八月份的数据,但是想要得到最后一个月的数据呢?最后两个月的数据呢?

看来日期转换是一个必须解决的问题了。

日期转换

感谢 @Adamus_7 网友提醒,对日期的分割可以直接用字符串比较,例如查询最后一个月的记录:

select * from t_alibaba_bigdata_user_brand_total_1 where visit_datetime > '07-15'

但是为了以后加入对日期的分析,例如加入时间衰减因子,还是需要计算日期间隔。

ODPS SQL 提供了非常丰富的日期转换的内建函数,基本满足了我们的需求。

bigint datediff(datetime date1, datetime date2, string datepart)

DATEDIFF 函数的用途是计算两个时间date1date2在指定时间单位datepart的差值。因此我们只要传进8月16日作为date1visit_datetime作为date2datepart以天为单位,就能得到日期差值。但是visit_datetime是一个只包含月日的字符串,因此我们还需要进行字符串拼接(concat 函数)和格式转换(to_date 函数)。

以下是代码示例:

select visit_datetime,
datediff(
to_date('2013-08-16','yyyy-mm-dd'),
to_date(concat('2013-',visit_datetime),'yyyy-mm-dd'),
'dd') as days
from t_alibaba_bigdata_user_brand_total_1
where user_id = '2538398'

结果就是visit_datetime距离8月16日之间的天数。根据这个属性我们就可以进行分割训练集。我们将前三个月的数据作为训练集,最后一个月的购买记录作为验证集。

我们可以创建训练集如下:

create table train_set as
select *
from(
select * ,datediff(
to_date('2013-08-16','yyyy-mm-dd'),
to_date(concat('2013-',visit_datetime),'yyyy-mm-dd'),
'dd') as days
from t_alibaba_bigdata_user_brand_total_1
)a
where days > 30;

验证集如下:

create table validate_set as
select
user_id,
wm_concat(',',brand_id) as brand
from(
select distinct user_id,brand_id
from(
select *,datediff(
to_date('2013-08-16','yyyy-mm-dd'),
to_date(concat('2013-',visit_datetime),'yyyy-mm-dd'),
'dd') as days
from t_alibaba_bigdata_user_brand_total_1
where type = '1'
)a
where days <= 30
)b
group by user_id;

这样训练集中会多一个字段days距离天数了,有了这个字段就可以搞很多有意思的模型了。现在本地的离线评估模型就差个评估算法了,而计算召回率、准确率的前提是计算我们推荐集的 pair 数。

计算推荐集、验证集条数

当然你可以将计算推荐集的最外层查询改成 count(*) ,就能计算出 pair 数。但是这样显然太浪费计算资源了,而且不够通用,以后融合模型、用 MR 怎么办?

我们发现文档里有个REGEXP_COUNT函数。

bigint regexp_count(string source, string pattern[, bigint start_position])

用途:计算 source 中从 start_position 开始,匹配指定模式 pattern 的子串的次数。

例如:

regexp_count('abababc', 'a.c') = 1

所以我们可以这样计算:

select sum(regexp_count(brand,',')+1) from t_tmall_add_user_brand_predict_dh;

就是计算brand中逗号的个数在加一(好弱的正则啊… 复杂正则实在不会写…)。同样的方法,也适用于验证集,计算出验证集validate_set有 254 多万条。

接下来计算推荐集和验证集中重复的个数就能算出F1值了。这个估计要用到 UDF 或者 M/R 了,下次再给上吧。

几点建议

觉得几个可能会比较有用的内建函数,比如substrsplit_partregexp_extract等等。

本文没有什么高深的东西,小弟在这只是抛砖引玉,相信认真看过文档的同学,对上面的东西肯定了然于心。对于没空看文档的同学,希望通过这篇文章能让您重拾文档。另外小弟提点个人的建议:

  • 文档很重要,真的。熟读文档至少一遍,绝对比在那瞎猜函数的用处要好。
  • 先将 SQL 研究透了,基本一些简单的模型规则都能用 SQL 实现。能用 SQL 的就用 SQL。
  • 不能用 SQL 的再使用 MR 。
  • 注重团队分工,不要一股脑儿都去学 sql ,一人主学 sql 一人主学 mr 一人搞模型/算法,注重组内组间多经验分享和交流。

这样学习曲线可能会平缓些。希望对怕第一个月就淘汰的队伍有点帮助吧。

本文匆忙之中赶完,周末还要赶项目(QAQ),文中纰漏之处还望指出~

-EOF-