Writing files¶

MIINT provides custom COPY formats for writing bioinformatics file formats. The writers extend the native SQL methods for writing files through the COPY ... TO '...' (FORMAT csv) style. A quick example is below, where we read a FASTQ file and write it as FASTA:

COPY (SELECT * FROM read_fastx('some_file.fastq')) 
TO 'some_other_file.fasta' (FORMAT FASTA);

Options follow DuckDB's rules for COPY options in every format here, as they do for CSV and Parquet:

A boolean option given alone means true: (FORMAT FASTQ, INTERLEAVE) is INTERLEAVE true.
An option the format does not have is refused, and the error may suggest options with similar names. The names of DuckDB's own COPY options, such as OVERWRITE, are not refused as unknown; DuckDB handles them, and refuses those a format cannot support, such as FILE_SIZE_BYTES.
Other values are cast to the option's type, so COMPRESSION_LEVEL '3' is level 3 and COMPRESSION_LEVEL 1.6 is level 2. An option with no value or a NULL value is refused.

COMPRESSION takes the same values in the text formats, FASTQ, FASTA, NEWICK and SAM, in any case: 'gzip' or 'gz', and 'none' or 'uncompressed'. Without it, a path ending in .gz is written as gzip. zstd ('zstd', or a .zst path) is refused. BIOM's COMPRESSION takes the same values but sets HDF5's compression inside the file, and BAM's takes only gzip; see BIOM and SAM / BAM.

FASTQ, FASTA and NEWICK write a path with a URI scheme other than file:// (s3://, https://, az://, ...) through DuckDB's file systems, which load the extension that serves the scheme. For a scheme no file system serves, DuckDB opens the path as a local file, as it does for its own formats. SAM, BAM, UBAM and BIOM write local files only and refuse a remote target. In every format a file: URI is the local file it names, as DuckDB reads it (file:///p, file://localhost/p and file:/p are /p; on Windows, file:///C:/p is C:/p), and gzip output to it takes COMPRESSION_LEVEL as any local path does.

Table of Contents¶

FASTQ - Write FASTQ sequence files
FASTA - Write FASTA sequence files
SAM / BAM - Write SAM/BAM alignment files
UBAM - Write unaligned reads BAM (uBAM) files
BIOM - Write BIOM observation matrix files
NEWICK - Write Newick phylogenetic trees

FASTQ¶

Write query results to FASTQ format files. Requires read_id, sequence1, and qual1 columns from read_fastx output.

Required columns: - read_id (VARCHAR, BIGINT, or UUID): Sequence identifier. BIGINT is stringified to decimal and UUID to its canonical 36-char lowercase form; the record name on disk is always text. Round-trip via read_fastx returns read_id as VARCHAR (the BIGINT/UUID type is not preserved on disk — recover it with e.g. read_id::BIGINT). - sequence1 (VARCHAR): DNA/RNA sequence - qual1 (BLOB): Quality scores as raw bytes

Optional columns: - comment (VARCHAR): Comment line (only included if INCLUDE_COMMENT=true). Note this defaults to false, so a read_fastx → COPY round trip drops the comment unless you pass INCLUDE_COMMENT true. For Illumina data the comment carries the read number, filter flag and index (1:N:0:ATCACG), so pass it when a faithful round trip matters. - sequence_index (BIGINT): Used as identifier if ID_AS_SEQUENCE_INDEX=true - sequence2 (VARCHAR): Second read for paired-end data - qual2 (BLOB): Quality scores for second read

Parameters: - QUAL_OFFSET (default: 33): Quality score encoding offset (33 or 64) - INCLUDE_COMMENT (default: false): Include comment field in output - ID_AS_SEQUENCE_INDEX (default: false): Use sequence_index as identifier instead of read_id - INTERLEAVE (default: false): When the data is paired-end, write R1/R2 interleaved into a single file. Optional — see "Single-end vs paired-end output" below. Cannot be combined with the {ORIENTATION} placeholder. - COMPRESSION (default: auto): Enable gzip compression (auto-detected from .gz extension). Local .gz output is written as BGZF (block-gzip via htslib): a fully standard gzip file readable by any gzip tool, but compressed in parallel — it follows DuckDB's thread count (PRAGMA threads) and runs ~10x faster than single-threaded gzip on multi-core hosts, while staying block-indexable. Output order is preserved. (Remote scheme:// targets fall back to single-threaded gzip.) - COMPRESSION_LEVEL (0-9, default: 1): BGZF compression level for local .gz output, as for BAM. Lower is faster and writes larger files. Refused for uncompressed output and for remote targets, whose gzip writer takes no level.

Single-end vs paired-end output:

You do not need to project away sequence2/qual2 or pre-declare whether the data is paired. read_fastx always emits sequence2/qual2 (NULL for single-end), and the writer decides per record from whether those values are NULL:

{ORIENTATION} in the path selects split output: R1 → the R1 file, R2 → the R2 file. If the data turns out to be entirely single-end, only the R1 file is written (no empty R2 file is created).
No {ORIENTATION} selects a single file. Single-end data is written as-is. Paired-end data is interleaved only if INTERLEAVE=true.
A record is paired when both sequence2 and qual2 are non-NULL, and single-end when both are NULL. A single COPY must be all one or all the other.

