Cloud computing big data learning route course syllabus material: hive internal function
Today, I would like to share some cloud computing big data learning route course outline information with you. This article is some learning notes about hive internal functions. I hope I can give you some help:
hive internal function
1. Take random number function: rand()
Syntax: rand(),rand(int seed) Return value: double Remarks: Returns a random number in the range 0 to 1. If you specify seed, you get a stable sequence of random numbers.
select rand();
select rand(10);
Split(str, splitter)
Syntax: split(string str, string pat) Return value: array Remarks: Split str according to pat string, it will return the split string array, pay attention to the escape of special separators
select split(5.0,"\. ")[0];
select split(rand(10)*100,"\. ")[0];
3. String interception function: substr,substring
Syntax: substr(string A, int start),substring(string A, int start) Return value: string Description: Returns the string A from the start position to the end of the string
Syntax: substr(string A, int start, int len),substring(string A, int start, int len) Return value: string Description: Returns a string A of length len starting at start position
select substr(rand()*100,0,2);
select substring(rand()*100,0,2);
4. If function:if
Syntax: if(boolean testCondition, T valueTrue, T valueFalseOrNull) Return value: T Description: Returns valueTrue when testCondition is TRUE; otherwise returns valueFalseOrNull
select if(100>10,"this is true","this is false");
select if(2=1,"male","female");
select if(1=1,"male",(if(1=2,"female","don't know")));
select if(3=1,"male",(if(3=2,"female","don't know")));
5. Conditional judgment function: CASE
First format:
Syntax: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END Return value: T Description: b if a is TRUE; d if c is TRUE; e otherwise
Second format:
Syntax: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END Return value: T Description: If a is equal to b, then return c; if a is equal to d, then return e; otherwise return f
select
case 6
when 1 then "100"
when 2 then "200"
when 3 then "300"
when 4 then "400"
else "others"
end
;
##Creating tables
create table if not exists cw(
flag int
)
;
load data local inpath '/home/flag' into table cw;
##The first format
select
case c.flag
when 1 then "100"
when 2 then "200"
when 3 then "300"
when 4 then "400"
else "others"
end
from cw c
;
##The second format
select
case
when 1=c.flag then "100"
when 2=c.flag then "200"
when 3=c.flag then "300"
when 4=c.flag then "400"
else "others"
end
from cw c
;
Regular expression replacement function: regexp_replace
Syntax: regexpreplace(string A, string B, string C) Return value: string Description: Replace the part of string A that matches java regular expression B with C. Note that in some cases escape characters are used, similar to the regexreplace function in oracle
select regexp_replace("1.jsp",".jsp",".html");
7. Type conversion function: cast
Syntax: cast(expr as ) Return value: Expected "=" to follow "type" Remarks: Returns a converted data type
select 1;
select cast(1 as double);
select cast("12" as int);
8. String concatenation function: concat; delimited string concatenation function: concat_ws
Syntax: concat(string A, string B…) Return value: string Description: Returns the result of concatenation of input strings, supports any input strings
Syntax: concat_ws(string SEP, string A, string B…) Return value: string Description: Returns the result of concatenation of input strings, SEP represents the separator between strings
select "Qianfeng" + 1603 + "Class";
select concat("Thousand Peaks",1603,"Class");
select concat_ws("|"," Qianfeng ","1603"," Class ");
9. Ranking function:
rownumber(): not tied rank(): tied but empty denserank(): tied but empty
##Data
id class score
1 1 90
2 1 85
3 1 87
4 1 60
5 2 82
6 2 70
7 2 67
8 2 88
9 2 93
1 1 90 1
3 1 87 2
2 1 85 3
9 2 93 1
8 2 88 2
5 2 82 3
create table if not exists uscore(
uid int,
classid int,
score double
)
row format delimited fields terminated by '\t'
;
load data local inpath '/home/uscore' into table uscore;
select
u.uid,
u.classid,
u.score
from uscore u
group by u.classid,u.uid,u.score
limit 3
;
select
u.uid,
u.classid,
u.score,
row_number() over(distribute by u.classid sort by u.score desc) rn
from uscore u
;
Take the top three.
select
t.uid,
t.classid,
t.score
from
(
select
u.uid,
u.classid,
u.score,
row_number() over(distribute by u.classid sort by u.score desc) rn
from uscore u
) t
where t.rn < 4
;
See three ranking differences
select
u.uid,
u.classid,
u.score,
row_number() over(distribute by u.classid sort by u.score desc) rn,
rank() over(distribute by u.classid sort by u.score desc) rank,
dense_rank() over(distribute by u.classid sort by u.score desc) dr
from uscore u
;
10. Aggregate function:
min() max() count() count(distinct ) sum() avg()
count(1): accumulate 1 count(*): accumulate 1 count(col): accumulate 1 count(distinct col): accumulate 1 count
11. null value operation
Almost any number and NULL operation returns NULL
select 1+null;
select 1/0;
select null%2;
12. equivalent operation
select null=null; #null
select nullnull;#true
