Query with HTTP API

The HTTP API provides a lightweight interface for querying Altertable.

Goal

Authenticate an HTTP request, choose a streaming result format, and configure compute size or SQL dialect when the workload requires it.

Prerequisites

• Your lakehouse username and password, encoded for HTTP Basic Auth.
• SQL that can run against a catalog available in the target environment.
• An HTTP client that can process a streaming response.

The API can:

• Run SQL queries over HTTPS
• Stream the default Altertable JSONL envelope or standard CSV, JSONL, and Parquet output
• Estimate table scans without running a query

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.

Steps

1. Run a 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

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"

2. Configure the request

Add request options when you need more control over execution:

3. Choose a 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
{
  "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
{
  "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.

4. Transpile another SQL dialect

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
{
  "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.

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

Verification

Run a query that does not depend on an existing table:

Copy
curl https://api.altertable.ai/query \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  -d '{"statement":"SELECT 1 AS connected"}'

The default JSONL stream should include query metadata, a column-name array containing connected, and a row containing 1.

Troubleshooting

• The request is unauthorized: rebuild the Basic Auth token from the lakehouse username and password for the target environment.
• The response is hard to parse: process it one line at a time as JSONL rather than as one JSON document.
• The API returns 503 Service Unavailable: no Worker currently fits the request. Retry with backoff or select a smaller compute size.
• AUTO fails with a session: remove session_id or choose a fixed compute_size; the two options cannot be combined.
• Dialect transpilation returns 400 Bad Request: simplify the statement or rewrite unsupported functions in DuckDB SQL.
• A query runs on the wrong data: qualify table names with the intended catalog and schema.

Next steps

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

• POST /query
• POST /explain
• GET /query/{query_id}
• DELETE /query/{query_id}

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

• POST /append
• POST /upload
• POST /upsert
• GET /tasks/{task_id}
• POST /validate