tar_sql_raw: Target with a SQL query.

In sqltargets: 'Targets' Extension for 'SQL' Queries

Target with a SQL query.

Description

Shorthand to include a SQL query in a 'targets' pipeline.

Usage

tar_sql_raw(
  name,
  path = ".",
  query_params = query_params,
  format = format,
  error = targets::tar_option_get("error"),
  memory = targets::tar_option_get("memory"),
  garbage_collection = targets::tar_option_get("garbage_collection"),
  deployment = "main",
  priority = targets::tar_option_get("priority"),
  resources = targets::tar_option_get("resources"),
  retrieval = targets::tar_option_get("retrieval"),
  cue = targets::tar_option_get("cue"),
  deps = deps
)

Arguments

name	Character of length 1, name of the target. A target name must be a valid name for a symbol in R, and it must not start with a dot. Subsequent targets can refer to this name symbolically to induce a dependency relationship: e.g. tar_target(downstream_target, f(upstream_target)) is a target named downstream_target which depends on a target upstream_target and a function f(). In addition, a target's name determines its random number generator seed. In this way, each target runs with a reproducible seed so someone else running the same pipeline should get the same results, and no two targets in the same pipeline share the same seed. (Even dynamic branches have different names and thus different seeds.) You can recover the seed of a completed target with tar_meta(your_target, seed) and run set.seed() on the result to locally recreate the target's initial RNG state.
path	Character of length 1 to the single '*.sql' source file to be executed. Defaults to the working directory of the 'targets' pipeline.
query_params	A named list of parameters for parameterized queries.
format	Optional storage format for the target's return value. With the exception of format = "file", each target gets a file in ⁠_targets/objects⁠, and each format is a different way to save and load this file. See the "Storage formats" section for a detailed list of possible data storage formats.
error	Character of length 1, what to do if the target stops and throws an error. Options: "stop": the whole pipeline stops and throws an error. "continue": the whole pipeline keeps going. "abridge": any currently running targets keep running, but no new targets launch after that. (Visit https://books.ropensci.org/targets/debugging.html to learn how to debug targets using saved workspaces.) "null": The errored target continues and returns NULL. The data hash is deliberately wrong so the target is not up to date for the next run of the pipeline.
memory	Character of length 1, memory strategy. If "persistent", the target stays in memory until the end of the pipeline (unless storage is "worker", in which case targets unloads the value from memory right after storing it in order to avoid sending copious data over a network). If "transient", the target gets unloaded after every new target completes. Either way, the target gets automatically loaded into memory whenever another target needs the value. For cloud-based dynamic files (e.g. format = "file" with repository = "aws"), this memory strategy applies to the temporary local copy of the file: "persistent" means it remains until the end of the pipeline and is then deleted, and "transient" means it gets deleted as soon as possible. The former conserves bandwidth, and the latter conserves local storage.
garbage_collection	Logical, whether to run base::gc() just before the target runs.
deployment	Character of length 1, only relevant to tar_make_clustermq() and tar_make_future(). If "worker", the target builds on a parallel worker. If "main", the target builds on the host machine / process managing the pipeline.
priority	Numeric of length 1 between 0 and 1. Controls which targets get deployed first when multiple competing targets are ready simultaneously. Targets with priorities closer to 1 get built earlier (and polled earlier in tar_make_future()).
resources	Object returned by tar_resources() with optional settings for high-performance computing functionality, alternative data storage formats, and other optional capabilities of targets. See tar_resources() for details.
retrieval	Character of length 1, only relevant to tar_make_clustermq() and tar_make_future(). Must be one of the following values: "main": the target's dependencies are loaded on the host machine and sent to the worker before the target builds. "worker": the worker loads the targets dependencies. "none": the dependencies are not loaded at all. This choice is almost never recommended. It is only for niche situations, e.g. the data needs to be loaded explicitly from another language.
cue	An optional object from tar_cue() to customize the rules that decide whether the target is up to date.
deps	Optional character vector of the adjacent upstream dependencies of the target, including targets and global objects. If NULL, dependencies are resolved automatically as usual.

Details

'tar_sql()' is an alternative to 'tar_target()' for SQL queries that depend on upstream targets. The SQL source files ('*.sql' files) should mention dependency targets with 'tar_load()' within SQL comments ('–'). (Do not use 'tar_load_raw()' or 'tar_read_raw()' for this.) Then, 'tar_sql()' defines a special kind of target. It 1. Finds all the 'tar_load()'/'tar_read()' dependencies in the query and inserts them into the target's command. This enforces the proper dependency relationships. (Do not use 'tar_load_raw()' or 'tar_read_raw()' for this.) 2. Sets 'format = "file"' (see 'tar_target()') so 'targets' watches the files at the returned paths and reruns the query if those files change. 3. Creates another upstream target to watch the query file for changes '<target name> ‘sqltargets_option_get("sqltargets.target_file_suffix")'’.

Value

A data frame

Examples

targets::tar_dir({  # tar_dir() runs code from a temporary directory.
  # Unparameterized SQL query:
  lines <- c(
    "-- !preview conn=DBI::dbConnect(RSQLite::SQLite())",
    "-- targets::tar_load(data1)",
    "-- targets::tar_load(data2)",
    "select 1 AS my_col",
    ""
  )
  # In tar_dir(), not part of the user's file space:
  writeLines(lines, "query.sql")
  # Include the query in a pipeline as follows.
  targets::tar_script({
    library(tarchetypes)
    library(sqltargets)
    list(
      tar_sql(query, path = "query.sql")
    )
  }, ask = FALSE)
})

sqltargets documentation built on June 22, 2024, 10:54 a.m.