SQL

Redpanda SQL (Oxla)

Oxla is a distributed columnar analytical database that speaks the PostgreSQL wire protocol (port 5432). Any psql client, JDBC driver, psycopg2, or pgx application connects to it without modification. Oxla is purpose-built for analytical (OLAP) workloads — large scans, aggregations, multi-table joins, and window functions over wide tables — and differs from standard PostgreSQL in execution architecture (columnar, distributed, parallel) and in which DDL/DML features it supports.

Key differences from PostgreSQL to keep in mind:

• EXPLAIN exists but is not PostgreSQL's: it returns the explanation as a relation, not as plan text, and there is no EXPLAIN ANALYZE and no parenthesized option list. EXPLAIN alone renders a help table of the modes; EXPLAIN PHYSICAL <query>, EXPLAIN TIMING <query>, and EXPLAIN CONFIG are the modes. See ddl-dml.md.
• COPY FROM/TO uses explicit FORMAT option names (CSV, PARQUET, ORC).
• SELECT INTO supports two forms: SELECT ... INTO new_table FROM ... (creates a new table) and SELECT ... INTO 'path' (options) FROM ... (writes to a file). The parenthesized option list selects the file form.
• CREATE TABLE [IF NOT EXISTS] t AS SELECT ... (CTAS) is supported and is the idiomatic way to materialize or reshape a table.
• GENERATE_SERIES(start, stop[, step]) is supported as a table-valued function.

Quickstart

# Connect with psql (default password: oxla)
psql -h localhost -p 5432 -U oxla oxla

-- 1. Create a table
CREATE TABLE orders (
    order_id   INT,
    customer   VARCHAR,
    region     VARCHAR,
    amount     DOUBLE,
    order_date DATE
);

-- 2. Insert a few rows (native VALUES form)
INSERT INTO orders VALUES
    (1, 'Acme',   'EMEA', 1250.50, DATE '2024-01-15'),
    (2, 'Globex', 'APAC', 3400.00, DATE '2024-01-16'),
    (3, 'Acme',   'EMEA',  980.00, DATE '2024-02-01');

-- INSERT … SELECT is the form used for transformed or bulk loads:
INSERT INTO orders SELECT order_id, customer, region, amount * 1.1, order_date
FROM orders_staging;

-- 3. COPY from a Parquet file (local or S3)
-- Local file:
COPY orders FROM 'orders.parquet' (FORMAT PARQUET);

-- 4. Run a GROUP BY aggregation
SELECT region,
       COUNT(*)        AS order_count,
       SUM(amount)     AS total_revenue,
       AVG(amount)     AS avg_order
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

-- 5. Window function: running total per region
SELECT order_date,
       region,
       amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
ORDER BY region, order_date;

-- 6. CTE (WITH)
WITH monthly AS (
    SELECT EXTRACT(YEAR FROM order_date)  AS yr,
           EXTRACT(MONTH FROM order_date) AS mo,
           SUM(amount)                    AS revenue
    FROM orders
    GROUP BY yr, mo
)
SELECT * FROM monthly
ORDER BY yr, mo;

Connecting

See references/connect-and-types.md for full driver connection strings, SSL config, and the default initial_password.

Default connection parameters (from config/Release/default_config.yml):

# psql one-liner
PGPASSWORD=oxla psql -h localhost -p 5432 -U oxla oxla

# Python (psycopg2)
conn = psycopg2.connect(host="localhost", port=5432, user="oxla", password="oxla", dbname="oxla")

# Go (pgx)
conn, _ := pgx.Connect(ctx, "postgres://oxla:oxla@localhost:5432/oxla")

Supported Data Types

Full type reference: references/connect-and-types.md.

Types grounded in src/sqlparser/sql/ColumnType.h (the enum class DataType listing):

There is no MAP column type in CREATE TABLE, but map-typed values reach a query two ways: the map(...) expression-level Oxla extension, which builds a literals-only map (in practice a constant lookup table keyed by a column), and map columns of an external table — a Kafka topic whose Avro or Protobuf schema has a map field, or an Iceberg table with a map column. Either is read with the m[key] subscript:

