DBI connection examples

The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

Postgres

Connect to Postgres using the RPostgres package.

con <- DBI::dbConnect(RPostgres::Postgres(),
                      dbname = Sys.getenv("CDM5_POSTGRESQL_DBNAME"),
                      host = Sys.getenv("CDM5_POSTGRESQL_HOST"),
                      user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                      password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))

cdm <- cdmFromCon(con, 
                  cdmSchema = Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA"), 
                  writeSchema = Sys.getenv("CDM5_POSTGRESQL_SCRATCH_SCHEMA"))

DBI::dbDisconnect(con)

Connect to Postgres using DatabaseConnector (version 7 or later).


library(DatabaseConnector)
connectionDetails <- createConnectionDetails(dbms = "postgresql",
                                             server = Sys.getenv("CDM5_POSTGRESQL_SERVER"),
                                             user = Sys.getenv("CDM5_POSTGRESQL_USER"),
                                             password = Sys.getenv("CDM5_POSTGRESQL_PASSWORD"))


con <- connect(connectionDetails)

cdm <- cdmFromCon(con, 
                  cdmSchema = Sys.getenv("CDM5_POSTGRESQL_CDM_SCHEMA"), 
                  writeSchema = Sys.getenv("CDM5_POSTGRESQL_SCRATCH_SCHEMA"))

disconnect(con)

Redshift

Connect to Redshift using the RPostgres package.

con <- DBI::dbConnect(RPostgres::Redshift(),
                      dbname   = Sys.getenv("CDM5_REDSHIFT_DBNAME"),
                      host     = Sys.getenv("CDM5_REDSHIFT_HOST"),
                      port     = Sys.getenv("CDM5_REDSHIFT_PORT"),
                      user     = Sys.getenv("CDM5_REDSHIFT_USER"),
                      password = Sys.getenv("CDM5_REDSHIFT_PASSWORD"))

cdm <- cdmFromCon(con, 
                  cdmSchema = Sys.getenv("CDM5_REDSHIFT_CDM_SCHEMA"), 
                  writeSchema = Sys.getenv("CDM5_REDSHIFT_SCRATCH_SCHEMA"))

DBI::dbDisconnect(con)

Connect to Redshift using the DatabaseConnector package (version 7 or later).

library(DatabaseConnector)  

connectionDetails <- createConnectionDetails(dbms = "redshift",
                                             server = Sys.getenv("CDM5_REDSHIFT_SERVER"),
                                             user = Sys.getenv("CDM5_REDSHIFT_USER"),
                                             password = Sys.getenv("CDM5_REDSHIFT_PASSWORD"),
                                             port = Sys.getenv("CDM5_REDSHIFT_PORT"))
con <- connect(connectionDetails)

cdm <- cdmFromCon(con, 
                  cdmSchema = Sys.getenv("CDM5_REDSHIFT_CDM_SCHEMA"), 
                  writeSchema = Sys.getenv("CDM5_REDSHIFT_SCRATCH_SCHEMA"))

disconnect(con)

SQL Server

Using odbc with SQL Server requires driver setup described here. Note, you’ll likely need to download the ODBC Driver for SQL Server.

con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "ODBC Driver 18 for SQL Server",
                      Server   = Sys.getenv("CDM5_SQL_SERVER_SERVER"),
                      Database = Sys.getenv("CDM5_SQL_SERVER_CDM_DATABASE"),
                      UID      = Sys.getenv("CDM5_SQL_SERVER_USER"),
                      PWD      = Sys.getenv("CDM5_SQL_SERVER_PASSWORD"),
                      TrustServerCertificate="yes",
                      Port     = 1433)

cdm <- cdmFromCon(con, 
                    cdmSchema = c("cdmv54", "dbo"), 
                    writeSchema =  c("tempdb", "dbo"))

DBI::dbDisconnect(con)

The connection to SQL Server can be simplified by configuring a DSN. See here for instructions on how to set up the DSN. If we named it “SQL”, our connection is then simplified to.

con <- DBI::dbConnect(odbc::odbc(), "SQL")
cdm <- cdmFromCon(con, 
                    cdmSchema = c("tempdb", "dbo"), 
                    writeSchema =  c("ATLAS", "RESULTS"))
DBI::dbDisconnect(con)

Connect to SQL Server using the DatabaseConnector package (version 7 or later).

library(DatabaseConnector)
connectionDetails <- createConnectionDetails(
  dbms = "sql server",
  server = Sys.getenv("CDM5_SQL_SERVER_SERVER"),
  user = Sys.getenv("CDM5_SQL_SERVER_USER"),
  password = Sys.getenv("CDM5_SQL_SERVER_PASSWORD"),
  port = Sys.getenv("CDM5_SQL_SERVER_PORT")
)

