Long running API Query & Oauth2 token expiration

Matillion ETL
1

I have several jobs which periodically retrieve large datasets from an API endpoint that uses oauth2 authentication. When this job kicks off, there's an orchestration job involving some python that requests an oauth token, which is loaded into an environment variable. This token is valid for 10-15 minutes.

 

The next step in the job uses that token in the API Query component, along with a query profile, to paginate until all the data is returned. Unfortunately, the token will sometimes expire during the pagination, resulting in a 401 and failure of the job.

 

There doesn't appear to be any way to refresh the token from within the pagination loop, nor does there appear to be any way to "elegantly" fail, for instance passing the next pagination URL to an environment variable and writing the partial dataset to a table.

 

Any suggestions on how to better handle this scenario?

2

Have you tried querying parts of data at a time, instead of all at once?

 

so you can refresh token for data that is getting fetched within expiration. Looping to fetch some part at a time.

3

Unfortunately, this particular data set doesn't have an easy break-point, it's a big blob of product inventory data with no timestamps, incremental row numbers or other easy way to split it into chunks.

 

I'm coming from Talend, where it was possible to refresh the token between each page. Matillion is better in pretty much every way, except this one 😓

4

Okay got it..

The only other easy way would be to reach out API support team to check if they can increase the token refresh time.