MonoTS SQL Reference

This page documents the SQL syntax, data types, and operational rules supported by MonoTS. The query engine is built on Apache DataFusion and supports a large subset of standard SQL, with edge time-series optimizations underneath.

For CDC stream SQL (CREATE STREAM, sink properties), see the Edge-to-Cloud Sync Guide and Streams reference.

1. Core concept: the time column

As a time-series database, MonoTS enforces a mandatory time column on every table:

Rule	Detail
Required	Every table must include a column named exactly time
Types	Must be BIGINT or TIMESTAMP (precisions / time zones supported)
Nullability	Always NOT NULL
Engine use	Memtable ordering and Parquet time pruning during queries

Without a usable time range in filters, the engine cannot prune Parquet files efficiently.

2. Data types

MonoTS maps SQL types to Apache Arrow memory formats.

Numeric & Boolean

SQL type	Description	Notes
TINYINT / SMALLINT / INT / BIGINT	Signed integers	Status codes, counters
TINYINT UNSIGNED … BIGINT UNSIGNED	Unsigned integers	Non-negative metrics
FLOAT / REAL / DOUBLE	Floating point	Temperature, voltage, continuous signals
BOOLEAN	Boolean	TRUE / FALSE
DECIMAL(p,s) / NUMERIC(p,s)	Fixed-point decimal	Precision p up to 38; bare DECIMAL defaults to DECIMAL(38,10)

Strings & Binary

SQL type	Description	Notes
VARCHAR / CHAR / TEXT	UTF-8 text	e.g. sensor-1, us-west-edge
LARGETEXT / LONGTEXT / LARGEUTF8	Large UTF-8 text
BLOB / BINARY / VARBINARY / BYTES	Binary	Small serialized payloads
LARGEBLOB / LONGBLOB / LARGEBINARY	Large binary

Date & Timestamp

SQL type	Description	Notes
DATE	Date	Insert as 'YYYY-MM-DD' strings
TIMESTAMP / TIMESTAMP(p)	Timestamp	Default precision is milliseconds
TIMESTAMP … WITH TIME ZONE	Timestamp with TZ	Normalized and stored as UTC

Timestamp insert rule

For TIMESTAMP columns, INSERT VALUES does not accept ISO-8601 strings such as '2023-10-01T12:00:00Z'. Pass a raw epoch integer that matches the column precision (for example 1718000000000 for millisecond timestamps).

Nested types

SQL type	Description	Notes
ENUM('a','b',…)	Dictionary-backed enum	e.g. ENUM('running','stopped','error')
ARRAY<T> / T[]	Array / list	e.g. ARRAY<VARCHAR> for tags
STRUCT<field T, …>	Nested struct	Flattened JSON-like payloads

Unsupported types

UUID, JSON, and SQL Interval are not supported today and return an unsupported-type error.

3. Data Definition Language (DDL)

CREATE TABLE

Always include the time column.

CREATE TABLE edge_metrics (
  time BIGINT,
  device_id VARCHAR,
  temperature DOUBLE,
  status ENUM('active', 'idle', 'maintenance'),
  metadata ARRAY<VARCHAR>
);

Notes:

• All columns except time are nullable by default.
• Explicit column-level NOT NULL (other than on time) is currently ignored.
• Not supported: PRIMARY KEY, UNIQUE, FOREIGN KEY, CREATE TABLE IF NOT EXISTS, partitioning WITH (...).

ALTER TABLE

Only ADD COLUMN is supported. New columns are nullable so historical Parquet files can be padded safely.

ALTER TABLE edge_metrics ADD COLUMN region VARCHAR;

Not supported: DROP COLUMN, RENAME, ALTER COLUMN, type changes.

DROP TABLE

DROP TABLE IF EXISTS staging;

4. Data Manipulation Language (DML)

INSERT INTO

Only INSERT INTO … VALUES is supported (INSERT … SELECT is not).

INSERT INTO edge_metrics (time, device_id, temperature, status)
VALUES
  (1718000000000, 'sensor-1', 21.5, 'active'),
  (1718000005000, 'sensor-2', 22.1, 'idle');

Literal rules:

• Arrays: ARRAY['a','b'] or JSON string '["a","b"]'
• Structs: tuple ('alice', 90) or JSON object string '{"name":"alice","score":90}'
• Enums: must match a declared variant exactly

High-volume ingest

For high throughput, prefer the Rust SDK write_batch APIs. The SDK packs Arrow batches and sorts by time before sending over gRPC.

LOAD PARQUET

