API request using multiple values for a query parameter in one job

  • 11 April 2024
  • 1 reply

Hello There


I am trying to solve a very specific usecase here. I am trying to query a DB using an API and this API is using multiple query parameters. one of the query parameters is going to be ids which are more than 100 in count. The catch is that I can’t pass all 100 ids as an array to the API call because API is not designed to accept array for that parameter. It is going to be kind of looping over those 100 ids one by one and then calling the API with new id as the parameter value in each iteration.

Also, these IDs needs to be fetched from a snowflake table and then passed as the parameter to the API call. So I am thinking of having some snowflake or JDBC query consumer as as an origin. And these IDs would increase over the period of time so want to make it as dynamic as possible but that is not priority for now. 


Having multiple jobs to solve this would lead to 100+ jobs and that would keep increasing which is not a good practice at all.


Could someone please suggest the best possible way for this. Any help here would be highly appreciated.


Advance Thanks

1 reply

Userlevel 5
Badge +1

@Karan Bhatia 

In this scenario, store all IDs from the database table in a metadata table. Subsequently, retrieve the data from the metadata table and pass it into the API URL.

You have the option to utilize the HTTP processor, or if you prefer, you can handle it using a Jython or Groovy evaluator processor.


import requests

# Define the base URL of your API
base_url = "https://your.api.endpoint"

# Loop over each record in the batch
for record in records:
# Extract the ID from the record
id_value = record.value['id']

# Construct the API endpoint with the ID as a parameter
api_endpoint = "{base_url}?id={id_value}"

# Make the API call
response = requests.get(api_endpoint)

# Handle the API response as needed
if response.status_code == 200:
# Successful response, do something
# Handle errors

# Transfer all records to the next stage