SQL Query - Formatter [ hapikey ] failed in the evaluation of [_connection.hapikey]. The error was:
The value of the attribute could not be accessed: The attribute does not exist.
I have checked the endpoint URL on Postman and it fetches all details fine. Also, I can create the API Query profile, however, I get the error while running the job (API Query Component).
It would be really helpful if someone can point me to right direction as I am new with APIs.
One other thing I just thought of is that anything with _connection.XXXX always means that Matillion's API Profile is expecting the parameter to be set via the connection. Meaning, you would need to set whatever hapikey is to a value at the time of the connection which can be done in the API Query component under Connection Options. I am not familiar with the hapikey attribute so I suspect this is a custom header attribute that the API you are using requires. That's a complete guess without see the API Profile Perhaps you can post your raw API Profile here minus any credential information if it's included. I can usually look at the API Profile and know exactly what is going on.
The issue you are seeing isn't necessarily a Matillion or date issue. This is a common escaping situation. In JSON you have to escape forward slashes. So, where you have the "/" in your dateRange, make that "\/" the extra "\" in front of the "/" escapes it and allows it to be passed on as you are intending. Let us know if this doesn't fix it.
I was trying to convert my Python script for API extraction into Matillion query profile. As source sends different indent i was trying to standerize it before loading into Snowflake. I am working on Matillion Query Profile 1st time :).
Json from source API is not in "Record" format. I agree that by using Manage Extract profile we can load complete JSON in snowflake but this loaded everything in one column and one row and my json is huge which cause problem with variant column(like asked in : https://metlcommunity.matillion.com/s/question/0D54G00007rgb90SAA/api-extract-profile-to-snowflake-issue-with-big-dataset).
It sounds like you are trying to use the pandas Python module. I am not sure I have had to change the indenting or formatting of json but I do know that when you write json, you have a bit of control over how it's presented.
Just to be clear, are you trying to format the json because an application or process requires it to be formatted "pretty" or are you taking sections out of the json and then trying to output it?
I am curious why you needed the JSON formatted. If you are loading the JSON into a variant column in a Snowflake table and the JSON is valid, the format changes as Snowflake loads it. I say this because we have found instances where the JSON in the loaded table is reorganized and the objects are in a different order. I was told that this is Snowflake optimizing the JSON for it's query engine.
Just curious if you have seen different behavior where formatting matters?
I copied the parameter info from the API Query profile and added them in the API Query Component. You get the Parameter details in the rsd file in config tab.
You need to add the same details in the API Component, in the Connection Options and that should solve the problem.
I have used the variable in the SQL query we write in the API component but never in the connection options. If that is what you are asking then the query is below:
