How do you set up the API Profile that returned a full JSON response which can also be accessed by API Query component? Can API Query component access API Extract profile?

Matillion ETL
1

My job is to create a shared ETL job in Matillion which call any API and stage data in Snowflake. Specifically, I want to get the full API's JSON response and dynamically pass parameters values on run time. The parameters values for each API are taken from a metadata table. I have tried both API Query and API Extract but unable to achieve the above scenario.

 

I need help with either:

  1. Creating a API Query Profile that can return a whole JSON response in its raw format. OR
  2. Creating a generic parameter name in the API Extract Profile that will work for any other APIs if I use it as a job variable.

 

Any suggestion/ advice is very appreciated!

 

 

2

Hi @CP872896​,

You can use the API Extract Profiles. Those are meant to export the full json payload.

3

Hi Bryan,

 

The API Extract Profiles won't work for me. I have to create a job template that will take any API and unknown numbers of parameters. The parameters/ body parameters are stored a string/ SQL snippet in a Snowflake metadata table. I need to be able to manipulate the .rsd file to add a variable for parameters like in API Query Profiles. I have found a way to still use the API Query Component but return the full JSON path. It took me a few days to eventually get to the below solution:

 

In the .rsd file, I create only 1 column

  • <attr name="JSON"        xs:type="variant"  readonly="false" other:xPath="json/"    other:valueFormat="aggregate"      />

 

Also in the Orchestration job, before using the API Query component to copy data to Snowflake, I add a Create Table which has only 1 column name: JSON, type variant. This table is the target table used by API Query. By doing this I allow API Query to copy the whole json path into the JSON column.

4

Thank you very much for following up Bryan. I got a lot of help from your responses to other community members' posts which motivates me to come back and post my answer here just in case someone is in the same situation like me :P.

5

Excellent find @CP872896​! I had no idea that you could use the xs:type of variant. That isn't anywhere in the documentation. This is extremely helpful for me and my team! We really like API Profiles over API Extract Profiles. They have so much more flexibility over API Extract Profiles. It appears that we can use them for simple json extracts now as well. Sort of a one stop shop for all API needs. Thanks again for sharing and have a great weekend!

6

This is exactly what I am looking to do. Can you expand on this a bit? What do you have the repeating element set to? I assume just the "/".

 

Also, How does the xpath of json/ relate the the json of your response? Is the JSON part a keyword in the RSD or is the top level of your actual JSON?

 

Thanks for the help

7

Nevermind.

 

repeating element should be set to "/"

 

and other:xPath="json/"   the json will be equal to what ever the top level component is. For me it was "conversations/"

 

Thanks.