Backup & Restore

[lmit]

Hi All,

I have a requirement where I need to backup certain tables daily and store them in files. How many tables need to backup will change like today I may backup 10 tables tomorrow can be 15
Is it possible, if so how can I achieve it?

The same files will be used for restore , while restoring I many need to restore one table or more than one table or all the tables

Basically I want backup & restore should be happen without changing the job code , these 2 are separate jobs

Any suggestions would be very great full

Thanks in advance,
Lmit

[Anonymous]

How would you do this in code? If you think about what you are asking it is an incredibly difficult  problem to solve, even for someone writing bespoke code by hand. The problems you have are....

1) How do you identify the tables to be backed up and restored?  - Possibly there is a simple answer to this like a list of tables in a file or database, or maybe searching the database for tables that have been modified. This would need to be considered in your solution.

2) Are you doing a dump of the entire tables or incremental backups? - If you are intending to dump the whole table, that would be easier. If you want incremental changes, that makes it a lot more complicated.

3) How will you identify the datatypes? - You are dumping to a flat file which makes everything essentially a String. When you want to restore this, you will have to convert from a String to the correct data type. You cannot easily do this dynamically and it would require some bespoke code.

If you want to do this in Talend, you cannot expect to be able to create an export and an import job unless you are intending to use built-in backup and restore functionality of the database you are using. THIS is very possible. However if you want to bypass the built-in backup and restore functionality of your database, you won't be able to do it with 2 jobs very easily at all. It would be easier to create a job per table and dynamically call the relevant jobs depending on what you want to backup and restore.

[lmit]

Hi rhall ,
Thanks for your reply

I will keep the table names in a file which I want to backup and restore(both separate files)

I need complete data not incremental data.

If I create one job for one table then I will end up with n jobs if I have n no of tables.

No restrictions of using only 2 jobs

Can’t it be achieved by context or loop components or any other way


Thanks in advance,
Lmit

[Anonymous]

The easiest way to achieve this is to use the database's built in backup and restore functionality called via Talend. If this is command line based you can use a tSystem component, if it can be run from within the DB environment you can use tDBRow to call whatever statements you need. This can indeed be done with a tLoop and context variables. 

[lmit]

Hi All,

Thanks rhall for your reply 

I cant use DB build in functionality.so i need to achieve with other components

So can anyone help with the idea to achieve this logic

Thanks,

lmit 

[Anonymous]

There is only one way I could think of to do this and it can't be done with the Open Source version. You would need the subscription version to do this as it comes with the Dynamic schema functionality. I wrote on blog on how to do this between two different databases, you may be able to extrapolate from this....

https://www.talend.com/blog/2019/11/11/migrate-data-between-databases-one-job-using-dynamic-schema/

[lmit]

thanks i will check

[JohnRMK]

Hello,
What are you using as a version of Talend? the Open Studio or Enterprise version (dynamic scheme)?
According to your answer, there is the possibility of making a dynamic backup with multiple sources and multiple columns and with a single Talend job.

But, for restoration, it remains more complex. If you have for example an Id column in all your tables, it is possible with a single job but there too, your data must not be linked (because of the referential integrity).

I can send you a generic job for backup, just give me the type of source and target.

[lmit]

Thanks cbma for your reply.

My talend version is 6.5 enterprise version

My source is sql(Maria DB) and target is csv file with pipe delimiter

Thanks,
Lmit

[JohnRMK]

Hello,
You will find attached an example of jobs which allows to extract all the tables in CSV files. And conversely but it is a simple insertion and no integrity control.

I use a config file for connections, you can add context variables to not save some tables

You can ignore some tables in the component which lists all the tables in the DB

In the component of the flat files, I added the date and I create a directory for each table, I leave you the leisure to modify.

Actually, you will have to perform tests on the requests before extracting the data and you can even edit the requests dynamically from the config file.
for example by adding a field in config file
sqlWhere = FirstName LIKE 'Jo%'
and you add this clause at the end of your request "SELECT * FROM" + GLOBAL_VARIABLE + "WHERE " + context.sqlWHERE
You can do <key,values> set <TableName; sqlWHERE> for example and you get that with a tFlowToIterate.

For Dynamic SQL Query https://www.talend.com/blog/2016/03/30/talend-job-design-patterns-best-practices-part-2/?utm_medium=...

If you have other questions ! 

TalendForumResponses.zip