六、Hive Sql 大全

本節(jié)基本涵蓋了Hive日常使用的所有SQL,因為SQL太多,所以將SQL進(jìn)行了如下分類:一、DDL語句(數(shù)據(jù)定義語句):
對數(shù)據(jù)庫的操作:包含創(chuàng)建、修改數(shù)據(jù)庫
對數(shù)據(jù)表的操作:分為內(nèi)部表及外部表,分區(qū)表和分桶表
二、DQL語句(數(shù)據(jù)查詢語句):
單表查詢、關(guān)聯(lián)查詢
hive函數(shù):包含聚合函數(shù),條件函數(shù),日期函數(shù),字符串函數(shù)等
行轉(zhuǎn)列及列轉(zhuǎn)行:lateral view 與 explode 以及 reflect
窗口函數(shù)與分析函數(shù)
其他一些窗口函數(shù)

hive的DDL語法對數(shù)據(jù)庫的操作創(chuàng)建數(shù)據(jù)庫:create database if not exists myhive;
說明:hive的表存放位置模式是由hive-site．xml當(dāng)中的一個屬性指定的 :hive．metastore．warehouse．dir
創(chuàng)建數(shù)據(jù)庫并指定hdfs存儲位置 :
create database myhive2 location '/myhive2';
修改數(shù)據(jù)庫:alter  database  myhive2  set  dbproperties('createtime'='20210329');

說明:可以使用alter  database 命令來修改數(shù)據(jù)庫的一些屬性。但是數(shù)據(jù)庫的元數(shù)據(jù)信息是不可更改的,包括數(shù)據(jù)庫的名稱以及數(shù)據(jù)庫所在的位置

查看數(shù)據(jù)庫詳細(xì)信息查看數(shù)據(jù)庫基本信息
hive (myhive)> desc  database  myhive2;
查看數(shù)據(jù)庫更多詳細(xì)信息
hive (myhive)> desc database extended  myhive2;
刪除數(shù)據(jù)庫刪除一個空數(shù)據(jù)庫,如果數(shù)據(jù)庫下面有數(shù)據(jù)表,那么就會報錯
drop  database  myhive2;
強(qiáng)制刪除數(shù)據(jù)庫,包含數(shù)據(jù)庫下面的表一起刪除
drop  database  myhive  cascade;
對數(shù)據(jù)表的操作對管理表(內(nèi)部表)的操作:建內(nèi)部表:hive (myhive)> use myhive; -- 使用myhive數(shù)據(jù)庫
hive (myhive)> create table stu(id int,name string);
hive (myhive)> insert into stu values (1,"zhangsan");
hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多條數(shù)據(jù)
hive (myhive)> select * from stu;
hive建表時候的字段類型:分類類型描述字面量示例原始類型BOOLEANtrue/falseTRUE
TINYINT1字節(jié)的有符號整數(shù) -128～1271Y
SMALLINT2個字節(jié)的有符號整數(shù),-32768～327671S
INT4個字節(jié)的帶符號整數(shù)1
BIGINT8字節(jié)帶符號整數(shù)1L
FLOAT4字節(jié)單精度浮點(diǎn)數(shù)1．0
DOUBLE8字節(jié)雙精度浮點(diǎn)數(shù)1．0
DEICIMAL任意精度的帶符號小數(shù)1．0
STRING字符串,變長“a”,’b’
VARCHAR變長字符串“a”,’b’
CHAR固定長度字符串“a”,’b’
BINARY字節(jié)數(shù)組無法表示
TIMESTAMP時間戳,毫秒值精度122327493795
DATE日期‘2016-03-29’
INTERVAL時間頻率間隔
復(fù)雜類型ARRAY有序的的同類型的集合array(1,2)
MAPkey-value,key必須為原始類型,value可以任意類型map(‘a(chǎn)’,1,’b’,2)
STRUCT字段集合,類型可以不同struct(‘1’,1,1．0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1．0)
UNION在有限取值范圍內(nèi)的一個值create_union(1,’a’,63)

