# HTTP API

## Quick reference

- **Base URL**

  api.altertable.ai

- **Authentication**

  [HTTP Basic Auth](#authentication)

- **Terminal workflows**

  [Use the CLI](/docs/developer-tooling/cli.md)

* [Query with SQL](#post-query)
* [Estimate a query](#post-explain)
* [Upload a file](#post-upload)
* [Upsert by primary key](#post-upsert)

Use the Altertable HTTP API to run SQL, estimate query scans, upload files, upsert by primary key, append event-style JSON records, validate SQL, and request autocomplete suggestions without opening a database protocol connection.

If you are working from a terminal, the [CLI](/docs/developer-tooling/cli.md) wraps the query, upload, upsert, and append endpoints with profile-based authentication.

## API endpoint

| Hostname            | Port        |
| ------------------- | ----------- |
| `api.altertable.ai` | 443 (HTTPS) |

## Authentication

Authenticate every request with HTTP Basic Auth. Base64-encode your credentials in the format `lakehouse_username:lakehouse_password`:

```bash
echo -n "{{LAKEHOUSE_USERNAME}}:{{LAKEHOUSE_PASSWORD}}" | base64
```

Then send the encoded value in the `Authorization` header:

```http
Authorization: Basic <ALTERTABLE_BASIC_AUTH_TOKEN>
```

The Python and Ruby SDKs handle this for you when you pass your lakehouse username and lakehouse password during client initialization.

## POST `/query`

Execute SQL and stream the result as JSON Lines.

**Authentication:** HTTP Basic Auth\
**Permission:** Query access to referenced catalogs

| Parameter      | Type                               | Location | Required | Description                                                                                     |
| -------------- | ---------------------------------- | -------- | -------- | ----------------------------------------------------------------------------------------------- |
| `statement`    | string                             | body     | Yes      | The SQL statement to execute.                                                                   |
| `catalog`      | string                             | body     | No       | Catalog to use for unqualified table names.                                                     |
| `schema`       | string                             | body     | No       | Schema to use for unqualified table names.                                                      |
| `session_id`   | string                             | body     | No       | Reuse an existing query session.                                                                |
| `timezone`     | string                             | body     | No       | IANA timezone for the session. Defaults to UTC.                                                 |
| `compute_size` | XS \| S \| M \| L \| XL \| AUTO    | body     | No       | Choose a compute tier or infer one from the SQL. AUTO cannot be combined with session\_id.      |
| `dialect`      | string                             | body     | No       | Source SQL dialect to transpile to DuckDB before execution.                                     |
| `limit`        | integer                            | body     | No       | Limit the number of returned rows.                                                              |
| `offset`       | integer                            | body     | No       | Skip this many rows before returning results.                                                   |
| `format`       | default \| csv \| jsonl \| parquet | body     | No       | Choose the streamed output format. Defaults to the Altertable metadata, schema, and row stream. |
| `query_id`     | UUID                               | body     | No       | Assign a query identifier instead of generating one.                                            |

### Response format

The default response uses **JSONL** (JSON Lines) for efficient streaming:

1. **First line**: query metadata, including the session, query, and Worker identifiers
2. **Second line**: an array of column names
3. **Remaining lines**: one JSON array per result row, in column order

Set `format` to `csv`, `jsonl`, or `parquet` when you need a standard data stream without the Altertable metadata and schema lines. The `jsonl` format emits one JSON object per row.

### Request

```curl
curl -X POST https://api.altertable.ai/query \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
-d '{"statement":"SELECT event, COUNT(*) AS count FROM product_analytics.main.events GROUP BY event"}'
```

### Response (200 OK)

```javascript
{"statement":"SELECT ...","session_id":"1234567890"}
[{"name":"event","type":"VARCHAR"},{"name":"count","type":"BIGINT"}]
["signup",42]
["login",123]
```

## POST `/upload`

Upload a CSV, JSON, or Parquet file to create, append to, or replace a table.

**Authentication:** HTTP Basic Auth\
**Permission:** Write access to the destination catalog

| Parameter | Type                          | Location | Required | Description                                          |
| --------- | ----------------------------- | -------- | -------- | ---------------------------------------------------- |
| `catalog` | string                        | query    | Yes      | Catalog to ingest data into.                         |
| `schema`  | string                        | query    | Yes      | Schema within the catalog.                           |
| `table`   | string                        | query    | Yes      | Table to create or insert into.                      |
| `mode`    | create \| append \| overwrite | query    | Yes      | How the uploaded data changes the destination table. |

### Request body

Binary file data. The server infers CSV, JSON, or Parquet from the `Content-Type` header when present, otherwise from magic bytes in the payload:

- **CSV**: Comma-separated values with header row
- **JSON**: JSON array of objects or JSONL (one JSON object per line)
- **Parquet**: Apache Parquet columnar format (most efficient)

### Ingestion modes

- **`create`**: Create a new table with the uploaded data (fails if the table already exists)
- **`append`**: Append the uploaded data to an existing table (preserves existing data)
- **`overwrite`**: Drop the existing table and recreate it with the uploaded data (replaces all data)

Request bodies can contain up to **100 GB**.

**Example: Upload CSV file**

```bash
curl "https://api.altertable.ai/upload?catalog=my_catalog&schema=main&table=users&mode=create" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  --data-binary @users.csv
```

**Example: Append JSON file**

```bash
curl "https://api.altertable.ai/upload?catalog=my_catalog&schema=main&table=events&mode=append" \
  -H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
  --data-binary @events.json
```

### Request

```curl
curl "https://api.altertable.ai/upload?catalog=my_catalog&schema=main&table=users&mode=create" \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
--data-binary @users.csv
```

### Response (200 OK)

```http
HTTP/1.1 200 OK
```

## POST `/upsert`

Merge uploaded CSV, JSON, or Parquet rows into a table by primary key.

**Authentication:** HTTP Basic Auth\
**Permission:** Write access to the destination catalog

| Parameter     | Type   | Location | Required | Description                                    |
| ------------- | ------ | -------- | -------- | ---------------------------------------------- |
| `catalog`     | string | query    | Yes      | Catalog to ingest data into.                   |
| `schema`      | string | query    | Yes      | Schema within the catalog.                     |
| `table`       | string | query    | Yes      | Table to merge rows into.                      |
| `primary_key` | string | query    | Yes      | Column used to match and update existing rows. |

The request body contains binary CSV, JSON, or Parquet data and can be up to **100 GB**. Matching rows are updated and new rows are inserted.

### Request

```curl
curl "https://api.altertable.ai/upsert?catalog=my_catalog&schema=main&table=users&primary_key=id" \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
--data-binary @updates.parquet
```

### Response (200 OK)

```http
HTTP/1.1 200 OK
```

## POST `/append`

Append one JSON object or a batch of objects without managing pipelines or offsets.

**Authentication:** HTTP Basic Auth\
**Permission:** Write access to the destination catalog

| Parameter | Type    | Location | Required | Description                                     |
| --------- | ------- | -------- | -------- | ----------------------------------------------- |
| `catalog` | string  | query    | Yes      | Catalog to append data to.                      |
| `schema`  | string  | query    | Yes      | Schema within the catalog.                      |
| `table`   | string  | query    | Yes      | Table to append data to.                        |
| `sync`    | boolean | query    | No       | Wait for processing to finish before returning. |

The request body can be one JSON object or an array of objects, up to **32 MB**. For larger files, use `/upload` with `mode=append`.

Use `task_id` with `GET /tasks/{task_id}` to poll asynchronous processing. When `sync=true`, the response waits until the append task finishes.

The response confirms that Altertable accepted the records for asynchronous processing. Rows that still fail after retries are written to `altertable.main.quarantined_appends` with the destination catalog, schema, table, error, original payload, and retry count.

### Automatic table management

- Infer column types from JSON values
- Create the table when it does not exist
- Add columns when later payloads contain new fields
- Make accepted data queryable within seconds

Numbers become `BIGINT` or `DOUBLE`, strings become `VARCHAR`, booleans become `BOOLEAN`, arrays and objects become `JSON`, and RFC3339 timestamps become `TIMESTAMP`.

You can also create the table manually when you need explicit types, constraints, or defaults. Append respects the existing schema and only adds missing columns.

### Request

```curl
curl "https://api.altertable.ai/append?catalog=my_catalog&schema=main&table=events" \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
-d '{"event":"signup","user_id":123}'
```

### Response (200 OK)

```json
{
"ok": true,
"task_id": "019d68d4-c4cb-7e40-ad50-fd4b68c2045e",
"error_message": null,
"error_code": null
}
```

## POST `/explain`

Estimate the files, bytes, and rows a SQL statement will scan without running it.

**Authentication:** HTTP Basic Auth\
**Permission:** Query access to referenced catalogs

| Parameter      | Type    | Location | Required | Description                                   |
| -------------- | ------- | -------- | -------- | --------------------------------------------- |
| `statement`    | string  | body     | Yes      | SQL statement to estimate.                    |
| `catalog`      | string  | body     | No       | Catalog to use for unqualified table names.   |
| `schema`       | string  | body     | No       | Schema to use for unqualified table names.    |
| `session_id`   | string  | body     | No       | Existing session to use for the estimate.     |
| `include_plan` | boolean | body     | No       | Include the raw EXPLAIN plan in the response. |

The response reports per-table row estimates and, when available, file and byte estimates after filter pruning. It also returns totals across all scanned tables and connection errors for catalogs that could not be attached.

### Request

```curl
curl https://api.altertable.ai/explain \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
-d '{"statement":"SELECT * FROM my_catalog.main.events WHERE timestamp >= current_date - 7","include_plan":false}'
```

## POST `/validate`

Check a SQL statement for syntax and semantic issues without executing it.

**Authentication:** HTTP Basic Auth\
**Permission:** Query access to referenced catalogs

| Parameter    | Type   | Location | Required | Description                             |
| ------------ | ------ | -------- | -------- | --------------------------------------- |
| `statement`  | string | body     | Yes      | SQL statement to validate.              |
| `catalog`    | string | body     | No       | Catalog to use for validation.          |
| `schema`     | string | body     | No       | Schema to use for validation.           |
| `session_id` | string | body     | No       | Existing session to use for validation. |

### Request

```curl
curl https://api.altertable.ai/validate \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
-d '{"statement":"SELECT * FROM non_existent_table"}'
```

## POST `/autocomplete`

Return ranked SQL completions for a partial statement.

**Authentication:** HTTP Basic Auth\
**Permission:** Query access to referenced catalogs

| Parameter         | Type    | Location | Required | Description                                                          |
| ----------------- | ------- | -------- | -------- | -------------------------------------------------------------------- |
| `statement`       | string  | body     | Yes      | Partial SQL statement.                                               |
| `catalog`         | string  | body     | No       | Catalog used to scope suggestions.                                   |
| `schema`          | string  | body     | No       | Schema used to scope suggestions.                                    |
| `session_id`      | string  | body     | No       | Existing session used for catalog context.                           |
| `max_suggestions` | integer | body     | No       | Maximum suggestions to return. Defaults to 20 and cannot exceed 200. |

The response includes the original statement, connection errors by catalog when present, and suggestions with their type, score, and replacement start position.

### Request

```curl
curl https://api.altertable.ai/autocomplete \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN" \
-d '{"statement":"SELECT ma","catalog":"my_catalog","schema":"main","max_suggestions":20}'
```

## GET `/query/{query_id}`

Inspect a running or completed query.

**Authentication:** HTTP Basic Auth\
**Permission:** Access to the query

| Parameter  | Type | Location | Required | Description                                       |
| ---------- | ---- | -------- | -------- | ------------------------------------------------- |
| `query_id` | UUID | path     | Yes      | Query identifier returned when execution started. |

### Request

```curl
curl https://api.altertable.ai/query/<query-id> \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN"
```

## DELETE `/query/{query_id}`

Cancel a running query.

**Authentication:** HTTP Basic Auth\
**Permission:** Access to the query

| Parameter    | Type   | Location | Required | Description                                         |
| ------------ | ------ | -------- | -------- | --------------------------------------------------- |
| `query_id`   | UUID   | path     | Yes      | Query identifier to cancel.                         |
| `session_id` | string | query    | Yes      | Session identifier returned when execution started. |

### Request

```curl
curl -X DELETE "https://api.altertable.ai/query/<query-id>?session_id=<session-id>" \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN"
```

## GET `/tasks/{task_id}`

Inspect an asynchronous task returned by the append API.

**Authentication:** HTTP Basic Auth\
**Permission:** Access to the task

| Parameter | Type | Location | Required | Description                                          |
| --------- | ---- | -------- | -------- | ---------------------------------------------------- |
| `task_id` | UUID | path     | Yes      | Task identifier returned by an asynchronous request. |

Task status is `pending` or `completed`.

### Request

```curl
curl https://api.altertable.ai/tasks/<task-uuid> \
-H "Authorization: Basic $ALTERTABLE_BASIC_AUTH_TOKEN"
```

### Response (200 OK)

```json
{
"task_id": "019d68d4-c4cb-7e40-ad50-fd4b68c2045e",
"status": "completed"
}
```