The writer fails loud (rather than guessing or silently dropping data) when: - paired data is sent to a single-file path without INTERLEAVE=true (add {ORIENTATION} to split, or INTERLEAVE=true to interleave), - {ORIENTATION} is combined with INTERLEAVE=true (contradictory), - sequence2 and qual2 disagree on NULL-ness within a record, or - the input mixes single-end and paired-end records.

Examples:

-- Basic single-end FASTQ output. SELECT * carries NULL sequence2/qual2 columns;
-- they are recognized as single-end and a single file is written.
COPY (SELECT * FROM read_fastx('input.fastq'))
TO 'output.fastq' (FORMAT FASTQ);

-- Paired-end interleaved output (single file)
COPY (SELECT * FROM read_fastx('R1.fastq', 'R2.fastq'))
TO 'output.fastq' (FORMAT FASTQ, INTERLEAVE true);

-- Paired-end split files (the {ORIENTATION} placeholder selects split output)
COPY (SELECT * FROM read_fastx('R1.fastq', 'R2.fastq'))
TO 'output_{ORIENTATION}.fastq' (FORMAT FASTQ);

-- Single-end data with {ORIENTATION} simply writes the R1 file (output_R1.fastq.gz)
COPY (SELECT * FROM read_fastx('input.fastq'))
TO 'output_{ORIENTATION}.fastq.gz' (FORMAT FASTQ, COMPRESSION gzip);

-- Compressed output with custom quality offset
COPY (SELECT * FROM read_fastx('input.fastq'))
TO 'output.fastq.gz' (FORMAT FASTQ, QUAL_OFFSET 33, COMPRESSION gzip);

-- Use sequence index as identifier
COPY (SELECT * FROM read_fastx('input.fastq'))
TO 'output.fastq' (FORMAT FASTQ, ID_AS_SEQUENCE_INDEX true);

FASTA¶

Write query results to FASTA format files. Requires read_id and sequence1 columns from read_fastx output.

Required columns: - read_id (VARCHAR, BIGINT, or UUID): Sequence identifier. BIGINT is stringified to decimal and UUID to its canonical 36-char lowercase form; the record name on disk is always text. Round-trip via read_fastx returns read_id as VARCHAR (the BIGINT/UUID type is not preserved on disk — recover it with e.g. read_id::BIGINT). - sequence1 (VARCHAR): DNA/RNA/protein sequence

Optional columns: - comment (VARCHAR): Comment line (only included if INCLUDE_COMMENT=true) - sequence_index (BIGINT): Used as identifier if ID_AS_SEQUENCE_INDEX=true - sequence2 (VARCHAR): Second read for paired-end data

Parameters: - INCLUDE_COMMENT (default: false): Include comment field in output - ID_AS_SEQUENCE_INDEX (default: false): Use sequence_index as identifier instead of read_id - INTERLEAVE (default: false): When the data is paired-end, write R1/R2 interleaved into a single file. Optional — see "Single-end vs paired-end output" below. Cannot be combined with the {ORIENTATION} placeholder. - COMPRESSION (default: auto): Enable gzip compression (auto-detected from .gz extension). Local .gz output is written as BGZF (block-gzip via htslib): a fully standard gzip file readable by any gzip tool, but compressed in parallel — it follows DuckDB's thread count (PRAGMA threads) and runs ~10x faster than single-threaded gzip on multi-core hosts, while staying block-indexable. Output order is preserved. (Remote scheme:// targets fall back to single-threaded gzip.) - COMPRESSION_LEVEL (0-9, default: 1): BGZF compression level for local .gz output, as for BAM. Lower is faster and writes larger files. Refused for uncompressed output and for remote targets, whose gzip writer takes no level.

Single-end vs paired-end output:

FASTA paired-end output works the same way as FASTQ (a record is paired when sequence2 is non-NULL), without the quality columns:

{ORIENTATION} in the path writes split R1/R2 files; an entirely single-end dataset writes only the R1 file.
No {ORIENTATION} writes a single file; paired data is interleaved only if INTERLEAVE=true.
The writer fails loud when paired data is sent to a single-file path without INTERLEAVE=true, when {ORIENTATION} is combined with INTERLEAVE=true, or when the input mixes single-end and paired-end records.

Examples:

-- Basic FASTA output (single-end; NULL sequence2 columns are recognized as single-end)
COPY (SELECT * FROM read_fastx('input.fasta'))
TO 'output.fasta' (FORMAT FASTA);

-- Paired-end split files (the {ORIENTATION} placeholder selects split output)
COPY (SELECT * FROM read_fastx('R1.fasta', 'R2.fasta'))
TO 'output_{ORIENTATION}.fasta' (FORMAT FASTA);

-- Compressed with comments
COPY (SELECT * FROM read_fastx('input.fasta'))
TO 'output.fasta.gz' (FORMAT FASTA, INCLUDE_COMMENT true);

SAM and BAM¶

Write query results to SAM or BAM format files. Requires all mandatory SAM columns from read_alignments output.

