Snowflake External Functions using Azure Functions on Python not working

Question

I want to create an external function that can be used to upsert rows into MongoDB. I've created the function, tested it locally using Postman and after publishing. I've followed the documentation from https://docs.snowflake.com/en/sql-reference/external-functions-creating-azure-ui.html and at first, I used the javascript function they proposed to test and worked. However, when I run it it python I get an error. This is the code.

import logging

import azure.functions as func
import pymongo
import json
import os
from datetime import datetime

cluster = pymongo.MongoClient(os.environ['MongoDBConnString'])
db = cluster[f"{os.environ['MongoDB']}"]
collection = db[f"{os.environ['MongoDBCollection']}"]

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    name = req.params.get('name')
    if not name:
        try:
            req_body = req.get_json()
        except ValueError:
            pass
        else:
            name = req_body.get('name')

    if name:
        return func.HttpResponse(f"Hello, {name}. This HTTP triggered function executed successfully.")
    else:
        collection.update_one(
                filter={
                    '_id':req_body['_id']
                },
                update={
                    '$set': {'segment_ids': req_body['segment_ids']}
                },
                upsert=True)
        return func.HttpResponse(
                json.dumps({"status_code": 200,
                "status_message": "Upsert Success",
                "Timestamp": datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%S"),
                "_id": req_body['_id']}),
                status_code=200,
                mimetype="text/plain"
        )

The error states that req_body is referenced before being defined, failing at line '_id':req_body['_id']. In Snowflake I've created an external function called mongoUpsert(body variant) and I am parsing a simple query to test.

select mongoUpsert(object_construct('_id', 'someuuid', 'segment_ids;, array_construct(1,2,3,4)))

From what I can tell, the function is not receiving the body I'm parsing in Snowflake for some reason. I don't know what I am doing wrong. Can anyone help me? Can anyone also explain how Snowflake is sending the parameters (as body, params, headers) and is there a way to specify if I want to parse a body or params?

Nitya · Accepted Answer · 2021-05-27 02:20:22Z

2

External functions send and receive data in a particular format. All the parameters are sent in the request body.

https://docs.snowflake.com/en/sql-reference/external-functions-data-format.html

You can checkout snowflake-labs for external functions samples.

There is one specifically for Azure Python functions that calls the Translator API.

answered May 27, 2021 at 2:20

Nitya

742 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrei Budaes · Accepted Answer · 2021-05-26 11:10:59Z

I've started from scratch and stripped the layers one by one in Snowflake. So the Snowflake parameter is parsed to the body of the function but wrapped in an array which is then wrapped in another object called 'data'. Furthermore, it expects the same schema as a response back. So here's below the template to use for Azure Functions when using Python.

import logging
import azure.functions as func
import json

def main(req: func.HttpRequest) -> func.HttpResponse:
    # Get body response from Snowflake
    req_body = req.get_json()['data'][0][1]

    ###### Do Something
    
    # Return Response
    message = {"Task": "Completed"}
    return func.HttpResponse(
        json.dumps({'data': [[0, message]]}),
        status_code=200)

As an example, I've used a simple JSON object:

{
  "_id": "someuuid"
}

And created an external function in Snowflake called testfunc(body variant) and called it using select testfunc(object_construct('_id', 'someuuid')). If you would log the response (using logging.info(req.get_json())) it would print the following

{
  "data": 
  [
    [
      0,
      {
        "_id": "someuuid"
      }
    ]
  ]
}

So to get the clean input I fed in snowflake I have the line

req_body = req.get_json()['data'][0][1]

However, I kept getting errors on the response until I tried just echoing the input and noticed it returned it without the wrapping. The returned body needs to be a string (hence why using json.dumps()) but it also needs the wrapping. So to print it out, first define a message you want (it may be a calculation of the input or an acknowledgement), then wrap the message in {'data': [[0, message]]} and finally compile it as a string (json.dumps())

Collectives™ on Stack Overflow

Snowflake External Functions using Azure Functions on Python not working

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related