Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use Python in Snowflake: Syntax, Example, and Tips

You can use Python in Snowflake by using the Snowpark library, which lets you write Python code that runs inside Snowflake's environment. This allows you to process data directly in Snowflake without moving it outside. You write Python functions and use Snowflake's Python runtime to execute them.
📐

Syntax

To use Python in Snowflake, you first create a Snowpark session in your Python environment. Then you write Python code using Snowpark APIs to manipulate data. You can also create stored procedures in Python that run inside Snowflake.

  • Session.builder.configs(): Set connection details.
  • session.table(): Access tables.
  • session.udf.register(): Register Python functions as UDFs.
  • session.sproc.register(): Register Python stored procedures.
python
from snowflake.snowpark import Session

# Create a Snowpark session
connection_parameters = {
    "account": "<account_name>",
    "user": "<username>",
    "password": "<password>",
    "role": "<role>",
    "warehouse": "<warehouse>",
    "database": "<database>",
    "schema": "<schema>"
}
session = Session.builder.configs(connection_parameters).create()
💻

Example

This example shows how to create a Python function as a User Defined Function (UDF) in Snowflake using Snowpark, then use it in a SQL query.

python
from snowflake.snowpark import Session
from snowflake.snowpark.functions import udf

# Connection parameters (replace with your info)
connection_parameters = {
    "account": "xy12345",
    "user": "user1",
    "password": "mypassword",
    "role": "SYSADMIN",
    "warehouse": "COMPUTE_WH",
    "database": "TEST_DB",
    "schema": "PUBLIC"
}

# Create session
session = Session.builder.configs(connection_parameters).create()

# Define a Python function
@udf
class multiply_by_two:
    def __call__(self, x: int) -> int:
        return x * 2

# Register the UDF
session.udf.register(multiply_by_two(), name="multiply_by_two", replace=True)

# Use the UDF in a query
df = session.sql("SELECT 5 AS number, multiply_by_two(5) AS doubled")
df.show()
Output
+--------+---------+ | NUMBER | DOUBLED | +--------+---------+ | 5 | 10 | +--------+---------+
⚠️

Common Pitfalls

Common mistakes when using Python in Snowflake include:

  • Not setting up the Snowpark session with correct connection details.
  • Forgetting to register Python functions as UDFs before using them in SQL.
  • Trying to use Python libraries not supported in Snowflake's Python runtime.
  • Not handling data types correctly between Python and Snowflake.

Always test your Python code locally and then register it properly in Snowflake.

python
from snowflake.snowpark import Session

# Wrong: Missing connection parameters
# session = Session.builder.create()  # This will fail

# Right: Provide full connection info
connection_parameters = {
    "account": "xy12345",
    "user": "user1",
    "password": "mypassword",
    "role": "SYSADMIN",
    "warehouse": "COMPUTE_WH",
    "database": "TEST_DB",
    "schema": "PUBLIC"
}
session = Session.builder.configs(connection_parameters).create()
📊

Quick Reference

ActionCommand / MethodDescription
Create sessionSession.builder.configs(params).create()Connect to Snowflake with Python
Access tablesession.table("table_name")Get a Snowflake table as a DataFrame
Register UDFsession.udf.register(func, name, replace=True)Create a Python UDF in Snowflake
Register stored proceduresession.sproc.register(func, name)Create a Python stored procedure
Run SQLsession.sql("SQL query")Execute SQL commands
✅

Key Takeaways

Use Snowpark's Session to connect Python with Snowflake securely.
Register Python functions as UDFs or stored procedures to run them inside Snowflake.
Test Python code locally before deploying to Snowflake.
Ensure connection parameters are correct to avoid session creation errors.
Snowflake's Python runtime supports many but not all Python libraries.