Friday, July 12, 2024
HomeHow ToBuild Report with Devart Python Connector & Snowflake

Build Report with Devart Python Connector & Snowflake

Content

Objective 

In the article, we demonstrate using the Devart Python Connector for Snowflake how to connect to the Snowflake database within the Anaconda tool. 

Technology Background 

Python Connector for Snowflake 

Python Connector for Snowflake is a reliable connectivity solution for accessing the Snowflake data cloud from Python applications to perform create, read, update, and delete operations on stored data. The solution fully implements the Python DB API 2.0 specification and is distributed as a wheel package for Windows 32-bit and 64-bit. 

High Performance 

The connector supports connection pooling and local data caching to increase access speed. It also lets you submit multiple update statements to the data warehouse for processing as a batch to improve execution time. 

Platform Support 

The Python connector is available for Windows and Windows Server (32-bit and 64-bit). 

Unicode-Compliant Connector 

The Unicode-compliant connector lets you retrieve and update multilingual data, regardless of its character encoding (Chinese, Cyrillic, Hebrew, and more) in any language environment 

Data Types Support 

The connector supports all BigQuery and Python data types and offers additional options to control data type mapping between them. 

Snowflake platform 

Snowflake is a cloud-based platform that empowers data-driven organizations to mobilize data, apps, and AI across various industries and use cases. One of the key points of Snowflake is the Data Cloud Platform: Snowflake offers a single platform that eliminates data silos and simplifies architectures. 

Prerequisites 

Before starting to connect the Snowflake database by using Devart Python Connector for Snowflake and building Analytics, make sure to download the necessary tools and drivers: 

  • Ananconda IDE or any IDE to develop Python program 
  • Click here to download Devart Python Connectors for Snowflake 
  • Snowflake account and Snowflake database sample  

Architecture 

Create a database in Snowflake 

In this step, we create a database in Snowflake and use it to create the connection 

Click on the Database icon and enter the DEMO database 

After the DEMO database is created, we create a schema HR in this database 

Next, we create a table EMPLOYEE_CHURN using From File in Snowflake schema. In this case, we import the list of Employees, which is Active or Non-Active, in order to analyze Employee Churn. The sample file is below 

Click Create -> Table -> From File 

Keep all settings as default and enter table name: EMPLOYEE_CHURN -> Next 

We are navigated to the next popup -> Load 

Now, we see the data in the Snowflake table 

Get the connection in Snowflake 

This step is to get some information to create the connection to Snowflake in the next step. After registering your Snowflake account, you will receive an email confirming your account is active. In the content of the email, you must get two pieces of information: Account Identifier (Domain URL or the hostname of Snowflake instance) and User Name to access / login to the Snowflake instance. 

Account Identifier is extracted from Dedicated Login URL: iiathjj-dl47211 

Install Devart Python Connector for Snowflake 

Download the installation file and unzip it to the folder 

To install the Python package, your environment needs to have the installed Python and pip library to install the Python Connector package. 

Install Python in your environment. Download the Python 3.12 version from here and run the installation file 

After installation successfully, open the CMD Command Prompt to check the version of the pip library 

If pip is not available, run the command to install the pip library  

cd C:\Users\dtdinh\AppData\Local\Programs\Python\Python312 

python get-pip.py

In the command prompt, browse to the folder where you unzipped the installation package and run the command 

pip install devart_snowflake_connector-1.0.1-cp312-cp312-win_amd64.whl 

In case, we have installed Anaconda product in our environment, we are able to install the package and leverage Anaconda to develop your programs with Snowflake. 

Open Anaconda and launch the CMD.exe prompt 

Run the command to check if pip is installed. Otherwise, we must install pip  

py -m pip –version 

Run the command to create a new environment in Anaconda where we isolate the environment and packages 

Conda create -n Devart Python=3.12 anaconda  

Run the command to active the Devart environment created 

conda active Devart 

Run the command to install the Python Connector in the Devart environment Anaconda 

cd C:\Working\11-Tech-Published-Articles\26-Devart-Python-Connector-Snowflake\DevartPythonSnowflake\whl 

Browse to the folder of Python Connector 

pip install devart_snowflake_connector-1.0.1-cp312-cp312-win_amd64.whl 

Connect to Snowflake database 

After we install the Devart Python Connector, we are able to connect to the Snowflake database. We can use any IDE Python to develop Python programs that need to connect to the Snowflake database, such as Anaconda… 

Open Anaconda and launch the Jupyter Notebook in the Devart environment 

Create a Notebook and write the Python code to connect the Snowflake DEMO database and query data from the EMPLOYEE_CHURN table 

Step 1: Import the Devart Python Connector package 

# Step1 : Import Python Connector library 

import devart.snowflake

Step 2: Establish the connection to Snowflake database 

# Step2: Make the connection 

sf_connection = devart.snowflake.connect( 

    Domain="Account Identifier", 

    UserId="User to access the Snowflake database", 

    Password="Password of UserID ", 

    Database="Snowflake Database", 

    Schema="Schema in Database" 

)

Step3: Create the cursor object to fetch data from Snowflake database 

# Step3: Create the cursor to query data 

sf_cursor = sf_connection.cursor() 

Step4: Query 10 employees from EMPLOYEE_CHURN 

# Step4: Query 10 Employees 

sf_cursor.execute("SELECT * FROM EMPLOYEE_CHURN LIMIT 10")

Step5: Print 10 records  

# Step5: Print 10 Employee records 

for row in sf_cursor.fetchall():  

    print(row)

The program looks like 

Now, we execute the Python and see the result in order to analyze the Employee Churn. 

Conclusion 

Python Connector for Snowflake is a reliable connectivity solution for accessing the Snowflake data cloud from Python applications to perform create, read, update, and delete operations on stored data. The solution fully implements the Python DB API 2.0 specification and is distributed as a wheel package for Windows 32-bit and 64-bit. 

RELATED ARTICLES

Whitepaper

Social

Topics

Products