Accessing Snowflake from SQL Developer

blank

My first day of work this year was a training day. As a participant in a “Snowflake Fundamentals” training course. I opted for the four-day, multi-week option so that I would have time to better absorb what I had just learned. Tomorrow is my third day and I plan to write more about Snowflake once I complete this training.

The Problem

As a long-time Oracle SQL Developer user, I tried to connect to Snowflake via SQL Developer. SQL Developer supports the following database systems via third-party JDBC drivers:

  • TimesTen
  • Amazon Redshift
  • Cloud
  • DB2
  • Hive
  • JDBC
  • MongoDB
  • MySQL
  • PostgreSQL
  • SQLServer
  • Sybase
  • Teradata

The generic “JDBC” variant sounds promising. Why is this option not shown when creating a new connection? Because this driver requires the JDBC-ODBC bridge (as does the Microsoft ACCESS driver, by the way, which is not available in non-Windows environments). SQL Developer requires JDK 8 since version 4.1. And JDK 8 does not include the JDBC-ODBC-Bridge anymore.

But wait. In SQL Developer Data Modeler (SDDM) there is a generic JDBC driver that can connect to any database system. Kent Graziano described in this blog post how to configure it for Snowflake. And Federico Sicilia explained in this blog post how to deal with Snowflake specific data types. However, SDDM accesses the database exclusively via JDBC’s DatabaseMetaData interface. That’s why a generic JDBC driver is applicable in SDDM. On the other side, SQL Developer uses mainly SQL statements, and as a result the generic JDBC driver used in SDDM is not sufficient for the use in SQL Developer. Of course, Oracle could implement the support of such a driver, but since the access to third party database systems is provided in the context of data migrations only, this has not a high priority.

Briefly: no generic JDBC driver, no support for Snowflake’s JDBC driver in SQL Developer.

Options?

What are the alternatives? Use other tools such as Snowflake’s web UI worksheets, the CLI snowsql or a third party IDE that supports Snowflake. For example DBeaver or JetBrain’s DataGrip. These options work well and are recommended.

However, if you still want to access Snowflake from SQL Developer then I see basically two options:

  1. Write an extension that provides an additional connect panel (combobox entry) in SQL Developer
  2. Write a JDBC proxy that acts like a supported driver, e.g. MySQL

The first option is the most user-friendly one. In theory. In practice it will be difficult to make it work, because third party extensions need a UI action (e.g. own button, own menu item) to initialize the load of the extension. At least for the very first time. Once it is loaded it is cached. This makes it not that user friendly anymore, because there is no additional action the user has to trigger. I dealt with bugs in this area in other SQL Developer extensions. So I know what I’m talking about. Unless you want to introduce a dummy action, this approach is a dead end.

The second option sounds easy. SQL Developer allows to add third party JDBC drivers. So let’s do that.

The Solution

As almost always, it was more work than anticipated. In the end I have successfully implemented a JDBC proxy which is mimicking a MySQL driver and delegates requests to a configurable target JDBC driver. The target JDBC driver can be Snowflake, PostgreSQL, SQLite, H2 or MySQL. Adding more database systems should not be that difficult, as long as the JDBC driver is available on Maven Central.

I released this driver as an OpenSource project. The README.md on GitHub explains how it works and how to install it. Hence I’m not going to repeat that in this blog post. You can download this driver from here.

An Example

I like to use the tables DEPT and EMP to demonstrate things. Everyone in the Oracle field knows them. And therefore no lengthy or distracting introduction is necessary. Let’s create these tables in Snowflake:

The result in SQL Developer looks as follows:

blank

SQL Developer does not understand this multi-row INSERT statement. That’s why you see this pink wavy line on line 8. Nevertheless SQL Developer can execute these statements. That’s excellent.

Now, let’s show the newly created tables in the Connections window and some details for table DEPT. I like SQL Developer’s integration of SDDM and the ability to create an ad-hoc model. Here it is:

blank From my point of view there is no reason to avoid integrity constraints. Even if they are not enforced by the database system, they still help the user to better understand the model. In this model you see that MGR is a foreign key column and it is optional. That’s nice.

Summary

The implementation of a JDBC proxy driver for accessing Snowflake from SQL Developer started as an experiment. The result works amazingly well. As a side effect, I can now access my SQLite and H2 databases from SQL Developer as well. Other IDEs, however, offer more database-specific features. Anyway, the ability to access multiple database systems from SQL Developer has some value. At least for me.

What do you think of it? Is this useful or just another unnecessary feature? Please post your thoughts below. Thanks.

4 Comments

  1. blank Gary says:

    Cool addition. Suspect that Oracle will try to lock it down in a future release of SQL Developer so that they can gatekeep what they connect to.

    Once had a look to see whether there was a trivial way of querying Cassandra but it didn’t seem doable and we got licences for RazorSQL instead.  Squirrel SQL Client is an option if it is necessary to avoid a paid product.

    • Thank you, Gary.

      SQL Developer provides access to third party database systems for data migration to Oracle databases only. In fact, I was able to migrate Snowflake tables to my ATP instance in the Oracle cloud using the “Copy to Oracle…” menu option. So why would Oracle try to block this? ;-)

      The features provided by SQL Developer for these third party databases systems are quite limited (by purpose I guess). It’s a bit better for MySQL than for PostgreSQL, that’s why I decided to mimic the MySQL driver. For serious development it’s for sure a good idea to consider other tools with a broader generic JDBC related feature set and better support for database specific features.

      Regarding Cassandra. There are some JDBC drivers around, so it should be technically possible to proxy such a driver as well.

  2. Bravo, Philipp

    This is a really cool solution if you need to connect Snowflake from SQL Developer. Just brilliant work is done to write this proxy driver. I am enjoying your work. Moreover, it also works just fine with  authenticator=externalBrowser 

    Thanks

     

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.