I have a bearer token in extract profile which dynamically changes for every 20 min, then again I have to use that bearer token in header to call another api to get data. can we do that? if so how?

Matillion ETL
1

I have a bearer token in extract profile which dynamically changes for every 20 min, then again I have to use that bearer token in header to call another api to get data.
can we do that? if so how?

2

Hello mukunthan394

 

If I understand you correctly, you are asking whether API Extract would be able to handle an ever-changing Bearer Token?

 

How is this Bearer Token created; i.e. what parameters need to be provided?

Could you share the documentation for this API?

This does sound like an OAuth flow, which we can handle with API Extract.

 

Regards

Ben Smiles

Matillion Product Team

3

Hello mukunthan394

 

I believe the parameter pattern you are looking at is actually a OAuth flow. My best guess without seeing the API reference is Client Credentials. We have an OAuth builder which would manage the refresh you require.

 

If you would like me to explain further, I would be happy to on a more private channel.

 

Regards

Ben Smiles

Matillion Product Team

4

Hi Ben

 

I checked OAuthflow ,it's not what im looking for,

I'm trying to combine python component and api query profile.

 

Is it possible to send job variable value to RSD file dynamically? if so Kindly explain the solution for that.

 

Regards

Mukunthan S

5

@BenSmilesMatillionProductOwner​ , I do have similar issue. Could you please help with my use case. I have a username and password using that I hit the token API and get Bearer token and Refresh token as response. The bearer token is set to expire in 5 mins. For now I'm using a python script ahead of API Query component for generating the Bearer token and passing this as variable to API Query Component which is working fine. However, I'm using pagination to pull the large data set but this was failing after the bearer token time out limit. Do you have any idea how to refresh this bearer token when the API response is still polling more than the set timeout. Say for Eg. my API filter options are rendering 2 million records and its staging the data as per pagination like I've set limit of 5000 per page and its staging the records for 5 mins and throwing "HTTP protocol error. 403 Forbidden" error after then.

@mukunthan394​ , FYI - I'm generating the Bearer token Dynamically using Python script and updating the same to a job variable. Again this job variable will be used in the API Query component in the "Connection Options". Use "CustomHeaders" as parameter and value as "Authorization: Bearer ${job variable name}".

While configuring the API Query Profile you will have to use the below option to pass the Bearer token dynamically on the Params tab,

Parameter Name - Authorization

Value - Bearer <actual bearer token generated externally>

Parameter Type - Header

Variable Type - Connection

6

Hi @maran2100​

Thanks for sharing, I too did in similar way, but used Loop iterator for pagination.

Instead of using Pagination in API Query profile, you could use job variable(offset variable) to iterate API Query Component using Loop iterator. and when you get "HTTP protocol error. 403 Forbidden" error, you can use python component to retrieve new bearer token and retrieve records from where it stopped using that job variable(offset variable).

Hope this helps.

7

Hello All

 

I understand you are all seeing errors because your OAuth is expiring too quickly and not refreshing the tokens. This will be due to missing parameters or information that is not being passed correctly when trying to refresh the access tokens.

 

Unfortunately, the information currently being shared in this thread is only helpful with regards to the initial bearer token creation. So, if you could each please shared (via DM) a link to the API reference for the APIs you are having issues with.

 

In order to understand why the refresh token isn't being utilised, we need to see how the refresh token portion of the Auth Code call is being made - i.e. what parameters need to be provided. The API documentation will also enable us to help with the pagination too.

 

Thereafter, I can get my development team to have a look at where the "parameter" gap lies.

 

PS. In the mean time, if you have a Hub account, I would suggest try our Custom Connector tool to tackle these issues, as it has a more flexible OAuth and pagination builder with better error messaging.

 

Regards

Ben Smiles

Matillion Product Team

8

@BenSmilesMatillionProductOwner​ , Thanks for the response. In my use case I'm using SAP CPQ Rest API and it supports Basic and Token API for authentication. As per SAP documentation, Bearer token retrieved using basic method by passing Username and Password will be active for 5 mins and if this session needs to be extended we can make use of refresh_token rendered along with Bearer token to extend the session by 20 more mins. But I feel if the pagination poll is running more than 20 mins then we need to rely on handing the pagination outside API call to iterate through and generate the tokens for each iteration else do custom program to catch the errors and regenerate the tokens as in when it gets expire.

Following is the SAP CPQ rest API authentication doc link - https://help.sap.com/docs/SAP_CPQ/08a7929ad06d4680b4f18cb57bc1a1d3/92f68dffe7e746918cf289406c4a05db.html

9

Hello Maran2100

 

SAP CPQ's REST API also supports several OAuth flows such as Authorization Code and Client Credentials, which are supported by Matillion and should handle the token refresh automatically, thus avoiding the need to iterate outside the call.

 

Funnily enough, there is no mention of paging in the document...

 

Regards

Ben Smiles

10

Hello All

 

My last suggestion would be to either try our Custom Connector tool (https://docs.matillion.com/data-productivity-cloud/custom-connector/docs/custom-connector-overview/) or speak to one of our support team (https://support.matillion.com/s/) to help you move forward.

 

Regards

Ben Smiles

Matillion Product Team

11

Hi Ben

 

In extract profile, I provided the URI, in parameter I passed content-type and in body I passed the username, password as string to get a json data in response consisting tokentype,bearer token, expiring time.

 

I want to use that bearer token to call another api.

 

Kindly do the needful.

12

Thanks @mukunthan394​ . But in this case we need to perform few more action items to exit the loop iterator right? Like custom validation for completion of all the pages. How do we manage this?

13

Hi @maran2100​ In my case I used total_count info to exit the loop iterator, which is available as a part of JSON when I am extracting bearer token for Particular table using python component.