DBI connection examples

Postgres

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_cdmSchema"), 
                    writeSchema = Sys.getenv("CDM5_POSTGRESQL_SCRATCH_SCHEMA"))
DBI::dbDisconnect(con)

Redshift

Redshift is almost identical to Postgres.

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_cdmSchema"), 
                    writeSchema = Sys.getenv("CDM5_REDSHIFT_SCRATCH_SCHEMA"))
DBI::dbDisconnect(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("tempdb", "dbo"), 
                    writeSchema =  c("ATLAS", "RESULTS"))
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)

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.

Databricks/Spark

Please follow the instructions here: https://solutions.posit.co/connections/db/databases/databricks/

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

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

DBI::dbDisconnect(con)

Duckdb

Duckdb is an in-process database. We use the duckdb package to connect.

con <- DBI::dbConnect(duckdb::duckdb(), 
                      dbdir=Sys.getenv("CDM5_DUCKDB_FILE"))
cdm <- cdmFromCon(con, 
                    cdmSchema = "main", 
                    writeSchema = "main")
DBI::dbDisconnect(con)