OctoSQL

You can install OctoSQL using several methods depending on your environment:

Homebrew (macOS or Linux)

brew install cube2222/octosql/octosql

Note: On macOS, you may need to allow the app in Preferences -> Security and Privacy if it is not notarized.

Nix

To install in your local nix-profile:

nix-env -iA nixpkgs.octosql

To spawn an adhoc shell for testing:

nix-shell -p octosql

For NixOS, add octosql to your systemPackages in your configuration.

Pre-Compiled Binary

Download the binary directly from the GitHub Releases page.

Build from Source

Requires Go version >= 1.18:

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

OctoSQL is a CLI tool that allows you to query various databases and file formats using SQL. You can perform JOINs across different data sources (e.g., joining a JSON file with a PostgreSQL table).

Common Commands

• Query a file: octosql "SELECT * FROM ./path/to/file.json"
• Describe schema: Use the --describe flag to see the schema of a file or table.
• Specify output format: Use the --output flag. Supported values are live_table, batch_table, csv, and stream_native.

octosql "SELECT * FROM ./myfile.json" --describe

octosql "SELECT invoices.id, address, amount
         FROM invoices.csv JOIN db.customers ON invoices.customer_id = customers.id
         ORDER BY amount DESC"

To support databases like PostgreSQL or MySQL, you must install plugins. Plugins are managed via the octosql plugin command.

Installing Plugins

Install the latest version from the default core repository: octosql plugin install <plugin_name>

To install a specific version from a specific repository: octosql plugin install <repository>/<plugin_name>@<version>

Exploring Plugins via SQL

OctoSQL provides several tables to inspect plugin metadata:

• plugins.repositories
• plugins.available_plugins
• plugins.available_versions
• plugins.installed_plugins
• plugins.installed_versions

octosql plugin install postgres

octosql "SELECT name, description FROM plugins.available_plugins LIMIT 2"

Plugins like postgres require configuration. Settings are stored in ~/.octosql/octosql.yml.

Example configuration for a PostgreSQL database:

databases:
  - name: mydb
    type: postgres
    config:
      host: localhost
      port: 5432
      database: postgres
      user: postgres
      password: mypassword

OctoSQL is a query engine that accepts a single SQL query as a command-line argument. It supports SELECT statements and can query various data sources including files (CSV, JSON, Parquet, etc.) and plugins.

Basic Usage:

octosql "SELECT * FROM myfile.json"
octosql "SELECT * FROM mydir/myfile.csv"

OctoSQL is statically typed and supports union types (e.g., Float | String). To handle union types, you can use the following features:

• Type Assertions: Use the value::type syntax to get a value only if it matches the specified type; otherwise, it evaluates to NULL. For example, age::Int extracts the integer value from a String | Int column.
• Conversion Functions: Use functions like int(value) to attempt to convert types (e.g., converting a String to an Int).
• COALESCE: Combine assertions and conversions to clean data. For a column age of type String | Int, you can use COALESCE(age::int, int(age::string), 0) to return the integer, try to parse a string, or default to 0.

Accessing complex types:

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

OctoSQL is a dataflow engine that supports streaming data using Event Times and Watermarks. For GROUP BY queries, you can control when results are emitted using the TRIGGER clause.

Syntax: SELECT ... FROM ... GROUP BY ... TRIGGER [type] [args]

Supported Triggers:

• COUNTING [n]: Sends values every time a given number of records (n) arrive for a key.
• ON WATERMARK: Sends values for keys whenever the Watermark rises above the Event Time of the key.
• ON END OF STREAM: Sends values for all keys when the stream is over (this is the default behavior).

You can combine multiple triggers (e.g., TRIGGER COUNTING 300, ON WATERMARK).

SELECT window_end, user_id, COUNT(*) 
FROM my_table 
GROUP BY window_end, user_id 
TRIGGER COUNTING 300, ON WATERMARK

Table Valued Functions return a stream of Records. When using them, you must alias the result. You can specify arguments using the TABLE(...) operator for tables/subqueries and the DESCRIPTOR(...) operator for field descriptors.

Available Functions:

• range(start, end): Constructs a sequence of integers from start (inclusive) to end (exclusive).
• poll(source, poll_interval?): Periodically polls a finite subquery.
• tumble(source, window_length, time_field?, offset?): Assigns records to tumbling windows.
• max_diff_watermark(source, max_diff, time_field, resolution?): Updates Event Times and emits Watermarks based on the max_diff interval before the latest seen Event Time.

Usage Patterns:

• Use TABLE(...) for simple table names or other TVFs: TABLE(range(start=>1, end=>10)).
• Use TABLE((SELECT ...)) for subqueries.