hive自定义python-udf实现topk问题

2019独角兽企业重金招聘Python工程师标准>>>

概述... 2

Mysql 实现topK. 2

Python+hive 实现 top K问题... 4

概述

TopN子句用于规定要返回的记录的数目。针对某一列。

比如有一张表两个字段： studentId、score，topN问题就是查找分数前几的那些学生。

TopK带着分组的含义。针对两列的情况。

比如有一张表三个字段：studentid、courseName、score，TopK问题就是查找不同的course的分数前几的学生。

TopN比较简单，但是只有sql server支持top关键字。

Mysql 使用limit 关键字支持topn，postgresql使用limit offset 支持topn。Oracle使用ROWNUM关键字支持topn。

TopK问题就比较复杂了，要写多段sql

Mysql 实现topK

首先，我们的数据库很简单：

--

-- 表的结构 `ta`

--

CREATE TABLE `ta` (

`id` int(11) NOT NULL auto_increment,

`clsno` varchar(20) collate utf8_bin NOT NULL,

`score` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;

--

-- 导出表中的数据 `ta`

--

INSERT INTO `ta` (`id`, `clsno`, `score`) VALUES

(1, 'aa', 12),

(2, 'aa', 11),

(3, 'aa', 89),

(4, 'bb', 45),

(5, 'bb', 56),

(6, 'bb', 90);

Id为学生id，clsno 为课程名字，score为分数

Mysql不支持top关键字。 可以用limit

SELECT * FROM ta AS T WHERE id IN ( SELECT id FROM ta ORDER BY score DESC limit 0,2);

意思是从第一行起 （包括第一行）查询2条记录。

但这样查询失败。

报错#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

但是可以这样查询：

SELECT * FROM ta AS Tout WHERE id IN ( select t.id from (select * from ta ORDER BY score DESC limit 0,2) as t);

如果是Postgresql 是这样写的：

SELECT id FROM ta ORDER BY score DESC limit 2 offset 0 // 忽略第0条，查询2条记录

典型的topK问题：

SELECT *
FROM ta ass
WHERE 2 > (
SELECT COUNT( 1 )
FROM ta
WHERE clsno = a.clsno
AND score > a.score )
ORDER BY a.clsno, a.score DESC
LIMIT 0 , 30;

结果：

id clsno score

3 aa 89

7 aa 78

6 bb 90

5 bb 56

功能：得到两科目的各自前两名成绩。 俗称TopK问题

如果K=1，可以这么写：

select * from ta a

where not exists

(select 1 from ta where clsno=a.clsno and score>a.score);

Python+hive 实现 top K问题

准备好数据：(分隔符是“\t”)

cat city.txt
1 wh 500
2 bj 600
3 wh 100
4 sh 400
5 wh 200
6 bj 100
7 sh 200
8 bj 300
9 sh 900

> create table city(id int,cname string,value int) row format delimited fields terminated by '\t' STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '/home/server0/hive/apache-hive-0.13.1-bin/input/city.txt' OVERWRITE INTO TABLE city;

rankk.py

#!/usr/bin/python

import sys

import datetime

count=0

lastkey=''

file = sys.stdin

for line in file:

    if not line or not line.strip():

        continue

    try:

            line = line.strip()

           cname,value = line.split("\t");

           key=cname

           if key==lastkey:

                 count+=1

           else:

                 count=0

                 lastkey=key

              print cname+"\t"+str(count)+"\t"+value

       except:

               continue

网上大部分udf自定义函数都是用java写的，github都有代码。java写的有个很大的缺陷，因为你要编译代码，那么你会落入无尽的api版本的麻烦中（hadoop0.20、hadoop1.x、hadoop2.x），但是用python写，没有api版本问题。

添加py文件：

add file /home/server0/hadoop/python/rankk.py;

终极版本：

select cname,value from (select TRANSFORM(a.cname,a.value) USING 'python rankk.py' AS(cname,rank,value) from (select id, cname, value from city distribute by cname sort by cname,value desc) a) b where rank<2;

效果：

注意：分隔符只能是“\t”。python的transform只认“\t”做分隔符，什么其他的全都不认。

-end

转载于:https://my.oschina.net/liangshao/blog/300749