-- Constant lookup table applied to a column
SELECT (map(1, 'new', 2, 'shipped', 3, 'closed'))[status_code] FROM orders;

-- Map column of an external table (Kafka source or Iceberg table)
SELECT id, attrs['region'] FROM my_kafka=>events;

A miss and a stored NULL both return NULL; duplicate keys resolve to the last occurrence; keys must be primitive and non-NULL (no NUMERIC/DECIMAL, INTERVAL, JSON, or geospatial keys); slicing is rejected; and the map value itself cannot be grouped, sorted, deduplicated, concatenated, or cast. See references/connect-and-types.md, and /redpanda:sql-federated-queries for how an external schema's map fields resolve.

DDL and DML

Full reference: references/ddl-dml.md.

-- CREATE TABLE
CREATE TABLE sales (
    id         INT,
    product    VARCHAR,
    qty        INT,
    price      DOUBLE,
    sold_at    TIMESTAMP
);

-- CREATE TABLE AS SELECT (CTAS — materialize / reshape a table)
CREATE TABLE sales_2024 AS
    SELECT * FROM sales WHERE sold_at >= TIMESTAMP '2024-01-01 00:00:00';

-- CREATE TABLE IF NOT EXISTS AS SELECT
CREATE TABLE IF NOT EXISTS sales_backup AS SELECT * FROM sales;

-- DROP TABLE
DROP TABLE sales;
DROP TABLE IF EXISTS sales;

-- ALTER TABLE ... ADD COLUMN (native tables only; metadata-only, no data rewrite)
ALTER TABLE sales ADD COLUMN discount BIGINT;             -- COLUMN keyword optional
ALTER TABLE sales ADD COLUMN IF NOT EXISTS discount BIGINT;
-- Always nullable: rows written before the ALTER read back as NULL. One column per
-- statement; NOT NULL and DEFAULT are rejected; no DROP/RENAME COLUMN. Refused on a
-- view and on external (Kafka/Iceberg) tables. See references/ddl-dml.md.
-- The other ALTER TABLE forms re-bind an external Redpanda/Kafka catalog table
-- (ALTER TABLE IF EXISTS my_catalog=>my_table WITH (schema_lookup_policy='LATEST'))
-- and reassign ownership. For DROP/RENAME/retype a column, recreate via CTAS.

-- NOTE: GRANT ... ON ALL TABLES IN SCHEMA is NOT valid in Oxla (parser rejects
-- the literal 'tables'). Valid targets: ON table, ON TABLE table, ON SCHEMA name,
-- ON DATABASE name, ON EXTERNAL SOURCE catalog. See references/ddl-dml.md.

-- TRUNCATE
TRUNCATE TABLE sales;

-- CREATE / DROP VIEW — non-materialized: the SELECT text is stored and inlined
-- on every use, so the view always reads current base data. Privileges are
-- invoker-based (the caller needs SELECT on the view and on its base relations).
CREATE VIEW emea_orders AS
    SELECT * FROM orders WHERE region = 'EMEA';
DROP VIEW emea_orders;

-- INSERT — native VALUES form (literal rows)
INSERT INTO sales VALUES (1, 'Widget', 10, 9.99, TIMESTAMP '2024-01-15 12:00:00');

-- INSERT … SELECT (for transformed or bulk loads)
INSERT INTO dest SELECT col1, col2 FROM source WHERE condition;

-- UPDATE / DELETE
UPDATE orders SET amount = 0 WHERE order_id = 1;
DELETE FROM orders WHERE order_date < DATE '2020-01-01';

-- Transactions
BEGIN;
INSERT INTO orders VALUES (99, 'Test', 'EMEA', 1.0, CURRENT_DATE);
ROLLBACK;

-- PREPARE / EXECUTE
PREPARE get_region(VARCHAR) AS
  SELECT * FROM orders WHERE region = $1;
