hive之分区表详解

一 新建分区表

语法：

PARTITIONEDBY (col type,col type......)

示例：

CREATETABLE order (

      oid STRING,

      otime STRING,

      ofrom STRING,

      age INT

)

COMMENT'order partition table'

PARTITIONEDBY (code STRING,range STRING)

ROWFORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

二 分区表插入数据的方式

2.1通过HDFS或者localload 数据

特点：通过这种方式加载数据到分区表，必须指定分区，也就是说，只能根据文件分区，而不能根据文件里的数据分区

LOADDATA LOCAL INPATH '/opt/data/film/film-om-2015.txt' INTO TABLEfilm_second_level_part PARTITION(area='Western',release_year

='2015');

LOADDATA LOCAL INPATH '/opt/data/film/film-rh-2017.txt' INTO TABLEfilm_second_level_part PARTITION(area='J-Korea',release_year

='2017');

LOADDATA LOCAL INPATH '/opt/data/film/film-dl-2015.txt' INTO TABLE film_second_level_partPARTITION(area='Mainland', release_

year='2015');

 

2.2通过手动方式将数据放到HDFS中表对应的分区目录

特点：需要手动创建分区；手动copy、上传数据; 需要对hive元数据进行修复，因为元数据并不知道你在hdfs这些操作，所以你需要告诉他。

2.2.1创建HDFS目录

hdfsdfs -mkdir -p /user/hive/warehouse/hadoop.db/film_one_level

_part_manual/year=1999;

hdfsdfs -mkdir -p /user/hive/warehouse/hadoop.db/film_one_

level_part_manual/year=2005;

hdfsdfs -mkdir -p /user/hive/warehouse/hadoop.db/film_one_level

_part_manual/year=2015;

hdfsdfs -mkdir -p /user/hive/warehouse/hadoop.db/film_one_level

_part_manual/year=2016;

 

2.2.2将对应文件移到分区目录

hdfsdfs -cp input/film/movie-1999.txt /user/hive/warehouse/hadoop.db/

film_one_level_part_manual/year=1999;

hdfsdfs -cp input/film/movie-2005.txt /user/hive/warehouse/hadoop.db/

film_one_level_part_manual/year=2005;

hdfsdfs -cp input/film/movie-2015.txt /user/hive/warehouse/hadoop.db/

film_one_level_part_manual/year=2015;

hdfsdfs -cp input/film/movie-2016.txt /user/hive/warehouse/hadoop.db/

film_one_level_part_manual/year=2016;

2.2.3添加新分区数据到元数据信息中

第一种方式：

ALTERTABLE film_one_level_part_manual ADD PARTITION(

year='1999');

ALTERTABLE film_one_level_part_manual ADD PARTITION(

year=2005);

ALTERTABLE film_one_level_part_manual ADD PARTITION(

year=2015);

ALTERTABLE film_one_level_part_manual ADD PARTITION(

year=2016);

很明显，如果分区数太多，这不是一个效率的办法。

第二种方式：

MSCKREPAIR TABLE film_one_level_part_manual;

原理相当简单，执行后，Hive会检测如果HDFS目录下存在但表的metastore中不存在的PARTITION元信息，更新到metastore中。

2.3INSERT INTO/OVERWRITE TABLE …SELECT 语句

创建一个待插入的表：

CREATETABLE IF NOT EXISTS film_one_level_part_insert (

      fid INT,

      fname STRING,

      director STRING,

      conutry STRING,

      release_time STRING,

      time STRING,

      grade DOUBLE,

      comment_num INT,

      film_type STRING,

      region STRING

)

PARTITIONED BY(release_year STRING)

ROWFORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

2.3.1直接插入

INSERTINTO TABLE insert_film_partition PARTITION(year='1999')

SELECT* FROM film_one_level_part_insert WHERE SUBSTR(release

_time,0,4)= '1999';

INSERTINTO TABLE insert_film_partition PARTITION(year='2005')

SELECT* FROM film_one_level_part_insert WHERE SUBSTR(release

_time,0,4)= '2005';

2.3.2覆盖插入

INSERT OVER WRITE TABLE insert_film_partition PARTITION

(year='2015')SELECT * FROM film_one_level_part_insert WHERE SUBSTR(release_time,0,4) ='2015';

INSERT OVER WRITE TABLE insert_film_partition PARTITION(

year='2016')SELECT * FROM film_one_level_part_insert WHERE SUBSTR(release_time,0,4) ='2016';

 

2.3.3多插入

FROM emp e

INSERT OVER WRITE TABLE copy_emp SELECT e.* WHERE e.deptno = 10

INSERT INTO TABLE copy_emp2 SELECT e.* WHERE e.deptno = 20

INSERT OVER WRITE TABLE copy_emp2 SELECT e.* WHERE e.deptno = 20

INSERT INTO TABLE copy_emp3 SELECT e.* WHERE e.deptno = 20;

多插入的注意点：

非overwrite的插入，待插入的表名字不能相同，否则会报错。

 

2.3.4动态分区的插入方式

