rquery v1.4.6

0

Monthly downloads

0th

Percentile

Relational Query Generator for Data Manipulation at Scale

A piped query generator based on Edgar F. Codd's relational algebra, and on production experience using 'SQL' and 'dplyr' at big data scale. The design represents an attempt to make 'SQL' more teachable by denoting composition by a sequential pipeline notation instead of nested queries or functions. The implementation delivers reliable high performance data processing on large data systems such as 'Spark', databases, and 'data.table'. Package features include: data processing trees or pipelines as observable objects (able to report both columns produced and columns used), optimized 'SQL' generation as an explicit user visible table modeling step, plus explicit query reasoning and checking.

Readme

CRAN\_Status\_Badge status

rquery

rquery is a piped query generator based on Codd’s relational algebra (updated to reflect lessons learned from working with R, SQL, and dplyr at big data scale in production).

Introduction

rquery is a data wrangling system designed to express complex data manipulation as a series of simple data transforms. This is in the spirit of R’s base::transform(), or dplyr’s dplyr::mutate() and uses a pipe in the style popularized in R with magrittr. The operators themselves follow the selections in Codd’s relational algebra, with the addition of the traditional SQL “window functions.” More on the background and context of rquery can be found here.

The R/rquery version of this introduction is here, and the Python/data_algebra version of this introduction is here.

In transform formulations data manipulation is written as transformations that produce new data.frames, instead of as alterations of a primary data structure (as is the case with data.table). Transform system can use more space and time than in-place methods. However, in our opinion, transform systems have a number of pedagogical advantages.

In rquery’s case the primary set of data operators is as follows:

  • drop_columns
  • select_columns
  • rename_columns
  • select_rows
  • order_rows
  • extend
  • project
  • natural_join
  • convert_records (supplied by the cdata package).

These operations break into a small number of themes:

  • Simple column operations (selecting and re-naming columns).
  • Simple row operations (selecting and re-ordering rows).
  • Creating new columns or replacing columns with new calculated values.
  • Aggregating or summarizing data.
  • Combining results between two data.frames.
  • General conversion of record layouts (supplied by the cdata package).

The point is: Codd worked out that a great number of data transformations can be decomposed into a small number of the above steps. rquery supplies a high performance implementation of these methods that scales from in-memory scale up through big data scale (to just about anything that supplies a sufficiently powerful SQL interface, such as PostgreSQL, Apache Spark, or Google BigQuery).

We will work through simple examples/demonstrations of the rquery data manipulation operators.

rquery operators

Simple column operations (selecting and re-naming columns)

The simple column operations are as follows.

  • drop_columns
  • select_columns
  • rename_columns

These operations are easy to demonstrate.

We set up some simple data.

d <- data.frame(
  x = c(1, 1, 2),
  y = c(5, 4, 3),
  z = c(6, 7, 8)
)

knitr::kable(d)
x y z
1 5 6
1 4 7
2 3 8

For example: drop_columns works as follows. drop_columns creates a new data.frame without certain columns.

library(rquery)
## Loading required package: wrapr
library(rqdatatable)

drop_columns(d, c('y', 'z'))
##   x
## 1 1
## 2 1
## 3 2

In all cases the first argument of a rquery operator is either the data to be processed, or an earlier rquery pipeline to be extended. We will take about composing rquery operations after we work through examples of all of the basic operations.

We can write the above in piped notation (using the wrapr pipe in this case):

d %.>%
  drop_columns(., c('y', 'z')) %.>%
  knitr::kable(.)
x
1
1
2

Notice the first argument is an explicit “dot” in wrapr pipe notation.

select_columns’s action is also obvious from example.

d %.>%
  select_columns(., c('x', 'y')) %.>%
  knitr::kable(.)
x y
1 5
1 4
2 3

rename_columns is given as name-assignments of the form 'new_name' = 'old_name':

d %.>%
  rename_columns(., 
                 c('x_new_name' = 'x', 
                   'y_new_name' = 'y')
                 ) %.>%
  knitr::kable(.)
x_new_name y_new_name z
1 5 6
1 4 7
2 3 8

