Plain HTTP

Warp SQL is plain HTTP with JSON control messages and Arrow IPC data, so any HTTP client can use Hardcharger. This page walks through the protocol with curl, jq and a line of Python to read Arrow; the specification has every detail.

Discover

$ curl -s localhost:31337/warp | jq -c '{server: .server.name, protocol_versions, capabilities}'
{"server":"Hardcharger","protocol_versions":["1"],"capabilities":["prepared_statements","transactions","ingest","partitioned_ingest","long_poll","ipc_compression_zstd","ipc_compression_lz4_frame","result_partitions","openapi"]}

A client uses only the optional features listed in capabilities, and ignores names it doesn’t know. Everything else in the protocol (result retention included) is core: every server has it.

Sign in and open a session

$ H='Content-Type: application/json'
$ TOKEN=$(curl -s localhost:31337/warp/v1/auth/token -H "$H" \
    -d '{"grant_type": "password", "username": "hardcharger", "password": "change-me"}' | jq -r .access_token)
$ AUTH="Authorization: Bearer $TOKEN"
$ curl -s localhost:31337/warp/v1/sessions -H "$AUTH" -H "$H" -d '{"options": {"schema": "main"}}' | tee session.json | jq -c '{options, transaction, idle_timeout_s}'
{"options":{"catalog":"my","schema":"main","autocommit":true,"duckdb_arrow_lossless_conversion":false},"transaction":"none","idle_timeout_s":900}
$ URL=localhost:31337/warp/v1/sessions/$(jq -r .session_id session.json)
$ echo "export H='$H' AUTH='$AUTH' URL='$URL'" > env.sh

The token goes in Authorization: Bearer on every other request. Tokens last a day by default; sign in again (or let a driver renew) when one expires.

The session document lists every option the session has, with its current value. A bad option in POST /sessions fails the whole request (400 unknown_option or invalid_option_value), and no session is created. duckdb_arrow_lossless_conversion is an option of Hardcharger™’s own (DuckDB’s arrow_lossless_conversion setting for this session; see HUGEINT and UHUGEINT): names a server adds take a prefix, so they never collide with the protocol’s. transaction says whether a transaction is open (or aborted, refusing statements until a rollback); read it here rather than tracking BEGIN and COMMIT yourself.

Change session options

PATCH sets each option on its own and answers with every option and the ones that failed (errors is {} when none did):

$ . ./env.sh
$ curl -s -X PATCH $URL -H "$AUTH" -H "$H" -d '{"options": {"duckdb_arrow_lossless_conversion": true, "bogus": 1}}'
{"options":{"catalog":"my","schema":"main","autocommit":true,"duckdb_arrow_lossless_conversion":true},"errors":{"bogus":{"code":"unknown_option","detail":"unknown session option"}}}
$ curl -s -X PATCH $URL -H "$AUTH" -H "$H" -d '{"options": {"duckdb_arrow_lossless_conversion": false}}' | jq -c .errors
{}

Run a query

With Accept: application/vnd.apache.arrow.stream, the result streams back in the response as an Arrow IPC stream:

$ . ./env.sh
$ curl -s $URL/statements -H "$AUTH" -H "$H" -H 'Accept: application/vnd.apache.arrow.stream' \
    -d '{"sql": "SELECT i, i * 2 AS twice FROM range(3) t(i) WHERE i >= ?", "parameters": [1]}' -o rows.arrows
$ python3 -c "import pyarrow.ipc as ipc; print(ipc.open_stream('rows.arrows').read_all().to_pylist())"
[{'i': 1, 'twice': 2}, {'i': 2, 'twice': 4}]

Statements without rows answer with a JSON statement document instead, so check Content-Type:

$ . ./env.sh
$ curl -s $URL/statements -H "$AUTH" -H "$H" -H 'Accept: application/vnd.apache.arrow.stream' \
    -d '{"sql": "CREATE TABLE notes AS SELECT 1 AS id, '"'"'hello'"'"' AS body"}' | jq -c '{state, result}'
{"state":"succeeded","result":{"kind":"update","rows_affected":1}}

Parameters are positional (?, as an array) or named ($name, as an object), and always values. For exact types (decimals, timestamps, binary), use a prepared statement with Arrow parameters.

Submit, poll, fetch

For long queries, or behind a proxy that cuts long requests:

$ . ./env.sh
$ ID=$(curl -s $URL/statements -H "$AUTH" -H "$H" -H 'Prefer: respond-async' \
    -d '{"sql": "SELECT count(*) AS n FROM range(100000000)"}' | jq -r .statement_id)
$ curl -s $URL/statements/$ID -H "$AUTH" -H 'Prefer: wait=10' | jq -r .state
succeeded
$ curl -s $URL/statements/$ID/result -H "$AUTH" -o count.arrows
$ python3 -c "import pyarrow.ipc as ipc; print(ipc.open_stream('count.arrows').read_all()['n'][0])"
100000000
$ curl -s -o /dev/null -w '%{http_code}\n' -X DELETE $URL/statements/$ID -H "$AUTH"
204

