Hive学习-数据查询语句

A RLIKE B

JAVA的LIKE

如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；如果字符串A不符合JAVA正则表达式B的正则语法，则为FALSE。

count(*)

所有的不包含null值的行数

包含null值

count(id)

统计所有行数

不包含null值

max()

求最大值

不包含null，除非所有值都是null

min()

求最小值

不包含null，除非所有值都是null

avg()

求平均值

不包含null

var_pop(col)

非空集合总体变量函数

忽略null

double

var_samp (col)

非空集合样本变量函数

忽略null

double

stddev_pop(col)

该函数计算总体标准偏离

并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同

double

percentile(BIGINT col, p)

求准确的第pth个百分位数

p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型

double

=

等于

TRUE|FALSE

!= 或 <>

不等于

TRUE|FALSE

<

小于

TRUE|FALSE

<=

小于等于

TRUE|FALSE

>

大于

TRUE|FALSE

>=

大于等于

TRUE|FALSE

is null

空值判断

TRUE|FALSE

is not null

非空判断

TRUE|FALSE

A LIKE B

LIKE比较

如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；如果字符串A不符合表达式B 的正则语法，否则为FALSE。B中字符'_'表示任意单个字符，而字符'%'表示任意数量的字符。

NULL|TRUE|FALSE

A RLIKE B

JAVA的LIKE

如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；如果字符串A不符合JAVA正则表达式B的正则语法，则为FALSE。

NULL|TRUE|FALSE

A REGEXP B

功能与RLIKE相同

SELECT 1 FROM tablenamee WHERE 'footbar' REGEXP '^f.*r$';

NULL|TRUE|FALSE

加

-

减

*

乘

/

除

%

取余

&

位与

|

位或

^

位异或

~

位取反

and

逻辑与

or

逻辑或

not

逻辑非

round(double a)

取整函数

返回double类型的整数值部分 （遵循四舍五入）hive>SELECT round(3.1415926) FROM tablenamee; 3

BIGINT

round(double a, int d)

指定精度取整函数

返回指定精度d的double类型hive>SELECT round(3.1415926,4) FROM tablenamee;3.1416

DOUBLE

floor(double a)

向下取整函数

返回等于或者小于该double变量的最大的整数hive>SELECT floor(3.641) FROM tablenamee;3

BIGINT

ceil(double a)

向上取整函数

返回等于或者大于该double变量的最小的整数 hive>SELECT ceil(3.1415926) FROM tablenamee;4

BIGINT

rand(),rand(int seed)

取随机数函数

返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列hive>SELECT rand() FROM tablenamee; -- 每次执行此语句得到的结果都不同0.5577432776034763hive>SELECT rand(100) ; -- 只要指定种子，每次执行此语句得到的结果一样的0.7220096548596434

DOUBLE

exp(double a)

自然指数函数

返回自然对数e的a次方hive>SELECT exp(2) ;7.38905609893065

DOUBLE

log10(double a)

以10为底对数函数

hive>返回以10为底的a的对数SELECT log10(100) ;2.0

DOUBLE

log2()

以2为底对数函数

log()

对数函数

pow(double a, double p)

幂运算函数

返回a的p次幂hive> SELECT pow(2,4) ;16.0

DOUBLE

sqrt(double a)

开平方函数

返回a的平方根hive> SELECT sqrt(16) ;4.0

DOUBLE

bin(BIGINT a)

二进制函数

返回a的二进制代码表示hive> SELECT bin(7) ;111

string

hex()

十六进制函数

unhex()

将十六进制转化为字符串函数

conv(bigint num, int FROM_base, int to_base)

进制转换函数

将数值num从FROM_base进制转化到to_base进制

abs()

绝对值函数

pmod()

正取余函数

sin()

正弦函数

asin()

反正弦函数

cos()

余弦函数

acos()

反余弦函数

positive()

positive函数

negative()

negative函数

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

If函数

当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNullhive> SELECT if(1=2,100,200) ;200hive> SELECT if(1=1,100,200) ;100

T

coalesce(T"v1,"T"v2,"…)

非空查找函数

返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULLhive> SELECT coalesce(null,'100','50') ;100

