hive python 笔记

分析数据用的小工具，做个笔记

/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