一、DDL操作（數據定義語言）

DDL操作（數據定義語言）包括：Create、Alter、Show、Drop等。

1. create database- 創建新數據庫

2. alter database - 修改數據庫

3. drop database - 刪除數據庫

4. create table - 創建新表

5. alter table - 變更（改變）數據庫表

6. drop table - 刪除表

7. create index - 創建索引（搜索鍵）

8. drop index - 刪除索引

9. show table - 查看錶

二、DML操作（數據操作語言）

1. load data - 加載數據

①insert into - 插入數據

②insert overwrite - 覆蓋數據（insert ... values從Hive 0.14開始可用）

2. update table - 更新表（update在Hive 0.14開始可用，並且只能在支持ACID的表上執行）

3. delete from table where id = 1; - 刪除表中ID等於1的數據（delete在Hive 0.14開始可用，並且只能在支持ACID的表上執行）

4. merge - 合併（MERGE在Hive 2.2開始可用，並且只能在支持ACID的表上執行）

注意：頻繁的update和delete操作已經違背了Hive的初衷，不到萬不得已的情況，最好使用增量添加的方式。

三、Hive數據倉庫操作

1. 創建數據倉庫：

create database test;

2. 查看數據倉庫：

show databases;

3. 創建數據倉庫：

create database if not exists test;

4. 查看數據倉庫信息以及路徑：

describe database test;

5. 刪除名為f的數據倉庫：

drop database if not exists test;

四、Hive數據表操作

1. 查看錶：

show tables;

2. 創建一個名為cat的內部表，有兩個字段為cat_id和cat_name，字符類型為string：

create table cat(cat_id string,cat_name string); 

3.創建一個外部表，表名為cat2，有兩個字段為cat_id和cat_name，字符類型為string。

create external table if not exists cat2(cat_id string,cat_name string);

4. 修改cat表的表結構。對cat表添加兩個字段group_id和cat_code：

alter table cat add columns(group_id string,cat_code string);

5. 使用desc命令查看一下加完字段後的cat表結構：

desc cat; 

6. 修改cat2表的表名。把cat2表重命名為cat3：

alter table cat2 rename to cat3; 

7. 刪除名為cat3的表並查看：

drop table cat3;  

show tables;  

8. 創建與已知表相同結構的表，創建一個與cat表結構相同的表，名為cat4，這裏要用到like關鍵字：

create table cat4 like cat; 

9. 創建完成並查看結果：

show tables; 

五、Hive中數據的4種導入

1、從本地文件系統中導入數據到Hive表

①在Hive中創建一個cat_group表，包含group_id和group_name兩個字段，字符類型為string，以“\t”為分隔符，並查看結果：

create table cat_group(group_id string,group_name string) row format delimited fields terminated by '\t'  stored as textfile;  

show tables;  

②將Linux本地/home/hadoop/data/hive2目錄下的cat_group文件導入到Hive中的cat_group表中：

load data local inpath '/data/hive2/cat_group' into table cat_group;

③通過select語句查看cat_group表中是否成功導入數據，由於數據量大，使用limit關鍵字限制輸出10條記錄：

select * from cat_group limit 10; 

2、將HDFS上的數據導入到Hive中

①另外開啓一個操作窗口，在HDFS上創建/hive2目錄：

hdfs dfs  -mkdir /hive2

②將本地/data/hive2/下的cat_group表上傳到HDFS的/hive2上，並查看是否創建成功：

hdfs dfs -put /home/hadoop/cat_group /hive2  

hdfs dfs  -ls /hive2  

③在Hive中創建名為cat_group1的表，創表語句如下:

create table cat_group1(group_id string,group_name string) row format delimited fields terminated by '\t'  stored as textfile;  

④將HDFS下/myhive2中的表cat_group導入到Hive中的cat_group1表中 ，並查看結果：

load data inpath '/hive2/cat_group' into table cat_group1;  

select * from cat_group1 limit 10;

提示：HDFS中數據導入到Hive中與本地數據導入到hive中的區別是load data後少了local。

3、從別的表中查詢出相應的數據並導入到Hive中

①在Hive中創建一個名為cat_group2的表：

create table cat_group2(group_id string,group_name string) row format delimited fields terminated by '\t'  stored as textfile;

②用下面兩種方式將cat_group1表中的數據導入到cat_group2表中：

insert into table cat_group2 select * from cat_group1;

或

insert overwrite table cat_group2 select * from cat_group1;  

（insert overwrite 會覆蓋數據）

③導入完成後，用select語句查詢cat_group2表：

select * from cat_group2 limit 10; 

