Skip to main content

R

note

We ran this page with R 4.6.2, duckdb 2.0.0 and arrow 26.0.1 against S3-compatible objects, an Iceberg REST catalog and DuckLake. The AWS credential chain and Google Cloud Storage settings follow the packages' documentation.

In R, query Send2Bucket data with the duckdb package through DBI. It runs DuckDB inside your R session, so it reads bucket objects, Iceberg tables and DuckLake tables in place and returns data frames, and the SQL is the same as on the DuckDB page. The arrow package also reads bucket objects with dplyr verbs, but not Iceberg or DuckLake catalogs.

install.packages(c("duckdb", "DBI", "dplyr", "dbplyr", "arrow"))

We ran the snippets on this page with R 4.6, duckdb 2.0.0 and arrow 26.0.1 against local S3-compatible storage, an Iceberg REST catalog and a DuckLake catalog in PostgreSQL. DuckLake needs duckdb 2.0.0 or later; check with packageVersion("duckdb").

Connect with duckdb​

library(DBI)
con <- dbConnect(duckdb::duckdb())

Each section below creates a view named records with the columns signal, received_at, resource and data, as on the DuckDB page. Run one statement per dbExecute() call. DuckDB installs and loads the httpfs, iceberg and ducklake extensions the first time a statement needs them.

Amazon S3​

# Uses your AWS profile, environment variables or instance role.
dbExecute(con, "CREATE SECRET s2b_bucket (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1')")

