How to use Snowflake backend without specifying user, password, and account

[SamEdwardes]

Hi Ibis team. First off thank you for the awesome work on Ibis, I really enjoy using it. I have a question/comment about using the Snowflake back end: https://ibis-project.org/backends/snowflake#connect.

I work at Posit, and we have a feature for one of our products where we managed Oauth credentials for users: https://docs.posit.co/ide/server-pro/user/posit-workbench/managed-credentials/snowflake.html.

When I connect to Snowflake using the Python Connector, my code looks like this:

import snowflake.connector

con = snowflake.connector.connect(
    connection_name="workbench",
    database="LENDING_CLUB",
    schema="PUBLIC"
)

query = """
SELECT "ID", "LOAN_AMNT", "TERM", "INT_RATE", "GRADE"
FROM LOAN_DATA
WHERE "LOAN_AMNT" IS NOT NULL
ORDER BY "LOAN_AMNT" DESC
LIMIT 10;
"""

con.cursor().execute(query).fetchall()

When I use ibis, I have found that I need to do this:

import ibis
import snowflake.connector

con = ibis.snowflake.connect(
    # user, password, and account must be set to empty strings when using ibis
    # unless you are running Posit Workbench in the Snowflake Native App
    user="",
    password="",
    account="",
    # Database should be set to <database>/<schema>
    database="LENDING_CLUB/PUBLIC",
    connection_name="workbench",
)

tbl = con.table("LOAN_DATA")

(
    tbl
    .select("ID", "LOAN_AMNT", "TERM", "INT_RATE", "GRADE")
    .filter(tbl.LOAN_AMNT.notnull())
    .order_by(ibis.desc("LOAN_AMNT"))
    .limit(10)
    .to_pandas()
)

I have to put empty strings in user, account, and password. These should not need to be defined because they are set in my users environment:

❯ env | rg SNOW
SNOWFLAKE_ACCOUNT=yyyy
SNOWFLAKE_ROLE=SOLENG
SNOWFLAKE_HOME=/tmp/sam.edwardes/posit-workbench/a11233c9ab5a7a4183a69

❯ ll "$SNOWFLAKE_HOME"
total 8
drwx------ 2 sam.edwardes sam.edwardes  49 Dec 11 23:26 ./
drwx------ 3 sam.edwardes sam.edwardes  51 Dec 11 23:26 ../
-rw------- 1 sam.edwardes sam.edwardes 111 Dec 11 23:28 config.toml
-rw------- 1 sam.edwardes sam.edwardes 431 Dec 11 23:28 connections.toml

❯ cat "${SNOWFLAKE_HOME}/config.toml"
# This file was automatically generated by Posit Workbench. Do not edit.
default_connection_name = "workbench"

❯ cat "${SNOWFLAKE_HOME}/connections.toml"
# This file was automatically generated by Posit Workbench. Do not edit.
[workbench]
account = "yyyy"
token = "xxxx"
authenticator = "oauth"

Here is the output if I do not use empty strings:

import ibis
import snowflake.connector

con = ibis.snowflake.connect(
    database="LENDING_CLUB/PUBLIC",
    connection_name="workbench",
)

