Using Dremio with R and dplyr on macOS

Install unixODBC
Download and Install Dremio Server for macOS
Download and Install Dremio ODBC driver
Verify Installation of ODBC Driver for Dremio (Optional)
Install latest version of odbc package
- Check if odbc package is linked with Dremio Driver
- Update “odbcinst.ini” and “odbc.ini” if no Dremio drivers listed
Establish connection with DBI
Use your Dremio data with dplyr functions

Date Completed: November 4, 2017

By: Connor Lenio

The following is a brief guide on how to access tables connected to a Dremio Server using macOS and R. The documentation on the Dremio website did not give explicit instructions on how to get this working using the ODBC drivers, so I figured it out myself!

sessionInfo()

## R version 3.4.2 (2017-09-28)
## Platform: x86_64-apple-darwin15.6.0 (64-bit)
## Running under: macOS Sierra 10.12.6
## 
## Matrix products: default
## BLAS: /Library/Frameworks/R.framework/Versions/3.4/Resources/lib/libRblas.0.dylib
## LAPACK: /Library/Frameworks/R.framework/Versions/3.4/Resources/lib/libRlapack.dylib
## 
## locale:
## [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## loaded via a namespace (and not attached):
##  [1] compiler_3.4.2  backports_1.1.0 magrittr_1.5    rprojroot_1.2  
##  [5] tools_3.4.2     htmltools_0.3.6 yaml_2.1.14     Rcpp_0.12.13   
##  [9] stringi_1.1.5   rmarkdown_1.6   knitr_1.16      stringr_1.2.0  
## [13] digest_0.6.12   evaluate_0.10.1

Install unixODBC

Using Homebrew in a Terminal window, install unixODBC

brew install unixODBC

The end of the installation should look like this:

Download and Install Dremio Server for macOS

Navigate to https://www.dremio.com/download/
Find the download link for Dremio Server and download to computer
Open .dmg and copy dremio-app to Applications folder

Download and Install Dremio ODBC driver

Navigate to https://www.dremio.com/download/
Find the Download link for ODBC Drivers and download to computer
Open .dmg and run .pkg executable to install drivers

Verify Installation of ODBC Driver for Dremio (Optional)

Navigate to http://www.odbcmanager.net/
Run the program once installed
Verify that Dremio ODBC is listed

Install latest version of odbc package

Open R or RStudio and run the following code

# make sure to use github version
devtools::install_github("r-dbi/odbc")
library(odbc)

Check if odbc package is linked with Dremio Driver

Run the following code once odbc is installed

odbc::odbcListDrivers()

If everything is ready, your output should look like this:

Update “odbcinst.ini” and “odbc.ini” if no Dremio drivers listed

If no Dremio Drivers are listed then open Terminal
Change directories to Library/ODBC/
Use a text editor like nano to open both “odbcinst.ini” and “odbc.ini”

cd /Library/ODBC/
nano odbcinst.ini

Here is what “obdcinst.ini” looks like:

nano odbc.ini

Here is what “obdc.ini” looks like:
Now in an additional Terminal window, change directory to /usr/local/Cellar/unixodbc/2.3.4/etc

cd /usr/local/Cellar/unixodbc/2.3.4/etc

You will find this directory also contains “odbc.ini” and “odbcinst.ini” files
Using your favorite text editor, update these two files in /usr/local/Cellar/unixodbc/2.3.4/etc to contain the same entries for the Dremio ODBC Driver from the matching files present in /Library/ODBC/
Now odbc::odbcListDrivers() should list information for the Dremio Driver (You may need to restart R session to refresh)

Establish connection with DBI

Now you are ready to connect to your Dremio Server (this guide assumes Dremio Server is already up and running on your computer)

library(odbc)
library(DBI)
library(tidyverse)
library(dbplyr)

dremio_host <- "localhost"
dremio_port <- "31010"
dremio_uid <- "your_username"
dremio_pwd <- "your_password"

con <- DBI::dbConnect(odbc::odbc(), 
                      driver = "Dremio ODBC Driver",
                      uid = dremio_uid, 
                      pwd = dremio_pwd, 
                      host = dremio_host, 
                      port = dremio_port,
                      AuthenticationType = "Basic Authentication", ConnectionType = "Direct")

Use your Dremio data with dplyr functions

This guide assumes you already have data present on your Dremio Server
If you need to know the name of your table, navigate to the file using the Dremio Server UI (http://localhost:9047/) and look at the default SQL Editor name of the table (the string after “FROM” in the SQL Editor query)

Use this table name to reference the table in the following code. dplyr’s arrange function is included here to show how the table is ready for use with dplyr functions.
Note the exact use of double versus single quotes in the SQL query in the call below.

con %>% tbl(sql('SELECT * FROM "@cojamalo".german_credit')) %>% arrange(Purpose)

Alternatively, you can save the link to the table to a variable

dremio_tbl = con %>% tbl(sql('SELECT * FROM "@cojamalo".german_credit'))