community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Options in Output tool

Alteryx Partner

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.

@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

 

Alteryx Partner

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

Alteryx
Alteryx

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

Alteryx Partner

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

Alteryx Certified Partner

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)

Alteryx
Alteryx

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.

Alteryx Certified Partner

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?)

Meteor

Hi,

Can anyone explain me how update options work?

I mean the one highlighted in yellow:

Przechwytywanie.PNG

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