Hive TransForm案例

Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能，适合实现Hive中没有的功能又不想写UDF的情况。

步骤

1、先加载rating.json文件到hive的一个原始表 rat_json

create table rat_json(line string) row format delimited;
load data local inpath '/home/hadoop/rating.json' into table rat_json;

2、需要解析json数据成四个字段，插入一张新的表 t_rating

insert overwrite table t_rating
select get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate  from rat_json;

3、使用transform+python的方式去转换unixtime为weekday

先编辑一个python脚本文件

vi weekday_mapper.py

内容如下：

########python######代码

#!/bin/python
import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  movieid, rating, unixtime,userid = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print '\t'.join([movieid, rating, str(weekday),userid])

保存文件

然后，将文件加入hive的classpath：

hive>add FILE /home/hadoop/weekday_mapper.py;

执行查询

hive>create TABLE u_data_new as
SELECT
  TRANSFORM (movieid, rate, timestring,uid)
  USING 'python weekday_mapper.py'
  AS (movieid, rate, weekday,uid)
FROM t_rating;

select distinct(weekday) from u_data_new limit 10;