對decimal類型簡單解釋下:
用法:decimal(11,2) 代表最多有11位數(shù)字,其中后2位是小數(shù),整數(shù)部分是9位;如果整數(shù)部分超過9位,則這個字段就會變成null;如果小數(shù)部分不足2位,則后面用0補(bǔ)齊兩位,如果小數(shù)部分超過兩位,則超出部分四舍五入
也可直接寫 decimal,后面不指定位數(shù),默認(rèn)是 decimal(10,0)  整數(shù)10位,沒有小數(shù)

創(chuàng)建表并指定字段之間的分隔符create  table if not exists stu2(id int ,name string) row format delimited fields terminated by ' ' stored as textfile location '/user/stu2';

row format delimited fields terminated by ' '  指定字段分隔符,默認(rèn)分隔符為 '
'
stored as 指定存儲格式
location 指定存儲位置

根據(jù)查詢結(jié)果創(chuàng)建表create table stu3 as select * from stu2;
根據(jù)已經(jīng)存在的表結(jié)構(gòu)創(chuàng)建表create table stu4 like stu2;
查詢表的結(jié)構(gòu)只查詢表內(nèi)字段及屬性
desc stu2;
詳細(xì)查詢
desc formatted  stu2;
查詢創(chuàng)建表的語句show create table stu2;
對外部表操作

外部表因為是指定其他的hdfs路徑的數(shù)據(jù)加載到表當(dāng)中來,所以hive表會認(rèn)為自己不完全獨(dú)占這份數(shù)據(jù),所以刪除hive表的時候,數(shù)據(jù)仍然存放在hdfs當(dāng)中,不會刪掉,只會刪除表的元數(shù)據(jù)

構(gòu)建外部表create external table student (s_id string,s_name string) row format delimited fields terminated by ' ';
從本地文件系統(tǒng)向表中加載數(shù)據(jù)追加操作
load data local inpath '/export/servers/hivedatas/student．csv' into table student;
覆蓋操作
load data local inpath '/export/servers/hivedatas/student．csv' overwrite  into table student;
從hdfs文件系統(tǒng)向表中加載數(shù)據(jù)load data inpath '/hivedatas/techer．csv' into table techer;
加載數(shù)據(jù)到指定分區(qū)
load data inpath '/hivedatas/techer．csv' into table techer partition(cur_date=20201210);
注意:
1．使用 load data local 表示從本地文件系統(tǒng)加載,文件會拷貝到hdfs上
2．使用 load data 表示從hdfs文件系統(tǒng)加載,文件會直接移動到hive相關(guān)目錄下,注意不是拷貝過去,因為hive認(rèn)為hdfs文件已經(jīng)有3副本了,沒必要再次拷貝了
3．如果表是分區(qū)表,load 時不指定分區(qū)會報錯
4．如果加載相同文件名的文件,會被自動重命名對分區(qū)表的操作創(chuàng)建分區(qū)表的語法create table score(s_id string, s_score int) partitioned by (month string);
創(chuàng)建一個表帶多個分區(qū)create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

注意:
hive表創(chuàng)建的時候可以用 location 指定一個文件或者文件夾,當(dāng)指定文件夾時,hive會加載文件夾下的所有文件,當(dāng)表中無分區(qū)時,這個文件夾下不能再有文件夾,否則報錯
當(dāng)表是分區(qū)表時,比如 partitioned by (day string), 則這個文件夾下的每一個文件夾就是一個分區(qū),且文件夾名為 day=20201123這種格式,然后使用:msck  repair   table  score; 修復(fù)表結(jié)構(gòu),成功之后即可看到數(shù)據(jù)已經(jīng)全部加載到表當(dāng)中去了

加載數(shù)據(jù)到一個分區(qū)的表中l(wèi)oad data local inpath '/export/servers/hivedatas/score．csv' into table score partition (month='201806');
加載數(shù)據(jù)到一個多分區(qū)的表中去load data local inpath '/export/servers/hivedatas/score．csv' into table score2 partition(year='2018',month='06',day='01');
查看分區(qū)show  partitions  score;
添加一個分區(qū)alter table score add partition(month='201805');
同時添加多個分區(qū) alter table score add partition(month='201804') partition(month = '201803');

注意:添加分區(qū)之后就可以在hdfs文件系統(tǒng)當(dāng)中看到表下面多了一個文件夾

