Saturday 28 October 2023

How to Call AWS API from Snowflake

My Snowflake free account expires tomorrow. So I thought I should get the external integration happening today.

Following the length instructions from Snowflake turns out not to be enough. I got stuck at the input and output parameter formats between SF and the Lambda function. I finally got it working and here they are:

The Lambda function in Python:

import json

print('Loading function')
dict={2000:"Sydney", 2218:"Allawah", 3000 : "Melbourne"}

def lambda_handler(event, context):
    print(f"event={event}")
    # normal channel the event looks like: {'body': '{ "data": [ [ 0, 43, "page" ], [ 1, 42, "life, the universe, and everything" ] ] }'}
    # from SF, event looks like: {'data': [[0, 2000]]}
    if "body" in event:
        postcode=event["body"]["data"][0][1]
    else:
        postcode=event["data"][0][1]
    city="unknown"
    print(f"postcode={postcode}")
    if postcode in dict:
        city=dict[postcode]
    output=[0, city]
   
    array_of_rows_to_return = [ ]
    array_of_rows_to_return.append(output)

    json_compatible_string_to_return = json.dumps({'data' : array_of_rows_to_return})
   
    return {'data': array_of_rows_to_return}

The Snowflake side:

create or replace external function postcode_to_city(postcode int)
    returns array
    api_integration = demonstration_external_api_integration_01
    as 'https://....execute-api.ap-southeast-2.amazonaws.com/dev';

select postcode_to_city(2000) as city;

The result: