forked from daranzolin/sqltargets
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathREADME.Rmd
120 lines (94 loc) · 3.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
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "man/figures/README-",
out.width = "100%"
)
```
# sqltargets
<!-- badges: start -->
[](https://www.repostatus.org/#wip)
[](https://github.com/daranzolin/sqltargets/actions/workflows/R-CMD-check.yaml)
[](https://CRAN.R-project.org/package=sqltargets)
[](https://cran.r-project.org/package=sqltargets)
[](https://wlandau.github.io/targetopia/)
<!-- badges: end -->
SQL queries (as separate files) occupy an awkward spot within R pipelines. The goal of sqltargets is to offer a shorthand `tar_sql` to reference and execute queries within [a targets project.](https://github.com/ropensci/targets)
## Installation
You can install sqltargets from CRAN with:
```r
install.packages("sqltargets")
```
You can install the development version of sqltargets with:
``` r
remotes::install_github("daranzolin/sqltargets)
```
## Example
```{r example}
library(targets)
library(sqltargets)
tar_dir({ #
# Unparameterized SQL query:
lines <- c(
"-- !preview conn=DBI::dbConnect(RSQLite::SQLite())",
"select 1 AS my_col",
""
)
writeLines(lines, "query.sql")
# Include the query in a pipeline as follows.
tar_script({
library(tarchetypes)
library(sqltargets)
list(
tar_sql(query, path = "query.sql")
)
}, ask = FALSE)
})
```
## Specifying dependencies
Use `tar_load` or `targets::tar_load` within a SQL comment to indicate query
dependencies. Check the dependencies of any query with `tar_sql_deps`.
```{r}
lines <- c(
"-- !preview conn=DBI::dbConnect(RSQLite::SQLite())",
"-- targets::tar_load(data1)",
"-- targets::tar_load(data2)",
"select 1 AS my_col",
""
)
query <- tempfile()
writeLines(lines, query)
tar_sql_deps(query)
```
## Passing parameters
Pass parameters (presumably from another object in your targets project) from a named list with
'glue' syntax: `{param}`.
query.sql
```sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite())
-- tar_load(query_params)
select id
from table
where age > {age_threshold}
```
```{r eval = FALSE}
tar_script({
library(targets)
library(tarchetypes)
library(sqltargets)
list(
tar_target(query_params, list(age_threshold = 30)),
tar_sql(query, path = "query.sql", query_params = query_params)
)
}, ask = FALSE)
```
## Code of Conduct
Please note that the sqltargets project is released with a [Contributor Code of Conduct](https://contributor-covenant.org/version/2/1/CODE_OF_CONDUCT.html). By contributing to this project, you agree to abide by its terms.
## Acknowledgement
Much of the code has been adapted from [the excellent tarchetypes package.](https://github.com/ropensci/tarchetypes) Special
thanks to the authors and Will Landau in particular for revolutionizing data pipelines in R.