**1. 靜態分區特點** * 往分區表裝載數據時，需要明確指定分區字段和分區值。 * 支持 `load` 和 `insert` 兩種插入數據方式。 * 適用于分區數少，分區名可以明確的數據。 **2. 創建靜態分區** ```sql #### 1. 創建分區表 #### create table if not exists dept_part( dept_id int, dept_name string, dept_salary double ) -- partitioned by 要在 row format的前面 -- month字段名必須是建表字段中沒有的字段 partitioned by (month string) row format delimited fields terminated by '\t' ; #### 2. 往分區表加載數據 #### load data local inpath "/hdatas/dept.txt" into table dept_part partition(month='201908'); load data local inpath "/hdatas/dept.txt" into table dept_part partition(month='201909'); load data local inpath "/hdatas/dept.txt" into table dept_part partition(month='201910'); ``` 分區表在hdfs存儲的路徑如下，當前我使用的數據庫為hivebook. ```sql /hivebook.db/dept_part/month=201908/dept.txt /hivebook.db/dept_part/month=201909/dept.txt /hivebook.db/dept_part/month=201910/dept.txt ```  <br/> **3. 分區表常用操作** ```sql #### 查詢所有分區 #### 0: jdbc:hive2://hadoop101:10000> select * from dept_part; +--------------------+----------------------+------------------------+------------------+--+ | dept_part.dept_id | dept_part.dept_name | dept_part.dept_salary | dept_part.month | +--------------------+----------------------+------------------------+------------------+--+ | 10 | ACCOUNTING | 1700.0 | 201908 | | 20 | RESEARCH | 1800.0 | 201908 | | 30 | SALES | 1900.0 | 201908 | | 10 | ACCOUNTING | 1700.0 | 201909 | | 20 | RESEARCH | 1800.0 | 201909 | | 30 | SALES | 1900.0 | 201909 | | 10 | ACCOUNTING | 1700.0 | 201910 | | 20 | RESEARCH | 1800.0 | 201910 | | 30 | SALES | 1900.0 | 201910 | +--------------------+----------------------+------------------------+------------------+--+ #### 單分區查詢 #### 0: jdbc:hive2://hadoop101:10000> select * from dept_part where month="201908"; +--------------------+----------------------+------------------------+------------------+--+ | dept_part.dept_id | dept_part.dept_name | dept_part.dept_salary | dept_part.month | +--------------------+----------------------+------------------------+------------------+--+ | 10 | ACCOUNTING | 1700.0 | 201908 | | 20 | RESEARCH | 1800.0 | 201908 | | 30 | SALES | 1900.0 | 201908 | +--------------------+----------------------+------------------------+------------------+--+ #### 查詢有哪些分區 #### 0: jdbc:hive2://hadoop101:10000> show partitions dept_2; +---------------+--+ | partition | +---------------+--+ | month=201910 | | month=201911 | | month=201912 | +---------------+--+ #### 刪除單個分區 #### alter table dept_2 drop partition(month="201910"); #### 刪除多個分區 #### alter table dept_2 drop partition(month="201910"), partition(month="201912"); #### 新增單個分區 #### alter table dept_2 add partition(month="202010"); #### 新增多個分區 #### alter table dept_2 add partition(month="202012"), partition(month="202013"); ``` <br/> **4. 創建多級分區表** ```sql #### 創建二級分區 #### create table if not exists dept_3( dept_id int, dept_name string, dept_salary double ) -- 二級分區 partitioned by (month string, day string) row format delimited fields terminated by '\t' ; #### 往二級分區裝載數據 #### load data local inpath "/hdatas/dept.txt" into table dept_3 partition(month='201910', day='23'); load data local inpath "/hdatas/dept.txt" into table dept_3 partition(month='201910', day='24'); load data local inpath "/hdatas/dept.txt" into table dept_3 partition(month='201910', day='25'); 0: jdbc:hive2://hadoop101:10000> select * from dept_3; +-----------------+-------------------+---------------------+---------------+-------------+--+ | dept_3.dept_id | dept_3.dept_name | dept_3.dept_salary | dept_3.month | dept_3.day | +-----------------+-------------------+---------------------+---------------+-------------+--+ | 10 | ACCOUNTING | 1700.0 | 201910 | 23 | | 20 | RESEARCH | 1800.0 | 201910 | 23 | | 30 | SALES | 1900.0 | 201910 | 23 | | 10 | ACCOUNTING | 1700.0 | 201910 | 24 | | 20 | RESEARCH | 1800.0 | 201910 | 24 | | 30 | SALES | 1900.0 | 201910 | 24 | | 10 | ACCOUNTING | 1700.0 | 201910 | 25 | | 20 | RESEARCH | 1800.0 | 201910 | 25 | | 30 | SALES | 1900.0 | 201910 | 25 | +-----------------+-------------------+---------------------+---------------+-------------+--+ ``` 在hdfs中的路徑如下，我使用的數據庫為hivebook. ```sql /hivebook.db/dept_3/month=201910/day=23/dept.txt /hivebook.db/dept_3/month=201910/day=24/dept.txt /hivebook.db/dept_3/month=201910/day=25/dept.txt ```  ```sql #### 查詢單個分區 #### 0: jdbc:hive2://hadoop101:10000> select * from dept_3 where month="201910" and day="23"; +-----------------+-------------------+---------------------+---------------+-------------+--+ | dept_3.dept_id | dept_3.dept_name | dept_3.dept_salary | dept_3.month | dept_3.day | +-----------------+-------------------+---------------------+---------------+-------------+--+ | 10 | ACCOUNTING | 1700.0 | 201910 | 23 | | 20 | RESEARCH | 1800.0 | 201910 | 23 | | 30 | SALES | 1900.0 | 201910 | 23 | +-----------------+-------------------+---------------------+---------------+-------------+--+ ```