Setting primary key for result table in recipes

[Antal]

Hi there,

I'm running into the issue that the database (Teradata Vantage) doesn't like a lot of large tables being created without a primary key. Leaving the primary key in the table settings tab to Default, results in the database having to figure out a primary key on it's own and the result of that is that everything from one project seems to go to a couple of specific AMPs, which then overload.

Setting a (unique) primary manually from the table settings tab alleviates this.

Only.... using this method, I first have to run a recipe to create the table, then go into the resulting table settings and change the primary key manually and then rerun the recipe to create the table again with the correct primary key.

That seems like a lot of unnecessary steps (and waiting for the tables to be created). Is there a way to set the primary on the output table from the recipe settings itself, without having to first create the table and rerun?

If not, this would probably be a handy option to have.

[AlexT · Accepted solution]

Hi,

You shouldn't have run the recipe to alter the Automatically generated SQL syntax.

When you create your recipe output before running the recipe, simply go to the Dataset - Advanced - Manually defined and then alter the table to create SQL to include your primary key and save. When running the recipe the first table created will have the primary key you defined. 

Let me know if that works for you.  The disadvantage of using this approach is will need to manually maintain the SQL creating your table.

You can also submit this to your https://community.dataiku.com/t5/Product-Ideas/idb-p/Product_Ideas to allow for easier management of primary key.