TopolearnConverting and exporting data is half of every pipeline, and DuckDB does it in one statement.
COPY ... TO writes a table or the result of any query to a file:
COPY (SELECT * FROM events WHERE ok) TO 'events.parquet' (FORMAT parquet);The format is inferred from the extension, or set with FORMAT. Useful options:
- Parquet:
COMPRESSION zstd(the default is snappy),ROW_GROUP_SIZE - CSV:
HEADER,DELIMITER PARTITION_BY (col, ...)writes a Hive-style folder tree, one subfolder per valueOVERWRITE_OR_IGNORE/OVERWRITEcontrol what happens if the target folder existsPER_THREAD_OUTPUTwrites one file per thread for maximum speed
The reverse direction, COPY tbl FROM 'file.csv', appends a file into an existing table, though INSERT INTO tbl SELECT * FROM 'file.csv' is more flexible.
A common one-liner is CSV to Parquet conversion: COPY (FROM 'raw.csv') TO 'clean.parquet'. To move an entire database, EXPORT DATABASE 'dir' writes every table plus a schema script, and IMPORT DATABASE 'dir' rebuilds it.
Sort data by a frequently filtered column before writing Parquet: row-group min/max statistics then become tight, and later filters skip far more data.
Example
import duckdb, os, tempfile
os.chdir(tempfile.mkdtemp())
con = duckdb.connect()
con.sql("CREATE TABLE t AS SELECT i AS id, 'item' || i AS name FROM range(5) r(i)")
con.sql("COPY t TO 'items.csv' (HEADER, DELIMITER ',')")
con.sql("COPY (SELECT * FROM t WHERE id >= 3 ORDER BY id) TO 'items.parquet' "
"(FORMAT parquet, COMPRESSION zstd)")
print(open("items.csv").read().splitlines()[:2]) # ['id,name', '0,item0']
print(con.sql("FROM 'items.parquet'").fetchall()) # [(3, 'item3'), (4, 'item4')]Remember
COPY (query) TO 'file'exports any result; format follows the extension or FORMATPARTITION_BYwrites Hive-partitioned folders- Sorting before writing Parquet makes later filtering much faster
Watch out for
- Writing a partitioned export twice into the same folder without OVERWRITE options and getting an error or duplicate files.