con <- connect(connectionDetails)

cdm <- cdmFromCon(con, 
                  cdmSchema = c("cdmv54", "dbo"), 
                  writeSchema =  c("tempdb", "dbo"))

disconnect(con)

Snowflake

We can use the odbc package to connect to snowflake.

con <- DBI::dbConnect(odbc::odbc(),
                          SERVER = Sys.getenv("SNOWFLAKE_SERVER"),
                          UID = Sys.getenv("SNOWFLAKE_USER"),
                          PWD = Sys.getenv("SNOWFLAKE_PASSWORD"),
                          DATABASE = Sys.getenv("SNOWFLAKE_DATABASE"),
                          WAREHOUSE = Sys.getenv("SNOWFLAKE_WAREHOUSE"),
                          DRIVER = Sys.getenv("SNOWFLAKE_DRIVER"))
cdm <- cdmFromCon(con, 
                  cdmSchema =  c("OMOP_SYNTHETIC_DATASET", "CDM53"), 
                  writeSchema =  c("ATLAS", "RESULTS"))

DBI::dbDisconnect(con)

Note, as with SQL server we could set up a DSN to simplify this connection as described here for windows and here for macOS.

Connect to Snowflake using the DatabaseConnector package (version 7 or later).

Your connection string will look something like jdbc:snowflake://asdf.snowflakecomputing.com?db=DBNAME&warehouse=COMPUTE_WH

library(DatabaseConnector)

connectionDetails <- createConnectionDetails(
  dbms = "snowflake",
  connectionString = Sys.getenv("SNOWFLAKE_CONNECTION_STRING"),
  user = Sys.getenv("SNOWFLAKE_USER"),
  password = Sys.getenv("SNOWFLAKE_PASSWORD")
)

con <- connect(connectionDetails)

cdm <- cdmFromCon(con, 
                  cdmSchema =  c("OMOP_SYNTHETIC_DATASET", "CDM53"), 
                  writeSchema =  c("ATLAS", "RESULTS"))

disconnect(con)

Databricks/Spark

To connect to Databricks using ODBC please follow the instructions here: https://solutions.posit.co/connections/db/databases/databricks/

You will need to set two environment variables in your .Renviron file: DATABRICKS_HOST=“[Your organization’s Host URL]” DATABRICKS_TOKEN=“[Your personal Databricks token]”

Create or open the .Renviron file by running usethis::edit_r_environ()

con <- DBI::dbConnect(
  odbc::databricks(),
  httpPath = Sys.getenv("DATABRICKS_HTTPPATH"),
  useNativeQuery = FALSE
)

cdm <- cdmFromCon(con, 
                  cdmSchema =  "gibleed", 
                  writeSchema = "scratch")

DBI::dbDisconnect(con)

To connect to Databricks using DatabaseConnector use the following example. The connection will look something like "jdbc:databricks://asdf.cloud.databricks.com/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/warehouses/6"

The password should be your databricks token.

library(DatabaseConnector)

connectionDetails <- createConnectionDetails(
  dbms = "spark",
  user = "token",
  password = Sys.getenv('DATABRICKS_TOKEN'),
  connectionString = Sys.getenv('DATABRICKS_CONNECTION_STRING')
)

con <- connect(connectionDetails)


cdm <- cdmFromCon(con, 
                  cdmSchema =  "gibleed", 
                  writeSchema = "scratch")

disconnect(con)

We can ignore the “ERROR StatusLogger Unrecognized format/conversion specifier” messages as these have to do with the log format.

Duckdb

Duckdb is an in-process database similar to SQLite. We use the duckdb package to connect. The dbdir argument should point to the database file location.

library(CDMConnector)
con <- DBI::dbConnect(duckdb::duckdb(), 
                      dbdir = eunomiaDir("GiBleed"))

cdm <- cdmFromCon(con, 
                  cdmSchema = "main", 
                  writeSchema = "main")

DBI::dbDisconnect(con)

We can also use DatabaseConnector to connect to duckdb. In the example the server argument points to the duckdb file location.

library(DatabaseConnector)
connectionDetails <- createConnectionDetails(
  "duckdb", 
  server = CDMConnector::eunomiaDir("GiBleed"))

con <- connect(connectionDetails)

cdm <- cdmFromCon(con, 
                  cdmSchema = "main", 
                  writeSchema = "main")


disconnect(con)

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.