EXECUTE get_region('EMEA');

Data Loading

Full reference: references/data-loading.md.

-- COPY FROM CSV via STDIN
COPY orders FROM STDIN (FORMAT CSV);

-- COPY FROM Parquet file
COPY orders FROM 'orders.parquet' (FORMAT PARQUET);

-- COPY FROM ORC file
COPY orders FROM 'orders.orc' (FORMAT ORC);

-- COPY TO CSV
COPY orders TO 'export.csv' (NULL '', DELIMITER ',', HEADER ON);

-- COPY TO Parquet
COPY orders TO 'export.parquet' (FORMAT parquet);

-- SELECT INTO file form (parenthesized options → writes to file, not table)
SELECT id, amount
INTO 's3://bucket/prefix/out.csv'
     (NULL '', DELIMITER ',', HEADER ON,
      aws_cred(aws_region 'eu-central-1',
               aws_key_id 'AKID...',
               aws_private_key 'secret...',
               endpoint 'https://s3.amazonaws.com'))
FROM orders
WHERE sold_at >= TIMESTAMP '2024-01-01 00:00:00';

Redpanda/Kafka and Iceberg External Sources (enterprise differentiator)

Full reference: references/kafka-iceberg.md.

Oxla can attach Redpanda/Kafka topics and Apache Iceberg tables as external sources and query them with plain SQL — the central Oxla + Redpanda enterprise differentiator. The Redpanda-side features it consumes (Iceberg Topics, Tiered Storage, Cloud Topics) each require a Redpanda Enterprise license.

External tables use the catalog=>table_name reference syntax.

-- 1. Object-storage connection (s3 / gcs / abs)
CREATE STORAGE my_s3 TYPE = S3 WITH (
    region = 'us-west-2', access_key_id = 'AKID...', secret_access_key = 'secret...');

-- 2. Iceberg catalog (e.g. Redpanda Iceberg Topics, Glue, Polaris)
CREATE ICEBERG CATALOG my_iceberg STORAGE my_s3 WITH (
    uri = 'https://rest-catalog', warehouse = 's3://wh/', auth_type = 'oauth2',
    oauth2_client_id = 'id', oauth2_client_secret = 'secret');

-- 3. Redpanda/Kafka catalog (initial_brokers + schema_registry_url required)
CREATE REDPANDA CATALOG my_rp WITH (
    initial_brokers = 'localhost:9092', schema_registry_url = 'http://localhost:8081');

-- 4. Bind a topic to a queryable table (topic option required)
CREATE TABLE my_rp=>orders WITH (
    topic = 'orders', schema_lookup_policy = 'LATEST', struct_mapping_policy = 'JSON');

-- 5. Query it like any table
SELECT region, COUNT(*) FROM my_rp=>orders GROUP BY region;

-- Rebind/reconfigure (IF EXISTS + catalog=>table both required)
ALTER TABLE IF EXISTS my_rp=>orders WITH (error_handling_policy = 'DROP_RECORD');

-- Refresh external metadata; grant access
REFRESH my_rp=>orders;
GRANT SELECT ON EXTERNAL SOURCE my_rp TO analyst;

Related Redpanda Enterprise Iceberg-topic properties (set on the Redpanda side): redpanda.iceberg.mode (disabled/key_value/value_schema_id_prefix/ value_schema_latest), redpanda.iceberg.delete, redpanda.iceberg.partition.spec, redpanda.iceberg.target.lag.ms, redpanda.iceberg.invalid.record.action (drop or dlq_table; default dlq_table).

Functions and Analytics

Full reference: references/functions-and-analytics.md.

Aggregate functions

SELECT COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount)
FROM orders;

-- Ordered-set aggregates
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY amount DESC) AS median FROM orders;
SELECT percentile_cont(ARRAY[0.25, 0.75]) WITHIN GROUP (ORDER BY amount) AS quartiles FROM orders;
SELECT mode() WITHIN GROUP (ORDER BY region) FROM orders;

Window functions