刪除分區(qū) alter table score drop partition(month = '201806');
對分桶表操作

將數(shù)據(jù)按照指定的字段進(jìn)行分成多個桶中去,就是按照分桶字段進(jìn)行哈希劃分到多個文件當(dāng)中去
分區(qū)就是分文件夾,分桶就是分文件

分桶優(yōu)點(diǎn):
1． 提高join查詢效率
2． 提高抽樣效率

開啟hive的捅表功能set hive．enforce．bucketing=true;
設(shè)置reduce的個數(shù)set mapreduce．job．reduces=3;
創(chuàng)建桶表create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

桶表的數(shù)據(jù)加載:由于桶表的數(shù)據(jù)加載通過hdfs  dfs  -put文件或者通過load  data均不可以,只能通過insert  overwrite 進(jìn)行加載
所以把文件加載到桶表中,需要先創(chuàng)建普通表,并通過insert  overwrite的方式將普通表的數(shù)據(jù)通過查詢的方式加載到桶表當(dāng)中去

通過insert  overwrite給桶表中加載數(shù)據(jù)insert overwrite table course select * from course_common cluster by(c_id);  -- 最后指定桶字段
修改表和刪除表修改表名稱alter  table  old_table_name  rename  to  new_table_name;
增加/修改列信息查詢表結(jié)構(gòu)
desc score5;
添加列
alter table score5 add columns (mycol string, mysco string);
更新列
alter table score5 change column mysco mysconew int;
刪除表操作drop table score5;
清空表操作truncate table score6;
說明:只能清空管理表,也就是內(nèi)部表;清空外部表,會產(chǎn)生錯誤

注意:truncate 和 drop:
如果 hdfs 開啟了回收站,drop 刪除的表數(shù)據(jù)是可以從回收站恢復(fù)的,表結(jié)構(gòu)恢復(fù)不了,需要自己重新創(chuàng)建;truncate 清空的表是不進(jìn)回收站的,所以無法恢復(fù)truncate清空的表
所以 truncate 一定慎用,一旦清空將無力回天

向hive表中加載數(shù)據(jù)直接向分區(qū)表中插入數(shù)據(jù)insert into table score partition(month ='201807') values ('001','002','100');
通過load方式加載數(shù)據(jù) load data local inpath '/export/servers/hivedatas/score．csv' overwrite into table score partition(month='201806');
通過查詢方式加載數(shù)據(jù)insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;
查詢語句中創(chuàng)建表并加載數(shù)據(jù)create table score2 as select * from score1;
在創(chuàng)建表是通過location指定加載數(shù)據(jù)的路徑create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';
export導(dǎo)出與import 導(dǎo)入 hive表數(shù)據(jù)(內(nèi)部表操作)create table techer2 like techer; --依據(jù)已有表結(jié)構(gòu)創(chuàng)建表
export table techer to  '/export/techer';
import table techer2 from '/export/techer';
hive表中數(shù)據(jù)導(dǎo)出insert導(dǎo)出將查詢的結(jié)果導(dǎo)出到本地
insert overwrite local directory '/export/servers/exporthive' select * from score;
將查詢的結(jié)果格式化導(dǎo)出到本地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by ' ' collection items terminated by '#' select * from student;
將查詢的結(jié)果導(dǎo)出到HDFS上(沒有l(wèi)ocal)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by ' ' collection items terminated by '#' select * from score;
Hadoop命令導(dǎo)出到本地dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local．txt;
hive shell 命令導(dǎo)出基本語法:(hive -f/-e 執(zhí)行語句或者腳本 > file)
hive -e "select * from myhive．score;" > /export/servers/exporthive/score．txt
hive -f export．sh > /export/servers/exporthive/score．txt
export導(dǎo)出到HDFS上export table score to '/export/exporthive/score';
hive的DQL查詢語法單表查詢SELECT [ALL | DISTINCT] select_expr, select_expr, ．．．
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
 | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

注意:
1、order by 會對輸入做全局排序,因此只有一個reducer,會導(dǎo)致當(dāng)輸入規(guī)模較大時,需要較長的計算時間。
2、sort by不是全局排序,其在數(shù)據(jù)進(jìn)入reducer前完成排序。因此,如果用sort by進(jìn)行排序,并且設(shè)置mapred．reduce．tasks>1,則sort by只保證每個reducer的輸出有序,不保證全局有序。
3、distribute by(字段)根據(jù)指定的字段將數(shù)據(jù)分到不同的reducer,且分發(fā)算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外,還會對該字段進(jìn)行排序。
因此,如果分桶和sort字段是同一個時,此時,cluster by = distribute by + sort by

WHERE語句select * from score where s_score < 60;

注意:
小于某個值是不包含null的,如上查詢結(jié)果是把 s_score 為 null 的行剔除的

GROUP BY 分組select s_id ,avg(s_score) from score group by s_id;
分組后對數(shù)據(jù)進(jìn)行篩選,使用having
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;

注意:
如果使用 group by 分組,則 select 后面只能寫分組的字段或者聚合函數(shù)
where和having區(qū)別:
1 having是在 group by 分完組之后再對數(shù)據(jù)進(jìn)行篩選,所以having 要篩選的字段只能是分組字段或者聚合函數(shù)
2 where 是從數(shù)據(jù)表中的字段直接進(jìn)行的篩選的,所以不能跟在gruopby后面,也不能使用聚合函數(shù)

join 連接INNER JOIN 內(nèi)連接:只有進(jìn)行連接的兩個表中都存在與連接條件相匹配的數(shù)據(jù)才會被保留下來
select * from techer t [inner] join course c on t．t_id = c．t_id; -- inner 可省略
LEFT OUTER JOIN 左外連接:左邊所有數(shù)據(jù)會被返回,右邊符合條件的被返回
select * from techer t left join course c on t．t_id = c．t_id; -- outer可省略
RIGHT OUTER JOIN 右外連接:右邊所有數(shù)據(jù)會被返回,左邊符合條件的被返回、
select * from techer t right join course c on t．t_id = c．t_id;
FULL OUTER JOIN 滿外(全外)連接: 將會返回所有表中符合條件的所有記錄。如果任一表的指定字段沒有符合條件的值的話,那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t．t_id = c．t_id ;

注:1． hive2版本已經(jīng)支持不等值連接,就是 join on條件后面可以使用大于小于符號了;并且也支持 join on 條件后跟or (早前版本 on 后只支持 = 和 and,不支持 > < 和 or)
2．如hive執(zhí)行引擎使用MapReduce,一個join就會啟動一個job,一條sql語句中如有多個join,則會啟動多個job

注意:表之間用逗號(,)連接和 inner join 是一樣的
select * from table_a,table_b where table_a．id=table_b．id;
它們的執(zhí)行效率沒有區(qū)別,只是書寫方式不同,用逗號是sql 89標(biāo)準(zhǔn),join 是sql 92標(biāo)準(zhǔn)。用逗號連接后面過濾條件用 where ,用 join 連接后面過濾條件是 on。

order by 排序全局排序,只會有一個reduce
ASC(ascend): 升序(默認(rèn)) DESC(descend): 降序
SELECT * FROM student s LEFT JOIN score sco ON s．s_id = sco．s_id ORDER BY sco．s_score DESC;

注意:order by 是全局排序,所以最后只有一個reduce,也就是在一個節(jié)點(diǎn)執(zhí)行,如果數(shù)據(jù)量太大,就會耗費(fèi)較長時間

sort by 局部排序每個MapReduce內(nèi)部進(jìn)行排序,對全局結(jié)果集來說不是排序。
設(shè)置reduce個數(shù)
set mapreduce．job．reduces=3;
查看設(shè)置reduce個數(shù)
set mapreduce．job．reduces;
查詢成績按照成績降序排列
select * from score sort by s_score;

將查詢結(jié)果導(dǎo)入到文件中(按照成績降序排列)
insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;
distribute by  分區(qū)排序distribute by:類似MR中partition,進(jìn)行分區(qū),結(jié)合sort by使用
設(shè)置reduce的個數(shù),將我們對應(yīng)的s_id劃分到對應(yīng)的reduce當(dāng)中去
set mapreduce．job．reduces=7;
通過distribute by  進(jìn)行數(shù)據(jù)的分區(qū)
select * from score distribute by s_id sort by s_score;

