Dynamic Table Name

CloverDX Platform
1

Is it possible to design a graph to select data from multiple tables in Snowflake Database where their table names are values from a column of another table?

I have a Master Table in Snowflake DB consists of all the table names I need.

Master Table

Table_Name INDEPENDENT_1 INDEPENDENT_2 INDEPENDENT_3 ... INDEPENDENT_n

How can I select data from tables that having the same names as the Table_Name column in Master Table? All tables are in Snowflake DB. The result should be like:

SELECT * FROM INDEPENDENT_1 
UNION 
SELECT * FROM INDEPENDENT_2 
UNION 
SELECT * FROM INDEPENDENT_3 
UNION 
.... 
UNION 
SELECT * FROM INDEPENDENT_n

Thank you!

2

Hi Yijin,

Good question!

Once you query the table names from your database, you can build the SELECT statement in CTL. In this case, the Denormalizer component (which produces a single output record from multiple input records) is the most suitable component.

Without further ado, let’s jump to the solution:

First, we query the table names from the master table. Then, we can proceed to build the SELECT statement. Since all the input records are of a single group (table name), we do not need to specify the key in Denormalizer’s properties. The only thing we need to implement is the Denormalize code. Within the Denormalize, we find two functions that need implementing: append and transform.

//#CTL2
// This transformation defines the way in which multiple input records
// (with the same key) are denormalized into one output record.
string final_query = ""; // global (in a component scope) variable

// This function is called for each input record from a group of records
// with the same key.
function integer append() {
     if (isEmpty(final_query)){ // if empty, we are processing the first record
            final_query = "SELECT * FROM " + $in.0.table_name;
     }      
     else { 
            final_query += " UNION SELECT * FROM " + $in.0.table_name;
     }
     return OK;        
}

// This function is called once after the append() function was called for all records
// of a group of input records defined by the key.
// It creates a single output record for the whole group.
function integer transform() {
     $out.0.final_query = final_query;
     
     return OK;
}

This will provide us with the final SELECT statement. Now, if you wish to use this statement, set the DatabaseReader component to read the query from the edge:

327×228 6.42 KB

Best,

Ladislav.