Adding a job variable to the data returned from an API call

Matillion ETL
1

Hi,

 

I am making a call to an API URI which requires a SystemID parameter as part of the URI. I have a Grid Iterator component which extracts each SystemID in turn and links to an API Query component which makes the call to the API and successfully returns data for each SystemID in the loop.

 

The data returned by the API does not include the SystemID so I want to add this as a column in the returned data. The SytemID is available as a job variable.

 

In BASIC mode on the API Query component, the Data Selection property includes a Variables tab, which includes the job variable that I want to include in the returned data. After selecting it (along with the columns from the API), I get an error message “Unrecognised Options” followed by the default value for the job variable.

 

In ADVANCED mode I am able to write a SQL statement that selects the job variable and the API columns. On clicking Sample, the default job variable value is returned along with the API columns.  This is what I want as my output, however on running the job, it fails with an SQL compilation error – invalid identifier on the job variable column.

 

As a Matillion newbie I would appreciate any advice, please.

 

Many thanks

2

Think I've resolved my own query.

When connecting the API Query component to the Grid Iterator component it seems I could only select columns contained in the API profile. The inclusion of job variables resulted in the error.

By moving the API query component to a separate orchestration job, then linking the orchestration job to the Grid Iterator I was able to select the job variables in the SQL Query, which then executed successfully. Values from the job variables were passed to the snowflake target table, which is what I wanted to achieve.

Job done.

3

Hi. I had same issue with a few API extracts and have tried both solutions. adding the job variable to the select (after switching to advanced) when the API extract is directly attached to the grid iterator seemed to work fine for me as well, so hopefully no need for the extra orchestration level. I'm finding the less you do within an iterator the better as it impacts performance massively if your looping through a large number of records, especially when trying to run concurrently.