topic Re: How to prevent sql queries in 2 notebooks from reading the same row from a Table ? in Data Engineering

How to prevent sql queries in 2 notebooks from reading the same row from a Table ?

KrishZ — Thu, 15 Sep 2022 06:35:08 GMT

I have an SQL query to select and update rows in a table.

I do this in batches of 300 rows (select 300 , update the selected 300 , select new 300 and update the newly selected and so on..)

I run this query in 2 different notebooks concurrently to speed up my processing

Can someone tell how to prevent the same row from the table getting selected in the sql query ?

Re: How to prevent sql queries in 2 notebooks from reading the same row from a Table ?

PriyaAnanthram — Thu, 15 Sep 2022 07:30:26 GMT

Not too clear what your trying to do but I'd try to break the data set logically using mbe a column like date and then process them Hope that helps

Re: How to prevent sql queries in 2 notebooks from reading the same row from a Table ?

PriyaAnanthram — Thu, 15 Sep 2022 08:51:54 GMT

mbe A rownumber column that you can add may help you run it in batches of 300

Re: How to prevent sql queries in 2 notebooks from reading the same row from a Table ?

KrishZ — Thu, 15 Sep 2022 10:26:39 GMT

Yup that's exactly my current plan but there's an issue here. I will explain:

I have 1 million rows in my table

Let's say I give row# 1 to row# 500000 to 1st notebook

and

I give row# 500001 to row# 1 Million to 2nd Notebook.

What if the data is such that the first half (row# 1 to row# 500000) takes 1/10th the time for processing when compared to 2nd half (row# 500001 to row# 1 Million) ?

You see how this can mean Notebook 1 will finish way before Notebook 2. Ideally both Notebooks should run for equal time (around the same time) to finish my activity the fastest..

Hence predetermining the datasets for each notebook is not efficient. The notebooks should dynamically ingest a new batch(of 300 rows) as soon as it finishes the current batch. But my problem is both notebooks might end up ingesting the same batch .

Let me know if that makes sense 🙂

Re: How to prevent sql queries in 2 notebooks from reading the same row from a Table ?

Anonymous — Wed, 28 Sep 2022 07:34:20 GMT

Hi @Krishna Zanwar

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.

We'd love to hear from you.

Thanks!