Hey guys! Getting started on my Matillion journey and I can't figure out if the body of an API query profile can be dynamic/parameterized. I have tested out a couple of methods and searched all of Matillion's documentation but I can't find anything related to this.
The API's I am working with pass most of the parameters through the body and it seems as if the query profile can only parameterize headers and text that gets append to the end of the URL.
Thanks Bryan! So how could I edit that Testing this text without it being hardcoded into the RSD? My text would look something like this:
{
"StartDate": "2021-01-01",
"EndDate": "2021-03-31"
}
So everything in there could be hardcoded except I would want the bolded dates to be assigned at runtime or via a variable. Hopefully that makes sense.
I'm also new to Matillion and how they handle API calls.
I'm trying to make POST calls to use an inhouse API to create a user and I'm struggling to make the POST call with variables o columns from a table from my DWH.
I haven't implemented this exact scenario before but this should work fine from what I can tell. You would edit your Query Profile to include and input which will allow you to pass in the content you want to send in the body and then use the input in the "data" section for your body. So it would look like this:
When you go to call it with the API Query you would use the Advanced method and specify the query. In the where statement you pass in the the body information:
Check out this doc. It does a pretty good job of explaining what is required in the API Profile to successfully execute POST calls what parameters. https://documentation.matillion.com/docs/2836045
Do a find in the documentation for POST. Hopefully this helps.
Then just add your custom SQL query to assign your variable. So using my/Bryan's example above you would do:
SELECT * FROM Test WHERE bodydata = '${myvariable}'
Test is the name of your API Query Profile, bodydata is the input attribute your API profile is expecting, and myvariable is your variable with the JSON.
I've had a go at that, and it's got me - on the face of it - a lot further.
I don't think it's quite there yet though.
The endpoint I'm using has both a POST and a GET option. I use the GET option to return a list of the items availble, and this is fine. No parameters required.
However, when I use the POST option to create a new item, I have to send up a specific JSON string as part of the payload.
When I press the sample button on the SQL Query within the API query, it returns the values for the first item that is already on the endpoint, rather than pushing in the job variables that I've set up. This makes me wonder whether this is not the right approach for a POST.
I'm going to try to get a call with Matillion to see if they can help further. It's all just very strange...
Hey Ben - I am using a POST in my example so I think we can get this approach to work for you. Matillion support will certainly be better at me than solving this for you but it almost sounds like you're trying to use the same API Query profile for both your POST and GET options. I would set up two API Query profiles, one for the POST and one for the GET. If you are pressing Sample and it's returning data vs pushing data then I think you're just hitting the GET side of the endpoint - The request method would have been assigned when you first set up your API Query Profile. I would open up your Query profile(s) and verify that you have one that says POST and one says GET and then use the POST one in your SQL Query.
I have 2 separate API Profiles, one with GET and one with POST. Took me a while to get to that point, but it's all good. There's actually 2 references to the method in the RSD though - one says GET and one says POST in the POST version of the API Profile.
The client I'm working for has raised a request with Matillion for a catch up with an engineer to work out what's going wrong, so hopefully we'll get somewhere with this. It's really frustrating!
