Hive的hql操作方式

本篇內容介紹了“Hive的hql操作方式”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

前言：
常見hive參數設置：

//Hive 顯示列名/表頭
set hive.cli.print.header=true;
//hive設置本地資源執行
set hive.exec.mode.local.auto=true;
1
2
3
4
1、hive的DDL——data define language數據定義語言
1.1庫的定義語言
1）創建數據庫
create database  if not exists  數據庫名;
1
2）切換數據庫
use  數據庫名；
1
3）查看所有數據庫
show databases;
show databases like "test*";    所有test開頭的數據庫
1
2
4）查看數據庫的描述信息
desc database  數據庫名;
desc database  extended 數據庫名;    //查看數據庫的擴展信息
1
2
5)查看正在使用的數據庫
select current_database();
1
6）修改數據庫——不支持
7）刪除數據庫
drop database [if exists] 數據庫名[restrict];         //只能刪除空數據庫
drop database 數據庫名 cascade；      //級聯刪除非空數據庫
1
2
1.2表的定義語言
1.2.1創建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
 [(col_name data_type [COMMENT col_comment], ...)]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [CLUSTERED BY (col_name, col_name, ...)
 [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
 [ROW FORMAT row_format]
 [STORED AS file_format]
1
2
3
4
5
6
7
8
建表語句說明：
①externel hive中的建表關鍵字
外部表關鍵字，加上后建的表就是外部表，不加默認是內部表
②if not exists 建表防止報錯
if exists 刪表防止報錯
③comment 指定列或表的描述信息
④partitioned by(字段名 字段類型,…)
分區表用于指定分區用的，括號里的是指定分區字段用的，一般是常用過濾字段
注意：分區字段一定不是建表中字段的字段
⑤clustered by (col_name,col_name,…)
sorted by (col_name [ASC | DSC],…) into num_buckets buckets
指定分桶表相關信息
分桶字段——分桶過程中需要按照哪一個字段將大表切成小文件
clustered by（分桶字段）
into num_buckets buckets 指定分桶個數，每一個桶的數據，分桶字段.hash % 分桶個數
sorted by 指定桶中數據的排序
注意：分桶字段一定包含在建表字段中
⑥row format 指定行格式化
hive不支持update、delete，支持insert，但是效率低
hive擅長導入表中數據方式——load，之間將一個文件導入到hive表中
這里的行格式化指定的是文件每一個字段的切分依據，行之間的切分依據
⑦指定存儲格式
指定的是hive表數據在hdfs存儲的文件格式
TEXTFILE 文本——默認
SEQUENCEFILE——二進制
RCFILE——行列結合
⑧location 指定hive的表數據在hdfs的存儲路徑
hive表數據存儲路徑

hive-default.xml hive.metastore.warehouse.dir 默認的
hive-site.xml 修改后的
location hive表存儲路徑
這個表必須在hdfs上，在建表過程中指定
1.2.2創建表案例
1）內部表
create table if not exists stu_managed(
sid int,name string,sex string,age int,dept string
) comment "test one managed_table" 
row format delimited fields terminated by "," 
stored as textfile location "/data/hive/managed/stu";
1
2
3
4
5
2）外部表
create external table if not exists stu_external(
sid int,name string,sex string,age int,dept string
) comment "test one external_table" 
row format delimited fields terminated by "," ;
1
2
3
4
3）分區表
分區字段：dept

create table if not exists stu_partitioned(
sid int,name string,sex string,age int
) comment "test one partitioned_table" 
partitioned by (dept string) row format delimited fields terminated by "," ;
1
2
3
4
4）分桶表
分桶字段：age
分桶分數：3

create table if not exists stu_buk(
sid int,name string,sex string,age int,dept string
) clustered by (age) sorted by (dept desc,age asc) into 3 buckets
row format delimited fields terminated by ",";
1
2
3
4
5）表復制
like
只會復制表的字段信息，不會復制表的屬性（存儲位置、存儲格式、權限）

create [external] table if not exists tbname1 like tbname2;
1
6）ctas建表
將一個sql查詢結果存放到一張表中

create [external] table tbname as select ....
1
1.2.3查看表列表
show tables;
show tables like "stu_*";
show tables in 數據庫名;
查看表的詳細描述信息
desc 表名;                 //查看表字段信息
desc extended 表名;      查看表擴展信息，但是不易讀
desc formatted 表名   //查看表擴展信息，格式化顯示，易讀
1
2
3
4
5
6
7
1.2.4表修改 alter
1）修改表名
alter table 老表名 rename to 新表名;
1
2）修改表列信息
添加一個列
alter table 表名 add columns(列名 列屬性);    ——添加到表的最后
1
修改列
alter table 表名change 老列名  新列名 新列屬性;    ——修改列名，類型不變
alter table 表名change 列名  列名 新屬性;    ——修改類型，列名不變
1
2
注意： 修改列屬性，大→小是可以的，由小→大不支持

3）刪除列——不支持
4）修改表的分區信息
默認分區表中的分區是0個

①添加一個分區信息
alter table 表名 add partition(dept="IS");     約束當前分區存儲的數據
//添加多個分區
alter table stu_partitioned add partition(dept="IS")
partition(dept="MA") partition(dept="CS");
1
2
3
4
②修改表的分區信息——了解
修改表的分區的存儲位置
分區表的每一個分區，默認的存儲位置

/user/myhive/warehouse/test_1901.db/stu_ptn/dept="is"
1
添加分區的時候直接指定這個分區的存儲位置
alter table stu_ptn add if not exists partition(dept="aa")
location "/data/hive/ptn/aa";
1
2
對于已經添加的分區，set location修改分區存儲位置
alter table stu_ptn partition(dept="IS") 
set location "/data/hive/ptn/is";
1
2
這個指定的路徑不會立即創建，插入數據的時候才會創建，原來的路徑也不會幫我們刪除

③查詢表的分區信息
show partitions 表名；    ——查詢表的所有分區
分區字段超過一個就是多級分區，前面的字段是高級分區，后面的是低級分區
show partitions 表名 partition(高級分區);       ——查看某一分區下的所有子分區
1
2
3
④刪除表的分區信息
alter table 表名 drop if exists partition(分區字段=分區名)；
1
1.2.5清空表
truncate table 表名；
1
清空表數據，不會刪除表，只能內部表使用，刪除表目錄下的所有文件

1.2.6刪除表
drop table if exists 表名;
1
1.2.7其他輔助命令
show create table 表名;    查看建表語句
1
2、hive的DML——data manage language數據操作/管理語言
2.1表數據插入
load
將數據從一個文件直接加載到hive的一個表中

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE
tablename [PARTITION(partcol1=val1, partcol2=val2 ...)]
1
2
語句說明：

1）load data 加載數據；
2）local從本地磁盤加載的關鍵字，從本地磁盤把數據加載到hive表中；不加local是從hdfs加載數據，從hdfs把數據加載到hive中 ；
3）inpath 文件的存儲路徑；
4）overwrite 覆蓋導入，將原來表中的數據清空，不加 overwrite則追加；
5）partition 指定分區表的數據導入。

注意： 分區表的數據導入一定指定導入到哪一個分區
總結： load就是將數據放在hive表存儲的hdfs路徑下，hive中表就是hdfs一個路徑（目錄）的使用者，只要在這個路徑下添加文件，就可以被表識別該文件沒有限制，關聯上以表結構的形式呈現出來

insert
1）單條數據插入
每次只插入一條數據
insert into table tbname values(.....);
?:
insert into table stu_copy values(1,"zs","nan",99,"ufo");
1
2
3
實際上hive的語句插入是將插入語句轉換 mapreduce任務
過程如下：
先生成一個臨時表（當前客戶端），最終將這個臨時表的數據插入到需要插入表中

2）單重數據插入
一次插入多條數據
#直接將本地文件加載到表中
load data local inpath "/home/hadoop/tmpdata/student.txt" into table stu_managed;
#從一個表中進行查詢數據，將查詢的結果插入到另一個表中
insert into table tbname select ...
?:
insert into table stu_copy select * from stu_managed where age=20;
1
2
3
4
5
6
3)多重數據插入
對一個表掃描一次，將多個結果插入到不同的表中

eg： stu_managed  
        age=18  age=19   stu_copy 
        age=18   age=20   stu_external
1
2
3
正常的寫法：
insert into table stu_copy select * from stu_managed where age=18 or age=19;
insert into table stu_external select * from stu_managed where age=18 or age=20;
1
2
上面操作會對原始表 stu_managed掃描兩次

優化寫法：
對同一個表掃描只掃描一次，最終不同的結果插入到不同的表中
from tbname 
insert ...select ...where ...
insert ... select ...where...
?:
from stu_managed 
insert into stu_copy select * where age=18 or age=19 
insert into stu_external select * where age=18 or age=20;
1
2
3
4
5
6
7
2.2數據導入
2.2.1數據導入——分區表的數據導入
注意：分區表數據無法直接導入，必須指定分區

1）靜態分區導入
導入數據的時候是靜態指定分區名，分區名導入數據的時候是寫死的

缺陷：
一定要足夠了解數據有哪些分區，如果數據很大，比如有2T，分區很多的時候這個方式不太適用了
適用：
分區數比較少、分區名固定的情況下，分區表進行讀取數據的時候，前面的表字段正常從表對應的文件中讀取，分區字段從導入數據的時候指定的分區字段讀取 分區字段存儲在分區的目錄上

①load的方式
可以向分區表導入數據，但是這個時候不會對導入的數據進行檢查（本質就是數據文件的移動或復制） 所以這種方式在進行分區表數據導入的時候要慎重，只有當你確定這個數據一定是這個分區的時候才可以這么使用，如果不確定則不可以使用這種方式；
生產上也會使用load方式進行導入數據，一般會按照日期建分區，數據采集一般也會按照日期存儲。

②insert方式
先將原始數據導入到一個普通表（非分區表）中，再從這個表結果放到分區表中

單重數據插入
insert into table tbname partition (分區字段=分區值) select ... from....
1
注意：
a、分區表數據 insert插入的時候，select的字段的個數和順序一定要和分區表的建表字段一致；
b、插入insert或導入load 數據到分區表的時候，分區已經存在直接將數據放在分區目錄下，分區不存在則自動創建這個分區。

多重數據插入
對原始表掃描一次 最終將數據插入到了多個分區中
from ...
insert ... select ... where ..
insert ... select .... where ...
1
2
3
注意：分區字段在進行查詢的時候，按照普通字段查詢就可以了

select * from stu_ptn where dept="MA";
只會掃描  /user/myhive/warehouse/test_1901.db/stu_ptn/dept=MA 分區下的數據
select * from stu_ptn where age=18;  全表掃描的
1
2
3
2）動態分區插入方式
根據分區字段的實際值進行動態生成分區名，這種方式插入數據只能使用insert的方式
語法：

insert into table tbname partition(分區字段名) select ... from ...
1
分區字段對應的值是根據select查詢的數據來的，所以select中需要將分區字段查詢出來

insert into table stu_ptn partition(dept) 
select sid,name,sex,age,dept from stu_copy;
1
2
此時會報錯：

FAILED: SemanticException [Error 10096]: Dynamic partition strict
mode requires at least one static partition column. To turn this
off set hive.exec.dynamic.partition.mode=nonstrict
1
2
3
解決辦法：
需要打開動態分區參數，默認沒有打開動態分區

set hive.exec.dynamic.partition.mode=nonstrict;
1
注意： 分區表在建表的時候，會將分區字段放在最后

補充說明：
多級分區的時候，分區級別超過1級，分區字段的個數超過1個一般會按照日期建分區year/month/day；分區字段超過一個按照分區字段的順序劃分級別，前面的字段的級別高于后面的字段的級別

craete ... partitioned by(dept string,age int)...
1
分區：先按照dept分區，再按照age進行分區，最終目錄是
stu_ptn02/dept=../age=..

2.2.2導入數據實例
1）兩個分區都是靜態分區
load和insert 均可，以insert為例

insert into table stu_ptn02 partition(dept="CS",age=18) 
select sid,name,sex from stu_copy where dept="CS" and age=18;
1
2
2）一靜一動
注意： 靜態分區必須是高級分區dept，高級分區必須是靜態分區，低級分區為動態分區，否則語法報錯
insert

insert into table stu_ptn02 partition(dept="MA",age) 
select sid,name,sex,age from stu_copy where dept="MA";
#目錄結構如下：
/user/myhive/warehouse/test_1901.db/stu_ptn02/dept=MA/age=17
1
2
3
4
3）兩個分區都是動態分區
insert

insert into table stu_ptn02 partition(dept,age) 
select sid,name,sex,dept,age from stu_copy;
1
2
2.2.2數據導入——分桶表的數據導入
1）load 方式
將表數據分成 3個文件存儲
分桶依據： 分桶字段.hash % 分桶個數 ，0 1 2 針對每一條數據進行判斷
原則上load方式不支持
load data local inpath “/home/hadoop/tmpdata/student.txt” into table stu_buk;
不支持load的 只能insert

2）insert方式
分桶表插入數據：設置參數

set hive.strict.checks.bucketing=false;
set hive.mapred.mode=nonstrict;
insert into table stu_buk select * from stu_copy;
1
2
3
Number of reducers (= 3) is more than 1 默認設置reducetask的個數就是3個 == 桶的個數的
查詢表stu_copy數據導入stu_buk的時候，會根據建表語句中指定的分桶字段分桶，分桶個數將數據分成3個
總結：

分桶表 只能用insert方式
分桶規則
如果分桶字段整型，分桶字段 % 分桶個數；不是整型，分桶字段.hash % 分桶個數，余數相同的數據到同一個桶中
2.3數據導出
hive中提供將一個查詢結果導出為一個文件

2.3.1單重數據導出
INSERT OVERWRITE [LOCAL] DIRECTORY 文件夾（本地的|hdfs） select_statemen
1
參數解釋：
local 導出數據到本地
案例：

#本地：
insert overwrite local directory "/home/hadoop/hive_data" 
select * from stu_copy where age=18;
#hdfs：
insert overwrite directory "/home/hadoop/hive_data" 
select * from stu_copy where age=18;
1
2
3
4
5
6
2.3.2多重數據導出
掃描一次表，將不同的結果導出不同的目錄下

from ... 
insert ...
insert ....
1
2
3
案例：

from stu_copy 
insert overwrite local directory "/home/hadoop/hive/age18" 
select * where age=18 
insert overwrite local directory "/home/hadoop/hive/age19" 
select * where age=19;
1
2
3
4
5
3、hive的DML之查詢
語法順序：

join  group by  order by  limit   where  having 
select .... from ... join...on...where...
group by...having....order by....limit ... 
1
2
3
3.1join
注意：

join 支持等值，不支持非等值
支持and連接 不支持or連接 多個連接條件的時候
select * from a join b on a.id=b.id and a.name=b.name;  支持
select * from a join b on a.id=b.id or a.name=b.name;   不支持 mapkey不好設計
1
2
支持超過2個表的連接
3.1.1join的分類
3.1.1.1內連接 inner join | join
求兩個表的連接鍵的交集,兩個表都有返回

3.1.1.2外連接 outer join
左外 left outer join == left join
join 左側表作為基礎表，左側表有的則返回，沒有的不要了，左有右沒有就補null

select * from a left join b on a.id=b.id; 
    結果
    1       zs      1       23
    2       ls      2       45
    4       ww      NULL    NULL
1
2
3
4
5
3.1.1.3右外 right outer join == right join
join 右側表作為基礎表，右表有的則返回，沒有的不要了，右有左沒有就補null

select * from a right join b on a.id=b.id;
結果：
1       zs      1       23
2       ls      2       45
NULL    NULL    3       56
1
2
3
4
5
3.1.1.4全外 full outer join == full join
求兩個表的并集，兩個表中有的所有的關聯建都會返回，哪一個對應的關聯建有數據，補充數據，沒有數據補null

select * from a full join b on a.id=b.id;
結果：
1       zs      1       23
2       ls      2       45
NULL    NULL    3       56
4       ww      NULL    NULL
1
2
3
4
5
6
3.1.1.5半連接 semi join
左半連接 left semi join
mysql in/exits 判斷字段值是否包含在我們給定的范圍中
select * 
from a where id in (select id from b);
hive2中這個語句可以執行但是執行效率低  mapkey hive1中不支持 
1
2
3
半連接就是高效解決 in /exists 問題

select * from a left semi join b on a.id=b.id;
1
最終取的結果a b進行內連接，取的是左半表；
最終返回的是a表中在b表中出現過的a所有的數據。
另一種實現方式：

select a.* from a join b on a.id=b.id;
1
內連接取左部分的表

3.2group by
group by后面的字段相同的分到一組中
注意：group by 使用的時候是有很大的限制的

3.2.1使用group by時select后面的字段(需要查詢的字段)有嚴格的限制
只能跟兩種類型的字段

1）group by的分組字段
select dept from stu_copy group by dept;
1
2)根據分組字段的進行的聚合函數
聚合函數 多–>一條，如sum max avg min…

