We Do Hadoop  

 
 
 
Contents
 
Cheat Sheet 1 Creating Functions

Hive Functions 2
3
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
functions
 
 
 
 
 
Additional Resources
 
Learn to become fluent in Apache Hive with the Hive Language Manual:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual
 
 
 
Try out Qubole Data Service (QDS) for free:
http://www.qubole.com/try

Get in the Hortonworks Sandbox and try out Hadoop with interactive tutorials:
http://hortonworks.com/sandbox
 
 
 
 
 

Call: 855-HADOOP-HELP Twitter: twitter.com/hortonworks
Web: qubole.com/try

 

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

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

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

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

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