sqltargets makes it easy to integrate SQL files within your targets workflows. 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.
Parameters can be specified using glue::glue_sql() bracket notation
(‘{}’) (or configured using the
sqltargets.glue_sql_opening_delimiter
and
sqltargets.glue_sql_closing_delimiter
options.
You can install sqltargets from CRAN with:
install.packages("sqltargets")
You can install the development version of sqltargets with:
::install_github("daranzolin/sqltargets) remotes
library(targets)
#> Warning: package 'targets' was built under R version 4.2.2
library(sqltargets)
tar_dir({ #
# Unparameterized SQL query:
<- c(
lines "-- !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(sqltargets)
list(
tar_sql(query, path = "query.sql")
)ask = FALSE)
}, })
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
.
<- c(
lines "-- !preview conn=DBI::dbConnect(RSQLite::SQLite())",
"-- targets::tar_load(data1)",
"-- targets::tar_load(data2)",
"select 1 AS my_col",
""
)<- tempfile()
query writeLines(lines, query)
tar_sql_deps(query)
#> [1] "data1" "data2"
Pass parameters (presumably from another object in your targets
project) from a named list with ‘glue’ syntax: {param}
.
query.sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite())
-- tar_load(query_params)
select id
from table
where age > {age_threshold}
tar_script({
library(targets)
library(sqltargets)
list(
tar_target(query_params, list(age_threshold = 30)),
tar_sql(report, path = "query.sql", query_params = query_params)
)ask = FALSE)
},
tar_visnetwork()
Please note that the sqltargets project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.
Much of the code has been adapted from the excellent tarchetypes package. Special thanks to the authors and Will Landau in particular for revolutionizing data pipelines in R.