Files
The files connector reads and writes local files: CSV, TSV, JSON, JSONL and Parquet, in local folders and POSIX
mounts. It needs no credentials and no network, so it is the easiest way to try StreamWright, and a good fit for tests
and demos.
This page is also the home of the file-reading and file-writing options. The s3, gcs and azure_blob
connectors take the same ones.
Install
sw connectors -i files
A DuckDB job's provider: files is this connector too, so install it to read or write local files from a job.
Connect
kind: source
name: files_demo
auth:
provider: files
roots: [./data] # the only folders the connector may read
| Key | What it is | Required / default |
|---|---|---|
roots | The local folders the connector may read and, in jobs, write. No URLs: use s3, gcs or azure_blob for object storage | Required |
- A relative root is relative to the working directory (
streamwright run, Spark jobs) or to the job's folder (DuckDB jobs). streamwright runneeds each root to exist. DuckDB and Spark job outputs create the folders they write to.- A relative
pathis relative to the first root.
Read data
In a source stream, a request uses service: object, method: read:
requests:
- name: raw_orders
operation:
service: object
method: read
arguments:
path: orders/*.jsonl # a glob, relative to the first root
format: jsonl
transform:
mode: page
steps:
- name: orders
select: |
SELECT
(record->>'id')::BIGINT AS order_id,
(record->>'total')::DECIMAL(18,2) AS total
FROM raw_orders
export:
orders:
step: orders
primary_key: [order_id]
streamwright run files_demo --allow-connector files --output jsonl:out
Each row of a file is one record, in pages of at most 1,000, so memory stays bounded however large the file is. CSV
and TSV values are text (empty cells are null) unless columns types them; Parquet decimals stay exact.
In a DuckDB job, a read step names the format and the path:
connections:
local:
provider: files
roots: [./data]
steps:
- name: orders
connection: local
read: csv # or tsv, json, jsonl, parquet, auto
path: orders/*.csv
options: {header: true}
In a Spark job, a step uses the same operation: as a source stream:
steps:
- name: orders
connection: local
operation:
service: object
method: read
arguments:
path: orders/
format: csv
options: {header: true}
File-reading options
These arguments work on every file connector (files, s3, gcs, azure_blob). An argument an engine does not
take is refused by name when you validate.
| Argument | What it does | Engines |
|---|---|---|
path | A file, a glob or a list of them. Absolute (or a URL) inside a root, or relative to the first root. Required | all |
format | csv, tsv, json, jsonl, parquet, or auto (the default: from the file extension) | all |
options | The format's reader options (see below) | all |
on_missing | A path that matches nothing: skip it with a warning (default) or error | source, Spark |
match, recursive | A regex that must fully match each file's path below the folder path; recursive: true also searches sub-folders | source |
schema_mode | Files whose columns differ: union them by_name (default) or reject them (strict) | DuckDB, Spark |
columns, vocabulary, on_unmapped_column, column_groups, path_pattern, row_index | Map different headers to one schema, take columns from the path, number rows | DuckDB, Spark |
on_mismatch, row_errors, inject_path_vars | Skip unknown files, keep bad rows with a _row_errors column, add path columns | Spark |
- Globs:
*,[ab]and**(any folders).?is not a glob character: a?,%,#or backslash in a path is refused. - Order: files are read in name order.
autoknows.csv,.tsv/.tab,.json,.jsonl/.ndjsonand.parquet/.pq, also followed by.gzor.zst.- Different file layouts: see Multi-schema file ingestion for
columns,path_pattern,column_groups,row_indexandrow_errors.
Format options
options uses one vocabulary on every file connector and engine.
| Format | Options | Engines |
|---|---|---|
| csv, tsv | header, delimiter, quote, escape, compression, nullstr, dateformat, timestampformat | all (nullstr is text on Spark) |
| csv, tsv | columns (name: DuckDB type), null_padding, ignore_errors, skip, all_varchar, filename | source, DuckDB |
| csv, tsv | encoding, multiLine | Spark |
| json | format (auto, array, newline_delimited), compression, ignore_errors, filename | source, DuckDB |
| jsonl | compression, ignore_errors, filename | source, DuckDB |
| parquet | filename | source, DuckDB |
filename: true adds each file's path, relative to its root, to its records.
Write data
DuckDB and Spark job outputs write a step's rows as CSV, TSV, JSON, JSONL or Parquet files inside the roots.
streamwright run does not write through this connector (use --output instead).
DuckDB job output:
outputs:
- name: save_orders
connection: local
step: orders
path: exports/orders.parquet
format: parquet
mode: overwrite
Spark job output:
outputs:
- name: save_orders
connection: local
operation:
service: object
method: write
arguments:
path: exports/orders
format: parquet
mode: overwrite
sql: SELECT * FROM orders
File-writing options
| Argument | What it does | Engines |
|---|---|---|
path | The file, or the folder when file_name names the files. Required | DuckDB, Spark |
format | csv, tsv, json, jsonl or parquet (json and jsonl both write one object per line). Required | DuckDB, Spark |
mode | error (never write over a file), overwrite, or append (Spark only). A DuckDB output must set it | DuckDB, Spark |
options | The format's writer options | DuckDB, Spark |
split_by, layout, file_name | One file per distinct value combination, in folders named by layout and files named by file_name | DuckDB, Spark |
partition_by | Hive-style folders (not with split_by, layout or file_name) | DuckDB |
single_file, max_splits, on_exists | One file only; the most files a split may write (default 500); a split file that exists: error (default), skip or overwrite | Spark |
File-name placeholders such as {today} and {col} are listed in DuckDB jobs: file names.
Good to know
- Nothing outside the roots. Every file is checked before any is read. A path with
.., a URL, or a symbolic link that leads outside the roots fails withpath ... is outside the roots. A DuckDB job checks every file a glob matches the same way, hidden files included. - Sandboxed reads. On
streamwright run, the connector's DuckDB connection can only read inside the roots, with external access off and its settings locked. - Vendor arguments. Reader and writer options pass through to DuckDB and Spark, for example
options: { compression: gzip }.
See also
- Object storage with the same options: Amazon S3, Google Cloud Storage, Azure Blob Storage.
- DuckDB jobs: outputs.
- Starter template:
examples/sources/readers/files_demo(runs offline).