[Hive基礎]-- 建立分區表

一、為什麼要建立分區表

1、select查詢中會掃描整個表内容，會消耗大量時間。由于相當多的時候人們隻關心表中的一部分資料，

   故建表時引入了分區概念。

2、hive分區表:是指在建立表時指定的partition的分區空間，若需要建立有分區的表，

   需要在create表的時候調用可選參數partitioned by，詳見表建立的文法結構。

二、實作建立、删除分區表

注意：

1、一個表可以擁有一個或者多個分區，每個分區以檔案夾的形式單獨存在表檔案夾的目錄下。

2、hive的表和列名不區分大小寫(故建表時，都是小寫)

3、分區是以字段的形式在表結構中存在，通過"desc table_name"指令可以檢視到字段存在，該字段僅是分區的辨別。

4、建表的文法（建分區可參見PARTITIONED BY參數）：

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] 

[LOCATION hdfs_path]

5、分區建表分為2種，一種是單分區，也就是說在表檔案夾目錄下隻有一級檔案夾目錄。另外一種是多分區，表檔案夾下出現多檔案夾嵌套模式。(如果需要 group by 多個字段，建議按分區嵌套順序 group by )

a、單分區建表語句：create table test_table (id int, content string) partitioned by (dt string);

   單分區表，按天分區，在表結構中存在id，content，dt三列。

b、雙分區建表語句：create table test_table_2 (id int, content string) partitioned by (dt string, hour string);

   雙分區表，按天和小時分區，在表結構中新增加了dt和hour兩列。

6、增加分區表文法（表已建立，在此基礎上添加分區）：

ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... partition_spec: : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

使用者可以用 ALTER TABLE ADD PARTITION 來向一個表中增加分區。當分區名是字元串時加引号。例：

ALTER TABLE test_table ADD PARTITION (dt='2016-08-08', hour='10') location '/path/uv1.txt' PARTITION (dt='2017-08-08', hour='12') location '/path/uv2.txt';

7、删除分區文法：

ALTER TABLE table_name DROP partition_spec, partition_spec,...

使用者可以用 ALTER TABLE DROP PARTITION 來删除分區。分區的中繼資料和資料将被一并删除。例：

ALTER TABLE test_table DROP PARTITION (dt='2016-08-08', hour='10');

8、資料加載進分區表中文法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

例：

LOAD DATA INPATH '/user/uv.txt' INTO TABLE test_table_2 PARTITION(dt='2016-08-08', hour='08'); LOAD DATA local INPATH '/user/hh/' INTO TABLE test_table  partition(dt='2013-02- 07');

當資料被加載至表中時，不會對資料進行任何轉換。Load操作隻是将資料複制至Hive表對應的位置。資料加載時在表下自動建立一個目錄，檔案存放在該分區下。

9、基于分區的查詢的語句：

SELECT test_table.* FROM test_table WHERE test_table.dt>= '2008-08-08';

10、檢視雙分區語句：

hive> show partitions test_table_2; 

OK 

dt=2016-08-08/hour=10 

dt=2016-08-09/hour=10

dt=2008-08-09/hour=10

舉例：

CREATE TABLE `incr_test_2`(
  `ord_id` string, 
  `ord_no` string, 
  `creat_date` string, 
  `creat_time` string, 
  `time_stamp` string)
COMMENT 'Imported by sqoop on 2016/08/08 14:53:43'
PARTITIONED BY ( 
  `log_time` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\u0001', 
  'line.delim'='\n', 
  'serialization.format'='\u0001') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;      

檢視對應的建表資訊：

hive (origin_test)> show create table incr_test_2;
OK
CREATE TABLE `incr_test_2`(
  `ord_id` string, 
  `ord_no` string, 
  `creat_date` string, 
  `creat_time` string, 
  `time_stamp` string)
COMMENT 'Imported by sqoop on 2016/08/04 14:53:43'
PARTITIONED BY ( 
  `log_time` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\u0001', 
  'line.delim'='\n', 
  'serialization.format'='\u0001') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://nameservice/user/hive/warehouse/origin_test.db/incr_test_2'
TBLPROPERTIES (
  'transient_lastDdlTime'='1470293625')      

檢視分區表：

-- 檢視單分區：
hive (origin_test)>show partitions incr_test_2;
OK
log_time=20160917182510
log_time=20160917192512
log_time=20160917202512
log_time=20160917212512
log_time=20160917222510
log_time=20160917232511
log_time=20160918002525
log_time=20160918012514
log_time=20160918022513
log_time=20160918032510
log_time=20160918042510
log_time=20160918052511
log_time=20160918062513
log_time=20160918072510
log_time=20160918082510
log_time=20160918092511
log_time=20160918102510
log_time=20160918112511
log_time=20160918122512
log_time=20160918132511
Time taken: 0.264 seconds, Fetched: 20 row(s)
hive (origin_ennenergy_transport)>