I am using the API extract component to get data from an API. This specific API will only return 7 days of data at a time with the filters. I need to iterate over multiple sequences of months. So I need to do a lot of calls to the API.
If I think about it from a scripting perspective I would just iterate through the dates I need and write to files.
What I tried (see photo):
I am utilizing a grid variable and the grid iterator. I reference the values in the Parameters for the API call but the Target table only holds the values from the last API call. Because the component is destructive so it deletes the table and recreates it. Is there any way to get around that?
Is the only way to do this to make 'n' number of tables for each API call?
If not my best solution is to just do the historical load outside of Matillion and then do the everyday load with Matillion as it would only be pulling one day of data.
Hi Grant
From what I understand, you want to make multiple calls via an API and load the data from each call into the same table, without the table being destroyed after each API call ?
The way to do this is add a "Create Table" component, with the "Replace" property set, prior to your iterator/API component to create/replace the table on each ETL run. Then within your API component on the "Load Options" property, set the "Recreate Target Table" setting to "Off".
Therefore, upon each run of the job the table will be recreated once and within each API iteration, the data obtained will be added to the target table.
Thank you for the response!
"From what I understand, you want to make multiple calls via an API and load the data from each call into the same table, without the table being destroyed after each API call ?" Yes this is correct.
I am using the API Extract Component - I will see if I can do the API Query. I was having issues with the fields because of the structure of the JSON.
Hi Grant
I'm sorry, didn't realise it was an API Extract. If you want to continue using an Extract profile then it makes it a bit more tricky as there is no option to set 'recreate table=off' on the Extract API component. The only option I can think of would be to pass a variable into the target table name and have multiple tables created with your JSON variant extracts. Then in a transformation job you could use a multi-table input on these tables to bring the data into one singular table and from there uses one of the flatten components.
Yes - I thought about that but then I would have 52 tables for one year - I am thinking of just doing the one-time historical load outside of Matillion.
Thanks for the help!
Yes that's an alternative. I've done a ETL in the past though, that created many "on-the-fly" semi-structured temp data-holding tables like this and then brought them into one table with a multi-table component.
Then I've dropped the temp tables by;
- getting the temp table names into a grid variable by using a Query Result to Grid component (in Snowflake with a select table_name from information_schema.tables where <your filter> query).
- and then using the grid variable, in a Delete Table component to drop them.
