Getting Data from Snowflake REST API using Python: Complete Tutorial

Name

Olaf Källström

Published on 8/17/2023

(opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab) (opens in a new tab)

In the world of data warehousing, Snowflake has emerged as a leading platform, offering a plethora of connectors compatible with industry standards across various programming languages. One such connector is the Snowflake REST API, which is language-agnostic and allows for seamless interaction with the Snowflake platform. This article aims to provide a comprehensive guide on getting data from Snowflake REST API using Python, a popular programming language known for its simplicity and robustness.

Python, with its rich ecosystem of libraries and frameworks, is an excellent choice for interacting with Snowflake's REST API. Whether you're using the Python connector, which is compatible with PEP 249, or leveraging the Snowpipe REST API for data loading, Python offers a range of possibilities. This article will delve into the details of these interactions, providing practical examples and addressing common FAQs.

Want to Quickly Visulize Snowflake Data (opens in a new tab)? You might want to take a look at RATH (opens in a new tab)!

RATH GitHub Link: https://github.com/Kanaries/Rath (opens in a new tab)

Imagine you can easily clean and import your data stored in Snowflake, and generate data insights with visualization quickly and efficientl, and perform exploratory data analysis without complicated coding. That is exactly what RATH is designed for.

Watch the following demo of RATH quickly identifying anomalies in data with the Data Painter (opens in a new tab) feature:

Interested? RATH has more advanced features that rocks! Check out RATH website (opens in a new tab) for more details now!

Part 1: Understanding Snowflake and REST API

Snowflake is a cloud-based data warehousing platform that provides a multitude of connectors compatible with industry standards in each programming language. One of these connectors is the REST API, which is agnostic to any programming language. This means you can use any programming language to interact with Snowflake via the REST API, making it a versatile and flexible choice for developers.

The REST API operates by sending HTTP requests to Snowflake's server and receiving HTTP responses. This interaction allows you to perform various operations, such as data loading, querying data, and managing your Snowflake account. For instance, you can use the REST API to authenticate your session, issue SQL queries, monitor the status of your queries, and retrieve query results.

Part 2: Using Python with Snowflake REST API

Python is a powerful programming language that's widely used in data analysis, machine learning, web development, and more. Its simplicity and readability make it a popular choice among developers. When it comes to interacting with Snowflake's REST API, Python offers several advantages.

Firstly, Python has a rich ecosystem of libraries that can simplify the process of sending HTTP requests and handling HTTP responses. Libraries such as requests and http.client provide easy-to-use functions for these tasks. Secondly, Python's support for JSON (JavaScript Object Notation) is invaluable when working with REST APIs, as JSON is commonly used to structure data in API requests and responses.

In the context of Snowflake, Python can be used to send SQL queries to the REST API, handle the responses, and manipulate the returned data. For instance, you can use Python to issue a SQL query to Snowflake, retrieve the query results in JSON format, and then use the json library to parse the JSON data.

Part 3: Practical Example of Using Python with Snowflake REST API

Let's delve into a practical example of how you can use Python to interact with Snowflake's REST API. In this example, we'll focus on the process of authenticating a session, issuing a SQL query, and retrieving the query results.

Firstly, you need to authenticate your session by sending a POST request to the /session/v1/login-request endpoint. The request body should include your Snowflake account details and credentials. If the authentication is successful, you'll receive a response containing a token, which you'll use in subsequent API requests.

Next, you can issue a SQL query by sending a POST request to the /queries/v1/query-request endpoint.

After issuing the SQL query, you will receive a response that includes the query id and a success flag. The success flag indicates whether the system has accepted the query, but it does not provide information about the execution status of the query.

To check the status of the query, you can send a GET request to the /monitoring/queries/{query-id} endpoint, passing the query id in the URL. If the query has been executed successfully, you will receive a response indicating that the query succeeded.

Finally, to retrieve the results of the query, you can send another POST request to the /queries/v1/query-request endpoint, this time passing the query id in the SQL text of the request body. The response will contain the query results in the rowset field of the data object.

Here is a simplified example of how you might implement this process in Python:

import requests
import json
 
## Authenticate the session
auth_url = "https://{account}.{region}.snowflakecomputing.com/session/v1/login-request?warehouse={warehouse}"
auth_data = {
    "data": {
        "CLIENT_APP_ID": "lightweight-client",
        "CLIENT_APP_VERSION": "0.0.1",
        "ACCOUNT_NAME": "...",
        "LOGIN_NAME": "...",
        "PASSWORD": "..."
    }
}
auth_response = requests.post(auth_url, data=json.dumps(auth_data))
token = auth_response.json()["data"]["token"]
 
