Iterate over a list of IDs and call an REST API

Matillion ETL
1

Hello,

 

I have a list of IDs. I need to iterate over them, and call an REST API. The idea is to check if the ID has been deleted from the source. If it is deleted, the blank response in returned. In which case I need to add that ID to a table and mark it as deleted. If a response has a text, then that is not a deleted record. I need to ignore.

 

What is the best way to implement this in Matillion?

 

Thanks,

Saqib

2

Hi @saqib​,

The way I read you post, I believe you are saying you have a list of ID's and you already have some sort of REST API that you want to call on each iteration of an ID and the responses that you expect to get back could be anything from blank to some sort of text output. From there you want to take all the blank responses and update the corresponding ID record in a table and mark it deleted. Is this correct?

Assuming this is correct, there are a couple ways to handle this. One way would be to have your ID's in a grid variable or table. From there, you would iterate over the ID's and with each iteration on an ID you would call another orchestration that would handle the logic for calling the API, handling the result, and updating the table if needed. For this orchestration you could use the API Query component to make the call and get the result back into a table or do the same using a Python Script.

If you used a Python script to make the API call then you could get the results back and assign a value to a Job Variable which could drive the update statement for the corresponding record. If you choose to use the API Query component then you will need to write a Snowflake MERGE statement or call a transformation job that would facilitate the MERGE between the API results table and the table you want to update. If you go the Python route, it's as simple as writing a dynamic SQL statement and storing it in a job variable and then calling the SQL Script component where you would leverage the job variable with the SQL statement in it.

Attached is an example of how to do this using Python. I hope this helps with the understanding of how this could be done.