Common functions of hive
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.