Storage Query
Storage Query runs a single read-only SQL SELECT over CSV, JSON, NDJSON, and Parquet files held in Celigo Storage. A file or folder is referenced by its storage path, written where a table name would go (FROM "orders/2026/september.csv") or passed to a reader function such as read_csv_auto. Submitting a query validates and authorizes it synchronously, then runs it in the background: poll the job's status until it completes and fetch the rows page by page. The account's license must include the Storage Query entitlement (filesql); every call on an account without it answers 403 STORAGE_QUERY_NOT_ENTITLED.
Storage query job schema
Validates one read-only SQL SELECT, resolves every storage path it references with the caller's own permissions, and queues it for execution. The response is the new job in queued state; poll GET /v1/storage/query/{_id}/status until it is completed, then read rows from GET /v1/storage/query/{_id}/results.
Everything that can be checked before running is checked here and answered synchronously, and a rejected submit creates no job: the statement must parse, be exactly one SELECT, use only allow-listed functions, reference files only through bare storage paths or the storage reader functions, and every path must resolve to a file — or a folder of same-format files — the caller can read. A statement that references no file is accepted only when it is a pure constant expression. Supported file formats are CSV/TSV, JSON, NDJSON/JSONL, and Parquet; the reader is chosen from the file's extension (or its MIME type), and an explicit reader function must match it.
Read options are named arguments of the reader call, checked against a per-reader allowlist — for read_csv/read_csv_auto: header, delim, quote, escape, sample_size, types, names, dateformat, timestampformat, ignore_errors, null_padding, all_varchar, nullstr, allow_quoted_nulls, compression, union_by_name; for the JSON and NDJSON readers: sample_size, ignore_errors, dateformat, timestampformat, map_inference_threshold, maximum_depth, maximum_sample_files, maximum_object_size, columns, format, records, convert_strings_to_integers, compression, union_by_name; for read_parquet: union_by_name. Option values must be literals.
Submissions are rate-limited per account; over the limit the route answers 429 STORAGE_QUERY_SUBMIT_RATE_LIMITED with a Retry-After header, and every submit — accepted or rejected — counts toward it. The number of distinct files a statement may reference, the number of files a referenced folder may hold, and the total bytes a statement may read are each capped; the 422 that reports an exceeded cap names the current limit. Requires the account's license to include the Storage Query entitlement (filesql).
One read-only SQL statement to run over files in Celigo Storage. The body carries nothing else — reader options such as CSV delimiters belong inside the SQL, as named arguments of the reader function that reads the file.
A single SELECT statement. Storage files are its tables: write a file or
folder path where a table name would go, quoted as an identifier
(FROM "orders/2026/september.csv"), or pass the path as the first, literal
argument of a reader function (read_csv_auto, read_csv, read_json_auto,
read_json, read_ndjson_auto, read_ndjson, read_parquet) when the file
needs explicit read options. Paths are relative to the account's storage root.
Anything other than one SELECT — an UPDATE, two statements, a table function
that is not a storage reader, or a function outside the allowlist — is rejected
at submit with a 4xx and creates no job. A statement that references no file is
accepted only when it is a pure constant expression.
SELECT customer_id, SUM(total) AS spend FROM "orders/2026/september.csv" GROUP BY customer_id ORDER BY spend DESC LIMIT 100Query accepted and queued. Poll the status route with jobId.
Identifier of the new job — the {_id} for the status and results routes.
6ab5a7d0c772d334c44701beInitial lifecycle state of the job.
The body or the statement is malformed: sql is missing or not a non-empty
string (STORAGE_QUERY_SQL_REQUIRED), the statement does not parse
(STORAGE_QUERY_INVALID_SQL — the message carries the parser's position), or it
exceeds the size cap (STORAGE_QUERY_SQL_TOO_LARGE).
Unauthorized. The request lacks a valid bearer token, or the provided token failed to authenticate.
Note: the 401 response is produced by the auth middleware before the
request reaches the endpoint handler, so it does not follow the
standard {errors: [...]} envelope. Instead the body is a bare
{message: string} object with no code, no errors array. Callers
handling 401s should key off the HTTP status and the message string,
not try to destructure an errors[].
The account's license does not include the Storage Query entitlement
(STORAGE_QUERY_NOT_ENTITLED). Also returned when the caller's role,
environment, or account-share context does not permit the query.
A referenced storage path did not resolve to a file the caller can read
(STORAGE_QUERY_SOURCE_NOT_FOUND). A path outside the caller's reach looks
exactly like a missing one.
The statement parsed but is outside the supported surface. As-built cases:
not a SELECT (STORAGE_QUERY_NOT_SELECT); more than one statement
(STORAGE_QUERY_MULTIPLE_STATEMENTS); a table function other than the storage
readers, unnest, generate_series, or range
(STORAGE_QUERY_TABLE_FUNCTION_NOT_ALLOWED); a scalar function outside the
allowlist (STORAGE_QUERY_FUNCTION_NOT_ALLOWED); a reader option the reader does
not accept, or a positional option (STORAGE_QUERY_INVALID_READ_OPTION); a reader
whose first argument is not a literal path or list of paths
(STORAGE_QUERY_READER_PATH_NOT_LITERAL); a reader that does not match the
file's format (STORAGE_QUERY_READER_FORMAT_MISMATCH); a row generator with no
file reference (STORAGE_QUERY_NO_STORAGE_REFERENCE) or one that would generate
more rows than allowed (STORAGE_QUERY_GENERATOR_TOO_LARGE); a schema- or
catalog-qualified table name (STORAGE_QUERY_INVALID_SOURCE); an unsupported
file type (STORAGE_QUERY_UNSUPPORTED_FILE_TYPE); a folder that is empty, mixes
formats, or holds too many files (STORAGE_QUERY_FOLDER_EMPTY,
STORAGE_QUERY_MIXED_FORMAT_FOLDER, STORAGE_QUERY_FOLDER_TOO_MANY_FILES); too
many distinct files (STORAGE_QUERY_TOO_MANY_SOURCES); or more input bytes than
the cap (STORAGE_QUERY_INPUT_TOO_LARGE).
Too many submits for this account in the current window
(STORAGE_QUERY_SUBMIT_RATE_LIMITED). Wait for the number of seconds in the
Retry-After header before submitting again.
POST /v1/storage/query HTTP/1.1
Host: api.integrator.io
Authorization: Bearer YOUR_SECRET_TOKEN
Content-Type: application/json
Accept: */*
Content-Length: 135
{
"sql": "SELECT customer_id, SUM(total) AS spend FROM \"orders/2026/september.csv\" GROUP BY customer_id ORDER BY spend DESC LIMIT 100"
}{
"jobId": "6ab5a7d0c772d334c44701be",
"status": "queued"
}Returns the job's lifecycle state and timestamps. Once status is completed the response also carries the result manifest — rowCount, pageCount, truncated, columns, and executionMs — which is everything needed to page through GET /v1/storage/query/{_id}/results. A failed job carries error instead. Jobs are scoped to the submitting account: another account's job, or one that has expired, answers 404 exactly like an unknown id. Requires the account's license to include the Storage Query entitlement (filesql).
The jobId returned by POST /v1/storage/query.
6ab5a7d0c772d334c44701beCurrent state of the job.
A submitted query and its progress. Created by POST /v1/storage/query, read from
GET /v1/storage/query/{_id}/status. The result manifest (rowCount, pageCount,
columns, …) appears once the job completes; rows are fetched separately from
GET /v1/storage/query/{_id}/results. Jobs are visible only to the account that
submitted them and expire on their own after a retention period, after which the
status and results routes answer 404.
Identifier of the query job. Use it as {_id} on the status and results routes.
6ab5a7d0c772d334c44701beLifecycle of the job. Every job starts queued (the submit response) and moves
to running when a worker claims it, then to completed or failed; there is
no cancel. Constant queries usually complete within a few hundred milliseconds of
submission, so a status read straight after the submit may already show
completed.
completedPossible values: Timestamp when the submit accepted the query.
2026-09-24T22:44:32.845ZTimestamp when a worker began executing the statement. Absent while the job is queued.
2026-09-24T22:44:32.927ZTimestamp when the job reached completed or failed. Absent until then.
2026-09-24T22:44:33.004ZOnly present when status is completed. Milliseconds the statement took to execute, excluding time spent queued.
67Only present when status is completed. Total rows in the result set across
all pages; 0 when the query matched nothing, in which case pageCount is also 0
and page 1 of the results route returns an empty page.
1Only present when status is completed. When true, the result set hit the
service's row cap and rowCount counts only the rows that were kept; narrow the
query (a WHERE, or a LIMIT with ORDER BY) to see the rest.
Only present when status is completed. Number of result pages to fetch, numbered from 1; 0 for an empty result set.
1The path segment is not a valid job identifier (STORAGE_QUERY_INVALID_JOB_ID).
Unauthorized. The request lacks a valid bearer token, or the provided token failed to authenticate.
Note: the 401 response is produced by the auth middleware before the
request reaches the endpoint handler, so it does not follow the
standard {errors: [...]} envelope. Instead the body is a bare
{message: string} object with no code, no errors array. Callers
handling 401s should key off the HTTP status and the message string,
not try to destructure an errors[].
The account's license does not include the Storage Query entitlement (STORAGE_QUERY_NOT_ENTITLED).
No job with this id is visible to the caller (STORAGE_QUERY_JOB_NOT_FOUND) — unknown, expired, or submitted by another account.
Too many status or results reads for this account (STORAGE_QUERY_RATE_LIMITED). Wait for the seconds in Retry-After.
GET /v1/storage/query/{_id}/status HTTP/1.1
Host: api.integrator.io
Authorization: Bearer YOUR_SECRET_TOKEN
Accept: */*
{
"jobId": "6ab5a7d0c772d334c44701be",
"status": "completed",
"createdAt": "2026-09-24T22:44:32.845Z",
"startedAt": "2026-09-24T22:44:32.927Z",
"completedAt": "2026-09-24T22:44:33.004Z",
"executionMs": 67,
"rowCount": 1,
"truncated": false,
"pageCount": 1,
"columns": [
{
"name": "one",
"type": "INTEGER"
},
{
"name": "letter",
"type": "VARCHAR"
},
{
"name": "num",
"type": "DECIMAL(2,1)"
},
{
"name": "flag",
"type": "BOOLEAN"
}
]
}Returns one page of a completed job's rows. Pages run from 1 to the job's pageCount; omit page for the first one and keep requesting page + 1 while hasMore is true. A completed job that matched nothing has pageCount 0 and still serves page 1 as an empty page, so an empty result set never looks like a bad request. Rows exist only for a completed job: a queued or running job answers 409 until it finishes, and a failed job answers 409 permanently — read its error from the status route instead. Jobs are scoped to the submitting account, and an expired job answers 404. Requires the account's license to include the Storage Query entitlement (filesql).
The jobId returned by POST /v1/storage/query.
6ab5a7d0c772d334c44701bePage to return, from 1 to the job's pageCount. Defaults to 1 when omitted.
Anything that is not a positive integer within range — 0, a non-numeric
value, or a number past pageCount — answers 400 rather than being coerced.
1Example: 1One page of rows.
One page of a completed job's rows, as returned by
GET /v1/storage/query/{_id}/results?page=N. Pages are numbered from 1 up to the
job's pageCount; fetch them in turn until hasMore is false.
Page number served. 1 when the page query parameter was omitted.
1Total pages in the result set — the same value as pageCount on the job's status. 0 when the query matched nothing.
1Number of rows on this page (not the job-wide total the status route reports).
1When true, a further page exists — request page + 1. False on the last page and on an empty result set.
page is not a positive integer within 1..pageCount
(STORAGE_QUERY_PAGE_OUT_OF_RANGE — the message names the job's page count),
or the path segment is not a valid job identifier (STORAGE_QUERY_INVALID_JOB_ID).
Unauthorized. The request lacks a valid bearer token, or the provided token failed to authenticate.
Note: the 401 response is produced by the auth middleware before the
request reaches the endpoint handler, so it does not follow the
standard {errors: [...]} envelope. Instead the body is a bare
{message: string} object with no code, no errors array. Callers
handling 401s should key off the HTTP status and the message string,
not try to destructure an errors[].
The account's license does not include the Storage Query entitlement (STORAGE_QUERY_NOT_ENTITLED).
No job with this id is visible to the caller (STORAGE_QUERY_JOB_NOT_FOUND) — unknown, expired, or submitted by another account.
The job has not produced rows: it is still queued or running, or it failed
(STORAGE_QUERY_JOB_NOT_READY — the message names the current status). Poll the
status route; retry here only once it reports completed.
Too many status or results reads for this account (STORAGE_QUERY_RATE_LIMITED). Wait for the seconds in Retry-After.
GET /v1/storage/query/{_id}/results HTTP/1.1
Host: api.integrator.io
Authorization: Bearer YOUR_SECRET_TOKEN
Accept: */*
{
"page": 1,
"pageCount": 1,
"rowCount": 1,
"hasMore": false,
"rows": [
{
"one": 1,
"letter": "a",
"num": "2.5",
"flag": true
}
]
}Last updated
Was this helpful?