SQL Guide¶

ApexBase supports a practical SQL dialect for embedded HTAP workloads: DDL, DML, analytical SELECT queries, joins, CTEs, set operations, transactions, table functions, full-text search, and vector search.

Running SQL¶

from apexbase import ApexClient

with ApexClient("./data") as client:
    client.execute("CREATE TABLE IF NOT EXISTS users")
    client.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
    result = client.execute("SELECT * FROM users WHERE age >= 18")

DDL¶

CREATE TABLE IF NOT EXISTS users;
ALTER TABLE users ADD COLUMN name STRING;
ALTER TABLE users ADD COLUMN age INT;
DROP TABLE IF EXISTS old_users;

Use qualified names for named databases:

CREATE TABLE analytics.events;
DROP TABLE IF EXISTS analytics.old_events;

DML¶

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25), ('Charlie', 35);

UPDATE users SET age = 31 WHERE name = 'Alice';
DELETE FROM users WHERE age < 18;

Analytical Queries¶

SELECT city, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE age BETWEEN 18 AND 65
GROUP BY city
HAVING users > 10
ORDER BY avg_age DESC
LIMIT 20;

Joins¶

SELECT u.name, e.event, e.ts
FROM users u
JOIN events e ON u.id = e.user_id
WHERE e.event = 'purchase';

Supported join forms include INNER, LEFT, RIGHT, FULL, and CROSS.

CTEs And Subqueries¶

WITH active_users AS (
    SELECT user_id, COUNT(*) AS events
    FROM events
    GROUP BY user_id
)
SELECT u.name, a.events
FROM users u
JOIN active_users a ON u.id = a.user_id
WHERE a.events > 5;

SELECT *
FROM users
WHERE id IN (SELECT user_id FROM events WHERE event = 'signup');

Window Functions¶

SELECT
    user_id,
    ts,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) AS event_rank,
    COUNT(*) OVER (PARTITION BY user_id) AS user_events
FROM events;

Set Operations¶

SELECT user_id FROM web_events
UNION
SELECT user_id FROM mobile_events;

SELECT user_id FROM paid_users
EXCEPT
SELECT user_id FROM refunded_users;

UNION, UNION ALL, INTERSECT, and EXCEPT are supported.

Transactions¶

BEGIN;
INSERT INTO orders (order_id, total) VALUES (1001, 39.9);
SAVEPOINT before_adjustment;
UPDATE orders SET total = 35.9 WHERE order_id = 1001;
RELEASE before_adjustment;
COMMIT;

Use ROLLBACK to cancel a transaction, or ROLLBACK TO savepoint_name to undo part of one.

File Table Functions¶

Query files directly:

SELECT city, COUNT(*) AS rows
FROM read_csv('events.csv')
GROUP BY city;

Supported functions:

read_csv(path)
read_parquet(path)
read_json(path)

For repeated queries over a file, register it as a temporary table from Python. See Data Import.

Full-Text Search¶

CREATE FTS INDEX ON articles(title, content);

SELECT title
FROM articles
WHERE MATCH('rust database');

For fuzzy matching, lifecycle commands, and configuration, see the Full-Text Search Guide.

Vector Search¶

SELECT *
FROM explode_rename(
    topk_distance('items', 'embedding', '[0.1, 0.2, 0.3]', 10, 'cosine'),
    '_id,dist'
);

For Python batch search and float16 vector storage, see the Float16 Vector Guide.

Explain¶

EXPLAIN SELECT * FROM users WHERE age > 30;
EXPLAIN ANALYZE SELECT city, COUNT(*) FROM users GROUP BY city;

Use EXPLAIN when you are checking whether a query is taking a fast path, using an index, or falling back to the full planner.