Alteryx Designer Desktop Discussions

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

Write Data In-DB temporary Table.

mariop
5 - Atom

So I'm using the Write Data In-DB and under the creation mode selecting Create Temporary Table. When I excute the workflow it all goes well until it tries to create the temporary table and I get an error message:Error: Write Data In-DB (5): Error running PreSQL on "NoTable": [Teradata][ODBC Teradata Driver][Teradata Database] The user does not have CREATE TABLE access to database xxxxxx.

This is correct, i dont have access to the xxxxxx database and would like to change where the temporary table is being created. Is there a way to change what database this temporary table is created on?

 

8 REPLIES 8
MikeA
Alteryx
Alteryx

Hi @mariop,

 

Alteryx does not manage the temporary tables in a database and these are set up by your DBA or person that administers the database; Alteryx can only leverage these tables once they have been set up in the database itself. There is not a way to change where a temporary table is being created from Alteryx. 

 

If you are able to however, you could create a new table in the Write Data In-DB tool to a location that you have access to:

Capture.JPG

 

Thanks!

 

 

 

Jash
7 - Meteor

Hi -

I have a similar problem. I DO have access to create a temp table in our Teradata database.

The SQL to create temp tables in Teradata is "Create MULTISET VOLATILE TABLE Temp_tablename...."

I tried executing this in a SQL Assistant and it worked perfectly.

 

However, Alteryx generates the SQL as "CREATE  MULTISET  TABLE "AYX17040533fe5552f0477a8605" ..." missing the "VOLATILE" word in the statement. I do not have access to create permanent tables, and hence my workflow fails. Error message is noted below.

 

Data Stream In (14) Error creating table "AYX17040533fe5552f0477a8605": [Teradata][ODBC Teradata Driver][Teradata Database] The user does not have CREATE TABLE access to database PRD_CONTR_DMV. ¶CREATE  MULTISET  TABLE "AYX17040533fe5552f0477a8605" ..... 

 

How do I fix this issue? Can the "VOLATILE" syntax be added to the Alteryx generated SQL statement when temp table option is chosen in the In-DB Data Stream In tool? Please help!

HenrietteH
Alteryx
Alteryx

Hi @Jash and @mariop

 

For the in-db tools Alteryx creates two separate sessions for read and write. The stream in tool first writes data in one session and then reads it in in another session. If it created a volatile table in the write session, the read session would not have access to it, which is why it doesn't create volatile tables. 

 

This post by another community user has a macro attached that creates a SELECT * FROM VALUES query for the dynamic input in-db tool that allows him to bring data into in-db without writing a table. 

 

You might be able to adapt his macro for your needs (NOTE: The dynamic input in-db tool does not accept CREATE TABLE statements, only SELECT statements). 

 

 

 

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

davidhenington
10 - Fireball
 
simonaubert_bd
13 - Pulsar

Hello,

The main issue is that you can't choose where you create the temporary table, leading either to not using the function or safety issues. there is an idea to choose the temporary tables path:

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/In-DB-Choose-the-base-or-schema-of-the-Temp-...

 

 Best regards,

Simon

apathetichell
18 - Pollux

@simonaubert_bd isn't this DB specific and at times controlled by the schema setting in the ODBC 64 config? For example - with Snowflake, if I enter a DB and Schema in ODBC 64 doesn't that carry over to my temp table? I haven't checked this in months but that is my vague memory.

simonaubert_bd
13 - Pulsar

@apathetichellDepending on the database, you can set one in your dsn (or not). But if you want to have several places (because you work for several entities), you will need several dsn, several alteryx connection etc.

An other way we can't use in alteryx is to pass this parameter through session parameters (such as set something), since we can't use pre-sql on in database.

apathetichell
18 - Pollux

This is all part of my proposed "SQL" tool....

Labels