Skip to main content

Microsoft Fabric

Fabric reaches Amazon S3, Google Cloud Storage and S3-compatible buckets through OneLake shortcuts, which show a bucket prefix as a read-only folder in a lakehouse without copying it. A Spark notebook reads the gzip NDJSON objects from that folder and appends them to a Delta table; the lakehouse's SQL analytics endpoint then answers T-SQL with JSON_VALUE and OPENJSON. The record layout is in Delivery format.

note

The shortcut, notebook scheduling, SQL analytics endpoint and Iceberg shortcut steps follow Microsoft's Fabric documentation; we have not run them in Fabric. We ran the notebook code with PySpark 3.5.3 and Delta Lake 3.2.1 on a local copy of the sample objects, and the T-SQL queries on SQL Server 2022, which has the same JSON functions as Fabric. If something fails for you in Fabric, tell us and we will fix the page.

Fabric's T-SQL engines can read JSON Lines files in place with OPENROWSET(BULK …), but Microsoft does not list gzip compression for JSON Lines, and COPY INTO states that compressed JSON Lines files are not supported. Send2Bucket objects are always gzip, so this page loads them with Spark.

Connect a bucket with a shortcut​

In a lakehouse, open the Files section, select New shortcut, and pick the source:

SourceURLCredentialPermissions
Amazon S3https://<bucket>.s3.<region>.amazonaws.comAccess key and secret of an IAM users3:GetObject, s3:GetBucketLocation, s3:ListBucket
Google Cloud Storagehttps://<bucket>.storage.googleapis.comHMAC key of a service accountstorage.objects.get, storage.objects.list
Amazon S3 compatiblehttps://<endpoint_host> (no bucket in the URL; the endpoint must accept path-style requests)Access key and secretRead and list on the bucket

Point the shortcut at <prefix>/s2b and name it s2b_raw. The objects then appear under Files/s2b_raw/stream=…/date=…/hour=…/. Shortcut target paths cannot contain RFC 3986 reserved characters such as =, so target the s2b folder, not a stream=… folder inside it.

Use a key that can only read, not the one Send2Bucket writes with. Shortcuts are read-only whatever the key allows. See Microsoft's pages for S3, Cloud Storage and S3-compatible shortcuts.

Load with a Spark notebook​

Create a notebook with the lakehouse attached as its default, so relative Files/ paths resolve. This cell appends every delivery batch that is not in the table yet:

from pyspark.sql import types as T

SOURCE = "Files/s2b_raw" # the shortcut to <prefix>/s2b
TABLE = "s2b_records"

schema = T.StructType([
T.StructField("v", T.IntegerType()),
T.StructField("delivery_batch_id", T.StringType()),
T.StructField("batch_id", T.StringType()),
T.StructField("received_at", T.TimestampType()),
T.StructField("signal", T.StringType()),
T.StructField("resource", T.StringType()),
T.StructField("scope", T.StringType()),
T.StructField("data", T.StringType()),
])

records = (spark.read.schema(schema)
.option("basePath", SOURCE)
.json(f"{SOURCE}/stream=*/date=*/hour=*/*.ndjson.gz"))

if spark.catalog.tableExists(TABLE):
loaded = spark.table(TABLE).select("delivery_batch_id").distinct()
records = records.join(loaded, "delivery_batch_id", "left_anti")

records.write.format("delta").mode("append").partitionBy("date").saveAsTable(TABLE)
  • Spark decompresses the .gz objects on its own. basePath turns the stream=, date= and hour= folders into stream, date and hour columns.
  • The explicit schema keeps resource, scope and data as JSON text. Without it, Spark infers a struct from the lines it samples and your JSON events lose fields that differ between records.
  • The anti-join on delivery_batch_id makes reruns add nothing: each object is one delivery batch.
  • stream=* leaves out the destination's test objects under s2b/_verify/.

Schedule the notebook to run every hour or so. It lists the whole prefix on every run; once that gets slow, narrow the glob, for example to date=2026-11-*. Keep the window wide: a delivery delayed by an outage lands under the hour it was received, which can be an older folder than the newest one.

Query with T-SQL​

The lakehouse's SQL analytics endpoint shows s2b_records as dbo.s2b_records. Connect to it from the Fabric query editor, SQL Server Management Studio or any SQL Server client, and create a view with the same columns as the other engine pages:

