Tech Tip: Connect to Snowflake db using #SQLDevModeler
So, some of you may have noticed that I took “real” job this week. I am now the Senior Technical Evangelist for a cool startup company called Snowflake Computing.
Basically we provide a data warehouse database as a service in the cloud.
Pretty cool stuff. (If you want to know more, check out our site at snowflake.net)
I will talk more about the coolness of Snowflake (pun intended) in the future, but for now I just want to show you how easy it is to connect to.
Of course the first thing I want to do when I meet a new database is see if I can connect my most favorite data modeling tool, Oracle SQL Developer Data Modeler (SDDM), to it and reverse engineer some tables.
The folks here told me that tools like Informatica, MicroStrategy, and Tableau connect just fine using either JDBC or ODBC, and that since we are ANSI SQL compliant, there should be no problem.
And they were right. It was almost as easy as connecting to Oracle but it was WAY easier than connecting to SQL Server.
First you need a login to a Snowflake database. No problem here. Since I am an employee, I do get a login. Check.
We have both a web-UI and a desktop command line tool. Turned out I needed the command line tool which incidentally needed our Snowflake JDBC connector to work. Followed the Snowflake documentation, downloaded the JDBC drive (to my new Mac!). Piece of cake.
So connecting from SDDM is really easy. First add the 3rd party JDBC driver in preferences. Preferences ->Data Modeler -> Third Party JDBC Driver (press the green + sign, then browse to the driver).
As you can see our JDBC driver is conveniently named snowflake_jdbc.jar.
Next step is to configure the database connection. To do this you go to File -> Import -> Data Dictionary, then add a new connection in the wizard.
Give at a name and login information, then go to the JDBC tab.
So getting the URL was the trick (for me anyway). Luckily the command line tool displayed the URL when I launched it in a terminal window, so I just copied it from there (totally wild guess on my part).
So the URL (for future reference) is:
jdbc:snowflake://sfcsandbox.snowflakecomputing.com:443/?account=<service name>&user=<account>&ssl=on
Where account is whatever you named your account in Snowflake (once you have one of your very own that is).
The driver class was a little trickier – I had to read our documentation! Thankfully it is very good and has an entire section on how to connect using JDBC. In there I found the drive class name:
com.snowflake.client.jdbc.SnowflakeDriver
That was it.
I pushed the Test button and success!
Now to really test it, I did the typical reverse engineer and was able to see the demo schema and tables and brought them all in.
Demo schema in Snowflake (no, not a snowflake schema!)
So I call that a win.
Not a bad weeks work really:
- New job orientation
- Start learning a new tech and the “cloud”
- Got logged in
- Installed SDDM on a Mac for the 1st time ever!
- Configured to speak to an “alien” database
- Successfully reverse engineer a schema
- Blog about it.
So that was my 1st week a a Senior Technical Evangelist.
TGIF!
Kent
still, The Data Warrior
P.S. If you want to see more about my week, just check my twitter stream and start following @SnowflakeDB too.
The Data Warrior 
Pingback: How to Connect to Snowflake with JDBC | Snowflake
Pingback: Sql Developer Data Modeler to Support Snowflake VARIANT
Pingback: 3 Reasons to Include RI Constraints in Data Mart | Snowflake
Pingback: Top 10 Cool Things I Like About Snowflake - Snowflake
Pingback: Top 10 Cool Things I Like About Snowflake | Snowflake Blog
Pingback: Oracle SQL Developer Data Modeler with Snowflake - Sonra
Hi Kent, I’m trying to do this with SDDM v.19.1 and JDBC driver v.3.8.5 but the dialogs are different and I keep getting “incorrect user/password error”.
Any ideas what’s wrong ?
Have not tried it yet but just downloaded the new version and looked and they did move the location for adding the drivers. You need to go to Tools->Preferences->Data Modeler->Third Party JDBC Drivers to add in our driver.
Using in the add database connection wizard change the Database Type to JDBC then add the the username, password, JDBC URL and driver class.
If you did that, the only other thing would be to check to see that you can log into your Snowflake account, using our native web UI, with the user name and password you entered in Data Modeler. Having that wrong is the only real reason I would expect to see that error. (Anything else would say something like invalid driver class or URL can’t be found etc…)
I was able to connect to our SF using:
JDBC URL= jdbc:snowflake://.us-east-1.snowflakecomputing.com:443/?account=&db=OUR_SANDBOX&role=OUR_SANDBOX_DB_DEVELOPER_ROLE&ssl=on
– Data Modeler > Tools > Preferences > Web Browser and Proxy > Proxy Settings > Manual Proxy Setings:
Host=
Port=8000
No Proxy= No need to change (fyi default is: localhost|localhost.localdomain|127.0.0.1|::1|myvm.xxxxxx.com|myvm|myvmip)
click [Text proxy]
– Data Modeler > Tools > Preferences > Data Modeler > Third Party JDBC Drivers:
[+] snowflake-jdbc-3.8.7.jar
click [OK]
Kent , I guess this shall also work using Data modeler version inside Sql Developer ?
It may not as SQL Developer does not let you add the JDBC connector last I tried. SQL Developer only supports connections to databases that the migration utility works with.
Kent, I just tried to add VARIANT in SDDM 19.1 and doesnt work, data type on the table column shows UNKNOWN 😦
sadly not working on data modeler 19.1
1. did all setup from your video on SDDM 19.1 (using oracle 12c, despite theres an oracle 12cR2 to stay inline with your video).
2. reverse engineering 1 table from Snowflake table with a VARIANT column.
3. column appeared as VARCHAR2 (as expected).
4. now i changed column from Source type=VARCHAR into VARIANT –> Apply –> BUT… data type changed into UNKNOWN (and not VARIANT as expected from your video).
also adding a new table > new column > selecting Source Type=VARIANT will set Data Type=UNKNOWN
Bummer. I will have to take another look to see if I can find a work around. It might be worth posting on the Data Modeler forum on OTN. Since you can still define the custom data type and see it and select it, the fact that it shows UNKNOWN seems like a bug.
Did you try generating the DDL to see if it outputs the right code?
Yeah, ddl comes with unknown and comment could not generate proper ddl.
Pingback: Accessing Snowflake from SQL Developer - Philipp Salvisberg's Blog
Pingback: Oracle SQL Developer Data Modeler with Snowflake - Sonra
Pingback: SQL Developer Data Modeler pour prendre en charge Snowflake VARIANT - Blog Development Source
Pingback: Comment se connecter à Snowflake avec JDBC - Blog Development Source
Pingback: How to Connect to Snowflake with JDBC