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](references/ddl-dml.md#explain). - `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 ```bash # Connect with psql (default password: oxla) psql -h localhost -p 5432 -U oxla oxla ``` ```sql -- 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](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`): | Parameter | Default | |-----------|---------| | Host | localhost | | Port | 5432 | | User | oxla | | Password | oxla | | Database | oxla | ```bash # 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](references/connect-and-types.md). Types grounded in `src/sqlparser/sql/ColumnType.h` (the `enum class DataType` listing): | Oxla Type | Notes | |-----------|-------| | `INT` / `INTEGER` | 32-bit integer (`i32`) | | `LONG` / `BIGINT` | 64-bit integer (`i64`) | | `INT16` | 128-bit (16-byte) wide integer (`pg_typeof`: `int16`); Oxla-native, **not** 16-bit | | `INT32` | 256-bit (32-byte) wide integer (`pg_typeof`: `int32`); Oxla-native, **not** an alias of `INT` | | `FLOAT` | 32-bit float | | `DOUBLE` | 64-bit float | | `NUMERIC(p,s)` / `DECIMAL(p,s)` | Fixed-precision | | `CHAR(n)` / `VARCHAR(n)` / `TEXT` | String types | | `DATE` | Calendar date | | `TIME` / `TIMETZ` | Time of day | | `TIMESTAMP` / `TIMESTAMPTZ` | Date+time | | `INTERVAL` | Duration | | `BOOL` | Boolean | | `JSON` / `JSONB` | JSON document | | `BYTEA` | Binary data | | `UUID` | RFC-4122 UUID (`pg_typeof`: `uuid`); literal `UUID '...'`, casts to/from text and `BYTEA` | | `OID` | PostgreSQL object identifier (`pg_typeof`: `oid`, type 26); equality/grouping/joins only, no ordering; implicit `int4`→`oid`, explicit `oid`↔`int4`/`text` | | `ARRAY` | Arrays (`INT[]`, `FLOAT[]`, etc.) | | `GEOMETRY` / `GEOGRAPHY` / `POINT` | Geospatial | 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: ```sql -- 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](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](references/ddl-dml.md). ```sql -- 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](references/data-loading.md). ```sql -- 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](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. ```sql -- 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](references/functions-and-analytics.md). ### Aggregate functions ```sql 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 ```sql -- 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 ```sql SELECT UPPER(customer), LOWER(region),