hive-common operations and functions

Select ' Us ' from File_cto_user_info limit 1
Select 1 from file_cto_user_info where ' football ' like ' foot____ ' limit 1
--Note: Negative comparisons use A like B
Select 1 from File_cto_user_info where isn't ' football ' like ' fff% ' limit 1

--Note: Determine whether a string is all numeric:
Select 1 from file_cto_user_info where ' 123456 ' rlike ' ^\\d+$ ' limit 1
Select 1 from file_cto_user_info where ' footbar ' REGEXP ' ^f.*r$ ' limit 1

--note: The highest precision data type in hive is double, which is only accurate to 16 digits after the decimal point, and you should pay special attention when doing the division operation.
Select Ceil (28.0/6.999999999999999999999) from File_cto_user_info limit 1

--Note: Accuracy is a big problem in hive, such operations are best specified by round
Select round (8.4% 4, 2) from File_cto_user_info limit 1

--Logical non-operation: not
Select 1 from File_cto_user_info where not 1=2 limit 1

--Take down the whole function: floor
Select Floor (3.1415926) from File_cto_user_info limit 1
--Take up the whole function: ceil
Select Ceil (3.1415926) from File_cto_user_info limit 1
Select Ceiling (3.1415926) from File_cto_user_info limit 1

--Random number
Select Rand from File_cto_user_info limit 1

--Open Square
Select sqrt (+) from File_cto_user_info limit 1

--binary function: Bin
Select Bin (7) from File_cto_user_info limit 1

--16 binary function: Hex
Select Hex (' abc ') from File_cto_user_info limit 1

--Inverse hexadecimal function: Unhex
Select Unhex (616263) from File_cto_user_info limit 1

--Binary conversion function: Conv
--Description: Converts numeric num from from_base into to_base
Select CONV (8,10,2) from File_cto_user_info limit 1
Select CONV (111,2,10) from File_cto_user_info limit 1
Select CONV (9,10,5) from File_cto_user_info limit 1

--Absolute value function: ABS
Select ABS ( -3.9) from File_cto_user_info limit 1

--positive take-up function: Pmod
Select Pmod ( -9,4) from File_cto_user_info limit 1

--The opposite number
Select Negative (8) from File_cto_user_info limit 1

--unix Timestamp to date function: From_unixtime
Select From_unixtime (1458814863, ' Yyyy-mm-dd hh:mm:ss ') from File_cto_user_info limit 1
Select From_unixtime (1458814863, ' Yyyy-mm-dd HH:mm:ss ') from File_cto_user_info limit 1
Select From_unixtime (1458814863, ' HH ') from File_cto_user_info limit 1

Select From_unixtime (1426672632, ' Yyyy-mm-dd ') from File_cto_user_info limit 1
Select From_unixtime (CAST (substr (1454285291013,1,10) as bigint), ' YyyyMMdd hh:mm:ss ') from File_cto_user_info limit 1



Select From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd ') from File_cto_user_info limit 1

--Get current UNIX timestamp function: Unix_timestamp
Select Unix_timestamp () from File_cto_user_info limit 1
--Date to time stamp
Select Unix_timestamp (' 2011-12-07 13:01:03 ') from File_cto_user_info limit 1

--Date time to date function: To_date
Select To_date (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1

--Date following year function: Year
Select year (' 2012-12-08 ') from File_cto_user_info limit 1
Select year (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1

--date-to-month function: Month
Select month (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1;
Select month (' 2011-08-08 ') from File_cto_user_info limit 1

--Date The following function: Day
Select Day (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1;
Select Day (' 2011-08-12 ') from File_cto_user_info limit 1

--date-to-hour function: Hour
Select Hour (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1;

Select Concat (Split (' 2011-12-08 10:03:01 ', ') [0], ', concat (Split (' substr 2011-12-08 ', ') [10:03:01] 1],1,2 (' 2011-12-08 10:03:01 ', ') [1],3,6)]
from File_cto_user_info limit 1;

--Date to minute function: minute
Select minute (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1;

--Date to seconds function: second
Select second (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1;

--date-to-week function: WeekOfYear returns the week number of the year in which the specified date is, ranging from 0 to 53.
Select WeekOfYear (' 2011-12-08 10:03:01 ') from File_cto_user_info limit 1;

--the difference between the dates of two time parameters
Select DateDiff (' 2015-11-30 ', ' 2015-11-28 ') from File_cto_user_info limit 1;

--Return weeks
Select Pmod (DateDiff (' 2015-11-09 ', ' 2013-01-07 '), 7) +1 from File_cto_user_info limit 1;

--at a given time, add a specified time period based on the current time.
Select Date_add (' 2015-04-05 10:00:00 ', 0.5) from file_cto_user_info limit 1;
From_unixtime (Unix_timestamp (), ' Yyyy-mm-dd hh:mm:ss ')
--at a given time, subtract the specified time period on this basis
Select Date_sub (' 2015-04-05 ', 5) from File_cto_user_info limit 1;

--Determines whether the condition is satisfied, returns a value if satisfied, and returns another value if it is not satisfied
Select if (1>0, ' condition satisfied ', ' condition is met ') from File_cto_user_info limit 1;

--Returns the first non-null value in a set of data, or null if both are NULL
Select COALESCE (null, ' 1 ', 2, ' 3 ', ', null) from File_cto_user_info limit 1;

--Returns the reverse string
Select reverse (' ABCDEFG ') from File_cto_user_info limit 1;

--Link multiple strings, separating the strings with the specified delimiter.
Select Concat_ws (' @ ', ' 1111 ', ' 2222 ', ' 3333 ') from File_cto_user_info limit 1;

--The B character in string A is replaced by the C character
Select Regexp_replace (' asdc://', '://', ' a ') from File_cto_user_info limit 1;

--Returns the part specified by the regular expression by the subscript.
Select Regexp_extract (' ASDC ', ' A (*) DC ', 1) from File_cto_user_info limit 1;

--Get Host name
Select Parse_url (' Http://www.csdn.net/aaa.txt ', ' HOST ') from File_cto_user_info limit 1;

--Get the parameter value
Select Parse_url (' http://www.csdn.net/aaa.htm?userid=1234 ', ' userid ') from File_cto_user_info limit 1;

--substr
Set Mapred.job.queue.name=hadoop;
Select substr (' 2015-05-04 ', 1,4) from File_cto_user_info limit 1;