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

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

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.