3

Accessing Snowflake from SQL Developer

 3 years ago
source link: https://www.salvis.com/blog/2021/01/17/accessing-snowflake-from-sql-developer/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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
  • 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:

Create tables DEPT/EMP
CREATE TABLE dept (
   deptno   NUMERIC(2)   CONSTRAINT pk_dept PRIMARY KEY,
   dname    VARCHAR(14)  NOT NULL,
   loc      VARCHAR(13)  NOT NULL
INSERT INTO dept VALUES
   (10, 'ACCOUNTING', 'NEW YORK'),
   (20, 'RESEARCH',   'DALLAS'),
   (30, 'SALES',      'CHICAGO'),
   (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE emp (
   empno    NUMERIC(4)     CONSTRAINT pk_emp PRIMARY KEY,
   ename    VARCHAR(10)    NOT NULL,
   job      VARCHAR(9)     NOT NULL,
   mgr      NUMERIC(4),
   hiredate DATE           NOT NULL,
   sal      NUMERIC(7,2)   NOT NULL,
   comm     NUMERIC(7,2),
   deptno   NUMERIC(2)     CONSTRAINT fk_deptno REFERENCES dept,
   CONSTRAINT fk_mgr FOREIGN KEY (mgr) REFERENCES emp
INSERT INTO emp VALUES
   (7839, 'KING',   'PRESIDENT', NULL, DATE '1981-11-17', 5000, NULL, 10),
   (7698, 'BLAKE',  'MANAGER',   7839, DATE '1981-05-01', 2850, NULL, 30),
   (7499, 'ALLEN',  'SALESMAN',  7698, DATE '1981-02-20', 1600, 300,  30),
   (7900, 'JAMES',  'CLERK',     7698, DATE '1981-12-03', 950,  NULL, 30),
   (7654, 'MARTIN', 'SALESMAN',  7698, DATE '1981-09-28', 1250, 1400, 30),
   (7844, 'TURNER', 'SALESMAN',  7698, DATE '1981-09-08', 1500, 0,    30),
   (7521, 'WARD',   'SALESMAN',  7698, DATE '1981-02-22', 1250, 500,  30),
   (7782, 'CLARK',  'MANAGER',   7839, DATE '1981-06-09', 2450, NULL, 10),
   (7934, 'MILLER', 'CLERK',     7782, DATE '1982-01-23', 1300, NULL, 10),
   (7566, 'JONES',  'MANAGER',   7839, DATE '1981-04-02', 2975, NULL, 20),
   (7902, 'FORD',   'ANALYST',   7566, DATE '1981-12-03', 3000, NULL, 20),
   (7369, 'SMITH',  'CLERK',     7902, DATE '1980-12-17', 800,  NULL, 20),
   (7788, 'SCOTT',  'ANALYST',   7566, DATE '1987-04-19', 3000, NULL, 20),
   (7876, 'ADAMS',  'CLERK',     7788, DATE '1987-05-23', 1100, NULL, 20);

The result in SQL Developer looks as follows:

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:

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK