spark-sql: insert overwrite分区表问题-Toy模板网

这篇具有很好参考价值的文章主要介绍了spark-sql: insert overwrite分区表问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1. 问题背景

用spark-sql，insert overwrite分区表时发现两个比较麻烦的问题：

从目标表select出来再insert overwrite目标表时报错：Error in query: Cannot overwrite a path that is also being read from.
从其他表select出来再insert overwrite目标表时，其他分区都被删除了.

2. 问题描述

2.1 代码示例

drop table pt_table_test1;
create table pt_table_test1 (
    id int,
    region string,
    dt string
) using parquet
partitioned by (region, dt)
;


drop table pt_table_test2;
create table pt_table_test2 (
    id int,
    region string,
    dt string
) using parquet
partitioned by (region, dt)
;

set hive.exec.dynamic.partition =true;
set hive.exec.dynamic.partition.mode = nonstrict;

truncate table pt_table_test1;
insert into table pt_table_test1 values (1,'id', '2022-10-01'),(2,'id', '2022-10-02'),(3,'ph', '2022-10-03'),(1,'sg', '2022-10-01'),(2,'sg', '2022-10-02'),(3,'ph', '2022-10-03');
select * from pt_table_test1;


insert overwrite table pt_table_test1 select * from pt_table_test1 where dt = '2022-10-01';
select * from pt_table_test1;

truncate table pt_table_test2;
insert into table pt_table_test2 values (2,'id', '2022-10-01'),(2,'id', '2022-10-02'),(2,'sg', '2022-10-01'),(2,'sg', '2022-10-02');
insert overwrite table pt_table_test1 select * from pt_table_test2 where id = 2;
select * from pt_table_test1;

2.2 错误演示

spark-sql: insert overwrite分区表问题

3. 解决方法

印象中这两个问题也出现过，但凭经验和感觉，应该可以解决。找到以前正常运行的表，对比分析了下，发现是建表方式不一致问题:

错误建表，指定表的文件格式：using parquet
正确姿势，指定表的文件格式：stored as parquet

3.1 示例代码

drop table pt_table_test1;
create table pt_table_test1 (
    id int,
    region string,
    dt string
) stored as parquet
partitioned by (region, dt)
;


drop table pt_table_test2;
create table pt_table_test2 (
    id int,
    region string,
    dt string
) stored as parquet
partitioned by (region, dt)
;

set hive.exec.dynamic.partition =true;
set hive.exec.dynamic.partition.mode = nonstrict;

truncate table pt_table_test1;
insert into table pt_table_test1 values (1,'id', '2022-10-01'),(1,'id', '2022-10-02'),(1,'ph', '2022-10-03'),(1,'sg', '2022-10-01'),(1,'sg', '2022-10-02'),(1,'ph', '2022-10-03');
select * from pt_table_test1;


insert overwrite table pt_table_test1 select * from pt_table_test1 where dt = '2022-10-01';
select * from pt_table_test1;

truncate table pt_table_test2;
insert into table pt_table_test2 values (2,'id', '2022-10-01'),(2,'id', '2022-10-02'),(2,'sg', '2022-10-01'),(2,'sg', '2022-10-02');
insert overwrite table pt_table_test1 select * from pt_table_test2 where id = 2;
select * from pt_table_test1;