HiveSQL 数据定义语言（DDL）
第一章、數(shù)據(jù)定義語言（DDL）概述
1.1 DDL語法的作用
數(shù)據(jù)定義語言 (Data Definition Language, DDL)，是SQL語言集中對(duì)數(shù)據(jù)庫(kù)內(nèi)部的對(duì)象結(jié)構(gòu)進(jìn)行創(chuàng)建，刪除，修改等的操作語言，這些數(shù)據(jù)庫(kù)對(duì)象包括database（schema）、table、view、index等。核心語法由CREATE、ALTER與DROP三個(gè)所組成。DDL并不涉及表內(nèi)部數(shù)據(jù)的操作。
在某些上下文中，該術(shù)語也稱為數(shù)據(jù)描述語言，因?yàn)樗枋隽藬?shù)據(jù)庫(kù)表中的字段和記錄。
1.2 Hive中DDL使用
Hive SQL（HQL）與SQL的語法大同小異，基本上是相通的，學(xué)過SQL的使用者可以無痛使用Hive SQL。只不過在學(xué)習(xí)HQL語法的時(shí)候，特別要注意Hive自己特有的語法知識(shí)點(diǎn)，比如partition相關(guān)的DDL操作。
基于Hive的設(shè)計(jì)、使用特點(diǎn)，HQL中create語法（尤其create table）將是學(xué)習(xí)掌握DDL語法的重中之重。可以說建表是否成功直接影響數(shù)據(jù)文件是否映射成功，進(jìn)而影響后續(xù)是否可以基于SQL分析數(shù)據(jù)。通俗點(diǎn)說，沒有表，表沒有數(shù)據(jù)，你分析什么呢？
選擇正確的方向,往往比盲目努力重要。
第二章、Hive DDL建表基礎(chǔ)
2.1 完整建表語法樹
藍(lán)色字體是建表語法的關(guān)鍵字，用于指定某些功能。
[]中括號(hào)的語法表示可選。
|表示使用的時(shí)候，左右語法二選一。
建表語句中的語法順序要和上述語法規(guī)則保持一致。
2.2 Hive數(shù)據(jù)類型詳解
整體概述
Hive中的數(shù)據(jù)類型指的是Hive表中的列字段類型。Hive數(shù)據(jù)類型整體分為兩個(gè)類別：原生數(shù)據(jù)類型（primitive data type）和復(fù)雜數(shù)據(jù)類型（complex data type）。
原生數(shù)據(jù)類型包括：數(shù)值類型、時(shí)間類型、字符串類型、雜項(xiàng)數(shù)據(jù)類型；
復(fù)雜數(shù)據(jù)類型包括：array數(shù)組、map映射、struct結(jié)構(gòu)、union聯(lián)合體。
關(guān)于Hive的數(shù)據(jù)類型，需要注意：
英文字母大小寫不敏感；
除SQL數(shù)據(jù)類型外，還支持Java數(shù)據(jù)類型，比如：string；
int和string是使用最多的，大多數(shù)函數(shù)都支持；
復(fù)雜數(shù)據(jù)類型的使用通常需要和分隔符指定語法配合使用。
如果定義的數(shù)據(jù)類型和文件不一致，hive會(huì)嘗試隱式轉(zhuǎn)換，但是不保證成功。
原生數(shù)據(jù)類型
Hive支持的原生數(shù)據(jù)類型如下圖所示：
其中標(biāo)注的數(shù)據(jù)類型是使用較多的，詳細(xì)的描述請(qǐng)查詢語法手冊(cè)：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
復(fù)雜數(shù)據(jù)類型
Hive支持的復(fù)雜數(shù)據(jù)類型如下圖所示：
其中標(biāo)注的數(shù)據(jù)類型是使用較多的，詳細(xì)的描述請(qǐng)查詢語法手冊(cè)：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
數(shù)據(jù)類型隱式、顯示轉(zhuǎn)換
與SQL類似，HQL支持隱式和顯式類型轉(zhuǎn)換。
原生類型從窄類型到寬類型的轉(zhuǎn)換稱為隱式轉(zhuǎn)換，反之，則不允許。
下表描述了類型之間允許的隱式轉(zhuǎn)換：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
顯式類型轉(zhuǎn)換使用CAST函數(shù)。
例如，CAST（'100'as INT）會(huì)將100字符串轉(zhuǎn)換為100整數(shù)值。 如果強(qiáng)制轉(zhuǎn)換失敗，例如CAST（'INT'as INT），該函數(shù)返回NULL。
2.3 Hive讀寫文件機(jī)制
SerDe是什么
SerDe是Serializer、Deserializer的簡(jiǎn)稱，目的是用于序列化和反序列化。序列化是對(duì)象轉(zhuǎn)化為字節(jié)碼的過程；而反序列化是字節(jié)碼轉(zhuǎn)換為對(duì)象的過程。
Hive使用SerDe（和FileFormat）讀取和寫入行對(duì)象。
需要注意的是，“key”部分在讀取時(shí)會(huì)被忽略，而在寫入時(shí)key始終是常數(shù)。基本上行對(duì)象存儲(chǔ)在“value”中。
可以通過desc formatted tablename查看表的相關(guān)SerDe信息。默認(rèn)如下：
Hive讀寫文件流程
Hive讀取文件機(jī)制：首先調(diào)用InputFormat（默認(rèn)TextInputFormat），返回一條一條kv鍵值對(duì)記錄（默認(rèn)是一行對(duì)應(yīng)一條記錄）。然后調(diào)用SerDe（默認(rèn)LazySimpleSerDe）的Deserializer，將一條記錄中的value根據(jù)分隔符切分為各個(gè)字段。
Hive寫文件機(jī)制：將Row寫入文件時(shí)，首先調(diào)用SerDe（默認(rèn)LazySimpleSerDe）的Serializer將對(duì)象轉(zhuǎn)換成字節(jié)序列，然后調(diào)用OutputFormat將數(shù)據(jù)寫入HDFS文件中。
SerDe相關(guān)語法
在Hive的建表語句中，和SerDe相關(guān)的語法為：
其中ROW FORMAT是語法關(guān)鍵字，DELIMITED和SERDE二選其一。
如果使用delimited表示使用默認(rèn)的LazySimpleSerDe類來處理數(shù)據(jù)。如果數(shù)據(jù)文件格式比較特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde類來處理數(shù)據(jù),甚至支持用戶自定義SerDe類。
LazySimpleSerDe分隔符指定
LazySimpleSerDe是Hive默認(rèn)的序列化類，包含4種子語法，分別用于指定字段之間、集合元素之間、map映射 kv之間、換行的分隔符號(hào)。在建表的時(shí)候可以根據(jù)數(shù)據(jù)的特點(diǎn)靈活搭配使用。
默認(rèn)分隔符
hive建表時(shí)如果沒有row format語法。此時(shí)字段之間默認(rèn)的分割符是'\001'，是一種特殊的字符，使用的是ascii編碼的值，鍵盤是打不出來的。
在vim編輯器中，連續(xù)按下Ctrl+v/Ctrl+a即可輸入'\001' ，顯示^A
在一些文本編輯器中將以SOH的形式顯示：
2.4 Hive數(shù)據(jù)存儲(chǔ)路徑
默認(rèn)存儲(chǔ)路徑
Hive表默認(rèn)存儲(chǔ)路徑是由${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir屬性指定。默認(rèn)值是：/user/hive/warehouse。
在該路徑下，文件將根據(jù)所屬的庫(kù)、表，有規(guī)律的存儲(chǔ)在對(duì)應(yīng)的文件夾下。
指定存儲(chǔ)路徑
在Hive建表的時(shí)候，可以通過location語法來更改數(shù)據(jù)在HDFS上的存儲(chǔ)路徑，使得建表加載數(shù)據(jù)更加靈活方便。
語法：LOCATION'<hdfs_location>'。
對(duì)于已經(jīng)生成好的數(shù)據(jù)文件，使用location指定路徑將會(huì)很方便。
3.5 案例—王者榮耀
原生數(shù)據(jù)類型案例
文件archer.txt中記錄了手游《王者榮耀》射手的相關(guān)信息，內(nèi)容如下所示，其中字段之間分隔符為制表符\t,要求在Hive中建表映射成功該文件。
1 后羿 5986 1784 396 336 remotely archer 2 馬可波羅 5584 200 362 344 remotely archer 3 魯班七號(hào) 5989 1756 400 323 remotely archer 4 李元芳 5725 1770 396 340 remotely archer 5 孫尚香 6014 1756 411 346 remotely archer 6 黃忠 5898 1784 403 319 remotely archer 7 狄仁杰 5710 1770 376 338 remotely archer 8 虞姬 5669 1770 407 329 remotely archer 9 成吉思汗 5799 1742 394 329 remotely archer 10 百里守約 5611 1784 410 329 remotely archer
字段含義：id、name（英雄名稱）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻擊范圍）、role_main（主要定位）、role_assist（次要定位）。
分析一下：字段都是基本類型，字段的順序需要注意一下。字段之間的分隔符是制表符，需要使用row format語法進(jìn)行指定。
建表語句：
--創(chuàng)建數(shù)據(jù)庫(kù)并切換使用
create database itcast;
use itcast;
--ddl create table
create table t_archer(
id int comment "ID",
name string comment "英雄名稱",
hp_max int comment "最大生命",
mp_max int comment "最大法力",
attack_max int comment "最高物攻",
defense_max int comment "最大物防",
attack_range string comment "攻擊范圍",
role_main string comment "主要定位",
role_assist string comment "次要定位"
) comment "王者榮耀射手信息"
row format delimited fields terminated by "\t";
建表成功之后，在Hive的默認(rèn)存儲(chǔ)路徑下就生成了表對(duì)應(yīng)的文件夾，把a(bǔ)rcher.txt文件上傳到對(duì)應(yīng)的表文件夾下。
hadoop fs -put archer.txt /user/hive/warehouse/honor_of_kings.db/t_archer
執(zhí)行查詢操作，可以看出數(shù)據(jù)已經(jīng)映射成功。
想一想：Hive這種能力是不是比mysql一條一條insert插入數(shù)據(jù)方便多了？
復(fù)雜數(shù)據(jù)類型案例
文件hot_hero_skin_price.txt中記錄了手游《王者榮耀》熱門英雄的相關(guān)皮膚價(jià)格信息，內(nèi)容如下,要求在Hive中建表映射成功該文件。
1,孫悟空,53,西部大鏢客:288-大圣娶親:888-全息碎片:0-至尊寶:888-地獄火:1688 2,魯班七號(hào),54,木偶奇遇記:288-福祿兄弟:288-黑桃隊(duì)長(zhǎng):60-電玩小子:2288-星空夢(mèng)想:0 3,后裔,53,精靈王:288-阿爾法小隊(duì):588-輝光之辰:888-黃金射手座:1688-如夢(mèng)令:1314 4,鎧,52,龍域領(lǐng)主:288-曙光守護(hù)者:1776 5,韓信,52,飛衡:1788-逐夢(mèng)之影:888-白龍吟:1188-教廷特使:0-街頭霸王:888
字段：id、name（英雄名稱）、win_rate（勝率）、skin_price（皮膚及價(jià)格）
分析一下：前3個(gè)字段原生數(shù)據(jù)類型、最后一個(gè)字段復(fù)雜類型map。需要指定字段之間分隔符、集合元素之間分隔符、map kv之間分隔符。
建表語句：
create table t_hot_hero_skin_price(
id int,
name string,
win_rate int,
skin_price map<string,int>
)row format delimited
fields terminated by ',' --指定字段之間分隔符
collection items terminated by '-' --指定集合元素之間的分隔符
map keys terminated by ':' ;---指定map元素kv之間的分隔符
建表成功后，把hot_hero_skin_price.txt文件上傳到對(duì)應(yīng)的表文件夾下。
hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/honor_of_kings.db/t_hot_hero_skin_price
執(zhí)行查詢操作，可以看出數(shù)據(jù)已經(jīng)映射成功。
想一想：如果最后一個(gè)字段以String類型來定義，后續(xù)使用方便嗎？
默認(rèn)分隔符案例
文件team_ace_player.txt中記錄了手游《王者榮耀》主要戰(zhàn)隊(duì)內(nèi)最受歡迎的王牌選手信息，內(nèi)容如下,要求在Hive中建表映射成功該文件。
字段：id、team_name（戰(zhàn)隊(duì)名稱）、ace_player_name（王牌選手名字）
分析一下：數(shù)據(jù)都是原生數(shù)據(jù)類型，且字段之間分隔符是\001，因此在建表的時(shí)候可以省去row format語句，因?yàn)閔ive默認(rèn)的分隔符就是\001。
建表語句：
create table t_team_ace_player(
id int,
team_name string,
ace_player_name string
); --沒有指定row format語句 此時(shí)采用的是默認(rèn)的\001作為字段的分隔符
建表成功后，把team_ace_player.txt文件上傳到對(duì)應(yīng)的表文件夾下。
hadoop fs -put team_ace_player.txt /user/hive/warehouse/honor_of_kings.db/t_team_ace_player
執(zhí)行查詢操作，可以看出數(shù)據(jù)已經(jīng)映射成功。
想一想：字段以\001分隔建表時(shí)很方便，那么采集、清洗數(shù)據(jù)時(shí)對(duì)數(shù)據(jù)格式追求有什么啟發(fā)？你青睞于什么分隔符？
優(yōu)先考慮\001分隔符
指定數(shù)據(jù)存儲(chǔ)路徑
文件team_ace_player.txt中記錄了手游《王者榮耀》主要戰(zhàn)隊(duì)內(nèi)最受歡迎的王牌選手信息，字段之間使用的是\001作為分隔符。
要求把文件上傳到HDFS任意路徑下，不能移動(dòng)復(fù)制，并在Hive中建表映射成功該文件。
建表語句
create table t_team_ace_player(
id int,
team_name string,
ace_player_name string
)location '/tmp';--使用location關(guān)鍵字指定本張表數(shù)據(jù)在hdfs上的存儲(chǔ)路徑
第三章、Hive DDL建表高階
3.1 Hive內(nèi)、外部表
內(nèi)部表
內(nèi)部表（Internal table）也稱為被Hive擁有和管理的托管表（Managed table）。默認(rèn)情況下創(chuàng)建的表就是內(nèi)部表，Hive擁有該表的結(jié)構(gòu)和文件。換句話說，Hive完全管理表（元數(shù)據(jù)和數(shù)據(jù)）的生命周期，類似于RDBMS（關(guān)系型數(shù)據(jù)庫(kù)）中的表。
當(dāng)您刪除內(nèi)部表時(shí)，它會(huì)刪除數(shù)據(jù)以及表的元數(shù)據(jù)。
create table student(
num int,
name string,
sex string,
age int,
dept string)
row format delimited
fields terminated by ',';
可以使用DESCRIBE FORMATTED itcast.student;來獲取表的描述信息，從中可以看出表的類型。
外部表
外部表（External table）中的數(shù)據(jù)不是Hive擁有或管理的，只管理表元數(shù)據(jù)的生命周期。要創(chuàng)建一個(gè)外部表，需要使用EXTERNAL語法關(guān)鍵字。
刪除外部表只會(huì)刪除元數(shù)據(jù)，而不會(huì)刪除實(shí)際數(shù)據(jù)。在Hive外部仍然可以訪問實(shí)際數(shù)據(jù)。
而且外部表更為方便的是可以搭配location語法指定數(shù)據(jù)的路徑。
create external table student_ext(
num int,
name string,
sex string,
age int,
dept string)
row format delimited
fields terminated by ','
location '/stu';
可以使用DESC FORMATTED itcast. student_ext;來獲取表的描述信息，從中可以看出表的類型。
內(nèi)部表、外部表差異
無論內(nèi)部表還是外部表，Hive都在Hive Metastore中管理表定義及其分區(qū)信息。
刪除內(nèi)部表會(huì)從Metastore中刪除表元數(shù)據(jù)，還會(huì)從HDFS中刪除其所有數(shù)據(jù)/文件。
刪除外部表，只會(huì)從Metastore中刪除表的元數(shù)據(jù)，并保持HDFS位置中的實(shí)際數(shù)據(jù)不變。
如何選擇內(nèi)部表、外部表
當(dāng)需要通過Hive完全管理控制表的整個(gè)生命周期時(shí)，請(qǐng)使用內(nèi)部表。
當(dāng)文件已經(jīng)存在或位于遠(yuǎn)程位置時(shí)，請(qǐng)使用外部表，因?yàn)榧词箘h除表，文件也會(huì)被保留。
Location關(guān)鍵字
在創(chuàng)建外部表的時(shí)候，可以使用location指定存儲(chǔ)位置路徑，如果不指定會(huì)如何？
如果不指定location，外部表的默認(rèn)路徑也是位于/user/hive/warehouse，由默認(rèn)參數(shù)控制。
創(chuàng)建內(nèi)部表的時(shí)候，是否可以使用location指定？
內(nèi)部表可以使用location指定位置的。
是否意味著Hive表的數(shù)據(jù)在HDFS上的位置不是一定要在/user/hive/warehouse下？
不一定，Hive中表數(shù)據(jù)存儲(chǔ)位置，不管內(nèi)部表還是外部表，默認(rèn)都是在/user/hive/warehouse，當(dāng)然可以在建表的時(shí)候通過location關(guān)鍵字指定存儲(chǔ)位置在HDFS的任意路徑。
3.2 Hive分區(qū)表
2.1．分區(qū)表的引入、產(chǎn)生背景
現(xiàn)有6份數(shù)據(jù)文件，分別記錄了《王者榮耀》中6種位置的英雄相關(guān)信息。現(xiàn)要求通過建立一張表t_all_hero，把6份文件同時(shí)映射加載。
create table t_all_hero(
id int,
name string,
hp_max int,
mp_max int,
attack_max int,
defense_max int,
attack_range string,
role_main string,
role_assist string
)
row format delimited
fields terminated by "\t";
加載數(shù)據(jù)文件到HDFS指定路徑下：
hadoop fs -put archer.txt assassin.txt mage.txt support.txt tank.txt warrior.txt /user/hive/warehouse/itcast.db/t_all_hero
現(xiàn)要求查詢r(jià)ole_main主要定位是射手并且hp_max最大生命大于6000的有幾個(gè)，sql語句如下：
select count(*) from t_all_hero where role_main="archer" and hp_max >6000;
思考一下：where語句的背后需要進(jìn)行全表掃描才能過濾出結(jié)果，對(duì)于hive來說需要掃描表下面的每一個(gè)文件。如果數(shù)據(jù)文件特別多的話，效率很慢也沒必要。本需求中，只需要掃描archer.txt文件即可，如何優(yōu)化可以加快查詢，減少全表掃描呢？
分區(qū)表的概念、創(chuàng)建
當(dāng)Hive表對(duì)應(yīng)的數(shù)據(jù)量大、文件多時(shí)，為了避免查詢時(shí)全表掃描數(shù)據(jù)，Hive支持根據(jù)用戶指定的字段進(jìn)行分區(qū)，分區(qū)的字段可以是日期、地域、種類等具有標(biāo)識(shí)意義的字段。比如把一整年的數(shù)據(jù)根據(jù)月份劃分12個(gè)月（12個(gè)分區(qū)），后續(xù)就可以查詢指定月份分區(qū)的數(shù)據(jù)，盡可能避免了全表掃描查詢。
分區(qū)表建表語法：
CREATE TABLE table_name (column1 data_type, column2 data_type) PARTITIONED BY (partition1 data_type, partition2 data_type,….);
針對(duì)《王者榮耀》英雄數(shù)據(jù)，重新創(chuàng)建一張分區(qū)表t_all_hero_part，以role角色作為分區(qū)字段。
create table t_all_hero_part(
id int,
name string,
hp_max int,
mp_max int,
attack_max int,
defense_max int,
attack_range string,
role_main string,
role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";
需要注意：分區(qū)字段不能是表中已經(jīng)存在的字段，因?yàn)榉謪^(qū)字段最終也會(huì)以虛擬字段的形式顯示在表結(jié)構(gòu)上。
分區(qū)表數(shù)據(jù)加載--靜態(tài)分區(qū)
所謂靜態(tài)分區(qū)指的是分區(qū)的字段值是由用戶在加載數(shù)據(jù)的時(shí)候手動(dòng)指定的。
語法如下：
load data [local] inpath ' ' into table tablename partition(分區(qū)字段='分區(qū)值'...);
Local表示數(shù)據(jù)是位于本地文件系統(tǒng)還是HDFS文件系統(tǒng)。關(guān)于load語句后續(xù)詳細(xì)展開講解。
靜態(tài)加載數(shù)據(jù)操作如下，文件都位于Hive服務(wù)器所在機(jī)器本地文件系統(tǒng)上。
load data local inpath '/opt/module/hive/archer.txt' into table t_all_hero_part partition(role='sheshou'); load data local inpath '/opt/module/hive/assassin.txt' into table t_all_hero_part partition(role='cike'); load data local inpath '/opt/module/hive/mage.txt' into table t_all_hero_part partition(role='fashi'); load data local inpath '/opt/module/hive/support.txt' into table t_all_hero_part partition(role='fuzhu'); load data local inpath '/opt/module/hive/tank.txt' into table t_all_hero_part partition(role='tanke'); load data local inpath '/opt/module/hive/warrior.txt' into table t_all_hero_part partition(role='zhanshi');
分區(qū)掃描：
select count(*) from t_all_hero_part where role="archer" and hp_max >6000
分區(qū)表數(shù)據(jù)加載--動(dòng)態(tài)分區(qū)
往hive分區(qū)表中插入加載數(shù)據(jù)時(shí)，如果需要?jiǎng)?chuàng)建的分區(qū)很多，則需要復(fù)制粘貼修改很多sql去執(zhí)行，效率低。因?yàn)閔ive是批處理系統(tǒng)，所以hive提供了一個(gè)動(dòng)態(tài)分區(qū)功能，其可以基于查詢參數(shù)的位置去推斷分區(qū)的名稱，從而建立分區(qū)。
所謂動(dòng)態(tài)分區(qū)指的是分區(qū)的字段值是基于查詢結(jié)果自動(dòng)推斷出來的。核心語法就是insert+select。
啟用hive動(dòng)態(tài)分區(qū)，需要在hive會(huì)話中設(shè)置兩個(gè)參數(shù)：
set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;
第一個(gè)參數(shù)表示開啟動(dòng)態(tài)分區(qū)功能，第二個(gè)參數(shù)指定動(dòng)態(tài)分區(qū)的模式。分為nonstick非嚴(yán)格模式和strict嚴(yán)格模式。strict嚴(yán)格模式要求至少有一個(gè)分區(qū)為靜態(tài)分區(qū)。
創(chuàng)建一張新的分區(qū)表t_all_hero_part_dynamic
create table t_all_hero_part_dynamic(
id int,
name string,
hp_max int,
mp_max int,
attack_max int,
defense_max int,
attack_range string,
role_main string,
role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";
執(zhí)行動(dòng)態(tài)分區(qū)插入（從原有的t_all_hero表中導(dǎo)入）
insert into table t_all_hero_part_dynamic partition(role) --注意這里 分區(qū)值并沒有手動(dòng)寫死指定 select tmp.*,tmp.role_main from t_all_hero tmp;
動(dòng)態(tài)分區(qū)插入時(shí)，分區(qū)值是根據(jù)查詢返回字段位置自動(dòng)推斷的。
select * from t_all_hero_part_dynamic;
分區(qū)表的本質(zhì)
外表上看起來分區(qū)表好像沒多大變化，只不過多了一個(gè)分區(qū)字段。實(shí)際上在底層管理數(shù)據(jù)的方式發(fā)生了改變。這里直接去HDFS查看區(qū)別。
非分區(qū)表：t_all_hero
分區(qū)表：t_all_hero_part
分區(qū)的概念提供了一種將Hive表數(shù)據(jù)分離為多個(gè)文件/目錄的方法。不同分區(qū)對(duì)應(yīng)著不同的文件夾，同一分區(qū)的數(shù)據(jù)存儲(chǔ)在同一個(gè)文件夾下。只需要根據(jù)分區(qū)值找到對(duì)應(yīng)的文件夾，掃描本分區(qū)下的文件即可，避免全表數(shù)據(jù)掃描。
分區(qū)表的使用
分區(qū)表的使用重點(diǎn)在于：
一、建表時(shí)根據(jù)業(yè)務(wù)場(chǎng)景設(shè)置合適的分區(qū)字段。比如日期、地域、類別等；
二、查詢的時(shí)候盡量先使用where進(jìn)行分區(qū)過濾，查詢指定分區(qū)的數(shù)據(jù)，避免全表掃描。
比如：查詢英雄主要定位是射手并且最大生命大于6000的個(gè)數(shù)。使用分區(qū)表查詢和使用非分區(qū)表進(jìn)行查詢，SQL如下：
--非分區(qū)表 全表掃描過濾查詢 select count(*) from t_all_hero where role_main="archer" and hp_max >6000; --分區(qū)表 先基于分區(qū)過濾 再查詢 select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;
想一想：底層執(zhí)行性能來說，分區(qū)表的優(yōu)勢(shì)在哪里？
分區(qū)表的注意事項(xiàng)
一、分區(qū)表不是建表的必要語法規(guī)則，是一種優(yōu)化手段表，可選；
二、分區(qū)字段不能是表中已有的字段，不能重復(fù)；
三、分區(qū)字段是虛擬字段，其數(shù)據(jù)并不存儲(chǔ)在底層的文件中；
四、分區(qū)字段值的確定來自于用戶價(jià)值數(shù)據(jù)手動(dòng)指定（靜態(tài)分區(qū)）或者根據(jù)查詢結(jié)果位置自動(dòng)推斷（動(dòng)態(tài)分區(qū)）
五、Hive支持多重分區(qū)，也就是說在分區(qū)的基礎(chǔ)上繼續(xù)分區(qū)，劃分更加細(xì)粒度
多重分區(qū)表
通過建表語句中關(guān)于分區(qū)的相關(guān)語法可以發(fā)現(xiàn)，Hive支持多個(gè)分區(qū)字段：PARTITIONED BY (partition1 data_type, partition2 data_type,….)。
多重分區(qū)下，分區(qū)之間是一種遞進(jìn)關(guān)系，可以理解為在前一個(gè)分區(qū)的基礎(chǔ)上繼續(xù)分區(qū)。從HDFS的角度來看就是文件夾下繼續(xù)劃分子文件夾。比如：把全國(guó)人口數(shù)據(jù)首先根據(jù)省進(jìn)行分區(qū)，然后根據(jù)市進(jìn)行劃分，如果你需要甚至可以繼續(xù)根據(jù)區(qū)縣再劃分，此時(shí)就是3分區(qū)表。
--單分區(qū)表，按省份分區(qū) create table t_user_province (id int, name string,age int) partitioned by (province string); --雙分區(qū)表，按省份和市分區(qū) create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string); --三分區(qū)表，按省份、市、縣分區(qū) create table t_user_province_city_county (id int, name string,age int) partitioned by (province string, city string,county string);
多分區(qū)表的數(shù)據(jù)插入和查詢使用
load data local inpath '文件路徑' into table t_user_province partition(province='shanghai'); load data local inpath '文件路徑' into table t_user_province_city_county partition(province='zhejiang',city='hangzhou',county='xiaoshan'); select * from t_user_province_city_county where province='zhejiang' and city='hangzhou';
3.3 Hive分桶表
分桶表的概念
分桶表也叫做桶表，源自建表語法中bucket單詞。是一種用于優(yōu)化查詢而設(shè)計(jì)的表類型。該功能可以讓數(shù)據(jù)分解為若干個(gè)部分易于管理。
在分桶時(shí)，我們要指定根據(jù)哪個(gè)字段將數(shù)據(jù)分為幾桶（幾個(gè)部分）。
默認(rèn)規(guī)則是：
可以發(fā)現(xiàn)桶編號(hào)相同的數(shù)據(jù)會(huì)被分到同一個(gè)桶當(dāng)中。hash_function取決于分桶字段bucketing_column的類型：
如果是int類型，hash_function(int) == int;
如果是其他類型，比如bigint,string或者復(fù)雜數(shù)據(jù)類型，hash_function比較棘手，將是從該類型派生的某個(gè)數(shù)字，比如hashcode值。
分桶表的語法
--分桶表建表語句 CREATE [EXTERNAL] TABLE [db_name.]table_name [(col_name data_type, ...)] CLUSTERED BY (col_name) INTO N BUCKETS;
其中CLUSTERED BY (col_name)表示根據(jù)哪個(gè)字段進(jìn)行分；
INTO N BUCKETS表示分為幾桶（也就是幾個(gè)部分）。
需要注意的是，分桶的字段必須是表中已經(jīng)存在的字段。
分桶表的創(chuàng)建
現(xiàn)有美國(guó)2021-1-28號(hào)，各個(gè)縣county的新冠疫情累計(jì)案例信息，包括確診病例和死亡病例，數(shù)據(jù)格式如下所示：
2021-01-28,Juneau City and Borough,Alaska,02110,1108,3 2021-01-28,Kenai Peninsula Borough,Alaska,02122,3866,18 2021-01-28,Ketchikan Gateway Borough,Alaska,02130,272,1 2021-01-28,Kodiak Island Borough,Alaska,02150,1021,5 2021-01-28,Kusilvak Census Area,Alaska,02158,1099,3 2021-01-28,Lake and Peninsula Borough,Alaska,02164,5,0 2021-01-28,Matanuska-Susitna Borough,Alaska,02170,7406,27 2021-01-28,Nome Census Area,Alaska,02180,307,0 2021-01-28,North Slope Borough,Alaska,02185,973,3 2021-01-28,Northwest Arctic Borough,Alaska,02188,567,1 2021-01-28,Petersburg Borough,Alaska,02195,43,0
字段含義如下：count_date（統(tǒng)計(jì)日期）,county（縣）,state（州）,fips（縣編碼code）,cases（累計(jì)確診病例）,deaths（累計(jì)死亡病例）。
根據(jù)state州把數(shù)據(jù)分為5桶，建表語句如下：
CREATE TABLE itcast.t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
CLUSTERED BY(state) INTO 5 BUCKETS;
在創(chuàng)建分桶表時(shí)，還可以指定分桶內(nèi)的數(shù)據(jù)排序規(guī)則
--根據(jù)state州分為5桶 每個(gè)桶內(nèi)根據(jù)cases確診病例數(shù)倒序排序
CREATE TABLE itcast.t_usa_covid19_bucket_sort(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
CLUSTERED BY(state) sorted by (cases desc) INTO 5 BUCKETS;
分桶表的數(shù)據(jù)加載
--step1:開啟分桶的功能 從Hive2.0開始不再需要設(shè)置
set hive.enforce.bucketing=true;
--step2:把源數(shù)據(jù)加載到普通hive表中
CREATE TABLE itcast.t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
row format delimited fields terminated by ",";
--將源數(shù)據(jù)上傳到HDFS，t_usa_covid19表對(duì)應(yīng)的路徑下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itcast.db/t_usa_covid19
--step3:使用insert+select語法將數(shù)據(jù)加載到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;
到HDFS上查看t_usa_covid19_bucket底層數(shù)據(jù)結(jié)構(gòu)可以發(fā)現(xiàn)，數(shù)據(jù)被分為了5個(gè)部分。
并且從結(jié)果可以發(fā)現(xiàn)，只要hash_function(bucketing_column)一樣的，就一定被分到同一個(gè)桶中。
分桶表的使用好處
和非分桶表相比，分桶表的使用好處有以下幾點(diǎn)：
1、基于分桶字段查詢時(shí)，減少全表掃描
--基于分桶字段state查詢來自于New York州的數(shù)據(jù) --不再需要進(jìn)行全表掃描過濾 --根據(jù)分桶的規(guī)則hash_function(New York) mod 5計(jì)算出分桶編號(hào) --查詢指定分桶里面的數(shù)據(jù) 就可以找出結(jié)果 此時(shí)是分桶掃描而不是全表掃描 select * from t_usa_covid19_bucket where state="New York";
2、JOIN時(shí)可以提高M(jìn)R程序效率，減少笛卡爾積數(shù)量
對(duì)于JOIN操作兩個(gè)表有一個(gè)相同的列，如果對(duì)這兩個(gè)表都進(jìn)行了分桶操作。那么將保存相同列值的桶進(jìn)行JOIN操作就可以，可以大大較少JOIN的數(shù)據(jù)量。
3、分桶表數(shù)據(jù)進(jìn)行抽樣
當(dāng)數(shù)據(jù)量特別大時(shí)，對(duì)全體數(shù)據(jù)進(jìn)行處理存在困難時(shí)，抽樣就顯得尤其重要了。抽樣可以從被抽取的數(shù)據(jù)中估計(jì)和推斷出整體的特性，是科學(xué)實(shí)驗(yàn)、質(zhì)量檢驗(yàn)、社會(huì)調(diào)查普遍采用的一種經(jīng)濟(jì)有效的工作和研究方法。
3.4 Hive Transactional Tables事務(wù)表
Hive事務(wù)背景知識(shí)
Hive本身從設(shè)計(jì)之初時(shí)，就是不支持事務(wù)的，因?yàn)镠ive的核心目標(biāo)是將已經(jīng)存在的結(jié)構(gòu)化數(shù)據(jù)文件映射成為表，然后提供基于表的SQL分析處理，是一款面向分析的工具。且映射的數(shù)據(jù)通常存儲(chǔ)于HDFS上，而HDFS是不支持隨機(jī)修改文件數(shù)據(jù)的。
這個(gè)定位就意味著在早期的Hive的SQL語法中是沒有update，delete操作的，也就沒有所謂的事務(wù)支持了，因?yàn)槎际莝elect查詢分析操作。
從Hive0.14版本開始，具有ACID語義的事務(wù)已添加到Hive中，以解決以下場(chǎng)景下遇到的問題：
流式傳輸數(shù)據(jù)。使用如Apache Flume或Apache Kafka之類的工具將數(shù)據(jù)流式傳輸?shù)紿adoop集群中。雖然這些工具可以每秒數(shù)百行或更多行的速度寫入數(shù)據(jù)，但是Hive只能每隔15分鐘到一個(gè)小時(shí)添加一次分區(qū)。頻繁添加分區(qū)會(huì)很快導(dǎo)致表中大量的分區(qū)。因此通常使用這些工具將數(shù)據(jù)流式傳輸?shù)浆F(xiàn)有分區(qū)中，但是這會(huì)使讀者感到臟讀（也就是說，他們將在開始查詢后看到寫入的數(shù)據(jù)），并將許多小文件留在目錄中，這將給NameNode帶來壓力。通過事務(wù)功能，同時(shí)允許讀者獲得一致的數(shù)據(jù)視圖并避免過多的文件。
尺寸變化緩慢。在典型的星型模式數(shù)據(jù)倉(cāng)庫(kù)中，維度表隨時(shí)間緩慢變化。例如，零售商將開設(shè)新商店，需要將其添加到商店表中，或者現(xiàn)有商店可能會(huì)更改其平方英尺或某些其他跟蹤的特征。這些更改導(dǎo)致插入單個(gè)記錄或更新 記錄（取決于所選策略）。
數(shù)據(jù)重述。有時(shí)發(fā)現(xiàn)收集的數(shù)據(jù)不正確，需要更正。從Hive 0.14開始，可以通過INSERT，UPDATE和 DELETE支持這些用例 。
Hive事務(wù)表局限性
雖然Hive支持了具有ACID語義的事務(wù)，但是在使用起來，并沒有像在MySQL中使用那樣方便，有很多局限性。原因很簡(jiǎn)單，畢竟Hive的設(shè)計(jì)目標(biāo)不是為了支持事務(wù)操作，而是支持分析操作，且最終基于HDFS的底層存儲(chǔ)機(jī)制使得文件的增加刪除修改操作需要?jiǎng)右恍┬⌒乃肌＞唧w限制如下：
尚不支持BEGIN，COMMIT和ROLLBACK。所有語言操作都是自動(dòng)提交的。
僅支持ORC文件格式（STORED AS ORC）。
默認(rèn)情況下事務(wù)配置為關(guān)閉。需要配置參數(shù)開啟使用。
表必須是分桶表（Bucketed）才可以使用事務(wù)功能。
表參數(shù)transactional必須為true；
外部表不能成為ACID表，不允許從非ACID會(huì)話讀取/寫入ACID表。
案例：創(chuàng)建使用Hive事務(wù)表
如果不做任何配置修改，直接針對(duì)Hive中已有的表進(jìn)行Update、Delete、Insert操作，可以發(fā)現(xiàn)，只有insert語句可以執(zhí)行，Update和Delete操作會(huì)報(bào)錯(cuò)。
Insert插入操作能夠成功的原因在于，底層是直接把數(shù)據(jù)寫在一個(gè)新的文件中的。
下面看一下如何在Hive中配置開啟事務(wù)表，并且進(jìn)行操作
--Hive中事務(wù)表的創(chuàng)建使用
--1、開啟事務(wù)配置（可以使用set設(shè)置當(dāng)前session生效 也可以配置在hive-site.xml中）
set hive.support.concurrency = true; --Hive是否支持并發(fā)
set hive.enforce.bucketing = true; --從Hive2.0開始不再需要 是否開啟分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --動(dòng)態(tài)分區(qū)模式 非嚴(yán)格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore實(shí)例上運(yùn)行啟動(dòng)線程和清理線程
set hive.compactor.worker.threads = 1; --在此metastore實(shí)例上運(yùn)行多少個(gè)壓縮程序工作線程。
--2、創(chuàng)建Hive事務(wù)表
create table trans_student(
id int,
name String,
age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--3、針對(duì)事務(wù)表進(jìn)行insert update delete操作
insert into trans_student (id, name, age)
values (1,"allen",18);
update trans_student
set age = 20
where id = 1;
delete from trans_student where id =1;
select *
from trans_student;
3.5 Hive View視圖
View的概念
Hive中的視圖（view）是一種虛擬表，只保存定義，不實(shí)際存儲(chǔ)數(shù)據(jù)。通常從真實(shí)的物理表查詢中創(chuàng)建生成視圖，也可以從已經(jīng)存在的視圖上創(chuàng)建新視圖。
創(chuàng)建視圖時(shí)，將凍結(jié)視圖的架構(gòu)，如果刪除或更改基礎(chǔ)表，則視圖將失敗，并且視圖不能存儲(chǔ)數(shù)據(jù)，操作數(shù)據(jù)，只能查詢。
概況起來就是：視圖是用來簡(jiǎn)化操作的，它其實(shí)是一張?zhí)摫恚谝晥D中不緩沖記錄，也沒有提高查詢性能。
View相關(guān)語法
--hive中有一張真實(shí)的基礎(chǔ)表t_usa_covid19
select *
from itcast.t_usa_covid19;
--1、創(chuàng)建視圖
create view v_usa_covid19 as select count_date, county,state,deaths from t_usa_covid19 limit 5;
--能否從已有的視圖中創(chuàng)建視圖呢 可以的
create view v_usa_covid19_from_view as select * from v_usa_covid19 limit 2;
--2、顯示當(dāng)前已有的視圖
show tables;
show views;--hive v2.2.0之后支持
--3、視圖的查詢使用
select *
from v_usa_covid19;
--能否插入數(shù)據(jù)到視圖中呢？
--不行 報(bào)錯(cuò) SemanticException:A view cannot be used as target table for LOAD or INSERT
insert into v_usa_covid19 select count_date,county,state,deaths from t_usa_covid19;
--4、查看視圖定義
show create table v_usa_covid19;
--5、刪除視圖
drop view v_usa_covid19_from_view;
--6、更改視圖屬性
alter view v_usa_covid19 set TBLPROPERTIES ('comment' = 'This is a view');
--7、更改視圖定義
alter view v_usa_covid19 as select county,deaths from t_usa_covid19 limit 2;
View的好處
1、將真實(shí)表中特定的列數(shù)據(jù)提供給用戶，保護(hù)數(shù)據(jù)隱式
--通過視圖來限制數(shù)據(jù)訪問可以用來保護(hù)信息不被隨意查詢: create table userinfo(firstname string, lastname string, ssn string, password string); create view safer_user_info as select firstname, lastname from userinfo; --可以通過where子句限制數(shù)據(jù)訪問，比如，提供一個(gè)員工表視圖，只暴露來自特定部門的員工信息: create table employee(firstname string, lastname string, ssn string, password string, department string); create view techops_employee as select firstname, lastname, ssn from userinfo where department = 'java';
2、降低查詢的復(fù)雜度，優(yōu)化查詢語句
--使用視圖優(yōu)化嵌套查詢
from (
select * from people join cart
on(cart.pepople_id = people.id) where firstname = 'join'
)a select a.lastname where a.id = 3;
--把嵌套子查詢變成一個(gè)視圖
create view shorter_join as
select * from people join cart
on (cart.pepople_id = people.id) where firstname = 'join';
--基于視圖查詢
select lastname from shorter_join where id = 3;
3.6 物化視圖materialized views
物化視圖概念
在傳統(tǒng)的數(shù)據(jù)庫(kù)領(lǐng)域基本已經(jīng)都實(shí)現(xiàn)了物化視圖, 屬于數(shù)據(jù)庫(kù)的高級(jí)功能。物化視圖（Materialized View）是一個(gè)包括查詢結(jié)果的數(shù)據(jù)庫(kù)對(duì)像，可以用于預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作的結(jié)果。這樣，在執(zhí)行查詢時(shí)，就可以避免進(jìn)行這些耗時(shí)的操作，而從快速的得到結(jié)果。使用物化視圖的目的就是通過預(yù)計(jì)算，提高查詢性能，當(dāng)然需要占用一定的存儲(chǔ)空間。
但是在SQL On Hadoop領(lǐng)域里支持這個(gè)特性的還不多，比較令人期待。Hive3.0開始嘗試引入物化視圖，并提供對(duì)于物化視圖的查詢自動(dòng)重寫（基于Apache Calcite實(shí)現(xiàn)）。值得注意的是，3.0中提供了物化視圖存儲(chǔ)選擇機(jī)制，可以本地存儲(chǔ)在hive，同時(shí)可以通過用戶自定義storage handlers存儲(chǔ)在其他系統(tǒng)（如Druid）。
Hive引入物化視圖的目的就是為了優(yōu)化數(shù)據(jù)查詢?cè)L問的效率,相當(dāng)于從數(shù)據(jù)預(yù)處理的角度優(yōu)化數(shù)據(jù)訪問。Hive從3.0丟棄了index索引的語法支持，推薦使用物化視圖和列式存儲(chǔ)文件格式來加快查詢的速度。
物化視圖、視圖區(qū)別
視圖是虛擬的，邏輯存在的，只有定義沒有存儲(chǔ)數(shù)據(jù)。
物化視圖是真實(shí)的，物理存在的，里面存儲(chǔ)著預(yù)計(jì)算的數(shù)據(jù)。
不同于視圖，物化視圖能夠緩存數(shù)據(jù)，在創(chuàng)建物化視圖的時(shí)候就把數(shù)據(jù)緩存起來了，hive把物化視圖當(dāng)成一張“表”，將數(shù)據(jù)緩存。而視圖只是創(chuàng)建一個(gè)虛表，只有表結(jié)構(gòu)，沒有數(shù)據(jù)，實(shí)際查詢的時(shí)候再去改寫SQL去訪問實(shí)際的數(shù)據(jù)表。
視圖的目的是簡(jiǎn)化降低查詢的復(fù)雜度，而物化視圖的目的是提高查詢性能。
物化視圖語法
--物化視圖的創(chuàng)建語法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
[DISABLE REWRITE]
[COMMENT materialized_view_comment]
[PARTITIONED ON (col_name, ...)]
[CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
AS SELECT ...;
語法說明：
（1）物化視圖創(chuàng)建后，select查詢執(zhí)行數(shù)據(jù)自動(dòng)落地，"自動(dòng)"也即在query的執(zhí)行期間，任何用戶對(duì)該物化視圖是不可見的
（2）默認(rèn)該物化視圖可被用于查詢優(yōu)化器optimizer查詢重寫（在物化視圖創(chuàng)建期間可以通過DISABLE REWRITE參數(shù)設(shè)置禁止使用）
（3）SerDe和storage format非強(qiáng)制參數(shù)，可以用戶配置，默認(rèn)可用hive.materializedview.serde、 hive.materializedview.fileformat
（4）物化視圖可以使用custom storage handlers存儲(chǔ)在外部系統(tǒng)（如druid）例如：
CREATE MATERIALIZED VIEW druid_wiki_mv
STORED AS 'org.apache.hadoop.hive.druid.DruidStorageHandler'
AS
SELECT __time, page, user, c_added, c_removed
FROM src;
目前支持物化視圖的drop和show操作，后續(xù)會(huì)增加其他操作
-- Drops a materialized view DROP MATERIALIZED VIEW [db_name.]materialized_view_name; -- Shows materialized views (with optional filters) SHOW MATERIALIZED VIEWS [IN database_name]; -- Shows information about a specific materialized view DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;
當(dāng)數(shù)據(jù)源變更（新數(shù)據(jù)插入inserted、數(shù)據(jù)修改modified），物化視圖也需要更新以保持?jǐn)?shù)據(jù)一致性，目前需要用戶主動(dòng)觸發(fā)rebuild
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;
基于物化視圖的查詢重寫
物化視圖創(chuàng)建后即可用于相關(guān)查詢的加速，用戶提交查詢query，若該query經(jīng)過重寫后可命中已建視圖，則被重寫命中相關(guān)已建視圖實(shí)現(xiàn)查詢加速。
是否重寫查詢使用物化視圖可以通過全局參數(shù)控制，默認(rèn)為true：
SET hive.materializedview.rewriting=true;
用戶可選擇性的失能物化視圖的重寫：
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;
案例：物化視圖查詢重寫
--1、新建一張事務(wù)表 student_trans
set hive.support.concurrency = true; --Hive是否支持并發(fā)
set hive.enforce.bucketing = true; --從Hive2.0開始不再需要 是否開啟分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --動(dòng)態(tài)分區(qū)模式 非嚴(yán)格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore實(shí)例上運(yùn)行啟動(dòng)線程和清理線程
set hive.compactor.worker.threads = 1; --在此metastore實(shí)例上運(yùn)行多少個(gè)壓縮程序工作線程。
CREATE TABLE student_trans (
sno int,
sname string,
sdept string)
clustered by (sno) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--2、導(dǎo)入數(shù)據(jù)到student_trans中
insert overwrite table student_trans
select sno,sname,sdept
from student;
select *
from student_trans;
--3、對(duì)student_trans建立聚合物化視圖
CREATE MATERIALIZED VIEW student_trans_agg
AS SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;
--注意 這里當(dāng)執(zhí)行CREATE MATERIALIZED VIEW，會(huì)啟動(dòng)一個(gè)MR對(duì)物化視圖進(jìn)行構(gòu)建
--可以發(fā)現(xiàn)當(dāng)下的數(shù)據(jù)庫(kù)中有了一個(gè)物化視圖
show tables;
show materialized views;
--4、對(duì)原始表student_trans查詢
--由于會(huì)命中物化視圖，重寫query查詢物化視圖，查詢速度會(huì)加快（沒有啟動(dòng)MR，只是普通的table scan）
SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;
--5、查詢執(zhí)行計(jì)劃可以發(fā)現(xiàn) 查詢被自動(dòng)重寫為TableScan alias: itcast.student_trans_agg
--轉(zhuǎn)換成了對(duì)物化視圖的查詢 提高了查詢效率
explain SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;
第四章 Hive DDL其他語法
4.1 Database|schema（數(shù)據(jù)庫(kù)） DDL操作
Create database
Hive中DATABASE的概念和RDBMS中類似，我們稱之為數(shù)據(jù)庫(kù)。在Hive中， DATABASE和SCHEMA是可互換的，使用DATABASE或SCHEMA都可以。
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
COMMENT：數(shù)據(jù)庫(kù)的注釋說明語句
LOCATION：指定數(shù)據(jù)庫(kù)在HDFS存儲(chǔ)位置，默認(rèn)/user/hive/warehouse
WITH DBPROPERTIES：用于指定一些數(shù)據(jù)庫(kù)的屬性配置。
下面創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)：itheima
create database if not exists itheima
comment "this is my first db"
with dbproperties ('createdBy'='AllenWoon');
注意：使用location指定路徑的時(shí)候，最好是一個(gè)新創(chuàng)建的空文件夾。
Describe database
Hive中的DESCRIBE DATABASE語句用于顯示Hive中數(shù)據(jù)庫(kù)的名稱，其注釋（如果已設(shè)置）及其在文件系統(tǒng)上的位置等信息。
DESCRIBE DATABASE/SCHEMA [EXTENDED] db_name;
EXTENDED：用于顯示更多信息。
Use database
Hive中的USE DATABASE語句用于選擇特定的數(shù)據(jù)庫(kù),切換當(dāng)前會(huì)話使用哪一個(gè)數(shù)據(jù)庫(kù)進(jìn)行操作。
USE database_name;
Drop database
Hive中的DROP DATABASE語句用于刪除（刪除）數(shù)據(jù)庫(kù)。
默認(rèn)行為是RESTRICT，這意味著僅在數(shù)據(jù)庫(kù)為空時(shí)才刪除它。要?jiǎng)h除帶有表的數(shù)據(jù)庫(kù)，我們可以使用CASCADE。
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
Alter database
Hive中的ALTER DATABASE語句用于更改與Hive中的數(shù)據(jù)庫(kù)關(guān)聯(lián)的元數(shù)據(jù)。
--更改數(shù)據(jù)庫(kù)屬性 ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); --更改數(shù)據(jù)庫(kù)所有者 ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; --更改數(shù)據(jù)庫(kù)位置 ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;
5.2 Table（表）DDL操作
Describe table
Hive中的DESCRIBE table語句用于顯示Hive中表的元數(shù)據(jù)信息。
describe formatted [db_name.]table_name; describe extended [db_name.]table_name;
如果指定了EXTENDED關(guān)鍵字，則它將以Thrift序列化形式顯示表的所有元數(shù)據(jù)。如果指定了FORMATTED關(guān)鍵字，則它將以表格格式顯示元數(shù)據(jù)。
Drop table
DROP TABLE刪除該表的元數(shù)據(jù)和數(shù)據(jù)。如果已配置垃圾桶（且未指定PURGE），則該表對(duì)應(yīng)的數(shù)據(jù)實(shí)際上將移動(dòng)到.Trash/Current目錄，而元數(shù)據(jù)完全丟失。刪除EXTERNAL表時(shí)，該表中的數(shù)據(jù)不會(huì)從文件系統(tǒng)中刪除，只刪除元數(shù)據(jù)。
如果指定了PURGE，則表數(shù)據(jù)不會(huì)進(jìn)入.Trash/Current目錄，跳過垃圾桶直接被刪除。因此如果DROP失敗，則無法挽回該表數(shù)據(jù)。
DROP TABLE [IF EXISTS] table_name [PURGE]; -- (Note: PURGE available in Hive 0.14.0 and later)
Truncate table
從表中刪除所有行。可以簡(jiǎn)單理解為清空表的所有數(shù)據(jù)但是保留表的元數(shù)據(jù)結(jié)構(gòu)。如果HDFS啟用了垃圾桶，數(shù)據(jù)將被丟進(jìn)垃圾桶，否則將被刪除。
TRUNCATE [TABLE] table_name;
Alter table
--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name;
--2、更改表屬性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
--更改表注釋
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
--3、更改SerDe屬性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )];
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
--移除SerDe屬性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );
--4、更改表的文件存儲(chǔ)格式 該操作僅更改表元數(shù)據(jù)。現(xiàn)有數(shù)據(jù)的任何轉(zhuǎn)換都必須在Hive之外進(jìn)行。
ALTER TABLE table_name SET FILEFORMAT file_format;
--5、更改表的存儲(chǔ)位置路徑
ALTER TABLE table_name SET LOCATION "new location";
--6、更改列名稱/類型/位置/注釋
CREATE TABLE test_change (a int, b int, c int);
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is: b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is: c1 int, b int, a2 string.
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';
--7、添加/替換列
--使用ADD COLUMNS，您可以將新列添加到現(xiàn)有列的末尾但在分區(qū)列之前。
--REPLACE COLUMNS 將刪除所有現(xiàn)有列，并添加新的列集。
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);
4.3 Partition（分區(qū)）DDL操作
Add partition
分區(qū)值僅在為字符串時(shí)才應(yīng)加引號(hào)。位置必須是數(shù)據(jù)文件所在的目錄。
ADD PARTITION會(huì)更改表元數(shù)據(jù)，但不會(huì)加載數(shù)據(jù)。如果分區(qū)位置中不存在數(shù)據(jù)，查詢將不會(huì)返回任何結(jié)果。
--1、增加分區(qū)
ALTER TABLE table_name ADD PARTITION (dt='20170101') location
'/user/hadoop/warehouse/table_name/dt=20170101';
--一次添加一個(gè)分區(qū)
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';
--一次添加多個(gè)分區(qū)
rename partition
--2、重命名分區(qū) ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec; ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAME TO PARTITION (dt='20080809');
delete partition
可以使用ALTER TABLE DROP PARTITION刪除表的分區(qū)。這將刪除該分區(qū)的數(shù)據(jù)和元數(shù)據(jù)。
--3、刪除分區(qū) ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us'); ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE; --直接刪除數(shù)據(jù) 不進(jìn)垃圾桶
msck partition
Hive將每個(gè)表的分區(qū)列表信息存儲(chǔ)在其metastore中。但是，如果將新分區(qū)直接添加到HDFS（例如通過使用hadoop fs -put命令）或從HDFS中直接刪除分區(qū)文件夾，則除非用戶ALTER TABLE table_name ADD/DROP PARTITION在每個(gè)新添加的分區(qū)上運(yùn)行命令，否則metastore（也就是Hive）將不會(huì)意識(shí)到分區(qū)信息的這些更改。
但是，用戶可以使用修復(fù)表選項(xiàng)運(yùn)行metastore check命令。
--4、修復(fù)分區(qū) MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];
MSC命令的默認(rèn)選項(xiàng)是“添加分區(qū)”。使用此選項(xiàng)，它將把HDFS上存在但元存儲(chǔ)中不存在的所有分區(qū)添加到元存儲(chǔ)中。DROP PARTITIONS選項(xiàng)將從已經(jīng)從HDFS中刪除的metastore中刪除分區(qū)信息。SYNC PARTITIONS選項(xiàng)等效于調(diào)用ADD和DROP PARTITIONS。
如果存在大量未跟蹤的分區(qū)，則可以批量運(yùn)行MSCK REPAIR TABLE，以避免OOME（內(nèi)存不足錯(cuò)誤）。
alter partition
--5、修改分區(qū) --更改分區(qū)文件存儲(chǔ)格式 ALTER TABLE table_name PARTITION (dt='2008-08-09') SET FILEFORMAT file_format; --更改分區(qū)位置 ALTER TABLE table_name PARTITION (dt='2008-08-09') SET LOCATION "new location";
第五章、Hive Show顯示語法
Show相關(guān)的語句提供了一種查詢Hive metastore的方法。可以幫助用戶查詢相關(guān)信息。
--1、顯示所有數(shù)據(jù)庫(kù) SCHEMAS和DATABASES的用法 功能一樣 show databases; show schemas; --2、顯示當(dāng)前數(shù)據(jù)庫(kù)所有表/視圖/物化視圖/分區(qū)/索引 show tables; SHOW TABLES [IN database_name]; --指定某個(gè)數(shù)據(jù)庫(kù) --3、顯示當(dāng)前數(shù)據(jù)庫(kù)下所有視圖 Show Views; SHOW VIEWS 'test_*'; -- show all views that start with "test_" SHOW VIEWS FROM test1; -- show views from database test1 SHOW VIEWS [IN/FROM database_name]; --4、顯示當(dāng)前數(shù)據(jù)庫(kù)下所有物化視圖 SHOW MATERIALIZED VIEWS [IN/FROM database_name]; --5、顯示表分區(qū)信息，分區(qū)按字母順序列出，不是分區(qū)表執(zhí)行該語句會(huì)報(bào)錯(cuò) show partitions table_name; --6、顯示表/分區(qū)的擴(kuò)展信息 SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name; show table extended like student; --7、顯示表的屬性信息 SHOW TBLPROPERTIES table_name; show tblproperties student; --8、顯示表、視圖的創(chuàng)建語句 SHOW CREATE TABLE ([db_name.]table_name|view_name); show create table student; --9、顯示表中的所有列，包括分區(qū)列。 SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]; show columns in student; --10、顯示當(dāng)前支持的所有自定義和內(nèi)置的函數(shù) show functions; --11、Describe desc --查看表信息 desc extended table_name; --查看表信息（格式化美觀） desc formatted table_name; --查看數(shù)據(jù)庫(kù)相關(guān)信息 describe database database_name;
作者：王陸
出處：https://www.cnblogs.com/wkfvawl/
-------------------------------------------
個(gè)性簽名：罔談彼短，靡持己長(zhǎng)。做一個(gè)謙遜愛學(xué)的人！
本站使用「署名 4.0 國(guó)際」創(chuàng)作共享協(xié)議，轉(zhuǎn)載請(qǐng)?jiān)谖恼旅黠@位置注明作者及出處。鑒于博主處于考研復(fù)習(xí)期間，有什么問題請(qǐng)?jiān)谠u(píng)論區(qū)中提出，博主盡可能當(dāng)天回復(fù)，加微信好友請(qǐng)注明原因
總結(jié)
以上是生活随笔為你收集整理的HiveSQL 数据定义语言（DDL）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： string_view理解与用法（二）
- 下一篇： 脉脉高聘：超八成猎头不到35岁，六成猎头