Advent of 2020, Day 27 – Connecting Azure Databricks with on premise environment

[This article was first published on R – TomazTsql, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Series of Azure Databricks posts:

Yesterday we connected the Azure Machine Learning services and Azure Databricks workspace for tracking and monitoring experiments and models in Azure Machine Learning.

Today we will connect on premise development environment (R or Python) with resources in Azure Databricks. With other words, we will have running code on a client / on premise machine and pushing all the workload to the cloud.

1.Connecting with R-Studio and SparkR

Launch your Azure Databricks workspace from Azure portal. Once the Azure Databricks is launched, head to Clusters.

Start the cluster you will be using to connect to R Studio. Go to “Apps” tab under clusters:

Before you click the “Set up RStudio” button, you will need to disable the auto termination option. By default is enabled, making a cluster terminate itself after period of time of inactivity. Under configuration, select Edit and disable the termination. Cluster will restart. And then click the set up Rstudio. Beware, you should not stop the cluster yourself after finishing work (!).

Click Set up RStudio and you will get the following credentials:

And click the “Open RStudio” and you will get redirected to web portal with RStudio opening.

In order to get the Databricks cluster objects into R Studio, you must also run the spark_connect:

sc <- spark_connect(method = "databricks")

And you will see all the DataFrames or CSV Files from previous days:

Please note, you can also connect to RStudio desktop version (!). If so, you would, there are the following steps:

Open your RStudio Desktop and install:


Install Databricks-connect in CLI (it is a 250Mb Package):

pip uninstall pyspark
pip install -U databricks-connect

Now set the connections to Azure Databricks:

databricks-connect get-jar-dir

And after that run the command in CLI:

databricks-connect configure

CLI will look like a text input:

And all the information you need to fill in the the CLI can find in URL:

Databrick host:

Cluster Name: 1128-xxxx-yaws18/

Organization: 860xxxxxxxxxx95

Port: 15001

Token: /////

2.Connecting with Python

Go to your Cluster in Azure Databricks and straight to configure.

Cluster will need to have these two items added in the Advanced Options -> Spark Config section. Set these values to a cluster that you want to connect to from on-premise or client machine.

spark.databricks.service.server.enabled true
spark.databricks.service.port 8787

It is a key-pair value so there must be a space between the key and it’s value. Once you save this, the cluster will need to restart (click “Confirm & Restart)”!

You can create a virtual environment; it is up to you. I wil create a new environment for Databricks connections and python. In your CLI run the following command:

conda create --name databricksconnect python=3.7

And activate the environment:

conda activate databricksconnect

If you are using an existing Python environment, I strongly suggest you to first uninstall PySpark. This is due the fact that databricks-connect package will have it’s own version of PySpark (same as with R).

Now install the Databricks-Connect package. You can alos specify the version of databricks-connect by adding =5.2 to have like pip install -U databricks-connect=5.2. This depends on your Datatricks cluster version

#pip uninstall pyspark
pip install -U databricks-connect

After the installation, you will need to run the configurations again:

databricks-connect configure

Adding all the needed settings as explained in R section. And test your connection by using:

databricks-connect test

Once you have all the settings enabled you are ready to use Databricks on your on-premis / client machine.

You have many ways to use it:

  • Anaconda notebooks / Jupyter notebooks
  • PyCharm
  • Atom
  • Visual Studio Code
  • etc.

In Visual Studio Code (lightweight, open-source, multi-platform) you can set up this connection. Make sure you have a Python extension installed.

and run the following script:

from pyspark.sql import SparkSession
from pyspark.sql.functions import lit, col
spark = SparkSession.builder.getOrCreate()

# Import
data ="csv").option("header", "true").load("/databricks-datasets/samples/population-vs-price/data_geo.csv")


And once you run this, you should have the results.

Make sure you also choose the correct Python interpreter in Visual Studio Code.

3. Connecting to Azure Databricks with ODBC

You can also connect Azure Databricks SQL tables using ODBC to your on-premise Excel or to Python or to R.

It will only see the SQL tables and connections. but it can also be done. This will require some ODBC installation, but I will not go into it.

Tomorrow we will look into Infrastructure as Code and how to automate, script and deploy Azure Databricks.

Complete set of code and the Notebook is available at the Github repository.

Happy Coding and Stay Healthy!

To leave a comment for the author, please follow the link and comment on their blog: R – TomazTsql. offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)