三，分區(qū)表

1.靜態(tài)分區(qū)

CREATE TABLE IF NOT EXISTS salgrade2 (
GRADE int,
LOSAL int,
HISAL int
) partitioned by (day string)
row format delimited fields terminated by '\t'
location '/data/inner/ODS/01/salgrade2';

CREATE TABLE IF NOT EXISTS salgrade3 (
GRADE int,
LOSAL int,
HISAL int
) partitioned by (day string,code string)
row format delimited fields terminated by '\t'
location '/data/inner/ODS/01/salgrade3';

// 加載一次，文件就要put一次，每次加載都會(huì)直接移動(dòng)源文件
hadoop fs -put ./salgrade.txt /data/inner/RAW/01/salgrade

//另外指定分區(qū)名
load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade2 partition (day='99990101');
load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade3 partition (day='99990101',code='01');
load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade2 partition (day='20200703');

// 先進(jìn)行分區(qū)，再執(zhí)行腳本導(dǎo)入表的數(shù)據(jù)
alter table salgrade2 add partiton (day='99990102');
load data inpath '/data/inner/RAW/01/salgrade/salgrade.txt' into table salgrade2 partition (day='99990102');

// 需要加上hivevar
select * from salgrade2 where day='${YYYYMMDD}';
// 查詢具體分區(qū)下的數(shù)據(jù)
select * from salgrade2 where day='99990101';

2.動(dòng)態(tài)分區(qū)

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

create table if not exists salgrade4 like salgrade2;

insert overwrite table salgrade4 partition (day='20200520') select GRADE, LOSAL, HISAL from salgrade2 where day='99990101';

// 顯示如下內(nèi)容：
INFO : Number of reduce tasks is set to 0 since there is no reduce operator
INFO : number of splits:1
INFO : Submitting tokens for job: job_1593741356151_0001
INFO : The url to track the job: http://bd1601:8088/proxy/application_1593741356151_0001/
INFO : Starting Job = job_1593741356151_0001, Tracking URL = http://bd1601:8088/proxy/application_1593741356151_0001/
INFO : Kill Command = /opt/bdp/hadoop-2.6.5/bin/hadoop job -kill job_1593741356151_0001
INFO : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
INFO : 2020-07-03 08:09:51,082 Stage-1 map = 0%, reduce = 0%
INFO : 2020-07-03 08:10:12,868 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.06 sec
INFO : MapReduce Total cumulative CPU time: 3 seconds 60 msec
INFO : Ended Job = job_1593741356151_0001
INFO : Stage-4 is selected by condition resolver.
INFO : Stage-3 is filtered out by condition resolver.
INFO : Stage-5 is filtered out by condition resolver.
INFO : Moving data to: hdfs://bdp/bdp/hive/metastore/warehouse/salgrade4/day=20200520/.hive-staging_hive_2020-07-03_08-09-17_320_8093467842238301801-2/-ext-10000 from hdfs://bdp/bdp/hive/metastore/warehouse/salgrade4/day=20200520/.hive-staging_hive_2020-07-03_08-09-17_320_8093467842238301801-2/-ext-10002
INFO : Loading data to table default.salgrade4 partition (day=20200520) from hdfs://bdp/bdp/hive/metastore/warehouse/salgrade4/day=20200520/.hive-staging_hive_2020-07-03_08-09-17_320_8093467842238301801-2/-ext-10000
INFO : Partition default.salgrade4{day=20200520} stats: [numFiles=1, numRows=5, totalSize=59, rawDataSize=54]
No rows affected (60.04 seconds)