CREATE VIEW dbo.records AS
SELECT stream, [date], [hour], signal, received_at, delivery_batch_id, resource, data
FROM dbo.s2b_records;

Spark SQL in the same notebook reads the table too, with get_json_object in place of JSON_VALUE.

Connect an Iceberg table​

OneLake can show an Iceberg table that lives in S3, Cloud Storage or S3-compatible storage as a Delta table, through a table shortcut. For an Apache Iceberg destination:

  1. Find the table's location in your catalog. Its folder holds metadata/ and data/.
  2. In a lakehouse without schemas, select New shortcut on the Tables section (not Files), target that table folder, and name the shortcut s2b_events.
  3. The shortcut appears as a table. _delta_log/latest_conversion_log.txt inside it reports whether the conversion worked.

Then create the view over it, instead of the one for bucket objects:

CREATE VIEW dbo.records AS
SELECT signal_type AS signal, ingested_at AS received_at, delivery_batch_id,
JSON_QUERY(payload, '$.resource') AS resource,
JSON_QUERY(payload, '$.data') AS data
FROM dbo.s2b_events;

Microsoft documents limits that matter for Send2Bucket, which commits one snapshot per delivery batch, often once a minute per stream (Use Iceberg tables with OneLake):

  • Conversion takes 5 seconds to 2 minutes, and Microsoft asks for source updates less often than once every 2 minutes, or the Delta view can be inconsistent.
  • The source table must have fewer than 5,000 commits; Microsoft's advice is to compact the table's manifests. At one commit a minute, a stream passes 5,000 in under four days.
  • Only the latest metadata version is converted, so there is no time travel through the shortcut.

For Fabric, a bucket destination with the notebook above is the more predictable path. Amazon S3 Tables and Supabase Analytics Buckets are not covered: Microsoft documents no shortcut to either.

Example queries​

Records per signal:

SELECT signal, COUNT(*) AS records
FROM dbo.records
GROUP BY signal
ORDER BY records DESC;

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

SELECT JSON_VALUE(data, '$.event') AS event,
COUNT(*) AS events,
SUM(CAST(JSON_VALUE(data, '$.cents') AS bigint)) / 100.0 AS revenue_usd
FROM dbo.records
WHERE signal = 'event'
GROUP BY JSON_VALUE(data, '$.event')
ORDER BY events DESC;

Log records by service and severity. OPENJSON turns the resource's attribute list into rows, and the WHERE clause keeps the service.name entry:

SELECT a.service,
JSON_VALUE(r.data, '$.severityText') AS severity,
COUNT(*) AS logs
FROM dbo.records AS r
CROSS APPLY OPENJSON(r.resource, '$.attributes')
WITH ([key] varchar(256) '$.key', service varchar(256) '$.value.stringValue') AS a
WHERE r.signal = 'log' AND a.[key] = 'service.name'
GROUP BY a.service, JSON_VALUE(r.data, '$.severityText');

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

SELECT TOP (5)
JSON_VALUE(data, '$.name') AS span,
JSON_VALUE(data, '$.traceId') AS trace_id,
(CAST(JSON_VALUE(data, '$.endTimeUnixNano') AS bigint)
- CAST(JSON_VALUE(data, '$.startTimeUnixNano') AS bigint)) / 1e6 AS duration_ms
FROM dbo.records
WHERE signal = 'span'
ORDER BY duration_ms DESC;

Notes​

  • The SQL analytics endpoint of a lakehouse maps Delta string columns to varchar(8000) (Data types). A data, resource or payload value longer than 8,000 bytes is cut, and JSON_VALUE on cut JSON fails with "JSON text is not properly formatted". If your records can be that large, extract the fields you need into their own columns in the notebook, or query them with Spark SQL.
  • String columns in the SQL analytics endpoint use the Latin1_General_100_BIN2_UTF8 collation, so comparisons such as signal = 'event' are case-sensitive.
  • Shortcut reads from S3 and Cloud Storage are egress from that cloud. Shortcut caching (workspace settings, OneLake tab) keeps files for 1 to 28 days and cuts repeated egress; files over 1 GB are not cached.
  • Azure Synapse serverless SQL pool reads Azure Storage and OneLake but not S3 or Cloud Storage directly, so it cannot read a Send2Bucket destination without a copy. Use Fabric.
  • For Spark outside Fabric, see Spark. For other engines, see the Querying overview.