We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Terradata Volatile Tables

Amarendra
10 - Fireball

Hello Everyone, 

 

I am working on something which requires creating a couple of volatile tables in Terradata. My idea is to automate a couple of scripts which create these volatile tables. Can this be done using Alteryx? 

 

Thanks, 

Amar

12 REPLIES 12
JohnJPS
15 - Aurora

Hi @Amarendra

One thing you could try is the dynamic input, which allows you to specify some SQL to run.  I haven't worked with Teradata and Alteryx other than simple data pulls, and I don't have access to experiment, but it may be worth a look for you.

Hope that helps!

Amarendra
10 - Fireball

@JohnJPS I did look into that option, I have come to understand that Alteryx does not go well with the creating temp tables. I finished my workflow but had the guys create temp tables for me. 

Thanks you for looking into this. 

Andy_Katona
8 - Asteroid

Thank you for this. I wasn't able to create volatile table either. So I just created it myself in Teradata first.

 

Now, my workflow will not see the temp table. Have you had this issue?

 

Thank You,

Andy

bkclaw113
8 - Asteroid

Teradata Volatile tables are specific to a session, so if you create the volatile table in one Application (say TD SQL Assistant) and then try to load data to the table in a different application (say Alteryx) the second app is never going to be able to see the table you created as the two applications have unique sessions.  This is very much on design so you never need to worry about cleaning up volatile tables as they automatically get destroyed when the session ends.

 

Has anyone tried using the pre-execution SQL in Alteryx to send the DDL commands to create the volatile table in an output tool node?

Andy_Katona
8 - Asteroid

I was creating the Volatile table inside of Teradata so it would stay all day. Then working to have my workflow find my temp table to process. Yet, that didn't seem to work either..

I have to bit 65 Million member records for searches. Because of Teradata limitations I have to split that 5 times so it doesn't time out. Thus, I thought if I made the volatile table it would go quicker.

 

In Teradata I can make a volatile table with the 65 million records in a few minutes as opposed to the 1hr it takes to initialize through Alteryx.

 

I am going to test the pre section but I thought I had.

Amarendra
10 - Fireball

Hey @Andy_Katona

 

Any updates with what you were trying to do, I am interested in looking at your workflow. 

 

Thanks, 

Amar

Andy_Katona
8 - Asteroid

No, no luck with the temp tables. Yet, my time is limited for testing. I am working now to try & get connected to Oracle so I can run missing A1c reports.

bkclaw113
8 - Asteroid

I happened to come back to this and was able to utilize a Teradata volatile table in the "Pre SQL Statement" option of an input tool.  I combined this with a date control and the action was able to update the value in the pre sql statement.  This approach definitely has limited use, but you may be able to expand on this to make something more robust.

Robbobu1
7 - Meteor

Thanks @bkclaw113  the pre-sql worked for me but only when using the teradata driver not a generic ODBC (opposite of what is mentioned in a  different thread).  Great tip!.

Labels