odbc

Databases

• RDBMS - Relational Database Management System
• SQL - Strucured Query Language
• Lots of data
• Many types exist
• Each has proprietary API / SQL Dialect

ODBC - Open Database Connectivity

• Defined Standard
• Common Connection Procedures
• Data Insertion / Retrieval
• Widely adopted, Many Drivers
• Cross Platform (Unix, MacOS, Windows)

DBI - Database Interface

• DBs <-> R/S
• First CRAN release (2001-12-05), also S-PLUS
• David A. James - (2001), Hadley Wickham (2014), Kirill Müller (2016)
• DBItest - comprehensive compatibility testing
• dplyr - 0.7.0 generic DBI backend

odbc

• install.packages("odbc")
• DBI compatible
• Complete Rewrite (C++11 / Rcpp) vs RODBC
• Native support for dates / timestamps / raw binary / 64 bit integers
• Parameterized queries / Batched insert, retrieval
• Well tested - SQL Server, PostgreSQL, MySQL
• QA / Solutions Engineering - Extensive testing Commercial DBs (Oracle, SQL Server, Impala, Hive, ...)
• Faster than existing alternatives

dplyr / dbplyr

• High level interface / SQL translator
• As of 0.7 works directly with DBI connections
• Robust Translations
  • SQL Server, Oracle, PostgreSQL, Hive, Impala...

RStudio Server Pro - Bundled ODBC Drivers

• Driver install script / Easy setup
• Improved performance
• Improved error messages
• Kerberos support
• Future - RStudio Connect

Demo

RStudio's DB Vision

• Uniform Interfaces - DBI, dbplyr
• Commercial DBs, Free and Professional Drivers - odbc
• Storing Credentials - keyring, config
• IDE Support / R Markdown / Notebooks
• https://db.rstudio.com
• https://bit.ly/odbc2017