Delta Lake
The deltalake connector reads Delta Lake tables in local folders, Amazon S3 (and S3-compatible stores),
Google Cloud Storage or Azure Blob Storage, and appends, overwrites or merges them from DuckDB and Spark job
outputs. You need no metastore and no Spark cluster to read: streamwright run and DuckDB jobs use DuckDB's Delta
extension.
Install
sw connectors -i deltalake
DuckDB job writes need the duckdb extra (pip install "streamwright-deltalake[duckdb]"), Spark jobs the spark
extra (pip install "streamwright-deltalake[spark]").
Connect
roots is required. Every engine reads and writes only inside the roots. A table path is a URL or absolute path
inside a root, or a path relative to the first root.
kind: source
name: deltalake_demo
spec:
config:
data_dir: { type: string, default: "/data/delta" }
auth:
provider: deltalake
roots: ["{{ config.data_dir }}"]
For object storage, add the keys of your store:
kind: source
name: deltalake_demo
spec:
secrets:
aws_access_key_id: { type: string }
aws_secret_access_key: { type: string }
auth:
provider: deltalake
roots: ["s3://example-lakehouse/delta/"]
aws_access_key_id: "{{ secrets.aws_access_key_id }}"
aws_secret_access_key: "{{ secrets.aws_secret_access_key }}"
aws_region: us-east-1
# S3-compatible stores: s3_endpoint: minio:9000, s3_url_style: path, s3_use_ssl: false
kind: source
name: deltalake_demo
spec:
secrets:
azure_connection_string: { type: string }
auth:
provider: deltalake
roots: ["abfss://lake@account.dfs.core.windows.net/delta/"]
azure_connection_string: "{{ secrets.azure_connection_string }}" # or azure_account_name + azure_account_key
kind: source
name: deltalake_demo
spec:
secrets:
gcs_hmac_key_id: { type: string }
gcs_hmac_secret: { type: string }
auth:
provider: deltalake
roots: ["gs://example-lakehouse/delta/"]
gcs_hmac_key_id: "{{ secrets.gcs_hmac_key_id }}" # reads: an HMAC key
gcs_hmac_secret: "{{ secrets.gcs_hmac_secret }}"
# gcs_key_file: /keys/lake-writer.json # DuckDB job writes: a service account key file
| Key | What it is | Required / default |
|---|---|---|
roots | Local folders or URL prefixes the tables may be in | Required |
aws_access_key_id, aws_secret_access_key, aws_session_token | S3 credentials. Secrets | Optional |
aws_region | S3: the buckets' region | Optional |
s3_endpoint | S3: an S3-compatible endpoint as host[:port] | Optional |
s3_url_style | S3: vhost or path (most S3-compatible stores use path) | Optional |
s3_use_ssl | S3: false for plain HTTP to the endpoint | Optional |
gcs_hmac_key_id, gcs_hmac_secret | GCS: an HMAC key, for reads. Secrets (set both) | Optional |
gcs_key_file | GCS: a service account key file, for DuckDB job writes | Optional |
azure_connection_string | Azure: a connection string. Secret | Optional |
azure_account_name, azure_account_key | Azure: the storage account and its key (the key is a secret) | Optional |
storage | Spark: the object storage connection (s3, gcs, azure_blob or files) whose credentials Spark uses | Optional |
schema, schema_location, schema_comment | Spark: defaults for the catalog schema of the tables Spark writes register | Optional |
The account key inside an Azure connection string is redacted (***) in logs and errors too.
Google Cloud Storage credentials
Each engine reaches GCS with a different credential:
| Engine | GCS credential |
|---|---|
streamwright run and DuckDB job reads | gcs_hmac_key_id + gcs_hmac_secret (an HMAC key) |
| DuckDB job writes | gcs_key_file, else the environment's Google credentials. HMAC keys do not work here |
| Spark jobs | The gcs connection named by storage (its HMAC key_id and secret). This connection's own GCS keys are not used |
A connection with gcs_key_file but no HMAC key cannot be used for reads on streamwright run or in DuckDB jobs.
Read data
A request uses service: table:
| Method | Arguments | What it does |
|---|---|---|
read | path, columns, where, limit | Reads a table: some or all columns, the rows that match every where condition, at most limit rows |
query | path, query, params | streamwright run only. Runs one read-only DuckDB SELECT over the table at path, named delta_table |
requests:
- name: lake_events
operation:
service: table
method: read
arguments:
path: "events_table" # relative to the first root
columns: [event_id, user_id, event_time, amount]
transform:
mode: page
steps:
- name: events
select: |
SELECT record->>'event_id' AS event_id,
record->>'user_id' AS user_id,
(record->>'amount')::DOUBLE AS amount,
record->>'event_time' AS event_time
FROM lake_events
export:
events:
step: events
primary_key: [event_id]
A query runs SQL over the table before the records come back. $name placeholders take their values from params
and are never pasted into the query:
requests:
- name: daily_counts
operation:
service: table
method: query
arguments:
path: "events_table"
query: |
SELECT CAST(event_time AS DATE) AS event_date,
COUNT(*) AS total_events,
SUM(amount) AS total_amount
FROM delta_table
GROUP BY 1
transform:
mode: page
steps:
- name: daily
select: |
SELECT (record->>'event_date')::DATE AS event_date,
(record->>'total_events')::BIGINT AS total_events,
(record->>'total_amount')::DOUBLE AS total_amount
FROM daily_counts
export:
daily_aggregates:
step: daily
primary_key: [event_date]
In jobs, a DuckDB job reads a table with a read: delta step, and a Spark job with the same operation: as a
source stream (method: read). Jobs do not take query: read the table and do the SQL in a step's sql.
steps:
- name: orders # DuckDB job
connection: lake
read: delta
path: orders_delta
| Engine | table.read arguments |
|---|---|
| source | path, columns, where, limit |
| DuckDB job | path |
| Spark job | path, columns, where, limit, version (time travel), options |
Write data
DuckDB and Spark job outputs write a step's rows in one Delta transaction. A DuckDB job writes with flat keys:
outputs:
- name: orders_delta_merge
connection: lake
step: enriched_orders
path: orders_delta # relative to the first root
mode: merge
condition: "target.order_id = source.order_id"
target_predicate: "target.order_date IN ({{ enriched_orders.order_date }})"
partition_by: [order_date]
schema_evolution: add_columns
when_matched:
- action: update
set:
amount: source.amount
status: source.status
when_not_matched:
- action: insert
A Spark job puts the same keys under operation: {service: table, method: write, arguments: {...}}.
| Key | What it does | Default |
|---|---|---|
mode | append; overwrite (needs replace_where or whole_table: true); or merge | append |
replace_where | overwrite: the rows to replace, for example d = '{{ batch.ts }}' | |
whole_table | overwrite: replace the whole table | |
condition | merge: the ON condition, using target. and source. | |
when_matched, when_not_matched, when_not_matched_by_source | merge: clauses of {action, set, predicate} | |
target_predicate | merge: prunes target files, derived from the source rows | |
partition_by | The partition columns of a new table | |
schema_evolution | strict refuses new columns; add_columns adds them | strict |
table_name, schema, schema_location, schema_comment, table_properties, comment, options | Spark only: register the table in the catalog, and Delta writer options |
Every write carries an application transaction (the job, output and batch), so replaying a batch never writes twice.
Good to know
- Sandboxed reads. On
streamwright run, the connector's DuckDB connection reads only inside the roots, with external access off and its settings locked. Every path is checked against the roots first. - Spark. A Spark job takes credentials from the connection named by
storage, and ignores this connection's own storage keys. The table version a batch reads is pinned, so a replay reads the same data. - Vendor arguments (Spark). A
table.readortable.writeargument the connector does not declare is passed to Delta as a reader or writer option, for examplereadChangeFeed: trueorstartingVersion: 3.
See also
- Complete DuckDB job:
examples/deltalake/jobs/orders/merge.yaml. - Starter template:
examples/sources/readers/deltalake_demo.