RBQL

Enjoy this cheat sheet at its fullest within Dash, the macOS documentation browser.

Supported SQL Keywords

SELECT

The SELECT statement is used to query the database.

WHERE

the WHERE expression is evaluated for each row in the input data as a boolean expression.

ORDER BY … [ DESC | ASC ]

Orders the values returned by a SELECT statement ascending (ASC) or descending (DESC) order.

[ LEFT | INNER ] JOIN

  • INNER JOIN: selects records that have matching values in both tables.
  • LEFT JOIN: selects all records from the left table, and the matching records from the right table.

DISTINCT

Eliminates duplicate rows.

GROUP BY

Groups together rows in a table that have the same values in all the columns listed.

TOP

Specifies the number of records to return.

LIMIT

Places an upper bound on the number of rows returned by the entire SELECT statement.

UPDATE

UPDATE query produces a new table where original values are replaced according to the UPDATE expression, so it can also be considered a special type of SELECT query.

RBQL variables

Value of i-th field in the current record in input table

a1, a2,…, a{N}

Value of i-th field in the current record in join table B

b1, b2,…, b{N}

Record number (1-based)

NR

Number of fields in the current record

NF

Value of the field referenced by it’s “name”, You can use this notation if the field in the header has a “good” alphanumeric name.

a.name, b.Person_age, … a.{Good_alphanumeric_column_name}

Value of the field referenced by it’s “name”, You can use this notation to reference fields by arbitrary values in the header.

a[“object id”], a[‘9.12341234’], b[“%$ !! 10 20”] … a[“Arbitrary column name!”]

Aggregate functions

COUNT

Returns a count of the number of times that X is not NULL in a group.

ARRAY_AGG

Collects all the input values, including nulls, into an array.

MIN

Returns the minimum non-NULL value of all values in the group.

MAX

Returns the maximum value of all values in the group.

SUM

Return sum of all non-NULL values in the group.

AVG

Returns the average value of all non-NULL X within a group.

VARIANCE

Returns the population standard variance.

MEDIAN

Return the median (middle value) of numeric data.

Aggregate statements

JOIN

[ LEFT JOIN and INNER JOIN

Join table B can be referenced either by its file path or by its command - an arbitrary string name the user should provide before executing the JOIN query.
RBQL supports STRICT LEFT JOIN which is like LEFT JOIN, but generates an error if any key in the left table “A” doesn’t have exactly one matching key in the right table “B”.
Table B path can be either relative to the working dir, relative to the main table or absolute. Limitation: JOIN statements can’t contain Python/JS expressions and must have the following form:
<JOIN_KEYWORD> (/path/to/table.tsv | table_command ) ON a… == b… [AND a… == b… [AND … ]]

EXCEPT

Can be used to select everything except specific columns. E.g. to select everything but columns 2 and 4, run: SELECT * EXCEPT a2, a4 Traditional SQL engines do not support this query mode.

WITH

You can set whether the input (and join) CSV file has a header or not using the environment configuration parameters which could be --with_headers CLI flag or GUI checkbox or something else. But it is also possible to override this selection directly in the query by adding either WITH (header) or WITH (noheader) statement at the end of the query. Example: select top 5 NR, * with (header)

UNNEST()

UNNEST(list) takes a list/array as an argument and repeats the output record multiple times - one time for each value from the list argument. Example: SELECT a1, UNNEST(a2.split(';'))

LIKE()

RBQL does not support LIKE operator, instead it provides “like()” function which can be used like this: SELECT * where like(a1, 'foo%bar')

User Defined Functions

User Defined Functions (UDF)

RBQL supports User Defined Functions You can define custom functions and/or import libraries in two special files:
* ~/.rbql_init_source.py - for Python
* ~/.rbql_init_source.js - for JavaScript

Examples

With Python expressions

  1. select top 100 a1, int(a2) * 10, len(a4) where a1 == "Buy" order by int(a2) desc
  2. select * order by random.random() - random sort
  3. select len(a.vehicle_price) / 10, a2 where int(a.vehicle_price) < 500 and a['Vehicle type'] in ["car", "plane", "boat"] limit 20 - referencing columns by names from header and using Python’s “in” to emulate SQL’s “in”
  4. update set a3 = 'NPC' where a3.find('Non-playable character') != -1
  5. select NR, * - enumerate records, NR is 1-based
  6. select * where re.match(".*ab.*", a1) is not None - select entries where first column has “ab” pattern
  7. select a1, b1, b2 inner join ./countries.txt on a2 == b1 order by a1, a3 - example of join query
  8. select MAX(a1), MIN(a1) where a.Name != 'John' group by a2, a3 - example of aggregate query
  9. select *a1.split(':') - Using Python3 unpack operator to split one column into many.
    Do not try this with other SQL engines!

With JavaScript expressions

  1. select top 100 a1, a2 * 10, a4.length where a1 == "Buy" order by parseInt(a2) desc
  2. select * order by Math.random() - random sort
  3. select top 20 a.vehicle_price.length / 10, a2 where parseInt(a.vehicle_price) < 500 && ["car", "plane", * "boat"].indexOf(a['Vehicle type']) > -1 limit 20 - referencing columns by names from header
  4. update set a3 = 'NPC' where a3.indexOf('Non-playable character') != -1*
  5. select NR, * - enumerate records, NR is 1-based*
  6. select a1, b1, b2 inner join ./countries.txt on a2 == b1 order by a1, a3 - example of join query
  7. select MAX(a1), MIN(a1) where a.Name != 'John' group by a2, a3 - example of aggregate query
  8. select ...a1.split(':') - Using JS "destructuring assignment" syntax to split one column into many.
    Do not try this with other SQL engines!

Notes