INSERT OVERWRITE 与 先 DELETE 再 INSERT INTO 对比_overwrite 可以根据字段来对比删除,插入吗

在hive或者spark的sql中 INSERT OVERWRITE 与 先 DELETE 再 INSERT INTO 对比

INSERT OVERWRITE 和先执行 DELETE 再执行 INSERT 这两种方式在达到全量覆盖表数据的目的上是等效的,但是它们在执行方式和效率上有一些区别:

1. 执行方式:

   • INSERT OVERWRITE 是一个原子操作,要么整个操作成功,要么整个操作失败,可以确保数据的完整性和一致性。
   • 先 DELETE 再 INSERT 则需要分两个步骤执行,中间可能会受到其他操作的干扰,存在一定的不确定性。

2. 执行效率:

   • INSERT OVERWRITE 直接将新数据写入目标表,只需执行一次写操作,效率较高。
   • 先 DELETE 再 INSERT 需要先扫描并删除整个旧表数据,然后再将新数据插入,需要执行两次操作,效率相对较低。

3. 元数据处理:

   • INSERT OVERWRITE 不会影响表的元数据(如分区信息、列信息等),会保留原有的元数据。
   • 如果先执行 DELETE 删除整个表,则需要重新创建表并恢复元数据,可能会导致元数据丢失或不一致的风险。

4. 容错能力:

   • 由于 INSERT OVERWRITE 是原子操作,如果中途失败,数据不会被改变,可以重试。
   • 先 DELETE 再 INSERT 如果在两个步骤之间失败,可能会导致数据丢失或不一致,需要人工介入恢复。

综上所述,如果只是为了全量覆盖表数据,INSERT OVERWRITE 方式更加安全高效,是推荐的做法。

只有在需要重建表的元数据或有其他特殊需求时,才需要先执行 DELETE 删除整张表。

在实际使用时,你还需要考虑并发修改、事务隔离级别等因素,确保数据操作的正确性和完整性。如果条件允许,可以在开发和测试环境中对两种方式进行试验,比较执行性能和结果,了解适合您场景的最佳实践。

例1: 全量覆盖非分区表

假设有一个记录产品信息的表products,现在需要用最新的数据来完全替换表中的数据。

使用INSERT OVERWRITE:

INSERT OVERWRITE TABLE products
SELECT 
  product_id,
  product_name,
  category,
  price
FROM staging_products;
1
2
3
4
5
6
7

这条语句会用staging_products临时表中的最新数据,原子地覆盖products表中的全部数据。

使用DELETE + INSERT:

DELETE FROM products;
INSERT INTO products
SELECT
  product_id,
  product_name,  
  category,
  price
FROM staging_products;
1
2
3
4
5
6
7
8

这两条语句的作用和INSERT OVERWRITE是一样的,但是是分两步完成,中间如果出现失败可能会导致数据不一致或丢失。而且需要额外的DELETE开销。

例2: 全量覆盖分区表的特定分区

假设有一个按年份分区的销售记录表sales_part,需要用新数据覆盖2022年的销售分区数据。

使用INSERT OVERWRITE:

INSERT OVERWRITE TABLE sales_part
PARTITION (year=2022)
SELECT
  order_id,
  product_id,
  quantity,
  amount
FROM daily_sales
WHERE year=2022;  
1
2
3
4
5
6
7
8
9

通过指定PARTITION,INSERT OVERWRITE会只覆盖year=2022这个分区的数据,不影响其他分区。

使用DELETE + INSERT:

DELETE FROM sales_part WHERE year=2022;
INSERT INTO sales_part PARTITION(year=2022)
SELECT
  order_id,
  product_id,  
  quantity,
  amount
FROM daily_sales
WHERE year=2022;
1
2
3
4
5
6
7
8
9

需要先DELETE删除2022年的分区数据,然后再将新数据INSERT进去。如果在两步之间失败,可能导致2022年分区数据丢失或仅部分写入。

例3: 重建表结构和数据

有时需要重建表的schema,比如修改列名、类型或增减列等,这时就需要先删除再重建。

-- 备份旧表
CREATE TABLE products_backup AS SELECT * FROM products;

-- 删除旧表
DROP TABLE products; 

-- 使用新的schema创建表
CREATE TABLE products (
  ...新的schema...
);

-- 插入数据
INSERT INTO products
SELECT 
  ...映射列...
FROM products_backup;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

总的来说,如果只是全量覆盖表数据,INSERT OVERWRITE更高效、安全;

如果需要修改表结构或元数据,则必须先DELETE再重建插入。

在具体场景下,需要权衡风险并采用合适的方式。