Simple row operations (selecting and re-ordering rows)

The simple row operations are:

  • select_rows
  • order_rows

select_rows keeps the set of rows that meet a given predicate expression.

d %.>%
  select_rows(., x == 1) %.>%
  knitr::kable(.)
x y z
1 5 6
1 4 7

Notes on how to use a variable to specify column names in select_rows can be found here.

order_rows re-orders rows by a selection of column names (and allows reverse ordering by naming which columns to reverse in the optional reverse argument). Multiple columns can be selected in the order, each column breaking ties in the earlier comparisons.

d %.>%
  order_rows(., 
             c('x', 'y'),
             reverse = 'x') %.>%
  knitr::kable(.)
x y z
2 3 8
1 4 7
1 5 6

General rquery operations do not depend on row-order and are not guaranteed to preserve row-order, so if you do want to order rows you should make it the last step of your pipeline.

Creating new columns or replacing columns with new calculated values

The important create or replace column operation is:

  • extend

extend accepts arbitrary expressions to create new columns (or replace existing ones). For example:

d %.>%
  extend(., zzz := y / x) %.>%
  knitr::kable(.)
x y z zzz
1 5 6 5.0
1 4 7 4.0
2 3 8 1.5

We can use = or := for column assignment. In these examples we will use := to keep column assignment clearly distinguishable from argument binding.

extend allows for very powerful per-group operations akin to what SQL calls “window functions”. When the optional partitionby argument is set to a vector of column names then aggregate calculations can be performed per-group. For example.

shift <- data.table::shift

d %.>%
  extend(.,
         max_y := max(y),
         shift_z := shift(z),
         row_number := row_number(),
         cumsum_z := cumsum(z),
         partitionby = 'x',
         orderby = c('y', 'z')) %.>%
  knitr::kable(.)
x y z max_y shift_z row_number cumsum_z
1 4 7 5 NA 1 7
1 5 6 5 7 2 13
2 3 8 3 NA 1 8

Notice the aggregates were performed per-partition (a set of rows with matching partition key values, specified by partitionby) and in the order determined by the orderby argument (without the orderby argument order is not guaranteed, so always set orderby for windowed operations that depend on row order!).

More on the window functions can be found here. Notes on how to use a variable to specify column names in extend can be found here.

Aggregating or summarizing data

The main aggregation method for rquery is:

  • project

project performs per-group calculations, and returns only the grouping columns (specified by groupby) and derived aggregates. For example:

d %.>%
  project(.,
         max_y := max(y),
         count := n(),
         groupby = 'x') %.>%
  knitr::kable(.)
x max_y count
1 5 2
2 3 1

Notice we only get one row for each unique combination of the grouping variables. We can also aggregate into a single row by not specifying any groupby columns.

d %.>%
  project(.,
         max_y := max(y),
         count := n()) %.>%
  knitr::kable(.)
max_y count
5 3

Notes on how to use a variable to specify column names in project can be found here.

Combining results between two data.frames

To combine multiple tables in rquery one uses what we call the natural_join operator. In the rquery natural_join, rows are matched by column keys and any two columns with the same name are coalesced (meaning the first table with a non-missing values supplies the answer). This is easiest to demonstrate with an example.

Let’s set up new example tables.

d_left <- data.frame(
  k = c('a', 'a', 'b'),
  x = c(1, NA, 3),
  y = c(1, NA, NA),
  stringsAsFactors = FALSE
)

knitr::kable(d_left)
k x y
a 1 1
a NA NA
b 3 NA
d_right <- data.frame(
  k = c('a', 'b', 'q'),
  y = c(10, 20, 30),
  stringsAsFactors = FALSE
)

knitr::kable(d_right)
k y
a 10
b 20
q 30

To perform a join we specify which set of columns our our row-matching conditions (using the by argument) and what type of join we want (using the jointype argument). For example we can use jointype = 'LEFT' to augment our d_left table with additional values from d_right.

natural_join(d_left, d_right,
             by = 'k',
             jointype = 'LEFT') %.>%
  knitr::kable(.)
