[TOC] # 案例一 Hive的 TRANSFORM 關鍵字提供了在SQL中調用自寫腳本的功能 適合實現Hive中沒有的功能又不想寫UDF的情況 使用示例1： 下面這句sql就是借用了weekday_mapper.py對數據進行了處理. ~~~ CREATE TABLE u_data_new ( movieid INT, rating INT, weekday INT, userid INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; ~~~ ~~~ add FILE weekday_mapper.py; ~~~ ~~~ INSERT OVERWRITE TABLE u_data_new SELECT TRANSFORM (movieid , rate, timestring,uid) USING 'python weekday_mapper.py' AS (movieid, rating, weekday,userid) FROM t_rating; ~~~ 其中weekday_mapper.py內容如下 ~~~ #!/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]) ~~~ # 案例二 rating.json ~~~ {"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"} {"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"} {"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"} {"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"} {"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"} {"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"} {"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"} {"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"} {"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"} {"movie":"919","rate":"4","timeStamp":"978301368","uid":"1"} {"movie":"595","rate":"5","timeStamp":"978824268","uid":"1"} {"movie":"938","rate":"4","timeStamp":"978301752","uid":"1"} ~~~ 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腳本文件 ~~~ ########python######代碼 vi weekday_mapper.py #!/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; ~~~