Connect Oracle to the Snowflake Data Cloud

The Snowflake Data Cloud provides instant access to a substantial and growing library of valuable datasets. This includes second party datasets such as data from partners, suppliers and customers as well as third party datasets including industry datasets, government, geographic and environment datasets. The value of these datasets is the ability for you to enrich your analytics and data science models with a far wider range of correlated data.

As an Oracle user you may feel a little left out, wouldn’t it be great if you could tap into those datasets directly from your Oracle database and applications. Well you can.

Snowflake has an ODBC driver and Oracle supports heterogeneous services to allow data in a non-Oracle database to be queried using SQL. For connecting to Snowflake we can use the Oracle Database Gateway for ODBC. I have included instructions on how I configured ODBC in the Appendix.

Once you have configured SQLNet with an ODBC driver you can create a database link. In this example I have pre-created a Snowflake user called ‘oracle’ who has their own database and virtual warehouse.

SQL> create public database link sno connect to "oracle" identified by "***********" using 'SNO';

Now we can query Snowflake tables and views directly from Oracle using the database link.

On Snowflake I created a view to expose JSON weather data in an easily digestible form. This data was loaded directly into Snowflake but could easily be one of the shared Weather datasets.

create or replace view json_weather_vw as
      select
         date_trunc(hour, t)                    observation_time
        ,v:city.name::varchar(64)               city_name
        ,avg(utils.degKtoC(v:main.temp::float)) temp_avg_c
        ,avg(utils.degKtoF(v:main.temp::float)) temp_avg_f
        ,avg(v:wind.deg::float)                 wind_dir
        ,avg(v:wind.speed::float)               wind_speed
        from citibike.demo.weather
       group by 1,2;

Below are a couple of example queries using filters on the view we created above.

SQL> select count(1)
       from json_weather_vw@sno
      where city_name = 'San Francisco';
SQL> select *
       from json_weather_vw@sno
      where city_name = 'San Francisco'
        and observation_time > '01-JAN-2020';

Queries with filters will push down those filters to Snowflake which is very efficient, letting Snowflake do all the hard work and returning only the matching rows.

However operations like GROUP BY, ORDER BY, FIRST ROWs, or aggregations on the SELECT list columns will be processed at the Oracle end. If you have complex logic that you want to push down to Snowflake you may want to create a Snowflake view and query the view via the database link.

In most cases you will want to correlate data hosted in Snowflake with data held in Oracle. I have a copy of the Citibike trips table in Oracle called orc_trips. We can now join Oracle hosted data with Snowflake hosted data as follows:

SQL> select t.*, w.*
       from orc_trips t,
            json_weather_vw@sno w
      where w.city_name = 'San Francisco'
        and t.starttime = w.observation_time;

The functionality supported by generic connectivity (ODBC) is typically more limited than that possible when using vendor specific gateways, however the ODBC gateway, if used with care, can provide an effective and performant method to tap into the Snowflake Data Cloud shared datasets.

In an upcoming article I will document how this is much easier and seamless using Snowflake technology partner Gluent’s transparent data connector for Snowflake.

Thanks for reading.

APPENDIX — Configuring Snowflake ODBC on Oracle 19c

For my Oracle client environment I used the Oracle DB Developer VirtualBox VM which contains a full installation of Oracle 19c, plus utilities such as SQL Developer.

1. Install Linux ODBC driver, also installs installs odbcinst, isql

% sudo yum install unixODBC.x86_64
Installed:
unixODBC.x86_64 0:2.3.1-14.0.1.el7
Complete!

2. Check odbcinst and isql have been installed

% which odbcinst
/usr/bin/odbcinst
% which isql
/usr/bin/isql
% odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

3. Download and install latest snowflake ODBC driver from ( sfc-repo.snowflakecomputing.com )

% sudo yum install $HOME/Downloads/snowflake-odbc-2.23.0.x86_64.rpm
Installed:
snowflake-odbc.x86_64 0:2.23.0-1

4. Configure ODBC Driver

Something like the following.

% cat /etc/odbc.ini
[ODBC Data Sources]
sno = SnowflakeDSIIDriver
[sno]
Driver = /usr/lib64/snowflake/odbc/lib/libSnowflake.so
Description =
server = <full Snowflake URL>
account = <snowflake account>.<deploymemnt>
role=<snowflake role>
trace=off
database=oracle
schema = public
warehouse=oracle_wh
Locale=en-US
port=443
SSL=on

5. Test ODBC driver

% isql -v sno oracle <password>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(1) from catalog_returns;
+---------------------+
| COUNT(1)            |
+---------------------+
| 1440033112          |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit

6. Configure Oracle SQLNet

Create a file like the following in $ORACLE_HOME/hs/admin.

% cat initSNO.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = "SNO"
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.2.0.0=
HS_NLS_NCHAR=UCS2
#
# ODBC specific environment variables
#
set ODBCINI= /etc/odbc.ini
set LD_LIBRARY_PATH=/usr/lib64
#
# Environment variables required for the non-Oracle system
#
set HOME=/u01/app/oracle/product/version/db_1

Add entry to listener.ora in $ORACLE_HOME/network/admin

% pwd
/u01/app/oracle/product/version/db_1/network/admin
% cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
     (GLOBAL_DBNAME = orclcdb)
     (SID_NAME = orclcdb)
     (ORACLE_HOME = /u01/app/oracle/product/version/db_1)
  )
  (SID_DESC=
    (SID_NAME=SNO)
  (ENVS="LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/snowflake/odbc/lib:/u01/app/oracle/product/version/db_1/hs/lib:/u01/app/oracle/product/version/db_1/lib")
    (ORACLE_HOME = /u01/app/oracle/product/version/db_1
    (PROGRAM=dg4odbc)
  )
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )
#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orclcdb)

Add entry to tnsnames.ora like

% cat tnsnames.ora
ORCLCDB=localhost:1521/orclcdb
ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
SNO =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=SNO))
    (HS=OK)
  )

7. Test

% tnsping SNO
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-MAR-2021 14:09:58
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/version/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=SNO)) (HS=OK))
OK (0 msec)