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
| Action | Command / Method | Description |
|---|---|---|
| Create session | Session.builder.configs(params).create() | Connect to Snowflake with Python |
| Access table | session.table("table_name") | Get a Snowflake table as a DataFrame |
| Register UDF | session.udf.register(func, name, replace=True) | Create a Python UDF in Snowflake |
| Register stored procedure | session.sproc.register(func, name) | Create a Python stored procedure |
| Run SQL | session.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.
