Snowflake JDBC - guidance to help add support

[mbarton98]

This was the only Emacs solution that I found that works with Snowflake. I would love to help test or with some guidance add some additional support for completion. The show tables function errors out with a bad query, but describe table at point works. So for now to get completion of fields, I use C-h t to open a buffer describing the table and expansion will use that buffer as a source when editing the SQL.

If nothing else, I could share the config if you wanted to add to the readme.

[daniel2501]

What config did you use to get connected to Snowflake? I've been trying, but getting a timeout.

[mbarton98]

I have these two connections defined in my init file. I replaced some of the sensitive info, but the first is an example using the browser for authentication to support our SAML federation setup with Snowflake. The second example is for a training class using username and password. (ejc-create-connection "ideas_snowflake" :dependencies [[net.snowflake/snowflake-jdbc "3.13.4"] [net.java.dev.jna/jna "5.8.0"]] :classpath (concat "~/.m2/repository/net/snowflake/client/jdbc" "/SnowflakeDriver/snowflake-jdbc-3.13.4.jar") :connection-uri (concat "jdbc:snowflake://our_server.snowflakecomputing.com:443/?" “user=my_email_login&" "db=IAM_IDEAS&" "warehouse=IAM_ETL_WH_XS&" "schema=IAM_CRPROFILE&authenticator=externalbrowser")) (ejc-create-connection "class_snowflake" :dependencies [[net.snowflake/snowflake-jdbc "3.13.4"] [net.java.dev.jna/jna "5.8.0"]] :classpath (concat "~/.m2/repository/net/snowflake/client/jdbc" "/SnowflakeDriver/snowflake-jdbc-3.13.4.jar") :connection-uri (concat "jdbc:snowflake://aqa11592.snowflakecomputing.com:443/?" "user=woodchuck&" “password=Password%20WithAspace&" "db=woodchuck_db&" "warehouse=woodchuck_query_wh&" "schema=public"))

…

On Aug 22, 2021, at 1:55 PM, Daniel Benedict ***@***.***> wrote: What config did you use to get connected to Snowflake? I've been trying, but getting a timeout. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#152 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHE6ASYBNJRSUVTBTZNLN3T6FP35ANCNFSM43O7HKOA>. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email>.

[cfclrk]

Just to document another example, here's the config that ended up working for me:

(ejc-create-connection
 "snowflake"
 :dependencies [[net.snowflake/snowflake-jdbc "3.13.27"]
                [net.java.dev.jna/jna "5.13.0"]]
 :connection-uri (concat "jdbc:snowflake://my-db.snowflakecomputing.com:443"
                         "?user="my-email@myjob.com"
                         "&warehouse=my-warehouse"
                         "&role=my-role"
                         "&db=my-db"
                         "&authenticator=externalbrowser"
                         "&JDBC_QUERY_RESULT_FORMAT=JSON"))

The authenticator=externalbrowser is because we use SSO for authentication, so you may need a different value for this option (refer to snowflake's JDBC Driver Connection Parameter Reference).

The JDBC_QUERY_RESULT_FORMAT=JSON was really important for me to set. Without it, I kept getting the error:

Error: JDBC driver internal error: exception creating result java.lang.ExceptionInInitializerError at net.snowflake.client.jdbc.internal.apache.arrow.memory.UnsafeAllocationManager.<clinit>(UnsafeAllocationManager.java:27).

I'm able to execute SQL queries and a couple of the interactive ejc commands, like ejc-describe-table.

The setup isn't perfect yet, as I'm getting an error running M-x show-views-list or M-x show-tables-list (and probably other things... today is my first day with ejc-sql):

Error: SQL compilation error:
syntax error line 1 at position 0 unexpected 'nil'.

[kostafey]

@mbarton98, @cfclrk, I've added Snowflake connection example to README, from the example you provided.
If you want to better completions for tables, rows and other database objects, as well as views- and tables- lists, please provide a related queries. You can do it here in the comments or feel free to create pull request.

Here is an example for Oracle database: https://github.com/kostafey/ejc-sql/blob/master/src/ejc_sql/structure.clj#L119

[Lenbok]

I've managed to use this information to connect to snowflake. However every time I execute a query, it is doing the externalbrowser authentication - is a new connection being established for every query? (I would expect that the connection would be kept open, so the authentication would only happen when making making the initial ejc-connect)

[mbarton98]

This is what I used to configure connection caching with JDBC. https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use#label-browser-based-sso-connection-caching

…

On May 2, 2023, at 6:40 PM, Len Trigg ***@***.***> wrote: I've managed to use this information to connect to snowflake. However every time I execute a query, it is doing the externalbrowser authentication - is a new connection being established for every query? (I would expect that the connection would be kept open, so the authentication would only happen when making making the initial ejc-connect) — Reply to this email directly, view it on GitHub <#152 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHE6AV63JFWQJFNQO37T33XEGZQ3ANCNFSM43O7HKOA>. You are receiving this because you were mentioned.

[Lenbok]

I got our admin to enable that, but it turns out that credential caching is not supported on linux so I'm out of luck. Which leaves my other question of why the connection isn't kept open, removing the need to re-connect for every query.