3
0
During this first part of post we will discuss the External Functions in snowflake. An external function is a type of UDF. Unlike other UDFs, an external function does not contain its own code; instead, it calls code that is stored and executed outside Snowflake. External functions make it easier to access external API services such as geocoders, coindesk, crypto data and other custom code running outside of Snowflake.
For our use case, We will be hitting the Public API through REST service in AWS and once payload returned by API in JSON format , will pass the response to the snowflake and snowflake can stores the JSON response for further analysis.
However, As per the above diagram, below is the complete flow:
- Firstly, Snowflake calls AWS API Gateway using an External Function.
- Secondly, AWS API Gateway triggers the Lambda function that will hit the Public REST API for two URL :
- Lambda process the response returned in JSON.
- Transform the returned response Snowflake-defined JSON format
- Finally, External function interpret the response and pass to the query result in Snowflake
To achieve the above Flow, below Technical steps needs to be followed.
The major steps in creating an external function on AWS are:
- Create the Remote Service (Lambda Function on AWS)
- Configure the Proxy Service (Amazon API Gateway)
- Create the API Integration (in Snowflake)
- Create the External Function.
- Call the Function.
Steps:
Create Remote Service (Lambda function):
a) Create Lambda Function
b) Create the IAM Role and assign the required polies to the Role and attach it with Lambda function.
Configure Proxy Service (Amazon API Gateway) several steps including:
- Creating a new IAM role in your AWS account.
- Creating an Amazon API Gateway endpoint and configuring it.
- Securing your Amazon API Gateway endpoint.
a) Creating an IAM role for Snowflake use:
b) Creating an AWS API Gateway
c) Securing your Amazon API Gateway endpoint
In next part of the series, we will create API Integration and External Function inside the snowflake.
