Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Dynamically Union Tables Together

The Scenario: I'm creating a job that is intended to be iterated through for a number of source ONE job that can be run over and over again and work to pull over each source table into an ODS in redshift. Our data is sharded, so as part of that process, for each table one step needs to union 5 shards together into a final table.

The Question: How do I union tables together dynamically so that the union works on each iteration of source table that i'm trying to pull over. For example, if the first iteration of my job is pulling over the 'clients' table, i need the process to union all 5 shards of the clients table, but then when the iterator moves on to pull over the 'sales' table, i need it to union together the 5 shards for the sales table. Using the multi-table input component, you have to specify which columns to select for the union - which wont work since each iteration (for each table) will have a different set of columns. I think that component would work perfectly if i didn't have to specify which columns need to be part of the union - if there was just an option for "all columns". Anyone run into this before?

1 Community Answers

Matillion Agent  

Ian Funnell —

Hi James,

There isn’t any easy way to do this currently (v1.29) without involving variables containing column lists, and heavy use of the SQL component.

But in a couple of releases time (v1.31) we are planning to introduce array variables to the product. This will allow you much more flexibility in terms of having a highly configurable generic job.

Please monitor the release notes for this new feature, which is planned for Q1 2018.

Best regards,

Post Your Community Answer

To add an answer please login