Exporting files

Write query results as a file into your own bucket — a CSV a partner picks up every morning, a Parquet drop another system loads, a small Excel report — or into a tab of an existing Google Sheet.

COPY (SELECT id, name, amount FROM sales WHERE amount > 100)
TO 'raw_files/exports/sales_{today}.csv'
(HEADER, DELIMITER ';', NULL 'NA', DATEFORMAT '%Y-%m-%d');

This is the only form of COPY Thunderduck accepts: COPY (<query>) TO '<catalog>/<path>' (<options>). It works wherever a query runs: Studio, scheduled jobs, the HTTP API, the JDBC, SQLAlchemy and .NET drivers, and MCP. To export a whole table, select it: COPY (SELECT * FROM t) TO …; a bare COPY t TO … is refused.

Where files go

The target starts with the name of an object-storage catalog (S3, GCS, Azure or Cloudflare R2), followed by a path inside that catalog's bucket or container. You never write a bucket URL.

A Google Sheets target is the exception: its three segments are Drive and tab names, not a path, so none of the path rules below apply to it — no forbidden characters, no trailing /, and the middle segment must name a spreadsheet that already exists.

For everything else: refused targets include a URL or scheme (s3://…, https://…), a local path, a leading / or ~, any .. segment, a shared catalog, and a catalog that is not an object-storage or Google Sheets catalog (use INSERT or CREATE TABLE … AS for those). A path can't contain \ % ' " ? # * [ ] { }. Templates are the only exception: their braces — and the quotes of a {now,"…"} format — are replaced before the path is checked, so neither can reach the object name.

An existing file with the same name is overwritten.

Path templates

All of them are always UTC, whatever timezone a scheduled job uses, and a scheduled run uses the job's scheduled time, not the moment the container started. Anything else in braces is an error.

The single-value tokens exist for Hive-style partition folders, which are what Spark, Trino, Athena and BigQuery external tables expect:

COPY (SELECT * FROM events)
TO 'warehouse_lake/events/year={year}/month={month}/day={day}/hour={hour}/events.lance'
(FORMAT lance);

Each export writes one file into the folder named for its hour, so running it hourly builds year=…/month=…/day=…/hour=… over time. (The folder is named from the clock; it does not split rows by their own timestamps.)

{now} formats

Only {now} takes a format; {today}, {year}, {month}, {day} and {hour} are fixed. The format is Java-style, as in Spark's date_format:

- _ . : T and a space pass through as themselves. Any other character — including /, \ and % — is refused, because the result becomes a path.

Watch the date near midnight. A job scheduled at 00:30 in Europe/Istanbul (UTC+3) runs at 21:30 UTC the previous day, so {today} is the previous UTC day and the file is named for yesterday's date.

Formats

The format comes from the file extension, or from a FORMAT option; without an extension, the extension is added from the format.

If FORMAT and the extension disagree, the export is refused.

Allowed options: FORMAT, HEADER, DELIMITER / SEP, QUOTE, ESCAPE, NULL / NULLSTR, DATEFORMAT, TIMESTAMPFORMAT, COMPRESSION, CODEC, FORCE_QUOTE (col, …), ARRAY, ROW_GROUP_SIZE, ROW_GROUP_SIZE_BYTES, FILE_EXTENSION, FILE_SIZE_BYTES, PER_THREAD_OUTPUT, and SHEET (Excel only), and MODE (Lance only: 'overwrite' or 'append'). Values must be plain literals. Any other option, including PARTITION_BY, OVERWRITE*, USE_TMP_FILE and RETURN_FILES, is refused.

A Google Sheets export takes its own, smaller set — no delimiter, compression or partitioning — because a spreadsheet destination has none of those.

Examples by format

Each example writes into raw_files, an object-storage catalog with write access. Swap in your own catalog and query.

CSV

A semicolon-separated file with a header, NA for missing values and ISO dates, which is what most spreadsheet imports and partner SFTP drops expect:

COPY (SELECT order_id, customer, amount, order_date FROM sales.public.orders
      WHERE order_date = current_date - 1)
TO 'raw_files/exports/orders_{today}.csv'
(HEADER, DELIMITER ';', QUOTE '"', NULL 'NA', DATEFORMAT '%Y-%m-%d');

Compressed, with every text column quoted:

COPY (SELECT customer_id, name, email FROM crm.public.customers)
TO 'raw_files/exports/customers_{today}.csv.gz'
(FORMAT csv, HEADER, COMPRESSION gzip, FORCE_QUOTE (name, email));

Parquet

Typed, compressed and columnar: the best choice when another system (Spark, BigQuery, Athena, Snowflake, pandas) loads the file.

COPY (SELECT * FROM lake.default.events WHERE event_date = current_date - 1)
TO 'raw_files/exports/events_{today}.parquet'
(FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 100000);

JSON Lines

One JSON object per line. Good for log tools, jq, and loaders that stream a file line by line:

COPY (SELECT id, status, created_at FROM app.public.tickets WHERE status = 'open')
TO 'raw_files/exports/open_tickets_{now}.jsonl'
(FORMAT json, TIMESTAMPFORMAT '%Y-%m-%dT%H:%M:%S');

JSON array

The whole result as one array, [{…}, {…}]. Good for small files a web page or script reads in one go:

COPY (SELECT code, name, price FROM shop.public.products WHERE active)
TO 'raw_files/exports/products.json'
(FORMAT json, ARRAY true);

Excel

A workbook with a header row and a named sheet, for business users who open the file directly. At most 100,000 rows (see Excel limit):

COPY (SELECT region, sum(amount) AS revenue FROM sales.public.orders
      WHERE order_date >= date_trunc('month', current_date)
      GROUP BY region ORDER BY revenue DESC)
TO 'raw_files/reports/revenue_by_region_{today}.xlsx'
(HEADER true, SHEET 'Revenue');

Google Sheets

A tab of a spreadsheet in a Google Sheets catalog. Three segments — catalog, spreadsheet, tab — and the tab is created if it does not exist, or replaced if it does:

COPY (SELECT region, sum(amount) AS revenue FROM sales.public.orders
      WHERE order_date >= date_trunc('month', current_date)
      GROUP BY region ORDER BY revenue DESC)
TO 'sheet_reports/Monthly/Revenue by region'
(FORMAT gsheet);

The tab segment takes the usual templates, so a scheduled job can write one tab per day (UTC):

COPY (SELECT * FROM sales.public.orders WHERE order_date = current_date - 1)
TO 'sheet_reports/Monthly/Daily{now,"yyyyMMdd"}'
(FORMAT gsheet, HEADER true);

The spreadsheet segment does not render templates: it has to name a file that already exists in the folder.

Split Parquet for large exports

Several files of about 512 MB each in a dated folder, for exports too big for one file:

COPY (SELECT * FROM lake.default.clickstream WHERE event_date >= current_date - 30)
TO 'raw_files/exports/clickstream_{today}/'
(FORMAT parquet, FILE_SIZE_BYTES '512MB');

A whole table

COPY needs a query, so select the table:

COPY (SELECT * FROM app.public.customers)
TO 'raw_files/snapshots/customers_{today}.parquet';

Large exports: split files

CSV, Parquet and JSON can be split into several files in a folder. Give a folder target (ending in /) and a split option:

COPY (SELECT * FROM events)
TO 'raw_files/exports/events_{today}/'
(FORMAT parquet, FILE_SIZE_BYTES '512MB');

FILE_SIZE_BYTES (a number or a size such as '1GB') or PER_THREAD_OUTPUT true splits the export; a folder target without one is refused. Without a split option the export is one file.

Files from earlier runs are not removed. If a new run writes fewer files than the last one, the extra files stay in the folder. Use a {today} or {now} folder for split exports so each run starts empty. Excel can't be split.

Excel limit

An Excel export holds at most 100,000 rows; for more, use CSV or Parquet.

• Before running, if the query plan estimates more than 100,000 rows, the export is refused. This covers sorted queries. Grouped or aggregated queries are checked while running instead, because their estimates are unreliable.
• During the run, if the real result is larger, the run stops and fails.

No file is written in either case.

What the run returns

Instead of the query's rows, the result is a table with one row per file written:

Studio, Query History, the API, the drivers and MCP show it like any result. For a split export, only files this run wrote or changed are listed; stale files from earlier runs are not. If another writer adds files to the same folder during the run, they can appear in the list.

Write access

Two things must allow the write.

Your role. You need write on the target catalog (admins always have it; companies without custom roles are unrestricted). A role that grants only read is refused. The inner query also needs read on everything it reads. MCP clients need the query:write scope. Scheduled jobs run with their owner's access.

The catalog's credentials. Exports need the catalog's own credentials: a catalog registered without keys of its own can't receive exports, and the export is refused when you submit it. A catalog can receive exports only if the keys it was registered with can write. If they can't, the export fails with a message saying the catalog's credentials need write access. Re-register the catalog with write-capable keys:

Giving the catalog write keys also lets anyone with write on it delete or overwrite files through exports, so use a dedicated bucket.

Scheduled exports on Google Cloud. On the Google Cloud deployment, a scheduled export refused before it runs (an Excel export estimated at over 100,000 rows, or an owner without write on the catalog) appears in the scheduler's logs, not as an entry in the job's run history.

MCP

An MCP client needs the query:write scope to export. Exports work only when the MCP server uses the live Thunderduck engine; a local or demo-engine deployment refuses COPY.

Still refused

COPY … FROM, COPY <table> TO without a query, a target that is not an object-storage catalog path, EXPORT DATABASE, and every other COPY shape. See Accepted SQL.