Load local Parquet files directly into a table (useful for cold start and recovery). Files skip the memtable and register into the catalog.

LOAD PARQUET '/data/import/historical_metrics_part-000.parquet' INTO edge_metrics;
LOAD PARQUET '/data/batch_folder/' INTO TABLE edge_metrics;

Requires exact schema alignment, including a valid time column.

FLUSH

Force the active memtable to seal into local Parquet SST files.

FLUSH TABLE edge_metrics;  -- one table
FLUSH TABLES;              -- all tables

Batch CDC sinks (Delta / filesystem) only see sealed Parquet data after flush (or automatic size-based flush).

5. Queries (DQL)

Queries run through Apache DataFusion. Prefer a time range in WHERE so MonoTS can prune Parquet files before scanning.

SELECT syntax

SELECT [DISTINCT] <expression_list>
FROM <table>
[ <join_type> JOIN <table> ON <join_condition> ]...
[WHERE <predicate>]
[GROUP BY <expression_list>]
[HAVING <predicate>]
[ORDER BY <expression> [ASC|DESC], ...]
[LIMIT <n>]
[OFFSET <m>]

<join_type> can be INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER], or CROSS (no ON for CROSS JOIN).

Supported query capabilities

Area	Supported
Projection	Column lists, aliases, expressions, DISTINCT
Filtering	WHERE with AND / OR / IN / BETWEEN / comparisons
Aggregation	COUNT, SUM, AVG, MIN, MAX
Grouping	GROUP BY, HAVING
Ordering / paging	ORDER BY, LIMIT, OFFSET
Joins	INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS; self-joins and multi-table joins
Window functions	e.g. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and windowed aggregates (SUM() OVER (...))

Architecture constraints

• Time pruning: always include a time range when possible.
• Single-threaded execution: target_partitions = 1 to respect edge memory limits.
• Immutable rows: UPDATE and DELETE are not supported.
• No information_schema: use SHOW commands instead.

Aggregation and filtering example

SELECT
  device_id,
  COUNT(*) AS data_points,
  MAX(temperature) AS peak_temp,
  AVG(temperature) AS avg_temp
FROM edge_metrics
WHERE time >= 1718000000000 AND time < 1718086400000
GROUP BY device_id
HAVING COUNT(*) >= 1
ORDER BY peak_temp DESC
LIMIT 20;

Join examples

-- INNER JOIN
SELECT
  m.time,
  m.device_id,
  m.temperature,
  d.site
FROM edge_metrics AS m
INNER JOIN device_dim AS d ON m.device_id = d.device_id
WHERE m.time >= 1718000000000 AND m.time < 1718086400000;

-- LEFT OUTER JOIN
SELECT m.device_id, d.site
FROM edge_metrics AS m
LEFT OUTER JOIN device_dim AS d ON m.device_id = d.device_id
WHERE m.time >= 1718000000000;

-- CROSS JOIN
SELECT m.device_id, t.tag
FROM edge_metrics AS m
CROSS JOIN tags AS t
WHERE m.time >= 1718000000000;

Window function example

SELECT
  device_id,
  time,
  temperature,
  LAG(temperature, 1) OVER (PARTITION BY device_id ORDER BY time) AS prev_temp,
  ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY time) AS rn
FROM edge_metrics
WHERE time >= 1718000000000 AND time < 1718086400000;

Point lookup / latest-style filter

SELECT *
FROM edge_metrics
WHERE device_id = 'sensor-1'
  AND time >= 1718000000000
  AND time < 1718003600000
ORDER BY time DESC
LIMIT 1;

Best practice: time pruning

Always include a time range in WHERE. MonoTS uses it to skip unrelated Parquet chunks, which improves query latency and reduces memory use on constrained edge hosts.

6. Introspection (SHOW)

SHOW TABLES;
SHOW CREATE TABLE edge_metrics;

Command	Returns
SHOW TABLES	table_name, column_count, parquet_files
SHOW CREATE TABLE	Original DDL plus row / file metadata

7. Client access

All SQL is executed over gRPC (not HTTP POST /sql).

Setting	Default
Endpoint	http://127.0.0.1:50051
Username / password	admin / admin
Clients	monots CLI (interactive / --sql), Rust SDK

# Interactive CLI
make run-cli

# One-shot statement
monots --sql "SHOW TABLES"

Related docs

• 5-Minute Edge Quick Start — build, ingest, and query locally
• Edge-to-Cloud Sync Guide — CREATE STREAM to Kafka / Delta / filesystem
• Streams reference — stream SQL and sink properties