Skip to main content

Databricks

Databricks reads the gzip NDJSON objects that Amazon S3 and Google Cloud Storage destinations write, and S3-compatible destinations on Cloudflare R2. Query them in place with read_files, or ingest them into a Delta table with Auto Loader and query that. The Delta table is faster for dashboards and repeated queries; read_files needs no pipeline. For the record layout, see Delivery format.

Databricks runs Apache Spark, so the schema and the variant_get queries from the Apache Spark page carry over. External locations, read_files, Auto Loader, streaming tables and the column:field path syntax are Databricks-only.

note

This page follows the Databricks documentation. We have not run these statements on Databricks ourselves; if one fails for you, tell us and we will fix the page. The schema and the variant_get queries ran on Apache Spark 4.0 against our sample data, as described on the Apache Spark page.

Connect to bucket objects​

Unity Catalog reads a bucket through an external location: a path plus a storage credential that can read it. On AWS the credential is an IAM role; on Google Cloud it is a service account. A metastore admin creates the location once:

CREATE EXTERNAL LOCATION s2b_raw
URL 's3://<bucket>/<prefix>/s2b/'
WITH (STORAGE CREDENTIAL <credential_name>);

GRANT READ FILES ON EXTERNAL LOCATION s2b_raw TO `<group>`;

The credential needs read access only, for example s3:GetObject and s3:ListBucket on the prefix.

The rest of this page uses s3:// paths. Swap in your gs:// or r2:// path.

Query objects in place​

read_files reads the objects from Databricks SQL or a notebook, on Databricks Runtime 13.3 LTS or later. VARIANT columns need Databricks Runtime 15.3 or later:

CREATE OR REPLACE TEMPORARY VIEW records AS
SELECT * FROM read_files(
's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format => 'json',
schema => 'v INT, delivery_batch_id STRING, batch_id STRING, received_at TIMESTAMP,
signal STRING, resource VARIANT, scope VARIANT, data VARIANT');
  • The .gz extension tells Spark to decompress each object.
  • The schema keeps types stable. Without it, read_files infers a schema from the data and turns data into a struct of whichever event fields it saw.
  • stream=* keeps the destination's test objects under s2b/_verify/ out of the view.
  • Narrow the path to read less: stream=<stream_id>/date=2026-11-19/hour=*/*.ndjson.gz reads one stream's day.

read_files lists and reads the objects on every query, so it always sees the latest deliveries, and a query over many days is slow. For anything you run repeatedly, ingest into a Delta table.

Load into a Delta table​

Auto Loader keeps a checkpoint of the objects it has ingested and picks up new ones on each run. Send2Bucket never rewrites an object, so each object is ingested once. In a notebook or job:

(spark.readStream
.format("cloudFiles")
.option("cloudFiles.format", "json")
.option("pathGlobFilter", "*.ndjson.gz")
.option("cloudFiles.partitionColumns", "stream,date,hour")
.schema("v INT, delivery_batch_id STRING, batch_id STRING, received_at TIMESTAMP, "
"signal STRING, resource VARIANT, scope VARIANT, data VARIANT, "
"stream STRING, date DATE, hour INT")
.load("s3://<bucket>/<prefix>/s2b/")
.writeStream
.option("checkpointLocation", "<checkpoint_path>")
.trigger(availableNow=True)
.toTable("<catalog>.<schema>.s2b_records"))
  • pathGlobFilter reads only delivered objects and skips the .json test objects under s2b/_verify/.
  • Auto Loader takes stream, date and hour from the key=value folders. With an explicit schema, the schema must list them.
  • availableNow ingests everything new and stops. Schedule the notebook as a job, hourly for example, or drop the trigger to run continuously.

In Lakeflow Declarative Pipelines or Databricks SQL, a streaming table does the same with read_files:

CREATE OR REFRESH STREAMING TABLE s2b_records AS
SELECT * FROM STREAM read_files(
's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format => 'json',
schema => 'v INT, delivery_batch_id STRING, batch_id STRING, received_at TIMESTAMP,
signal STRING, resource VARIANT, scope VARIANT, data VARIANT');

Example queries​

These run against the records view or the s2b_records table. They use the Databricks path syntax column:field::type on VARIANT columns. On Apache Spark, use the variant_get versions on the Apache Spark page; Databricks runs those too.

Records per signal:

SELECT signal, count(*) AS records
FROM records
GROUP BY ALL
ORDER BY records DESC;

JSON events by name, with revenue from order.paid:

SELECT data:event::string AS event,
count(*) AS events,
sum(data:cents::int) / 100 AS revenue_usd
FROM records
WHERE signal = 'event'
GROUP BY ALL
ORDER BY events DESC;

Log records by service and severity. service.name is one entry in the resource's attribute list. This query is the one we ran on Apache Spark 4.0:

SELECT variant_get(
filter(variant_get(resource, '$.attributes', 'array<variant>'),
a -> variant_get(a, '$.key', 'string') = 'service.name')[0],
'$.value.stringValue', 'string') AS service,
data:severityText::string AS severity,
count(*) AS logs
FROM records
WHERE signal = 'log'
GROUP BY ALL;

The five slowest spans. The timestamps are nanosecond strings; cast them to bigint before subtracting:

SELECT data:name::string AS span,
data:traceId::string AS trace_id,
(data:endTimeUnixNano::bigint - data:startTimeUnixNano::bigint) / 1e6 AS duration_ms
FROM records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

Notes​

  • Runtime versions. read_files needs Databricks Runtime 13.3 LTS or later. Ingesting JSON as VARIANT needs 15.3 or later, and Databricks documents the VARIANT type from 15.4. On older runtimes, declare resource, scope and data as STRING and use the Spark 3.5 queries.
  • Iceberg destinations. Unity Catalog reads Iceberg tables from other catalogs through catalog federation, which covers AWS Glue, Hive metastores, Snowflake and a few others, read-only, on Databricks Runtime 16.4 LTS or later. It does not document federation with a generic Iceberg REST catalog, so for Databricks use an Amazon S3 or Google Cloud Storage destination and ingest with Auto Loader. See What is Apache Iceberg in Databricks?.
  • DuckLake destinations. Databricks has no DuckLake reader. Use DuckDB.
  • Costs. read_files and Auto Loader in directory listing mode list the prefix on each run. For a bucket with many streams and months of objects, point the path at one stream, or see the Auto Loader documentation for file notification mode.
  • Databricks documentation used: read_files, Auto Loader options, Ingest data as VARIANT, Query VARIANT data, CREATE EXTERNAL LOCATION.
  • For the same questions in other engines, see the overview.