daranzolin / sqltargets

targets extension for SQL queries

sqltargets

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.

Installation

You can install sqltargets from CRAN with:

install.packages("sqltargets")

You can install the development version of sqltargets with:

remotes::install_github("daranzolin/sqltargets)

Example

library(targets)
#> Warning: package 'targets' was built under R version 4.2.2
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.

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)
#> [1] "data1" "data2"

Passing parameters

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(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. 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. Special thanks to the authors and Will Landau in particular for revolutionizing data pipelines in R.

sqltargets's Issues

Add tests

• tar_manifest() returns correct command
• tar_sql_deps() return correct deps
• target will rerun when SQL file changes
• tar_sql() works
• query_params passed correctly to query
• query_params are a recognized dependency

Shorthand to write targets to a database

Maybe:

...
tar_plan(
  cars = mtcars,
  flowers = iris,
  tar_write_to_db(tables_loaded, cars, flowers)
  tar_sql(report, "inst/query.sql")
)
...

Then the db dependency could be specified in query.sql instead of individual tables?

-- tar_load(tables_loaded)
...

detect changes in a database?

Could a database or database connection be a dependency? How would that work?

glue_sql detects parameters within comments

While not a bug per tidyverse/glue#280, I think tar_sql should be able to ignore them.

tar_make skips target even when the query file changes

tar_prql shorthand?