Connecting to Various Database Platforms

An R 'DataBase Interface' ('DBI') compatible interface to various database platforms ('PostgreSQL', 'Oracle', 'Microsoft SQL Server', 'Amazon Redshift', 'Microsoft Parallel Database Warehouse', 'IBM Netezza', 'Apache Impala', 'Google BigQuery', and 'SQLite'). Also includes support for fetching data as 'ffdf' objects. Uses 'Java Database Connectivity' ('JDBC') to connect to databases (except SQLite).


DatabaseConnector

Build Status codecov.io CRAN_Status_Badge CRAN_Status_Badge

DatabaseConnector is part of the OHDSI Methods Library.

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)
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

Requires R. Also requires Java 1.6 or higher (Oracle Java is recommended. Issues have been reported when using GCJ.)

Dependencies

Please note that this package requires Java to be installed. If you don't have Java already intalled on your computed (on most computers it already is installed), go to java.com to get the latest version.

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.

DatabaseConnector also depends on the OHDSI DatabaseConnectorJars and SqlRender packages.

For Redshift Bulk Mpp inserts, the cloudyR aws S3 pacakge is required.

Getting Started

To install the latest stable version, install from CRAN:

install.packages("DatabaseConnector")

To install the latest development version, install from GitHub:

install.packages("devtools")
devtools::install_github("ohdsi/DatabaseConnectorJars")
devtools::install_github("ohdsi/DatabaseConnector")

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

User Documentation

Support

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.

News

DatabaseConnector 2.3.0

Changes:

  1. Adding support for SQLite through RSQLite (mainly for demonstration and testing)

  2. Adding convenience functions renderTranslateExecuteSql, renderTranslateQuerySql, and renderTranslateQuerySql.ffdf

  3. Dropping Starschema BigQuery driver (in favor of newer Simba driver)

  4. Added support for inserting BIGINTs (large interegers stored as numeric in R)

  5. Applying CTAS hack to improve insertion performance for RedShift (was already used for PDW)

Bugfixes:

  1. Executing multi-statement SQL where one statement returns results no longer causes error.

DatabaseConnector 2.2.1

Changes:

  1. Now supporting proper insertion and extraction of DATETIME fields

Bugfixes:

  1. Closing output stream when writing to zip file to avoid orphan file locks
  2. Fixed the problem that Jar file is not detected when setting JDBC driver manually

DatabaseConnector 2.2.0

Changes:

  1. Checking number of inserted rows after bulk upload, throwing error if not correct
  2. Added convenience function for cross-platform zipping of files and folders

DatabaseConnector 2.1.4

Changes:

  1. Faster inserts by building batches in Java instead of R

DatabaseConnector 2.1.3

Changes:

  1. Updated to DBI specification 1.0
  2. Defaulting connect arguments to NULL to prevent missing argument warnings in RStudio

Bugfixes:

  1. Now generating unique display names for RStudio's Connections tab to prevent problems when opening two connections to the same server.

DatabaseConnector 2.1.2

Changes: initial submission to CRAN

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.

install.packages("DatabaseConnector")

2.4.1 by Martijn Schuemie, a month ago


https://ohdsi.github.io/DatabaseConnector, https://github.com/OHDSI/DatabaseConnector


Report a bug at https://github.com/OHDSI/DatabaseConnector/issues


Browse source code at https://github.com/cran/DatabaseConnector


Authors: Martijn Schuemie [aut, cre] , Marc Suchard [aut] , Observational Health Data Science and Informatics [cph] , Microsoft Inc. [cph] (SQL Server JDBC driver) , PostgreSQL Global Development Group [cph] (PostgreSQL JDBC driver) , Oracle Inc. [cph] (Oracle JDBC driver) , Amazon Inc. [cph] (RedShift JDBC driver)


Documentation:   PDF Manual  


Apache License license


Imports DatabaseConnectorJars, rJava, bit, ff, ffbase, SqlRender, methods, utils, DBI, urltools

Suggests aws.s3, uuid, R.utils, testthat, DBItest, knitr, rmarkdown, RSQLite


See at CRAN