Option to not generate file if row count is 0
sashk8 opened this issue · 4 comments
Hi Markus,
Is there a way to prevent output file to be generated if the returned number of rows from a query is 0?
I saw in the code that the file is not generated if the query doesn't return any result at all. However, as soon as a schema is returned, a file is generated - even if no rows are returned.
Unfortunatelly I don't have permission to modify the views / stored procedures to do this kind of check at the data source level.
Best regards,
Sašo
Hello Sašo ,
yeah in ODBC there is a distinction between no Result Set and and empty Result Set. Typically you would only get no result set if the type of SQL statement does not generate Results (e.g. INSERT). Ultimately driver implementations differ here.
Is there a way to prevent output file to be generated if the returned number of rows from a query is 0?
Currently there is not. Seems reasonably to me though. I wonder even if this should be the default. Could you also help me to understand the context better? Do you use odbc2parquet as part of an automated data pipeline? Why are empty files bad for you?.
Best regards,
Markus
odbc2parquet 0.15.0 has been released featuring the --no-empty-file flag.
Hi Markus,
Currently there is not. Seems reasonably to me though. I wonder even if this should be the default. Could you also help me to understand the context better? Do you use odbc2parquet as part of an automated data pipeline? Why are empty files bad for you?.
I plan to use odbc2parquet in a data pipeline export process. The data from an on-premise ERP system will be exported to parquet files, uploaded to a data lake and imported to a cloud data warehousing solution. We are using "change data capture" functionality of SQL Server to export the new/modified data in quite high frequency (every 30 minutes). The CDC creates specific functions/views for each table that return the delta records only - in case of no changes in the table, an empty result set is returned (schema, but no rows).
Every 30 minutes we get new data in around 50 tables - other tables change infrequently. The total number of tables that we export is around 300. So, at the moment we get ca. 250 empty files and 50 files with data generated every 30 minutes. Basically, the solution works, but it's not very practical to get all those empty files (unneccesary uploads, unneeded processing, etc.). I could do additional check before upload and delete the empty files - but that's additional step that I'd like to avoid.
So, I've seen you've already released a new version with the additional option flag added. Amazing! Thank you very much!
Have a nice Christmas!
Regards,
Sašo
Hello Sašo,
thanks for your detailed description of the use case. This truly helps to make odbc2parquet better. Something which sometimes is helpful if running odbc2parquet in a pipeline, is that it can also stream its output to stdout. This allows for your data pipeline sometimes to be expressed with a pipe in the console. It does not sound to me like you would have use for it right now, but things always change in our line of work.
I could do additional check before upload and delete the empty files - but that's additional step that I'd like to avoid.
I guess that step exists now anyway, just within odbc2parquet.
So, I've seen you've already released a new version with the additional option flag added. Amazing! Thank you very much!
You are welcome.
Have a nice Christmas!
And a nice Christmas to you, too!
Regards,
Markus