Another word count - Hive UDF with Python

For some reason word count seems to be a good example to start with regular expression, python and map/reduce, well the reason is clear, it's now become the "Hello World" for map/reduce

Input : Alice.txt download from http://www.gutenberg.org, to make reading easier, I am working with only top 10 rows using limit and head;

Steps:
1. Create a table in hive to store the book and load the text file
2. Create a python script to count the words and display
3. Use the python script as UDF (why? Regular expression, loops and other goodies available)

1. Create a table in hive to store the book and load the text file

hive> create table alice (line String) row format delimited lines terminated by '\n';
OK
Time taken: 0.196 seconds

hive> load data local inpath '/home/username/python/alice.txt' into table alice;

Loading data to table database.alice

Table database.alice stats: [numFiles=1,  totalSize=163771]

OK

Time taken: 0.757 seconds

2. Create a python script to count the words and display (I have saved it as 1udf.py)

------------------------------------------------------------------------------------

import sys

import re

count = {}

#select statement in hive prints it to stdout, so the script reads from stdin

for line in sys.stdin: 

        words =  map(lambda x: re.sub(r'[^\w\s]','',x),line.lower().strip().split(' '))

        for w in words:

                count[w] = count.get(w,0) + 1

for x in sorted(count.items(),key = lambda z: z[1], reverse=True):

        print x[0], '\t', x[1]

------------------------------------------------------------------------------------

script can be tested like so: 

$ cat alice.txt  | python 1udf.py | head -n 10

the     1804

          1251

and     912

to       800

a         685

of       625

it        541

she     538

said    462

you    429

3. Use the python script as part of TRANSFORM 

hive> add file /home/username/python/1udf.py; (Important! See errors encountered)

Added resources: [/home/username/python/1udf.py]

select transform(line) using 'python 1udf.py' as t_line , count from alice limit 10;

Stage-Stage-1: Map: 2   Cumulative CPU: 4.9 sec   HDFS Read: 164257 HDFS Write: 126 SUCCESS

Total MapReduce CPU Time Spent: 4 seconds 900 msec

OK

the     1804

        1251

and     912

to      800

a       685

of      625

it      541

she     538

said    462

you     429

Time taken: 16.577 seconds, Fetched: 10 row(s)

using TEZ engine:

hive> set hive.execution.engine=tez;

hive> select transform(line) using 'python 1udf.py' as t_line , count from alice limit 10;

Query ID = username_20151016120505_20e5f39d-7dcc-4dd2-8247-baa8e57b49b9

Total jobs = 1

Launching Job 1 out of 1

Status: Running (Executing on YARN cluster with App id application_1443556515457_25523)

--------------------------------------------------------------------------------

        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED

--------------------------------------------------------------------------------

Map 1 ..........   SUCCEEDED      1          1        0        0       0       0

--------------------------------------------------------------------------------

VERTICES: 01/01  [==========================>>] 100%  ELAPSED TIME: 5.32 s

--------------------------------------------------------------------------------

OK

the     1804

        1251

and     912

to      800

a       685

of      625

it      541

she     538

said    462

you     429

Time taken: 13.724 seconds, Fetched: 10 row(s)

Errors Encountered:
FAILED: Execution Error, return code 20003 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask. An error occurred when trying to close the Operator running your custom script
Reason: I used the query as "select transform(line) using 'python /home/username/python/1udf.py' as t_line , count from alice limit 10;"
Solution: use add file /home/username/python/1udf.py so that the script is available in hive cache
then drop the absolute path in the hive query and just use the python script name

select transform(line) using 'python 1udf.py' as t_line , count from alice limit 10;

Simple Lambda examples in Python

Simple lambda function examples (Python)

Use this to learn lambda, I do not recommend using this in your production code.

The exercises are here:

https://github.com/zhiwehu/Python-programming-exercises/blob/master/100%2B%20Python%20challenging%20programming%20exercises.txt

Solutions with the question numbers below. Had some help from http://stackoverflow.com/questions/33113720/count-in-python-lambda-function

import math
from itertools import count
from operator import itemgetter, attrgetter

mylist = ['abc', '123', '758', 'cde']
hello = "Hello world this is Testing for Caps and Lower"

# 1 print filter(lambda x : x%7 == 0 and x%5!=0, range(2000, 3200))

# 3 print  map(lambda x: (x, x*x) ,range(1,input()+1))

# 6 print map(lambda x: round(math.sqrt((2 * 50 * x)/30)) , list(input()))

# 9 print ''.join(map(lambda x: x.upper(),raw_input()))

# 11 print filter(lambda x : int(x,2)%5 == 0, raw_input().split(','))

# 12 WIP print (lambda y : int(y[0])%2 ==0 ,map(lambda x: x, str(range(1,10))))

# 13 print reduce(lambda (x,y),(a,b): (x+a, y+b), [(e.isdigit(),e.isalpha()) for e in mylist], (0,0))
# 13 print reduce(lambda (x,y),(a,b): (x+a, y+b), map(lambda x: (1 if x.isalpha() else 0, 1 if x.isdigit() else 0), mylist), (0,0))

# 14 print reduce(lambda (x,y), (a,b) : (x+a, y+b) , [(e[0].isupper(), e[0].islower()) for e in hello.split(' ')], (0,0))

# 16 intlist = [1,2,3,4,5,6,7,8,9] print filter(lambda x: x%2!=0, intlist)