You are on page 1of 6

We Do Hadoop  

Cheat Sheet 1 Creating Functions

Hive Functions 2
Mathematical, String, Date, Collection, Text, Conditional
Built-In Aggregates, Built-In Table-Generating Functions
HiveQL provides big data querying capabilities in Hadoop in a dialect very similar to SQL and should be
familiar to anyone used to working with SQL databases. This cheat sheet covers more advanced capabilities
of Hive – specifically creating and using User Defined Functions (UDFs) in Hive, and some of the built in
Additional Resources
Learn to become fluent in Apache Hive with the Hive Language Manual:
Try out Qubole Data Service (QDS) for free:

Get in the Hortonworks Sandbox and try out Hadoop with interactive tutorials:

Call: 855-HADOOP-HELP Twitter:


udf.UDF.udf.}   public  class  YourGenericUDTFName  extends  GenericUDTF  {.".jar.   Call: 855-HADOOP-HELP Twitter: twitter.   At the end of each session:   DROP  TEMPORARY  FUNCTION  IF  EXISTS  YourUDFName.YourUDFName'.  explode()   Macros   A  function  that  users  other  Hive  functions   How To Develop UDFs package  org.          extended  =  "Example:\n"                            +  "    >  SELECT  _FUNC_(InputDataType)  FROM  tablename.apache.ql..          value  =  "_FUNC_(InputDataType)  -­‐  using  the  input  datatype  X   .example. Generic UDFs..text.")   public  class  YourUDFName  extends  UDF{   .   import  java.g..}   How To Deploy/Drop UDFs At start of each session:   ADD  JAR  /full_path_to_jar/YourUDFName.hadoop.apache.      public  YourUDFName(  InputDataType  InputValue  ){          .Date.hadoop.contrib.util..      }      public  String  evaluate(  InputDataType  InputValue  ){          .hadoop...  "+                          "returns  YYY.   CREATE  TEMPORARY  FUNCTION  YourUDFName  AS  'org.hive.SimpleDateFormat.  col2)   UDAF   Aggregates  column  values  in  multiple  rows  and  returns  a  single  value  e. We Do Hadoop   Hive Function Meta Commands SHOW  FUNCTIONS   Lists  Hive  functions  and  operators   DESCRIBE  FUNCTION  [function  name]   Displays  short  description  of  the  function   DESCRIBE  FUNCTION  EXTENDED  [function  name]   Access  extended  description  of  the  function   Types of Hive Functions UDF   A  function  that  takes  one  or  more  columns  from  a  row  as  argument  and  returns  a  single  value  or  object   e.  concat(col1.   import  org.   import  java.apache.   @Description(name  =  "YourUDFName".      }   }   How To Develop Web: qubole.exec.contrib. UDAFs and UDTFs public  class  YourUDFName  extends  UDF{   public  class  YourGenericUDFName  extends  GenericUDF  {.hive.g.  sum(c1)   UDTF   Takes  zero  or  more  inputs  and  produces  multiple  columns  or  rows  of  output  e.hive..}   public  class  YourGenericUDAFName  extends  AbstractGenericUDAFResolver  {.

 int  to_base).  Specifiying  the  seed  will  make  sure  the  generated   random  number  sequence  is  deterministic.  double  b)   Returns  the  positive  value  of  a  mod  b double   sin(double  a)   Returns  the  sine  of  a  (a  is  in  radians) double   asin(double  a)   Returns  the  arc  sin  of  x  if  -­‐1<=a<=1  or  null  otherwise double   cos(double  a)   Returns  the  cosine  of  a  (a  is  in  radians) double   acos(double  a)   Returns  the  arc  cosine  of  x  if  -­‐1<=a<=1  or  null  otherwise double   tan(double  a)   Returns  the  tangent  of  a  (a  is  in  radians) double   atan(double  a)   Returns  the  arctangent  of  a double   degrees(double  a)   Converts  value  of  a  from  radians  to  degrees double   radians(double  a)   Converts  value  of  a  from  degrees  to  radians int  double   positive(int  a).  double  p).  int  d)   Returns  the  double  rounded  to  d  decimal  places bigint   floor(double  a)   Returns  the  maximum  BIGINT  value  that  is  equal  or  less  than  the  double bigint   ceil(double  a).  conv(STRING   Converts  a  number  from  a  given  base  to  another num.  rand(int  seed)   Returns  a  random  number  (that  changes  from  row  to  row)  that  is  distributed   uniformly  from  0  to  1.  double  p)   Return  a double   sqrt(double  a)   Returns  the  square  root  of  a string   bin(BIGINT  a)   Returns  the  number  in  binary  format string   hex(BIGINT  a)  hex(string  a)   If  the  argument  is  an  int.  it  converts  each  character  into  its  hex   representation  and  returns  the  resulting  string.  power(double  a.  ceiling(double  a)   Returns  the  minimum  BIGINT  value  that  is  equal  or  greater  than  the  double double   rand().  double  a)   Return  the  base  "base"  logarithm  of  the  argument p double   pow(double  a. a double   exp(double  a)   Returns  e  where  e  is  the  base  of  the  natural  logarithm double   ln(double  a)   Returns  the  natural  logarithm  of  the  argument double   log10(double  a)   Returns  the  base-­‐10  logarithm  of  the  argument double   log2(double  a)   Returns  the  base-­‐2  logarithm  of  the  argument double   log(double  base. string   unhex(string  a)   Inverse  of Web: qubole.  hex  returns  the  number  as  a  string  in  hex  format.  int  b)  pmod(double  a.  negative(double  a)   Returns  -­‐a     Call: 855-HADOOP-HELP Twitter: twitter.  int  from_base. We Do Hadoop   Mathematical Functions   Return Type Name (Signature) Description bigint   round(double  a)   Returns  the  rounded  BIGINT  value  of  the  double double   round(double  a.  int  to_base)   double   abs(double  a)   Returns  the  absolute  value int  double   pmod(int   . string   conv(BIGINT  num.  Interprets  each  pair  of  characters  as  a  hexidecimal  number  and   converts  to  the  character  represented  by  the  number.  int  from_base.   Otherwise  if  the  number  is  a  string.  positive(double  a)   Returns  a int  double   negative(int  a).

 delimiter2])   Splits  text  into  key-­‐value  pairs  using  two  delimiters.9. We Do Hadoop   String Functions   Return Type Name (Signature) Description int   ascii(string  str)   Returns  the  numeric  value  of  the  first  character  of  str string   concat(string|binary  A.g.  2)  returns  'bar.  right-­‐padded  with  pad  to  a  length  of  len string   rtrim(string  A)   Returns  the  string  resulting  from  trimming  spaces  from  the  end(right  hand  side)  of  A  e.  string  partToExtract   Returns  the  specified  part  from  the  URL.0)   string   trim(string  A)   Returns  the  string  resulting  from  trimming  spaces  from  both  ends  of  A  e.   regexp_replace("foobar".  int  len)   len  e.'   int  double   pmod(int  a. string   printf(String  format.  string  substr)   Returns  the  position  of  the  first  occurence  of  substr  in  str int   length(string  A)   Returns  the  length  of  the  string int   locate(string  substr.  array<string>)   Like  concat_ws()  above.  int  pos])   Returns  the  position  of  the  first  occurrence  of  substr  in  str  after  position  pos string   lower(string  A)  lcase(string  A)   Returns  the  string  resulting  from  converting  all  characters  of  B  to  lower  case  e.  string  str[.  See  docs/api/java/util/regex/Matcher.  int  start.0) int   find_in_set(string  str.  string  pattern. string   concat_ws(string  SEP.  etc.  args)   Returns  the  input  formatted  according  do  printf-­‐style  format  strings  (as  of  Hive  0.g.  string   Returns  the  string  resulting  from  replacing  all  substrings  in  INITIAL_STRING  that  match  the   PATTERN.  trim('  foobar  ')   results  in  'foobar'   string   upper(string  A)  ucase(string  A)   Returns  the  string  resulting  from  converting  all  characters  of  A  to  upper  case  e.0) string   get_json_object(string  json_string.   upper('fOoBaR')  results  in  'FOOBAR'   Call: 855-HADOOP-HELP Twitter: twitter.string>   str_to_map(text[.  QUERY.  concat('foo'.   rtrim('  foobar  ')  results  in  '  foobar' array<array<string sentences(string  str.  FILE.  find_in_set('ab'.g. string   concat_ws(string  SEP.  (as  of  Hive  0.  int  start)   Returns  the  substring  or  slice  of  the  byte  array  of  A  starting  from  start  position  till  the  end   substring(string|binary  A.*?)(bar)'.  and  Delimiter2  splits  each  K-­‐V  pair.  4)  results  in  'bar'   string   substr(string|binary  A.  Note  that  this  function  can   take  any  number  of  input  strings.###.  int  start)   of  string  A  e.  "oo|ar".  This  is  similar  to  the  translatefunction   in  PostgreSQL.  'abc.  string  A. boolean   in_file(string  str.###.  substr('foobar'.  the  result  has  no  decimal  point  or  fractional  part.  double  b)   s'  is  necessary  to  match  whitespace.ab.  string   Tokenizes  a  string  of  natural  language  text  into  words  and  sentences.  e.  string  B.  int  d)   Formats  the  number  X  to  a  format  like  '#.  rounded  to  D  decimal  places.   [.  1)  results  in  'b'   string   translate(string  input.  e.  delimiter1.g.  e.  string  filename)   Returns  true  if  the  string  str  appears  as  an  entire  line  in  filename.  string  lang.   lower('fOoBaR')  results  in  'foobar' string   lpad(string  str.10.'  for  delimiter1  and  '='  for   delimiter2. Web: qubole.  but  with  custom  separator  SEP. string   regexp_replace(string  INITIAL_STRING.  left-­‐padded  with  pad  to  a  length  of  len string   ltrim(string  A)   Returns  the  string  resulting  from  trimming  spaces  from  the  beginning(left  hand  side)  of  A   e. string   substr(string|binary  A.  Returns  0  if  the  first  argument  contains  any   commas.g.  4.  and   returns  the  result  as  a  string.0) string   regexp_extract(string  subject.  and  USERINFO.def')  returns  3 string   format_number(number  x..  int  n)   Repeat  str  n  times string   reverse(string  A)   Returns  the  reversed  string string   rpad(string  str.  "")  returns  'fb.##'.  string  keyToExtract])   PATH.  string  path)   Extract  json  object  from  a  json  string  based  on  json  path  specified.  REF.)   Like  concat()  above. int   instr(string  str.   int  index)   'foo(.g.  string  strList)   Returns  the  first  occurance  of  str  in  strList  where  strList  is  a  comma-­‐delimited  string.  string|binary  B. string   space(int  n)   Return  a  string  of  n  spaces array   split(string  str.10.  If  D  is  0.  It  will  return  null  if  the  input  json  string  is  invalid.  AUTHORITY.  int  len.g.  int  start.g.9.html  for  more  information   on  the  'index'  or  Java  regex  group()  method.  string  to)   Translates  the  input  string  by  replacing  the  characters  present  in  the  from  string  with  the   corresponding  characters  in  the  to  string.  If  any  of  the  parameters  to  this  UDF  are  NULL.g.  int  len)   Returns  the  substring  or  slice  of  the  byte  array  of  A  starting  from  start  position  with  length   substring(string|binary  A.  The  'index'  parameter  is  the  Java  regex  Matcher   group()  method  index..  Valid  values  for  partToExtract  include  HOST.  int  b)  pmod(double  a.b.)   Returns  the  string  or  bytes  resulting  from  concatenating  the  strings  or  bytes  passed  in  as   parameters  in  order.  and  return  json  string  of   the  extracted  json  object.  the  result  is  NULL  as  well   (available  as  of  Hive  0.  etc.  string  pat)   Split  str  around  pat  (pat  is  a  regular  expression) map<string.  Delimiter1  separates  text  into  K-­‐V   .  string  pad)   Returns  str.c.  but  taking  an  array  of  strings.   Returns  the  string  extracted  using  the  pattern.  Default  delimiters  are  '.' double   asin(double  a)   s'  is  necessary  to  match  whitespace.  ltrim('  foobar  ')  results  in  'foobar  ' string   parse_url(string  urlString.  PROTOCOL.   Returns  null  if  either  argument  is  null.  string  pad)   Returns  str.  string  REPLACEMENT)   java  regular  expression  syntax  defined  in  PATTERN  with  instances  of  REPLACEMENT....  int  len.g.  Obj.  where  each  sentence   >>   locale)   is  broken  at  the  appropriate  sentence  boundary  and  returned  as  an  array  of  words.  substr('foobar'.  'bar')  results  in  'foobar'. string   repeat(string  str.  string  from..  regexp_extract('foothebar'.  (as   of  Hive  0.g.

 int  pf)   string  of  "context".0)   Text Analytics Functions Return Type Name (Signature) Description array<struct<string.     Returns  the  top-­‐k  N-­‐grams  from  a  set  of  tokenized  sentences.  .       Contextual  n-­‐grams  are  similar  to  n-­‐grams.     N-­‐grams  are  subsequences  of  length  N  drawn  from  a  longer  sequence.  T  v2.  but  allow  you  to  specify  a  'context'  string   around  which  n-­‐grams  are  to  be Web: qubole.  You  could  achieve  the  same  result  by  manually  stripping  sentences  of   non-­‐contextual  content  and  then  passing  them  to  ngrams().  T  valueTrue.)   Return  the  first  v  that  is  not  NULL.  For  example.  such  as  those  returned      int  N.  int  pf)   by  the  sentences()  UDAF...  The  purpose  of   the  ngrams()  UDAF  is  to  find  the  k  most  frequent  n-­‐grams  from  one  or  more   sequences.  or  the  collect()  function  to  analyze  more  general   string  data.  returns  b.  ceiling(double  a)   Returns  the  minimum  BIGINT  value  that  is  equal  or  greater  than  the  double Collection Functions Return Type Name (Signature) Description int   size(Map<K.  returns  valueFalseOrNull   T   valueFalseOrNull)   otherwise T   COALESCE(T  v1.  int  K.  when  c  =  true.   Conditional Functions Return Type Name (Signature) Description if(boolean  testCondition.  int  K.V>)   Returns  an  unordered  array  containing  the  keys  of  the  input  map array<V>   map_values(Map<K.double>>   ngrams(array<array<string>>.V>)   Returns  the  number  of  elements  in  the  map  type int   size(Array<T>)   Returns  the  number  of  elements  in  the  array  type array<K>   map_keys(Map<K.  you  can  specify  that  you're   only  interested  in  finding  the  most  common  two-­‐word  phrases  in  text  that  follow  the   context  "I  love".   Returns  the  top-­‐k  contextual  N-­‐grams  from  a  set  of  tokenized  sentences.  return  d. We Do Hadoop   Date Functions Return Type Name (Signature) Description BIGINT   round(double  a)   Returns  the  rounded  BIGINT  value  of  the  double DOUBLE   round(double  a.  else  return  e Call: 855-HADOOP-HELP Twitter: twitter.  See  StatisticsAndDataMining  for  more  information.  else  return  f T   CASE  WHEN  a  THEN  b  [WHEN  c  THEN  d]*  [ELSE  e]  END   When  a  =   .   array<struct<string.V>)   Returns  an  unordered  array  containing  the  values  of  the  input  map boolean   array_contains(Array<T>.  returns  c.  It  can  be  used  in  conjunction  with  the  sentences()  UDF  to  analyze   unstructured  natural  language  text.9.  return  e.  when  a  =  d.  or  NULL  if  all  v's  are  NULL T   CASE  a  WHEN  b  THEN  c  [WHEN  d  THEN  e]*  [ELSE  f]  END   When  a  =  b.double>>   context_ngrams(array<array<string>>.  int  d)   Returns  the  double  rounded  to  d  decimal  places BIGINT   floor(double  a)   Returns  the  maximum  BIGINT  value  that  is  equal  or  less  than  the  double BIGINT   ceil(double  a).  but  context_ngrams()   makes  it  much  easier.  value)   Returns  TRUE  if  the  array  contains  value   Sorts  the  input  array  in  ascending  order  according  to  the  natural  ordering  of   array<t>   sort_array(Array<T>)   the  array  elements  and  returns  it  (as  of  version  0.  T   Return  valueTrue  when  testCondition  is  true.  given  a        array<string>.

 count(DISTINCT  expr[.  p  must  be  between  0  and  1.  UDTF's  can   be  used  in  the  SELECT  expression  list  and  as  a  part  of  LATERAL  VIEW..  B])   1 2 Same  as  above.  p ].  var_pop(col)   Returns  the  variance  of  a  numeric  column  in  the  group   double   var_samp(col)   Returns  the  unbiased  sample  variance  of  a  numeric  column  in  the  group   double   stddev_pop(col)   Returns  the  standard  deviation  of  a  numeric  column  in  the  group   double   stddev_samp(col)   Returns  the  unbiased  sample  standard  deviation  of  a  numeric  column  in  the   group   double   covar_pop(col1.   array<double>   percentile(BIGINT  col.  including  rows   containing  NULL Web: qubole.  p  must  be  between  0  and  1..  The  B  parameter  controls  approximation  accuracy   at  the  cost  of  memory.  p ].Eg:       An  example  use  of  explode()  in  the  SELECT  expression  list  is  as  follows:  Consider  a  table  named  myTable  that   has  a  single  column  (myCol)  and  two  rows:   Array<int>  myCol   [1.  col2)   Returns  the  population  covariance  of  a  pair  of  numeric  columns  in  the  group   double   covar_samp(col1.  count(expr).  b)   Computes  a  histogram  of  a  numeric  column  in  the  group  using  b  non-­‐ {'x'.))   Returns  the  exact  percentiles  p .  avg(DISTINCT  col)   Returns  the  average  of  the  elements  in  the  group  or  the  average  of  the   distinct  values  of  the  column  in  the  group double   min(col)   Returns  the  minimum  of  the  column  in  the  group double   max(col)   Returns  the  maximum  value  of  the  column  in  the  group   double   variance(col).STRUCT]>)   Explode   explode()  takes  in  an  array  as  an  input  and  outputs  the  elements  of  the  array  as  separate  rows.])   count(*)  -­‐  Returns  the  total  number  of  retrieved  rows.  col2)   Returns  the  Pearson  coefficient  of  correlation  of  a  pair  of  a  numeric  columns   in  the  group   th double   percentile(BIGINT  col.'y'}>   uniformly  spaced  bins.   array<double>   percentile_approx(DOUBLE  col.  p  [.  p)   Returns  the  exact  p  percentile  of  a  column  in  the  group  (does  not  work  with   floating  point  types).   array<struct   histogram_numeric(col... We Do Hadoop   Built-In Aggregate Functions (UDAF) Return Type Name (Signature) Description bigint   count(*).  .  array(p  [.  p .y)   coordinates  that  represent  the  bin  centers  and  heights   array   collect_set(col)   Returns  a  set  of  objects  with  duplicate  elements  eliminated   Built-In Table-Generating Functions (UDTF) Return Type Name Description (Signature) inline(ARRAY<STRUCT[.com/try   ..2]   [4]   Then  running  the  query:  SELECT  explode(myCol)  AS  myNewCol  FROM  myTable  will  produce:   (int)  myNewCol   1   2   4 Call: 855-HADOOP-HELP Twitter: twitter.  The  output  is  an  array  of  size  b  of  double-­‐valued  (x.  array(p  [.  but  accepts  and  returns  an  array  of  percentile  values  instead   of  a  single  one.  sum(DISTINCT  col)   Returns  the  sum  of  the  elements  in  the  group  or  the  sum  of  the  distinct  values   of  the  column  in  the  group double   avg(col).)  [.  B])   Returns  an  approximate  p  percentile  of  a  numeric  column  (including  floating   point  types)  in  the  group.   i th double   percentile_approx(DOUBLE  col.  count(expr) double   sum(col).  expr_.  col2)   Returns  the  sample  covariance  of  a  pair  of  a  numeric  columns  in  the  group   double   corr(col1.  of  a  column  in  the  group  (does  not   1 2 1 2 work  with  floating  point  types).