API Query: Connecting to OAuth2 provider *not* in the list and how to get bearer tokens

Matillion ETL
1

Hello all,

 

I was wondering if there is a best practice to connecting to an API that uses OAuth2 but does not appear in the list of providers such as Twitter, Google, et cetera. There are plenty of examples and documentation if you are connecting to a common service, but I haven't come up with much when it's a private endpoint. We need authenticate and retrieve a new bearer token each time we connect.

 

The use case is typical OAuth2:

  • Connect to a token endpoint, providing client_id and client_secret and receiving a bearer authorization_token in return
  • Connect to other endpoints for GET/PUT/et al. using the bearer token as the authorization token

 

In this case, the bearer token that we receive has an expiration of two hours, so we cannot just store it in our password manager. Given that the API Query component only has bearer token options of "Store in Password Manager" or "Store in Component," I'm a bit stumped as to how to get and use this token each time the orchestration runs.

 

Does anyone have any recommendations on the best way to proceed with this API use case?

 

Thanks!

Nick Block

Data Solutions Architect

Enterprise Fleet Management

2

Hey @NickBlock​, I believe what you are trying to do is very doable. Two approaches come to mind. Depending on your situation the first approach could be to utilize the Query Profiles that would set you up to make the call to get the bearer token. You would use the API Query component to make the call then store the bearer token in a variable that could be used to call the next Query Profile which was configured to use a variable that has the bearer token from the previous step. Again, this may or may not work for your situation.

The second approach is to do it all in Python. You can make the API call using the "requests" module to get the bearer token and then make the second request while passing in the bearer token you pulled from the first call. You could do this all in 1 Python script or split it into multiple steps. Meaning, you would make the call to get the bearer token from a Python script that would store the token in a Job Variable. Then any call from that point forward whether it be an API Query or Python script would just use the Job Variable to supply the bearer token. I hope this helps in some way. Please let us know if not and we can try to help further. Thanks for posting!