Databricks ODBC and JDBC drivers

This article describes how to configure the Databricks ODBC and JDBC drivers to connect your tools or clients to Databricks. For tool or client specific connection instructions, see the Databricks integrations guide.

Requirements
ODBC driver
JDBC driver
Advanced configurations
Troubleshooting
See also

Requirements

Before you start, you need to make sure you have the appropriate permissions to connect to Databricks, to prepare your credentials and to retrieve the connection details.

Permission requirements
Authentication requirements
Retrieve the connection details

Permission requirements

There are two permissions you may need when you connect to a Databricks cluster:

Can Attach To permission to connect to the running cluster.
Can Restart permission to automatically trigger the cluster to start if its state is terminated when connecting.

To access a Databricks SQL endpoint, you need Can Use permission. The Databricks SQL endpoint will be automatically started if it was stopped.

Authentication requirements

The Databricks ODBC and JDBC drivers support authentication by using a personal access token or your Databricks username and password.

For instructions about how to generate a token, see Generate a personal access token.
Username and password authentication is possible only if single sign-on is disabled.

Retrieve the connection details

This section explains how to retrieve the connection details that you need to connect to Databricks.

Get connection details for a cluster
Get connection details for a SQL endpoint

Get connection details for a cluster

Click Compute in the sidebar.
Choose a cluster to connect to.
Navigate to Advanced Options.
Click on the JDBC/ODBC tab.
Copy the connection details.

Get connection details for a SQL endpoint

Click SQL Endpoints in the sidebar.
Choose an endpoint to connect to.
Navigate to the Connection Details tab.
Copy the connection details.

ODBC driver

This section presents the steps to configure your ODBC driver to connect to Databricks.

Download the ODBC driver
Install and configure the ODBC driver for Windows
Install and configure the ODBC driver for macOS
Install and configure the ODBC driver for Linux
ODBC configuration and connection parameters
DSN examples for non-Windows machines
ODBC driver capabilities

Download the ODBC driver

Review the license agreement for the Databricks ODBC driver before installing the software.

Some tools and clients require you to install the Databricks ODBC driver to set up a connection to Databricks, while others embed the driver and do not require separate installation. For example, to use Tableau Desktop, the ODBC driver needs to be installed, while recent Power BI Desktop releases include the driver preinstalled and no action is needed.

Go to the Databricks ODBC driver download page to download the driver for your operating system.

After you download the appropriate installer of the driver for your platform, use the following instructions to install and configure the driver:

Install and configure the ODBC driver for Windows
Install and configure the ODBC driver for Mac
Install and configure the ODBC driver for Linux
ODBC configuration and connection parameters

Install and configure the ODBC driver for Windows

In Windows, you can set up a Data Source Name (DSN) configuration to connect your ODBC client application to Databricks. To set up a DSN configuration, use the Windows ODBC Data Source Administrator.

Download the latest driver version for Windows, if you haven’t already done so. See Download the ODBC driver.
Double-click on the dowloaded .msi file to install the driver. The installation directory is C:\Program Files\Simba Spark ODBC Driver.
From the Start menu, search for ODBC Data Sources to launch the ODBC Data Source Administrator.
Navigate to the Drivers tab to verify that the driver (Simba Spark ODBC Driver) is installed.
Go to the User DSN or System DSN tab and click the Add button.
Select the Simba Spark ODBC Driver from the list of installed drivers.
Choose a Data Source Name and set the mandatory ODBC configuration and connection parameters. See also ODBC driver capabilities for more driver configurations.
Click OK to create the DSN.

Install and configure the ODBC driver for macOS

In macOS, you can set up a Data Source Name (DSN) configuration to connect your ODBC client application to Databricks. To set up a DSN on macOS, use the ODBC Manager.

Download the ODBC Manager and double-click on the downloaded .dmg file to install it.
Download the latest driver version for macOS, if you haven’t already done so. See Download the ODBC driver.
Double-click on the dowloaded .dmg file to install the driver. The installation directory is /Library/simba/spark.
Start the ODBC Manager.
Navigate to the Drivers tab to verify that the driver (Simba Spark ODBC Driver) is installed.
Go to the User DSN or System DSN tab and click the Add button.
Select the Simba Spark ODBC Driver from the list of installed drivers.
Click Add.
Choose a Data Source Name and create key-value pairs to set the mandatory ODBC configuration and connection parameters. See also ODBC driver capabilities for more driver configurations.
Click OK to create the DSN.

Install and configure the ODBC driver for Linux

In Linux, you can set up a Data Source Name (DSN) configuration to connect your ODBC client application to Databricks. To set up a DSN on Linux, use the unixODBC Driver Manager.

Install unixODBC.
Download the latest driver version for Linux, if you haven’t already done so. See Download the ODBC driver.

Install the ODBC driver.

RPM package:

sudo yum --nogpgcheck localinstall simbaspark_<version>.rpm

