DatabaseConnector

DatabaseConnector is part of HADES.

Introduction

This R package provides function for connecting to various DBMSs.

Features

  • Create connections to the various database platforms:
    • MicrosoftSQL Server
    • Oracle
    • PostgresSql
    • Microsoft Parallel Data Warehouse (a.k.a. Analytics Platform System)
    • Amazon Redshift
    • Apache Impala
    • Google BigQuery
    • IBM Netezza
    • SQLite
  • Statements for executing queries with
    • Error reporting to file
    • Progress reporting
    • Multiple statements per query
  • Support for fetching data to ffdf objects
  • Insert data frame to a database table
  • Supports the DBI interface
  • Integrates with RStudio’s Connections tab

Examples

connectionDetails <- createConnectionDetails(dbms="postgresql",
                                             server="localhost",
                                             user="root",
                                             password="blah",
                                             schema="cdm_v4")
conn <- connect(connectionDetails)
querySql(conn,"SELECT COUNT(*) FROM person")
disconnect(conn)
## regular data insert
insertTable(connection = connection,
            tableName = "scratch.somedata",
            data = data,
            dropTableIfExists = TRUE,
            createTable = TRUE,
            tempTable = FALSE,
            useMppBulkLoad = FALSE)

## bulk data insert with Redshift or PDW
insertTable(connection = connection,
            tableName = "scratch.somedata",
            data = data,
            dropTableIfExists = TRUE,
            createTable = TRUE,
            tempTable = FALSE,
            useMppBulkLoad = TRUE)

Technology

DatabaseConnector is an R package using Java’s JDBC drivers.

System Requirements

Running the package requires R with the package rJava installed. Also requires Java 1.8 or higher.

Installation

  1. See the instructions here for configuring your R environment, including Java.

  2. To install the latest stable version, install from CRAN:

install.packages("DatabaseConnector")

To download and use the JDBC drivers for BigQuery, Impala, or Netezza, see these instructions.

To be able to use Windows authentication for SQL Server, you have to install the JDBC driver. Download the .exe from Microsoft and run it, thereby extracting its contents to a folder. In the extracted folder you will find the file sqljdbc_4.0/enu/auth/x64/sqljdbc_auth.dll (64-bits) or sqljdbc_4.0/enu/auth/x86/sqljdbc_auth.dll (32-bits), which needs to be moved to location on the system path, for example to c:/windows/system32. If you not have write access to any folder in the system path, you can also specify the path to the folder containing the dll by setting the environmental variable PATH_TO_AUTH_DLL, so for example Sys.setenv("PATH_TO_AUTH_DLL" = "c:/temp").

User Documentation

Documentation can be found on the package website.

PDF versions of the documentation are also available:

Support

Contributing

Read here how you can contribute to this package.

License

DatabaseConnector is licensed under Apache License 2.0. The JDBC drivers fall under their own respective licenses.

Development

DatabaseConnector is being developed in R Studio.

Development status

Stable. The code is actively being used in several projects.

Acknowledgements

  • This project is supported in part through the National Science Foundation grant IIS 1251151.