`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).

`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.frame`

s, 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.frame`

s. - 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`

operatorsThe simple column operations are as follows.

`drop_columns`

`select_columns`

`rename_columns`

These operations are easy to demonstrate.

We set up some simple data.

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

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')) %.>%
::kable(.) knitr
```

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')) %.>%
::kable(.) knitr
```

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')
%.>%
) ::kable(.) knitr
```

x_new_name | y_new_name | z |
---|---|---|

1 | 5 | 6 |

1 | 4 | 7 |

2 | 3 | 8 |

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) %.>%
::kable(.) knitr
```

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') %.>%
::kable(.) knitr
```

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.

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) %.>%
::kable(.) knitr
```

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.

```
<- data.table::shift
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')) %.>%
::kable(.) knitr
```

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.

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') %.>%
::kable(.) knitr
```

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()) %.>%
::kable(.) knitr
```

max_y | count |
---|---|

5 | 3 |

Notes on how to use a variable to specify column names in
`project`

can be found here.

`data.frame`

sTo 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.

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

k | x | y |
---|---|---|

a | 1 | 1 |

a | NA | NA |

b | 3 | NA |

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

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') %.>%
::kable(.) knitr
```

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.”

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

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(.,
. == 1)
row_number <- drop_columns(.,
. "row_number")
::kable(.) knitr
```

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(.,
== 1) ->.;
row_number drop_columns(.,
"row_number") ->.;
::kable(.) knitr
```

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(.,
== 1) %.>%
row_number drop_columns(.,
"row_number") %.>%
::kable(.) knitr
```

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:

```
<- local_td(d) %.>%
ops extend(.,
row_number := row_number(),
partitionby = 'x',
orderby = c('y', 'z')) %.>%
select_rows(.,
== 1) %.>%
row_number 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 ::kable(.) knitr
```

x | y | z |
---|---|---|

1 | 4 | 7 |

2 | 3 | 8 |

And for `SQL`

we have the following.

```
<- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
raw_connection ::initExtension(raw_connection)
RSQLite<- rquery_db_info(
db 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_87263209472242564970_0000000000
## ) tsql_87263209472242564970_0000000001
## WHERE `row_number` = 1
## ) tsql_87263209472242564970_0000000002
```

```
# clean up
::dbDisconnect(raw_connection) DBI
```

For more `SQL`

examples, please see here.

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.

`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:

`Alpha`

~1971.`ISBL`

/ Information system based language ~1973`QUEL`

~1974.`IBM System R`

~1974.`SQL`

~1974.`Tutorial D`

~1994.`data.table`

~2006.`LINQ`

~2007.`pandas`

~2008.`dplyr`

~2014.`Apache Calcite`

~2014.

`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:

`data.table`

`disk.frame`

`dbplyr`

`dplyr`

`dtplyr`

`maditr`

`nc`

`poorman`

`rqdatatable`

`SparkR`

`sparklyr`

`sqldf`

`table.express`

`tidyfast`

`tidyfst`

`tidyquery`

`tidyr`

`tidytable`

(formerly`gdt`

/`tidydt`

)`data_algebra`

To install `rquery`

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

.

`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.