I guys, This blog have been move to new address hadoopgyaan.tk so, please from now onwards new case studies have been posts in the given website.Keep visiting my new website for latests case studies.
A simple UDF to split JSON arrays into Hive arrays.
Building
Check out the code and run
mvn package
to build an uberjar with everything you need.
Split UDF
The split UDF accepts a single JSON string containing only an array. In the Hive CLI:
add jar target/JsonSplit-1.0-SNAPSHOT.jar;
create temporary function json_split as 'com.pythian.hive.udf.JsonSplitUDF';
create table json_example (json string);
load data local inpath 'split_example.json' into table json_example;
SELECT ex.* FROM json_example LATERAL VIEW explode(json_split(json_example.json)) ex;
json_split converts the string to the following array of structs, which are exploded into individual records:
You can access the JSON string for the element with the json_string attribute. The json_string can be any arbitrary JSON string, including another array or a nested object. row_id is the position in the array.
Map UDF
The map UDF accepts a flat JSON object (only integer and string values, no arrays or maps) and converts it into a Hive map. The elements of the map don't have to be defined until query-time, and can be accessed with the square bracket syntax ['key'].
add jar target/JsonSplit-1.0-SNAPSHOT.jar;
create temporary function json_map as 'com.pythian.hive.udf.JsonMapUDF';
create table json_map_example (json string);
load data local inpath 'map_example.json' into table json_map_example;
SELECT json_map(json)['x'] FROM json_map_example LATERAL VIEW explode(json_split(json_example.json)) ex;
The above converts the JSON string to a map, then pulls out the value for each record's key 'x'.
hive>selectname from json_table LATERAL VIEW explode(jsonArray(data, 'request.countries')) t AS name;...
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.3 sec HDFS Read: 643 HDFS Write: 174 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 300 msec
OK
{"name":"USA","regions":["California","New York","Washington"]}
{"name":"Japan","regions":["Tokyo","Osaka","Aichi"]}
{"name":"Italy","regions":["Lazio","Lombardy","Veneto"]}
Time taken: 17.082 seconds, Fetched: 3 row(s)
Path 2
hive>selectname from json_table LATERAL VIEW explode(jsonArray(data, 'request.countries.regions')) t AS name;...
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.23 sec HDFS Read: 643 HDFS Write: 71 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 230 msec
OK
California
New York
Washington
Tokyo
Osaka
Aichi
Lazio
Lombardy
Veneto
Time taken: 14.134 seconds, Fetched: 9 row(s)
Dependencies : Maven
Compiling and Building
To compile run:
# mvn compile
To get JAR (located in target/) run:
# mvn package
Usage
hive> ADD JAR target/hive-udfs-1.0-SNAPSHOT.jar;
hive> create temporary function jsonArray as 'ArrayUDF';
hive> create table json_table( data string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/hive/test/';
hive> select name from json_table LATERAL VIEW explode(jsonArray(data, 'request.countries')) t AS name;
Funnel analysis is a method for tracking user conversion rates across actions. This enables detection of actions causing high user fallout.
These Hive UDFs enables funnel analysis to be performed simply and easily on any Hive table.
Requirements
Maven is required to build the funnel UDFs.
How to build
There is a provided Makefile with all the build targets.
Build JAR
make jar
This creates a funnel.jar in the target/ directory.
Register JAR with Hive
To use the funnel UDFs, you need to register it with Hive.
With temporary functions:
ADD JAR funnel.jar;
CREATE TEMPORARY FUNCTION funnel AS'com.yahoo.hive.udf.funnel.Funnel';
CREATE TEMPORARY FUNCTION funnel_merge AS'com.yahoo.hive.udf.funnel.Merge';
CREATE TEMPORARY FUNCTION funnel_percent AS'com.yahoo.hive.udf.funnel.Percent';
With permenant functions you need to put the JAR on HDFS, and it will be registered with a database (you have to replaceDATABASE and PATH_TO_JAR with your values):
CREATEFUNCTIONDATABASE.funnel AS'com.yahoo.hive.udf.funnel.Funnel' USING JAR 'hdfs:///PATH_TO_JAR/funnel.jar';
CREATEFUNCTIONDATABASE.funnel_merge AS'com.yahoo.hive.udf.funnel.Merge' USING JAR 'hdfs:///PATH_TO_JAR/funnel.jar';
CREATEFUNCTIONDATABASE.funnel_percent AS'com.yahoo.hive.udf.funnel.Percent' USING JAR 'hdfs:///PATH_TO_JAR/funnel.jar';
How to use
There are three funnel UDFs provided: funnel, funnel_merge, funnel_percent.
The funnel UDF outputs an array of longs showing conversion rates across the provided funnels.
The funnel_merge UDF merges multiple arrays of longs by adding them together.
The funnel_percent UDF takes a raw count funnel result and converts it to a percent change count.
There is no need to sort the data on timestamp, the UDF will take care of it. If there is a collision in the timestamps, it then sorts on the action column.
Builds a funnel report applied to the action_column, sorted by the timestamp_column.
The funnels are scalars or arrays of the same type as the action column. This allows for multiple matches to move to the next funnel.
For example, funnel_1 could be array('register_button', 'facebook_invite_register'). The funnel will match the first occurence of either of these actions and proceed to the next funnel.
Or, funnel_1 could just be 'register_button'.
You can have an arbitrary number of funnels.
The timestamp_column can be of any comparable type (Strings, Integers, Dates, etc).
funnel_merge
funnel_merge(funnel_column)
Merges funnels. Use with funnel UDF.
funnel_percent
funnel_percent(funnel_column)
Converts the result of a funnel_merge to percent change. Use with funnel and funnel_merge UDF.
For example, a result from funnel_merge could look like [245, 110, 54, 13]. This is result is in raw counts. If we pass this through funnel_percent then it would look like [1.0, 0.44, 0.49, 0.24].
Examples
Assume a table user_data:
action
timestamp
user_id
gender
signup_page
100
1
f
confirm_button
200
1
f
submit_button
300
1
f
signup_page
200
2
m
submit_button
400
2
m
signup_page
100
3
f
confirm_button
200
3
f
decline
200
3
f
...
...
...
...
Simple funnel
SELECT funnel_merge(funnel)
FROM (SELECT funnel(action, timestamp, array('signup_page', 'email_signup'),
'confirm_button',
'submit_button') AS funnel
FROM user_data
GROUP BY user_id) t1;
Result: [3, 2, 1]
Simple funnel with percent
SELECT funnel_percent(funnel_merge(funnel))
FROM (SELECT funnel(action, timestamp, 'signup_page',
'confirm_button',
'submit_button') AS funnel
FROM user_data
GROUP BY user_id) t1;
Result: [1.0, 0.66, 0.5]
Funnel with multiple groups
SELECT gender, funnel_merge(funnel)
FROM (SELECT gender,
funnel(action, timestamp, 'signup_page',
'confirm_button',
'submit_button') AS funnel
FROM table
GROUP BY user_id, gender) t1
GROUP BY gender;
Result: m: [1, 0, 0], f: [2, 2, 1]
Multiple parallel funnels
SELECT funnel_merge(funnel1), funnel_merge(funnel2)
FROM (SELECT funnel(action, timestamp, 'signup_page',
'confirm_button',
'submit_button') AS funnel1
funnel(action, timestamp, 'signup_page',
'decline') AS funnel2
FROM table
GROUP BY user_id) t1;
distance - which calculates the distance between to strings based on selected algorithm (e.g Levenstein, Jaro Winkler, NGramDistance, etc.).
suggestion - based on a text based dictionary.
clean - clean text from whitspaces and other characters.
urlextractor - extract first url match from text
classifier - classify text based on a trainings set (naive bayes classifier)
Hive configuration
First you must build the JAR.
mvn package
Start the Hive CLI and add the hive-udf-textmining-1.0-SNAPSHOT.jar to the Hive class path.
hive
ADD JAR /home/dwh/projects/hive-udf/target/hive-udf-textmining-1.0-SNAPSHOT-jar-with-dependencies.jar;
CREATE TEMPORARY FUNCTION distance as 'ch.yax.hive.udf.text.Distance';
CREATE TEMPORARY FUNCTION suggestion as 'ch.yax.hive.udf.text.Suggestion';
CREATE TEMPORARY FUNCTION clean as 'ch.yax.hive.udf.text.Clean';
CREATE TEMPORARY FUNCTION urlextractor as 'ch.yax.hive.udf.text.UrlExtractor';
CREATE TEMPORARY FUNCTION classifier as 'ch.yax.hive.udf.text.TextClassifier';
CREATE TEMPORARY FUNCTION timestamp as 'ch.yax.hive.udf.number.Timestamp';
CREATE TEMPORARY FUNCTION increment as 'ch.yax.hive.udf.number.AutoIncrement';
create a table dummy and a file dual.txt with value ‘X’. The load the file into the table.
CREATE TABLE DUAL (text STRING);
LOAD DATA LOCAL INPATH '/data/dual.txt' OVERWRITE INTO TABLE DUAL;
You can now execute the query to calculate the Levenshtein distance between two strings.
SELECT distance("L", "my text", "me text") FROM DUAL;
Or for the Jaro–Winkler distance
SELECT distance("J", "my text", "me text") FROM DUAL;
Or the suggestions function which returns the best match for "football" in the file "/tmp/sports.txt" based on the Levenshtein distance.
ADD FILE /data/sport.txt;
SELECT suggestion("L", "i love football", "/data/sport.txt") FROM DUAL;
This query should return FOOTBALL. You can also add the threshold a value from 0.0 to 1.0 and the minimum token length.
SELECT suggestion("L", "i love foot", "/data/sport.txt", 0.5, 4) FROM DUAL;
strategy: the algorithm which should be used for calculating the distance. L = LEVENSTEIN, J = JAROWINKLER or N2 = BIGRAM
target: string to compare
file: a file with suggestions which should be returned when they matched.
returns: the string from the file in upper-case which has the best match with the target string or 'UNKNOW' when not match was found. As default minimum token length is 4 and match must be equal or better than a threshold 0.85.
