octosql

OctoSQL

OctoSQL is predominantly a CLI tool which lets you query a plethora of databases and file formats using SQL through a unified interface, even do JOINs between them. (Ever needed to join a JSON file with a PostgreSQL table? OctoSQL can help you with that.)

At the same time it's an easily extensible full-blown dataflow engine, and you can use it to add a SQL interface to your own applications.

Usage

octosql "SELECT * FROM ./myfile.json"
octosql "SELECT * FROM ./myfile.json" --describe  # Show the schema of the file.
octosql "SELECT invoices.id, address, amount
         FROM invoices.csv JOIN db.customers ON invoices.customer_id = customers.id
         ORDER BY amount DESC"
octosql "SELECT customer_id, SUM(amount)
         FROM invoices.csv
         GROUP BY customer_id"

OctoSQL supports a bunch of file formats out of the box, but you can additionally install plugins to add support for other databases.

octosql "SELECT * FROM plugins.available_plugins"
octosql plugin install postgres
echo "databases:
  - name: mydb
    type: postgres
    config:
      host: localhost
      port: 5443
      database: mydb
      user: postgres
      password: postgres" > octosql.yml
octosql "SELECT * FROM mydb.users" --describe
octosql "SELECT * FROM mydb.users"

You can specify the output format using the --output flag. Available values for it are live_table, batch_table, csv and stream_native.

The documentation about available aggregates and functions is contained within OctoSQL itself. It's in the aggregates, aggregate_signatures, functions and function_signatures tables in the docs database.

…

Installation

Homebrew

You can install OctoSQL using Homebrew on MacOS or Linux:

brew install cube2222/octosql/octosql

After running it for the first time on MacOS you'll have to go into Preferences -> Security and Privacy -> Allow OctoSQL, as with any app that's not notarized.

Pre-Compiled binary

You can also download the binary for your operating system directly from the Releases page.

Nix Package

The package can be installed in the local nix-profile.

nix-env -iA nixpkgs.octosql

For adhoc or testing purposes a shell with the package can be spawned.

nix-shell -p octosql

For NixOS users it is highly recommended to install the package by adding it to the list of systemPackages.

environment.systemPackages = with pkgs; [
  octosql
  # ...
];

Building from source

With Go in version >= 1.18 the application can be built from source. This can be achieved by cloning the repository and running go install from the project directory.

git clone https://github.com/cube2222/octosql
cd octosql
go install

File Access

Support for multiple file types is included by default in OctoSQL:

• JSON (in JSONLines format, one object per line)
• CSV
• TSV
• Parquet
• Lines (reading a file line by line)

If your file has a matching extension, you can use its path directly as a table:

~> octosql "SELECT * FROM my/file/path.json"

or, if the extension is not right, you can use this alternative notation, where the extension is used in place of the database name:

~> octosql "SELECT * FROM `json.my/file/path.whatever`"

You can also specify additional options using the following notation: myfile.ext?key=value&key2=value2

The following options are available:

• CSV
  • header: true/false (default: true) - Whether the file has a header row.
• JSON
  • tail: true/false (default: false) - Whether to keep waiting for new content after reaching the end of the file.
• Lines
  • tail: true/false (default: false) - Whether to keep waiting for new content after reaching the end of the file.

Reading from Standard Input

You can also pipe data in through stdin, and OctoSQL will expose it as the stdin.<file_type> table. For example:

~> echo '{"hello": "world"}' | octosql "SELECT * FROM stdin.json"
+---------+
|  hello  |
+---------+
| 'world' |
+---------+
~> seq 100 | octosql "SELECT SUM(int(text)) FROM stdin.lines"
+------+
| sum  |
+------+
| 5050 |
+------+

Plugins

To use databases which are not included in the core of OctoSQL - like PostgreSQL or MySQL - you need to install a plugin. Installing plugins is very easy. The following command installs the latest version of the PostgreSQL plugin:

octosql plugin install postgres

Plugins are grouped into repositories, and potentially have many versions available. The above uses the default core repository and tries to install the latest version. So if 0.42.0 was the latest version, the above would be equivalent to:

octosql plugin install core/[email protected]

Browsing available and installed plugins is possible through OctoSQL itself, behind a SQL interface. The available tables are: plugins.repositories, plugins.available_plugins, plugins.available_versions, plugins.installed_plugins, plugins.installed_versions.

…

Some plugins, like the random_data plugin, can be used without any additional configuration:

…

Others, like the postgres plugin, require additional configuration. The configuration file is located at ~/.octosql/octosql.yml. You can find the available configuration settings for a plugin in its own documentation.

…

In order to create your own plugins, see examples of existing plugins:

• MySQL
• PostgreSQL
• Random Data

To test plugins while developing locally, put the plugin binary into ~/.octosql/plugins/core/octosql-plugin-<plugin name>/0.1.0/octosql-plugin-<plugin name>. That's the location where OctoSQL will be looking for it.

