The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.
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.
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")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 0s — 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 4The 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 0s — 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 4Consider 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")).
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")).
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.’
These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.