删除hive分区的外部表,但保留分区

tkclm6bt  于 2021-05-29  发布在  Hadoop
关注(0)|答案(1)|浏览(619)

使用外部配置单元表时,是否有一种方法可以删除目录中的数据,但通过查询保留分区。请注意,我不想放弃表和重建它。我只想清空底层文件夹,重新开始一个进程。我的表很大,按年、月、日和小时划分分区,手动重新创建分区需要很多时间。
谢谢

myzjeezk

myzjeezk1#

truncate table ... 删除所有数据。 truncate table partition (...) 删除特定分区的数据。
目录结构保持不变。
外部表应首先转换为manged,例如

alter table t set tblproperties('EXTERNAL'='FALSE');

当我们完成后,我们可以把它转换回来

alter table t set tblproperties('EXTERNAL'='TRUE');

演示

create table t (i int) partitioned by (x char(1));
set hive.exec.dynamic.partition.mode=nonstrict;
insert into t partition (x) values (1,'A'),(2,'B'),(3,'C');
alter table t set tblproperties('EXTERNAL'='TRUE');
select * from t;
+-----+-----+
| t.i | t.x |
+-----+-----+
|   1 | A   |
|   2 | B   |
|   3 | C   |
+-----+-----+
dfs -ls -R /user/hive/warehouse/t;
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=A
-rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=A/000000_0
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=B
-rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=B/000000_0
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=C
-rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=C/000000_0
truncate table t partition (x='B');

失败:semanticexception[错误10146]:无法截断非托管表t。

alter table t set tblproperties('EXTERNAL'='FALSE');
truncate table t partition (x='B');
select * from t;
+-----+-----+
| t.i | t.x |
+-----+-----+
|   1 | A   |
|   3 | C   |
+-----+-----+
dfs -ls -R /user/hive/warehouse/t;
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=A
-rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=A/000000_0
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:42 /user/hive/warehouse/t/x=B
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:40 /user/hive/warehouse/t/x=C
-rwxrwxrwx   1 cloudera supergroup          2 2017-03-28 11:40 /user/hive/warehouse/t/x=C/000000_0
truncate table t;
+-----+-----+
| t.i | t.x |
+-----+-----+
dfs -ls -R /user/hive/warehouse/t;
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:43 /user/hive/warehouse/t/x=A
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:43 /user/hive/warehouse/t/x=B
drwxrwxrwx   - cloudera supergroup          0 2017-03-28 11:43 /user/hive/warehouse/t/x=C
alter table t set tblproperties('EXTERNAL'='TRUE');

相关问题