Query with HTTP API

The HTTP API provides a lightweight interface for querying Altertable.

With the HTTP API, you can:

• Run SQL queries over HTTPS
• Stream results as JSONL

For terminal queries and scripts, use the CLI. It stores profiles locally and can return human-readable tables, JSON, CSV, Markdown, or agent-friendly JSON.

Query

Query runs SQL over HTTPS and streams results as JSONL, so applications can process rows as they arrive instead of buffering the full response. Run a query with cURL or SDKs:

cURL

1

2

3

curl -X POST https://api.altertable.ai/query \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"statement": "SELECT * FROM my_catalog.main.events ORDER BY timestamp DESC LIMIT 100"}'

SDKs

JavaScript

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

import { AltertableLakehouseClient } from '@altertable/lakehouse';

const client = new AltertableLakehouseClient({
  username: 'YOUR_LAKEHOUSE_USERNAME',
  password: 'YOUR_LAKEHOUSE_PASSWORD',
});

const result = await client.queryAll({
  statement: `
    SELECT
      date_trunc('day', timestamp) AS day,
      event,
      COUNT(*)                     AS events,
      ROUND(AVG(value), 2)         AS avg_value
    FROM   my_catalog.main.events
    WHERE  timestamp >= NOW() - INTERVAL '30 days'
    GROUP  BY 1, 2
    ORDER  BY day DESC, events DESC
  `,
});

for (const row of result.rows) {
  console.log(row);
}

From the CLI, run the same SQL with:

Copy
altertable query "SELECT * FROM users LIMIT 10"

Add request options when you need more control over execution:

Query compute size

Without per-query sizing, every statement can run on the same compute profile—whether it scans ten rows or ten billion.

Pass "compute_size": "AUTO" (the default in Query Explorer) and Altertable picks a size per query from the SQL itself. A narrow LIMIT 100 over a large table can run on XS; a full-table aggregate may need XL.

Copy
1
2
3
4
{
  "statement": "SELECT id, email FROM users WHERE created_at > now() - interval '7 days' LIMIT 100",
  "compute_size": "AUTO"
}

How AUTO works

Before execution, Altertable runs EXPLAIN on the statement and walks the physical plan to estimate how much parallelism the query needs. It then selects the smallest tier that fits:

Constraints:

• AUTO cannot be combined with an explicit session_id. Reuse sessions when you need temporary state; pick a fixed size when you need both session reuse and predictable compute.

Fixed sizes

Override inference when you know the workload or need repeatable performance:

Copy
1
2
3
4
{
  "statement": "SELECT ...",
  "compute_size": "M"
}

Use a fixed tier for long-running batch jobs, dashboards that should always warm the same worker profile, or queries where you have already benchmarked the right size.

SQL dialect transpilation

Altertable executes DuckDB SQL. Most of the lakehouse—Query Explorer, Postgres adapter, dashboards—assumes you write DuckDB syntax directly.

The dialect parameter is different: it lets you send SQL written for another engine and have Altertable transpile it to DuckDB before execution. That is useful when you are porting saved queries from another platform, running BI-generated SQL, or prototyping a migration without rewriting every function by hand.

Copy
1
2
3
4
{
  "statement": "SELECT DATEDIFF(day, start_date, end_date) AS days FROM my_catalog.main.orders",
  "dialect": "snowflake"
}

Altertable transpiles the statement, then runs the DuckDB version on your catalog.

How it works

1. You send statement in the source dialect and set dialect to that engine.
2. Altertable transpiles the SQL to DuckDB using polyglot-sql.
3. The transpiled statement runs on Workers like any other query.

Omit dialect (or write DuckDB SQL and leave it unset) when you already target DuckDB. Transpilation is optional, not the default path.

Dialect names are case-insensitive. Some engines accept aliases—for example, tsql, mssql, and sqlserver all map to SQL Server.

Common source dialects

Transpilation coverage depends on the source dialect and the functions in your SQL. These are the engines teams most often port from:

Many other dialects are supported, including hive, oracle, sqlite, teradata, and fabric. Unknown dialect names return 400 Bad Request.

Limits and best practices

Transpilation handles common syntax and function differences, but it is not a full semantic rewrite. Warehouse-specific UDFs, session variables, staged files, and engine-only table functions may fail to transpile or behave differently after conversion.

When transpilation fails, the API returns 400 Bad Request with an error such as Failed to transpile <source engine> SQL to DuckDB. Fix the statement, simplify it, or rewrite the incompatible parts in DuckDB SQL.

For production workloads, treat transpilation as a migration shortcut: validate results against the source engine, then commit to DuckDB-native SQL where performance and clarity matter.

Check the API reference for the complete request shape and all supported parameters.

API reference

Use the reference page for endpoint details, payload shapes, and full examples:

• POST /query
• GET /query/:query-uuid
• DELETE /query/:query-uuid

The same reference also covers related ingest, validation, and task endpoints:

• POST /append
• POST /upload
• POST /upsert
• GET /tasks/:task-uuid
• POST /validate