We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Options in Output tool

cmcclellan
13 - Pulsar

I'm trying to find more information about the Output Data tool, specifically the Output Options

 

2018-04-24 14_38_53-Clipboard.png

 

I need to know specifically how the "Delete Data & Append" option works in regards to big tables (ie 1+ million records).

 

Does this:

 

a) delete the records in the table, then add the new records, OR

b) truncate the records in the table, then add the new records, OR

c) it depends on the database (in which case I'm using Netezza at the moment)

 

I can't find anything this specific in the help files and the processing time does have an impact at this level and I want to avoid writing the Pre Create SQL Statement if I can.

12 REPLIES 12
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@cmcclellan:

Delete and Append:

- The entire table's data (not the table itself/structure) will be deleted and the data feeding the Output tool will replace it.

Append Existing:

- Take the records feeding the Output tool will just be added (like a union) into the table.

Overwrite:

- Imagine it executing this SQL statement: Drop Table (TableName); Create Table (your incoming data here).  This option would be used over the Delete and Append if you wanted to change the primary keys or needed to add/remove fields

 

cmcclellan
13 - Pulsar

What I was meaning was in the "delete and append" is the "delete" really a delete or is it a truncate ?

KaneG
Alteryx Alumni (Retired)

I performed a quick profile and on my SQL instance, it performed a Truncate.

cmcclellan
13 - Pulsar

Thanks mate, we're using Netezza but I'm going to assume it works the same way :)

anotherusername
8 - Asteroid

Is there any way to intercept the SQL being sent to the server from the In-DB Output Tool to confirm this?

 

I tried using the Dynamic output - but that can't intercept from between two in-DB tools because it connects to the back-end of an in-DB tool and then feeds it out to std tools.  (reason why I'm asking, is that if the in-DB tool performs TRUNCATE then there is no need to do the Netezza GROOMs on the tables being overwritten to recover space on large or regular overwrites - and which allows us to confirm with the DB admins that we're adhering to their best-practice - though perhaps we could ask them to monitor the server log to confirm for us)

KaneG
Alteryx Alumni (Retired)

There is no way to intercept it on the In-DB tools as if you put the Dynamic Output tool after a Write In-DB, then you'll just get the read query..., monitoring the table is the only way that I know.

anotherusername
8 - Asteroid

Update:

 

I've done some testing with both in-DB (Write Data In-DB) and std Output tool (Output Data).  Both are set to DELETE DATA & APPEND and I am getting DELETE commands being issued to Netezza.

 

(the 2 tools are in the same workflow, writing data from the same source, but to 2 different tables in the DB)

 

 

Have had a server admin checking the logs for me.

 

This soft-delete in Netezza is probably important to take note of, as it will therefore need regular GROOMs to be performed on tables with regular/large churn.

 

(am using 11.7 - perhaps may differ between versions?)

KasiaP
7 - Meteor

Hi,

Can anyone explain me how update options work?

I mean the one highlighted in yellow:

Przechwytywanie.PNG

KasiaP
7 - Meteor
Or can anyone direct me to the place where it is described?
Labels