分析数据用的小工具,做个笔记
/app/hives/myscript/nginx_ip_date.py;
add file 'xxx.py' 加载python脚本
transform(...) using 'xxx.py' as c0,c1,... 在 sql 中使用python脚本处理数据
distinct 可以去除重复
distrbute by 指定分区关键字就是 mapred 的 partition 函数
sort by 就是 reducen 内排序
以下是一个demo 用途是统计nginx日志的PV和独立IP,按天统计:
/app/hives/myscript/nginx_ip_date.py;
#!/usr/bin/env python
#coding:utf-8
import datetime
import time
import re
line = '''
202.43.151.2 - - [28/Mar/2012:19:17:28 +0800] "GET /favicon.ico HTTP/1.1" http_status 404 988 "-" "360se" "-" 0.010
'''
class Parser :
'''
把传进来的固定格式的日期,转换成更友好的格式,方便统计
date 方法返回 Y-m-d 格式日期
dateH 方法返回 Y-m-d H 就是精确到小时
'''
def __init__( self , str ):
str = str.replace(' +0800','')
str = str.replace('[','')
str = str.replace(']','')
self.str = str
def date( self ) :
dt = datetime.datetime.strptime( self.str,'%d/%b/%Y:%H:%M:%S')
return dt.strftime('%Y-%m-%d')
def dateH( self ) :
dt = datetime.datetime.strptime( self.str,'%d/%b/%Y:%H:%M:%S')
return dt.strftime('%Y-%m-%d %H')
class LineVo() :
'''
暂时把一行数据分为两个列,IP和日期,用来训练PV等统计
'''
def __init__( self , line ):
self.line = line
def getDate( self ):
pattern = '\\[.+\\+0800\\]'
match = re.search( pattern , self.line )
s = match.start()
e = match.end()
return self.line[s+1:e-1]
def getIp( self ):
pattern = '\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3} '
match = re.search( pattern , self.line )
s = match.start()
e = match.end()
return self.line[s:e]
for line in sys.stdin :
try:
vo = LineVo(line.strip())
dt_src = vo.getDate()
ip = vo.getIp()
dt = Parser(dt_src).date()
print '%s\t%s' % (ip,dt)
except:
print 'fuck\terror'
add file 'xxx.py' 加载python脚本
transform(...) using 'xxx.py' as c0,c1,... 在 sql 中使用python脚本处理数据
distinct 可以去除重复
distrbute by 指定分区关键字就是 mapred 的 partition 函数
sort by 就是 reducen 内排序
以下是一个demo 用途是统计nginx日志的PV和独立IP,按天统计:
add file /app/hives/myscript/nginx_ip_date.py;
select *
from (
select dt , count(ip) as pv , count(distinct ip) as sip
from (
select transform(info) using 'nginx_ip_date.py' as ip , dt
from log_src
where ds = '2013-05-30' and type = 'users.access.log'
)tab1 group by dt
) tab1
distribute by tab1.pv sort by tab1.pv desc , tab1.sip asc