注意:Hive要求 distribute by 語句要寫在 sort by 語句之前

cluster by當(dāng)distribute by和sort by字段相同時,可以使用cluster by方式．
cluster by除了具有distribute by的功能外還兼具sort by的功能。但是排序只能是正序排序,不能指定排序規(guī)則為ASC或者DESC。
以下兩種寫法等價
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;
Hive函數(shù)聚合函數(shù)hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函數(shù)

注意:
聚合操作時要注意null值
count(*) 包含null值,統(tǒng)計所有行數(shù)
count(id) 不包含null值
min 求最小值是不包含null,除非所有值都是null
avg 求平均值也是不包含null

非空集合總體變量函數(shù): var_pop語法: var_pop(col)
返回值: double
說明: 統(tǒng)計結(jié)果集中col非空集合的總體變量(忽略null)
非空集合樣本變量函數(shù): var_samp語法: var_samp (col)
返回值: double
說明: 統(tǒng)計結(jié)果集中col非空集合的樣本變量(忽略null)
總體標(biāo)準(zhǔn)偏離函數(shù): stddev_pop語法: stddev_pop(col)
返回值: double
說明: 該函數(shù)計算總體標(biāo)準(zhǔn)偏離,并返回總體變量的平方根,其返回值與VAR_POP函數(shù)的平方根相同
中位數(shù)函數(shù): percentile語法: percentile(BIGINT col, p)
返回值: double
說明: 求準(zhǔn)確的第pth個百分位數(shù),p必須介于0和1之間,但是col字段目前只支持整數(shù),不支持浮點(diǎn)數(shù)類型
關(guān)系運(yùn)算支持:等值(=)、不等值(!= 或 )、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)
空值判斷(is null)、非空判斷(is not null)
LIKE比較: LIKE語法: A LIKE B
操作類型: strings
描述: 如果字符串A或者字符串B為NULL,則返回NULL;如果字符串A符合表達(dá)式B 的正則語法,則為TRUE;否則為FALSE。B中字符”_”表示任意單個字符,而字符”%”表示任意數(shù)量的字符。
JAVA的LIKE操作: RLIKE語法: A RLIKE B
操作類型: strings
描述: 如果字符串A或者字符串B為NULL,則返回NULL;如果字符串A符合JAVA正則表達(dá)式B的正則語法,則為TRUE;否則為FALSE。
REGEXP操作: REGEXP語法: A REGEXP B
操作類型: strings
描述: 功能與RLIKE相同
示例:select 1 from tableName where 'footbar' REGEXP '^f．*r$';
結(jié)果:1
數(shù)學(xué)運(yùn)算支持所有數(shù)值類型:加(+)、減(-)、乘(*)、除(/)、取余(%)、位與(&)、位或(|)、位異或(^)、位取反(～)
邏輯運(yùn)算支持:邏輯與(and)、邏輯或(or)、邏輯非(not)
數(shù)值運(yùn)算取整函數(shù): round語法: round(double a)
返回值: BIGINT
說明: 返回double類型的整數(shù)值部分 (遵循四舍五入)
示例:select round(3．1415926) from tableName;
結(jié)果:3
指定精度取整函數(shù): round語法: round(double a, int d)
返回值: DOUBLE
說明: 返回指定精度d的double類型
hive> select round(3．1415926,4) from tableName;
3．1416
向下取整函數(shù): floor語法: floor(double a)
返回值: BIGINT
說明: 返回等于或者小于該double變量的最大的整數(shù)
hive> select floor(3．641) from tableName;
3
向上取整函數(shù): ceil語法: ceil(double a)
返回值: BIGINT
說明: 返回等于或者大于該double變量的最小的整數(shù)
hive> select ceil(3．1415926) from tableName;
4
取隨機(jī)數(shù)函數(shù): rand語法: rand(),rand(int seed)
返回值: double
說明: 返回一個0到1范圍內(nèi)的隨機(jī)數(shù)。如果指定種子seed,則會等到一個穩(wěn)定的隨機(jī)數(shù)序列
hive> select rand() from tableName; -- 每次執(zhí)行此語句得到的結(jié)果都不同
0．5577432776034763
hive> select rand(100) ;  -- 只要指定種子,每次執(zhí)行此語句得到的結(jié)果一樣的
0．7220096548596434
自然指數(shù)函數(shù): exp語法: exp(double a)
返回值: double
說明: 返回自然對數(shù)e的a次方
hive> select exp(2) ;
7．38905609893065
以10為底對數(shù)函數(shù): log10語法: log10(double a)
返回值: double
說明: 返回以10為底的a的對數(shù)
hive> select log10(100) ;
2．0