Required columns: - read_id (VARCHAR, BIGINT, or UUID): Query template name - flags (USMALLINT): Bitwise flags - reference (VARCHAR, BIGINT, or UUID): Reference sequence name - position (BIGINT): 1-based leftmost mapping position - mapq (UTINYINT): Mapping quality - cigar (VARCHAR): CIGAR string - mate_reference (VARCHAR, BIGINT, or UUID): Reference name of mate/next read - mate_position (BIGINT): Position of mate/next read - template_length (BIGINT): Observed template length

Identifier-column types (read_id, reference, mate_reference): - Each column is independently VARCHAR, BIGINT, or UUID. The three need not match. Other numeric types are rejected at bind time with the message Column '<name>' must be VARCHAR, BIGINT, or UUID. - BIGINT values are stringified to decimal on write; UUID values are stringified to their canonical 36-char lowercase form. The on-disk SAM/BAM record always carries text. - A NULL in any of these three columns is written as the SAM * sentinel, whatever the column's type. So a single-end record can carry NULL (or '*') for mate_reference interchangeably. - Round-trip through read_alignments / read_sam returns these columns as VARCHAR regardless of how they were written; the BIGINT/UUID type is not preserved on disk. - HTSlib normalises RNEXT to = when it equals RNAME at the reference-tid level. This applies to all id types — a BIGINT/UUID mate_reference that matches reference still appears as = when the file is read back.

NULL handling in the required columns: a NULL is rejected in flags, mate_position and template_length (Column '<name>' is NULL for read: <id>), because it has no SAM spelling and no defensible default. read_id, reference and mate_reference accept NULL and write the * sentinel, as above. position, mapq and cigar depend on the unmapped flag — see below. (stop_position is not consulted by the writer at all.)

cigar, position, mapq: whether a record may omit these depends on its unmapped flag (0x4):

Unmapped (0x4 set): none of them is expected. cigar may be '*', '' or NULL and is written as *; position and mapq may be NULL and are written as 0. Nothing can be lost, since there is no alignment to describe. This is what lets align_minimap2(…, include_unmapped := true) output — which carries NULL in exactly these columns — be written directly.
Mapped (0x4 clear): a CIGAR is required, and position/mapq must be present. A missing CIGAR ('*', '' or NULL) is rejected with Mapped read '<id>' has no CIGAR (flags=<n>).

The mapped case is refused rather than written because htslib cannot represent it: samtools reads such a record back and silently rewrites it as unmapped ([W::sam_parse1] mapped query must have a CIGAR; treated as unmapped, FLAG 0 → 4). A CIGAR dropped by a bad join would otherwise become a mapped record with no alignment, and the COPY would report success. If you genuinely mean "did not align", set the 0x4 flag.

read_alignments / read_sam return '' for a record whose on-disk CIGAR is *, so both spellings occur naturally and COPY (SELECT * FROM read_alignments(...)) round-trips unmapped reads unchanged. One consequence worth knowing: a mapped record with no CIGAR — which older BAM files can contain, since bam_read1 accepts n_cigar == 0 without the rewrite sam_parse1 applies — was previously copied through and is now rejected. Set 0x4 on such records, or filter them.

Note that these validation errors, like the pre-existing position checks, fire mid-write: the output file will already exist and, for BAM, be structurally incomplete. Treat a failed COPY's output as garbage rather than a partial result.

Optional columns: - tag_as, tag_xs, tag_ys, tag_xn, tag_xm, tag_xo, tag_xg, tag_nm (BIGINT): Optional integer tags - tag_yt, tag_md, tag_sa (VARCHAR): Optional string tags

