SuperDB Documentation

SuperSQL is a Pipe SQL language adapted for super-structured data. It is a superset of standard SQL query syntax and includes a modern type system with sum types to handle heterogeneous data.

Queries follow a Unix-like pipeline pattern where a data source is followed by a series of operators that manipulate the data:

from source | operator | operator | ...

While the syntax implies a linear dataflow, the SuperSQL compiler can optimize the query execution for better performance.

SuperDB is an analytics database designed to handle relational tables and JSON on equal footing. It is optimized for data wrangling, exploring, and processing large, heterogeneous (eclectic) datasets.

Key characteristics include:

• Unified Data Model: All ingested data (JSON, CSV, Parquet, Arrow, etc.) is converted into super-structured data. This allows a single compute engine to support both statically-typed relational models and dynamically-typed JSON models.
• Strong/Dynamic Typing: Unlike standard document models, every value in SuperDB is strongly and dynamically typed, providing both flexibility and performance.
• Schema Flexibility: SuperDB does not rely on fragile schema inference for JSON; it ingests data in its natural form, allowing for high heterogeneity without failure.

SuperSQL aims for high compatibility with the PostgreSQL dialect. It is validated using sqllogictest queries from SQLite.

As of the first GA release, SuperSQL achieves a 99.88% success rate against eligible SQLite queries compared to PostgreSQL.

Known compatibility issues/limitations:

• Correlated subqueries
• INTERSECT and EXCEPT operators
• Large cartesian products (performance issues)
• NULL values absent from JOIN output
• Signed zero handling
• Promoting return types to a common type of arguments

SuperDB uses a unified data model for super-structured data, allowing for lossless conversions between human-readable, row-based binary, and columnar formats. The three primary formats are:

• Super (SUP): A human-readable format. It is a strict superset of JSON, meaning all JSON documents are valid SUP values.
• Super Binary (BSUP): A row-based binary representation (similar to Avro) designed to represent a sequence of arbitrarily-typed values.
• Super Columnar (CSUP): A columnar format (similar to Parquet, ORC, or Arrow) optimized for super-structured data.

Because these formats all implement the same underlying data model, you can convert data between them without losing information.

SuperSQL is the extended SQL dialect used by SuperDB. It extends standard SQL with pipe syntax and various functional shortcuts to facilitate complex data transformations.

In SuperSQL, you can chain operations using the pipe (|) operator, allowing for a flow-based query style similar to Unix pipes. This is particularly useful for complex joins, subqueries, and data reshaping.

FROM 'https://data.gharchive.org/2015-01-01-15.json.gz'
| SELECT union(repo.name) AS repos, actor.login AS user
  GROUP BY user
  ORDER BY len(repos) DESC
  LIMIT 5
| FORK
  ( FROM f'https://api.github.com/users/{user}'
    SELECT login AS user, created_at::time AS created_at )
  ( PASS )
| JOIN USING (user)
| VALUES {...left,repos:right.repos}

Data shaping is the process of unifying heterogeneous data sources by transforming them to fit a well-defined schema. In SuperSQL, shaping involves converting leaf field types, adding or removing fields, and reordering fields to match a target type. This is essential for cleaning up disparate data for use in a unified store like a data warehouse.

Core shaping functions include:

• cast: Coerces values to a different type.
• crop: Removes fields not present in the target type.
• fill: Adds missing fields with null values.
• order: Reorders fields to match the target type.
• shape: A composite function that applies cast, fill, and order in a single step.

Super-structured data is the core data model of SuperDB. It unifies relational tables and dynamic JSON into a single, powerful model. Unlike traditional relational models that struggle with mixed-type JSON, super-structured data is:

• Dynamic: Data collections can vary by type and are not constrained by rigid schemas.
• Strongly Typed: Allows for static type checking and type-based query optimizations even on dynamic data.
• Self-describing: Eliminates the need to define schemas upfront, as the data carries its own type information.

In this model, a relational table is simply a collection of uniformly typed records, while dynamic data (like logs or events) can be modeled as sequences of varying JSON-like values. This allows you to mix and match formats like Parquet and JSON seamlessly in a single query.

SuperSQL uses strong typing for all data. It supports two modes of type checking:

1. Static Type Checking: For data conforming to a schema, types are checked at compile time. This works for traditional relational SQL and super-structured pipe queries.
2. Fused Type Checking: For dynamic data (like JSON or CSV), the compiler uses a technique called fused types. It interrogates data sources to compute a fused type on the fly and performs type checking based on these types rather than a fixed relational schema.

If a file is too large for the compiler to compute fused types efficiently, the process may be skipped (based on a configurable limit), which may result in runtime errors instead of compile-time errors.