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:


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

 power(double  a.  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.  Interprets  each  pair  of  characters  as  a  hexidecimal  number  and   converts  to  the  character  represented  by  the  number.  conv(STRING   Converts  a  number  from  a  given  base  to  another num.  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).  int  to_base).  int  b)  pmod(double  a.  it  converts  each  character  into  its  hex   representation  and  returns  the  resulting  string. 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   conv(BIGINT  num.  double  p).  int  to_base)   double   abs(double  a)   Returns  the  absolute  value int  double   pmod(int  a.  hex  returns  the  number  as  a  string  in  hex  format.   Otherwise  if  the  number  is  a  string.  ceiling(double  a)   Returns  the  minimum  BIGINT  value  that  is  equal  or  greater  than  the  double double   rand().  negative(double  a)   Returns  -­‐a     Call: 855-HADOOP-HELP Twitter: twitter. 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.  positive(double  a)   Returns  a int  double   negative(int  a).  double  a)   Return  the  base  "base"  logarithm  of  the  argument p double   pow(double   .  int  from_base.  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).com/hortonworks Web: qubole.  int  from_base.  rand(int  seed)   Returns  a  random  number  (that  changes  from  row  to  row)  that  is  distributed   uniformly  from  0  to  1. string   unhex(string  a)   Inverse  of  hex.

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

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

.  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).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[.])   count(*)  -­‐  Returns  the  total  number  of  retrieved  rows.))   Returns  the  exact  percentiles  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.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.  UDTF's  can   be  used  in  the  SELECT  expression  list  and  as  a  part  of  LATERAL  VIEW.  expr_..  p  must  be  between  0  and  1.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.  p  [.  p ].  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).   i th double   percentile_approx(DOUBLE  col.  of  a  column  in  the  group  (does  not   1 2 1 2 work  with  floating  point  types).  B])   1 2 Same  as  above..   array<double>   percentile(BIGINT  col.  p)   Returns  the  exact  p  percentile  of  a  column  in  the  group  (does  not  work  with   floating  point  types).  col2)   Returns  the  population  covariance  of  a  pair  of  numeric  columns  in  the  group   double   covar_samp(col1.   array<struct   histogram_numeric(col.  but  accepts  and  returns  an  array  of  percentile  values  instead   of  a  single  one.   array<double>   percentile_approx(DOUBLE  col. We Do Hadoop   Built-In Aggregate Functions (UDAF) Return Type Name (Signature) Description bigint   count(*).  p  must  be  between  0  and  1.  p .  b)   Computes  a  histogram  of  a  numeric  column  in  the  group  using  b  non-­‐ {'x'.  col2)   Returns  the  sample  covariance  of  a  pair  of  a  numeric  columns  in  the  group   double   corr(col1.  array(p  [..  including  rows   containing  NULL  values.  .  count(expr).  col2)   Returns  the  Pearson  coefficient  of  correlation  of  a  pair  of  a  numeric  columns   in  the  group   th double   percentile(BIGINT  col.  count(DISTINCT  expr[.)  [.com/hortonworks Web: qubole.  The  B  parameter  controls  approximation  accuracy   at  the  cost  of  memory.  The  output  is  an  array  of  size  b  of  double-­‐valued  (   .STRUCT]>)   Explode   explode()  takes  in  an  array  as  an  input  and  outputs  the  elements  of  the  array  as  separate  rows..  array(p  [.  count(expr) double   sum(col).'y'}>   uniformly  spaced  bins.  B])   Returns  an  approximate  p  percentile  of  a  numeric  column  (including  floating   point  types)  in  the  group.