/
README.Rmd
132 lines (99 loc) · 5.36 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, echo = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "README-",
eval = as.logical(Sys.getenv("ODBC_EVAL_README", "false"))
)
```
# odbc
<!-- badges: start -->
[](https://www.repostatus.org/)
[](https://cran.r-project.org/package=odbc)
[](https://github.com/r-dbi/odbc/actions/workflows/R-CMD-check.yaml)
[](https://app.codecov.io/gh/r-dbi/odbc?branch=main)
<!-- badges: end -->
The goal of the odbc package is to provide a [DBI](https://dbi.r-dbi.org/)-compliant interface to [ODBC](https://learn.microsoft.com/en-us/sql/odbc/microsoft-open-database-connectivity-odbc) drivers. This makes it easy to connect databases such as [SQL Server](https://www.microsoft.com/en-us/sql-server/), [Oracle](https://www.oracle.com/database), [Databricks](https://www.databricks.com/), and [Snowflake](https://www.snowflake.com/en/).
The odbc package is an alternative to
[RODBC](https://cran.r-project.org/package=RODBC) and [RODBCDBI](https://cran.r-project.org/package=RODBCDBI) packages, and is typically much faster. See `vignette("benchmarks")` to learn more.
## Overview
The odbc package is one piece of the R interface to databases with support for ODBC:
```{r whole-game, eval = TRUE, echo = FALSE}
#| fig-alt: >
#| A diagram containing four boxes with arrows linking each pointing left to
#| right. The boxes read, in order, "R interface," "driver manager,"
#| "ODBC driver," and "DBMS." The left-most box, R interface, contains
#| three smaller components, labeled "dbplyr," "DBI," and "odbc."
knitr::include_graphics("man/figures/whole-game.png")
```
Support for a given DBMS is provided by an **ODBC driver**, which defines how to interact with that DBMS using the standardized syntax of ODBC and SQL. Drivers can be downloaded from the DBMS vendor or, if you're a Posit customer, using the [professional drivers](https://docs.posit.co/pro-drivers/).
Drivers are managed by a **driver manager**, which is responsible for configuring driver locations, and optionally named **data sources** that describe how to connect to a specific database. Windows is bundled with a driver manager, while MacOS and Linux require installation of [unixODBC](https://www.unixodbc.org/). Drivers often require some manual configuration; see `vignette("setup")` for details.
In the **R interface**, the [DBI package](https://dbi.r-dbi.org/) provides a front-end while odbc implements a back-end to communicate with the driver manager. The odbc package is built on top of the
[nanodbc](https://nanodbc.github.io/nanodbc/) C++ library. To interface with DBMSs using R and odbc:
```{r r-interface, eval = TRUE, echo = FALSE}
#| fig-alt: >
#| A high-level workflow for using the R interface in 3 steps.
#| In step 1, configure drivers and data sources, the functions
#| odbcListDrivers() and odbcListDataSources() help to interface with the
#| driver manager. In step 2, the dbConnect() function, called with the
#| first argument odbc(), connects to a database using the specified ODBC
#| driver to create a connection object "con." Finally, in step 3, that
#| connection object can be passed to various functions to retrieve
#| information on database structure, iteratively develop queries, and
#| query data objects.
knitr::include_graphics("man/figures/r-interface.png")
```
You might also use the [dbplyr package](https://dbplyr.tidyverse.org/) to automatically generate SQL from your dplyr code.
## Installation
Install the latest release of odbc from CRAN with the following code:
```r
install.packages("odbc")
```
To get a bug fix or to use a feature from the development version, you can install the development version of odbc from GitHub:
```r
# install.packages("pak")
pak::pak("r-dbi/odbc")
```
## Usage
To use odbc, begin by creating a database connection, which might look something like this:
```{r}
#| eval: false
library(DBI)
con <- dbConnect(
odbc::odbc(),
driver = "SQL Server",
server = "my-server",
database = "my-database",
uid = "my-username",
pwd = rstudioapi::askForPassword("Database password")
)
```
(See `vignette("setup")` for examples of connecting to a variety of databases.)
`dbListTables()` is used for listing all existing tables in a database.
```{r}
#| eval: false
dbListTables(con)
```
`dbReadTable()` will read a full table into an R `data.frame()`.
```{r}
#| eval: false
data <- dbReadTable(con, "flights")
```
`dbWriteTable()` will write an R `data.frame()` to an SQL table.
```{r}
#| eval: false
dbWriteTable(con, "iris", iris)
```
`dbGetQuery()` will submit a SQL query and fetch the results:
```{R}
#| eval: false
df <- dbGetQuery(
con,
"SELECT flight, tailnum, origin FROM flights ORDER BY origin"
)
```
It is also possible to submit the query and fetch separately with `dbSendQuery()` and `dbFetch()`. This allows you to use the `n` argument to `dbFetch()` to iterate over results that would otherwise be too large to fit in memory.