DEB package:
```
sudo dpkg -i simbaspark_<version>.deb
```

The installation directory is /opt/simba/spark.

Locate the odbc.ini driver configuration file that corresponds to SYSTEM DATA SOURCES:
```
odbcinst -j
```
In a text editor, open the odbc.ini configuration file.

Create an [ODBC Data Sources] section:

[ODBC Data Sources]
Databricks=Databricks ODBC Connector

Create another section with the same name as your DSN and specify the configuration parameters as key-value pairs. See the mandatory ODBC configuration and connection parameters for more information about how to set the configuration parameters. See also ODBC driver capabilities for more driver configurations.
```
[Databricks]
Driver=<path-to-driver>
<key>=<value>
...
```

ODBC configuration and connection parameters

This section presents the mandatory configuration and connection parameters for the ODBC driver.

Configuration	Description
Driver	Driver installation path
Host	Databricks server hostname
Port	443
HTTPPath	Databricks compute resources URL
SSL	1
ThriftTransport	2

Authentication parameters

To authenticate by using a personal access token, set the following configurations:

Configuration	Description
AuthMech	3
UID	token
PWD	<personal-access-token>

If you want to use your Databricks credentials, then set UID and PWD to your username and password, respectively.

Define the connection string to use in your application as follows:

Driver=<path-to-driver>;Host=<server-hostname>;Port=443;HTTPPath=<http-path>;ThriftTransport=2;SSL=1;AuthMech=3;UI=token;PWD=<personal-access-token>

Set the HOST and HTTPPath configurations to the values that you retrieved in Retrieve the connection details.

DSN examples for non-Windows machines

Here are some examples that show how to set up a DSN on different platforms based on your authentication method.

When you authenticate with a personal access token, complete the following steps:

Set the content of the odbc.ini to:

[Databricks]
Driver=<path-to-driver>
Host=<server-hostname>
Port=443
HTTPPath=<http-path>
ThriftTransport=2
SSL=1
AuthMech=3
UID=token
PWD=<personal-access-token>

Set <personal-access-token> to the token that you retrieved in Authentication requirements.
Set the HOST, PORT and HTTPPath configurations to the values that you retrieved in Retrieve the connection details.
Use the DSN in your ODBC application by setting the DSN property in the connection string DSN=Databricks;.

ODBC driver capabilities

This section presents optional ODBC driver configurations.

Set the initial schema in ODBC
ANSI SQL-92 query support in ODBC
Extract large query results in ODBC
ODBC driver guide

Set the initial schema in ODBC

The ODBC driver allows you to specify the schema by setting Schema=<schema_name> as a connection configuration. This is equivalent to running USE <schema_name>.

ANSI SQL-92 query support in ODBC

The ODBC driver accepts SQL queries in ANSI SQL-92 dialect and translates the queries to the Databricks SQL dialect. However, if your application generates Databricks SQL directly or your application uses any non-ANSI SQL-92 standard SQL syntax specific to Databricks, Databricks recommends that you set UseNativeQuery=1 as a connection configuration. With that setting, the driver passes the SQL queries verbatim to Databricks.

Extract large query results in ODBC

To achieve the best performance when you extract large query results, use the latest version of the ODBC driver that includes the following optimizations.

Arrow serialization in ODBC

The ODBC driver version 2.6.15 and above supports an optimized query results serialization format that uses Apache Arrow. To use this format extract query results using this format, use Databricks Runtime 6.4 or above.

Cloud Fetch in ODBC

The ODBC driver version 2.6.17 and above supports Cloud Fetch, a capability that fetches query results through the cloud storage that is set up in your Databricks deployment. To use Cloud Fetch to extract query results using this capability, use Databricks Runtime 8.3 or above.

Query results are uploaded to an internal DBFS storage location as Arrow-serialized files of up to 20 MB. When the driver sends fetch requests after query completion, Databricks generates and returns presigned URLs to the uploaded files. The ODBC driver then uses the URLs to download the results directly from DBFS.

Cloud Fetch is only used for query results larger than 1 MB. Smaller results are retrieved directly from Databricks.

Databricks automatically garbage collects the accumulated files, which are marked for deletion after 24 hours. These marked files are completely deleted after an additional 24 hours.

Cloud Fetch is only available for E2 workspaces. Also, your corresponding Amazon S3 buckets must not have versioning enabled. If you have versioning enabled, you can still enable Cloud Fetch by following the instructions in Advanced configurations.

To learn more about the Cloud Fetch architecture, see How We Achieved High-bandwidth Connectivity With BI Tools.

ODBC driver guide

For more information about the ODBC driver, refer to the installation and configuration guide: Simba Apache Spark ODBC Connector Install and Configuration Guide. The location of the Databricks ODBC driver installation and configuration guide depends on your platform:

MacOS: /Library/simba/spark
Linux: /opt/simba/spark
Windows: C:\Program Files\Simba Spark ODBC Driver

