Connecting Azure Functions to Snowflake DB Using Python

on

 


Snowflake, which is one of the most popular cloud-based data warehouses nowadays, provides a variety of connecting approaches either from other cloud services or on-premise. This includes:
  • A well-designed Web-based user interface that runs on any popular browsers
  • A wide range of partners’ software such as Attunity and Matillion for data integration, and Power BI, Qlik Sense and Tableau for Business Intelligence
  • Snowflake Connector for Python
  • SnowSQL: A command-line tool that is developed utilising the Snowflake Connector for Python
  • Snowflake Connector for Spark
  • Snowflake Connector for Kafka
  • Node.js Driver
  • Go Snowflake Driver
  • .NET Driver
  • And of course, ODBC and JDBC drivers that ensure most of the other data integration requirements can also be satisfied as long as they accept one of these two pervasively used DB connectors
Today, I’m going to show a possibility to integrate Snowflake Data Warehouse with Azure Functions, which is potentially a very useful solution in some cases:

  1. When the data transformation is complex enough. In this case, if we use ETL tools such as Azure Data Factory, it could end up with too many nodes so that the whole data pipeline is difficult to be maintained.
  2. When we’ve already got legacy Python code for the transformation, and just want to automate it.
  3. When there are some requirements other than only data transformation. For example, we’re using Azure Function App as a back-end server and simply want to grab some data from Snowflake, so it’s not worth to impose an ETL tool to do such a job.
  4. You just want to code! Fair enough :)

Pre-requisites:

  • A Snowflake account is needed of course.
If you don’t have a Snowflake account yet, it is free to register a trial account, which comes with 400 credits. Here is the link:
https://trial.snowflake.com/
  • Python on your machine
You will need either Python 2.7.9 (or higher) or Python 3.5.0 (or higher) is installed.
  • Microsoft Visual Studio Code
An IDE tool is optional, but to work with Azure Functions, it is much easier to use the VS Code.

Install the snowflake-connector-python Library

Option 1: You can simply use pip to install the snowflake-connector-python as follows
$ pip install snowflake-connector-python
Option 2: You can also add snowflake-connector-python to requirements.txt in the root path of the Azure Function, and then install it via the text file.
$ pip install -r requirements.txt
Because Azure Functions rely on requirements.txt file to install any dependencies required, the name of the library snowflake-connector-python has to be added into the text file anyway. It is suggested to use option 2 for any dependencies installation when you develop Azure Functions. This will make sure you never forget to do so when you deploy the Azure Function.

Create the Azure Function

You can easily create Azure Functions using VS Code. If this is the first time that you’re creating an Azure Function, please check this story as follows.

https://pythondsplayground.blogspot.com/2020/11/access-azure-database-for-mysql-from.html

In this article, I would like to create an HttpTrigger Azure Function because it is easy for testing purposes.

Implement the Azure Function using Snowflake Python Connector

Now, let’s implement it.
To begin with, we need a function to get the connection of the Snowflake Data Warehouse.

def get_connection(): return snowflake.connector.connect( user='<your_username>', password='<your_password>', account='xxxxx.australia-east.azure', warehouse='<warehouse_name>' )

The account name can be easily found from the URL that you use to access your Snowflake Web UI. Just simply strip out the domain name and extension .snowflakecomputing.com the part on the left of this is your account name. In my case, the account name is a unique ID with australia-est.azure because I choose to use Azure as the cloud platform for Snowflake, and the region I chose is Australia East. You’ll need to replace this whole string based on your actual account name.

Note:
  • This is just a test. In practice, your password should not be visible in the code as plain text. Instead, you can use Azure Function “Application settings” to store these values.
  • To be more secure, the account you used in this Azure Function should be only granted with the permission that it actually needs. For example, grant read permission for only the tables it is supposed to utilise
  • warehouse is not a compulsory parameter. However, I would like to add it in this connect constructor. Otherwise, I’ll need to set the warehouse later on anyway.
Here is the full code for the Azure Function that includes the main function. It makes use the get_connection() function to connect to Snowflake, and then take two parameters schema and table from the HTTP Get Request. Note that the data SNOWFLAKE_SAMPLE_DATA used in this example is a default sample database from Snowflake officially powered by “Data Sharing” that is a standard feature of Snowflake. You will have this sample database available after you created the trial account.

import logging
import azure.functions as func
import snowflake.connector


def get_connection():
    return snowflake.connector.connect(
        user='<your_user>',
        password='<your_password>',
        account='<your_account>',
        warehouse='<your_virtual_warehouse>'
    )


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

    ctx = get_connection()
    result_string = ""

    schema = req.params.get('schema')
    table = req.params.get('table')

    if schema and table and ctx :
        cursor = ctx.cursor()

        try:
            results = cursor.execute("SELECT * FROM SNOWFLAKE_SAMPLE_DATA.{}.{}".format(schema, table))
            for row in results:
                result_string = result_string + str(row[0]) + " " + str(row[1]) + "\n"
            result_string += "\n\nQuery ID: " + cursor.sfqid
        except Exception as e:
            print(e)
        finally:
            cursor.close()
        ctx.close()

        return func.HttpResponse(result_string)
    else:
        return func.HttpResponse(
             "Request failed.",
             status_code=400
        )

Test the Azure Function

Now, let’s the Azure Function. Let’s use the schema TPCH_SF1 and its table NATION for this testing because it is a relatively small table. As shown below, I’m testing the Azure Function locally using func start run. The parameters are passed in the URL.
http://localhost:7071/api/snowflake-connector?schema=TPCH_SF1&table=NATION
Access the URL in your browser, you should be able to view the results



OK, done.

This is just a simple test for the snowflake-connector-python. If you wish to read more about Snowflake Data Warehouse and its amazing features. Please follow me, and I’ll keep updating this topic weekly.

Resources:

1. How to create an Azure Function

2. Snowflake trail account registration

3. All Python Code for this Test

This article was originally published on Medium.com by myself:

Comments

Post a Comment