Groovy UDF example Can be compiled at run time Currently only works in "hive" shell, does not work in beeline <code>su guest hive paste the following code into the hive shellthis will use Groovy String replace function to replace all instances of lower case 'e' with 'E' <code>compile `import org.apache.hadoop.hive.ql.exec.UDF \; import org.apache.hadoop.io.Text \; public class Replace extends UDF { public Text evaluate(Text s){ if (s == null) return null \; return new Text(s.toString().replace('e', 'E')) \; } } ` AS GROOVY NAMED Replace.groovy; now create a temporary function to leverage the Groovy UDF <code>CREATE TEMPORARY FUNCTION Replace as 'Replace'; now you can use the function in your SQL <code>SELECT Replace(description) FROM sample_08 limit 5; full example <code>hive> compile `import org.apache.hadoop.hive.ql.exec.UDF \; > import org.apache.hadoop.io.Text \; > public class Replace extends UDF { > public Text evaluate(Text s){ > if (s == null) return null \; > return new Text(s.toString().replace('e', 'E')) \; > } > } ` AS GROOVY NAMED Replace.groovy; Added [/tmp/0_1452022176763.jar] to class path Added resources: [/tmp/0_1452022176763.jar] hive> CREATE TEMPORARY FUNCTION Replace as 'Replace'; OK Time taken: 1.201 seconds hive> SELECT Replace(description) FROM sample_08 limit 5; OK All Occupations ManagEmEnt occupations ChiEf ExEcutivEs GEnEral and opErations managErs LEgislators Time taken: 6.373 seconds, Fetched: 5 row(s) hive> Another example this will duplicate any String passed to the function <code>compile `import org.apache.hadoop.hive.ql.exec.UDF \; import org.apache.hadoop.io.Text \; public class Duplicate extends UDF { public Text evaluate(Text s){ if (s == null) return null \; return new Text(s.toString() * 2) \; } } ` AS GROOVY NAMED Duplicate.groovy; CREATE TEMPORARY FUNCTION Duplicate as 'Duplicate'; SELECT Duplicate(description) FROM sample_08 limit 5; All OccupationsAll Occupations Management occupationsManagement occupations Chief executivesChief executives General and operations managersGeneral and operations managers LegislatorsLegislators JSON Parsing UDF <code>compile `import org.apache.hadoop.hive.ql.exec.UDF \; import groovy.json.JsonSlurper \; import org.apache.hadoop.io.Text \; public class JsonExtract extends UDF { public int evaluate(Text a){ def jsonSlurper = new JsonSlurper() \; def obj = jsonSlurper.parseText(a.toString())\; return obj.val1\; } } ` AS GROOVY NAMED json_extract.groovy; CREATE TEMPORARY FUNCTION json_extract as 'JsonExtract'; SELECT json_extract('{"val1": 2}') from date_dim limit 1; 2 ... View more

I’m going to show you a neat way to work with CSV files and Apache Hive. Usually, you’d have to do some preparatory work on CSV data before you can consume it with Hive but I’d like to show you a built-in SerDe (Serializer/Deseriazlier) for Hive that will make it a lot more convenient to work with CSV. This work was merged in Hive 0.14 and there’s no additional steps necessary to work with CSV from Hive. Suppose you have a CSV file with the following entries id first_name last_name email gender ip_address 1 James Coleman jcoleman0@cam.ac.uk Male 136.90.241.52 2 Lillian Lawrence llawrence1@statcounter.com Female 101.177.15.130 3 Theresa Hall thall2@sohu.com Female 114.123.153.64 4 Samuel Tucker stucker3@sun.com Male 89.60.227.31 5 Emily Dixon edixon4@surveymonkey.com Female 119.92.21.19 to consume it from within Hive, you’ll need to upload it to hdfs hdfs dfs -put sample.csv /tmp/serdes/ now all it takes is to create a table schema on top of the file drop table if exists sample; create external table sample(id int,first_name string,last_name string,email string,gender string,ip_address string) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' stored as textfile location '/tmp/serdes/'; now you can query the table as is select * from sample limit 10; but what if your CSV file was tab-delimited rather than comma? well the SerDe got you covered there too: drop table if exists sample; create external table sample(id int,first_name string,last_name string,email string,gender string,ip_address string) row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' with serdeproperties ( "separatorChar" = "\t" ) stored as textfile location '/tmp/serdes/'; notice the separatorChar argument, in all, the SerDe accepts two more arguments; custom escape characters and quote characters Take a look at the wiki for more info https://cwiki.apache.org/confluence/display/Hive/CSV+Serde. ... View more