Celigo Storage query read options and format behavior

The query engine infers column names and types every time it reads a CSV or JSON file — that inference is how file reading works, not a setting you turn on. What you control is how it infers, through options you pass to the reader function in your SQL. This article covers the supported formats, the reader functions and their options, how columns are combined across a folder's files, and the format-specific behaviors to plan for.

For how to reference files and folders, see About querying Celigo Storage files. For the SQL surface, see Celigo Storage query SQL reference.

Supported formats

Celigo detects each file's format from its extension, and for a file with no recognized extension, from the MIME type recorded when the file was uploaded. A file whose format can't be detected is rejected.

XLSX, fixed-width, EDI, and XML aren't supported. Use file definitions and the existing parsers for those formats.

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 .csv beside .json, a folder that holds .json beside .ndjson, and a folder that holds .csv beside .tsv. It also rejects two files with the same extension but different MIME types, for example a.csv recorded as text/csv beside b.csv recorded as application/octet-stream. Files uploaded through different tools can carry different MIME types for the same extension; the Celigo CLI records .ndjson, .jsonl, and .parquet files as application/octet-stream. To combine formats, put each in its own folder and combine the folders with UNION ALL.

How inference works, and when it fails

Reading a CSV or JSON file requires knowing the columns and their types before any row can be parsed. The engine samples the first 20,480 rows by default and decides the types from that sample.

This fails on messy data in a specific and common way: a file whose sampled rows contain only integers in a column, and whose next row contains text, fails partway through with a message like Could not convert string "text" to 'BIGINT'. Nothing is wrong with the file — the sample was unrepresentative. You have three ways out, in increasing order of certainty:

• Widen the sample with sample_size, so more rows inform the decision.
• Declare the types explicitly per column with types (CSV) or columns (JSON), which skips inference for those columns.
• Skip the rows that don't parse with ignore_errors for CSV and NDJSON. See Malformed rows behave differently by format in this article.

Parquet files never need any of this. Their schema and row counts come from file metadata, so nothing is inferred.

Set read options in the reader function

Read options are passed in the SQL, by calling the reader function for the file's format and passing named options. They can't be passed as API request parameters — the request body carries only the sql field.

SELECT * FROM read_csv('/staging/orders/', header = false)

The reader must match the file's format (read_json on a .csv file fails with STORAGE_QUERY_READER_FORMAT_MISMATCH), the first argument must be a literal path or a literal list of paths, and only the documented options are accepted. An unknown option, a duplicate, a positional argument, or a value of the wrong type fails with STORAGE_QUERY_INVALID_READ_OPTION, which names the option. filename, hive_partitioning, and encoding are never accepted.

Available options

read_csv also accepts quote, escape, all_varchar, null_padding, allow_quoted_nulls, and compression. read_json and read_ndjson also accept format, records, convert_strings_to_integers, map_inference_threshold, maximum_object_size, and maximum_sample_files. read_parquet accepts only union_by_name. Celigo rejects sep, delimiter, skip, auto_detect, normalize_names, strict_mode, max_line_size, field_appearance_threshold, filename, hive_partitioning, and encoding with STORAGE_QUERY_INVALID_READ_OPTION.

Malformed rows behave differently by format

A malformed row is one that doesn't parse — the wrong number of fields, an unclosed quote, invalid syntax. With ignore_errors = true, a CSV row that does not parse is skipped, and a malformed NDJSON line comes back as a row of NULLs; filter those out, for example with WHERE id IS NOT NULL. Without it, a malformed row usually fails the query, but in a CSV file it can instead change what format detection decides, for example reading every line into one column or reporting a header column as not found. Set delim, names, and types explicitly in that case.

That choice only exists for CSV and NDJSON. ignore_errors is accepted on read_json, but it only takes effect for newline-delimited JSON — on a JSON array file, the engine reports that parse errors can't be ignored, and the query fails. If you need row-level fault tolerance on a dataset you don't control, NDJSON is the format that gives it to you.

How columns combine across a folder's files

When a query reads a folder, the files' schemas may not match — a column added last month exists in newer files but not older ones. By default, Celigo combines the columns by name: every column across 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 still match by name, and columns that appear only in later files are dropped.

The by-name tradeoff is worth knowing: a renamed column is absorbed as two half-populated columns rather than raising an error, because from the reader's point of view a renamed column is a new column plus a missing one. If the files should agree, declare names and types on the reader, or fix the files.

Nested and JSON-typed values

A JSON field whose values have consistent types arrives as its typed value: STRUCT values as objects and LIST values as arrays. A field whose values have inconsistent types across records is typed as JSON and arrives as a string containing JSON text; its column is reported as VARCHAR. Nesting is typed at any depth unless you set maximum_depth, in which case levels below it arrive as JSON text. Keys missing from some objects read as NULL.

If you set maximum_depth, set it deep enough for the fields you need. To pull specific fields out of a JSON-typed value, use json_extract and cast the result.

Numbers in results

• 64-bit integers and decimals arrive as strings. The engine types every whole-number column it infers from a CSV or JSON file as a 64-bit integer, and Celigo returns 64-bit integers, unsigned 64-bit integers, 128-bit integers, and decimals as strings so that no digits are lost. COUNT, SUM over an integer column, and // follow the same rule. AVG and / return numbers. To receive a number, declare the column's type in the reader, for example read_csv('/staging/orders/', types = {'qty': 'INTEGER'}), or cast the expression, for example CAST(COUNT(*) AS INTEGER).

• Non-finite numbers arrive as null. Division by zero doesn't raise an error — it produces infinity or not-a-number, which have no JSON representation, so Celigo emits null. To distinguish "divided by zero" from "genuinely null," guard the denominator:

  SELECT order_id,
         CASE WHEN quantity = 0 THEN NULL ELSE total / quantity END AS unit_price
  FROM '/staging/orders.csv'

Learn more

• About querying Celigo Storage files
• Query files in Celigo Storage
• Troubleshoot a Celigo Storage query job
• Celigo Storage query API reference
• Celigo Storage query SQL reference