Execute Multi-Step SQL Workflows • qryflow

Overview

qryflow lets you write multi-step SQL workflows in plain .sql files and run them from R with a single function call. Specially formatted tags tell R how to execute each SQL chunk and what to name the results. This allows you to:

Keep multiple SQL statements in the same file.
Control how each SQL “chunk” is executed.
Return results as named R objects.

Install

You can install the released version of qryflow from CRAN with:

install.packages("qryflow")

And the development version from GitHub with:

# install.packages("devtools")
devtools::install_github("christian-million/qryflow")

Example

Basic Usage:

library(qryflow)

# Connection to In-Memory DB with table populated from mtcars
con <- example_db_connect(mtcars)

sql <- "
-- @exec: drop_cyl_6
DROP TABLE IF EXISTS cyl_6;

-- @exec: prep_cyl_6
CREATE TABLE cyl_6 AS
SELECT *
FROM mtcars
WHERE cyl = 6;

-- @query: df_mtcars
SELECT *
FROM mtcars;

-- @query: df_cyl_6
SELECT *
FROM cyl_6;
"

# Pass tagged SQL to `qryflow`
results <- qryflow(con, sql, verbose = TRUE)
#> Running 4 chunks
#> [1/4] drop_cyl_6 [exec]
#>       ✓ success  0s
#> [2/4] prep_cyl_6 [exec]
#>       ✓ success  0s
#> [3/4] df_mtcars [query]
#>       ✓ success  0s
#> [4/4] df_cyl_6 [query]
#>       ✓ success  0s
#> Done in 0.01s — 4 success, 0 error, 0 skipped

# Access the results from the chunk named `df_cyl_6`
head(results$df_cyl_6)
#>    mpg cyl  disp  hp drat    wt  qsec vs am gear carb
#> 1 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
#> 2 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
#> 3 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
#> 4 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
#> 5 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
#> 6 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4

The path to a file containing SQL can also be passed:

filepath <- example_sql_path('mtcars.sql')

# Pass tagged SQL to `qryflow`
results <- qryflow(con, filepath, verbose = TRUE)
#> Running 4 chunks
#> [1/4] drop_cyl_6 [exec]
#>       ✓ success  0s
#> [2/4] prep_cyl_6 [exec]
#>       ✓ success  0s
#> [3/4] df_mtcars [query]
#>       ✓ success  0s
#> [4/4] df_cyl_6 [query]
#>       ✓ success  0s
#> Done in 0.01s — 4 success, 0 error, 0 skipped

# Access the results from the chunk named `df_cyl_6`
head(results$df_cyl_6)
#>    mpg cyl  disp  hp drat    wt  qsec vs am gear carb
#> 1 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
#> 2 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
#> 3 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
#> 4 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
#> 5 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
#> 6 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4

Additional Learning

Consider the following vignettes for a more in depth understanding:

Getting Started: Outlines available features, how to use qryflow, and provides an operational understanding of how it works (vignette("getting-started", package = "qryflow")).
Advanced Usage: A look under the hood at the objects and classes that power qryflow so that you can get more out of the package (vignette("advanced-qryflow", package = "qryflow")).
Extend qryflow: A guide to understanding how to implement custom tags, or override the built-in tags, using custom chunk handlers (vignette("extend-qryflow", package = "qryflow")).

Similar Packages

The functionality made available by qryflow exists in other packages. However, the scope and implementation of qryflow makes it distinct enough to justify a unique package.

I recommend reviewing these other packages to see which works best for your needs. If you feel this list is incomplete, please submit an issue:

sqlhelper provides comprehensive tools for executing parameterized SQL scripts, managing database connections and configurations, supporting spatial data types, and statement-level control within SQL files.
SQLove is ‘a lightweight R package for handling complex SQL scripts including temp tables, multiple queries, etc.’