Push Data

You can push data into Hotdata through the API. This page covers the loading model, the upload sequence, and the write modes for publishing to a table; for data Hotdata pulls from systems you connect, see Pull Data.

The loading model

Teams arriving from a relational database generally expect CREATE TABLE followed by repeated INSERT statements. Hotdata does not work that way.

There is no DDL to run and no transactional insert path — SQL here is read-only. Data is loaded by publishing it: a file is supplied, and it becomes the contents of a table. Tables are created on first publish, so no migration step is required. Every publish is atomic — readers see either the previous contents or the new ones, never a partially written table.

The engine is optimised for bulk operations. A single call carrying ten million rows is the intended pattern; ten million single-row calls are not. Integrations that write row-at-a-time should batch upstream and publish at whatever interval freshness allows.

Every publish, regardless of size, goes through one endpoint:

POST /v1/databases/{database_id}/schemas/{schema}/tables/{table}/loads

The body carries a mode — how the incoming rows are applied to those already present, covered in the next section — and exactly one source. More than one source, or an unrecognised field name, is rejected with a 400 rather than silently ignored.

The upload sequence

File contents do not travel through the API: the bytes go straight to object storage, and the load names the file by id.

The clients already do this. upload_file opens the session, transfers the bytes — one PUT or many, concurrently — and finalizes, in one call:

upload = uploads_api.upload_file("orders.csv", content_type="text/csv")

databases_api.load_database_table(
    database_id, "public", "orders",
    hotdata.LoadManagedTableRequest(
        mode="replace",
        upload_id=upload.upload_id,
        format="csv",
    ),
)

The Python and Rust clients both carry it, for Parquet, CSV, or NDJSON, at any size up to the 16 GiB per-file limit. From the command line, hotdata databases load --file orders.csv does the same. The rest of this section is the protocol they implement.

Writing your own client

Every upload is the same three steps, with a fourth call to publish what arrived:

1. Request an upload session. POST /v1/uploads — declare the approximate size, and the response returns an upload id, one or more URLs to write to, and a finalize token.
2. PUT the bytes to the URL. They are written directly to object storage, so a 12 GB transfer does not hold an API connection open for its duration.
3. Finalize. POST /v1/uploads/{upload_id}/finalize commits the upload, after which the file can be loaded.
4. Load it. The endpoint above, with the upload_id as its source.

Only the second step varies with file size: a single PUT or several. The choice need not be made in advance — open a session and read mode from the response.

POST /v1/uploads
{ "filename": "orders.csv", "declared_size_bytes": 10485760, "content_type": "text/csv" }

# => { "upload_id": "upld7k2m9q4rx8vt3wnb5cyzhdfjs0", "mode": "multipart",
#      "part_size": 8388608, "part_urls": ["https://…", "https://…"],
#      "headers": {}, "finalize_token": "…" }

PUT <part_urls[0]>             # bytes [0, part_size); keep the response's ETag
PUT <part_urls[1]>             # the remainder

POST /v1/uploads/upld7k2m9q4rx8vt3wnb5cyzhdfjs0/finalize
X-Upload-Finalize-Token: <finalize_token>
{ "parts": [{ "part_number": 1, "e_tag": "\"9f8c1e5b7a2d4f60b3c8e1a9d7f4b206\"" },
            { "part_number": 2, "e_tag": "\"4b2af0c73d1e5a89f60b2c4e8d1a7f35\"" }] }

POST /v1/databases/dbid6lguax1dxn9y1xj5gxnameyywl/schemas/public/tables/orders/loads
{ "mode": "replace", "upload_id": "upld7k2m9q4rx8vt3wnb5cyzhdfjs0", "format": "csv" }

A session that comes back single carries a url instead: PUT the whole file to it, and finalize with no body. format is optional — it is detected from the Content-Type and the contents — and overrides that detection.

Four things the clients handle, and a new one has to:

A single file

For a file small enough to send in one request, the sequence is four calls and a single transfer.

A large file