T

case when a then b [when c then d]* [else e] end

条件判断函数(两种写法，其一)

如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e hive> SELECT case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end FROM tablenamee; mary

T

case a when b then c [when d then e]* [else f] end

条件判断函数(两种写法，其二)

如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回fhive> SELECT case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end FROM tablenamee; mary

T

unix_timestamp()

获得当前时区的UNIX时间戳

hive> SELECT unix_timestamp() 1616906976

bigint

FROM_unixtime(bigint unixtime[, string format])

转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

hive> SELECT FROM_unixtime(1616906976,'yyyyMMdd') ;20210328

string

unix_timestamp(string date, string pattern)

转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

hive> SELECT unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') ;1615184475

bigint

to_date(string timestamp)

返回日期时间字段中的日期部分

hive> SELECT to_date('2021-03-28 14:03:01'); 2021-03-28

string

year(string date)

返回日期中的年

hive> SELECT year('2021-03-28 10:03:01'); 2021SELECT year('2021-03-28'); 2021

int

month (string date)

返回日期中的月份

hive> SELECT month('2020-12-28 12:03:01') ;12hive> SELECT month('2021-03-08') ;8

int

day (string date)

返回日期中的天

hive> SELECT day('2020-12-08 10:03:01') ;8SELECT day('2020-12-24') ;24

int

hour (string date)

返回日期中的小时

hive> SELECT hour('2020-12-08 10:03:01') ;10

int

minute (string date)

返回日期中的分钟

hive> SELECT minute('2020-12-08 10:03:01') ; 3

int

second (string date)

返回日期中的秒

hive> SELECT second('2020-12-08 10:03:01') ;1

int

weekofyear (string date)

返回日期在当年的周数

hive> SELECT weekofyear('2020-12-08 10:03:01');49

int

datediff(string enddate, string startdate)

返回结束日期减去开始日期的天数

hive> SELECT datediff('2020-12-08','2020-05-09') ;213

Int

date_add(string startdate, int days)

返回开始日期startdate增加days天后的日期

hive> SELECT date_add('2020-12-08',10) ;2020-12-18

string

date_sub (string startdate, int days)

返回开始日期startdate减少days天后的日期

hive> SELECT date_sub('2020-12-08',10) ;2020-11-28

string

unix_timestamp(string date)

转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。

hive> SELECT unix_timestamp('2021-03-08 14:21:15') ;1615184475

bigint

length(string A)

返回字符串A的长度

hive> SELECT length('abcedfg');7

int

reverse(string A)

返回字符串A的反转结果

hive> SELECT reverse('abcedfg') ;gfdecba

string

concat(string A, string B…)

返回输入字符串连接后的结果，支持任意个输入字符串

hive> SELECT concat('abc','def','gh') ; abcdefgh

string

concat_ws(string SEP, string A, string B…)

返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

hive> SELECT concat_ws(',','abc','def','gh');gabc,def,gh

string

substr(string A, int start)substring(string A, int start)

返回字符串A从start位置到结尾的字符串

hive> SELECT substr('abcde',3);cdehive> SELECT substring('abcde',3) ;cdehive> SELECT substr('abcde',-1) ;e

string

substr(string A, int start, int len)substring(string A, int start, int len)

返回字符串A从start位置开始，长度为len的字符串

hive> SELECT substr('abcde',3,2) ;cd hive> SELECT substring('abcde',3,2) ;cdhive> SELECT substring('abcde',-2,2) FROM tablename;de

string

upper(string A) ucase(string A)

返回字符串A的大写格式

hive> SELECT upper('abSEd');ABSEDhive> SELECT ucase('abSEd');ABSED

string

lower(string A)lcase(string A)

返回字符串A的小写格式

hive> SELECT lower('abSEd');absedhive> SELECT lcase('abSEd'); absed

string

trim(string A)

去除字符串两边的空格

hive> SELECT trim(' abc ');abc

string

ltrim(string A)

去除字符串左边的空格

hive> SELECT ltrim(' abc ');abc

string

rtrim(string A)

去除字符串右边的空格

hive>SELECT rtrim(' abc '); abc

string

regexp_replace(string A, string B, string C)

