---
title: "DBI connection examples"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{DBI connection examples}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = FALSE
)
```

The following connection examples are provided for reference.

### Postgres

```{r, eval=FALSE}
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.

```{r, eval=FALSE}
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](https://solutions.posit.co/connections/db/r-packages/odbc/). Note, you'll likely need to [download the ODBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16). 

```{r, eval=FALSE}
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](https://www.r-bloggers.com/2018/05/setting-up-an-odbc-connection-with-ms-sql-server-on-windows/) for instructions on how to set up the DSN.If we named it "SQL", our connection is then simplified to.

```{r, eval=FALSE}
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. 

```{r, eval=FALSE}
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](https://docs.snowflake.com/developer-guide/odbc/odbc-windows) for windows and [here](https://docs.snowflake.com/developer-guide/odbc/odbc-mac) for macOS.


### Databricks/Spark

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

```{r}
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.

```{r, eval=FALSE}
con <- DBI::dbConnect(duckdb::duckdb(), 
                      dbdir=Sys.getenv("CDM5_DUCKDB_FILE"))
cdm <- cdmFromCon(con, 
                    cdmSchema = "main", 
                    writeSchema = "main")
DBI::dbDisconnect(con)
```