run_pipeline_dbi {condusco}

A wrapper for running pipelines with a DBI connection invocation query

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)