search

snowflakedb / snowflake-connector-python

Snowflake Connector for Python
https://pypi.python.org/pypi/snowflake-connector-python/
Apache License 2.0
578 stars 467 forks source link

SNOW-1644764: Async query is not executed. (internal tracking id for root cause: SNOW-1644152) #2039

Closed motykatomasz closed 3 days ago

motykatomasz commented 2 weeks ago

Python version

3.11.9

Operating system and processor architecture

macOS-14.6.1-arm64-arm-64bit

Installed packages

annotated-types==0.7.0
anyio==4.4.0
appnope @ file:///home/conda/feedstock_root/build_artifacts/appnope_1707233003401/work
asn1crypto==1.5.1
astroid==3.2.3
asttokens @ file:///home/conda/feedstock_root/build_artifacts/asttokens_1698341106958/work
atpublic==4.1.0
certifi==2024.7.4
cffi==1.16.0
cfgv==3.4.0
charset-normalizer==3.3.2
click==8.1.7
cloudpickle==2.2.1
comm @ file:///home/conda/feedstock_root/build_artifacts/comm_1710320294760/work
coverage==7.6.0
cryptography==41.0.7
debugpy @ file:///private/var/folders/k1/30mswbxs7r1g6zwn8y4fyt500000gp/T/abs_563_nwtkoc/croot/debugpy_1690905063850/work
decorator @ file:///home/conda/feedstock_root/build_artifacts/decorator_1641555617451/work
dill==0.3.8
distlib==0.3.8
exceptiongroup @ file:///home/conda/feedstock_root/build_artifacts/exceptiongroup_1704921103267/work
executing @ file:///home/conda/feedstock_root/build_artifacts/executing_1698579936712/work
fastapi==0.109.2
filelock==3.15.4
gitdb==4.0.11
GitPython==3.1.43
gunicorn==22.0.0
h11==0.14.0
httpcore==1.0.5
httpx==0.25.2
identify==2.6.0
idna==3.7
importlib_metadata==8.0.0
iniconfig==2.0.0
ipykernel @ file:///Users/runner/miniforge3/conda-bld/ipykernel_1708996616394/work
ipython @ file:///home/conda/feedstock_root/build_artifacts/ipython_1715263367085/work
isort==5.13.2
jaraco.classes==3.4.0
jedi @ file:///home/conda/feedstock_root/build_artifacts/jedi_1696326070614/work
Jinja2==3.1.4
jupyter_client @ file:///home/conda/feedstock_root/build_artifacts/jupyter_client_1710255804825/work
jupyter_core @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_782yoyc_98/croot/jupyter_core_1698937318631/work
keyring==24.3.1
markdown-it-py==3.0.0
MarkupSafe==2.1.5
matplotlib-inline @ file:///home/conda/feedstock_root/build_artifacts/matplotlib-inline_1713250518406/work
mccabe==0.7.0
mdurl==0.1.2
more-itertools==10.3.0
multidict==6.0.5
mypy==1.11.1
mypy-extensions==1.0.0
nest_asyncio @ file:///home/conda/feedstock_root/build_artifacts/nest-asyncio_1705850609492/work
nodeenv==1.9.1
numpy==2.0.0
oscrypto==1.3.0
packaging==24.1
pandas==2.2.2
parso @ file:///home/conda/feedstock_root/build_artifacts/parso_1712320355065/work
pexpect @ file:///home/conda/feedstock_root/build_artifacts/pexpect_1706113125309/work
pickleshare @ file:///home/conda/feedstock_root/build_artifacts/pickleshare_1602536217715/work
platformdirs==3.11.0
pluggy==1.5.0
pre-commit==3.7.1
prometheus_client==0.20.0
prompt-toolkit @ file:///home/conda/feedstock_root/build_artifacts/prompt-toolkit_1702399386289/work
psutil @ file:///Users/cbousseau/work/recipes/ci_py311_2/psutil_1678995687212/work
ptyprocess @ file:///home/conda/feedstock_root/build_artifacts/ptyprocess_1609419310487/work/dist/ptyprocess-0.7.0-py2.py3-none-any.whl
pure-eval @ file:///home/conda/feedstock_root/build_artifacts/pure_eval_1642875951954/work
pyarrow==17.0.0
pycparser==2.22
pycryptodomex==3.20.0
pydantic==2.8.0
pydantic-settings==2.3.4
pydantic_core==2.20.0
Pygments @ file:///home/conda/feedstock_root/build_artifacts/pygments_1714846767233/work
PyJWT==2.8.0
pylint==3.2.5
pyOpenSSL==23.3.0
pytest==7.4.4
pytest-asyncio==0.21.2
pytest-cov==4.1.0
python-dateutil==2.9.0.post0
python-dotenv==1.0.1
pytz==2024.1
PyYAML==6.0.1
pyzmq @ file:///private/var/folders/k1/30mswbxs7r1g6zwn8y4fyt500000gp/T/abs_43pxpbos3z/croot/pyzmq_1705605108344/work
requests==2.32.3
requirements-parser==0.9.0
rich==13.7.1
ruff==0.1.15
shellingham==1.5.4
six==1.16.0
smmap==5.0.1
sniffio==1.3.1
snowflake-cli-labs==2.6.1
snowflake-connector-python==3.11.0
snowflake-snowpark-python==1.19.0
snowflake-sqlalchemy==1.6.1
snowflake.core==0.8.0
sortedcontainers==2.4.0
SQLAlchemy==2.0.31
stack-data @ file:///home/conda/feedstock_root/build_artifacts/stack_data_1669632077133/work
starlette==0.36.3
starlette_exporter==0.19.0
strictyaml==1.7.3
tomlkit==0.12.5
tornado @ file:///private/var/folders/nz/j6p8yfhx1mv_0grj5xl4650h0000gp/T/abs_3a5nrn2jeh/croot/tornado_1696936974091/work
traitlets @ file:///home/conda/feedstock_root/build_artifacts/traitlets_1713535121073/work
typer==0.12.3
types-setuptools==70.3.0.20240710
typing_extensions==4.12.2
tzdata==2024.1
urllib3==2.2.2
uvicorn==0.25.0
virtualenv==20.26.3
wcwidth @ file:///home/conda/feedstock_root/build_artifacts/wcwidth_1704731205417/work
wrapt==1.16.0
yarl==1.9.4
zipp==3.19.2

