Snowflake External Functions
Snowflake External Functions
In this article, we will demonstrate how to invoke an API via Amazon Web Services API Gateway that will trigger an AWS Lambda function.
Join the DZone community and get the full member experience.Join For Free
Snowflake has recently announced external functions available in public preview. This allows developers to invoke external APIs from within their Snowflake SQL queries and blend the response into their query result, in the same way as if they were internal Snowflake functions.
In this article, we will demonstrate how to invoke an API via Amazon Web Services API Gateway that will trigger an AWS Lambda function. The Lambda function (written in Python) then invokes a public API from to return the exchange rate for USD and multiple foreign currencies that can be used to calculate our sales values in USD and a number of selected currencies in SQL query running in our Snowflake warehouse. This solution eliminates the need for loading exchange rates into Snowflake regularly and also guarantees accurate, reliable real-time currency values.
The solution architecture is as follows:
As mentioned about, Snowflake initiates the API request to AWS API Gateway using an External Function that is referred in an SQL statement. AWS API Gateway triggers the Lambda function that will call the ExchangeRate-APO REST API and process the response returned in JSON. I will then pack the requested exchange rate into a Snowflake-defined JSON format so the External function can interpret the values and blend it into the query result in Snowflake. Similar architecture could be used to handle stock values, translated texts and many other publicly available APIs.
First we will need define the Lambda function, in our case it is written in Python (note: in the code below you need to replace XXXXXXX with your own API key for )
We can test the Lambda function using the Test feature e.g. with the following test event:
If the Lambda function returned the JSON response as expected then we can expose it via API Gateway:
We need to define a REST POST method that can be invoked using a specific URL:
AWS API Gateway also provides a Client Test feature where we can specify the incoming request body and execute the end-to-end flow including the Lambda function.
Once the API Gateway works as expected, we can move over to Snowflake.
The first step is to create the API using the specific Snowflake SQL statement (note: the Account Id and the API-GW values need to be replaced with your own parameters from the AWS configuration described above) This will also require a particuar AWS IAM role (in our case called APIGW-Lambda) that allows the API Gateway call from Snowflake. This parameter is referred in the API_AWS_ROLE_ARN attribute below:
Then the next step is to define the External Function:
The AWS IAM Role needs to have an established trust relationship so we need to define a Trust Policy for this role:
The required values for the AWS trust policy can be retrieved using :
And now we have everything ready to invoke the external function from Snowflake Worksheet:
And the result will look like this:
This tutorial demonstrates a simple use case of how we can embed external functions into Snowflake SQL and invoke 3rd-party APIs from within SQL. The Lambda function could be more elaborate to handle multiple rows but in general, it should provide you a fairly good understanding of how these functions and the API calls can be used. This can take our data warehouse toolset of built-in and user-defined-functions to the next level; I am really looking forward to seeing various real-life scenarios where external functions can provide great value. More details about Snowflake External Functions can be found here.
Opinions expressed by DZone contributors are their own.