I have a API where I am making a post call by sending a query to filter data according to my specifications and also passing the API Key. I want to then move this data into a Snowflake table. May I know what’s the best way to achieve this via Matillion?
Hi @AR253900, if you are not leveraging the API profiles or API Extract profiles, I would start there. As painful as it may sound to learn a new pattern for dealing with API's it will make your life a whole lot easier in the future. Your current situation is a great example. If you could leverage the API Profile then you could call the API and load the results directly into Snowflake with a single component. API Profiles support virtually any authentication pattern out there.
If you absolutely must make the API calls using Python, you will need to take some additional steps. Depending on the size of the results you could store the results in a job variable then follow that component up with another Python script where the interpreter is Jython and then iterate over the results and insert records as needed. This is definitely not a preferred method of loading. The other option would be to take the results and save them to file in an Azure Blob or AWS S3 Bucket location. You would then use the S3 Load or Blob Load component to load the results into Snowflake.
Again, if you can make the API Profiles work, you will save yourself a lot of time down the road. I hope this helps!
Thanks so much, Bryan. This is super helpful. Is there any guide or video that walks through setting up of the API profile. The help guide was not very helpful to me.
There isn't a specific video around API Profile or API Extract Profiles that I know of. The major difference between the 2 is that the Extract Profiles return a single column which is the response of the API call that was made. That response is up to you on how you flatten or process it.
The API Profiles allow you to flatten and expose the attributes within the response as columns. The response is return to you in a table like format. There are some limitations in that it will only flatten the response 2 levels. I typically use API Profiles but there are certainly reasons to use Extract Profiles.
The docs for this API profile can be found here: https://documentation.matillion.com/docs/2970075
The docs for the API Extract Profile can be found here: https://documentation.matillion.com/docs/1959484
I found it easier for me to use sample API profiles as templates to create my own from. The examples don't include all possible scenarios but they do a decent job of getting you started.
The other easy way to get started is to just walk through the Wizard. Instead of starting with a complicated API, I would start with something simple like the Matillion API itself. It's well documented and doesn't require anything more than basic authentication. Once you have created an endpoint flip the API Profile to Advanced Mode using the slider in the top right. This will allow you to see the RSD that was generated. This will get you familiar with the boilerplate RSD and where the pieces from the from wizard fit into it. From there, you can start to add on to it. The Test button within within the Profile editor is your friend. You can test, make changes, test, make changes very quickly without having to jump out and test the call using the API Query component.
I am sure you will have questions as you go along. This forum is good for that part as there is a wealth of knowledge in the API Profile space among users. I hope this helps!