In colin-fraser/queries: Store and Manage Parameterized Queries

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)

queries

I have a number of parameterized SQL queries that I run regularly. The purpose of this package is to keep canonical versions of those queries with standardized parameterization. It uses the glue package to replace parameters.

Usage

The package reads modified sql files that have metadata stored in yaml format in a comment block at the top. Here's a simple example: start with a file like [tests/testthat/example_with_defaults.sql](tests/testthat/example_with_defaults.sql).

cat(readr::read_file("tests/testthat/example_with_defaults.sql"))

The header is written in yaml format. I can import this into R with

library(queries)
query <- query_load("tests/testthat/example_with_defaults.sql")

and then plug in parameters with

query_substitute(query, metrics = c(total_sales = "SUM(Sales)", avg_sales = "AVG(Sales)")) %>% 
  cat

Notice that vector names are converted to column identifiers.

Defaults are applied as specified in the yaml header. You can also create a function that completes the query:

qf <- query_as_function(query)
qf(dimensions = c("Country", "Segment", "Product"), metrics = c(Sales = "SUM(Sales)"))

The created function has arguments corresponding to the params as specified in the yaml header.

print(args(qf))

RStudio will see these and autocomplete them, which is convenient. When I forget a parameter, it tells me.

qf(dimensions = "Segment")

Big picture usage

Query Library

I use this in two ways. First, I have a library of queries stored in a repository—say ~/projects/path/to/queries. These are canonical versions of parameterized queries that my team uses. In my .Rprofile, I have a line that sets the default query location to that path

options(default_queries_location='~/projects/path/to/queries')

Which contains files

• sales_by_group.sql
• customer_list.sql
• etc...

Whenever I need to run one of these queries, I build the query with

q <- load_query('sales_by_group')

If I don't remember all the parameters, I can view the header with head:

head(query)

R Projects

The other way that I use this is to store bespoke queries in a /sql directory in an R project. load_query will look for a /sql directory first, then check whether the default_query_location option is set up.

Installation

Install with devtools:

devtools::install_github("colin-fraser/queries")

colin-fraser/queries documentation built on April 14, 2022, 2:12 p.m.