一 新建分区表
语法:
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搞挂掉。
所以动态分区最好慎用。