Add Snowflake support

[janblom]

Is your feature request related to a problem? Please describe.
Snowflake is gaining traction as a data store, and the OHDSI tools "downstream" support Snowflake.

Describe the solution you'd like
WhiteRabbit should be able to access and scan tables in a Snowflake warehouse.

Describe alternatives you've considered
The option to add Snowflake support based on SqlRender was considered and discussed with @schuemie , but this is not a feasible option.

Additional context
The initial implementation will be experimental, but should not affect other databases.

[heschmidt04]

I'm interested in this as well (from MGB CHoRUS Bridge2AI) -- MGB has moved its data warehouse to Snowflake. Is there anyone who has successfully made a scan of a Snowflake Database?

[janblom]

@heschmidt04
I wrote the basic Snowflake connector for WhiteRabbit some months ago, but so far lacked the opportunity to test it (other than on the sample data that comes with a Snowflake developer account).

Would you be willing to help with a more realistic test scenario?

[heschmidt04]

Sure thing. I just got my sites Snowflake access the last week of Sept and am getting all the OMOP into a workspace. I think I can help with a basic Snowflake setup on a test account if that helps. Feel free to contact me via email about coordinating that. They are using OAUTH and so our Snowflake access is using python modules and a single sign on method that I don't think ODBC can emulate. Open to all options and willing to test. Best regards, Heidi

[heschmidt04]

Hi Jan, Thanks for providing me a release candidate to test. I replied in email with my results and screen shots. The snowflake connectivity test was a success using "external browser" method. Yeay! <3 The generate scan report at the command line fails for me in the RC on Windows 10 VM using Java 1.8 and using v0.10.8 on my M1 Mac with Java 17. I can take that piece offline with you. I wanted to update here to close the communication loop in case other folks were watching this thread for Snowflake updates. Many thanks! Heidi [email protected]

[janblom]

The code for WhiteRabbit 1.0, with Snowflake support is currently under review (Pull Request).
As soon as the PR has been accepted, I will publish the first release candidate for version 1.0.

[janblom]

Hi Heidi, just wanted to share with you that an "official" release candidate for WhiteRabbit 1.0, with Snowflake support, is now available: https://github.com/OHDSI/WhiteRabbit/releases/tag/v1.0.0-RC1 Thanks again for helping me with testing for Snowflake! And should you be able to, a quick smoke test of the release candidate would be highly appreciated :-) Best regards, Jan Blom

…

On Thu, Nov 16, 2023 at 5:58 PM Heidi E. Schmidt ***@***.***> wrote: Hi Jan, Thanks for providing me a release candidate to test. I replied in email with my results and screen shots. The snowflake connectivity test was a success using "external browser" method. Yeay! <3 The generate scan report at the command line fails for me in the RC on Windows 10 VM using Java 1.8 and using v0.10.8 on my M1 Mac with Java 17. I can take that piece offline with you. I wanted to update here to close the communication loop in case other folks were watching this thread for Snowflake updates. Many thanks! Heidi ***@***.*** — Reply to this email directly, view it on GitHub <#366 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABYL2KV6QEDS2ZPZFSVV5V3YEZA4DAVCNFSM6AAAAAAUGMZ7HCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJUHA2TGNJTGI> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[heschmidt04]

Awesome to hear! Thank you again for your time and help. I'll be able to test more towards April so I'll put this as a reminder in my calendar to do so. Best, Heidi

…

On Fri, Mar 1, 2024 at 11:50 AM janblom ***@***.***> wrote: Hi Heidi, just wanted to share with you that an "official" release candidate for WhiteRabbit 1.0, with Snowflake support, is now available: https://github.com/OHDSI/WhiteRabbit/releases/tag/v1.0.0-RC1 Thanks again for helping me with testing for Snowflake! And should you be able to, a quick smoke test of the release candidate would be highly appreciated :-) Best regards, Jan Blom On Thu, Nov 16, 2023 at 5:58 PM Heidi E. Schmidt ***@***.***> wrote: > Hi Jan, Thanks for providing me a release candidate to test. I replied in > email with my results and screen shots. The snowflake connectivity test was > a success using "external browser" method. Yeay! <3 The generate scan > report at the command line fails for me in the RC on Windows 10 VM using > Java 1.8 and using v0.10.8 on my M1 Mac with Java 17. I can take that piece > offline with you. I wanted to update here to close the communication loop > in case other folks were watching this thread for Snowflake updates. Many > thanks! Heidi ***@***.*** > > — > Reply to this email directly, view it on GitHub > <#366 (comment)>, > or unsubscribe > < https://github.com/notifications/unsubscribe-auth/ABYL2KV6QEDS2ZPZFSVV5V3YEZA4DAVCNFSM6AAAAAAUGMZ7HCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJUHA2TGNJTGI> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> > — Reply to this email directly, view it on GitHub <#366 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAYR4GPPAGMKWKT5CDJX6X3YWCWVLAVCNFSM6AAAAAAUGMZ7HCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNZTGUZTCMJXHA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- "Nothing is for itself, but each thing partaking of the other is living its greatest possibility . . ." -Robert Henri

[matteosanchez-dahl]

hi all,

I hope you're doing well. I was running a test of this release of WhiteRabbit 1.0 with Snowflake support by @janblom & encountered an error when testing the connection. has anyone dealt with the following error:

net.snowflake.client.jdbc.SnowflakeConnectString parse
WARNING: Exception thrown while parsing Snowflake connect string. Illegal character in URL.

relatively new to Snowflake, so I would greatly appreciate any guidance or support.

thanks,
Matteo

[heschmidt04]

@matteosanchez-dahl Are you running it with an .ini file or did you run it with the GUI?

We have Okta so I have to use "eternal browser"
I've seen something similar when I didn't put the correct name.privatelink in the .ini file for SNOWFLAKE_ACCOUNT, in tandem with the extra parameter I needed SNOWFLAKE_AUTHENTICATOR and its value externalbrowser

I owe Jan testing so your comment reminded me :)
I was also able to successfully scan a table using the GUI and have it produce a ScanReport.xlsx file. I can run it through only using the .ini file if you need me to test.

I used the following syntax for RC2 v1 - WhiteRabbit_v1.0.0-rc2
./whiteRabbit -ini ~/M1-Snowflake-WhiteRabbit-EDW-enc.ini --add-opens java.base/java.nio=ALL-UNNAMED

[janblom]

Exception thrown while parsing Snowflake connect string

Hi @matteosanchez-dahl ,

could you let me know how (GUI or ini file) and with what parameters (excluding/masking the password of course) you attempt to make a connection?

Thanks,
Jan