API Query using Variable

Matillion ETL
1

I have a mapping as below:

API Query0 -> Table query to Grid - > Api Query1

 

In API Query0 - I get a daily changing authentication bearer token from API and load it into a table.

In Query Result To Grid - I load the authentication bearer token from table to a grid variable called $token.

In API Query1 - I am trying to use the $token from grid variable by inputting in the RSD file header parameter as Bearer [_input.token] so that Bearer token gets populated automatically at runtime but i am getting below error:

 

"Exception testing table. Formatter [ token ] failed in the evaluation of Bearer [_input.token]. The error was: The value of the attribute could not be accessed: The attribute does not exist."

 

What am i doing wrong here?

0694G00000UsM9KQAV_0D54G00009gT2v9SAC
0694G00000UsM9KQAV_0D54G00009gT2v9SAC966×248 12.5 KB

0694G00000UsM8qQAF_0D54G00009gT2v9SAC
0694G00000UsM8qQAF_0D54G00009gT2v9SAC637×113 11.4 KB

0694G00000UsM9oQAF_0D54G00009gT2v9SAC
0694G00000UsM9oQAF_0D54G00009gT2v9SAC958×161 8.8 KB

2

@BenSmilesMatillionProductOwner​ - Hello Ben, I had asked you a question a few months back and you had helped. Can you please help me with this question about Grid or Job variable in API Query. Thanks.

3

@PatrickS​ - you can use the CustomHeaders attribute in the Connection Options window to set this - rather than using the Authorization header in the RSD.

Find more details here.

https://docs.matillion.com/metl/docs/obtaining-api-token-and-passing-to-api-query-profile/#1.7

Also you probably want to use Query Result to Scalar component to extract the token from the db and assign it to a job variable that you can then send it as a CustomHeader.

4

@PatrickS​ - if you are getting 401 Unauthorized, then it means one of the below few cases.

  • The bearer token is invalid. Can you check if the token works when invoking the API from outside Matillion - say in Postman
  • The CustomHeaders value is not being sent to the API endpoint. Enable Auto Debug in the API Query component and check the logs when you run the job. https://docs.matillion.com/metl/docs/2338785/
  • Can you ensure that you have Authentication Method in the API Query component to "Other" and not "Bearer token".

Hope this helps.

5

@ramalingam​ - Hello I have followed the link you sent me as below:

1) Set the job variable as $token

0694G00000UtYc6QAF_0D74G000008B5PeSAK
0694G00000UtYc6QAF_0D74G000008B5PeSAK1077×135 7.31 KB

  1. Setup the Query Result To Scalar and able to get the value of access_token from database. Able to generate sample also.

    Mapping $token = access_token

0694G00000UtYcfQAF_0D74G000008B5PeSAK
0694G00000UtYcfQAF_0D74G000008B5PeSAK538×294 11 KB

  1. Added the CustomHeaders parameter in API Query profile as well as the Connection Options

0694G00000UtYcpQAF_0D74G000008B5PeSAK
0694G00000UtYcpQAF_0D74G000008B5PeSAK575×363 16.7 KB

I get the message that “Query result processing to scalar successful.” which i assumed as value assigned to $token

BUT for API Query i get “HTTP protocol error. 401 Unauthorized”.

Any idea what i am doing wrong or missing here?

Thanks

Patrick.

6

It seems to be working now, the changed the job variable to be token instead of $token, I think it was main mistake. I also turned BackwardsCompatability to false and added a parameter Header with Value as connection as i saw in the forum answers, but I don't know if these do anything in my case.

 

Thank you so much for you first suggestion as that link really helped me. Best Regards.

0694G00000Utg34QAB_0D74G000008B5gQSAS
0694G00000Utg34QAB_0D74G000008B5gQSAS482×154 7.46 KB