-
Notifications
You must be signed in to change notification settings - Fork 3
Expand file tree
/
Copy pathREADME.Rmd
More file actions
162 lines (119 loc) · 4.94 KB
/
Copy pathREADME.Rmd
File metadata and controls
162 lines (119 loc) · 4.94 KB
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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
---
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 <img src='man/figures/logo.png' align="right" height="139" />
<!-- 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 -->
sqltargets makes it easy to integrate SQL files within your [targets workflows.](https://github.com/ropensci/targets) The shorthand `tar_sql()` creates two targets: (1) the ‘upstream’ SQL file; and (2) the ‘downstream’ result of the query. Dependencies can be specified by calling `tar_load()` within SQL comments. The template engine can be specified using the `sqltargets.template_engine` option (either 'dbi', 'glue' or 'jinjar'). 'glue' is the default, 'dbi' is the simplest using [placeholders](https://dbi.r-dbi.org/reference/dbBind.html#details), and 'jinjar' is the most powerful.
## 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)
```
## Demo
See the [sqltargets-demo repository](https://github.com/daranzolin/sqltargets-demo) for a reproducible demonstration.
## 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}
library(sqltargets)
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)
```
## Parameters
You can pass parameters (presumably from another object in your targets project) to `tar_sql()` using one of two 'template engines': [glue](https://github.com/tidyverse/glue) or 'Jinja' (courtesy of [the 'jinjar' package.)](https://github.com/davidchall/jinjar)
Set the 'template engine' with `sqltargets_option_set("sqltargets.template_engine", "jinjar")`. ('glue' is the default.)
With glue:
`query.sql`
```sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite())
-- tar_load(params)
select id
from table
where age > {age_threshold}
```
`_targets.R`
```{r eval = FALSE}
library(targets)
library(sqltargets)
list(
tar_target(params, list(age_threshold = 30)),
tar_sql(report, path = "query.sql", params = params)
)
```
With dbi:
`query.sql`
```sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite())
-- tar_load(params)
select id
from table
where age > :age_threshold
-- this could be $1, $age_threshold, or ? for SQLite
```
`_targets.R`
```{r eval = FALSE}
library(targets)
library(sqltargets)
list(
tar_target(params, list(age_threshold = 30)),
tar_sql(report, path = "query.sql", params = params)
)
```
With 'Jinja':
`query.sql`
```sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite())
-- tar_load(payment_methods)
select
order_id,
{% for payment_method in params.payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
{% if not loop.is_last %},{% endif %}
{% endfor %}
from payments
group by 1
```
`_targets.R`
```{r eval = FALSE}
library(targets)
library(sqltargets)
sqltargets_option_set("sqltargets.template_engine", "jinjar")
list(
tar_target(payment_methods, list(payment_methods = c("bank_transfer", "credit_card", "gift_card"))),
tar_sql(report, path = "query.sql", params = payment_methods)
)
```
Note that `loop.is_last` differs from typical Jinja (`loop.last`). Refer to [this 'jinjar' vignette](https://davidchall.github.io/jinjar/articles/template-syntax.html) for other syntactical differences.
## 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.