set hive.support.quoted.identifiers=none; `(membership_level|extra_info)?+.+
insert overwrite table salgrade4 partition (day) select * from salgrade2 where day='99990101';

CREATE EXTERNAL TABLE IF NOT EXISTS emp2 (
EMPNO int,
ENAME varchar(255),
JOB varchar(255),
MGR int,
HIREDATE date,
SAL decimal(10,0),
COMM decimal(10,0),
DEPTNO int
) partitioned by (id string)
row format delimited fields terminated by '\t'
location '/data/inner/ODS/01/emp2';

// 報(bào)錯(cuò)
insert overwrite table emp2 partition(id) select * from emp;
insert overwrite table emp2 partition(id) select *,EMPNO id from emp;

創(chuàng)建表：
CREATE TABLE IF NOT EXISTS salgrade_test (
GRADE int,
LOSAL int,
HISAL int,
day string
) row format delimited fields terminated by '\t'
location '/data/inner/ODS/01/salgrade_test';

在本地有一個(gè)文件/data/a.txt其中有如下文本內(nèi)容：
1 700 1200 10100101
2 1201 1400 10100101
3 1401 2000 10100101
4 2001 3000 10100102
5 3001 9999 10100102

加載數(shù)據(jù)：
load data inpath '/data/inner/ODS/01/salgrade_test/a.txt' into table salgrade_test;

插入數(shù)據(jù)到salgrade_test：
insert into table salgrade4 partition(day) select * from salgrade_test;

分區(qū)、分桶的作用：

我們知道在傳統(tǒng)的DBMs系統(tǒng)中，一般都具有表分區(qū)的功能，通過(guò)表分區(qū)能夠在特定的區(qū)域檢索數(shù)據(jù)，減少掃描成本，在一定程度上提高了查詢效率，當(dāng)然我們還可以通過(guò)進(jìn)一步在分區(qū)上建立索引，進(jìn)一步提高查詢效率。

在Hive中的數(shù)據(jù)倉(cāng)庫(kù)中，也有分區(qū)分桶的概念，在邏輯上，分區(qū)表與未分區(qū)表沒(méi)有區(qū)別，在物理上分區(qū)表會(huì)將數(shù)據(jù)按照分區(qū)間的列值存儲(chǔ)在表目錄的子目錄中，目錄名=“分區(qū)鍵=鍵值”。其中需要注意的是分區(qū)鍵的列值存儲(chǔ)在表目錄的子目錄中，目錄名=“分區(qū)鍵=鍵值”。其中需要注意的是分區(qū)鍵的值不一定要基于表的某一列（字段），它可以指定任意值，只要查詢的時(shí)候指定相應(yīng)的分區(qū)鍵來(lái)查詢即可。我們可以對(duì)分區(qū)進(jìn)行添加、刪除、重命名、清空等操作。

分桶則是指定分桶表的某一列，讓該列數(shù)據(jù)按照哈希取模的方式隨機(jī)、均勻的分發(fā)到各個(gè)桶文件中。因?yàn)榉滞安僮餍枰鶕?jù)某一列具體數(shù)據(jù)來(lái)進(jìn)行哈希取模操作，故指定的分桶列必須基于表中的某一列（字段）。分桶改變了數(shù)據(jù)的存儲(chǔ)方式，它會(huì)把哈希取模相同或者在某一個(gè)區(qū)間的數(shù)據(jù)行放在同一個(gè)桶文件中。如此一來(lái)便可以提高查詢效率。如果我們需要對(duì)兩張?jiān)谕粋€(gè)列上進(jìn)行了分桶操作的表進(jìn)行JOIN操作的時(shí)候，只需要對(duì)保存相同列值的通進(jìn)行JOIN操作即可。

還有一點(diǎn)需要點(diǎn)一下：在hive中的數(shù)據(jù)是存儲(chǔ)在hdfs中的，我們知道hdfs中的數(shù)據(jù)是不允許修改只能追加的，那么在hive中執(zhí)行數(shù)據(jù)修改的命令時(shí)，就只能先找到對(duì)應(yīng)的文件，讀取后執(zhí)行修改操作，然后重新寫一份文件。如果文件比較大，就需要大量的IO讀寫。在hive中采用了分桶的策略，只需要找到文件存放對(duì)應(yīng)的桶，然后讀取再修改寫入即可。

分區(qū)：

hive中分區(qū)分為 ： 單值分區(qū)、范圍分區(qū)。
單值分區(qū)： 靜態(tài)分區(qū) 動(dòng)態(tài)分區(qū)

如下所示，現(xiàn)在有一張persionrank表，記錄每個(gè)人的評(píng)級(jí)，有id、name、score字段。我們可以創(chuàng)建分區(qū)rank（rank不是表中的列，我們可以把它當(dāng)做虛擬列），并將相應(yīng)的數(shù)據(jù)導(dǎo)入指定分區(qū)（將數(shù)據(jù)插入指定目錄）。

單值分區(qū)：

單值靜態(tài)分區(qū)：導(dǎo)入數(shù)據(jù)時(shí)需要手動(dòng)指定分區(qū)
單值動(dòng)態(tài)分區(qū)：導(dǎo)入數(shù)據(jù)時(shí)，系統(tǒng)可以動(dòng)態(tài)判斷目標(biāo)分區(qū)

1.靜態(tài)分區(qū)創(chuàng)建：
直接在PARTITI1ONED BY后面跟上分區(qū)鍵、類型即可（指定的分區(qū)鍵不能出現(xiàn)在定義列名中）

CREATE [EXTERNAL] TABLE <table_name>
(<col_name> <data_type> [, <col_name> <data_type> ...])
-- 指定分區(qū)鍵和數(shù)據(jù)類型
PARTITIONED BY (<partition_key> <data_type>, ...)
[CLUSTERED BY ...]
[ROW FORMAT <row_format>]
[STORED AS TEXTFILE|ORC|CSVFILE]
[LOCATION '<file_path>']
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

2.靜態(tài)分區(qū)寫入：
-- 覆蓋寫入
INSERT OVERWRITE TABLE <table_name>
PARTITION (<partition_key>=<partition_value>[, <partition_key>=<partition_value>, ...])
SELECT <select_statement>;

-- 追加寫入
INSERT INTO TABLE <table_name>
PARTITION (<partition_key>=<partition_value>[, <partition_key>=<partition_value>, ...])
SELECT <select_statement>;

3.添加分區(qū)：
//只能添加分區(qū)列的值，不能添加分區(qū)列，如果是多個(gè)分區(qū)列，不能單獨(dú)添加其中一個(gè)
alter table tablename add partition(col=value)

4.刪除分區(qū)：
//可以刪除一個(gè)分區(qū)列，但是會(huì)把表中所有包含當(dāng)前分區(qū)列的數(shù)據(jù)全部刪除
alter table tablename drop partition(col=value)

5.修復(fù)分區(qū)：
//手動(dòng)向hdfs中創(chuàng)建分區(qū)目錄，添加數(shù)據(jù)，創(chuàng)建好hive的外表之后，無(wú)法加載數(shù)據(jù)，
//元數(shù)據(jù)中沒(méi)有相應(yīng)的記錄
msck repair table tablename

6.動(dòng)態(tài)分區(qū)創(chuàng)建：
創(chuàng)建方式與靜態(tài)分區(qū)表完全一樣，一張表可同時(shí)被靜態(tài)分區(qū)和動(dòng)態(tài)分區(qū)鍵分區(qū)，只是動(dòng)態(tài)分區(qū)鍵需要放在靜態(tài)分區(qū)鍵的后面（HDFS上的動(dòng)態(tài)分區(qū)目錄下不能包含靜態(tài)分區(qū)的子目錄），如下spk即static partition key（靜態(tài)分區(qū)鍵），dpk為dynamic partition key（動(dòng)態(tài)分區(qū)鍵）

CREATE TABLE <table_name>
PARTITIONED BY ([<spk> <data_type>, ... ,] <dpk> <data_type>, [<dpk>
<data_type>,...]);

7.動(dòng)態(tài)分區(qū)寫入：
根據(jù)表中的某一個(gè)列值來(lái)確定hdfs存儲(chǔ)的目錄：

優(yōu)點(diǎn)：
動(dòng)態(tài)可變，不需要人為控制。假如設(shè)定的是日期，那么每一天的數(shù)據(jù)會(huì)單獨(dú)存儲(chǔ)在一個(gè)文件夾中

缺點(diǎn)：
需要依靠MR完成，執(zhí)行比較慢

靜態(tài)分區(qū)鍵要用 <spk>=<value> 指定分區(qū)值；動(dòng)態(tài)分區(qū)只需要給出分出分區(qū)鍵名稱 <dpk>。

-- 開(kāi)啟動(dòng)態(tài)分區(qū)支持，并設(shè)置最大分區(qū)數(shù)
set hive.exec.dynamic.partition=true;
//set hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.max.dynamic.partitions=2000;

insert into table1 select 普通字段 分區(qū)字段 from table2
范圍分區(qū)：

單值分區(qū)每個(gè)分區(qū)對(duì)應(yīng)于分區(qū)鍵的一個(gè)取值，而每個(gè)范圍分區(qū)則對(duì)應(yīng)分區(qū)鍵的一個(gè)區(qū)間，只要落在指定區(qū)間內(nèi)的記錄都被存儲(chǔ)在對(duì)應(yīng)的分區(qū)下。分區(qū)范圍需要手動(dòng)指定，分區(qū)的范圍為前閉后開(kāi)區(qū)間 [最小值, 最大值)。最后出現(xiàn)的分區(qū)可以使用 MAXVALUE 作為上限，MAXVALUE 代表該分區(qū)鍵的數(shù)據(jù)類型所允許的最大值。

CREATE [EXTERNAL] TABLE <table_name>
(<col_name> <data_type>, <col_name> <data_type>, ...)
PARTITIONED BY RANGE (<partition_key> <data_type>, ...)
(PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>),
[PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>),
...
]
PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>|MAXVALUE)
)
[ROW FORMAT <row_format>] [STORED AS TEXTFILE|ORC|CSVFILE]
[LOCATION '<file_path>']
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)];
多個(gè)范圍分區(qū)鍵的情況：

DROP TABLE IF EXISTS test_demo;
CREATE TABLE test_demo (value INT)
PARTITIONED BY RANGE (id1 INT, id2 INT, id3 INT)
(
-- id1在(--∞,5]之間，id2在(-∞,105]之間，id3在(-∞,205]之間
PARTITION p5_105_205 VALUES LESS THAN (5, 105, 205),
-- id1在(--∞,5]之間，id2在(-∞,105]之間，id3在(205,215]之間
PARTITION p5_105_215 VALUES LESS THAN (5, 105, 215),
PARTITION p5_115_max VALUES LESS THAN (5, 115, MAXVALUE),
PARTITION p10_115_205 VALUES LESS THAN (10, 115, 205),
PARTITION p10_115_215 VALUES LESS THAN (10, 115, 215),
PARTITION pall_max values less than (MAXVALUE, MAXVALUE, MAXVALUE)
);
分桶：

對(duì)Hive(Inceptor)表分桶可以將表中記錄按分桶鍵的哈希值分散進(jìn)多個(gè)文件中，這些小文件稱為桶。

1.創(chuàng)建分桶表：
分桶表的建表有三種方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，單值分區(qū)表不能用 CREATE TABLE AS SELECT 建表。這里以直接建表為例：

CREATE [EXTERNAL] TABLE <table_name>
(<col_name> <data_type> [, <col_name> <data_type> ...])]
[PARTITIONED BY ...]
CLUSTERED BY (<col_name>)
[SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])]

    INTO <num_buckets> BUCKETS  
    
[ROW FORMAT <row_format>] 
[STORED AS TEXTFILE|ORC|CSVFILE]
[LOCATION '<file_path>']    
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

分桶鍵只能有一個(gè)即<col_name>。表可以同時(shí)分區(qū)和分桶，當(dāng)表分區(qū)時(shí)，每個(gè)分區(qū)下都會(huì)有<num_buckets> 個(gè)桶。我們也可以選擇使用 SORTED BY … 在桶內(nèi)排序，排序鍵和分桶鍵無(wú)需相同。ASC 為升序選項(xiàng)，DESC 為降序選項(xiàng)，默認(rèn)排序方式是升序。<num_buckets> 指定分桶個(gè)數(shù)，也就是表目錄下小文件的個(gè)數(shù)。

2.向分桶表中寫數(shù)據(jù)：
因?yàn)榉滞氨碓趧?chuàng)建的時(shí)候只會(huì)定義Scheme，且寫入數(shù)據(jù)的時(shí)候不會(huì)自動(dòng)進(jìn)行分桶、排序，需要人工先進(jìn)行分桶、排序后再寫入數(shù)據(jù)。確保目標(biāo)表中的數(shù)據(jù)和它定義的分布一致。

目前有兩種方式往分桶表中插入數(shù)據(jù)：

方法一：打開(kāi)enforce bucketing開(kāi)關(guān)。
SET hive.enforce.bucketing=true;
INSERT (INTO|OVERWRITE) TABLE <bucketed_table> SELECT <select_statement>
[SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]];

方法二：將reducer個(gè)數(shù)設(shè)置為目標(biāo)表的桶數(shù)，并在 SELECT 語(yǔ)句中用 DISTRIBUTE BY <bucket_key>對(duì)查詢結(jié)果按目標(biāo)表的分桶鍵分進(jìn)reducer中。
SET mapred.reduce.tasks = <num_buckets>;
INSERT (INTO|OVERWRITE) TABLE <bucketed_table>
SELECT <select_statement>
DISTRIBUTE BY <bucket_key>, [<bucket_key>, ...]
[SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]];
如果分桶表創(chuàng)建時(shí)定義了排序鍵，那么數(shù)據(jù)不僅要分桶，還要排序

如果分桶鍵和排序鍵不同，且按降序排列，使用Distribute by … Sort by分桶排序
如果分桶鍵和排序鍵相同，且按升序排列（默認(rèn)），使用 Cluster by 分桶排序，即如下：

SET mapred.reduce.tasks = <num_buckets>;
INSERT (INTO|OVERWRITE) TABLE <bucketed_table>
SELECT <select_statement>
CLUSTER BY <bucket_sort_key>, [<bucket_sort_key>, ...];
抽樣語(yǔ)句 ：tablesample（bucket x out of y）

tablesample是抽樣語(yǔ)句，語(yǔ)法：tablesample(bucket x out of y)，y必須是table總共bucket數(shù)的倍數(shù)或者因子。Hive根據(jù)y的大小，決定抽樣的比例。例如：table總共分了64份，當(dāng)y=32時(shí)，抽取2(64/32)個(gè)bucket的數(shù)據(jù)，當(dāng)y=128時(shí)，抽取1/2(64/128)個(gè)bucket的數(shù)據(jù)。x表示從哪個(gè)bucket開(kāi)始抽取。例如：table總共bucket數(shù)為32，tablesample(bucket 3 out of 16)表示總共抽取2(32/16)個(gè)bucket的數(shù)據(jù)，分別為第三個(gè)bucket和第19(3+16)個(gè)bucket的數(shù)據(jù)。