# 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](/docs/lakehouse/authentication.md), 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](/docs/developer-tooling/cli.md). 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 ```bash 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"}' ``` ### JavaScript ```typescript import { AltertableLakehouseClient } from '@altertable/lakehouse'; const client = new AltertableLakehouseClient({ username: '{{LAKEHOUSE_USERNAME}}', password: '{{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); } ``` ### Python ```python from altertable_lakehouse import Client from altertable_lakehouse.models import QueryRequest client = Client( username="{{LAKEHOUSE_USERNAME}}", password="{{LAKEHOUSE_PASSWORD}}", ) req = QueryRequest(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 """) result = client.query_all(req) for row in result.rows: print(row) ``` ### Ruby ```ruby require 'altertable/lakehouse' client = Altertable::Lakehouse::Client.new( username: '{{LAKEHOUSE_USERNAME}}', password: '{{LAKEHOUSE_PASSWORD}}' ) client.query_all( statement: <<~SQL 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 SQL )[:rows].each do |row| p row end ``` ### Go ```go import ( "context" "fmt" "log" altertable "github.com/altertable-ai/altertable-lakehouse-go" ) client, err := altertable.NewClient(altertable.Config{ Username: "{{LAKEHOUSE_USERNAME}}", Password: "{{LAKEHOUSE_PASSWORD}}", }) if err != nil { log.Fatal(err) } result, err := client.QueryAll(context.Background(), altertable.QueryRequest{ 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 `, }) if err != nil { log.Fatal(err) } for _, row := range result.Rows { fmt.Println(row) } ``` ### PHP ```php withCredentials('{{LAKEHOUSE_USERNAME}}', '{{LAKEHOUSE_PASSWORD}}') ->build(); $client = new LakehouseClient($config); $result = $client->queryAll(new QueryRequest( statement: <<<'SQL' 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 SQL, )); foreach ($result->rows as $row) { print_r($row); } ``` ### Rust ```rust use altertable_lakehouse::{AltertableClient, QueryRequest}; let client = AltertableClient::builder() .credentials("{{LAKEHOUSE_USERNAME}}", "{{LAKEHOUSE_PASSWORD}}") .build()?; let result = client .query_all(&QueryRequest { statement: r#" 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 "# .into(), ..Default::default() }) .await?; for row in &result.rows { println!("{row:?}"); } ``` ### CLI ```bash # Install curl -fsSL https://install.altertable.ai | sh # Configure credentials altertable profile --configure # Query lakehouse data altertable query " 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 " ``` From the [CLI](/docs/developer-tooling/cli.md), run the same SQL with: ```bash altertable query "SELECT * FROM users LIMIT 10" ``` ### 2. Configure the request Add request options when you need more control over execution: | Parameter | Use it for | | ------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `session_id` | Reuse a query session across requests so temporary state and session context can carry forward. | | `timezone` | Evaluate timezone-aware SQL with a specific IANA timezone, such as `Europe/Paris`. | | `compute_size` | Pick query compute per request. Use `AUTO` (recommended) to infer size from the SQL, or a fixed tier (`XS` through `XL`). See [Choose a compute size](#3-choose-a-compute-size). | | `dialect` | Transpile SQL from another engine to DuckDB before execution. See [Transpile another SQL dialect](#4-transpile-another-sql-dialect). | | `format` | Return `csv`, row-oriented `jsonl`, or `parquet` instead of the default metadata, schema, and row stream. | | `limit` / `offset` | Page or cap result rows without rewriting the SQL. | ### 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`. ```json { "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: | Size | Threads | Typical fit | | ---- | ------- | ------------------------------------------------------ | | `XS` | 2 | Point lookups, small scans, queries with early `LIMIT` | | `S` | 4 | Moderate scans | | `M` | 8 | Larger scans or joins | | `L` | 16 | Heavy joins or aggregations | | `XL` | 32 | Full-table scans and large shuffles | **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: ```json { "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](/docs/lakehouse/sql-engine.md). 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. ```json { "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](https://github.com/tobilg/polyglot). 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: | `dialect` value | Source engine | | --------------- | -------------------- | | `snowflake` | Snowflake | | `databricks` | Databricks | | `bigquery` | Google BigQuery | | `spark` | Apache Spark SQL | | `trino` | Trino | | `presto` | Presto | | `redshift` | Amazon Redshift | | `postgresql` | PostgreSQL | | `mysql` | MySQL | | `tsql` | Microsoft SQL Server | | `clickhouse` | ClickHouse | | `athena` | Amazon Athena | Many other dialects are supported, including `hive`, `oracle`, `sqlite`, `teradata`, and `fabric`. Unknown dialect names return `400 Bad Request`. > **Note: transpilation limits:** > > 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 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. ## Verification Run a query that does not depend on an existing table: ```bash 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. **Continue in Altertable:** [Open Query Explorer](/app) ## Next steps Use the reference page for endpoint details, payload shapes, and full examples: - [POST /query](/docs/api-references/lakehouse.md#post-query) - [POST /explain](/docs/api-references/lakehouse.md#post-explain) - [`GET /query/{query_id}`](/docs/api-references/lakehouse.md#get-queryquery-uuid) - [`DELETE /query/{query_id}`](/docs/api-references/lakehouse.md#delete-queryquery-uuid) The same reference also covers related ingest, validation, and task endpoints: - [POST /append](/docs/api-references/lakehouse.md#post-append) - [POST /upload](/docs/api-references/lakehouse.md#post-upload) - [POST /upsert](/docs/api-references/lakehouse.md#post-upsert) - [`GET /tasks/{task_id}`](/docs/api-references/lakehouse.md#get-taskstask-uuid) - [POST /validate](/docs/api-references/lakehouse.md#post-validate)