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

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)

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.