topic Re: Backup & Restore in Talend Studio

Backup & Restore

lmit — Sat, 22 Feb 2020 18:53:56 GMT

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

Re: Backup & Restore

Anonymous — Mon, 24 Feb 2020 12:19:03 GMT

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.

Re: Backup & Restore

lmit — Mon, 24 Feb 2020 15:19:15 GMT

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

Re: Backup & Restore

Anonymous — Tue, 25 Feb 2020 20:34:51 GMT

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.

Re: Backup & Restore

lmit — Fri, 28 Feb 2020 05:00:42 GMT

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

Re: Backup & Restore

Anonymous — Fri, 28 Feb 2020 09:22:52 GMT

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/

Re: Backup & Restore

lmit — Tue, 03 Mar 2020 08:48:05 GMT

thanks i will check

Re: Backup & Restore

JohnRMK — Tue, 03 Mar 2020 13:19:18 GMT

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.

Re: Backup & Restore

lmit — Tue, 03 Mar 2020 15:33:04 GMT

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

Re: Backup & Restore

JohnRMK — Tue, 03 Mar 2020 16:50:36 GMT

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.

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=bloglink&utm_source=talend&utm_campaign=bloglink

If you have other questions !

TalendForumResponses.zip