此外還有:以2為底對數(shù)函數(shù): log2()、對數(shù)函數(shù): log()

冪運(yùn)算函數(shù): pow語法: pow(double a, double p)
返回值: double
說明: 返回a的p次冪
hive> select pow(2,4) ;
16．0
開平方函數(shù): sqrt語法: sqrt(double a)
返回值: double
說明: 返回a的平方根
hive> select sqrt(16) ;
4．0
二進(jìn)制函數(shù): bin語法: bin(BIGINT a)
返回值: string
說明: 返回a的二進(jìn)制代碼表示
hive> select bin(7) ;
111

十六進(jìn)制函數(shù): hex()、將十六進(jìn)制轉(zhuǎn)化為字符串函數(shù): unhex()
進(jìn)制轉(zhuǎn)換函數(shù): conv(bigint num, int from_base, int to_base) 說明: 將數(shù)值num從from_base進(jìn)制轉(zhuǎn)化到to_base進(jìn)制

此外還有很多數(shù)學(xué)函數(shù):絕對值函數(shù): abs()、正取余函數(shù): pmod()、正弦函數(shù): sin()、反正弦函數(shù): asin()、余弦函數(shù): cos()、反余弦函數(shù): acos()、positive函數(shù): positive()、negative函數(shù): negative()

條件函數(shù)If函數(shù): if語法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
說明: 當(dāng)條件testCondition為TRUE時,返回valueTrue;否則返回valueFalseOrNull
hive> select if(1=2,100,200) ;
200
hive> select if(1=1,100,200) ;
100
非空查找函數(shù): coalesce語法: coalesce(T v1, T v2, …)
返回值: T
說明: 返回參數(shù)中的第一個非空值;如果所有值都為NULL,那么返回NULL
hive> select coalesce(null,'100','50') ;
100
條件判斷函數(shù):case when (兩種寫法,其一)語法: case when a then b [when c then d]* [else e] end
返回值: T
說明:如果a為TRUE,則返回b;如果c為TRUE,則返回d;否則返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary
條件判斷函數(shù):case when (兩種寫法,其二)語法: case a when b then c [when d then e]* [else f] end
返回值: T
說明:如果a等于b,那么返回c;如果a等于d,那么返回e;否則返回f
hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary
日期函數(shù)

注:以下SQL語句中的 from tableName 可去掉,不影響查詢結(jié)果

