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

Alteryx designer Discussions

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

Terradata Volatile Tables

Asteroid

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

ACE Emeritus
ACE Emeritus

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!

Asteroid

@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. 

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

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

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.

Asteroid

Hey @Andy_Katona

 

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

 

Thanks, 

Amar

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.

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.

Labels