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

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Need Ability to Specify In-DB Index

Please add the ability to specify indexes when creating a table with the Write Data In-DB tool.

 

When running Teradata SQL using the Connect In-DB tool I need to create a table on the database using the Write Data In-DB tool and do numerous updates before bringing the data to the PC.  Currently there is no way to create a unique primary index (or any other index) when the Write Data In-DB tool creates a table.  This causes Teradata to consume huge amounts of wasted space.  Today I created a table with 160 columns and 50K rows.  This consumed over 20 Gigabytes of data with 19.7 Gigabytes of wasted space.  In Teradata the way to control wasted space(skew) is by properly defining the index which can't be done today.

7 Comments
Bolide

@Dave_1289,

 

This is by no means a permanent solution, but more of a damage control workaround.  I recently developed a Table Compression Application for our team's Teradata Sandbox Database.  I was running into the same problem when pushing the tables back into the database.  By default, Teradata throws an index on the first column of the table, so I simply used the Record ID tool to append a unique record ID field to the table (you can select the field position to be the first field) and then loaded it with the id field.  Usually, Teradata will make much better distribution of the ID field than the default first field of the tables that my compression app uses.

 

Like I said, it's not a permanent solution, but it has helped tremendously with skew issues in the app that I built.

 

P.S. - I highly recommend using the Auto Field tool when handling bigger data loads into DBs.

 

Regards,

Taylor

Thanks.  My solution was similar.  Since the data being written to the database was coming from SQL executed on the same database my workaround was to use the Teradata ROW_NUMBER as the first column.  In my testing this reduced skew even better than assigning a unique prrmary index.  The downside is that if the newly created table is refenced natively in Teradata later in the workflow (which is my case) then the Teradata optimizer likely does not take the most efficient path to execute the new query which could cause spool space (query failure) problems.

 

The second related problem is that since all my work was done In-DB the Write Data In-DB Tool does not have the feature to run Post Create SQL.  I still have no workaround for this.

 

I normally use Post Create SQL on the Output Data Tool to remove fallback on my tables.  Teradata natively creates 2 copies of every table.  Removing fallback removes the 2nd copy of the table cutting disk usage in half.   In my case tables are recreatable in the event of a system disk failure.

 

Dave 

Atom

For those needing an example of how to remove Fallback in the Post Create SQL Statement, see example below:

 

ALTER TABLE DB_NAME.TABLE_NAME, NO FALLBACK;

Meteoroid
Well, it's a usefull ability even If I do not use Teradata but Hive in an Hadoop cluster. So I would like this for Hive as well.

I use Teradata frequently; having the ability to create a PRIMARY INDEX upon table creation within Alteryx itself would be of great value to me and others I work with. Without it many users of Alteryx may unknowingly create SKEW when creating Tables on Teradata via Alteryx. 

Alteryx
Alteryx
Status changed to: Under Review

Hi All,

 

Thanks for the feedback- please keep starring this if you're interested!

 

Best,

Alex

Atom

I am new to the community - and I have a similar question, there is a facility to add sql code on the connect In-DB tool but there are no options to add code "after" the Write Data In-DB.  I am working in an Oracle environment and would like to add indexes and keys to the output table.  Currently jumping over to PLSQL to run the code and don't see a way to add the indexes within Alteryx.  Am I missing something?