About querying Celigo Storage files

The Celigo Storage query API lets you run SQL against files that are already in Celigo Storage and read the results back as JSON rows. You send one SELECT statement to the query API, point it at a file or a folder of files, and Celigo runs it over every row in every file, not one page at a time. Use it to aggregate, sort, join, and deduplicate across a dataset that spans many pages or many files — the operations a flow can't perform on its own.

This article explains when to query Celigo Storage files, how a query job runs, how to reference files and folders, who can read what, and the limits that apply. To run your first query, see Query files in Celigo Storage. To learn more about Celigo Storage, see Celigo Storage overview.

Querying is done through a REST API. There is no query step in the flow builder. To run a query from a flow, use an export on a Celigo APIs connection with an async helper — see Query Celigo Storage from a flow with an async helper.

When to query Celigo Storage files

Flows process records in pages of up to 5 MB, and each page is processed on its own. That design keeps flows fast and predictable, but it blocks any operation that needs the whole dataset at once. Group and sort on an export apply within one page, and for file providers within one file, so a group that spans two pages or two files comes out as two partial groups. Grouping on an HTTP export assumes the source already returned records sorted by the group key. Handlebars work within one record. A JavaScript hook sees one page.

Builders work around the limit in three ways: they write the data to a file provider and read it back in a second flow, they write custom hooks that only work when the data fits on one page, or they load everything into a data warehouse and query it there. Querying Celigo Storage replaces those workarounds with one SQL statement over files that are already in Celigo Storage. Use it in the following situations.

• Deduplicate across a folder of daily files. Invoices that arrive in separate files can repeat across files, and a hook can't see a duplicate that landed on a different page. SELECT DISTINCT over the folder sees every row in every file.
• Join two exports before you map them. An order file and a customer file share a key. Join them in SQL and read back one dataset, instead of looking up each customer one record at a time.
• Aggregate or roll up for a summary import. Sum, count, and average over tens of thousands of records that span hundreds of pages. Group by region or category and import the totals.
• Reconcile two systems' exports. Export the same entity from both systems into Celigo Storage, then use a join or a set operation to find the rows that exist in one and not the other.
• Filter a large file down before a flow reads it. Write the rows you need to a smaller result and reduce the volume before it reaches a destination.
• Inspect a file's columns before you build a mapping. DESCRIBE returns the column names and types the engine infers for a file, so you can see a file's shape before you write the real query.

Don't try to query files outside Celigo Storage, such as files in Amazon S3, Azure Blob Storage, or Google Drive. Use the connectors for those applications instead. Don't use it for XLSX, EDI, XML, or fixed-width files, which need file definitions and the existing parsers. It can't write, so use an import step to create or change files. It isn't a per-record lookup for a running flow: every query is an asynchronous job that you submit and poll, a fit for a dataset rather than a single record. A data warehouse remains the right place for analytical workloads that live outside the integration.

Querying Celigo Storage compared with the staging workaround

Without the query API, to group or sort across a full export, you build two flows. One exports the records and writes them to a file. A second reads the file, where the data now sits on one page, and groups or sorts it there. When you query Celigo Storage instead, you still stage the data in Celigo Storage, but one SQL statement replaces the second flow's group, sort, and hooks, and it works across every file in a folder.

How a query job runs

A query is a job. You submit it, Celigo runs it in the background, and you poll for the result. Every query follows the same three calls, whatever its size.

1. Submit. POST /v1/storage/query with the SQL statement. Before the request returns, Celigo parses the statement, checks it against the rules in Celigo SQL, finds every file and folder it references, and checks that your token can read each one. If any check fails, the response is an error that names the problem, and no job exists. If every check passes, the response carries a jobId and the status queued.
2. Poll. GET /v1/storage/query/{jobId}/status. The status moves from queued to running to completed or failed. A completed job reports the result's columns and types, the row count, and the number of result pages. A failed job reports an error code and message.
3. Fetch. GET /v1/storage/query/{jobId}/results?page=1, then the next page while hasMore is true. Celigo writes the result in pages of roughly 5 MB as the query runs, so a large result never arrives as one response.

The list of files a job reads is fixed when you submit it. A file uploaded to the folder after submit isn't included — submit the query again to include it. A file permanently deleted (purged from the recycle bin) between submit and execution fails the job. A file moved to the recycle bin is still read, and a replaced file is read with its new contents.

The execution timeout is 300 seconds, counted from when the job starts running, not from when you submitted it. Time spent queued doesn't count. Jobs run as capacity allows; further jobs wait in the queue, and Celigo never rejects a submission because too many are already running. A job that exceeds the timeout fails and returns no rows, never a partial result. If Celigo's service stops while a job is running, for example during a deployment, Celigo marks the job failed within about 7 minutes. Submit the query again.