INSERT OVER WRITE TABLE order PARTITION(code,range) SELECT * FROM t_order;

 

三 添加/删除/重命名/恢复分区

CREATE TABLE order (

      oid STRING,

      otime STRING,

      ofrom STRING,

      age INT

)

COMMENT order partition table'

PARTITIONED BY (code STRING,range STRING)

ROWFORMAT DELIMITED FIELDS TERMINATED BY '\t';

 

3.1ADD PARTITION

ALTER TABLE order ADD IF NOT EXISTS PARTITION (code='CA',

range='21-30');

 

3.2删除分区

ALTER TABLE order DROP PARTITION (code='CN',range='20-30');

 

 

3.3重命名

ALTERTABLE order PARTITION '50~' RENAME TO PARTITION '60~';

 

3.4恢复分区

MSCK REPAIR TABLE table_name;

 

四 修改分区文件格式、位置、受保护状态等

4.1修改分区文件格式

ALTERTABLE table_name [PARTITION partitionSpec] SET FILEFORMAT file_format

 

4.2修改分区文件位置

ALTERTABLE table_name [PARTITION partition_spec] SET LOCATION "newlocation";

 

4.3修改分区表分区受保护状态

ALTER TABLE table_name [PARTITION partition_spec] ENABLE|DISABLE NO_DROP [CASCADE];是否打开该分区受保护状态，一旦打开，则该分区不能被删除。

ALTER TABLE order PARTITION (code='CN',range='20-30') ENABLE NO_DROP;

ALTER TABLE order DROP PARTITION (code='CN',range='20-30');

Error:Partitionprotected from being dropped hadoop@order@code=CN/range=20-30

 

ALTERTABLE table_name [PARTITION partition_spec]

ENABLE|DISABLEOFFLINE;

是否打开禁止该分区查询的状态，一打开，该分区不能被查询

 

五 动态分区 和 静态分区

动态分区和静态分区在于是否需要我们指定分区内容。也就是说静态分区使我们指定的，但是动态分区是在执行HQL语句的时候再决定的。

5.1我们如何启用动态分区

hive.exec.dynamic.partition=true//默认是false

hive.exec.dynamic.partition.mode=nonstrict

#strict: 至少需要指定一个静态分区，而且静态分区必须在动态分区的前面

#nonstrict: 完全是动态的

每一个节点能创建的最大动态分区数目

hive.exec.max.dynamic.partitions.pernode

能创建的最大的分区数目

hive.exec.max.dynamic.partitions

一个任务最多可以创建的文件数目

hive.exec.max.created.files

限定一次最多打开的文件数

dfs.datanode.max.xcievres

 

 

5.2何为动态？体现在哪儿

第一种情况，完全动态

只要我们给出PARTITION字段，则会根据真实数据的最后PARTITION列数，而自动填充。

比如

CREATETABLE order (

      oid STRING,

      otime STRING,

      ofrom STRING,

      age INT

)

COMMENT'order partition table'

PARTITIONEDBY (code STRING,range STRING)

ROWFORMAT DELIMITED FIELDS TERMINATED BY '\t';

这里分区字段只有2个，那么完全动态的话，则会根据查询的所有状态最后2个字段来填充分区

INSERTOVERWRITE TABLE order PARTITION(code,range) SELECT * FROM t_order;

 

INSERTOVERWRITE TABLE order PARTITION(code,range) SELECT oid,otime,ofrom, age, ofromAS code1,

CASE

WHEN(age <= 20) THEN '~20'

WHEN(age > 20 AND age <=30) THEN '20-30'

WHEN(age > 30 AND age <=40) THEN '30-40' 

WHEN(age > 40 AND age <=50) THEN '40-50'

ELSE'50~'

ENDAS range2 FROM t_order;

 

第二种情况：一部分静态，一部分动态

这种情况，静态必须在动态前面,否则报错。我们在查询插入数据的时候，我们除了要查询所有字段之外，还必须指定动态的那个字段。

INSERTINTO TABLE c_order PARTITION(code='CN',range)

SELECToid,otime,ofrom, age, CASE

WHEN(age <= 20) THEN 'low than 20'

WHEN(age > 20 AND age <=30) THEN '20-30'

WHEN(age > 30 AND age <=40) THEN '30-40' 

WHEN(age > 40 AND age <=50) THEN '40-50'

WHEN(age > 50 AND age <=60) THEN '50-60'

ELSE'old than 60'

ENDAS range2 FROM t_order;

即我们分区列式range,但是我们在查询字段可以叫其他名字，比如range2，因为他是按照位置来填充的。

 

 

六 使用动态分区的问题

静态分区是在编译(compile阶段就已经确定)但是使用动态分区时，需要跑reduce，，动态分区会为每一个分区分配reduce数。

 

如果你设置了一个reduce数目：mapred.reduce.tasks=100;

如果你有5个分区，可能就是500个reduce，也就是说同时处理500多个文件，如果分区数目更多，很容易把NameNode搞挂掉。

所以动态分区最好慎用。