k x y
a 1 1
a NA 10
b 3 20

In a left-join (as above) if the right-table has unique keys then we get a table with the same structure as the left-table- but with more information per row. This is a very useful type of join in data science projects. Notice columns with matching names are coalesced into each other, which we interpret as “take the value from the left table, unless it is missing.”

General conversion of record layouts

Record transformation is “simple once you get it”. However, we suggest reading up on that as a separate topic here.

Composing operations

We could, of course, perform complicated data manipulation by sequencing rquery operations. For example to select one row with minimal y per-x group we could work in steps as follows.

. <- d
. <- extend(.,
            row_number := row_number(),
            partitionby = 'x',
            orderby = c('y', 'z'))
. <- select_rows(.,
                 row_number == 1)
. <- drop_columns(.,
                  "row_number")
knitr::kable(.)
x y z
1 4 7
2 3 8

The above discipline has the advantage that it is easy to debug, as we can run line by line and inspect intermediate values. We can even use the Bizarro pipe to make this look like a pipeline of operations.

d ->.;
  extend(.,
         row_number := row_number(),
         partitionby = 'x',
         orderby = c('y', 'z')) ->.;
  select_rows(.,
              row_number == 1)  ->.;
  drop_columns(.,
               "row_number")    ->.;
  knitr::kable(.)
x y z
1 4 7
2 3 8

Or we can use the wrapr pipe on the data, which we call “immediate mode” (for more on modes please see here).

d %.>%
  extend(.,
         row_number := row_number(),
         partitionby = 'x',
         orderby = c('y', 'z')) %.>%
  select_rows(.,
              row_number == 1)  %.>%
  drop_columns(.,
               "row_number")    %.>%
  knitr::kable(.)
x y z
1 4 7
2 3 8

rquery operators can also act on rquery pipelines instead of acting on data. We can write our operations as follows:

ops <- local_td(d) %.>%
  extend(.,
         row_number := row_number(),
         partitionby = 'x',
         orderby = c('y', 'z')) %.>%
  select_rows(.,
              row_number == 1)  %.>%
  drop_columns(.,
               "row_number")

cat(format(ops))
## mk_td("d", c(
##   "x",
##   "y",
##   "z")) %.>%
##  extend(.,
##   row_number := row_number(),
##   partitionby = c('x'),
##   orderby = c('y', 'z'),
##   reverse = c()) %.>%
##  select_rows(.,
##    row_number == 1) %.>%
##  drop_columns(.,
##    c('row_number'))

And we can re-use this pipeline, both on local data and to generate SQL to be run in remote databases. Applying this operator pipeline to our data.frame d is performed as follows.

d %.>% 
  ops %.>%
  knitr::kable(.)
x y z
1 4 7
2 3 8

And for SQL we have the following.

raw_connection <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
RSQLite::initExtension(raw_connection)
db <- rquery_db_info(
  connection = raw_connection,
  is_dbi = TRUE,
  connection_options = rq_connection_tests(raw_connection))

cat(to_sql(ops, db))
## SELECT
##  `x`,
##  `y`,
##  `z`
## FROM (
##  SELECT * FROM (
##   SELECT
##    `x`,
##    `y`,
##    `z`,
##    row_number ( ) OVER (  PARTITION BY `x` ORDER BY `y`, `z` ) AS `row_number`
##   FROM (
##    SELECT
##     `x`,
##     `y`,
##     `z`
##    FROM
##     `d`
##    ) tsql_29495934854436339343_0000000000
##  ) tsql_29495934854436339343_0000000001
##  WHERE `row_number` = 1
## ) tsql_29495934854436339343_0000000002
# clean up
DBI::dbDisconnect(raw_connection)

For more SQL examples, please see here.

Pipeline principles

What we are trying to illustrate above: there is a continuum of notations possible between:

  • Working over values with explicit intermediate variables.
  • Working over values with a pipeline.
  • Working over operators with a pipeline.

Being able to see these as all related gives some flexibility in decomposing problems into solutions. We have some more advanced notes on the differences in working modalities here and here.

