Dynamically creating final table based on VARIANT data in Staging table

Matillion ETL
1

I have 1000 staging tables containing VARIANT datatype data which i want to flatten and load to 1000 new tables. After staging tables i have used "Extract Nested Data" component in Snowflake. How can i achieve this dynamically by creating final tables on the fly based on column names received from the output of "extract nested data" component.

 

Please advice.

TIA

2

Hello viveksingh.singh897

 

Have you tried using our data structure variable capability to automatically build the tables for you?

https://docs.matillion.com/metl/docs/9191278/

 

Regards

Ben Smiles

Matillion Product Team

3

Are the tables staged already, or are you running a component that is staging them from a source?

4

Hello Vivek

 

You can use a Data Structure variable to flatten the tables on the fly, by creating a Data Structure variable (in the Manage Variables dialog), it should remain empty, then associating that variable with the API Extract component you are iterating over. Click the Export tab in the component's Properties panel, select Manage Variables, add a Data Structure and select the variable you have created. Then go to the Extract Nested Data component and, in the column selector, select to use a variable and choose the same variable you created before.

 

The API Extract component will then populate that variable at runtime and pass the structure to the Extract Nested Data component to create the flattened table dynamically.

 

Regards

Ben Smiles

Matillion Product Team

5

Yes, Manage Environment Variables. That is correct.

6

I tried and i am not getting correct data. How can we configure 1000 endpoints dynamically in API Extract profile where each endpoint may have some same columns or all columns may differ.

7

Also if you can help me if we can use S3 Load Generator to dynamically load 1000 csv files from S3 bucket into 1000 different tables.

 

Thanks,

Vivek

8

At this stage, Vivek, I would suggest contacting our support team (https://support.matillion.com/s/) to help you further, as I am unable to assist further without working directly with you and your job.

9

Thanks Ben!

After the extract nested data component how can i create the table on the fly and load the flattened columns.

Could you please explain in detail.

 

Thanks,

Vivek

10

I have a table containing 1000 endpoints. So using table iterator i am iterating over these endpoints and using API Extract i am loading the data of each endpoint into separate staging tables having VARIANT data type column.

 

Now my requirement is to dynamically iterate over these staging tables and flatten the data into 1000 output tables. Also these 1000 output tables should be created on the fly dynamically. Each endpoint results in a different set of columns.

11

Thanks for the info Ben!

By Manage Variables dialog you mean Manage Environment Variables?

 

Regards,

Vivek Singh