Above the threshold, the session returns as multipart with a list of part URLs. PUT the slices concurrently, retain every ETag, and submit the set at finalize. Where the total size is not known in advance — a stream, for instance — omit declared_size_bytes and mint part URLs as the transfer proceeds. The finalize call is unchanged, and no URL expires while waiting on a slow producer.

Multiple files

Open all the sessions in a single call, then transfer them with whatever concurrency suits. Loading is the step that cannot be parallelised: a load takes a write lock on its target table, so a second concurrent load into the same table returns 409.

Small payloads

Below 2 MiB, the upload sequence adds more overhead than the payload warrants. CSV text can go directly in the load call's data field — one call, no session to finalize. This suits a few thousand rows of reference data, a lookup table, or the remainder of a stream flushed on a timer.

Ingestion limits

The figures below apply across all of the paths above. The multipart threshold and the part size are the two figures that are not fixed, which is why the session response — not this table — is the authority on which mode applies and on how the bytes are sliced.

Load modes

mode determines what happens to the rows already in the table. replace and append require nothing further; the remaining three require a key — the columns that identify a row — either declared on the table or supplied with the load.

Constraints

The first load into a new table must be replace. Every other mode requires a table that already has a shape to modify, and requesting one against a never-loaded table returns a 400 stating as much. Whether append and the keyed modes are available at all also depends on how the instance is provisioned, so treat that 400 as a branch to handle rather than an assertion failure.

A key identifies rows for matching; it is not a uniqueness constraint. The key names the columns that match incoming rows against existing ones — the match upsert, update, and delete act on. Nothing verifies the key is unique, and duplicates are not rejected. It is resolved per load — from the request where supplied, otherwise from the table's declaration — so changing it between loads silently changes what the merge matches on. Keeping it stable for a given table is the caller's responsibility.

On replace and append, schema changes are accepted only where they are lossless. A column the table has not seen before is added. A type that widens without loss is promoted — an INTEGER column becomes BIGINT where the payload requires it. A narrowing or cross-family change — text to number, or a decimal whose scale has moved — is refused with a 409 before anything is written; the table remains as it was and remains queryable.

The keyed modes hold the table's types rather than widening them. upsert, update, and delete read the payload at the types the table already carries, so a payload column typed more widely than its target is narrowed to match; an INTEGER key sent as a BIGINT matches as expected. A value that does not fit the target column — an id beyond the range of an INTEGER, or text where a number is expected — is refused with a 400 naming the column, and nothing is written. Widening a column is therefore a replace, which carries the table's whole contents; it is not something a merge does on the caller's behalf.

Error handling

Errors are returned as { "error": { "message", "code" } }. Branch on the code, never the message. Every response — rejections included — carries an X-Trace-Id, which should be logged and quoted in support requests.

Querying and result formats

One endpoint runs SQL. It responds in JSON with a preview of the rows and, for larger results, a result_id that can be retrieved in the format required.

POST /v1/query
X-Database-Id: dbid6lguax1dxn9y1xj5gxnameyywl

{ "sql": "SELECT status, count(*) FROM default.main.orders GROUP BY 1" }

# => { "query_run_id": …, "result_id": …, "columns": […], "rows": […], "truncated": false }

GET /v1/results/{result_id}?format=arrow   # or parquet, csv, md, json

Use Arrow or Parquet for any significant volume; JSON is provided for convenience rather than throughput. Large results paginate with offset and limit and return a Link header for the next page; Parquet is returned whole.

Queries are read-only. INSERT, COPY, and DDL are refused for every caller — writing is the load endpoint's job.

The dialect is HotSQL. It is standard analytical SQL with additions for search and geospatial work; the full syntax and function reference is at /docs/sql.

Further reading

• Core Concepts — the object model and authentication.
• Pull Data — ingesting from sources you connect.
• API Reference — request and response shapes, field by field.
• SQL Reference — the HotSQL syntax and function reference.
• Python SDK — the Python client; upload_file implements the upload sequence.
• Rust SDK — the Rust client, with the same upload_file.