4、在創建表的時候從別的表中查詢出相應數據

並插入到所創建的表中

①Hive中創建表cat_group3並直接從cat_group2中獲得數據。

create table cat_group3 as select * from cat_group2;

②創建並導入完成，用select語句查詢實驗結果：

select * from cat_group3 limit 10; 

六、三種常見的數據導出方式

1、導出到本地文件系統

①在Linux本地新建/data/hive2/out目錄。

mkdir -p /data/hive2/out

②將Hive中的cat_group表導出到本地文件系統/data/hive2/out中。

（注意：方法和導入數據到Hive不一樣，不能用insert into來將數據導出。）

insert overwrite local directory '/data/hive2/out' select * from cat_group;

③導出完成後，在Linux本地切換到/data/hive2/out目錄，通過cat命令查詢導出文件的內容：

cd /home/hadoop/data/hive2/out  

ls  

cat 000000_0

④通過上圖可以看到導出的數據，字段之間沒有分割開，所以我們使用下面的方式，將輸出字段以“\t”鍵分割：

insert overwrite local directory '/data/hive2/out' select group_id,concat('\t',group_name) from cat_group;

通過cat命令查詢/data/hive2/out目錄下的導出文件：

cd /home/hadoop/data/hive2/out/  

cat 000000_0 

2、Hive中數據導出到HDFS中

①在HDFS上創建/myhive2/out目錄:

hdfs dfs -mkdir -p /hive2/out

②將Hive中的表cat_group中的數據導入到HDFS的/myhive2/out目錄裏：

insert overwrite directory '/myhive2/out' select group_id,concat('\t',group_name) from cat_group;  

③導入完成後，在HDFS上的/myhive2/out目錄下查看結果：

hdfs dfs -ls /myhive2/out 

3、導出到Hive的另一個表中

①將Hive中表cat_group中的數據導入到cat_group4中（兩表字段及字符類型相同）。

②在Hive中創建一個表cat_group4，有group_id和group_name兩個字段，字符類型為string，以‘\t’為分隔符：

create table cat_group4(group_id string,group_name string)  row format delimited fields terminated by '\t'  stored as textfile;  

③將cat_group中的數據導入到cat_group4中：

insert into table cat_group4 select * from cat_group;

④導入完成後，查看cat_group4表中數據：

select * from cat_group4 limit 10;

七、Hive分區表的操作

Hive中創建分區表沒有什麼複雜的分區類型（範圍分區、列表分區、hash 分區，混合分區等）。分區列也不是表中的一個實際的字段，而是一個或者多個偽列。意思是説，在表的數據文件中實際並不保存分區列的信息與數據。

1. 創建表分區，在Hive中創建一個分區表goods，包含goods_id和goods_status兩個字段，字符類型為string，分區為cat_id，字符類型為string，以“\t“為分隔符。

create table goods(goods_id string,goods_status string) partitioned by (cat_id string)  

row format delimited fields terminated by '\t';  

2. 查看錶goods表結構：

desc goods; 

3. 向分區表插入數據，將本地/data/hive2下的表goods中數據，插入到分區表goods中。

①在Hive中創建一個非分區表goods_1表，用於存儲本地/data/hive2下的表goods中數據。

create table goods_1(goods_id string,goods_status string,cat_id string)  

row format delimited fields terminated by '\t';  

②將本地/data/hive2下的表goods中數據導入到Hive中的goods_1表中：

load data local inpath '/data/hive2/goods' into table goods_1;

③將表goods_1中的數據導入到分區表goods中：

insert into table goods partition(cat_id='52052') select goods_id,goods_status from goods_1 where cat_id='52052';

④插入數據完成後，用select語句查看實驗結果：

select * from goods limit 10;

⑤查看錶goods中的分區：

show partitions goods;

⑥修改表分區，將分區表goods中的分區列cat_id=52052改為cat_id=52051,並查看修改後的分區名：

alter table goods partition(cat_id=52052) rename to partition(cat_id=52051);  

show partitions goods; 

4. 刪除表分區：

①在刪除goods分區表之前，先將goods表備份出一個goods_2表：

create table goods_2(goods_id string,goods_status string) partitioned by (cat_id string)  

row format delimited fields terminated by '\t';  

insert into table goods_2 partition(cat_id='52052') select goods_id,goods_status from goods_1 where cat_id='52052';

②刪除goods表中的cat_id分區：

alter table goods drop if exists partition (cat_id='52051'); 

八、Hive桶的操作

1桶的定義及用途

①桶的定義

