OHDSI/DatabaseConnector

Name: DatabaseConnector

Owner: Observational Health Data Sciences and Informatics

Description: An R package for connecting to databases using JDBC.

Created: 2014-04-03 08:18:32.0

Updated: 2017-12-30 07:04:45.0

Pushed: 2017-12-12 05:36:31.0

Homepage:

Size: 44147

Language: R

GitHub Committers

User	Most Recent Commit	# Commits

Other Committers

User	Email	Most Recent Commit	# Commits

README

DatabaseConnector

Introduction

This R package provides function for connecting to various DBMSs.

Features

• Create connections to the various database platforms:
• SQL Server
• Oracle
• Postgres
• Microsoft Parallel Data Warehouse (a.k.a. Analytics Platform System)
• Amazon Redshift
• MySQL
• Impala
• Google BigQuery
• 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

Examples

ectionDetails <- createConnectionDetails(dbms="mysql", 
                                         server="localhost",
                                         user="root",
                                         password="blah",
                                         schema="cdm_v4")
 <- connect(connectionDetails)
ySql(conn,"SELECT COUNT(*) FROM person")
onnect(conn)

egular data insert
rtTable(connection = connection, 
        tableName = "scratch.somedata", 
        data = data, 
        dropTableIfExists = TRUE, 
        createTable = TRUE, 
        tempTable = FALSE, 
        useMppBulkLoad = FALSE)

ulk data insert with Redshift or PDW
rtTable(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 SqlRender package.

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

Getting Started

Use the following commands in R to install the DatabaseConnector package:

stall.packages("drat")
at::addRepo("OHDSI")
stall.packages("DatabaseConnector")

Getting Involved

• Package manual: DatabaseConnector manual
• Developer questions/comments/feedback: OHDSI Forum
• We use the GitHub issue tracker for all bugs/issues/enhancements

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.