select dept,max(age) from stu_copy group by dept;
錯誤示范：
select dept,sid,max(age) from stu_copy group by dept;
1
2
3
3.2.2group by 的執行順序在select 執行之前 group by中不能使用select中字段的別名
錯誤的
select dept d,max(age) from stu_copy group by d;
正確的：
select dept d,max(age) from stu_copy group by dept;
1
2
3
4
3.3where和having
where:
where是對聚合函數之前的數據進行過濾的，where就是為聚合函數準備數據，需要聚合的數據先進行一步過濾再聚合。
having:
執行順序是在聚合函數之后，針對聚合之后的結果進行過濾。

3.4order by
注意： order by 的執行順序在select之后，可以使用select中的別名
在hive中order by這個位置4個語法：

order by 用于全局排序，無論啟動多少個reducetask最終全局有序
sort by 用于局部排序，每一個reducetask的運行結果有序的，不保證全局有序
distribute by 用于字段分配
reducetask的個數需要手動指定 set mapreduce.job.reduces=2;
按照指定的字段進行分配reducetask的數據
distribute by 分+ sort by 排序
需要指定字段進行分 reducetask （mapreduce 分區） 指定字段排序
先要按照性別分成兩個reducetask，再在每一個reducetask中按照年齡排序

select * from stu_copy distribute by sex sort by age;
1
劃分依據： sex .hash % reducetasks
cluster by 用于先按照指定字段切分reducetask的數據再按照指定字段進行升序排序=distribute by 字段 + sort by 字段
調整 reducetask的個數：
set mapreduce.job.reduces=2;
當distribute by 的字段和sort by的字段一致的時候，并且升序可以使用cluster by 替換

按照age 切分，按照age升序

select * from stu_copy cluster by age;
select * from stu_copy distribute by age sort by age;
1
2
3.5limit
全局前幾個，所有數據的前幾個

select * from stu_copy limit 1;
1
補充：hql語句轉換為mr時候日志中顯示和解釋

In order to change the average load for a reducer (in bytes):
    每一個reducer平均加載的字節數
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
    reducetask最大限制
  set hive.exec.reducers.max=<number>  1009 
In order to set a constant number of reducers:
    設置reducetask的個數的
  set mapreduce.job.reduces=<number>  -1 
  0  沒有reducetask  需要的時候1 
  特例 分桶表插入數據的時候  reducetask=== 桶的個數
————————————————
 

“Hive的hql操作方式”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！