The query API is read-only. The only statements that run are the SELECT family, including WITH, DESCRIBE, and SUMMARIZE, and a request carries exactly one statement. Every function in the statement must be on Celigo's allowlist, and the only table functions allowed are the file readers, unnest, generate_series, and range. Celigo rejects statements that write, such as INSERT, UPDATE, DELETE, COPY, and CREATE, before anything runs.

Reference files and folders in a query

You reference a file by the path you see on the File storage page, in quotes, where a table name would go. The leading slash is optional.

• A single file. FROM '/staging/orders.csv' reads that file.
• A folder. FROM '/staging/orders/' reads every file directly inside the folder as one dataset. The trailing slash is what makes it a folder reference. Files in subfolders aren't included, and Celigo rejects a folder reference that expands to more than 100 files.
• A folder without the trailing slash. FROM '/staging/orders' is a file reference. If orders is a folder, Celigo rejects the query.
• A file with a trailing slash. FROM '/staging/orders.csv/' is a folder reference. If orders.csv is a file, Celigo rejects the query.

For the error each rejected reference returns, see Troubleshoot a Celigo Storage query job.

Celigo detects each file's format from its extension: .csv and .tsv are read as CSV, .json as JSON, .ndjson and .jsonl as newline-delimited JSON, and .parquet as Parquet. For a file with no recognized extension, Celigo uses the MIME type recorded when the file was uploaded. Celigo rejects a file whose format it can't detect.

Every file in a queried folder must have the same format, and the check is exact: the files must share the same extension and MIME type. Celigo rejects a folder that holds policies.csv beside policies.json, and a folder that holds .json and .ndjson files together. To combine two formats, put each in its own folder and combine them with UNION ALL. When a folder's files have different columns, Celigo combines the columns by name by default: every column from every file appears in the result, and a file that lacks a column produces NULL for it. To keep only the first file's columns instead, set union_by_name = false on the reader. Columns are still matched by name, and columns that appear only in later files are dropped.

A bare path is enough for most queries. To control how a file is parsed, for example to declare that a CSV has no header row or to pin a column's type, call the reader function for the format and pass options: FROM read_csv('/staging/orders/', header = false). The reader must match the file's format, the first argument must be a literal path or a literal list of paths, and only the documented options are accepted. See Celigo storage query read options and format behavior.