Traceback (most recent call last):
  File "/home/sam.edwardes/projects/data-sources/reprex.py", line 4, in <module>
    con = ibis.snowflake.connect(
          ^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/sam.edwardes/projects/data-sources/.venv/lib/python3.11/site-packages/ibis/__init__.py", line 97, in connect
    return backend.connect(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/sam.edwardes/projects/data-sources/.venv/lib/python3.11/site-packages/ibis/backends/base/__init__.py", line 530, in connect
    new_backend.reconnect()
  File "/home/sam.edwardes/projects/data-sources/.venv/lib/python3.11/site-packages/ibis/backends/base/__init__.py", line 545, in reconnect
    self.do_connect(*self._con_args, **self._con_kwargs)
TypeError: Backend.do_connect() missing 3 required positional arguments: 'user', 'password', and 'account'

❯ uv run python --version
Python 3.11.9

❯ uv pip list
Package                    Version
-------------------------- -----------
asn1crypto                 1.5.1
asttokens                  3.0.0
atpublic                   3.1.2
bidict                     0.23.1
certifi                    2024.8.30
cffi                       1.17.1
charset-normalizer         3.4.0
comm                       0.2.2
cryptography               44.0.0
debugpy                    1.8.9
decorator                  5.1.1
executing                  2.1.0
filelock                   3.16.1
greenlet                   3.1.1
ibis-framework             5.1.0
idna                       3.10
ipykernel                  6.29.5
ipython                    8.30.0
jedi                       0.19.2
jupyter-client             8.6.3
jupyter-core               5.7.2
markdown-it-py             3.0.0
matplotlib-inline          0.1.7
mdurl                      0.1.2
multipledispatch           0.6.0
nest-asyncio               1.6.0
numpy                      1.26.4
packaging                  24.2
pandas                     2.2.3
parso                      0.8.4
parsy                      2.1
pexpect                    4.9.0
platformdirs               4.3.6
polars                     1.16.0
pooch                      1.8.2
prompt-toolkit             3.0.48
psutil                     6.1.0
ptyprocess                 0.7.0
pure-eval                  0.2.3
pyarrow                    18.1.0
pycparser                  2.22
pygments                   2.18.0
pyjwt                      2.10.1
pyodbc                     5.2.0
pyopenssl                  24.3.0
python-dateutil            2.9.0.post0
pytz                       2024.2
pyzmq                      26.2.0
requests                   2.32.3
rich                       13.9.4
six                        1.17.0
snowflake-connector-python 3.12.4
snowflake-sqlalchemy       1.7.1
sortedcontainers           2.4.0
sqlalchemy                 2.0.36
sqlalchemy-views           0.3.2
sqlglot                    11.7.1
stack-data                 0.6.3
tomlkit                    0.13.2
toolz                      0.12.1
tornado                    6.4.2
tqdm                       4.67.1
traitlets                  5.14.3
typing-extensions          4.12.2
tzdata                     2024.2
urllib3                    2.2.3
wcwidth                    0.2.13
xxhash                     3.5.0

Is there anything I am doing wrong? Is it possible to avoid having to put empty strings for user, password, and account?

[gforsyth]

Hey @SamEdwardes ! Your ibis version is quite old -- our current release is 9.5.0 - can you try upgrading and see if that sorts out the issue?

[SamEdwardes]

Thank you @gforsyth. It is strange that I ended up with such an old version of ibis since this is a new project. I think somehow the order I add my dependencies uv resolved to an old version of ibis.

I removed all of my dependencies and then re-added what I needed:

$ uv add 'ibis-framework[snowflake]' pyarrow ipykernel pandas polars pyodbc rich snowflake-connector-python

$ uv pip list
Package                    Version
-------------------------- -----------
asn1crypto                 1.5.1
asttokens                  3.0.0
atpublic                   5.0
certifi                    2024.8.30
cffi                       1.17.1
charset-normalizer         3.4.0
comm                       0.2.2
cryptography               44.0.0
debugpy                    1.8.9
decorator                  5.1.1
executing                  2.1.0
filelock                   3.16.1
ibis-framework             9.5.0
idna                       3.10
ipykernel                  6.29.5
ipython                    8.30.0
jedi                       0.19.2
jupyter-client             8.6.3
jupyter-core               5.7.2
markdown-it-py             3.0.0
matplotlib-inline          0.1.7
mdurl                      0.1.2
nest-asyncio               1.6.0
numpy                      2.2.0
packaging                  24.2
pandas                     2.2.3
parso                      0.8.4
parsy                      2.1
pexpect                    4.9.0
platformdirs               4.3.6
polars                     1.17.1
prompt-toolkit             3.0.48
psutil                     6.1.0
ptyprocess                 0.7.0
pure-eval                  0.2.3
pyarrow                    17.0.0
pyarrow-hotfix             0.6
pycparser                  2.22
pygments                   2.18.0
pyjwt                      2.10.1
pyodbc                     5.2.0
pyopenssl                  24.3.0
python-dateutil            2.9.0.post0
pytz                       2024.2
pyzmq                      26.2.0
requests                   2.32.3
rich                       13.9.4
six                        1.17.0
snowflake-connector-python 3.12.4
sortedcontainers           2.4.0
sqlglot                    25.20.2
stack-data                 0.6.3
tomlkit                    0.13.2
toolz                      0.12.1
tornado                    6.4.2
traitlets                  5.14.3
typing-extensions          4.12.2
tzdata                     2024.2
urllib3                    2.2.3
wcwidth                    0.2.13

With the latest version, it is now working as expected:

import ibis

con = ibis.snowflake.connect(
    schema="PUBLIC",
    database="LENDING_CLUB",
    connection_name="workbench",
)

tbl = con.table("LOAN_DATA")

print(
    tbl
    .select("ID", "LOAN_AMNT", "TERM", "INT_RATE", "GRADE")
    .filter(tbl.LOAN_AMNT.notnull())
    .order_by(ibis.desc("LOAN_AMNT"))
    .limit(10)
    .to_pandas()
)

There are some warnings and errors in the output, but the code executes. Any ideas on what I could/should do about these warnings and errors?

/home/sam.edwardes/projects/data-sources/.venv/lib/python3.11/site-packages/ibis/backends/snowflake/__init__.py:330: UserWarning: Unable to create Ibis UDFs, some functionality will not work: 100132 (P0000): JavaScript execution error: Uncaught Execution of multiple statements failed on statement "CREATE DATABASE IF NOT EXISTS ..." (at line 1, position 0).
SQL access control error:
Insufficient privileges to operate on account 'MHB16489' in SYSTEM$MULTISTMT at '    throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4
stackstrace: 
SYSTEM$MULTISTMT line: 10
  warnings.warn(
          ID  LOAN_AMNT       TERM INT_RATE GRADE
0  127187880      40000  60 months     7.35     A
1  127078752      40000  36 months     9.93     B
2  127302675      40000  60 months    16.02     C
3  127584602      40000  36 months     6.72     A
4  127500978      40000  60 months    10.42     B
5  126241541      40000  60 months     5.32     A
6  127581771      40000  60 months    11.99     B
7  126723098      40000  36 months     5.32     A
8  127521708      40000  60 months    13.59     C
9  126303756      40000  60 months     7.35     A

[gforsyth]

Ahh, right, we try to create a few small UDFs on Snowflake connections for helper functions. Since it looks like you don't have the permission to do that, you can update your connection line as follows to stop trying to run them:

import ibis

con = ibis.snowflake.connect(
    schema="PUBLIC",
    database="LENDING_CLUB",
    connection_name="workbench",
    create_object_udfs=False,
)

[SamEdwardes]

That worked, thank you for all your help.