Re: How to generate unique incrementing identifier in a structured streaming dataframe
Yes that is true. UUID only introduces uniqueness to the record. Some NoSql
databases requires a primary key where UUID can be used.
import java.util.UUID
scala> var pk = UUID.randomUUID
pk: java.util.UUID = 0d91e11a-f5f6-4b4b-a120-8c46a31dad0bscala>
pk = UUID.randomUUID
pk: java.util.UUID = 137ab1ef-625a-4277-9d94-9f4a11d793fc
So they are totally random.
Now Kafka producer requires a key, value pair, We generate UUID key as the
unique identifier of Kafka record
uuidUdf= F.udf(lambda : str(uuid.uuid4()),StringType())
result = df.withColumn("uuid",uuidUdf()) \
So back to your question. What is the use case for identity() in your SSS
application? If you want a true value as close as accurate (even MSSQL can
have gaps in the identity column because of crash etc), you need to store
the last value in a persistent storage like Hive table etc and start from
val start = spark.sql("SELECT MAX(id) FROM
test.randomData").collect.apply(0).getInt(0) + 1
HTH
view my Linkedin profile
Re: How to generate unique incrementing identifier in a structured streaming dataframe
Thank you so much for the insights.
@Mich Talebzadeh Really appreciate your
detailed examples.
@Jungtaek Lim I see your point. I am thinking of having a mapping table
with UUID to incremental ID and leverage range pruning etc on a large
dataset.
@sebastian I have to check how to do something like snowflake id. Do you
have any examples or directions?
Let me ask you another way, how are you handling the non incrementing
UUIDs? Because Parquet - range stats has min and max, but if your id is a
UUID, this doesn't help to decide whether the value that you search is
present in the files until you scan the entire file, because min-max on
uuid doesn't work greatly.
Please share your experiences or ideas on how you handled this situation.
Regards,
Felix K Jose
On Tue, Jul 13, 2021 at 7:59 PM Jungtaek Lim
wrote:
> Theoretically, the composed value of batchId +
> monotonically_increasing_id() would achieve the goal. The major downside is
> that you'll need to deal with "deduplication" of output based on batchID
> as monotonically_increasing_id() is indeterministic. You need to ensure
> there's NO overlap on output against multiple reattempts for the same batch
> ID.
>
> Btw, even just assume you dealt with auto increasing ID on write, how do
> you read files and apply range pruning by auto increasing ID? Is the
> approach scalable and efficient? You probably couldn't avoid reading
> unnecessary files unless you build an explicit metadata regarding files
> like the map file name to the range of ID and also craft a custom reader to
> leverage the information.
>
>
> On Wed, Jul 14, 2021 at 6:00 AM Sebastian Piu
> wrote:
>
>> If you want them to survive across jobs you can use snowflake IDs or
>> similar ideas depending on your use case
>>
>> On Tue, 13 Jul 2021, 9:33 pm Mich Talebzadeh,
>> wrote:
>>
>>> Meaning as a monolithically incrementing ID as in Oracle sequence for
>>> each record read from Kafka. adding that to your dataframe?
>>>
>>> If you do Structured Structured Streaming in microbatch mode, you will
>>> get what is known as BatchId
>>>
>>>result = streamingDataFrame.select( \
>>> col("parsed_value.rowkey").alias("rowkey") \
>>>, col("parsed_value.ticker").alias("ticker") \
>>>, col("parsed_value.timeissued").alias("timeissued") \
>>>, col("parsed_value.price").alias("price")). \
>>> writeStream. \
>>> outputMode('append'). \
>>> option("truncate", "false"). \
>>> *foreachBatch(sendToSink). \*
>>> trigger(processingTime='30 seconds'). \
>>> option('checkpointLocation', checkpoint_path). \
>>> queryName(config['MDVariables']['topic']). \
>>>
>>> That function sendToSink will introduce two variables df and batchId
>>>
>>> def *sendToSink(df, batchId):*
>>> if(len(df.take(1))) > 0:
>>> print(f"""md batchId is {batchId}""")
>>> df.show(100,False)
>>> df. persist()
>>> # write to BigQuery batch table
>>> s.writeTableToBQ(df, "append",
>>> config['MDVariables']['targetDataset'],config['MDVariables']['targetTable'])
>>> df.unpersist()
>>> print(f"""wrote to DB""")
>>> else:
>>> print("DataFrame md is empty")
>>>
>>> That value batchId can be used for each Batch.
>>>
>>>
>>> Otherwise you can do this
>>>
>>>
>>> startval = 1
>>> df = df.withColumn('id', monotonicallyIncreasingId + startval)
>>>
>>> HTH
>>>
>>>
>>>view my Linkedin profile
>>>
Re: How to generate unique incrementing identifier in a structured streaming dataframe
Theoretically, the composed value of batchId +
monotonically_increasing_id() would achieve the goal. The major downside is
that you'll need to deal with "deduplication" of output based on batchID
as monotonically_increasing_id() is indeterministic. You need to ensure
there's NO overlap on output against multiple reattempts for the same batch
ID.
Btw, even just assume you dealt with auto increasing ID on write, how do
you read files and apply range pruning by auto increasing ID? Is the
approach scalable and efficient? You probably couldn't avoid reading
unnecessary files unless you build an explicit metadata regarding files
like the map file name to the range of ID and also craft a custom reader to
leverage the information.
On Wed, Jul 14, 2021 at 6:00 AM Sebastian Piu
wrote:
> If you want them to survive across jobs you can use snowflake IDs or
> similar ideas depending on your use case
>
> On Tue, 13 Jul 2021, 9:33 pm Mich Talebzadeh,
> wrote:
>
>> Meaning as a monolithically incrementing ID as in Oracle sequence for
>> each record read from Kafka. adding that to your dataframe?
>>
>> If you do Structured Structured Streaming in microbatch mode, you will
>> get what is known as BatchId
>>
>>result = streamingDataFrame.select( \
>> col("parsed_value.rowkey").alias("rowkey") \
>>, col("parsed_value.ticker").alias("ticker") \
>>, col("parsed_value.timeissued").alias("timeissued") \
>>, col("parsed_value.price").alias("price")). \
>> writeStream. \
>> outputMode('append'). \
>> option("truncate", "false"). \
>> *foreachBatch(sendToSink). \*
>> trigger(processingTime='30 seconds'). \
>> option('checkpointLocation', checkpoint_path). \
>> queryName(config['MDVariables']['topic']). \
>>
>> That function sendToSink will introduce two variables df and batchId
>>
>> def *sendToSink(df, batchId):*
>> if(len(df.take(1))) > 0:
>> print(f"""md batchId is {batchId}""")
>> df.show(100,False)
>> df. persist()
>> # write to BigQuery batch table
>> s.writeTableToBQ(df, "append",
>> config['MDVariables']['targetDataset'],config['MDVariables']['targetTable'])
>> df.unpersist()
>> print(f"""wrote to DB""")
>> else:
>> print("DataFrame md is empty")
>>
>> That value batchId can be used for each Batch.
>>
>>
>> Otherwise you can do this
>>
>>
>> startval = 1
>> df = df.withColumn('id', monotonicallyIncreasingId + startval)
>>
>> HTH
>>
>>
>>view my Linkedin profile
>>
Re: How to generate unique incrementing identifier in a structured streaming dataframe
Sorry a correction regarding creating incrementing ID in Pyspark
>>> df = spark.range(1,5)
>>> from pyspark.sql.window import Window as W
>>> from pyspark.sql import functions as F
>>> df = df.withColumn("idx", F.monotonically_increasing_id())
>>> Wspec = W.orderBy("idx")
>>> df.withColumn("idx", F.row_number().over(windowSpec)).show()
2021-07-13 22:04:15,001 WARN window.WindowExec: No Partition Defined for
Window operation! Moving all data to a single partition, this can cause
serious performance degradation.
+---+---+
| id|idx|
+---+---+
| 1| 1|
| 2| 2|
| 3| 3|
| 4| 4|
+---+---+
HTH
view my Linkedin profile
Re: How to generate unique incrementing identifier in a structured streaming dataframe
If you want them to survive across jobs you can use snowflake IDs or
similar ideas depending on your use case
On Tue, 13 Jul 2021, 9:33 pm Mich Talebzadeh,
wrote:
> Meaning as a monolithically incrementing ID as in Oracle sequence for each
> record read from Kafka. adding that to your dataframe?
>
> If you do Structured Structured Streaming in microbatch mode, you will get
> what is known as BatchId
>
>result = streamingDataFrame.select( \
> col("parsed_value.rowkey").alias("rowkey") \
>, col("parsed_value.ticker").alias("ticker") \
>, col("parsed_value.timeissued").alias("timeissued") \
>, col("parsed_value.price").alias("price")). \
> writeStream. \
> outputMode('append'). \
> option("truncate", "false"). \
> *foreachBatch(sendToSink). \*
> trigger(processingTime='30 seconds'). \
> option('checkpointLocation', checkpoint_path). \
> queryName(config['MDVariables']['topic']). \
>
> That function sendToSink will introduce two variables df and batchId
>
> def *sendToSink(df, batchId):*
> if(len(df.take(1))) > 0:
> print(f"""md batchId is {batchId}""")
> df.show(100,False)
> df. persist()
> # write to BigQuery batch table
> s.writeTableToBQ(df, "append",
> config['MDVariables']['targetDataset'],config['MDVariables']['targetTable'])
> df.unpersist()
> print(f"""wrote to DB""")
> else:
> print("DataFrame md is empty")
>
> That value batchId can be used for each Batch.
>
>
> Otherwise you can do this
>
>
> startval = 1
> df = df.withColumn('id', monotonicallyIncreasingId + startval)
>
> HTH
>
>
>view my Linkedin profile
>
Re: How to generate unique incrementing identifier in a structured streaming dataframe
Meaning as a monolithically incrementing ID as in Oracle sequence for each
record read from Kafka. adding that to your dataframe?
If you do Structured Structured Streaming in microbatch mode, you will get
what is known as BatchId
result = streamingDataFrame.select( \
col("parsed_value.rowkey").alias("rowkey") \
, col("parsed_value.ticker").alias("ticker") \
, col("parsed_value.timeissued").alias("timeissued") \
, col("parsed_value.price").alias("price")). \
writeStream. \
outputMode('append'). \
option("truncate", "false"). \
*foreachBatch(sendToSink). \*
trigger(processingTime='30 seconds'). \
option('checkpointLocation', checkpoint_path). \
queryName(config['MDVariables']['topic']). \
That function sendToSink will introduce two variables df and batchId
def *sendToSink(df, batchId):*
if(len(df.take(1))) > 0:
print(f"""md batchId is {batchId}""")
df.show(100,False)
df. persist()
# write to BigQuery batch table
s.writeTableToBQ(df, "append",
config['MDVariables']['targetDataset'],config['MDVariables']['targetTable'])
df.unpersist()
print(f"""wrote to DB""")
else:
print("DataFrame md is empty")
That value batchId can be used for each Batch.
Otherwise you can do this
startval = 1
df = df.withColumn('id', monotonicallyIncreasingId + startval)
HTH
view my Linkedin profile
How to generate unique incrementing identifier in a structured streaming dataframe
Hello, I am using Spark Structured Streaming to sink data from Kafka to AWS S3. I am wondering if its possible for me to introduce a uniquely incrementing identifier for each record as we do in RDBMS (incrementing long id)? This would greatly benefit to range prune while reading based on this ID. Any thoughts? I have looked at monotonically_incrementing_id but seems like its not deterministic and it wont ensure new records gets next id from the latest id what is already present in the storage (S3) Regards, Felix K Jose
