run_pipeline_dbi: A wrapper for running pipelines with a DBI connection...

In condusco: Query-Driven Pipeline Execution and Query Templates

Description

A wrapper for running pipelines with a DBI connection invocation query

Usage

run_pipeline_dbi(pipeline, query, con, ...)

Arguments

pipeline	User-provided function with one argument, one row of query results
query	A query to execute via the DBI connection
con	The DBI connection
...	Additional arguments passed to dbSendQuery() and dbFetch()

Examples

## Not run: 
library(whisker)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)

#for each cylinder count, count the number of top 5 hps it has
pipeline <- function(params){

  query <- "SELECT
    {{#list}}
    SUM(CASE WHEN hp='{{val}}' THEN 1 ELSE 0 END )as n_hp_{{val}},
  {{/list}}
    cyl
    FROM mtcars
    GROUP BY cyl
  ;"


  dbGetQuery(
    con,
    whisker.render(query,params)
  )
}


#pass the top 5 most common hps as val params
run_pipeline_dbi(
  pipeline,
  '
  SELECT "[" || GROUP_CONCAT("{ ""val"": """ || hp ||  """ }") || "]" AS list
  FROM (
    SELECT
      CAST(hp as INTEGER) as HP,
      count(hp) as cnt
    FROM mtcars
    GROUP BY hp
    ORDER BY cnt DESC
    LIMIT 5
  )
  ',
  con
)


dbDisconnect(con)

## End(Not run)

condusco documentation built on May 2, 2019, 3:50 a.m.