四、Hive DDL建表高階

Hive內(nèi)、外部表

什么是內(nèi)部表

內(nèi)部表（Internal table）也稱為被Hive擁有和管理的托管表（Managed table）。默認(rèn)情況下創(chuàng)建的表就是內(nèi)部表，Hive擁有該表的結(jié)構(gòu)和文件。換句話說(shuō)，Hive完全管理表（元數(shù)據(jù)和數(shù)據(jù)）的生命周期，類似于RDBMS中的表。

當(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;來(lái)獲取表的描述信息，從中可以看出表的類型。

什么是外部表

外部表（External table）中的數(shù)據(jù)不是Hive擁有或管理的，只管理表元數(shù)據(jù)的生命周期。要?jiǎng)?chuàng)建一個(gè)外部表，需要使用EXTERNAL語(yǔ)法關(guān)鍵字。

刪除外部表只會(huì)刪除元數(shù)據(jù)，而不會(huì)刪除實(shí)際數(shù)據(jù)。在Hive外部仍然可以訪問(wèn)實(shí)際數(shù)據(jù)。

而且外部表更為方便的是可以搭配location語(yǔ)法指定數(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;來(lái)獲取表的描述信息，從中可以看出表的類型。

內(nèi)部表、外部表差異

無(wú)論內(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)需要通過(guò)Hive完全管理控制表的整個(gè)生命周期時(shí)，請(qǐng)使用內(nèi)部表。

當(dāng)文件已經(jīng)存在或位于遠(yuǎn)程位置時(shí)，請(qǐng)使用外部表，因?yàn)榧词箘h除表，文件也會(huì)被保留。

Hive分區(qū)表

分區(qū)表的引入、產(chǎn)生背景

現(xiàn)有6份數(shù)據(jù)文件，分別記錄了《王者榮耀》中6種位置的英雄相關(guān)信息。現(xiàn)要求通過(guò)建立一張表t_all_hero，把6份文件同時(shí)映射加載。

加載數(shù)據(jù)文件到HDFS指定路徑下：

現(xiàn)要求查詢r(jià)ole_main主要定位是射手并且hp_max最大生命大于6000的有幾個(gè)，sql語(yǔ)句如下：

思考一下：where語(yǔ)句的背后需要進(jìn)行全表掃描才能過(guò)濾出結(jié)果，對(duì)于hive來(lái)說(shuō)需要掃描表下面的每一個(gè)文件。如果數(shù)據(jù)文件特別多的話，效率很慢也沒(méi)必要。本需求中，只需要掃描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ū)表建表語(yǔ)法：

針對(duì)《王者榮耀》英雄數(shù)據(jù)，重新創(chuàng)建一張分區(qū)表t_all_hero_part，以role角色作為分區(qū)字段。

需要注意：分區(qū)字段不能是表中已經(jīng)存在的字段，因?yàn)榉謪^(qū)字段最終也會(huì)以虛擬字段的形式顯示在表結(jié)構(gòu)上。

添加圖片注釋，不超過(guò) 140 字（可選）

分區(qū)表數(shù)據(jù)加載--靜態(tài)分區(qū)

所謂靜態(tài)分區(qū)指的是分區(qū)的字段值是由用戶在加載數(shù)據(jù)的時(shí)候手動(dòng)指定的。

語(yǔ)法如下：

Local表示數(shù)據(jù)是位于本地文件系統(tǒng)還是HDFS文件系統(tǒng)。關(guān)于load語(yǔ)句后續(xù)詳細(xì)展開(kāi)講解。

靜態(tài)加載數(shù)據(jù)操作如下，文件都位于Hive服務(wù)器所在機(jī)器本地文件系統(tǒng)上。

分區(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)推斷出來(lái)的。核心語(yǔ)法就是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ù)表示開(kāi)啟動(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

執(zhí)行動(dòng)態(tài)分區(qū)插入

動(dòng)態(tài)分區(qū)插入時(shí)，分區(qū)值是根據(jù)查詢返回字段位置自動(dòng)推斷的。

分區(qū)表的本質(zhì)

外表上看起來(lái)分區(qū)表好像沒(méi)多大變化，只不過(guò)多了一個(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ū)過(guò)濾，查詢指定分區(qū)的數(shù)據(jù)，避免全表掃描。

比如：查詢英雄主要定位是射手并且最大生命大于6000的個(gè)數(shù)。使用分區(qū)表查詢和使用非分區(qū)表進(jìn)行查詢，SQL如下：

想一想：底層執(zhí)行性能來(lái)說(shuō)，分區(qū)表的優(yōu)勢(shì)在哪里？

分區(qū)表的注意事項(xiàng)

1. 分區(qū)表不是建表的必要語(yǔ)法規(guī)則，是一種優(yōu)化手段表，可選；

2. 分區(qū)字段不能是表中已有的字段，不能重復(fù)；

3. 分區(qū)字段是虛擬字段，其數(shù)據(jù)并不存儲(chǔ)在底層的文件中；

4. 分區(qū)字段值的確定來(lái)自于用戶價(jià)值數(shù)據(jù)手動(dòng)指定（靜態(tài)分區(qū)）或者根據(jù)查詢結(jié)果位置自動(dòng)推斷（動(dòng)態(tài)分區(qū)）

5. Hive支持多重分區(qū)，也就是說(shuō)在分區(qū)的基礎(chǔ)上繼續(xù)分區(qū)，劃分更加細(xì)粒度

多重分區(qū)表

通過(guò)建表語(yǔ)句中關(guān)于分區(qū)的相關(guān)語(yǔ)法可以發(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的角度來(lái)看就是文件夾下繼續(xù)劃分子文件夾。比如：把全國(guó)人口數(shù)據(jù)首先根據(jù)省進(jìn)行分區(qū)，然后根據(jù)市進(jìn)行劃分，如果你需要甚至可以繼續(xù)根據(jù)區(qū)縣再劃分，此時(shí)就是3分區(qū)表。

多分區(qū)表的數(shù)據(jù)插入和查詢使用

Hive分桶表

分桶表的概念

分桶表也叫做桶表，源自建表語(yǔ)法中bucket單詞。是一種用于優(yōu)化查詢而設(shè)計(jì)的表類型。該功能可以讓數(shù)據(jù)分解為若干個(gè)部分易于管理。

在分桶時(shí)，我們要指定根據(jù)哪個(gè)字段將數(shù)據(jù)分為幾桶（幾個(gè)部分）。默認(rèn)規(guī)則是：Bucket number = hash_function(bucketing_column) mod num_buckets。

可以發(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值。

分桶表的語(yǔ)法

其中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ù)格式如下所示：

字段含義如下：count_date（統(tǒng)計(jì)日期）,county（縣）,state（州）,fips（縣編碼code）,cases（累計(jì)確診病例）,deaths（累計(jì)死亡病例）。

根據(jù)state州把數(shù)據(jù)分為5桶，建表語(yǔ)句如下：

在創(chuàng)建分桶表時(shí)，還可以指定分桶內(nèi)的數(shù)據(jù)排序規(guī)則

分桶表的數(shù)據(jù)加載

到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í)，減少全表掃描

1. JOIN時(shí)可以提高M(jìn)R程序效率，減少笛卡爾積數(shù)量 對(duì)于JOIN操作兩個(gè)表有一個(gè)相同的列，如果對(duì)這兩個(gè)表都進(jìn)行了分桶操作。那么將保存相同列值的桶進(jìn)行JOIN操作就可以，可以大大較少JOIN的數(shù)據(jù)量。

2. 分桶表數(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ì)有效的工作和研究方法。