Best way to find new rows in parquet file to insert into table

Question

Best way to find new rows in parquet file to insert into table

PeterSh 171

I get several JSON files each day which are processed (flattened and a few calculated fields) and stored in a parquet file. There are millions of rows in the parquet file, and each update cycle adds 50-60 thousand additional rows.

One process that runs on this file keeps an SQL table up to date with the changes. The SQL data is mostly the same but has some additional data and calculations to make querying easier.

My question relates to the best way to find only new rows to add to the SQL table.

At present, I run a data flow that has both the parquet file and target table as sources, then uses the 'Exists' component to find rows that don't exist, and adds them to the table.

My concern is that this takes 40+ minutes to run. This seems to be split between around 20 minutes to read the SQL table, then 20 minutes to mode the data from a staging table into the target table.

Does anyone have any suggestions on how to do this better?

Thanks

Answer 1

ShaikMaheer-MSFT 35,796 Microsoft Employee

Hi @PeterSh ,

Thank you for posting query in Microsoft Q&A Platform.

To summarize your ask, its more about best way to find only new rows form parquet file to process them to SQL table. Please correct me if I am wrong.

Since you are loading all rows in to single file, you are ending up with using source and target as source transformations and using exists transformation to filter out new rows.

You could consider either of below approaches if that helps.

Save your new rows every time as a separate file with some datetime in file name. So that we can take only that file and process it in to SQL Table.
Consider having some flag column or date Time column which tells us that these are new rows. So that we can use source transformation for our source file and then Filter transformation to filter out new rows based on flag or datetime value.

Hope this helps. Please let us know how it goes. Thank you.

ShaikMaheer-MSFT 35,796 Reputation points Microsoft Employee

2022-04-04T16:24:38.143+00:00

Hi @PeterSh , Just checking if you get a chance to share updates? Thank you.
ShaikMaheer-MSFT 35,796 Reputation points Microsoft Employee

2022-04-05T16:23:49.307+00:00

Hi @PeterSh , Just checking in to see if the above answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
PeterSh 171 Reputation points

2022-08-17T05:55:55.277+00:00

Hi @ShaikMaheer-MSFT ,

Sorry for the lack of response. I posted this and promptly forgot about it. The 'follow' functionality of this site really needs to be re-thought. It's much harder to find threads you're following than it should be.

This is still an ongoing issue, though I've gone through a lot of iterative changes since.

The main issue is that the daily JSON files are not just new data - they also contain data from previous days. These can be duplicates or fill in gaps from previous days. As the files arrive, I merge the new data with the existing data to remove duplicates and to perform a running calculation across all rows (the data is from meter sensors that give the current reading - I calculate the usage between readings for each meter, as this is the metric we need). This seems to be the only way to get coherent usage data as new data arrives and gaps are filled in previous days.

As I'm merging all the data, I end up just writing the whole parquet file again with its (now) 30+ million rows.

Given this, my best option here is probably to use your second suggestion - insert a timestamp from when the row was added to the parquet and upsert them from there.

My other issue is reading from a large parquet file and writing back to it in the same flow. I'm using round-robin partitioning (8), writing into the same folder, with a pipeline action to delete all parquet files older than the SUCCESS file in order to keep things tidy. The flow fails if I try to clear the folder before write.

Best way to find new rows in parquet file to insert into table

1 answer

Activity