COPY TO Statement

The COPY ... TO statement writes the result of a query (or the contents of a table) to files. It is the only Chalk SQL statement that produces output outside of the query response itself; all other statements are read-only.

​

Examples

-- Export a query result to a single Parquet file
COPY (
    SELECT user_id, sum(amount) AS total
    FROM "my_bigquery.my_dataset.transactions"
    GROUP BY user_id
) TO 's3://my-bucket/exports/user_totals.parquet';

-- Export a table to a directory of CSV files
COPY "my_postgres.public.users" TO 's3://my-bucket/exports/users/' (FORMAT 'csv');

-- Export selected columns only
COPY "my_postgres.public.users" (user_id, email) TO 'gs://my-bucket/users.parquet';

-- Hive-partitioned Parquet output
COPY (SELECT * FROM "my_bigquery.my_dataset.transactions")
TO 's3://my-bucket/exports/transactions/'
(FORMAT 'parquet', PARTITION_BY (country, year));

-- Write an Iceberg table with a partition spec
COPY (SELECT * FROM "my_bigquery.my_dataset.transactions")
TO 's3://my-bucket/warehouse/transactions'
(FORMAT 'iceberg', PARTITION_BY (bucket(16, user_id), day(created_at)));

​

Syntax

COPY ( select_statement ) TO 'uri' [ ( option [, ...] ) ];
COPY "catalog.schema.table" [ (column [, ...]) ] TO 'uri' [ ( option [, ...] ) ];

The destination uri determines where files are written; the storage backend is selected from the URI scheme (for example a local path, s3://, or gs://). A URI ending in .parquet or .csv targets a single file, while a directory-style URI (trailing /) writes one or more files with generated names beneath it.

​

Options

Option	Description
FORMAT	'parquet' (default), 'csv', or 'iceberg'. When omitted, the format is inferred from the destination file extension, falling back to Parquet.
PARTITION_BY	Partitions the output. For Parquet and CSV, accepts column names and produces Hive-style partitioned directories. For Iceberg, additionally accepts the partition transforms bucket(N, col), truncate(W, col), year(col), month(col), day(col), and hour(col).
WRITE_BLOOM_FILTER	Parquet only: write bloom filters for the output files.
BLOOM_FILTER_FALSE_POSITIVE_RATIO	Parquet only: bloom filter false-positive ratio in (0, 1); defaults to 0.01. Setting this implies WRITE_BLOOM_FILTER true.

​

Iceberg output

With FORMAT 'iceberg', the destination URI names the Iceberg table root (a directory, not a file). Chalk SQL writes the Parquet data files and commits the Iceberg metadata (manifests and a new snapshot) beneath that root, creating the table on first write. PARTITION_BY entries define the table’s partition spec; a bare column name is an identity transform.

​

Limitations

• COPY ... FROM (loading data) is not supported — Chalk SQL is a read-only interface over your data sources.
• FORMAT 'delta' is not supported.
• Bloom filter options require FORMAT 'parquet' and cannot be combined with PARTITION_BY.