What did you do?

We had a sudden problem in a project with executing async queries, so I tested it on the simplest setup:


import snowflake
import snowflake.connector

conn = snowflake.connector.connect(
    user=SNOWFLAKE_USER,
    password=SNOWFLAKE_PASSWORD,
    account=SNOWFLAKE_ACCOUNT,
    warehouse=SNOWFLAKE_WAREHOUSE,
    database=SNOWFLAKE_DATABASE,
    schema=SNOWFLAKE_DB_SCHEMA,
    role=SNOWFLAKE_ROLE,
)
cur = conn.cursor()

cur.execute_async('select count(*) from table', timeout=100)
query_id = cur.sfqid

# Close the cursor and the connection.
cur.close()
conn.close()

# Open a new connection.
new_conn = snowflake.connector.connect(
    user=SNOWFLAKE_USER,
    password=SNOWFLAKE_PASSWORD,
    account=SNOWFLAKE_ACCOUNT,
    warehouse=SNOWFLAKE_WAREHOUSE,
    database=SNOWFLAKE_DATABASE,
    schema=SNOWFLAKE_DB_SCHEMA,
    role=SNOWFLAKE_ROLE,
)

# Create a new cursor.
new_cur = new_conn.cursor()

# Retrieve the results.
new_cur.get_results_from_sfqid(query_id)
results = new_cur.fetchall()
print(f'{results[0]}')

new_cur.close()
new_conn.close()

and indeed, it turns out that the query is not being executed. However, if I try to execute the query synchronously it runs just fine:

import snowflake
import snowflake.connector

conn = snowflake.connector.connect(
    user=SNOWFLAKE_USER,
    password=SNOWFLAKE_PASSWORD,
    account=SNOWFLAKE_ACCOUNT,
    warehouse=SNOWFLAKE_WAREHOUSE,
    database=SNOWFLAKE_DATABASE,
    schema=SNOWFLAKE_DB_SCHEMA,
    role=SNOWFLAKE_ROLE,
)
cur = conn.cursor()

# Submit an asynchronous query for execution.
cur.execute('select count(*) from table')

# Retrieve the results.
results = cur.fetchall()
print(f'{results[0]}')

# Close the cursor and the connection.
cur.close()
conn.close()

What did you expect to see?

I'd expect to see the result of the async query and to see this particular query in the query history in the snowflake monitoring UI.

Can you set logging to DEBUG and collect the logs?

n/a
greg-finley commented 2 weeks ago

From Snowflake support for I think is the same issue:

Hi Greg, Thanks for your patience. It seems that we have identified the root cause of this and fixed it around four hours ago. Please continue to monitor this and let us know if the issue is resolved.

motykatomasz commented 2 weeks ago

Yup, seems it is fixed.

motykatomasz commented 2 weeks ago

Seems that the issue still persists.

sfc-gh-dszmolka commented 1 week ago

hello - unfortunately we indeed had an issue for some customers where the queries executed asynchronously did not appear in the Query History. This is coming from the Snowflake backend (thus, not the client-side PythonConnector, this library) and supposed to be resolved.

If any of you still experiences this weird issue, we will need details, most of all queryIds which were launched recently (past 1-2 days) and still are affected.

Please, could you share either privately provide those examples to Snowflake Support, or here?

motykatomasz commented 6 days ago

@sfc-gh-dszmolka We opened a case in Snowflake Support but we haven't received any new updates there yet. Do you know what's the status for resolving this issue? Unfortunately it sill persists.

sfc-gh-dszmolka commented 6 days ago

i'm sorry to hear that you're not getting responses in a timely manner - but still the best way is to ask for updates in the same case you have with support. We have procedures, metrics, etc. tied to the support cases which simply don't exist out here in Github issues.

To my best knowledge is that issue supposed to be resolved, but unfortunately some of our customers see it. My colleagues in the relevant dev teams of Snowflake engine (thus, not the driver library here) are investigating and looking for new examples, queryIds that seems to be 'missing'. If you have them, please do share with your support engineer who is handling the case. You also have the option to escalate the support case if the issue is getting more serious for you.

sfc-gh-dszmolka commented 3 days ago

short update: my dev colleagues taking care of the Snowflake engine seems to have found a way to mitigate this issue; it's an account wide internal setting which is not accessible from the customer side.

If you already have a case open and still seeing this issue, please refer to SNOW-1644152 for your support engineer so they could proceed with the mitigation. If you're just finding this thread because being affected, please raise a case with Snowflake Support and reference SNOW-1644152.

Since the issue seems to turn out to be unrelated to Snowflake's PythonConnector (this library, in which this issue has been raised originally), please work with Snowflake Support for the mitigation and official root cause analysis. Thank you and again apologies for any inconvenience caused by this unexpected misbehaviour.

editing this comment to add a fresh information just becoming available to me; apparently the mitigation will be deployed system-wide with Snowflake version 8.35, planned to be deployed on week of 16 September 2024. (you can check current Snowflake engine version with select current_version())

Marking this issue as closed for now as it is unrelated to PythonConnector.