在Impala/Hive中删除多个分区 [英] Dropping multiple partitions in Impala/Hive
问题描述
1-我正在尝试一次删除多个分区,但是很难用Impala或Hive做到这一点.我尝试了以下查询,使用和不使用'
:
1- I'm trying to delete multiple partitions at once, but struggling to do it with either Impala or Hive. I tried the following query, with and without '
:
ALTER TABLE cz_prd_corrti_st.s1mme_transstats_info DROP IF EXISTS
PARTITION (pr_load_time='20170701000317')
PARTITION (pr_load_time='20170701000831')
ALTER TABLE cz_prd_corrti_st.s1mme_transstats_info DROP IF EXISTS
PARTITION (pr_load_time='20170701000317')
PARTITION (pr_load_time='20170701000831')
我得到的错误如下:
AnalysisException: 第3行的语法错误:PARTITION(pr_load_time ='20170701000831')^ 遇到:预期分区:缓存,位置,清除,设置, 导致未命中的原因:异常:语法错误
AnalysisException: Syntax error in line 3: PARTITION (pr_load_time='20170701000831') ^ Encountered: PARTITION Expected: CACHED, LOCATION, PURGE, SET, UNCACHED CAUSED BY: Exception: Syntax error
分区列为bigint类型,查询仅删除一个分区即可正常工作:
The partition column is bigint type, query for deleting only one partition works as expected:
ALTER TABLE cz_prd_corrti_st.s1mme_transstats_info DROP IF EXISTS
PARTITION (pr_load_time='20170701000317')
2-是先删除hdfs数据,然后将分区放在Impala/Hive中的好习惯吗?或者反之亦然?
2- Is it a good practice delete the hdfs data first and then drop the partitions in Impala/Hive, or is it supposed to be done vice versa?
推荐答案
1.
您的语法错误.
在DROP命令中,分区应以逗号分隔.
1.
Your syntax is wrong.
In the DROP command the partitions should be separated by commas.
演示
hive> create table t (i int) partitioned by (p int);
OK
hive> alter table t add partition (p=1) partition(p=2) partition(p=3) partition(p=4) partition(p=5);
OK
hive> show partitions t;
OK
partition
p=1
p=2
p=3
p=4
p=5
hive> alter table t drop if exists partition (p=1),partition (p=2),partition(p=3);
Dropped the partition p=1
Dropped the partition p=2
Dropped the partition p=3
OK
hive> show partitions t;
OK
partition
p=4
p=5
2.
您可以放下一个范围.
2.
You can drop a range.
演示
hive> create table t (i int) partitioned by (p int);
OK
hive> alter table t add partition (p=1) partition(p=2) partition(p=3) partition(p=4) partition(p=5);
OK
hive> show partitions t;
OK
partition
p=1
p=2
p=3
p=4
p=5
hive> alter table t drop if exists partition (p<=3);
Dropped the partition p=1
Dropped the partition p=2
Dropped the partition p=3
OK
hive> show partitions t;
OK
partition
p=4
p=5
这篇关于在Impala/Hive中删除多个分区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!