Hive是一個基於Hadoop的數據倉庫工具，它允許通過SQL-like查詢語言（HQL）來處理存儲在Hadoop文件系統（HDFS）中的大數據集。Hive中的“桶”（Bucketing）是一種數據分區和排序的方法，用於優化查詢性能和提高並行處理能力。

當我們在Hive表上創建桶時，會指定一個字段或一組字段作為桶鍵。Hive會根據這些鍵的哈希值將數據分成固定數量的桶。每個桶都會被寫入到單獨的文件中，這樣可以使得相同鍵值的數據落在同一個桶內。當執行JOIN操作時，如果JOIN條件涉及到桶鍵，Hive可以利用這個特性來減少JOIN的計算量，因為只需要比較相同桶號的數據，從而提高效率。

桶的使用還有助於並行化操作，因為每個桶可以獨立處理，尤其是在大規模數據集上，這可以顯著加快查詢速度。然而，需要注意的是，雖然桶化可以提高某些特定查詢的性能，但它也會增加數據寫入的複雜性和存儲空間的需求。因此，合理選擇桶的數量和桶鍵對於優化性能至關重要。

②桶的用途

優化JOIN操作：當兩個表在JOIN操作中使用相同的桶鍵時，Hive可以並行處理每個桶之間的JOIN，而不是處理整個表。這顯著減少了JOIN操作的時間，尤其是當JOIN涉及大數據集時。

數據採樣：桶化可以用於創建表的代表性樣本。通過選擇特定數量的桶，你可以輕鬆地獲取表的一小部分數據，這對於測試查詢性能、進行數據分析或生成報表非常有用。

提高查詢效率：通過桶化，Hive可以更有效地使用索引（如果已創建）和執行範圍查詢，因為數據已經按照桶鍵進行了預排序。

並行處理：每個桶都可以獨立處理，這意味着在分佈式環境中，不同的桶可以由集羣的不同節點同時處理，從而提高處理速度。

數據分佈均勻：桶化有助於確保數據在HDFS上的分佈更加均勻，避免某些節點過載，提高整體系統的性能和穩定性。

簡化數據處理：桶化還可以簡化某些複雜的統計操作，如計算分位數、百分比等，因為每個桶內的數據已經是有序的。

提示：儘管桶化有諸多好處，但使用時需要謹慎，因為它可能會增加存儲開銷，並且不適用於所有類型的查詢。在決定是否使用桶以及如何設計桶策略時，應根據具體的應用場景和查詢模式進行權衡。

③Hive還可以把表或分區，組織成桶。將表或分區組織成桶有以下幾個目的：

為使取樣更高效。在處理大規模的數據集時，在開發、測試階段將所有的數據全部處理一遍可能不太現實，如果能在數據集的一小部分數據上試運行查詢，會帶來很多方便。

為了獲得更好的查詢處理效率。桶為表提供了額外的結構，Hive在處理某些查詢時利用這個結構，能夠有效地提高查詢效率。

具體而言，連接兩個在（包含連接列的）相同列上劃分了桶的表，可以使用Map端連接（Map-side join）高效的實現。比如JOIN操作。對於JOIN操作兩個表有一個相同的列，如果對這兩個表都進行了桶操作。那麼將保存相同列值的桶進行JOIN操作就可以，可以大大較少JOIN的數據量。在建立桶之前，需要設置hive.enforce.bucketing屬性為true，使得Hive能識別桶。

2、創建桶

①創建一個名為goods_t的表，包含兩個字段goods_id和goods_status，字符類型都為string，按cat_id string做分區，按goods_status列聚類和goods_id列排序，劃分成兩個桶：

create table goods_t(goods_id string,goods_status string) partitioned by (cat_id string)  

clustered by(goods_status) sorted by (goods_id) into 2 buckets;  

②設置環境變量set hive.enforce.bucketing=ture

set hive.enforce.bucketing=true;

③向goods_t表中插入goods_2表中的數據：

from goods_2 insert overwrite table goods_t partition(cat_id='52063') select goods_id,goods_status;

查看結果：

select * from goods_t tablesample(bucket 1 out of 2 on goods_id);

tablesample 是抽樣語句，語法如下:

tablesample(bucket x out of y)

y必須是table總bucket數的倍數或者因子。hive根據y的大小，決定抽樣的比例。例如，table總共分了64份，當y=32時，抽取(64/32=)2個bucket的數據，當y=128時，抽取(64/128=)1/2個bucket的數據。

x表示從哪個bucket開始抽取。例如，table總bucket數為32，tablesample(bucket 3 out of 16)，表示總共抽取（32/16=）2個bucket的數據，分別為第3個bucket和第（3+16=）19個bucket的數據。

END