searchstr_map.py
#!/usr/bin/env python
#coding:utf-8
import sys
import re
for line in sys.stdin:
line = line.strip()
fields = line.split("\t")
cityid = str(fields[0])
searchstr = fields[1]
pv = str(fields[2])
qrys = re.findall(r'([a-z][\-_.0-9]+)', searchstr)
qrys.sort()
sorted_str = ''.join(qrys)
print '%s\t%s\t%s\t%s' % (cityid, searchstr, sorted_str, pv)
hive 命令行使用:
add file searchstr_map.py
select
TRANSFORM (src.cityid, src.searchstr, src.pv)
USING 'python searchstr_map.py'
AS (cityid, searchstr, sorted_str, pv)
from
(select
if(cityid='1', 0, cast(cityid as int)) cityid,
a.searchstr,
count(1) pv
from
(select
split(request['cityid'], ',') cityid_arr,
request['searchstr'] searchstr
from db.tablename where dt_ymd=20170731
and request_ser like '%/search/carinfo%'
and request['searchstr'] is not null and request['searchstr'] <> '') a
lateral view explode(a.cityid_arr)t as cityid
where cityid rlike '^[0-9]+$'
group by if(cityid='1', 0, cast(cityid as int)), a.searchstr) src