Troubleshooting

OctoSQL writes logs to ~/.octosql/logs.txt, which is the place to look for any errors or issues during execution. Only logs of the most recent execution are kept.

Advanced Features

The Type System

OctoSQL is statically typed. That means that queries are verified, typechecked, and optimized based on the schemas of the tables and types of any values used in the query.

Most of the type system is straight-forward and intuitive, similar to what you'd find in other SQL dialects, even though the types have names which are closer to common programming languages, not SQL databases - in OctoSQL you'll find String's, not varchar's.

However, OctoSQL also supports union types, which means that a value might be one of multiple types. For example, you might have a dataset where a column is usually a Float, but occasionally also a String with the Float inside. Thus, the type of the column would be Float | String.

Moreover, NULL is its own type, which means that a nullable Int column would be represented as Int | NULL in OctoSQL.

There's a few helper features to handle this union types in OctoSQL.

First, whenever a type, i.e. String | Int, is used in a place where a subtype, i.e. Int, is expected, OctoSQL will add a dynamic runtime check which will fail execution only if a String value actually ever reaches that place.

Second, you can use type assertions to get a value only if it's of a certain type and otherwise evaluate to NULL. The syntax for that is value::type. So for example we might have a column age of type String | Int and would like to get its value only if it's an Int. We can write age::Int to express that.

Third, there's a bunch of conversion functions which can help you turn types into other types. For example, the int function is able to turn values of many types, including strings, to integers. You could use it like this: int(age_string).

Fourth, and final, there's the COALESCE operator which accepts an arbitrary number of arguments and returns the first non-null one. It works very well with what's described in the previous two paragraphs. This way, if you have an age column of type String | Int and would like to clean it up, you can write COALESCE(age::int, int(age::string), 0). This would return the value of age as-is if it's an Int, try to parse it if it's a String, and just evaluate to 0 if that fails.

Additionally, you can work with objects and lists using the following syntax:

• List access: list[index]
• Object field access: object->field

Explaining Query Plans

You can use the --explain flag to get a visual explanation of the query plan. Setting it to 1 gives you a query plan but without type and schema information, setting it to 2 includes those too. For the visualization to work you need to have the graphviz dot command installed.

For example, running:

octosql "SELECT email, COUNT(*) as invoice_count
         FROM invoices.csv JOIN mydb.customers ON invoices.customer_id = customers.id
         WHERE first_name <= 'D'
         GROUP BY email
         ORDER BY invoice_count DESC" --explain 1

will produce the following output:

Here we can see that the first_name <= 'D' predicate has been pushed down to the mydb.customers table query.

Dataflow

OctoSQL is a dataflow engine. In practice that means it can execute a query and then update it based on changes in the inputs. The way this works in practice is by using retractions, each record sent internally has a retraction flag dictating whether it's an undo or not.

This also means that OctoSQL can work very well with endless streams of data, or display partial results before calculating the full query.

In order to handle that well, OctoSQL contains the concept of record event times and watermarks. Each Record can have an Event Time (the time when it originally happened). Watermarks (special metadata records) are used to signal the timestamp that will be the lower bound for all future records. I.e. if you have a watermark of 2021-12-13T00:11:03Z then all future records will have an event time that is past that timestamp. This let's us understand which Records cannot be retracted anymore, and is very useful when grouping by time windows.

OctoSQL is also internally consistent as defined by this article. This means that the live output at any given time will be a correct output for a common time-prefix of all the inputs. If you're using the stream-native output, this guarantee is satisfied whenever a watermark is emitted. (you can treat watermarks as atomic transactions as far as the output is concerned)

That means, that if you have two input streams, and one input stream is a few minutes behind the other, so for example its watermark value is 2021-12-13T00:11:03Z and the watermark of the other one is 2021-12-13T00:11:07Z, then the output of OctoSQL at that time will be a correct output based on all events up to 2021-12-13T00:11:03Z from both streams. The records in the second stream between 2021-12-13T00:11:03Z and 2021-12-13T00:11:07Z will be buffered until the first stream catches up.

For GROUP BY queries you can specify when you want to udpate the output using the TRIGGER clause: SELECT ... FROM ... GROUP BY ... TRIGGER COUNTING 300, ON WATERMARK, ON END OF STREAM. You can use the Counting Trigger and/or the Watermark Trigger and/or the End Of Stream Trigger; it defaults to the End Of Stream trigger.

The Watermark Trigger sends values for keys whenever the Watermark rises above the Event Time of the key. The Counting Trigger sends values every time a given number of records arrive for a key. The End Of Stream Trigger sends values for all keys when the stream is over.

We can take a look at an example query which simulates a stream using a JSON file:

…

It uses Table Valued Functions extensively.

First we create a stream o