Hive（7）--Hive操作语句（3）

这是我参与11月更文挑战的第27天，活动详情查看：2021最后一次更文挑战」

分区表

hive默认将表的数据保存在某一个hdfs的存储目录下，当需要检索符合条件的某一部分数据的时候，需要全量遍历数据，io量比较大，效率比较低，因此可以采用分而治之的思想，将符合某些条件的数据放置在某一个目录，此时检索的时候只需要搜索指定目录即可，不需要全量遍历数据。

注意：
	1、当创建完分区表之后，在保存数据的时候，会在hdfs目录中看到分区列会成为一个目录，以多级目录的形式存在
	2、当创建多分区表之后，插入数据的时候不可以只添加一个分区列，需要将所有的分区列都添加值
	3、多分区表在添加分区列的值得时候，与顺序无关，与分区表的分区列的名称相关，按照名称就行匹配

动态分区

**hive设置hive动态分区开启**
set hive.exec.dynamic.partition=true;
默认：true

**hive的动态分区模式(严格模式与非严格模式)**
set hive.exec.dynamic.partition.mode=nostrict;
	**默认：strict（至少有一个分区列是静态分区）**

**每一个执行mr节点上，允许创建的动态分区的最大数量(100)**
set hive.exec.max.dynamic.partitions.pernode;

**所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)**	
set hive.exec.max.dynamic.partitions;

**所有的mr job允许创建的文件的最大数量(100000)**	
set hive.exec.max.created.files;

**无分区的表创建

CREATE table order_no_partition(
order_id int,
order_money double,
pay_money double,
address string,
create_dates timestamp,
cancel_dates timestamp,
cancel_money double
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ",";

#######数据如下########
order_id|order_money|pay_money|address |create_dates           |cancel_dates           |cancel_money|
--------+-----------+---------+--------+-----------------------+-----------------------+------------+
       1|      178.8|      0.0|上海      |2020-02-21 00:00:00.000|                       |         0.0|
       2|       21.0|     21.0|内蒙古自治区  |2020-02-20 23:59:54.000|2020-02-21 00:00:02.000|         0.0|
       3|       37.0|      0.0|安徽省     |2020-02-20 23:59:35.000|                       |         0.0|
       4|      157.0|    157.0|湖南省     |2020-02-20 23:58:34.000|2020-02-20 23:58:44.000|         0.0|
       5|       64.8|      0.0|江苏省     |2020-02-20 23:57:04.000|2020-02-20 23:57:11.000|        64.8|
       6|      327.7|    148.9|浙江省     |2020-02-20 23:56:39.000|2020-02-20 23:56:53.000|       178.8|
       7|      357.0|    357.0|天津      |2020-02-20 23:56:36.000|2020-02-20 23:56:40.000|         0.0|
       8|       53.0|     53.0|浙江省     |2020-02-20 23:56:12.000|2020-02-20 23:56:16.000|         0.0|
       9|       43.0|      0.0|湖南省     |2020-02-20 23:54:53.000|2020-02-20 23:55:04.000|        43.0|
      10|      421.0|    421.0|北京      |2020-02-20 23:54:28.000|2020-02-20 23:54:33.000|         0.0|
      11|      267.9|      0.0|北京      |2020-02-20 23:54:24.000|2020-02-20 23:54:31.000|       267.9|
      12|       37.0|     37.0|四川省     |2020-02-20 23:54:24.000|2020-02-20 23:54:31.000|         0.0|
      13|       53.0|     53.0|上海      |2020-02-20 23:53:50.000|2020-02-20 23:57:09.000|         0.0|
      14|       34.9|      0.0|天津      |2020-02-20 23:53:44.000|                       |         0.0|
      15|       96.8|      0.0|贵州省     |2020-02-20 23:51:37.000|                       |         0.0|
      16|       80.8|     80.8|天津      |2020-02-20 23:51:29.000|2020-02-20 23:51:35.000|         0.0|
      17|       37.0|     37.0|辽宁省     |2020-02-20 23:51:22.000|2020-02-20 23:51:30.000|         0.0|
      18|      119.0|    119.0|上海      |2020-02-20 23:50:55.000|2020-02-20 23:51:12.000|         0.0|
      19|       37.0|     37.0|浙江省     |2020-02-20 23:50:48.000|2020-02-20 23:51:00.000|         0.0|
      20|      238.0|    238.0|上海      |2020-02-20 23:50:08.000|2020-02-20 23:50:17.000|         0.0|**

area_order.csv

可看下图，在没有分区的状态下，导入数据后数据文件只在一个文件当中也就是把文件直接COPY到HDFS中

####### 单分区表#########（创建单分区表）
分区表的配置值需要在创建表的时候加上特定的关键字即可例如：**<partitioned by(分区字段 字段类型)>
——————————————————————————————————————————————————————————————————————————————————————————**
**CREATE table order_1_partition(
order_id int,
order_money double,
pay_money double,
address string,
create_dates timestamp,
cancel_dates timestamp,
cancel_money double
)
partitioned by(address string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ",";

######动态分区创建和数据抽取###########

//从order_no_partition中提取数据并且依据address进行分区

insert into jacquesh_databases.order_1_partition partition(address) select order_id,order_money,pay_money,create_dates,cancel_dates,cancel_money,address  from  jacquesh_databases.order_no_partition;**

可以看到order_1_partition的目录中有依据address进行切分的目录

数据

weather.csv

####### 多分区表（案例）#########（创建多分区表）（天气）

create table jacquesh_databases.weather
(
		countycode int,
		no2_24hour_potency double,
		frist_pollutant string,
		pm10_24hour_index double,
		pollutant_level string,
		no2_24hour_index double,
		co_24hour_potency double,
		datesupdatetimes string,
		pm25_24hour_potency double,
		pm25_24hour_index double,
		status string,
		so2_24hour_index double,
		co_24hour_index double,
		pm10_24hour_potency double,
		aqi int,
		best_8hour_3day_avg int,
		observedate string
)
row format delimited 
fields terminated by ",";
####数据导入#######
load data local inpath '/home/bigdata/data/hive_database/weather' into table jacquesh_databases.weather;

## 接下来创建一个多分区的表

create table jacquesh_databases.weather_patition
(
	
		no2_24hour_potency double,
		frist_pollutant string,
		pm10_24hour_index double,
		no2_24hour_index double,
		co_24hour_potency double,
		datesupdatetimes string,
		pm25_24hour_potency double,
		pm25_24hour_index double,
		status string,
		so2_24hour_index double,
		co_24hour_index double,
		pm10_24hour_potency double,
		aqi int,
		best_8hour_3day_avg int,
		observedate string
)
partitioned by(countycode int,pollutant_level string )
row format delimited 
fields terminated by ",";
**使用查询插入语句对查询进行插入并且标明分区字段（分区需要的字段需要放在查询的最后）**
insert into jacquesh_databases.weather_patition **partition(countycode,pollutant_level)**  SELECT   no2_24hour_potency, frist_pollutant, pm10_24hour_index, no2_24hour_index, co_24hour_potency, datesupdatetimes, pm25_24hour_potency, pm25_24hour_index, status, so2_24hour_index, co_24hour_index, pm10_24hour_potency, aqi, best_8hour_3day_avg, observedate, **countycode, pollutant_level** FROM jacquesh_databases.weather;

可以看到多分区后在分区目录中还有分区目录