-- Ranking
SELECT order_id, region, amount,
       RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rnk,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM orders;

-- LAG / LEAD
SELECT order_date, amount,
       LAG(amount, 1) OVER (ORDER BY order_date)  AS prev_amount
FROM orders;

-- Named window
SELECT SUM(amount) OVER w, AVG(amount) OVER w
FROM orders
WINDOW w AS (PARTITION BY region ORDER BY order_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

String functions

SELECT UPPER(customer), LOWER(region),
       CONCAT(customer, ' / ', region),
       LENGTH(customer),
       SUBSTR(customer, 1, 4),
       STARTS_WITH(customer, 'A'),
       ENDS_WITH(customer, 'e'),
       STRPOS(customer, 'me'),
       REPLACE(customer, 'Acme', 'ACME'),
       REGEXP_REPLACE(customer, 'G.*', 'Corp', 'i')
FROM orders;

Math functions

SELECT ABS(-1.5), CEIL(1.2), FLOOR(1.9), ROUND(1.567),
       SQRT(16.0), EXP(1.0), LN(2.718), LOG10(100.0),
       SIN(3.14), COS(0.0), TAN(0.785),
       PI(), SIGN(-5);

Date/time functions

SELECT EXTRACT(YEAR FROM order_date),
       EXTRACT(MONTH FROM order_date),
       EXTRACT(DAY FROM order_date),
       TIMESTAMP_TRUNC(sold_at, HOUR),
       MAKE_DATE(2024, 3, 15),
       CURRENT_TIMESTAMP,
       CURRENT_TIMESTAMP(3)
FROM orders;

Joins

-- INNER JOIN
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer = c.name;

-- LEFT / RIGHT / FULL JOIN
SELECT o.order_id, p.product_name
FROM orders o
LEFT JOIN products p ON o.product = p.id;

-- FULL OUTER JOIN
SELECT o.order_id, r.label
FROM orders o
FULL JOIN regions r ON o.region = r.code;

-- CROSS JOIN
SELECT * FROM (SELECT amount FROM orders LIMIT 5)
CROSS JOIN (SELECT region FROM regions LIMIT 3);

-- Multi-table (star-schema style)
SELECT d.year, p.category, SUM(f.revenue) AS total
FROM fact_sales f
JOIN dim_date    d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY d.year, p.category
ORDER BY d.year, total DESC;

Set Operations

UNION, INTERSECT, and EXCEPT each accept an optional ALL qualifier. Without ALL, duplicates are eliminated (distinct semantics).

-- UNION ALL (keeps duplicates)
SELECT region FROM orders WHERE amount > 1000
UNION ALL
SELECT region FROM returns;

-- UNION (distinct)
SELECT region FROM orders
UNION
SELECT region FROM returns;

-- INTERSECT ALL
SELECT region FROM orders
INTERSECT ALL
SELECT region FROM targets;

-- EXCEPT [ALL] — rows in left but not right
SELECT region FROM orders
EXCEPT
SELECT region FROM blacklist;

SELECT i0 FROM tb1
EXCEPT ALL
SELECT i0 FROM tb2;

EXPLAIN

EXPLAIN returns its explanation as a relation, so it can be filtered and ordered with plain SQL. Four modes; an unrecognized spelling renders the help table rather than an error.

EXPLAIN;                                    -- help: the modes and an example of each
EXPLAIN CONFIG;                             -- query-planner options + session values
EXPLAIN TIMING SELECT * FROM orders;        -- planning-stage breakdown
SET oxla.query_planner.pipeline = on;       -- required for PHYSICAL only
EXPLAIN PHYSICAL SELECT * FROM orders;      -- operator topology, one row per output

-- Same explanations as a queryable relation
SELECT operation, value FROM explain('SELECT 1', 'physical_plan') ORDER BY row_num;

Only SELECT queries can be explained. See ddl-dml.md for the per-mode result columns, the explain() argument rules, and the steps=n option.

GENERATE_SERIES

SELECT * FROM generate_series(1, 10);
SELECT * FROM generate_series(0, 100, 10);
SELECT gs FROM generate_series(1, 5) AS gs;
SELECT * FROM generate_series(10, 1, -1);

Reference Directory

• connect-and-types.md: PostgreSQL wire protocol connection (port 5432, psql/JDBC/psycopg2/pgx), authentication (initial_password), SSL config, and the full supported data-type list grounded in ColumnType.h, including the wide-integer (INT16/INT32) cast and operator matrix plus how to bind wide-integer parameters, and the map surface (map(...) constructor, m[key] lookup on both constructed maps and external-schema map columns, supported key types, miss-vs-stored-NULL semantics).
• ddl-dml.md: CREATE/DROP TABLE, CREATE TABLE AS SELECT (CTAS), CREATE/DROP VIEW (non-materialized, inlined per use, pinned column contract, invoker privileges, pg_views), the EXPLAIN statement and explain() table function (help/physical_plan/timing/config modes, per-mode result columns, steps=n), CREATE/DROP SCHEMA, TRUNCATE, CREATE ROLE, GRANT/REVOKE (valid targets: ON table / ON TABLE / ON SCHEMA / ON DATABASE / ON EXTERNAL SOURCE — ON ALL TABLES IN SCHEMA is rejected by the parser), the three ALTER TABLE forms (ADD COLUMN on a native table — nullable-only, metadata-only, one column per statement, with the NOT NULL/DEFAULT/view/external-table rejections; the IF EXISTS catalog=>table WITH (...) Kafka-catalog rebind; and OWNER TO), SELECT/INSERT VALUES/INSERT SELECT/UPDATE/DELETE, SELECT INTO (table or file destination), PREPARE/EXECUTE, and transactions — all grounded in query_planner test cases and bison_parser.y.
• kafka-iceberg.md: Oxla + Redpanda enterprise differentiator — querying Redpanda/Kafka topics and Apache Iceberg tables via SQL. CREATE/ALTER/DROP STORAGE (s3/gcs/abs), CREATE/ALTER/DROP ICEBERG CATALOG (uri/warehouse/auth_type oauth2|basic|aws_sigv4|gcp + nested keys), CREATE/ALTER/DROP REDPANDA|KAFKA CATALOG (initial_brokers/schema_registry_url required, sasl_*, truststore, key_store_*, USING CATALOG bind/detach), CREATE TABLE / ALTER TABLE IF EXISTS catalog=>topic WITH (topic/schema_lookup_policy/error_handling_policy/struct_mapping_policy/confluent_wire_protocol plus the key/header decode options key_decode_mode/key_schema_subject/key_confluent_wire_protocol/key_schema_message_full_name/header_value_type), REFRESH, DESCRIBE/SHOW, GRANT ON EXTERNAL SOURCE, and the Redpanda Enterprise Iceberg-topic properties (redpanda.iceberg.mode/delete/partition.spec/target.lag.ms/invalid.record.action). Notes Redpanda Enterprise license requirements. Grounded in bison_parser.y, connection_option_names.h, kafka/conversions.cpp, iceberg_catalog_parser.cpp.
• data-loading.md: COPY FROM / COPY TO with CSV/Parquet/ORC formats, STDIN/STDOUT, S3 credentials via aws_cred(...), and bulk-loading patterns for analytical ingestion.
• functions-and-analytics.md: Complete function reference — aggregates (SUM/AVG/COUNT/percentile_disc/percentile_cont/mode/CORR), string (CONCAT/SUBSTR/REPLACE/REGEXP_REPLACE/STARTS_WITH/ENDS_WITH/STRPOS/LENGTH/UPPER/LOWER), math (ABS/CEIL/FLOOR/ROUND/SQRT/EXP/LN/LOG10/trig), date-time (EXTRACT/TIMESTAMP_TRUNC/MAKE_DATE/MAKE_TIMESTAMP/CURRENT_TIMESTAMP), window functions, CTEs, CASE/IF, and ARRAY functions — all grounded in test cases.