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: