SNOW-740868: snowpark-python and snowflake-sqlalchemy packages are not compatible #673
Comments
github-actions
bot
changed the title
|
Hi @ndamclean, I cannot reproduce this. Looking at the logs, it seems like the error occurs when sqlalchemy engine tries to initialize a snowflake connector connection. Could you please try constructing the connection like: instead? |
|
@sfc-gh-stan this code works fine when I remove the part that imports the Are you sure you are using all the same versions of python and packages described in the ticket? |
I can confirm I'm using the same version of all the python packages, I'm using Python3.8.5 and running on MacOS. I changed the column names in the second last line to "key" and "value", and the code snippet finished without errors. If I use the |
|
@sfc-gh-stan I don't know why it would be working on your end and not for me 🤷 My colleagues who use Mac OSX were able to reproduce this error. Also I opened up a support ticket with Snowflake and linked this github issue and they were able to reproduce an error.
A version of this function has been in our code base for months and has been working just fine until we tried using the |
I realized I had made a typo and I fixed the code in the ticket description. Sorry about that. If i make this change (don't import --- a/snowtest.py
+++ b/snowtest2.py
@@ -54,11 +54,11 @@ def get_snowpark_session():
db_engine = init_db_engine()
-snowpark_session = get_snowpark_session()
+#snowpark_session = get_snowpark_session()
stmt = sa.sql.text("CREATE OR REPLACE TABLE test_table (key TEXT, value TEXT)")
with db_engine.connect() as conn:
conn.execute(stmt)
-tbl = snowpark_session.table("test_table").select("key", "value")
-print(tbl.show())
+#tbl = snowpark_session.table("test_table").select("key", "value")
+#print(tbl.show())If i make this change (only use snowpark ; create a connection using SQLAlchemy but do not use it to execute queries), the code works: --- a/snowtest.py
+++ b/snowtest3.py
@@ -2,6 +2,7 @@ import os
from urllib.parse import urlunsplit, urlencode
import sqlalchemy as sa
+from snowflake.snowpark import types as T
def get_snowflake_env_vars():
@@ -56,9 +57,10 @@ def get_snowpark_session():
db_engine = init_db_engine()
snowpark_session = get_snowpark_session()
-stmt = sa.sql.text("CREATE OR REPLACE TABLE test_table (key TEXT, value TEXT)")
with db_engine.connect() as conn:
- conn.execute(stmt)
+ pass
+schema = T.StructType([T.StructField("key", T.StringType()), T.StructField("value", T.StringType())])
+snowpark_session.create_dataframe([["a", "b"]], schema).write.mode("overwrite").save_as_table("test_table")
tbl = snowpark_session.table("test_table").select("key", "value")
print(tbl.show()) |
|
@sfc-gh-stan in case it helps you reproduce this, here are the You could try setting up a new virtual environment using Thank you for your help looking into this. |
|
@sfc-gh-stan Hi. I have a quick update on this. I was able to get the previous code working by upgrading to the newest versions of the packages (the However, the actual code base I'm working with still has issues. I updated the code sample here in this ticket to better reflect the errors coming from our code base. The problem seemed to stem from the The I was able to work around the issue by adding constructor arguments to enforce usage of I explained my findings to the SF support person assigned to my ticket and they said they are investigating the issue but we have a workaround for now. This is the patch to the code that works around the issue: diff --git a/snowtest_original.py b/snowtest_fixed.py
index 250108f..c7ebb4f 100644
--- a/snowtest_original.py
+++ b/snowtest_fixed.py
@@ -46,13 +46,21 @@ def init_db_engine(**create_engine_kwargs) -> sa.engine.Engine:
"", # fragment
)
)
- return sa.create_engine(sqlalchemy_url, **create_engine_kwargs)
+
+ create_engine_kwargs["connect_args"] = create_engine_kwargs.get("connect_args", {})
+ if "paramstyle" in create_engine_kwargs["connect_args"]:
+ raise RuntimeError(
+ "paramstyle must be set to pyformat, other param styles currently have SF connector bugs"
+ )
+ create_engine_kwargs["connect_args"]["paramstyle"] = "pyformat"
+ return sa.create_engine(sqlalchemy_url, paramstyle="pyformat", **create_engine_kwargs)
def get_snowpark_session():
"""Get a SnowPark client session instance."""
- sf_env = get_snowflake_env_vars()
- return Session.builder.configs(sf_env).create()
+ connection_params = get_snowflake_env_vars()
+ connection_params["paramstyle"] = "pyformat"
+ return Session.builder.configs(connection_params).create()
db_engine = init_db_engine()
|
|
After all my investigations here, I think this might actually be a bug in the snowflake-connector-python package handling of It does seem a bit weird that importing the |
|
Hi @ndamclean , thank you so much for the pointers! The culprit is indeed the import statement Specifying This is a bug in Snowpark Python and I will work on the fix in the next sprint. Thanks again for all the investigation, we found a really obscure bug here 😄 |
|
Should be fixed by #692 , closing this. |
(also created an issue in the
snowflake-sqlalchemygithub: snowflakedb/snowflake-sqlalchemy#383)pip freeze)?snowflake packages
full outuput
Run the following python script that uses snowflake SQLAlchemy and SnowPark.
(note environment variables used for
snowsqlare used to configure the Snowflake connection)What did you expect to see?
Program to run and exit without an error.
Can you set logging to DEBUG and collect the logs?
The text was updated successfully, but these errors were encountered: