Need Ability to Specify In-DB Index

[Dave_1289]

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.

[Coxta45]

@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

[Dave_1289]

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 

[rsinnot]

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;

[Johnny]

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.

[FogoFortitude]

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. 

[ARich]

Hi All,

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

Best,

Alex

[Dave_M]

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?

[TedHansen]

I would also like to create indexes when creating Teradata tables.  I haven't seen any activity in this thread in over 2 years.  Is it still under review?

[jeffireland]

I'm also looking for the same thing.  The table I'm writing to MS SQL Server is 53 GB.  After adding a columnstore index (and changing some data types), the file size reduces to 4 GB.  This makes a tremendous difference on performance.  It would be great if we can specify this directly from Alteryx in the Output tool.

[simonaubert_bd]

Hello @AlteryxCommunityTeam Another idea for in-db that has been under review for 4 long years.

Alteryx really needs to rework on in-db before the competitors enter the dance.

Best regards,

Simon