## Issue a SQL query
query_url = "https://{account}.{region}.snowflakecomputing.com/queries/v1/query-request?requestId={random-uuid}"
query_headers = {"Authorization": f"Snowflake Token=\"{token}\""}
query_data = {
    "sqlText": "SELECT * FROM my_table",
    "asyncExec": True,
    "sequenceId": 1,
    "querySubmissionTime": 1635322866647
}
query_response = requests.post(query_url, headers=query_headers, data=json.dumps(query_data))
query_id = query_response.json()["data"]["queryId"]
 
## Check the status of the query
status_url = f"https://{account}.{region}.snowflakecomputing.com/monitoring/queries/{query_id}"
status_response = requests.get(status_url, headers=query_headers)
status = status_response.json()["data"]["queries"][0]["status"]
 
## Retrieve the query results
if status == "SUCCESS":
    results_url = "https://{account}.{region}.snowflakecomputing.com/queries/v1/query-request?requestId={random-uuid}"
    results_data = {
        "sqlText": f"SELECT * FROM table(result_scan('{query_id}'))",
        "asyncExec": False,
        "sequenceId": 1,
        "querySubmissionTime": 1635066639000
    }
    results_response = requests.post(results_url, headers=query_headers, data=json.dumps(results_data))
    results = results_response.json()["data"]["rowset"]

This example demonstrates the basic process of interacting with Snowflake's REST API using Python. However, keep in mind that this is a simplified example and the actual implementation may require additional error handling and other considerations.

Part 4: Handling Large Result Sets

When working with large datasets, the returned payload from Snowflake's REST API may not contain any rows in the rowset array. Instead, it contains chunkHeaders and chunks. These chunks are essentially S3 offloaded encrypted objects that are ready to download. The objects have the same JSON format as the rowset would have.

Here's how you can handle large result sets in Python:

## Check if the response contains chunks
if "chunks" in results_response.json()["data"]:
    chunks = results_response.json()["data"]["chunks"]
    chunk_headers = results_response.json()["data"]["chunkHeaders"]
 
    ## Download and decrypt each chunk
    for chunk in chunks:
        chunk_url = chunk["url"]
        chunk_response = requests.get(chunk_url, headers=chunk_headers)
        chunk_data = chunk_response.json()
 
        ## Process the chunk data
        for row in chunk_data["rowset"]:
            process_row(row)

This code checks if the response contains chunks. If it does, it downloads and decrypts each chunk, then processes the data in each chunk.

Part 5: Using Snowpipe REST API for Data Loading

Snowpipe is a service provided by Snowflake for loading data into your Snowflake data warehouse. It's designed to load data as soon as it arrives in your cloud-based storage. Snowpipe uses the Snowflake REST API, allowing you to automate the process of loading data.

Here's a basic example of how you can use Python to interact with the Snowpipe REST API:

## Define the Snowpipe REST API URL
snowpipe_url = "https://{account}.{region}.snowflakecomputing.com/v1/data/pipes/{pipe_name}/insertFiles"
 
## Define the request headers
headers = {
    "Authorization": f"Snowflake Token=\"{token}\"",
    "Content-Type": "application/json"
}
 
## Define the request body
body = {
    "files": [
        "s3://my-bucket/my-file.csv"
    ]
}
 
## Send the request to the Snowpipe REST API
response = requests.post(snowpipe_url, headers=headers, data=json.dumps(body))
 
## Check the response
if response.status_code == 200:
    print("Data loading started successfully.")
else:
    print(f"Failed to start data loading: {response.json()['message']}")

This code sends a request to the Snowpipe REST API to start loading data from a specified file in your S3 bucket. The response from the Snowpipe REST API will indicate whether the data loading process started successfully.

Conclusion

In conclusion, getting data from Snowflake REST API using Python is a powerful way to leverage the capabilities of Snowflake and Python. Whether you're loading data, querying data, or managing your Snowflake account, Python provides a robust and flexible way to interact with Snowflake's REST API. With the practical examples and FAQs provided in this article, you should now have a solid understanding of how to get started with Snowflake REST API and Python. Happy coding!

FAQs

How do you pull data from a Snowflake in Python?

You can pull data from Snowflake in Python by using the Snowflake Python Connector or the Snowflake REST API. The Python Connector allows you to interact with Snowflake using Python's database API specification (PEP 249), while the REST API allows you to send HTTP requests to perform various operations on Snowflake.

Can Snowflake pull data from API?

Yes, Snowflake can pull data from APIs using external functions. These functions allow Snowflake to call out to an external API and retrieve data during a query. Additionally, you can use Snowflake's REST API to interact with your Snowflake account and perform operations such as data loading and querying data.

What is the Snowflake API connection in Python?

The Snowflake API connection in Python refers to the connection established between your Python application and Snowflake using the Snowflake Python Connector or the Snowflake REST API. This connection allows your Python application to interact with Snowflake, enabling operations such as data loading, data querying, and account management.