python snowflake adbc_ingest "<Table>does not exist or not authorized"

[aschreiber1]

What happened?

I have permissions to insert into a table, but it appears i cant used adbc_ingest to append into the table as well, as I get does not exist or not authorised exception. I have the create stage permissions that the documentations say you need.

cursor.execute("Create table my_test (SECURITYID NUMBER)")

table = pa.table({
'SECURITYID': pa.array([1001, 1002, 1003, 1004, 1005], type=pa.int64())
})

//this works
for row in table.to_pylist():
cursor.execute(
"INSERT INTO my_test (SECURITYID) VALUES (?)",
(row['SECURITYID'],)
)

//this does not work
res = cursor.adbc_ingest("my_test", table, mode="append")

time="2025-02-22T01:45:56Z" level=error msg="error: 002003 (42S02): SQL compilation error:\nObject '"my_test2"' does not exist or not authorized." func="gosnowflake.(*snowflakeConn).queryContextInternal" file="connection.go:413"

Stack Trace

No response

How can we reproduce the bug?

time="2025-02-22T01:45:56Z" level=error msg="error: 002003 (42S02): SQL compilation error:\nObject '"my_test2"' does not exist or not authorized." func="gosnowflake.(*snowflakeConn).queryContextInternal" file="connection.go:413"

Environment/Setup

python snowflake driver, ive tried with both 1.3 and 1.4 and same issue

[aschreiber1]

Looks to me like the issue is that the select query it generates is:
SELECT COUNT(*) FROM IDENTIFIER('"my_test2"')

But those double quotes should not be there, not sure how this works for anybody?

[davlee1972]

The table created is "MY_TEST" if the Snowflake upper case everything default is turned on.

ADBC is saving to "my_test"

cursor.execute("Create table my_test (SECURITYID NUMBER)")
res = cursor.adbc_ingest("MY_TEST", table, mode="append")

or

cursor.execute('Create table "my_test" (SECURITYID NUMBER)')
res = cursor.adbc_ingest("my_test", table, mode="append")

There is an open ticket out there to set "quoted_identifiers" behavior which would wrap identifiers in quotes depending on how it is set.

[aschreiber1]

Right so it's a capitalization issue, thanks this at least gives me a workaround! it's also an issue with not handling the database and schema right. Assuming I am in database X, and Schema Y, all three of these should work
"X.Y.MY_TABLE"
"Y.MY_TABLE"
"MY_TABLE"

Also if I am in database X but schema Z, these should still work
"X.Y.MY_TABLE"
"Y.MY_TABLE"

And even if I am in Database Q, this should still work:
"X.Y.MY_TABLE"

But right now the only thing that seems to work is if you are in Database X and Schema Y and refer to it as "MY_TABLE"

[aschreiber1]

The next problem is that this does not seem to work with temp tables, which is probably the most common use case for inserting for a lot of people to work around the maximum size of an in clause restriction.

[zeroshade]

Assuming I am in database X, and Schema Y, all three of these should work
"X.Y.MY_TABLE"
"Y.MY_TABLE"
"MY_TABLE"

Also if I am in database X but schema Z, these should still work
"X.Y.MY_TABLE"
"Y.MY_TABLE"

And even if I am in Database Q, this should still work:
"X.Y.MY_TABLE"

All of those should work just fine with ADBC, do you have an example of them not working?

The next problem is that this does not seem to work with temp tables, which is probably the most common use case for inserting for a lot of people to work around the maximum size of an in clause restriction.

We have an option for this, I think it's just not yet implemented in the snowflake driver yet. I'll double check this, it shouldn't be difficult to add support for the temp flag in adbc_ingest for the snowflake driver.

[CurtHagenlocher]

Note that "X.Y.MY_TABLE" is a table with that name in the default catalog and schema. If X and Y are intended to be catalog and schema respectively, you would need "X"."Y"."MY_TABLE".

[davlee1972]

"X.Y.MY_TABLE" is a single table name I think. These should be equivalent I believe

X.Y.MY_TABLE
x.y.my_table
"X".y."MY_TABLE"

Anything wrapped in double quotes when passed to Snowflake SQL is literal.

[zeroshade]

Ah yes. You should use the options: "adbc.ingest.target_catalog" and "adbc.ingest.target_db_schema" alongside the table name to have them get used for ingest

[lidavidm]

It's unfortunate that SQL semantics are odd but essentially ADBC tries not to second-guess the input and treats it as literal (i.e. always quoted), if the database applies case folding to unquoted names it's best to always be explicit and work with the case folded name directly instead of expecting intermediate layers to apply it.

This is common enough that perhaps we should put together some cookbook examples, though

[aschreiber1]

Did you ever add support for writing to temp tables from the python snowflake driver?

[lidavidm]

@aschreiber1 please open a new issue

[aschreiber1]

@lidavidm have made it here: #3087

[lidavidm]

Thanks!

I also see #3090 just got opened. I think we can consider this closed once we implement the options for temp tables and to explicitly select a schema/database?