将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

hive>SELECT regexp_replace('foobar', 'oo|ar', '');fb

string

regexp_extract(string subject, string pattern, int index)

将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

hive>SELECT regexp_extract('foothebar', 'foo(.*?)(bar)', 1); thehive>SELECT regexp_extract('foothebar', 'foo(.*?)(bar)', 2);barhive>SELECT regexp_extract('foothebar', 'foo(.*?)(bar)', 0);foothebar

String

注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。SELECT data_field,regexp_extract(data_field,'.*?bgStart\=([^&] )',1) as aaa,regexp_extract(data_field,'.*?contentLoaded_headStart\=([^&] )',1) as bbb,regexp_extract(data_field,'.*?AppLoad2Req\=([^&] )',1) as ccc FROM pt_nginx_loginlog_st WHERE pt = '2021-03-28' limit 2;

parse_url(string urlString, string partToExtract [, string keyToExtract])

返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF,PROTOCOL, AUTHORITY, FILE, and USERINFO.

hive>SELECT parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');www.tableName.com hive>SELECT parse_url('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1');v1

get_json_object(string json_string, string path)

解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。

hive>SELECT get_json_object('{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner'); amy

space(int n)

返回长度为n的字符串

hive>SELECT space(10);hive>SELECT length(space(10));10

string

repeat(string str, int n)

返回重复n次后的str字符串

hive>SELECT repeat('abc',5);abcabcabcabcabc

string

ascii(string str)

返回字符串str第一个字符的ascii码

hive>SELECT ascii('abcde');97

int

lpad(string str, int len, string pad)

将str进行用pad进行左补足到len位

hive>SELECT lpad('abc',10,'td');tdtdtdtabc注意：与GP，ORACLE不同，pad 不能默认

string

rpad(string str, int len, string pad)

将str进行用pad进行右补足到len位

hive> SELECT rpad('abc',10,'td');abctdtdtdt

string

split(string str, string pat)

按照pat字符串分割str，会返回分割后的字符串数组

hive>SELECT split('abtcdtef','t');["ab","cd","ef"]

array

find_in_set(string str, string strList)

返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0

hive>SELECT find_in_set('ab','ef,ab,de');2hive>SELECT find_in_set('at','ef,ab,de');0

int

map (key1, value1, key2, value2, …)

根据输入的key和value对构建map类型hive>CREATE table mapTable as SELECT map('100','tom','200','mary') as t FROM tableName;hive> describe mapTable;t maphive> SELECT t FROM tableName;{"100":"tom","200":"mary"}

struct(val1, val2, val3, …)

根据输入的参数构建结构体struct类型hive> CREATE table struct_table as SELECT struct('tom','mary','tim') as t FROM tableName;hive> describe struct_table;t struct hive> SELECT t FROM tableName;{"col1":"tom","col2":"mary","col3":"tim"}

array(val1, val2, …)

根据输入的参数构建数组array类型hive> CREATE table arr_table as SELECT array("tom","mary","tim") as t FROM tableName;hive> describe tableName;t arrayhive> SELECT t FROM tableName;["tom","mary","tim"]

array类型：A[n]

A为array类型n为int类型

返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'hive>CREATE table arr_table2 as SELECT array("tom","mary","tim") as tFROM tableName;hive> SELECT t[0],t[1] FROM arr_table2; tom mary

map类型：M[key]

M为map类型key为map中的key值

返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'hive> Create table map_table2 as SELECT map('100','tom','200','mary') as t FROM tableName;hive> SELECT t['200'],t['100'] FROM map_table2;mary tom

struct类型：S.x

S为struct类型

返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段hive> CREATE table str_table2 as SELECT struct('tom','mary','tim') as t FROM tableName;hive> describe tableName;t structhive> SELECT t.col1,t.col3 FROM str_table2; tom tim

size(Map)

返回map类型的长度hive> SELECT size(t) FROM map_table2;2

int

size(Array)

返回array类型的长度hive> SELECT size(t) FROM arr_table2;4

Int

cast(expr as )

返回转换后的数据类型hive> SELECT cast('1' as bigint) FROM tableName;1

expected "=" to follow "type"