Databricks

Driver Options

Package Options

The odbc package, in combination with a driver, provides DBI support, and an ODBC connection.

Connection Settings

There are 10 settings needed to make a connection:

  • Driver - See the Drivers section for setup information
  • Host - Your organization’s Host URL within Databricks
  • Port - Typically set to 443
  • AuthMech - Choose 3 in order to use a PAT token for your credentials
  • HTTPPath - This is the path to the machine that will serve as the SQL server. To obtain this, navigate to the the Databricks web interface, go to SQL Warehouses > Select your SQL Warehouse > Connections Details tab
  • Protocol - Typically set to https
  • ThriftTransport - Set to 2
  • SSL - Set to 1
  • UID - Set to “token”, if using 3 as the AuthMech
  • PWD - Your personal Databricks Token (PAT). Use an environment variable to avoid passing in plain credentials in your code. We suggest the name DATABRICKS_TOKEN, which follows consistent form for many other APIs that interact with Databricks.
  • catalog - Not required, but it makes it easy for the RStudio IDE to display the schema and tables you want to navigate through
con <- DBI::dbConnect(odbc::odbc(),
                 Driver          = "[The file path to your driver]",
                 Host            = "[Your organization's Host URL]",
                 Port            = 443,
                 AuthMech        = 3,
                 HTTPPath        = "[SQL Warehouse service path]",
                 Protocol        = "https",
                 ThriftTransport = 2,
                 SSL             = 1,
                 UID             = "token",
                 PWD             = Sys.getenv("DATABRICKS_TOKEN"),
                 catalog         = "[Catalog you wish to connect to]"
                )

Troubleshooting: Apple macOS users

If you receive an error message similar to this when attempting to connect, please read this section:

Unable to locate SQLGetPrivateProfileString function: [Simba][Support] (50483) 
Could not load shared library, all attempted paths ("") failed


This is likely happening because the driver can’t locate the ODBC Driver Manager, which acts as the interface between an ODBC application (R), and an ODBC driver (your new Databricks driver).

To solve this issue:

  1. Locate the Driver Manager library. Search for a file called libodbcinst.dylib it in the ‘/usr/local/lib/’ folder.

    1. If it is not there, then install it via Homebrew in the terminal: brew install unixodbc.
    2. After installing, use: brew --prefix unixodbc to find out where Homebrew installed it. The libodbcinst.dylib file should be inside the ‘lib’ sub-folder.

  2. Open the driver configuration file to edit. By default the file will be located here: ‘/Library/simba/spark/lib/simba.sparkodbc.ini’.

  3. Append the following two lines at the end:

ODBCInstLib=[Location of your libodbcinst.dylib file]
DriverManagerEncoding=UTF-16

The second entry is to avoid output that may be unreadable. For more information, please read our article Encoding for ODBC connections.

More information

For more information, visit the Databricks documentation:

Back to top