Spark SQL实现类似Postgres的RETURNING ID自增ID方案问询

方案一：获取最新ID后自增插入 #

这种方式适合基于现有表最大ID生成连续自增ID的场景，步骤如下：

1. 查询当前表的最大ID
   先获取目标表的最大ID，若表为空则默认从0开始：

   SELECT COALESCE(MAX(id), 0) AS max_id FROM your_target_table;
   

2. 为新数据生成自增ID并插入
   将查询到的max_id作为基准，用row_number()窗口函数为新数据分配连续ID，再批量插入：

   INSERT INTO your_target_table (id, column1, column2)
   SELECT 
     (${max_id} + row_number() OVER (ORDER BY column1)) AS id,
     column1,
     column2
   FROM your_new_data_source;
   

   注：如果在Spark代码（Scala/Python）中操作，可先通过spark.sql()执行第一步查询获取max_id，再动态拼接第二步SQL语句。

   注意：多作业并发插入时，此方式可能出现ID冲突，可借助Redis等分布式存储生成全局唯一自增ID规避问题。

方案二：模拟自增ID列（依赖Hive元数据） #

若Spark环境对接Hive，可利用Hive的AUTO_INCREMENT特性创建自增ID列，原生Spark SQL不支持此语法，但Hive表可实现类似效果：

1. 创建带自增列的Hive表

   CREATE TABLE your_hive_table (
     id INT AUTO_INCREMENT COMMENT '自增ID',
     column1 STRING,
     column2 INT
   ) STORED AS PARQUET;
   

2. 插入数据时自动生成ID
   插入数据无需指定id列，Hive会自动为每条数据分配递增ID：

   INSERT INTO your_hive_table (column1, column2)
   SELECT column1, column2 FROM your_new_data_source;
   

类似PostgreSQL RETURNING ID的模拟实现 #

Spark SQL本身不支持RETURNING语法，可通过两步操作模拟：

1. 插入数据时确保新数据带有唯一业务键字段
2. 插入完成后，通过该唯一键查询对应ID：

   SELECT id FROM your_target_table WHERE unique_business_key = 'xxx';
   

内容的提问来源于stack exchange，提问作者ARIJIT SINGH