SAM and BAM are written to local files only; a remote target (s3://...) is refused.

Parameters: - INCLUDE_HEADER (default: true): Include header with reference sequences - Note: BAM format requires INCLUDE_HEADER=true (headers are mandatory in BAM files) - REFERENCE_LENGTHS (VARCHAR, required if INCLUDE_HEADER=true): Table or view name containing reference sequences. Must have at least 2 columns: first column = reference name (VARCHAR, BIGINT or UUID, as reference takes, matched as its text (BIGINT in decimal, UUID in the lowercase hyphenated form)), second column = reference length (INTEGER/BIGINT). Column names don't matter. Views are fully supported and can include computed columns. Duplicate reference names are collapsed, keeping the first occurrence.

@SQ order, and how to write a coordinate-sorted BAM:

@SQ lines are emitted sorted by reference name, in the name column's own order (numeric for BIGINT names), regardless of the order of rows in REFERENCE_LENGTHS. This matters because a reference's index in the @SQ list is its TID, and a BAM is coordinate-sorted by TID, not by reference name — so the header order determines what "sorted" means.

Because the order is name-sorted, sorting your records the obvious way produces a genuinely coordinate-sorted file, as long as the name column and reference have the same type (a VARCHAR reference sorts '10' before '2', a BIGINT name column puts 2 first) and VARCHAR names are compared with the default binary collation:

COPY (SELECT * FROM aln ORDER BY reference, position)
TO 'sorted.bam' (FORMAT BAM, REFERENCE_LENGTHS 'refs');
-- samtools index sorted.bam   -> succeeds

Two consequences worth knowing:

The order does not depend on how REFERENCE_LENGTHS was built (ASC, DESC or shuffled all give the same header), and it does not depend on PRAGMA threads. This is deliberate: DuckDB does not guarantee the row order of a parallel table scan, so the table's own row order could not be a reproducible contract.
The header is readable back from SQL with read_alignment_header('file.bam'), which exposes @SQ as (tid, reference, length) — see Reading. That is the only way to observe tid, and therefore the only way to check this ordering contract on a BAM.
Karyotype order is not expressible: 'chr10' sorts before 'chr2', so a human-genome BAM will be ordered chr1, chr10, chr11, …, chr2 rather than chr1, chr2, …, chr10. The file is still internally consistent and indexable; it simply won't match a reference-order convention that isn't lexicographic. Sorting your records by reference keeps them aligned with whatever the header says.

With INCLUDE_HEADER=false no @SQ lines are written at all, so the ordering above does not apply; each record's RNAME is written verbatim from its reference value. (This mode is SAM-only — BAM requires a header.)

REFERENCE_LENGTHS must be exhaustive when a header is written. Supplying the table asserts it is the complete reference set, so a record whose reference (or mate_reference) is absent from it is rejected: Column 'reference' is 'ctg000002' for read 'r2', which is not in the REFERENCE_LENGTHS table. The header is serialized before the records are, so it cannot gain @SQ lines afterwards — a missing reference has no representation in the file being written, and earlier versions lost such records silently (SAM read the name back as *, BAM dropped the record). Derive the table from the same relation you are writing, e.g. SELECT DISTINCT reference … FROM aln. With INCLUDE_HEADER=false there are no @SQ lines to be exhaustive about, so any reference is accepted and written verbatim. - SEQUENCE_DATA (VARCHAR, optional): Table or view name containing original read sequences from read_fastx. When provided, writes actual SEQ and QUAL fields into the output instead of *. See Sequence Data below. - COMPRESSION (default: auto): Enable gzip compression for SAM (auto-detected from .gz extension); values as for the other text formats, above. BAM is always BGZF-compressed: it accepts 'gzip' or 'gz', in any case, and refuses any other value, which it would ignore; COMPRESSION_LEVEL 0 writes its BGZF blocks uncompressed. - COMPRESSION_LEVEL (BAM, and gzip SAM): BGZF compression level 0-9 (default: 6). Higher = better compression, slower speed. Refused for uncompressed SAM, as for FASTQ, FASTA and NEWICK. - Multithreaded compression: BAM and gzip-SAM output compress in parallel, following DuckDB's thread count (PRAGMA threads). Record order is preserved and the output is byte-identical to single-threaded writing. (The speedup is smaller than for FASTQ/FASTA because per-record marshalling is serialized; only the BGZF compression is parallelized.)

A COPY reads REFERENCE_LENGTHS and SEQUENCE_DATA once, when it runs, so a COPY that writes several files gives every file the same @SQ lines and the same sequences.

SAM Format Examples:

-- Create reference table (recommended for reuse, especially with large reference sets)
CREATE TABLE ref_table AS
  SELECT 'genome1' AS name, 248956422 AS length
  UNION ALL SELECT 'genome2', 242193529;

-- Basic SAM output with header
COPY (SELECT * FROM read_alignments('input.sam'))
TO 'output.sam' (FORMAT SAM, REFERENCE_LENGTHS 'ref_table');

-- Headerless SAM output (no reference lengths needed)
COPY (SELECT * FROM read_alignments('input.sam'))
TO 'output.sam' (FORMAT SAM, INCLUDE_HEADER false);

-- Compressed SAM output with header
COPY (SELECT * FROM read_alignments('input.sam'))
TO 'output.sam.gz' (FORMAT SAM, COMPRESSION gzip, REFERENCE_LENGTHS 'ref_table');

-- Filter and write high-quality alignments (headerless)
COPY (
  SELECT * FROM read_alignments('input.sam')
  WHERE mapq >= 30 AND NOT alignment_is_unmapped(flags)
) TO 'filtered.sam' (FORMAT SAM, INCLUDE_HEADER false);

BAM Format Examples:

-- Basic BAM output (always includes header)
COPY (SELECT * FROM read_alignments('input.sam'))
TO 'output.bam' (FORMAT BAM, REFERENCE_LENGTHS 'ref_table');

-- BAM with maximum compression
COPY (SELECT * FROM read_alignments('input.bam'))
TO 'compressed.bam' (FORMAT BAM, COMPRESSION_LEVEL 9, REFERENCE_LENGTHS 'ref_table');

-- BAM with no compression (fastest)
COPY (SELECT * FROM read_alignments('input.bam'))
TO 'uncompressed.bam' (FORMAT BAM, COMPRESSION_LEVEL 0, REFERENCE_LENGTHS 'ref_table');

-- Convert SAM to BAM
COPY (SELECT * FROM read_alignments('input.sam'))
TO 'output.bam' (FORMAT BAM, REFERENCE_LENGTHS 'ref_table');

-- Convert BAM to SAM
COPY (SELECT * FROM read_alignments('input.bam'))
TO 'output.sam' (FORMAT SAM, REFERENCE_LENGTHS 'ref_table');

-- Filter alignments and write to BAM
COPY (
  SELECT * FROM read_alignments('input.bam')
  WHERE mapq >= 30 AND alignment_is_primary(flags)
) TO 'filtered.bam' (FORMAT BAM, REFERENCE_LENGTHS 'ref_table');

-- Write BAM with sequence data (SEQ and QUAL populated from FASTQ)
CREATE TABLE sequences AS SELECT * FROM read_fastx('reads_R1.fastq', 'reads_R2.fastq');

COPY (SELECT * FROM read_alignments('input.bam'))
TO 'with_seq.bam' (FORMAT BAM, REFERENCE_LENGTHS 'ref_table', SEQUENCE_DATA 'sequences');

Sequence Data¶

The SEQUENCE_DATA parameter allows writing actual SEQ and QUAL fields into SAM/BAM output by looking up original read sequences from a table or view. Without this parameter, SEQ and QUAL are written as *.

This design avoids duplicating immutable sequence data across alignment records. The original FASTQ sequences are stored once and joined at write time.

Required columns in the SEQUENCE_DATA table: - read_id (VARCHAR, BIGINT or UUID): Read identifier, matched as its text (BIGINT in decimal, UUID in the lowercase hyphenated form) against the alignment's read_id values, so a BIGINT or UUID read_id needs no cast - sequence1 (VARCHAR): Forward-strand DNA sequence for read 1 - qual1 (LIST(UTINYINT)): Phred quality scores for read 1 (raw values 0-93, no ASCII offset)

Optional columns (for paired-end): - sequence2 (VARCHAR): Forward-strand DNA sequence for read 2 - qual2 (LIST(UTINYINT)): Phred quality scores for read 2

These columns match the output schema of read_fastx, so the typical workflow is:

-- Load sequences
CREATE TABLE sequences AS SELECT * FROM read_fastx('R1.fastq', 'R2.fastq');

-- Load alignments
CREATE TABLE alignments AS SELECT * FROM read_alignments('aligned.bam');

-- Write with sequences
COPY (SELECT * FROM alignments)
TO 'output.bam' (FORMAT BAM, REFERENCE_LENGTHS 'ref_table', SEQUENCE_DATA 'sequences');

Behavior: - Paired-end reads: Uses SAM flag bit 0x80 (second in pair) to select sequence2/qual2 vs sequence1/qual1 - Reverse strand: When flag bit 0x10 is set, the sequence is reverse-complemented and quality scores are reversed - Hard clipping: CIGAR H operations trim the original sequence accordingly (leading H trims from start, trailing H from end) - CIGAR validation: After hard clipping, sequence length must match the sum of query-consuming CIGAR operations (M, I, S, =, X) - NULL quality: If qual1/qual2 is NULL, sequence is written but quality is marked as missing - Missing reads: An error is thrown if any alignment's read_id is not found in the SEQUENCE_DATA table

Notes: - The SEQUENCE_DATA table is loaded entirely into memory at initialization. For very large datasets, ensure sufficient RAM is available. - Reference lengths must be provided explicitly when writing headers - they cannot be inferred from the data - All optional tags present in the input are preserved in the output - BAM files always require headers (binary format specification)

UBAM¶

Write query results to an unaligned reads BAM (uBAM): reads with SEQ/QUAL and no reference alignment. This is the BAM counterpart of FORMAT FASTQ — it takes the same read_id/sequence1/qual1 columns — and exists for tools that require a BAM (rather than FASTQ) input for unaligned reads, e.g. PacBio's lima, which selects CCS-vs-CLR demultiplexing from the input format.

Every record is written unmapped (FLAG=4), with no @SQ header and no reference table. read_id is written verbatim as the record name (QNAME) — the writer makes no assumption about its contents. Output is always BAM (block-gzip).

Required columns: - read_id (VARCHAR, BIGINT, or UUID): Record name (QNAME), written verbatim. BIGINT/UUID are stringified as for FASTQ. - sequence1 (VARCHAR): Read sequence. - qual1 (UTINYINT[]): Phred quality scores, raw values 0–93 (no ASCII offset) — exactly what read_fastx / read_sequences_sam emit. Must be the same length as sequence1.

Single-end only (no sequence2/qual2). Any additional projected columns are ignored unless referenced by TAGS.

UBAM is written to local files only; a remote target is refused.

Parameters: - READ_GROUP (STRUCT, optional): Emits an @RG header line and a per-record RG:Z tag. An ID field is required; any other fields (PL, DS, SM, LB, PU, CN, …) are passed through. Field names are case-insensitive (upper-cased to the SAM codes). Example: READ_GROUP {ID: 'qiita', PL: 'PACBIO', DS: 'READTYPE=CCS'}. DS:READTYPE=CCS is the field lima keys on to treat the input as CCS. - TAGS (STRUCT, optional): Maps a 2-character BAM aux tag to a projected integer column, written as a signed 32-bit (i) tag per record. A NULL cell omits the tag for that record. Example: TAGS {zm: 'zmw'} writes zm:i:<zmw> (PacBio ZMW hole number). Non-integer columns are rejected at bind. Note: struct keys are lower-cased unless quoted, so an uppercase tag needs a quoted key (e.g. TAGS {'NM': 'edit_distance'}); the RG tag is reserved for READ_GROUP. - COMPRESSION_LEVEL (0–9, optional): BAM compression level (default: HTSlib's level 6).

REFERENCE_LENGTHS is not accepted — a uBAM is headerless by construction. Use FORMAT BAM for aligned records.

Examples:

-- CCS reads from the lake -> a uBAM lima will accept. zmw is a projected
-- integer column carrying the ZMW hole number for the zm tag.
COPY (SELECT read_id, sequence1, qual1, zmw FROM reads)
TO 'out.bam' (
  FORMAT UBAM,
  READ_GROUP {ID: 'qiita', PL: 'PACBIO', DS: 'READTYPE=CCS'},
  TAGS {zm: 'zmw'}
);

-- Minimal uBAM (no @RG, no tags).
COPY (SELECT read_id, sequence1, qual1 FROM read_fastx('input.fastq'))
TO 'reads.bam' (FORMAT UBAM);

Round-trip via read_sequences_sam('out.bam') returns read_id/sequence1/qual1 (with read_id as VARCHAR, as for the FASTQ writer).

BIOM¶

Write query results to BIOM (Biological Observation Matrix) format files. BIOM is an HDF5-based format commonly used for representing OGU/OTU/ASV tables in microbiome analyses.

Required columns: - feature_id (VARCHAR, BIGINT or UUID): Feature/OGU/OTU/ASV identifier - sample_id (VARCHAR, BIGINT or UUID): Sample identifier - value (DOUBLE): Abundance/count value

An id is written as its text, as read_id is elsewhere: BIGINT in decimal, UUID in the lowercase hyphenated form. read_biom returns ids as VARCHAR. A column of another type is refused when the COPY is planned, with an error naming the column. An untyped decimal literal such as 45.0 is DECIMAL, so cast a literal value to DOUBLE.

BIOM is written to local files only; a remote target is refused. PARTITION_BY and PER_THREAD_OUTPUT write .biom files, each a complete table of its own rows: one per thread, and one or more per partition (DuckDB starts another file for a partition when more partitions are open than partitioned_write_max_open_files), so a (feature, sample) pair can be split across files. DuckDB leaves the partition columns out of the rows it writes unless WRITE_PARTITION_COLUMNS true, so partitioning by sample_id or feature_id needs it.

A target file that already exists is refused, and so is one made between PREPARE and EXECUTE; the file is left as it was. The error is Cannot overwrite existing file, or H5Fcreate failed: File exists when DuckDB does not bind the COPY again at EXECUTE. USE_TMP_FILE true, which has DuckDB rename a temporary file over the target, is refused when the COPY runs. With PARTITION_BY or PER_THREAD_OUTPUT and OVERWRITE_OR_IGNORE, a file of the same name already in the directory is replaced, as that option asks. A write that fails partway, on a full disk for example, fails the COPY with an IO Error naming the file, the HDF5 call that failed and the system's reason, such as No space left on device, and DuckDB removes the file the failed COPY created.

Parameters: - COMPRESSION (default: 'gzip'): HDF5's compression of the datasets inside the file, 'gzip' or 'gz' (deflate level 4, h5py's default), or 'none' or 'uncompressed', in any case. Any other value, zstd included, is refused. The .biom file itself is always HDF5, never gzip, whatever its name. - ID (default: 'No Table ID'): Custom table identifier for BIOM metadata - GENERATED_BY (default: 'miint'): Tool/version string for provenance tracking

Behavior: - Automatic deduplication: Duplicate (feature_id, sample_id) pairs are automatically summed - Sparse optimization: Zero values are automatically removed from output - Ordering: Feature and sample IDs appear in order of first occurrence in the input data - NULL handling: NULL values in any required column cause an error

Examples:

-- Basic BIOM output from Woltka results
COPY (
    SELECT * FROM woltka_ogu('my_alignments', 'read_id', sample_id := 'sample_id')
) TO 'ogu_table.biom' (FORMAT BIOM);

-- With HDF5 gzip compression (recommended)
COPY (
    SELECT * FROM woltka_ogu('my_alignments', 'read_id', sample_id := 'sample_id')
) TO 'ogu_table.biom' (FORMAT BIOM, COMPRESSION 'gzip');

-- With custom metadata
COPY (
    SELECT * FROM woltka_ogu('my_alignments', 'read_id', sample_id := 'sample_id')
) TO 'ogu_table.biom' (FORMAT BIOM,
                       COMPRESSION 'gzip',
                       ID 'MyStudy_16S',
                       GENERATED_BY 'DuckDB-MIINT v1.0 + Woltka algorithm');

-- Export filtered high-quality alignments
CREATE VIEW high_qual AS
    SELECT *, 'sample1' AS sample_id
    FROM read_alignments('sample1.bam')
    WHERE mapq >= 30 AND alignment_is_primary(flags);

COPY (
    SELECT * FROM woltka_ogu('high_qual', 'read_id', sample_id := 'sample_id')
) TO 'high_qual.biom' (FORMAT BIOM, COMPRESSION 'gzip');

-- Convert any compatible table to BIOM format
CREATE TABLE feature_counts AS
SELECT * FROM (VALUES
    ('OGU_001', 'Sample_A', 45.0::DOUBLE),
    ('OGU_001', 'Sample_B', 32.0::DOUBLE),
    ('OGU_002', 'Sample_A', 18.0::DOUBLE),
    ('OGU_002', 'Sample_B', 27.0::DOUBLE)
) AS t(feature_id, sample_id, value);

COPY (SELECT * FROM feature_counts)
TO 'counts.biom' (FORMAT BIOM, COMPRESSION 'gzip');

-- Process multiple BAM files and export
CREATE VIEW all_samples AS
    SELECT *, 'sample1' AS sample_id FROM read_alignments('sample1.bam')
    UNION ALL
    SELECT *, 'sample2' AS sample_id FROM read_alignments('sample2.bam')
    UNION ALL
    SELECT *, 'sample3' AS sample_id FROM read_alignments('sample3.bam');

COPY (
    SELECT * FROM woltka_ogu('all_samples', 'read_id', sample_id := 'sample_id')
) TO 'all_samples.biom' (FORMAT BIOM, COMPRESSION 'gzip');

-- Export single-sample results (add sample_id column)
COPY (
    SELECT feature_id, 'MySample' AS sample_id, value
    FROM woltka_ogu('my_alignments', 'read_id')
) TO 'single_sample.biom' (FORMAT BIOM);

Deduplication behavior:

-- Duplicates are automatically summed
CREATE TABLE with_dups AS
SELECT * FROM (VALUES
    ('F1', 'S1', 10.0::DOUBLE),
    ('F1', 'S1', 5.0::DOUBLE),   -- Same feature + sample
    ('F2', 'S1', 8.0::DOUBLE)
) AS t(feature_id, sample_id, value);

COPY (SELECT * FROM with_dups)
TO 'merged.biom' (FORMAT BIOM);

-- Result: F1,S1,15.0 and F2,S1,8.0 (duplicates merged)

-- Zeros are removed
CREATE TABLE with_zeros AS
SELECT * FROM (VALUES
    ('F1', 'S1', 10.0::DOUBLE),
    ('F1', 'S2', 0.0::DOUBLE),   -- Zero value
    ('F2', 'S1', 8.0::DOUBLE)
) AS t(feature_id, sample_id, value);

COPY (SELECT * FROM with_zeros)
TO 'sparse.biom' (FORMAT BIOM);

-- Result: Only F1,S1,10.0 and F2,S1,8.0 (zero removed)

Compatibility: - Output files are compatible with: - QIIME2 (qiime tools import --type FeatureTable[Frequency]) - phyloseq (import_biom) - biom-format Python package - The read_biom() function in this extension

Notes: - BIOM format uses HDF5, which provides efficient storage for sparse matrices - The format automatically handles very large feature/sample counts - Feature and sample metadata columns are not currently supported (data only) - The output follows BIOM format specification v2.1

Newick¶

Write query results to Newick phylogenetic tree format. Reconstructs a tree from tabular node data and serializes to standard Newick format.

Required columns: - node_index (BIGINT): Unique identifier for each node - parent_index (BIGINT, nullable): Parent node's node_index (NULL for root)

Optional columns: - name (VARCHAR, BIGINT or UUID): Node label, written as its text - branch_length (DOUBLE): Branch length - edge_id (BIGINT): Edge identifier for jplace format

Each column takes only the types listed. A column of another type, such as the INTEGER and DECIMAL that untyped literals like 1 and 0.1 are, is refused when the COPY is planned; cast it in the query (node_index::BIGINT). name takes the types a read_id does. An untyped NULL column is cast to the column's type, so NULL AS name writes no names.

Parameters: - EDGE_IDS (BOOLEAN, default: auto): Include edge identifiers {n} in output - Default: true if edge_id column exists, false otherwise - Set to false to explicitly exclude edge IDs - COMPRESSION (default: auto): Enable gzip compression, auto-detected from the .gz extension; values as for the other text formats, above - COMPRESSION_LEVEL (0-9, default: 6): BGZF compression level for local .gz output, as for FASTQ and FASTA. Refused for uncompressed output and for remote targets, whose gzip writer takes no level. - PLACEMENTS (VARCHAR): Name of a table containing phylogenetic placement data to insert - Inserts fragment sequences into the tree at their placement locations - Requires tree to have edge_id column with valid edge identifiers - Required table columns: fragment_id (VARCHAR, BIGINT or UUID, as a read_id; the placed tip is named with its text), edge_id (BIGINT, INTEGER, UBIGINT or UINTEGER), like_weight_ratio, distal_length and pendant_length (each DOUBLE or FLOAT). A column of another type is refused, when the COPY is planned and again when the placements are read - Duplicate fragment_ids are deduplicated as in tree_resolve_placement, which does not always keep the placement with the highest like_weight_ratio - Multiple placements on same edge are handled correctly

Behavior: - Reconstructs tree structure from parent-child relationships - Validates tree structure: - Exactly one root (node with NULL parent_index) - All parent references valid - No cycles - All nodes connected - Serializes to standard Newick format with semicolon terminator - Extra columns (e.g., is_tip, filepath) are ignored - Writes one tree to one file: PER_THREAD_OUTPUT, PARTITION_BY and ORDER_BY (which DuckDB writes as a directory of files) are refused, as each file would hold only part of the tree. The error comes when the COPY runs, not when it is planned (EXPLAIN and PREPARE do not raise it), and a partitioned or ordered COPY of no rows succeeds and writes no file

Examples:

-- Basic roundtrip: read and write tree
COPY (SELECT * FROM read_newick('input.nwk'))
TO 'output.nwk' (FORMAT NEWICK);

-- Write compressed tree
COPY (SELECT * FROM read_newick('input.nwk'))
TO 'output.nwk.gz' (FORMAT NEWICK);

-- Explicit compression parameter
COPY (SELECT * FROM read_newick('input.nwk'))
TO 'output.nwk' (FORMAT NEWICK, COMPRESSION 'gzip');

-- Include edge IDs (for jplace compatibility)
COPY (SELECT * FROM read_newick('reference.nwk'))
TO 'with_edges.nwk' (FORMAT NEWICK, EDGE_IDS true);

-- Exclude edge IDs even if present in input
COPY (SELECT * FROM read_newick('reference.nwk'))
TO 'no_edges.nwk' (FORMAT NEWICK, EDGE_IDS false);

-- Modify tree before writing (e.g., scale branch lengths)
COPY (
    SELECT node_index, name, branch_length * 2.0 AS branch_length,
           edge_id, parent_index
    FROM read_newick('input.nwk')
) TO 'scaled.nwk' (FORMAT NEWICK);

-- Create tree from scratch (the casts give each column its type)
CREATE TABLE my_tree AS
SELECT * FROM (VALUES
    (0::BIGINT, NULL::BIGINT, '', NULL::DOUBLE),  -- root
    (1, 0, 'A', 0.1),                              -- tip A
    (2, 0, 'B', 0.2)                               -- tip B
) AS t(node_index, parent_index, name, branch_length);

COPY (SELECT * FROM my_tree)
TO 'new_tree.nwk' (FORMAT NEWICK);
-- Produces: (A:0.1,B:0.2);

-- Filter to subtree (advanced - requires careful node_index management)
-- Note: Ensure parent references remain valid after filtering

-- Insert phylogenetic placements into a reference tree
-- First, create a placement table (e.g., from read_jplace output)
CREATE TABLE placements AS
SELECT * FROM (VALUES
    ('fragment_1', 0::BIGINT, 0.95::DOUBLE, 0.05::DOUBLE, 0.001::DOUBLE),
    ('fragment_2', 1::BIGINT, 0.80::DOUBLE, 0.10::DOUBLE, 0.002::DOUBLE)
) AS t(fragment_id, edge_id, like_weight_ratio, distal_length, pendant_length);

-- Write tree with placements inserted
COPY (SELECT * FROM read_newick('reference.nwk'))
TO 'with_placements.nwk' (FORMAT NEWICK, PLACEMENTS 'placements');

-- Combine with read_jplace for seamless jplace to newick workflow
CREATE TABLE jplace_placements AS
SELECT fragment_id, edge_id, like_weight_ratio, distal_length, pendant_length
FROM read_jplace('placements.jplace');

COPY (SELECT * FROM read_newick('reference.nwk'))
TO 'resolved_tree.nwk' (FORMAT NEWICK, PLACEMENTS 'jplace_placements');

Validation errors: - "No data to write" - Empty result set - "no root" - All nodes have a parent_index - "multiple roots" - More than one node has NULL parent_index - "invalid parent reference" - parent_index references non-existent node - "cycle detected" - Circular parent-child relationship - "disconnected nodes" - Nodes not reachable from root

Placement-specific errors (when using PLACEMENTS): - "Placement table does not exist" - The specified table doesn't exist - "Placement table is empty" - The table has no rows - "missing required column" - Missing one of: fragment_id, edge_id, like_weight_ratio, distal_length, pendant_length - "Tree has no edge_id values" - Tree data lacks edge identifiers - "Unknown edge_id" - Placement references an edge_id not present in tree - "distal_length is negative" - distal_length must be >= 0 - "pendant_length is negative" - pendant_length must be >= 0 - "distal_length exceeds edge length" - distal_length is larger than the edge's branch_length

Roundtrip guarantee: Trees written with COPY FORMAT NEWICK can be read back with read_newick and will produce equivalent structure:

-- Write tree
COPY (SELECT * FROM read_newick('original.nwk'))
TO 'copy.nwk' (FORMAT NEWICK);

-- Read back - should have same structure
SELECT * FROM read_newick('copy.nwk');

Newick Format Output: - Node names are included without quotes (unless containing special characters) - Branch lengths are included after colon (:0.123) - Edge IDs are included in braces when EDGE_IDS=true ({0}) - Tree is terminated with semicolon (;)