Execute multi-step ‘SQL’ statements using specially formattedcomments that define and control execution.
qryflow lets you definemulti-step SQLworkflows using comment-based tags in your SQL code. These tagstell R how to execute each SQL chunk and what to name the results. Thisallows you to:
Keep multiple SQL statements in the same file.
Control how each SQL “chunk” is executed.
Return results as named R objects.
Extend behavior using custom tags, parsers, andhandlers.
You can install the released version ofqryflow fromCRAN with:
install.packages("qryflow")And the development version from GitHub with:
# install.packages("devtools")devtools::install_github("christian-million/qryflow")The code below demonstrates the primary use case forqryflow.
Basic Usage:
library(qryflow)# Connection to In-Memory DB with table populated from mtcarscon<-example_db_connect(mtcars)sql<-"-- @exec: drop_cyl_6DROP TABLE IF EXISTS cyl_6;-- @exec: prep_cyl_6CREATE TABLE cyl_6 ASSELECT *FROM mtcarsWHERE cyl = 6;-- @query: df_mtcarsSELECT *FROM mtcars;-- @query: df_cyl_6SELECT *FROM cyl_6;"# Pass tagged SQL to `qryflow`results<-qryflow(sql, con)# 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 4The path to a file containing SQL can also be passed:
filepath<-example_sql_path('mtcars.sql')# Pass tagged SQL to `qryflow`results<-qryflow(filepath, con)# Access the results from the chunk named `df_cyl_6`results$df_cyl_6|>head()#> 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 4Consider the following vignettes for a more in depthunderstanding:
Getting Started: Outlines available features, how to useqryflow, and provides an operational understanding of howit works(vignette("getting-started", package = "qryflow")).
Advanced Usage: A look under the hood at the objects and classesthat powerqryflow so that you can get more out of thepackage(vignette("advanced-qryflow", package = "qryflow")).
Extendqryflow: A guide to understanding how toimplement custom tags, or override the built-in tags, using custom chunkparsers and handlers(vignette("extend-qryflow", package = "qryflow")).
The functionality made available byqryflow exists inother packages. However, the scope and implementation ofqryflow makes it distinct enough to justify a uniquepackage.
I recommend reviewing these other packages to see which works bestfor your needs. If you feel this list is incomplete, please submit anissue:
sqlhelperprovides comprehensive tools for executing parameterized SQL scripts,managing database connections and configurations, supporting spatialdata types, and statement-level control within SQL files.