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 is part of the OHDSI Methods Library.
This R package provides function for connecting to various DBMSs.
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 PDWinsertTable(connection = connection,tableName = "scratch.somedata",data = data,dropTableIfExists = TRUE,createTable = TRUE,tempTable = FALSE,useMppBulkLoad = TRUE)
DatabaseConnector is an R package using Java's JDBC drivers.
Requires R. Also requires Java 1.6 or higher (Oracle Java is recommended. Issues have been reported when using GCJ.)
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.
To install the latest stable version, install from CRAN:
To install the latest development version, install from GitHub:
To download and use the JDBC drivers for BigQuery, Impala, or Netezza, see these instructions.
DatabaseConnector is licensed under Apache License 2.0. The JDBC drivers fall under their own respective licenses.
DatabaseConnector is being developed in R Studio.
Stable. The code is actively being used in several projects.
Adding support for SQLite through RSQLite (mainly for demonstration and testing)
Adding convenience functions renderTranslateExecuteSql, renderTranslateQuerySql, and renderTranslateQuerySql.ffdf
Dropping Starschema BigQuery driver (in favor of newer Simba driver)
Added support for inserting BIGINTs (large interegers stored as numeric in R)
Applying CTAS hack to improve insertion performance for RedShift (was already used for PDW)
Changes: initial submission to CRAN