Snowflake is currently one of the best Cloud Data Warehouses on the market. But it’s good to know that Snowflake is ‘just’ a Data Warehouse. Therefore you will need an additional front-end tool to visualize the Data. In this post we will have a look at connecting Oracle Data Visualization to Snowflake.
Setting up Snowflake
To be able to connect to Snowflake from any third party tool you will need to specify the connection details. If you look at the connection screen from Oracle Data Visualization (DVD) you’ll see there are a few things we need.
Some of the specifications are in the DVD-help. Looking at the screen at the right you will notice a few things important on the Snowflake side. First important thing is the Snowflake Hostname. The hostname consists of an account name and (depending on the region) a region combined with; “snowflakecomputing.com”. Currently I have an account in Azure, West-Europe; “https://xyz12345.west-europe.azure.snowflakecomputing.com". More details are in the documentation.
Furthermore you will need the user credentials. Because of the separation of storage and compute in Snowflake you have to specify the database and the virtual warehouse. I created the following objects in Snowflake; a role, a virtual warehouse and a user. I will use the SNOWFLAKE_SAMPLE_DATA sample database to connect to.
Everything on the Snowflake side should be in place to connect and query Snowflake from Oracle DVD. There is an Oracle Analytics User (OA_USER) and a role (OA_ROLE). This role has access to a virtual warehouse (ORACLE_DV_WH).
As you can see in the above picture I am able to query the SNOWFLAKE_SAMPLE_DATA-database. Querying the CUSTOMER-table from the TPCH_SF1-schema yields 150000 rows. Something similar should be achieved from Oracle DVD. Before being able to create the actual connection, you need a to install a JDBC Driver in Oracle DVD. If you forget the connection you will be presented with an error message.
Setting up Oracle Data Visualization
The JDBC Driver can be downloaded via the Snowflake WebUI. It will direct you to the Maven Repository where you can download the appropriate version for your situation
In my case I had to place the jar in the Mac version of Oracle Data Visualization (which I downloaded here) in the application directory of DVD; /Applicationsdvdesktop.app/Contents/Resources/app.nw/lib.
Now everything is set to actually create the connection and fill in the details of the Snowflake Account.
Hostname — “https://xyz12345.west-europe.azure.snowflakecomputing.com"
Username — OA_USER
Password — **********
Database — SNOWFLAKE_SAMPLE_DATA
Warehouse — ORACLE_DV_WH
Create Data Set
Now that the connection to the Snowflake Data Warehouse has been created, it’s time for the creation of a data set. In this case I make a small data set based on the CUSTOMER-table. Just to see whether I can select the same data.
A preview of the CUSTOMER-table yields the following data (see picture below). It seems like Oracle Data Visualization is able to query the Snowflake Cloud Datawarehouse.
Via the ‘History’-button in the menu bar of the Snowflake WebUI, you retrieve the queries fired to the various Snowflake databases fired from the various virtual warehouses.
To sum up, the above showed how easy connecting Oracle Data Visualization to Snowflake is. As from this point onwards you can start visualizing Snowflake data in Oracle DVD.
Thanks for reading.
Originally published at https://daanalytics.nl on June 17, 2019.