Oracle-Snowflake Connector from Snowflake Partner dataconsulting.pl
Amongst my many highly popular blog posts (š) some of the most popular are those that talk about connecting Oracle to Snowflake. Unfortunately, there is no Oracle Transparent Gateway for Snowflake and the ODBC connector is not very robust, however Snowflake partner Data Consulting have developed a connector which leverages Oracleās UTL_HTTP and/or APEX_WEB_SERVICE package that allows you to connect from SQL or PL/SQL to web based services. When using this connector with Snowflake it looks like requests are coming directly from the Snowflake UI.
Why connect Oracle to Snowflake?
Before we dive into the how we should probably consider the why. There are at least three primary use cases for connecting Oracle to Snowflake:
Data Sharing (Publish/Subscribe): Connect Oracle to Snowflake Data Shares published either on the Snowflake Data Marketplace or private shares and/or use Snowflake Data Cloud for sharing data between Oracle databases.
The oracle-snowflake connector can allow Oracle customers to subscribe to and publish data on the Snowflake Data Cloud. Even if you only use Snowflake for data sharing between Oracle databases, there is immense value in leveraging the Snowflake Data Grid to make access to shareable data sets much easier.
HTAP (OLTP/OLAP): Split workload between Oracle (OLTP) and Snowflake (OLAP) and use the two database engines in a Hybrid Transactional / Analytic Processing architecture where Oracle takes care of new (volatile) OLTP data and Snowflake handles the long tail of older OLAP data.
With HTAP Oracle is performing all the transactional (or analytical) ingest and transformation and Snowflake is used as an efficient and economical method to handle the larger volume of older data. The connector means that all this data can be accessed directly from Oracle. Clearly all data that is offloaded to Snowflake is directly accessible to Snowflake client tools as well.
Offload (OLAP/OLAP): Offload data and workload from Oracle (OLAP) to Snowflake (OLAP) and use the two database engines in an Analytic Processing architecture where Oracle takes care of new hot /OLAP data and Snowflake handles the long tail of older OLAP data.
Offload allows you to reduce the burden of workload on Oracle and can handle peaks much more efficiently.
The dataconsulting.pl Oracle-Snowflake Connector
From the publisherās web site.
- provides provides SQL and PL/SQL API to connect to Snowflake directly from Oracle
- allows to run any valid Snowflake SQL query against Snowflake data directly from Oracle
- enable processing of Snowflake queries results using Oracle SQL and PL/SQL
- allows to run DML statements against Snowflake tables directly from Oracle (from within SQL as well as PL/SQL)
- allows to run single-row and bulk inserts into Snowflake tables directly from Oracle
- allows to run Snowflake stored procedures from Oracle
- allows to run DDL in Snowflake from Oracle
- allows to run TCL in Snowflake from Oracle
- supports both synchronous and asynchronous Snowflake command execution from Oracle
- monitor asynchronous Snowflake queries statuses and fetch its results from Oracle
Using the dataconsulting.pl Oracle-Snowflake connector
The Oracle-Snowflake connector is a PL/SQL library which you can download from the dataconsulting.pl website and is easy to install, configure and use. There is no middle tier component, the Oracle Database becomes a client to Snowflake.
Connecting to Snowflake from Oracle
SQL> exec datacons_sfconnector.login('some_account.region.cloud', 'some_login', 'some_password', 'file:/home/oracle/some_wallet');
Configuring Snowflake connection
SQL> exec datacons_sfconnector.execute('use database my_db);
SQL> exec datacons_sfconnector.execute('use warehouse my_wh');
SQL> exec datacons_sfconnector.execute('use schema my_schema);Running a query (with binds)
SELECT
column0 AS o_orderkey
, column1 AS o_orderstatus
, column2 AS o_totalprice
, column3 AS o_comment
FROM table(datacons_sfconnector.query_o1(
pi_query => q'[SELECT
o_orderkey
, o_orderstatus
, o_totalprice
, o_comment
FROM orders o
WHERE o_orderdate BETWEEN to_date(?,'YYYY-MM-DD'
AND to_date(?,'YYYY-MM-DD')]'
, pi_binds => json_key_list('1998ā01ā01','1998ā12ā31')));Offloading Oracle Data Warehouse workload to Snowflake
I use Oracleās Sales History (SH) schema with an expanded dataset for running tests as there are loads of example SH queries published in Oracle documentation.
Offloading Data from Oracle to Snowflake
In Oracle most data warehouses use table partitioning which provides a convenient mechanism for choosing data for offloading, in particular if the partition key is date or timestamp based which it often is.
When offloading you can offload the entire partition for the fact table(s) to the corresponding Snowflake table and also offload all dimension tables related to the fact table(s). This consistent synchronisation will allow for entire queries including JOIN operations to be pushed down to Snowflake. When querying data hosted remotely it is essential to reduce the result set returned. By pushing as much of the query as possible to Snowflake you make the most of offloading the processing to Snowflake and will significantly reduce the number of rows that have to be transmitted over the network be processed by Oracle on receipt.
For offoading/synchornising Oracle tables and table partitions to Snowflake I used a PL/SQL stored procedure which used the dataconsulting.pl connector to perform bulk inserts. Since the row limit for a multi-row insert in Snowflake is 16,384 rows, the procedure performs multiple batch inserts if required. For very high data volumes it would be better to use an ELT tool.
Example bulk insert offload code below:
declare
C_INS_BATCH_SIZE_ROWS constant number := 16384; /* max INSERT count for Snowflake */
v_el clob;
cursor cur_2 is
select
' ["'||prod_id||'"
, "'||cust_id||'"
, "'||to_char(time_id, 'YYYY-MM-DD')||'"
, "'||channel_id||'"
, "'||promo_id||'"
, "'||quantity_sold||'"
, "'||seller||'"
, "'||fulfillment_center||'"
, "'||courier_org||'"
, "'||tax_country||'"
, "'||tax_region||'"
, "'||amount_sold||'"]'
as row_line
from SALES partition ( sales_2022_M1 ); /* single partition */
v_row varchar2(32767);
v_ct number := 0;
procedure ins(pio_el in out nocopy clob)
as
v_binds json_array_t;
begin
v_binds := json_array_t('['||pio_el||']');
pio_el := null;
datacons_sfconnector.execute(
pi_stmt => 'INSERT INTO SALES (PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
QUANTITY_SOLD, SELLER, FULFILLMENT_CENTER, COURIER_ORG,
TAX_COUNTRY, TAX_REGION, AMOUNT_SOLD)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?)',
pi_binds => v_binds);
end ins;
begin
datacons_sfconnector.execute(pi_stmt => 'begin transaction;');
open cur_2;
loop
fetch cur_2 into v_row;
if cur_2%notfound then
if v_el is not null then
ins(v_el);
end if;
exit;
end if;
v_el := v_el ||
case
when v_el is null then v_row
else ','|| v_row
end;
if mod(cur_2%rowcount,C_INS_BATCH_SIZE_ROWS) = 0 then
ins(v_el);
end if;
end loop;
close cur_2;
datacons_sfconnector.execute(pi_stmt => 'commit;');
end;
/Querying Offloaded Data
I used the following pattern to query SH data split between Oracle and Snowflake. We are essentially issuing two separate (fetch, filter and aggregate) queries, one to each database engine and using Oracle to finally post process the results.
WITH orcl AS
( /* Oracle query */ )
, snow AS
( /* Snowflake query using dataconsulting.pl connector */)
, olap AS
( SELECT o.* FROM orcl o UNION SELECT s.* FROM snow s )
/* apply any post processing or window functions in this final step */
SELECT * FROM olap;Here is how the data is split between Oracle and Snowflake
DB CALENDAR_YEAR RC
---- ------------ ---------
orcl 2022 1011720
snow 2021 9486787
snow 2020 9733831
snow 2019 9522726Here is an example query below where I have pushed the Oracle and Snowflake queries into database views to make the code more compact:
WITH orcl AS
( SELECT * from BestWorstYears_orcl )
, snow AS
( SELECT * from BestWorstYears_snow )
, olap AS
( SELECT o.* FROM orcl o UNION SELECT s.* FROM snow s )
SELECT country
, product
, year
, SUM(sales) AS sales
, SUM(rc) AS rc
, MAX(year) KEEP(DENSE_RANK FIRST ORDER BY SUM(sales) DESC)
OVER (PARTITION BY country, product) best_year
, MAX(year) KEEP(DENSE_RANK LAST ORDER BY SUM(sales) DESC)
OVER (PARTITION BY country, product) worst_year
FROM olap
GROUP BY
country
, product
, year
ORDER BY
country
, product
, year
;Below is the Snowflake query as part of a view definition using the connector, note all columns are returned as STRINGs by default so we cast them to native Oracle data types.
create or replace view BestWorstYears_snow
AS
select cast(column0 as varchar2(40)) AS country
, cast(column1 as varchar2(50)) AS product
, cast(column2 as number) AS year
, cast(column3 as number) AS sales
, cast(column4 as number) AS rc
from table(datacons_sfconnector.query_o1(
pi_query => q'[
SELECT
/* BestWorstYears */
country_name AS country,
prod_name AS product,
calendar_year AS year,
SUM(amount_sold) AS sales,
COUNT(amount_sold) AS rc
FROM
sales_all s,
times t,
customers cu,
countries co,
products pr
WHERE
s.time_id = t.time_id
AND s.prod_id = pr.prod_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
GROUP BY
country_name,
prod_name,
calendar_year
]'));Itās not very transparent
Correct, there is some work involved in constructing the query, mapping the data types and taking care of some aggregations in the final SELECT. However as a DBA how often do you get the chance to write a little code which can make such a huge difference?
Summary
The dataconsulting.pl Oracle-Snowflake connector is a simple and efficient way to unleash a bunch of use cases allowing Oracle to leverage Snowflake, for data sharing, HTAP and OLAP workload offloading. Give it a try, you can read more and download the bundle from https://datacons.co.uk/oracle-snowflake-connector-en/.
Donāt have a Snowflake account? Sign up for a 30 day free trial at https://signup.snowflake.com/.
Need to get up to speed with Snowflake in a hurry, check out the guides on https://quickstarts.snowflake.com/ in particular the āGetting Started with Snowflake ā Zero to Snowflakeā quickstart. As someone familiar with Oracle you will pick up Snowflake very easily.
