Integrating Databricks with Snowflake

Overview: Here is a practical guide to getting started with integrating Databricks with Snowflake.  We will get you started with the basic setup and show how easy it is to get the two of them connected to each other to write and read data from the other.

Pre-Requisites:

1.  You must have a Snowflake Account (the good thing is that this is really easy!)
   – 30 Day free trial, including $400 of credits.  Free Snowflake Account Setup –
2. You need to setup at least a Databricks Community edition (The Community Edition is free)
   – The Databricks Snowflake connector is included in Databricks Runtime 4.2 and above –

https://databricks.com/try-databricks

You should have some basic familiarity with Dataframes, Databricks, and Snowflake.

High level we are really doing these main steps:

1.  Import a notebook that already has a shell of the code you need.
2.  Fill in the details in the notebook for your Snowflake database.
3.  Execute the 3 separate parts of the notebook which will be
   1. making the connection.
   2. writing a dataframe to snowflake
   3. reading a snowflake table back into a dataframe

Steps:

Okay.  Now that you have a Databricks Account setup then login.
I’m going to assume you have the Community Edition so the login is here:  (if you have the regular editions then login to the appropriate area)

https://community.cloud.databricks.com/login.html

Then once you are logged in you should see a screen like this:

Go to Workspace icon (It is the 3rd from the top on the left handside)
Once you click on it then to the right there will be a dropdown arrow to the right of the menu item “Workspace”.  When you click there then click on Import and it should look like this:

Then Choose URL there and put in this notebook link and click the Import Button.
https://docs.databricks.com/_static/notebooks/snowflake-python.html

*This is one of my favorite parts about Databricks on how they make it easy to share Notebooks and be more organized.

Once you have imported the notebook it should look like this:

There are 3 main sections to this sample connector notebook from Databricks:

1.  The Snowflake connection.  You need to fill in all the details in blue.  You should setup the databricks secrets to start.

Then make sure you add the other appropriate details within here (database, schema, warehouse):

*- You will notice on the image there is in the upper right a run button.  If you have worked with Jupyter Notebooks this is very similar.

2.  Write data to a snowflake table.  Fill in the sections in blue.  Mainly just what table you want to write to.  If the table is not created then it will create it for you.  (you can change this part if you want to test more specificially with data you have and create a dataframe from existing data — spark.range(5).write)

3.  Read data to a snowflake table.  Fill in the sections in blue.  Mainly just what table you want to read from which is the one you just created.

That’s it.  This is a very simple example but hopefully it shows you its pretty straightforward to connect Databricks and Snowflake.  Connecting Spark itself outside of Databricks is relatively easy as well but you do need to deal with having the Spark to Snowflake Connector as well as the JDBC Driver setup.  The great part of Databricks 4.2 and higher is that this is already setup for you.

Enjoy!

Find out more about all the benefits Snowflake has to offer you and your business. Sign up for a free proof of concept!

How To Setup Confluent with Snowflake

Overview:  Here is a practical guide to getting started with setting up Confluent with Snowflake

Pre-Requisites:

1.  You must have a Snowflake Account (the good thing is that this is really easy!)
   – 30 Day free trial if you do not have a Snowflake Account yet.  Free Snowflake Account Setup –
2.  This setup requires using Docker.  (I’ll have separate instructions to do this without Docker later)
3.  You also need git.

Here we go – there are really 3 main parts to this setup:

1. Get this docker version of confluent/kafka up and running.
2. Create a topic on it to generate data for moving data into Snowflake
3. Setup the Kafka to Snowflake Connector as the Destination with the right Snowflake connectivity

Part 1 – Get docker version of Confluent/Kafka running.

Let’s go…execute these commands in sequence:

Okay….The first time it will take a few minutes downloading…and you will see output eventually like this:

If you want to verify that everything is up and running then execute this command:

Then you should see some output like this:

You can also see from the Confluent Dashboard/Visualizations of the work you are going to do below.  (sometimes it takes awhile for this to refresh)

http://localhost:9021/

Part 2 – Create a topic to send Data to Snowflake.  Generate data for it with the DataGen functionality.

Let’s go…execute these commands in sequence:

Create a topic:

Generate data for the topic:
Let’s first configure a json file for the data we want to create.
Use whatever editor you want and create this file u.config

I’m using vi u.config and paste this in there:

u.config details

Part 3 – Download the Kafka to Snowflake Connector and configure it.

Okay.  So you have Confluent/Kafka up and running.  You have data generating into a topic.

So now just download the magical Kafka to Snowflake connector here: https://mvnrepository.com/artifact/com.snowflake/snowflake-kafka-connector/0.3.2
I’m sure by the time I publish this the version will change but for now assume it’s this one.

Once you have the file in the same directory we have been using for everything then copy it to the connect virtual machine where it needs to be to work.

You now need to create the configuration file to setup the Connector and the Sink associated with it that connects to the Snowflake Database.  This does assume you have already setup your rsa key.  You do have to fill in 6 of the settings below to have this setup for your specific configuration.Again, use your favorite editor.  I’m using:
vi connector_snowflake.config and entering in my specific details.

connector_snowflake.config details

Okay.  Almost there.  Now use this configuration file to setup the sink.

Now in a few seconds or minutes if you setup everything correctly the topic should be writing to the Snowflake table. Go into the database and schema you connected to and you should be able to execute something like:

Now you should see data flowing from Kafka to Snowflake. Enjoy!

Find out more about all the benefits Snowflake has to offer you and your business. Sign up for a free proof of concept!