Skip to main content

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.

source.yaml
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:

source.yaml (Amazon S3)
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
source.yaml (Azure Blob Storage)
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
source.yaml (Google Cloud Storage)
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
KeyWhat it isRequired / default
rootsLocal folders or URL prefixes the tables may be inRequired
aws_access_key_id, aws_secret_access_key, aws_session_tokenS3 credentials. SecretsOptional
aws_regionS3: the buckets' regionOptional
s3_endpointS3: an S3-compatible endpoint as host[:port]Optional
s3_url_styleS3: vhost or path (most S3-compatible stores use path)Optional
s3_use_sslS3: false for plain HTTP to the endpointOptional
gcs_hmac_key_id, gcs_hmac_secretGCS: an HMAC key, for reads. Secrets (set both)Optional
gcs_key_fileGCS: a service account key file, for DuckDB job writesOptional
azure_connection_stringAzure: a connection string. SecretOptional
azure_account_name, azure_account_keyAzure: the storage account and its key (the key is a secret)Optional
storageSpark: the object storage connection (s3, gcs, azure_blob or files) whose credentials Spark usesOptional
schema, schema_location, schema_commentSpark: defaults for the catalog schema of the tables Spark writes registerOptional

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:

EngineGCS credential
streamwright run and DuckDB job readsgcs_hmac_key_id + gcs_hmac_secret (an HMAC key)
DuckDB job writesgcs_key_file, else the environment's Google credentials. HMAC keys do not work here
Spark jobsThe 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:

MethodArgumentsWhat it does
readpath, columns, where, limitReads a table: some or all columns, the rows that match every where condition, at most limit rows
querypath, query, paramsstreamwright run only. Runs one read-only DuckDB SELECT over the table at path, named delta_table
streams/events.yaml
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:

streams/daily_aggregates.yaml
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
Enginetable.read arguments
sourcepath, columns, where, limit
DuckDB jobpath
Spark jobpath, 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: {...}}.

KeyWhat it doesDefault
modeappend; overwrite (needs replace_where or whole_table: true); or mergeappend
replace_whereoverwrite: the rows to replace, for example d = '{{ batch.ts }}'
whole_tableoverwrite: replace the whole table
conditionmerge: the ON condition, using target. and source.
when_matched, when_not_matched, when_not_matched_by_sourcemerge: clauses of {action, set, predicate}
target_predicatemerge: prunes target files, derived from the source rows
partition_byThe partition columns of a new table
schema_evolutionstrict refuses new columns; add_columns adds themstrict
table_name, schema, schema_location, schema_comment, table_properties, comment, optionsSpark 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.read or table.write argument the connector does not declare is passed to Delta as a reader or writer option, for example readChangeFeed: true or startingVersion: 3.

See also​