Prefer: wait=N long-polls: the server answers as soon as the state changes, or after N seconds. Add Warp-Retain-Result: true when submitting to make the result re-readable and resumable (?from_batch=N); a server may decline, so check the statement document’s result.retained before relying on a re-read. See Warp SQL in one page.

A statement that has failed, or was cancelled, by the time the server answers its submission gets an error response (a problem document with the statement’s id in Warp-Statement-Id), never a success status with a failed or cancelled document. Here a statement times out while the submission waits for it:

$ . ./env.sh
$ curl -s $URL/statements -H "$AUTH" -H "$H" -H 'Warp-Statement-Timeout: 200' \
    -d '{"sql": "SELECT sum(hash(a.range + b.range)) FROM range(1000000) a, range(1000000) b"}' | jq -c '{status, code, cancel_reason}'
{"status":409,"code":"statement_timeout","cancel_reason":"timeout"}

Prepared statements

$ . ./env.sh
$ PREPARED=$(curl -s $URL/prepared -H "$AUTH" -H "$H" -d '{"sql": "SELECT ? + 1 AS next"}' | jq -r .prepared_id)
$ curl -s $URL/prepared/$PREPARED/execute -H "$AUTH" -H "$H" -H 'Accept: application/vnd.apache.arrow.stream' \
    -d '{"parameters": [41]}' -o next.arrows
$ python3 -c "import pyarrow.ipc as ipc; print(ipc.open_stream('next.arrows').read_all().to_pylist())"
[{'next': 42}]
$ curl -s -o /dev/null -w '%{http_code}\n' -X DELETE $URL/prepared/$PREPARED -H "$AUTH"
204

Executing with an Arrow body (Content-Type: application/vnd.apache.arrow.stream) runs the statement once per row: a batch of inserts in one request.

Preparing never runs the statement, however expensive the query or whatever it writes. The answer describes it: parameter_schema, and for a statement that returns rows result_kind: "rows" with result_schema (base64 Arrow schemas). An INSERT, UPDATE, DELETE or MERGE with RETURNING describes its rows too, and nothing is written until it is executed:

$ . ./env.sh
$ curl -s $URL/statements -H "$AUTH" -H "$H" -d '{"sql": "CREATE TABLE events (id INTEGER, kind VARCHAR)"}' | jq -r .state
succeeded
$ curl -s $URL/prepared -H "$AUTH" -H "$H" \
    -d '{"sql": "INSERT INTO events VALUES (?, ?) RETURNING id, upper(kind) AS kind"}' > insert.json
$ jq -r .result_kind insert.json
rows
$ python3 -c "import base64, json, pyarrow.ipc as ipc; print(ipc.open_stream(base64.b64decode(json.load(open('insert.json'))['result_schema'])).schema)"
id: int32
kind: string
$ curl -s $URL/statements -H "$AUTH" -H "$H" -H 'Accept: application/vnd.apache.arrow.stream' \
    -d '{"sql": "SELECT count(*) AS n FROM events"}' -o n.arrows
$ python3 -c "import pyarrow.ipc as ipc; print(ipc.open_stream('n.arrows').read_all()['n'][0])"
0

Metadata

The metadata endpoints answer with Arrow streams in Arrow Flight SQL’s result schemas, so driver code written for Flight SQL metadata carries over:

$ . ./env.sh
$ curl -s "$URL/metadata/tables?db_schema_filter_pattern=main&table_name_filter_pattern=notes" -H "$AUTH" -o tables.arrows
$ python3 -c "import pyarrow.ipc as ipc; print(ipc.open_stream('tables.arrows').read_all().select(['catalog_name', 'table_name', 'table_type']).to_pylist())"
[{'catalog_name': 'my', 'table_name': 'notes', 'table_type': 'BASE TABLE'}]

catalogs, db-schemas, tables, table-types, primary-keys, imported-keys, exported-keys, cross-reference and type-info are Arrow; server-info is JSON. Its keys are Flight SQL’s SqlInfo names in lower case; Hardcharger’s own take a prefix (duckdb_, hardcharger_), and a key that isn’t there means unknown, not false:

$ . ./env.sh
$ curl -s $URL/metadata/server-info -H "$AUTH" | jq .
{
  "server_name": "Hardcharger",
  "server_version": "…",
  "server_arrow_version": "…",
  "sql_identifier_quote_char": "\"",
  "sql_transactions_supported": true,
  "server_read_only": false,
  "duckdb_version": "…",
  "hardcharger_instance_id": "…",
  "hardcharger_access_control": false
}

server_version is the server’s version (as in discovery), server_arrow_version the Arrow library’s, duckdb_version DuckDB’s, hardcharger_instance_id this server process’s id, and hardcharger_access_control whether access control is on.

Close

$ . ./env.sh
$ curl -s -o /dev/null -w '%{http_code}\n' -X DELETE $URL -H "$AUTH"
204
$ curl -s $URL -H "$AUTH" | jq -r .code
session_not_found

Closing a session cancels its running statements and frees its results. A server never recreates a session implicitly.

Tip

Send an Idempotency-Key (a fresh UUID) with every request that creates a statement or writes data. If the response is lost, send the same request again with the same key: the server answers with what it already did instead of doing it twice.