How to create monotonic function to incrementally add obj_id for datasource in pyspark

SandhyaDB — Fri, 11 Sep 2026 10:52:52 GMT

I have used monotonically_increasing_id() to add unique to datasource .However , this does not add unique id in increasing number ..Its kind of random.How to perform this in pyspark.

Re: How to create monotonic function to incrementally add obj_id for datasource in pyspark

osingh — Fri, 11 Sep 2026 12:17:29 GMT

Why monotonically_increasing_id() Produces Non-Sequential IDs
That’s actually a super common trip-up! Spark does this by design to avoid an expensive global shuffle across nodes, but it definitely catches a lot of people off guard when they expect a simple 1, 2, 3 sequence.

monotonically_increasing_id() guarantees unique 64-bit integers that increase within each partition, but they are not consecutive across the whole DataFrame.

Because Spark is a distributed processing engine, each partition is assigned its own large block of IDs. The high 31 bits store the partition ID (calculated as partition_number × 2³³), while the low 33 bits store the record count within that specific partition. As a result, you see those huge numeric gaps between partitions (e.g., 0, 1, 2, ... on Partition 0, then jumping to 8589934592, 8589934593, ... on Partition 1).

Three Ways to Get Sequential IDs
1. Use row_number() over monotonically_increasing_id() (recommended)
Combine both functions: first add the monotonic ID, then use row_number() with a window ordered by that ID to get clean 1, 2, 3, … values.

from pyspark.sql.functions import monotonically_increasing_id, row_number, col from pyspark.sql.window import Window # Add monotonically increasing id first df_with_mono_id = df.withColumn("mono_id", monotonically_increasing_id()) # Use row_number() over a window ordered by the monotonic id window = Window.orderBy(col("mono_id")) df_with_seq_id = df_with_mono_id.withColumn("row_id", row_number().over(window)).drop("mono_id") df_with_seq_id.show()

Output: +-----+---+------+ | Name|Age|row_id| +-----+---+------+ |Alice| 10| 1| |Susan| 12| 2| +-----+---+------+

Note: row_number() with a single partition window can be slow on very large datasets because all data is shuffled to one partition for ordering.

2. Use zipWithIndex() via RDD (best for large datasets)
This approach avoids the single-partition bottleneck by working at the RDD level:

from pyspark.sql.functions import col # Convert to RDD, apply zipWithIndex, convert back df_rdd = df.rdd.zipWithIndex().toDF() df_with_id = df_rdd.select(col("_1.*"), col("_2").alias("row_id")) df_with_id.show()

Output: +-----+---+------+ | Name|Age|row_id| +-----+---+------+ |Alice| 10| 0| |Susan| 12| 1| +-----+---+------+

3. Start from a custom offset
If you need IDs to continue from a previous maximum (e.g., for incremental loads):

from pyspark.sql.functions import monotonically_increasing_id, row_number, col, lit from pyspark.sql.window import Window previous_max_value = 1000 # fetch this from your existing table df_with_mono_id = df.withColumn("mono_id", monotonically_increasing_id()) window = Window.orderBy(col("mono_id")) df_final = (df_with_mono_id .withColumn("row_id", row_number().over(window) + lit(previous_max_value)) .drop("mono_id")) df_final.show()

Output: +-----+---+------+ | Name|Age|row_id| +-----+---+------+ |Alice| 10| 1001| |Susan| 12| 1002| +-----+---+------+

Check the below details it will help you to decide which method to choose?

Method	Pros	Cons
row_number() over monotonically_increasing_id()	Simple, stays in DataFrame API	Single-partition shuffle; slow on very large data
zipWithIndex() via RDD	Distributed, efficient for large datasets	Requires RDD conversion and back
Custom offset	Supports incremental ID assignment	Same trade-offs as method 1

For most use cases, method 1 (row_number()) is the simplest. For very large datasets where performance matters, method 2 (zipWithIndex()) is preferred.

Hope this should help!

Thanks!

Re: How to create monotonic function to incrementally add obj_id for datasource in pyspark

DB-RKL — Fri, 11 Sep 2026 13:31:01 GMT

Hey

So monotonically_increasing_id() only promises the numbers go up and are unique, not that they're neat like 1, 2, 3. Under the hood it bakes the partition number into the ID, so each partition jumps ahead by billions.

If you let me know what you're using the ID for and I'll point you to the cleanest option 🙂

topic Re: How to create monotonic function to incrementally add obj_id for datasource in pyspark in Data Engineering

How to create monotonic function to incrementally add obj_id for datasource in pyspark

Re: How to create monotonic function to incrementally add obj_id for datasource in pyspark

Re: How to create monotonic function to incrementally add obj_id for datasource in pyspark