Conclusion

rquery supplies a very teachable grammar of data manipulation based on Codd’s relational algebra and experience with pipelined data transforms (such as base::transform(), dplyr, and data.table).

For in-memory situations rquery uses data.table as the implementation provider (through the small adapter package rqdatatable) and is routinely faster than any other R data manipulation system except data.table itself.

For bigger than memory situations rquery can translate to any sufficiently powerful SQL dialect, allowing rquery pipelines to be executed on PostgreSQL, Apache Spark, or Google BigQuery.

In addition the data_algebra Python package supplies a nearly identical system for working with data in Python. # Background

There are many prior relational algebra inspired specialized query languages. Just a few include:

rquery is realized as a thin translation to an underlying SQL provider. We are trying to put the Codd relational operators front and center (using the original naming, and back-porting SQL progress such as window functions to the appropriate relational operator).

Some related work includes:

Installing

To install rquery please try install.packages("rquery").

Note

rquery is intended to work with “tame column names”, that is column names that are legitimate symbols in R and SQL.

The previous rquery introduction is available here.

Functions in rquery

Name Description
describe_tables Build a nice description of a table.
inspect_join_plan check that a join plan is consistent with table descriptions.
ex Execute a wrapped execution pipeline.
example_employee_date Build some example tables (requires DBI).
if_else_op Build a relop node simulating a per-row block-if(){}else{}.
order_expr_se Make a order_expr node.
columns_used Return columns used
materialize Materialize an optree as a table.
commencify Hyderdrive (science fiction show) synonym for execute
materialize_node Create a materialize node.
assign_slice Assign a value to a slice of data (set of rows meeting a condition, and specified set of columns).
count_null_cols Count NULLs per row for given column set.
extend_se Extend data by adding more columns.
extend Extend data by adding more columns.
db_td Construct a table description from a database source.
execute Execute an operator tree, bringing back the result to memory.
lookup_by_column Use one column to pick values from other columns.
normalize_cols Build an optree pipeline that normalizes a set of columns so each column sums to one in each partition.
expand_grid Cross product vectors in database.
make_assignments Make a list of assignments, applying many functions to many columns.
convert_yaml_to_pipeline Convert a series of simple objects (from YAML deserializaton) to an rquery pipeline.
complete_design Complete an experimental design.
map_column_values Remap values in a set of columns.
mark_null_cols Indicate NULLs per row for given column set.
materialize_sql Materialize a user supplied SQL statement as a table.
drop_columns Make a drop columns node (not a relational operation).
format_node Format a single node for printing.
project project data by grouping, and adding aggregate columns.
project_se project data by grouping, and adding aggregate columns.
mk_td Make a table description directly.
rq_connection_advice Get advice for a DB connection (beyond tests).
rq_connection_name Build a canonical name for a db connection class.
pre_sql_token pre_sql_token
getDBOption Get a database connection option.
rq_connection_tests Try and test database for some option settings.
rq_copy_to Copy local R table to remote data handle.
pre_sql_to_query.pre_sql_token Convert a pre_sql token object to SQL query text.
rquery_apply_to_data_frame Execute optree in an environment where d is the only data.
quote_identifier Quote an identifier.
pre_sql_string pre_sql_string
order_rows Make an orderby node (not a relational operation).
pre_sql_sub_expr pre_sql_sub_expr
graph_join_plan Build a draw-able specification of the join diagram
rsummary Compute usable summary of columns of remote table.
rstr Quick look at remote data
setDBOpt Set a database connection option.
rquery_db_info Build a db information stand-in
quote_literal Quote a value
theta_join_se Make a theta_join node.
to_sql Return SQL implementation of operation tree.
setDBOption Set a database connection option.
if_else_block Build a sequence of statements simulating an if/else block-if(){}else{}.
key_inspector_sqlite Return all primary key columns as guess at preferred primary keys for a SQLite handle.
order_expr Make a order_expr node.
local_td Construct a table description of a local data.frame.
op_diagram Build a diagram of a optree pipeline.
key_inspector_all_cols Return all columns as guess of preferred primary keys.
natural_join Make a natural_join node.
key_inspector_postgresql Return all primary key columns as guess at preferred primary keys for a PostgreSQL handle.
pre_sql_identifier pre_sql_identifier: abstract name of a column and where it is comming from
non_sql_node Wrap a non-SQL node.
pre_sql_fn pre_sql_token funtion name
quantile_cols Compute quantiles of specified columns (without interpolation, needs a database with window functions).
rq_execute Execute a query, typically an update that is not supposed to return results.
quantile_node Compute quantiles over non-NULL values (without interpolation, needs a database with window functions).
rq_function_mappings Return function mappings for a connection
rq_coltypes Get column types by example values as a data.frame.
rq_colnames List table column names.
rquery_default_db_info An example rquery_db_info object useful for formatting SQL without a database connection.
rquery rquery: Relational Query Generator for Data Manipulation
rq_table_exists Check if a table exists.
null_replace Create a null_replace node.
str_pre_sql_sub_expr Structure of a pre_sql_sub_expr
sql_node Make a general SQL node.
orderby Make an orderby node (not a relational operation).
rq_nrow Count rows and return as numeric
rename_columns Make a rename columns node (copies columns not renamed).
row_counts Build an optree pipeline counts rows.
pick_top_k Build an optree pipeline that selects up to the top k rows from each group in the given order.
tokenize_for_SQL Cross-parse from an R parse tree into SQL.
to_transport_representation Convert an rquery op diagram to a simple representation, appropriate for conversion to YAML.
rq_remove_table Remove table
topo_sort_tables Topologically sort join plan so values are available before uses.
unionall Make an unionall node (not a relational operation).
pre_sql_to_query.pre_sql_sub_expr Convert a pre_sql token object to SQL query text.
quote_table_name Quote a table name.
quote_string Quote a string
pre_sql_to_query Return SQL transform of tokens.
select_columns Make a select columns node (not a relational operation).
rsummary_node Create an rsumary relop operator node.
rq_get_query Execute a get query, typically a non-update that is supposed to return results.
rq_head Get head of db table
tables_used Return vector of table names used.
sql_expr_set Build a query that applies a SQL expression to a set of columns.
rquery_default_methods Default to_sql method implementations.
set_indicator Make a set indicator node.
wrap Wrap a data frame for later execution.
theta_join Make a theta_join node.
select_rows_se Make a select rows node.
select_rows Make a select rows node.
apply_right_S4,ANY,rquery_db_info-method Apply pipeline to a database.
arrow Data arrow
apply_right_S4,relop_arrow,relop_arrow-method S4 dispatch method for apply_right.
actualize_join_plan Execute an ordered sequence of left joins.
apply_right_S4,data.frame,relop_arrow-method S4 dispatch method for apply_right.
affine_transform Implement an affine transformaton
apply_right.relop Execute pipeline treating pipe_left_arg as local data to be copied into database.
build_join_plan Build a join plan.
column_names Return column names
No Results!

Vignettes of rquery

Name
AssigmentPartitioner.Rmd
Parameterized_rquery.Rmd
PipeableSQL.Rmd
QueryGeneration.Rmd
R_mapping.Rmd
parameterized_rquery.png
present-2.png
rquery_intro.Rmd
rquery_many_columns.Rmd
rquery_substitution.Rmd
runtimes_1.png
sql_quoting.Rmd
No Results!

Last month downloads

Details

Type Package
Date 2020-10-17
URL https://github.com/WinVector/rquery/, https://winvector.github.io/rquery/
BugReports https://github.com/WinVector/rquery/issues
License GPL-2 | GPL-3
Encoding UTF-8
LazyData true
RoxygenNote 7.1.1
ByteCompile true
VignetteBuilder knitr
NeedsCompilation no
Packaged 2020-10-17 17:10:23 UTC; johnmount
Repository CRAN
Date/Publication 2020-10-17 17:40:02 UTC

Include our badge in your README

[![Rdoc](http://www.rdocumentation.org/badges/version/rquery)](http://www.rdocumentation.org/packages/rquery)