odbc

A modern database interface

Jim Hester (RStudio)

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
RODBC RSQLServer
Reading 3.2 X 1.5 X
Writing 1.9 X 37.6 X

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