How do I clear a table in TOS before transferring data into it?

Six Stars

How do I clear a table in TOS before transferring data into it?

Hello everyone, 

 

I am using TOS for Data Integration for transferring my data from data source to data warehouse, both on PostgreSQL databases. 

 

Before I transfer my data, I want to empty data warehouse table. How can I achieve this?

 

Thanks!


Accepted Solutions
Employee

Re: How do I clear a table in TOS before transferring data into it?

Hi,

 

    The designer tab look exactly like a normal insert. Please refer the skeleton diagram below.

image.png

 

The only change you need to do is that instead of "Default", you need to select the "Truncate Table" option in the drop down (shown in previous post).

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

All Replies
Employee

Re: How do I clear a table in TOS before transferring data into it?

Hi,

 

    Please select the drop down Truncate Table under Action on Table label.image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do I clear a table in TOS before transferring data into it?

Thank you for such an early response.

 

Can you please share the connection I need to make in order to implement this?

 

Thanks!

Employee

Re: How do I clear a table in TOS before transferring data into it?

Hi,

 

    Sorry. I didn't get your query. What do you mean by sharing the connection?

 

     You need to use your existing connection to do it but the only change is in Action on Table.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do I clear a table in TOS before transferring data into it?

To implement the "truncate table" action, what does the Designer tab look like or how do I connect this DB to implement this action?

 

Thanks!

Seventeen Stars

Re: How do I clear a table in TOS before transferring data into it?

This action (truncate) will be performed as the first action when the subjob starts. You do not need any special settings for the truncate action, the connection used to write the data in your table will be used to truncate the table first.

Employee

Re: How do I clear a table in TOS before transferring data into it?

Hi,

 

    The designer tab look exactly like a normal insert. Please refer the skeleton diagram below.

image.png

 

The only change you need to do is that instead of "Default", you need to select the "Truncate Table" option in the drop down (shown in previous post).

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: How do I clear a table in TOS before transferring data into it?

1. In case of multiple tMaps, I do not know which tMap will run first and thus do not know which output table do I truncate. How do I implement this there?

 

2. If I perform this truncate table action and write the data transfer query in the same job, will it work?

 

Thanks!

Employee

Re: How do I clear a table in TOS before transferring data into it?

Hi,

 

    In case of multiple tMaps, you need to give the truncate in first order.

image.png

 

But there is a flip side in this approach. If the first dataset is empty, it will not invoke Truncate. So you can gather all the datasets to temp files and then do a tUnite to merge all the different files. Once the file is merged, you can insert them to target DB with Truncate option selected.

 

The answer for your second question is "Yes" :-) Please try it once and you will understand yourself.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seventeen Stars

Re: How do I clear a table in TOS before transferring data into it?

Nikhil Thampi - Sorry to say, THAT IS NOT TRUE!

The truncate action will be performed regardless of there a records to write or not! Please take a look at the source code of the job and you will see the truncate action will be performed in the begin-part of the component -> this means BEFORE the actual flow starts.

Employee

Re: How do I clear a table in TOS before transferring data into it?

@jlolling 

 

Thanks for the correction :-) I overlooked that part.

 

My sincere apologies.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Seventeen Stars

Re: How do I clear a table in TOS before transferring data into it?

Do not worry, you are not alone. Most of the developers - including some Talend guys - have not learned about the 3 parts of a component:

begin (B), main (M) and end (E) part.

Imagine a job with 3 components - just like your example. tFileInputDelimited_1 (name it component 1), tMap_1 (name it component 2) , tPostgresqlOutput_1 (name it component 3).

How the code will be looks like with their begin, main and end parts:

B3 > B2 > B1 > M1 > M2 > M3 > E1 > E2 > E3

I always complain, such information cannot be read in the Talend Wiki and is not part of any trainings!

Employee

Re: How do I clear a table in TOS before transferring data into it?

@Harguna 

 

    We hope you have got the expected result for this post. Could you please mark the topic as closed by marking the posts which has helped to resolve your query?

 

    Its a great way to close the topic and it will help other community members also during their reference.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

    


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download