hotdata-dev/hotdata-analytics

Hotdata Analytics Skill

OLAP-style analytics in Hotdata: PostgreSQL-dialect SQL, query execution, run history, stored results, Chain materializations, and sorted indexes for filters and joins.

Prerequisites: Authenticate, workspace, and catalog discovery via the hotdata skill (connections, tables, datasets, databases).

Related skills: hotdata-search (BM25, vector, retrieval indexes), hotdata-geospatial (spatial SQL).

---

Execute SQL

hotdata query "<sql>" [--workspace-id <workspace_id>] [--connection <connection_id>] [--output table|json|csv]
hotdata query status <query_run_id> [--output table|json|csv]

• PostgreSQL dialect. Quote mixed-case identifiers: "CustomerName".
• Use hotdata tables list for schema discovery — not information_schema via query.
• Fully qualified names: <connection>.<schema>.<table>, datasets.<schema>.<table>, <database>.<schema>.<table>.
• Long-running queries may return query_run_id → poll with query status (exit 2 = still running). Do not re-run identical heavy SQL while polling.
• For workspace-wide joins and naming, load context:DATAMODEL when listed (hotdata context list → show DATAMODEL) — see hotdata skill.

OLAP patterns

Typical analytics SQL (all via hotdata query):

• Aggregations: COUNT, SUM, AVG, MIN, MAX with GROUP BY
• Joins: INNER / LEFT JOIN across <connection>.<schema>.<table> names
• Filtering: WHERE on partition-friendly columns (consider sorted indexes below)
• Ordering: ORDER BY on metrics or dimensions
• Bounded exploration: always LIMIT while iterating; widen once validated

Column names from CSV uploads may be case-sensitive — use double quotes when not all-lowercase.

---

Query run history

Uses the active workspace only (no --workspace-id; set with hotdata workspaces set).

hotdata queries list [--limit <int>] [--cursor <token>] [--status <csv>] [--output table|json|yaml]
hotdata queries <query_run_id> [--output table|json|yaml]

• list — status, duration, row count, SQL preview (default limit 20). Filter: --status running,failed.
• <query_run_id> — full metadata, formatted SQL, result_id when present.
• Use history to find recurring WHERE / JOIN / GROUP BY patterns before adding indexes (search skill) or chains.

---

Stored results

hotdata results list [--workspace-id <workspace_id>] [--limit <int>] [--offset <int>] [--output table|json|yaml]
hotdata results <result_id> [--workspace-id <workspace_id>] [--output table|json|csv]

• Prefer results <id> over re-running identical heavy queries.
• Query footers may include [result-id: rslt...]; also available from queries <query_run_id>.

---

Chain (materialized follow-ups)

Pattern: run SQL → materialize a smaller table → query the materialized name.

1. Base query

   hotdata query "SELECT ..."
   hotdata query status <query_run_id>   # if async
   

2. Materialize (pick one)

   hotdata datasets create --name chain_slice [--description "chain slice"] --sql "SELECT ..."
   hotdata datasets create --name chain_from_saved [--description "from saved"] --query-id <query_id>
   

   Or managed parquet:

   hotdata databases create --catalog analytics
   hotdata databases load --catalog analytics --table slice --file ./slice.parquet
   

3. Chain query — use printed full_name or datasets list FULL NAME column:

   hotdata query "SELECT * FROM datasets.main.chain_slice WHERE ..."
   hotdata query "SELECT * FROM analytics.public.slice WHERE ..."
   

Document stable chains in context:DATAMODEL → Derived tables (Chain).

Full procedure: references/WORKFLOWS.md.

---

Sorted indexes (filters and range scans)

For equality, range, and sort-heavy OLAP — not full-text or vector (see hotdata-search):

hotdata indexes create --connection-id <id> --schema <schema> --table <table> \
  --name idx_orders_created --column created_at --type sorted [--async]

List and delete use the same hotdata indexes commands as in the search skill; only --type sorted is the analytics focus here.

---

Sandboxes and chains

Sandbox datasets use datasets.<sandbox_id>.<table>, not datasets.main. Run queries with active sandbox config or hotdata sandbox <id> run hotdata query "...". See hotdata skill Sandboxes.