Skip to main content

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​

source.yaml
kind: source
name: files_demo
auth:
provider: files
roots: [./data] # the only folders the connector may read
KeyWhat it isRequired / default
rootsThe local folders the connector may read and, in jobs, write. No URLs: use s3, gcs or azure_blob for object storageRequired
  • A relative root is relative to the working directory (streamwright run, Spark jobs) or to the job's folder (DuckDB jobs).
  • streamwright run needs each root to exist. DuckDB and Spark job outputs create the folders they write to.
  • A relative path is relative to the first root.

Read data​

In a source stream, a request uses service: object, method: read:

streams/orders.yaml
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.

ArgumentWhat it doesEngines
pathA file, a glob or a list of them. Absolute (or a URL) inside a root, or relative to the first root. Requiredall
formatcsv, tsv, json, jsonl, parquet, or auto (the default: from the file extension)all
optionsThe format's reader options (see below)all
on_missingA path that matches nothing: skip it with a warning (default) or errorsource, Spark
match, recursiveA regex that must fully match each file's path below the folder path; recursive: true also searches sub-folderssource
schema_modeFiles whose columns differ: union them by_name (default) or reject them (strict)DuckDB, Spark
columns, vocabulary, on_unmapped_column, column_groups, path_pattern, row_indexMap different headers to one schema, take columns from the path, number rowsDuckDB, Spark
on_mismatch, row_errors, inject_path_varsSkip unknown files, keep bad rows with a _row_errors column, add path columnsSpark
  • 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.
  • auto knows .csv, .tsv/.tab, .json, .jsonl/.ndjson and .parquet/.pq, also followed by .gz or .zst.
  • Different file layouts: see Multi-schema file ingestion for columns, path_pattern, column_groups, row_index and row_errors.

Format options​

options uses one vocabulary on every file connector and engine.

FormatOptionsEngines
csv, tsvheader, delimiter, quote, escape, compression, nullstr, dateformat, timestampformatall (nullstr is text on Spark)
csv, tsvcolumns (name: DuckDB type), null_padding, ignore_errors, skip, all_varchar, filenamesource, DuckDB
csv, tsvencoding, multiLineSpark
jsonformat (auto, array, newline_delimited), compression, ignore_errors, filenamesource, DuckDB
jsonlcompression, ignore_errors, filenamesource, DuckDB
parquetfilenamesource, 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​

ArgumentWhat it doesEngines
pathThe file, or the folder when file_name names the files. RequiredDuckDB, Spark
formatcsv, tsv, json, jsonl or parquet (json and jsonl both write one object per line). RequiredDuckDB, Spark
modeerror (never write over a file), overwrite, or append (Spark only). A DuckDB output must set itDuckDB, Spark
optionsThe format's writer optionsDuckDB, Spark
split_by, layout, file_nameOne file per distinct value combination, in folders named by layout and files named by file_nameDuckDB, Spark
partition_byHive-style folders (not with split_by, layout or file_name)DuckDB
single_file, max_splits, on_existsOne file only; the most files a split may write (default 500); a split file that exists: error (default), skip or overwriteSpark

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 with path ... 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​