Large Table Copy & Rename

[dlash]

I am looking to make a copy of a very large table from SQL Teradata (80 columns millions of rows) and append 'YYQ#' (for example _23Q2) to the table name monthly for historical backups.

I have a workflow that does this using Teradata bulk but it is taking about 10 minutes and I have 8 tables. I am hoping there is a faster way.

Input -> Formula to create the date as a column -> Output using that column to append. I can quickly make a copy of the table using PreSQL and the output tool is great at appending to the table name, but I don't know how to put the two together because of the calculation that is needed.

I thought I could create the copy with the appended date with 1 row and then insert using another input tool, but I run into the same issue that the appended date will be different each quarter.

Thank you

[BS_THE_ANALYST]

@dlash perhaps leveraging the IN-DB tool palette here. This way you can do the processing at the database level. If the data already exists in the database, there's no point streaming it onto your local pc just to then perform a massive upload: https://help.alteryx.com/20231/designer/database-overview 

[dlash]

@BS_THE_ANALYST thanks for the response, can the In-DB support the 'Append Suffix to File/Table Name' function that is in the Output Data tool? I don't see this functionality in the Write Data In-DB tool. 

[BS_THE_ANALYST]

hey @dlash. 

Just to confirm what you're asking for:

1. You have a SQL table and you want to split this into separate tables each with different names? I.e. like the output data tool does. It gets the table name from a field, filters the data corresponding data, and creates a table(s) with the filtered data.
2. You have a SQL table and you just want to make a copy of it and rename it? You have to repeat this for 8 tables?

If it's 2, this took me 1 minute on a dataset that's 10+gb.

If it's #1, we'll have to think about that. I don't believe in-db macros can be batch macros. I would have liked to leverage a batch macro to recreate the Output "append file name" functionality. As always, things can be achieved. Just takes some thinking.

[dlash]

@BS_THE_ANALYST  It is 2 but hoping to have a fully automated process that appends the 'YYQ#' to the table name based on the current date. Each quarter the main table 'FINAL_TABLE' is dropped and recreated, I want to also make a 'FINAL_TABLE_YYQ#' table copy for each quarter to have historical records. I have the in-db formula to create the 'YYQ#' but don't know how to append it in the in-db workflow.

Thanks

[BS_THE_ANALYST]

@dlash sure thing. This was interesting and challenging. I love IN-DB but I'm by no means an expert with this part of the tool palette. Considering we can't use a batch macro to dynamically overwrite things, nor do we want to bring these massive tables onto our local machine, the following was my approach

1.  Connect INDB to the table. This will not bring it onto the local machine:
   
2.  Leverage the Dyanmic Output IN-DB and just pull the Query. This gets us access to the query for selecting the table you desire. From here, we've only brought the table query onto the canvas (not all the data). Now we can use this query, and create a new query which adds a little more to the query to dynamically create new tables using native designer.
   
3.  Now we bring the formula tool onto the canvas to adjust the SELECT statement to create a new table where we can dynamically rename it. 
   
   The logic here is that we are creating a Query that we will pump into the Output data tool. We will dynamically overwrite the Output Tool's PreSQL statement configuration with this statement. Therefore creating our desired table. However, it's a slight pain as we need to create a dummy table using the Output data tool. Ideally the upload options would show Temp Table but they don't. Therefore I will create a table then just drop it with the POSTSQL statement. We can tweak this part but I'm strapped for time at the moment. 
   
   Note, I put an Auto Field tool after the formula tool. SQL doesn't like massive string fields being uploaded to it. This is just a lazy method for me to push the query through.
   
4.  Dynamic Overwriting in the batch macro (we'll leverage the query field from the main workflow to overwrite the PreSQL configuration).
   
5. Last thing, remember to drop the table we create using the Output tool. Just hardcode this in the Post SQL statement:
    
6. Last steps, running workflow. I'll show you what happens for me (I'll show Snowflake Database before and after):
   Before
   
   

   Running Workflow:

   

   After:
    

   

    

   All I would change, is in the SQL statement (via the Formula tool). I would want this to say Community_Example_(DATE HERE). Just tweak the formula to do so here:
   

   
   Hopefully that provides you with a solution to the problem. Tricky but cool. 

   
   All the best,
   BS

[BS_THE_ANALYST]

@dlash for completeness, I'd adjust the formula like this and thus the database creates that 'history':

All the best,
BS

[BS_THE_ANALYST]

@dlash scrap the previous solution, I like it, but I think this is potentially quicker with fewer tools. Essentially, we just want Alteryx to push a query up to the SQL DB for us. Let's read in a dummy table (doesn't matter which one) and use LIMIT 1 or TOP 1. Depending on the SQL syntax. Then we will just leverage a POSTSQL statement. Then we wrap this in macro so we can dynamically overwrite it.

Here I'm making a copy of a >10gb database using the PostSQL statement:

  How to make it dynamic now, let's say I wanted to create multiple tables of this with different dates:

Example on the main workflow: I've got 3 rows as I want 3 tables created with 3 different dates appended.

I think this serves as a nice alternative. The IN-DB solution is cool but the output tool is creating a table and then dropping it in the former solution. I think this is better:

 

All the best,
BS  

[BS_THE_ANALYST · Accepted solution]

@dlash I posted an alternate solution but it's been removed somehow. 

Quicker method. Use Input tool, read in a table use TOP 1 or LIMIT 1 to read practically nothing into designer. Leverage PostSQL statement to create a table. Then use batch macro to dynamically create tables.

1) just running one iteration. Took 2 mins to upload 10gb> table.

 

2) dynamically overwrite piece of the POST SQL statement for tbl name:

3) main workflow:

  6 mins to dynamically create 3 tables. 

All the best,
BS

[dlash]

wow thanks so much for this @BS_THE_ANALYST , was able to scan through and excited to work through this. Hopefully I can get to it today.