JDBC driver

This section presents the steps to configure your JDBC driver to connect to Databricks.

Download the JDBC driver
JDBC configuration and connection parameters
JDBC driver capabilities

Download the JDBC driver

Go to the Databricks JDBC driver download page to download the driver.
The driver is packaged as a JAR, which does not require installation and can be added to the Java classpath.

JDBC configuration and connection parameters

To authentication by using a personal token, complete the following steps:

Get the JDBC URL that you retrieved in Retrieve the connection details.

Replace <personal-access-token> with the token that you created in Authentication requirements:

jdbc:spark://<server-hostname>:443/default;transportMode=http;ssl=1;httpPath=<http-path>;AuthMech=3;UID=token;PWD=<personal-access-token>

JDBC driver capabilities

This section presents optional JDBC driver configurations.

Set the initial schema in JDBC
ANSI SQL-92 query support in JDBC
Extract large query results in JDBC
JDBC driver guide

Set the initial schema in JDBC

The JDBC driver allows you to specify the schema by appending the <schema-name> to the server hostname and port as follows:

jdbc:spark://<server-hostname>:443/<schema-name>

ANSI SQL-92 query support in JDBC

The JDBC driver accepts SQL queries in ANSI SQL-92 dialect and translates the queries to the Databricks SQL dialect. However, if your application generates Databricks SQL directly or your application uses any non-ANSI SQL-92 standard SQL syntax specific to Databricks, Databricks recommends that you set UseNativeQuery=1 as a connection configuration. With that setting, the driver passes the SQL queries verbatim to Databricks.

Extract large query results in JDBC

To achieve the best performance when you extract large query results, use the latest version of the ODBC driver, which includes the following optimizations.

Arrow serialization in JDBC

The JDBC driver version 2.6.16 and above supports an optimized query results serialization format that uses Apache Arrow. To use this format to extract query results, use Databricks Runtime 6.4 or above.

Cloud Fetch in JDBC

The JDBC driver version 2.6.19 and above supports Cloud Fetch, a capability that fetches query results through the cloud storage that is set up in your Databricks deployment. To use Cloud Fetch to extract query results, use Databricks Runtime 8.3 or above.

Query results are uploaded to an internal DBFS storage location as Arrow-serialized files of up to 20 MB. When the driver sends fetch requests after query completion, Databricks generates and returns presigned URLs to the uploaded files. The JDBC driver then uses the URLs to download the results directly from DBFS.

Cloud Fetch is only used for query results larger than 1 MB. Smaller results are retrieved directly from Databricks.

Databricks automatically garbage collects the accumulated files which are marked for deletion after 24 hours. These marked files are completely deleted after an additional 24 hours.

Cloud Fetch is only available in E2 workspaces. Also, your corresponding Amazon S3 buckets must not have versioning enabled. If you have versioning enabled, you can still enable Cloud Fetch by following the instructions in Advanced configurations.

To learn more about the Cloud Fetch architecture, see How We Achieved High-bandwidth Connectivity With BI Tools.

JDBC driver guide

For more information about the JDBC driver, refer to the installation and configuration guide. Find the Databricks JDBC driver installation and configuration guide in the docs directory of the driver package.

Advanced configurations

If you have versioning enabled in your DBFS root, Databricks doesn’t automatically garbage collect accumulated files from the DBFS storage location. In order to use Cloud Fetch with versioning enabled without incurring extra costs, you must apply the following configurations before you can start using Cloud Fetch in ODBC or Cloud Fetch in JDBC:

Set a lifecycle policy for Cloud Fetch using the instructions from Set a lifecycle policy.
Set the Cloud Fetch override using the instructions from Set the Cloud Fetch override.

Set a lifecycle policy for Cloud Fetch

To set a lifecycle policy:

In the AWS console, go to the S3 service.
Click on the S3 bucket that you use for your workspace’s root storage.
Open the Management tab and choose Create lifecycle rule.
Choose any name for the Lifecycle rule name.
Keep the prefix field empty.
Under Object tags, click on Add tag.
Enter CloudFetchResult as the key, but do not specify a value.
Under Lifecycle rule actions select Permanently delete noncurrent versions of objects.
Set the value 1 under Days after objects become noncurrent.
Click Create rule.

Once you set a lifecycle policy you can enable Cloud Fetch by setting an override.

Enable Cloud Fetch on clusters

Go to the Advanced options of the cluster. See Get connection details from a cluster.
Click Spark.

Add this configuration:

spark.databricks.cloudfetch.override.enabled true

Enable Cloud Fetch on SQL endpoints

Click Settings in the sidebar.
From the SQL Admin Console select SQL Endpoint Settings.

In Data Access Configuration add this configuration:

spark.databricks.cloudfetch.override.enabled true

Click Save.

Troubleshooting

See Troubleshooting JDBC and ODBC connections.