SQL Compatibility

-- Create a table with a composite primary key + partition column + JSON column
CREATE TABLE events (
  id         BIGINT NOT NULL,
  user_id    VARCHAR NOT NULL,
  occurred_at TIMESTAMP NOT NULL,
  event_type VARCHAR NOT NULL,
  payload    JSON,
  PRIMARY KEY (id, user_id)
) PARTITIONED BY (event_type);
 
-- Add a new column
ALTER TABLE events ADD COLUMN session_id VARCHAR;
 
-- Drop a table
DROP TABLE events;
DROP TABLE IF EXISTS events;
 
-- CTAS (CREATE TABLE AS SELECT) — schema inferred from the SELECT
CREATE TABLE click_summary AS
  SELECT user_id, COUNT(*) AS n
  FROM events
  WHERE event_type = 'click'
  GROUP BY user_id;

-- Single-row insert (keyed)
INSERT INTO events (id, user_id, occurred_at, event_type, payload)
  VALUES (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click', '{"page":"/pricing"}');
 
-- Bulk insert (keyed)
INSERT INTO events (id, user_id, occurred_at, event_type, payload) VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click',    '{"page":"/pricing"}'),
  (2, 'u-101', TIMESTAMP '2026-05-27 10:01:00', 'click',    '{"page":"/signup"}'),
  (3, 'u-102', TIMESTAMP '2026-05-27 10:02:00', 'purchase', '{"sku":"A-12","amount":49.99}');
 
-- INSERT … SELECT (keyed-from-select)
INSERT INTO click_summary
  SELECT user_id, COUNT(*) FROM events WHERE event_type = 'click' GROUP BY user_id;
 
-- UPDATE via PK (keyed → write log)
UPDATE events SET event_type = 'click_pricing'
  WHERE id = 1 AND user_id = 'u-101';
 
-- UPDATE via non-PK predicate (non-keyed → ⚠️ Delta-only, see warnings)
UPDATE events SET event_type = 'archived' WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';
 
-- DELETE via PK (keyed)
DELETE FROM events WHERE id = 1 AND user_id = 'u-101';
 
-- DELETE via non-PK predicate (non-keyed → ⚠️)
DELETE FROM events WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';
 
-- MERGE (upsert) — source is inline rows / a query / another table
MERGE INTO events AS t
USING (SELECT * FROM events_staging) AS s
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN INSERT (id, user_id, occurred_at, event_type, payload)
  VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

-- Simple SELECT
SELECT * FROM events WHERE event_type = 'click' LIMIT 100;
 
-- Aggregates (federated across partitions when applicable)
SELECT user_id, COUNT(*) AS n
FROM events
GROUP BY user_id
ORDER BY n DESC;
 
-- Joins
SELECT e.user_id, u.email, COUNT(*) AS clicks
FROM events e JOIN users u ON e.user_id = u.id
WHERE e.event_type = 'click'
GROUP BY e.user_id, u.email;
 
-- Window functions
SELECT user_id, occurred_at,
       LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_event
FROM events;
 
-- CTEs (WITH …)
WITH purchases AS (
  SELECT user_id, payload->>'sku' AS sku, (payload->>'amount')::double AS amount
  FROM events WHERE event_type = 'purchase'
)
SELECT sku, SUM(amount) AS total FROM purchases GROUP BY sku;
 
-- Subqueries
SELECT * FROM events WHERE user_id IN (SELECT id FROM users WHERE tier = 'pro');
 
-- Set ops
(SELECT user_id FROM events WHERE event_type = 'click')
INTERSECT
(SELECT user_id FROM events WHERE event_type = 'purchase');

-- Field access
SELECT payload->'sku' FROM events;            -- returns JSON
SELECT payload->>'sku' FROM events;           -- returns TEXT
 
-- JSON path
SELECT json_extract(payload, '$.amount') FROM events;
SELECT json_extract_string(payload, '$.sku') FROM events;
 
-- Iterate over array elements / object entries
SELECT json_each(payload) FROM events;
 
-- Cast extracted scalars
SELECT (payload->>'amount')::double AS amount FROM events;
 
-- Round-trip a Postgres-style cast
SELECT payload::json->'tags'->>0 FROM events;

curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "sql": "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type",
    "freshness": "FRESHNESS_EVENTUAL"
  }'

# Default freshness (eventual)
dodil k3 table query --bucket kb-prod --sql "
  SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type
"
 
# Read-your-writes
dodil k3 table query --bucket kb-prod --sql "
  SELECT * FROM events WHERE user_id = 'u-101'
" --freshness strong
 
# JSON output for scripting
dodil k3 table query --bucket kb-prod --sql "SELECT * FROM events LIMIT 5" -o json