dbExecute(con, "
CREATE VIEW records AS
SELECT * FROM read_json(
's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format = 'newline_delimited',
hive_partitioning = true,
columns = {v: 'INTEGER', delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
resource: 'JSON', scope: 'JSON', data: 'JSON'})")

hive_partitioning adds stream, date and hour columns from the path; filter on date to read only that day's objects. For Google Cloud Storage and S3-compatible storage, change the secret as shown on the DuckDB page and keep the view.

Apache Iceberg, Amazon S3 Tables, Supabase Analytics Buckets​

dbExecute(con, "CREATE SECRET s2b_storage (TYPE s3, KEY_ID '<access_key_id>', SECRET '<secret_access_key>', REGION '<region>')")
dbExecute(con, "CREATE SECRET s2b_catalog (TYPE iceberg, TOKEN '<catalog_token>')")
dbExecute(con, "ATTACH '<warehouse>' AS lakehouse (TYPE iceberg, ENDPOINT '<catalog_uri>', SECRET s2b_catalog)")

dbExecute(con, "
CREATE VIEW records AS
SELECT signal_type AS signal, ingested_at AS received_at, delivery_batch_id,
payload::JSON->'$.resource' AS resource, payload::JSON->'$.data' AS data
FROM lakehouse.<namespace>.<table>")

This is the Iceberg REST catalog form. The ATTACH options for Amazon S3 Tables and Supabase Analytics Buckets are on the DuckDB page and work unchanged from R.

DuckLake​

dbExecute(con, "CREATE SECRET s2b_storage (TYPE s3, KEY_ID '<access_key_id>', SECRET '<secret_access_key>', REGION '<region>')")
dbExecute(con, "
ATTACH 'ducklake:postgres:host=db.example.com dbname=lake user=analyst password=<password>' AS lake
(METADATA_SCHEMA '<metadata_schema>', READ_ONLY)")

dbExecute(con, "
CREATE VIEW records AS
SELECT signal_type AS signal, ingested_at AS received_at, delivery_batch_id,
payload::JSON->'$.resource' AS resource, payload::JSON->'$.data' AS data
FROM lake.<namespace>.<table>")

Omit METADATA_SCHEMA if you did not set one on the destination.

Example queries​

dbGetQuery() returns a data frame. These are the DuckDB page's queries.

Records per signal:

dbGetQuery(con, "
SELECT signal, count(*) AS records
FROM records
GROUP BY ALL
ORDER BY records DESC")

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

dbGetQuery(con, "
SELECT data->>'$.event' AS event,
count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
FROM records
WHERE signal = 'event'
GROUP BY ALL
ORDER BY events DESC")
event events revenue_usd
1 page.view 177 NA
2 order.paid 53 4161
3 email.sent 47 NA
4 signup 23 NA

Log records by service and severity. service.name is one entry in the resource's attribute list:

dbGetQuery(con, "
SELECT (SELECT a->>'$.value.stringValue'
FROM unnest(json_extract(resource, '$.attributes[*]')) AS t(a)
WHERE a->>'$.key' = 'service.name') AS service,
data->>'$.severityText' AS severity,
count(*) AS logs
FROM records
WHERE signal = 'log'
GROUP BY ALL")

The five slowest spans. Timestamps are nanosecond strings, so cast to HUGEINT before subtracting:

dbGetQuery(con, "
SELECT data->>'$.name' AS span,
data->>'$.traceId' AS trace_id,
((data->>'$.endTimeUnixNano')::HUGEINT - (data->>'$.startTimeUnixNano')::HUGEINT) / 1e6 AS duration_ms
FROM records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 5")

Pass values as parameters rather than pasting them into the SQL:

dbGetQuery(con, "SELECT signal, count(*) AS records FROM records WHERE received_at >= ? GROUP BY ALL",
params = list(as.POSIXct("2026-11-19", tz = "UTC")))

dplyr​

With dbplyr installed, tbl() turns the view into a lazy table that dplyr translates to DuckDB SQL:

library(dplyr)

tbl(con, "records") |>
filter(received_at >= "2026-11-19") |>
count(signal, sort = TRUE) |>
collect()

For JSON paths, wrap the DuckDB expression in sql() inside mutate(), for example mutate(event = sql("data->>'$.event'")), or create a view with the extracted columns and use dplyr on that.

Bucket objects with arrow​

arrow::open_dataset() reads gzip NDJSON objects from S3 or Google Cloud Storage and finds the stream, date and hour partitions in the path:

library(arrow)
library(dplyr)

bucket <- s3_bucket("<bucket>", region = "<region>")
records <- open_dataset(bucket$path("<prefix>/s2b"), format = "json", unify_schemas = TRUE)
  • s3_bucket() uses the standard AWS credential chain. For S3-compatible storage, pass endpoint_override, access_key and secret_key. For Google Cloud Storage, use gs_bucket().
  • unify_schemas = TRUE reads every object's schema before the query. Without it, arrow takes the schema from the first object, and if that object holds only JSON events, log and span fields are missing. On a large prefix, open a narrower path, such as <prefix>/s2b/stream=<stream_id>/date=2026-11-19.
  • Arrow skips files and folders whose names start with _ or ., so the destination's test objects under s2b/_verify/ are left out.

Struct fields are reachable with $ inside dplyr verbs:

records |>
filter(signal == "event") |>
mutate(event = data$event, cents = data$cents) |>
group_by(event) |>
summarise(events = n(), revenue_usd = sum(cents, na.rm = TRUE) / 100) |>
arrange(desc(events)) |>
collect()

records |>
filter(signal == "span") |>
transmute(span = data$name,
duration_ms = (cast(data$endTimeUnixNano, int64()) - cast(data$startTimeUnixNano, int64())) / 1e6) |>
arrange(desc(duration_ms)) |>
head(5) |>
collect()

Cast nanosecond strings with cast(..., int64()) before subtracting. Arrow refuses to convert these values to a double, because they exceed the range a double holds exactly.

Arrow's dplyr bindings cannot search a list of attributes, so look up service.name after collect():

logs <- records |>
filter(signal == "log") |>
transmute(attributes = resource$attributes, severity = data$severityText) |>
collect()

logs$service <- vapply(logs$attributes,
function(a) a$value$stringValue[a$key == "service.name"][1], character(1))

count(logs, service, severity)

Or hand the dataset to DuckDB with to_duckdb() and use the SQL above.

Notes​

  • The duckdb package keeps downloaded extensions and secrets in a temporary directory by default, and prints a message saying so. Connect with duckdb::duckdb(shared_home = TRUE) to keep extensions between sessions, and run the CREATE SECRET, ATTACH and CREATE VIEW statements at the start of each session. See ?duckdb_storage.
  • Use a read-only key and catalog token in R, not the ones Send2Bucket writes with. READ_ONLY on the DuckLake ATTACH keeps the session from committing to the lake.
  • Call dbDisconnect(con) when you are done to shut down the in-process database.
  • For heavy repeated queries over bucket objects, copy a day to Parquet with the COPY statement on the DuckDB page, or use a table destination such as DuckLake.
  • See Delivery format for every envelope field, and the querying overview for other engines.