Generate SQL queries

[BenjaminDecreusefond]

Hi !

We just discovered this tool and is doing an amazing job so thank you !

Our goal is to compute all the data that should be exported given a table while also keeping the data integrity. Jailer seem a nice tool for that and we are wondering if we can use it in a slightly different way.

From our understanding today, jailer generates the file with the data already extracted from the database. We would like to know if it's possible to alt Jailer's execution at query build time so that we can use jailer as query extractor generator and use other tools like spark, aws glue, etc.. to perform the export.

Do you think that makes sense ?

Regards

[Wisser]

Hello,

Interesting idea, but I don't think it would be that easy to implement.

In general, these extraction requests depend not only on the data model, but also on the data itself. This rules out the option of generating the queries statically in advance and passing them on to another tool for processing. Dynamic generation that takes the data into account does not offer any benefits over the current approach.

The only way to avoid dependency on the data is if the extraction model is cycle-free, taking the restrictions into account. However, cycles are generally the norm. Consider, for example, the two tables ‘Customer’ and ‘Payment’ in the Sakila demo database. If you want to export all payments for each customer, you already have a cyclic relationship between these two tables.

Regards

[BenjaminDecreusefond]

Hi !

Thank you for your quick answer !

I'm curious to understand how does the data is generated by Jailer ? It does not run a query over the database ? sorry if it's a dummy question.

I wonder what alternatives would we have then 🤔

Regards

[Wisser]

The data is, of course, determined via queries. An overview of the procedure with an example can be found here:
https://wisser.github.io/Jailer/design.htm

Why not use Jailer directly for data extraction?

[BenjaminDecreusefond]

We have a need for scalability with tables that weigh more than hundreds of gigabyte so to make sure to remain performant while exporting we need to use tools dedicated for this usage. I'm trying to think of a how we can integrate Jailer's output with such tools in our use case because we would like to export queries's result to s3 as well, that's why having queries in the first place is easier for us, we can put them to any ETL tool that does the job.

Don't take me wrong, your solution is amazing ! I just think we need to twist output a little to fit our need :)

[Wisser]

The performance of data extraction does not depend on the size of the source tables, but on the amount of data extracted from them. If the latter is not huge, performance will probably be fine. Perhaps a trial use will bring clarity here.

[BenjaminDecreusefond]

Unfortunately, it can appear to be huge for us as we have a huge legacy of unordered datas. We'll try to see if we can cook something !

Thank you !