In Data Base

[SJBI]

Hi ,

I've a small query on In DataBase. 

When we use DataStream-in tool that data. Alteryx creates moves temporary data in the database.

How does Alteryx can create a temporary table? lets say if I have only Read only access to a table/view in a schema how the tool Works/creates a temp table?

Regards,

SJ

[MarqueeCrew]

@SJBI,

It is magic!  

User tables are written to, not the "database" where you only have read privileges.  Alteryx creates a temp table that is used to join data within your RDBMS.  When the query runs, the data is present.  After the workflow completes, the temp table is deleted.  

That's the secret.  Don't tell anyone.

Thanks,

Mark

[SJBI]

Thank you for the reply. Can you explain "User tables are written to, not the "database" where you only have read privileges". 

[MarqueeCrew · Accepted solution]

The database might look something like Database_name.Schema_name.Table_Name in the database.  Your user table is something like:  userid.tablename.

the SQL would look something like

CREATE TABLE userid.temporary_table_name   
    ( account_id int, favorite varchar(255)
);

If you have a userid with read access to the database, you should be able to create tables as userid.table.

Does this help?

[SJBI]

please correct me if I'm wrong. To use the indatabase tool, alteryx creates tables at time of installation.Along with tables/views , Alteryx created tables are giving access to the user When user use the In-Database tool. The tool uses those tables for doing operations (read/write/join).

Regards,

SJ

[MarqueeCrew]

The temporary tables are created only upon execution of the workflow and are deleted upon completion.  Installation of Alteryx has no impact on in-db functions.  You can be connected to many different database instances (teradata, oracle, sqlserver, etc) and create temporary table in each RDBMS that you have access to.

Would it help if I setup a webex with you and I could try to help you remotely?  If so, please PM me your email.

Cheers,

Mark

[SJBI]

Sent you mail id.

[SJBI]

Sorry for the delay. I understood. Thank you so much.

[eizarahman]

Where does the temporary table resides in? Locally?