Wildcard patterns aren't supported. Celigo rejects FROM '/staging/orders/*.csv' because * isn't a valid character in a storage path. Reference the folder instead.

Celigo SQL

Queries are written in Celigo SQL, a curated subset of standard SQL that is PostgreSQL-compatible where syntax is otherwise ambiguous. It covers SELECT with DISTINCT, WHERE, GROUP BY and HAVING, ORDER BY, LIMIT and OFFSET, joins, aggregates, window functions, common table expressions, set operations, subqueries, inline VALUES tables, CASE, nested-field access with dot notation, list indexing, UNNEST, and TRY_CAST. For the full list, including the behaviors that differ from PostgreSQL, see Celigo Storage query SQL reference.

Celigo checks every statement before it runs and rejects anything outside the rules with an error that names the problem. It rejects a statement outside the SELECT family and a request with more than one statement. It rejects a function that isn't on the allowlist, and the error names the function. The allowlist is the engine's own function library minus the functions that report engine or host state, such as version() and current_setting(). Table references must be storage paths or common table expressions defined in the same statement, so Celigo rejects schema-qualified names such as information_schema.tables. generate_series and range are allowed, but their arguments must be whole-number literals so that Celigo can check the row count before the query runs, and a query that generates rows must also read at least one storage file.

DESCRIBE is a statement like any other: DESCRIBE SELECT * FROM '/staging/orders.csv' runs as a query job and returns one row per column with the column's name and inferred type. There is no separate schema endpoint.

Authorization

A query reads only the files its caller can read, and the check happens when you submit, for every file the query references.

• A Full access API token reads any file in the account's Celigo Storage.
• A Custom API token reads only files inside the folders listed in its File storage paths. A file or folder reference must sit at or under a granted folder. Celigo denies a reference to a folder above a granted folder, even though some of its contents are in scope — it checks the reference, not the files the reference would expand to. A folder reference inside a granted folder can only expand to files that are also inside it. A Custom token with no File storage paths can't reach Celigo Storage at all, and Celigo refuses its submit request. See Restrict an API token to specific Celigo Storage paths.
• A personal access token reads the files its user can open, under the user's role and integration access. See Create an API token or Personal Access Token.

A denial looks like a missing file. A path outside your token's scope and a path that doesn't exist both return the same not-found error, STORAGE_QUERY_SOURCE_NOT_FOUND, so a query can't be used to discover files you can't read. When you're sure a path is right, check the token's File storage paths first.

Jobs are private to the identity that submitted them. Another identity's job looks like a job that doesn't exist.

What Celigo records

Celigo keeps a record of each job for about 24 hours after it finishes: its status, when it was created, started, and completed, how long it ran, the error if it failed, and, if it completed, the result's columns, row count, and page count. The result pages are kept for the same period. After that, status and results requests for the job return not found. Celigo doesn't keep the SQL text or the list of files a job read once the job has finished, so retrieve anything you want to keep before the record expires.

Limits and unsupported behavior

• Execution timeout: 300 seconds, counted from when the job starts running. A job that exceeds it fails with STORAGE_QUERY_TIMEOUT and returns no rows.
• Result row cap: 1,000,000 rows. A result that exceeds the cap is cut off at 1,000,000 rows, and the job's status response reports truncated: true. The job still completes, so check truncated before you treat a large result as complete. To stay under the cap, narrow the query with WHERE or use LIMIT in the statement.
• Rate limiting. Queries are rate-limited per environment, and query executions aren't billed as API calls.
• 10 sources per query. Each distinct file or folder path counts once, however many files a folder holds.
• 100 files per folder. Celigo rejects a folder reference that expands to more.
• 10 GB of input per query, summed across every file the query reads.
• 16 KB of SQL per request.
• One statement per request.
• Whole-number columns arrive as strings. Celigo returns 64-bit integers, unsigned 64-bit integers, 128-bit integers, and decimals as strings so that no digits are lost, and the engine types every whole-number column it infers from a CSV or JSON file as a 64-bit integer. count() and sum() over an integer column follow the same rule. avg() and / return numbers. To get a number, declare the column's type in the reader, for example read_csv('/staging/orders.csv', types = {'qty': 'INTEGER'}), or cast in the statement, for example CAST(SUM(qty) AS DOUBLE). See Celigo storage query read options and format behavior.
• Non-finite numbers are null. A DOUBLE that is infinite or not a number, for example the result of dividing by zero, arrives as null. A string column that happens to contain the text Infinity is unchanged.
• Values the engine types as JSON arrive as strings containing JSON text, in a column reported as VARCHAR. This includes a key whose values have different types across records, and nesting below a maximum_depth you set on the JSON reader; without maximum_depth, nesting is typed at any depth. STRUCT values arrive as objects and LIST values as arrays.
• No wildcards. A source is a single file or a whole folder.
• One format per folder.
• Empty files return zero rows, not an error. A zero-byte file queried on its own completes with zero rows and one placeholder column. Inside a folder, it adds no rows to the folder's result.
• No cancel. A job ends when it completes, fails, or times out.
• Files are read as they are at execution time. The set of files is fixed at submit, but the bytes are read when the job runs.

Frequently asked questions

Can I write to a file with SQL? No. Celigo SQL has no INSERT, UPDATE, DELETE, or COPY, and a statement that isn't a SELECT is rejected before it runs. Use an import step to write files.

Can I query files in Amazon S3, Azure Blob Storage, or Google Drive? No. Queries read Celigo Storage only. Transfer the files into Celigo Storage first, or use the connector for that storage.

Can I use a query in a flow? Not as a flow step. An export on a Celigo APIs connection can submit the query, and an async helper can poll the job and fetch its pages. See Query Celigo Storage from a flow with an async helper.

Why is my whole-number column a string? Celigo returns 64-bit integers as strings so that values beyond JavaScript's safe integer range aren't rounded, and the engine infers whole-number columns as 64-bit integers. To get a number, declare the column as INTEGER in the reader options, or cast the expression. See Limits and unsupported behavior.

Why does a query that worked on one file fail on the folder? The most common cause is type drift. The engine infers each column's type from a sample of rows, and a later file or a later row doesn't match. Widen the sample with sample_size, declare the types, or set ignore_errors for CSV and NDJSON. See Celigo storage query read options and format behavior. The second most common cause is a folder that holds more than one format.

Can I query more than 100 files? Not in one folder reference. Split the files across folders and combine the folders with UNION ALL, up to 10 sources per query.

Why did my query return different values when I ran it again? Nondeterministic functions such as random() and now() are allowed, and each run evaluates them again. Avoid them when you need a repeatable result.

How long are results available? For about 24 hours after the job finishes. Fetch every page you need before then.

Can I cancel a running query? No. A job runs until it completes, fails, or reaches the 300-second timeout.

Learn more

• Query files in Celigo Storage
• Query Celigo Storage from a flow with an async helper
• Troubleshoot a Celigo Storage query job
• Celigo Storage query SQL reference
• Celigo storage query read options and format behavior
• Celigo Storage query API reference
• Celigo Storage overview
• Create an API token or Personal Access Token
• Restrict an API token to specific Celigo Storage paths