獲取當(dāng)前UNIX時間戳函數(shù): unix_timestamp語法: unix_timestamp()
返回值: bigint
說明: 獲得當(dāng)前時區(qū)的UNIX時間戳
hive> select unix_timestamp() from tableName;
1616906976
UNIX時間戳轉(zhuǎn)日期函數(shù): from_unixtime語法: from_unixtime(bigint unixtime[, string format])
返回值: string
說明: 轉(zhuǎn)化UNIX時間戳(從1970-01-01 00:00:00 UTC到指定時間的秒數(shù))到當(dāng)前時區(qū)的時間格式
hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;
20210328
日期轉(zhuǎn)UNIX時間戳函數(shù): unix_timestamp語法: unix_timestamp(string date)
返回值: bigint
說明: 轉(zhuǎn)換格式為"yyyy-MM-dd HH:mm:ss"的日期到UNIX時間戳。如果轉(zhuǎn)化失敗,則返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15') from tableName;
1615184475
指定格式日期轉(zhuǎn)UNIX時間戳函數(shù): unix_timestamp語法: unix_timestamp(string date, string pattern)
返回值: bigint
說明: 轉(zhuǎn)換pattern格式的日期到UNIX時間戳。如果轉(zhuǎn)化失敗,則返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;
1615184475
日期時間轉(zhuǎn)日期函數(shù): to_date語法: to_date(string timestamp)
返回值: string
說明: 返回日期時間字段中的日期部分。
hive> select to_date('2021-03-28 14:03:01') from tableName;
2021-03-28
日期轉(zhuǎn)年函數(shù): year語法: year(string date)
返回值: int
說明: 返回日期中的年。
hive> select year('2021-03-28 10:03:01') from tableName;
2021
hive> select year('2021-03-28') from tableName;
2021
日期轉(zhuǎn)月函數(shù): month語法: month (string date)
返回值: int
說明: 返回日期中的月份。
hive> select month('2020-12-28 12:03:01') from tableName;
12
hive> select month('2021-03-08') from tableName;
8
日期轉(zhuǎn)天函數(shù): day語法: day (string date)
返回值: int
說明: 返回日期中的天。
hive> select day('2020-12-08 10:03:01') from tableName;
8
hive> select day('2020-12-24') from tableName;
24
日期轉(zhuǎn)小時函數(shù): hour語法: hour (string date)
返回值: int
說明: 返回日期中的小時。
hive> select hour('2020-12-08 10:03:01') from tableName;
10
日期轉(zhuǎn)分鐘函數(shù): minute語法: minute (string date)
返回值: int
說明: 返回日期中的分鐘。
hive> select minute('2020-12-08 10:03:01') from tableName;
3
日期轉(zhuǎn)秒函數(shù): second語法: second (string date)
返回值: int
說明: 返回日期中的秒。
hive> select second('2020-12-08 10:03:01') from tableName;
1
日期轉(zhuǎn)周函數(shù): weekofyear語法: weekofyear (string date)
返回值: int
說明: 返回日期在當(dāng)前的周數(shù)。
hive> select weekofyear('2020-12-08 10:03:01') from tableName;
49
日期比較函數(shù): datediff語法: datediff(string enddate, string startdate)
返回值: int
說明: 返回結(jié)束日期減去開始日期的天數(shù)。
hive> select datediff('2020-12-08','2012-05-09') from tableName;
213
日期增加函數(shù): date_add語法: date_add(string startdate, int days)
返回值: string
說明: 返回開始日期startdate增加days天后的日期。
hive> select date_add('2020-12-08',10) from tableName;
2020-12-18
日期減少函數(shù): date_sub語法: date_sub (string startdate, int days)
返回值: string
說明: 返回開始日期startdate減少days天后的日期。
hive> select date_sub('2020-12-08',10) from tableName;
2020-11-28
字符串函數(shù)
字符串長度函數(shù):length語法: length(string A)
返回值: int
說明:返回字符串A的長度
hive> select length('abcedfg') from tableName;
7
字符串反轉(zhuǎn)函數(shù):reverse語法: reverse(string A)
返回值: string
說明:返回字符串A的反轉(zhuǎn)結(jié)果
hive> select reverse('abcedfg') from tableName;
gfdecba
字符串連接函數(shù):concat語法: concat(string A, string B…)
返回值: string
說明:返回輸入字符串連接后的結(jié)果,支持任意個輸入字符串
hive> select concat('abc','def’,'gh')from tableName;
abcdefgh
帶分隔符字符串連接函數(shù):concat_ws語法: concat_ws(string SEP, string A, string B…)
返回值: string
說明:返回輸入字符串連接后的結(jié)果,SEP表示各個字符串間的分隔符
hive> select concat_ws(',','abc','def','gh')from tableName;
abc,def,gh
字符串截取函數(shù):substr,substring語法: substr(string A, int start),substring(string A, int start)
返回值: string
說明:返回字符串A從start位置到結(jié)尾的字符串
hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive> select substr('abcde',-1) from tableName; (和ORACLE相同)
e
字符串截取函數(shù):substr,substring語法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
說明:返回字符串A從start位置開始,長度為len的字符串
hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de
字符串轉(zhuǎn)大寫函數(shù):upper,ucase語法: upper(string A) ucase(string A)
返回值: string
說明:返回字符串A的大寫格式
hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED
字符串轉(zhuǎn)小寫函數(shù):lower,lcase語法: lower(string A) lcase(string A)
返回值: string
說明:返回字符串A的小寫格式
hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed
去空格函數(shù):trim語法: trim(string A)
返回值: string
說明:去除字符串兩邊的空格
hive> select trim(' abc ') from tableName;
abc
左邊去空格函數(shù):ltrim語法: ltrim(string A)
返回值: string
說明:去除字符串左邊的空格
hive> select ltrim(' abc ') from tableName;
abc
右邊去空格函數(shù):rtrim語法: rtrim(string A)
返回值: string
說明:去除字符串右邊的空格
hive> select rtrim(' abc ') from tableName;
abc
正則表達(dá)式替換函數(shù):regexp_replace語法: regexp_replace(string A, string B, string C)
返回值: string
說明:將字符串A中的符合java正則表達(dá)式B的部分替換為C。注意,在有些情況下要使用轉(zhuǎn)義字符,類似oracle中的regexp_replace函數(shù)。
hive> select regexp_replace('foobar', 'oo|ar', '') from tableName;
fb
正則表達(dá)式解析函數(shù):regexp_extract語法: regexp_extract(string subject, string pattern, int index)
返回值: string
說明:將字符串subject按照pattern正則表達(dá)式的規(guī)則拆分,返回index指定的字符。
hive> select regexp_extract('foothebar', 'foo(．*?)(bar)', 1) from tableName;
the
hive> select regexp_extract('foothebar', 'foo(．*?)(bar)', 2) from tableName;
bar
hive> select regexp_extract('foothebar', 'foo(．*?)(bar)', 0) from tableName;
foothebar
strong>注意,在有些情況下要使用轉(zhuǎn)義字符,下面的等號要用雙豎線轉(zhuǎn)義,這是java正則表達(dá)式的規(guī)則。
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;
URL解析函數(shù):parse_url語法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
說明:返回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')
from tableName;
www．tableName．com
hive> select parse_url
('https://www．tableName．com/path1/p．php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
from tableName;
v1
json解析函數(shù):get_json_object語法: get_json_object(string json_string, string path)
返回值: string
說明:解析json的字符串json_string,返回path指定的內(nèi)容。如果輸入的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') from tableName;
空格字符串函數(shù):space語法: space(int n)
返回值: string
說明:返回長度為n的字符串
hive> select space(10) from tableName;
hive> select length(space(10)) from tableName;
10
重復(fù)字符串函數(shù):repeat語法: repeat(string str, int n)
返回值: string
說明:返回重復(fù)n次后的str字符串
hive> select repeat('abc',5) from tableName;
abcabcabcabcabc
首字符ascii函數(shù):ascii語法: ascii(string str)
返回值: int
說明:返回字符串str第一個字符的ascii碼
hive> select ascii('abcde') from tableName;
97
左補(bǔ)足函數(shù):lpad語法: lpad(string str, int len, string pad)
返回值: string
說明:將str進(jìn)行用pad進(jìn)行左補(bǔ)足到len位
hive> select lpad('abc',10,'td') from tableName;
tdtdtdtabc
注意:與GP,ORACLE不同,pad 不能默認(rèn)
右補(bǔ)足函數(shù):rpad語法: rpad(string str, int len, string pad)
返回值: string
說明:將str進(jìn)行用pad進(jìn)行右補(bǔ)足到len位
hive> select rpad('abc',10,'td') from tableName;
abctdtdtdt
分割字符串函數(shù): split語法: split(string str, string pat)
返回值: array
說明: 按照pat字符串分割str,會返回分割后的字符串?dāng)?shù)組
hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]
集合查找函數(shù): find_in_set語法: find_in_set(string str, string strList)
返回值: int
說明: 返回str在strlist第一次出現(xiàn)的位置,strlist是用逗號分割的字符串。如果沒有找該str字符,則返回0
hive> select find_in_set('ab','ef,ab,de') from tableName;
2
hive> select find_in_set('at','ef,ab,de') from tableName;
0
復(fù)合類型構(gòu)建操作Map類型構(gòu)建: map語法: map (key1, value1, key2, value2, …)
說明:根據(jù)輸入的key和value對構(gòu)建map類型
hive> Create table mapTable as select map('100','tom','200','mary') as t from tableName;
hive> describe mapTable;
t       map