Snowflake External Function

External Function:

External functions allow you to create SQL functions that securely invoke externally implemented HTTPS endpoints via an AWS API Gateway. This means your functions can be implemented in any language and use any libraries, all the while using your data in Snowflake, giving you more options for processing your data.

Following diagram briefly describe the components and interactions to achieve the required functionality across Snowflake instances.

In this article, I am going to demonstrate, querying weather details available on an external website from Snowflake!

Querying werther data using external function in snowflake

Lambda function to make API call:

First step is to create lambda function to make API call to the third-party website providing weather details. I have used python to get the weather details from the external website.

Before creating lambda, function let us understand how snowflake data format send to lambda and return value from the lambda function.

In this article, I’m going to pass city name as parameter to fetch weather details from weather details.

• Python lambda function receives two parameters,

1. event
2. context

event parameters include many sub-fields, one of which is “body”. The body is dictionary that includes a key named “data”, corresponding value for data holds data received from snowflake in JSON format.

Sample input data format to lambda function:

{

  "body":

    "{ "data": [ [ 0, “<sf_func_parameter>”]] }"
} 

After this Python function extracts the body as a string, the function calls a JSON library function to convert the string to a Python data structure. The function then extracts individual rows from that data structure, processes them, and returns a value for each of them.

Get Sriganesh Palani’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Sample output data format from lambda function:

{
  "statusCode": 200,
  "body": "{"data": [[0, “<weather data from api>”]]}"
} 

Python code to fetch the weather details from external website:

import json
import http.client


def lambda_handler(event, context):
    array_of_rows_to_return = []
    event_body = event["body"]
    payload = json.loads(event_body)
    rows = payload["data"]
    for row in rows:
     row_number = row[0]
     input_value_1 = row[1]


    conn = http.client.HTTPSConnection("api.openweathermap.org")
    payload = ''
    headers = {}
    format_request = "/data/2.5/weather?q={0}&appid={your_api_id}".format(str(input_value_1))
    conn.request("GET", format_request, payload, headers)
    res = conn.getresponse()


    if res.code != 200:


        row_to_return = [row_number, res.status]
        array_of_rows_to_return.append(row_to_return)
        status_code = res.code
        json_compatible_string_to_return = json.dumps({"data": array_of_rows_to_return})


    else:
        data = res.read()
        row_to_return = [row_number, data.decode("utf-8")]
        array_of_rows_to_return.append(row_to_return)
        status_code = res.code
        json_compatible_string_to_return = json.dumps({"data": array_of_rows_to_return})


    return {
        'statusCode': status_code,
        'body': json_compatible_string_to_return 
 } 

In order to create a snowflake external function, we have to create below:

• Creating a remote service (Lambda Function on AWS)
• Creating a proxy service on AWS API Gateway.
• Creating an API Integration in Snowflake.
• Creating an external function in Snowflake.
• Calling the external function

Snowflake has very good documentation on this, you can refer the documentation in the below link: https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws

After creating all the services, executed external function in snowflake by passing the required parameter:

select sg_external_function('Chennai');

Conclusion:

I hope you enjoyed learning about Snowflake external function. If you have any specific use case, suggestion or feedback feel free to command or reach out me @ https://www.linkedin.com/in/sriganeshpalani/