Shulou(Shulou.com)06/03 Report--

View an introduction to the function (required):

Show functions; # View all built-in functions in hive

Desc function extended function name; # View a detailed description of a function

Common built-in functions in 1.hive 1) set functions

  creates an array

   array (ele1,ele2,ele3)

   example: select array (1, 2, 3, 4)

  determines whether the value is in the array.

   array_contains (arr,value)

   example: select array_contains (array (1 ~ 2 ~ 3 ~ 4), 5)

  creates a map (cardinality key, even number value)

   map (key0,value0,key1,value1 …)

   example: select map ('zs',1,'ls',2)

  returns all key in map

   map_keys (map)

   example: select map_keys (map ('zs',1,'ls',2))

  returns all value in map

   map_values (map)

   example: select map_values (map ('zs',1,'ls',2))

2) numerical function

  decimal is rounded:

   round (x, [d]) parameter 1: floating point number, parameter 2: reserved digits

   example: select round (4.5) returns 5

   example: select round (5.1) returns 5 default reserved integer bits

  rounding up:

   celi (num)

   example: ceil (5.1) # 6

  rounded down:

   floor (num)

   example: floor (5.1) # 6

3) string operation

  substr (str,pos,len) # intercepts the string (subscript starts at 1)

   example: select substr ('abcd',1) abcd

   example: select substr ('abcd',1,1) a

   ps:substr and substring are used the same way

  instr (str,substr) # returns the starting position of the substring

   example: instr ('abcd','cd') 3

   example: 0 is returned by default if there is no instr ('abcd',' zy')'

  split (str,regex) # string syncopation, returning an array

   example: select split ('hello world','')

  concat (str1,str2 …) # string concatenation

   example: concat ('ab','cd','ef')' abcdef'

  concat_ws (separarot, [string | array]) # string concatenation

   example: select concat_ws (',', 'ab','cd','ef') #' ab,cd,ef'

Examples of   : select concat_ws (',)).));'a,',',

  case conversion

Lcase / lower # string converted to lowercase

Ucase / upper # string converted to uppercase

  nvl # string judgment

   example: select nvl (value,'delfaut') # if the former is null, return the latter

  if

  syntax: if (expression, return value 1, return value 2)

   example: if (value is null, 'default',value), the expression is true, the return value is 1, otherwise the return value is 2

4) date operation

Unix_timestamp (data,format) # returns the timestamp of the specified date

  example:

  example: select unix_timestamp ('2018-9-1) returns the timestamp of a given date

From_unixtime (timestamp,format) # returns the time of the corresponding timestamp

Example: from_unixtime (1151561)

Year (data) # returns the year of a given date

  example: year ('2018-5-4') # returns 2018

The corresponding functions of   include: month, day, hour, minute, second

Weekofyear (data) # returns the corresponding date, which is the week ordinal of the year

  example: select weekofyear ('2018-5-5')

Datediff (date1,date2) # the number of days between two dates

  example: select datediff ('2018-5-9 June June 2018-5-10')

5) Table generation function

  syntax: explode (a) a can be an array or map that splits an array or map into multiple lines

  example: select explode (array (1 ~ 2 ~ 3))

  example: select explode (map ('zs',1,'ls',2))

Practical application:

# build table sentence create table user_info (name string,info map) row format delimited fields terminated by'\ t 'collection items terminated by', 'map keys terminated by':'

# data format: zs age:28,salary:20000,address:beijing

# generate data: zs age:28zs salary:20000zs address:Beijing# uses table-generated functions to solve: select name,t.* from user_info lateral view explode (info) t

two。 Custom function

Classification of functions in hive:

Here we customize UDF, all the way out.

Step 1: customize Java classes (import hive dependencies, write classes to inherit UDF)

Note: the name of the method must be "atekeeper"!

Package com.zy.mr.hive;import org.apache.hadoop.hive.ql.exec.UDF;public class MyUDF extends UDF {/ * parameter: the parameter is the parameter passed in by the calling function * the return value is the return value of the called function * Note: * 1. The modifier of the method must be public * 2. The returned value cannot be void * 3. The general parameters can not be * / / the sum of three numbers public int evaluate (int num1,int num2,int num3) {return num1+num2+num3;} / / ip digits to complete 192.166.1.1-192.168.001.001 public String evaluate (String ip) {String [] split = ip.split ("\\."); for (int iSum.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.