Skip to main content

MySQL

MySQL cannot read objects from a bucket or tables from an Iceberg or DuckLake catalog, so you load Send2Bucket deliveries into it on a schedule. Each run copies the delivery batches it has not seen yet into one table with a JSON column, and generated columns pull out the fields you filter on. The examples are for MySQL 8.0 and later. MariaDB needs a few changes.

When to use MySQL​

Use MySQL when your application or reporting tools already run on it and you want delivered records next to your other tables, for joins and dashboards. Loaded data is only as fresh as the last load. For ad hoc analysis over weeks of records, an engine that reads the destination in place, such as DuckDB, is faster and needs no load job. The overview compares the engines.

Create the tables​

Create three tables once, in the database you load into:

CREATE TABLE s2b_records (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
record JSON NOT NULL,
delivery_batch_id VARCHAR(64) AS (record->>'$.delivery_batch_id') STORED NOT NULL,
signal_type VARCHAR(8) AS (record->>'$.signal') STORED NOT NULL,
received_at DATETIME(6) AS (STR_TO_DATE(record->>'$.received_at', '%Y-%m-%dT%H:%i:%s.%fZ')) STORED NOT NULL,
event VARCHAR(255) AS (record->>'$.data.event') STORED,
resource JSON AS (record->'$.resource') VIRTUAL,
data JSON AS (record->'$.data') VIRTUAL,
KEY (delivery_batch_id),
KEY (signal_type, received_at),
KEY (event, received_at)
);

CREATE TABLE s2b_loads (
delivery_batch_id VARCHAR(64) PRIMARY KEY,
loaded_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);

CREATE TABLE s2b_stage (
record JSON NOT NULL
);
  • s2b_records has one row per record. record is the whole envelope from the delivery format, and a load only ever writes that column. MySQL computes the others from it.
  • signal_type matches the column name in table destinations. signal is a reserved word in MySQL, so it would need backticks everywhere.
  • received_at is the UTC time as a DATETIME, with no time zone conversion. STR_TO_DATE parses the trailing Z, which CAST rejects.
  • data and resource are virtual: they take no space and let queries write data->>'$.event' instead of record->>'$.data.event'.
  • event is the name of a JSON event, indexed for the most common filter. A load fails if an event name is longer than 255 characters, so widen the column if yours can be.
  • s2b_loads lists the delivery batches already loaded. Each object, and each table commit, has its own delivery_batch_id, and its contents never change, so a batch listed here never needs loading again.
  • s2b_stage holds lines between reading and copying. It is empty between runs.

To filter or join on another field, add a stored generated column and an index. This rebuilds the table, so do it before the table grows large:

ALTER TABLE s2b_records
ADD COLUMN order_id VARCHAR(64) AS (record->>'$.data.order_id') STORED,
ADD KEY (order_id);

Give the loader its own user, with only what a load needs:

CREATE USER 's2b_loader'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, INSERT, DELETE ON analytics.s2b_stage TO 's2b_loader'@'%';
GRANT SELECT, INSERT ON analytics.s2b_records TO 's2b_loader'@'%';
GRANT SELECT, INSERT ON analytics.s2b_loads TO 's2b_loader'@'%';

Load with DuckDB​

DuckDB reads every Send2Bucket destination, and its mysql extension writes into MySQL. Save this as load_s2b.sql:

CREATE SECRET s2b_bucket (
TYPE s3, KEY_ID '<access_key_id>', SECRET '<secret_access_key>', REGION '<region>');
CREATE SECRET s2b_mysql (
TYPE mysql, HOST '<mysql_host>', PORT 3306, DATABASE 'analytics',
USER 's2b_loader', PASSWORD '<password>');

ATTACH '' AS my (TYPE mysql, SECRET s2b_mysql);

BEGIN;

INSERT INTO my.s2b_stage (record)
SELECT json
FROM read_ndjson_objects('s3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
hive_partitioning = true)
WHERE date >= current_date - 2
AND json->>'delivery_batch_id' NOT IN (SELECT delivery_batch_id FROM my.s2b_loads);

CALL mysql_execute('my', 'INSERT INTO s2b_records (record) SELECT record FROM s2b_stage');
CALL mysql_execute('my', 'INSERT IGNORE INTO s2b_loads (delivery_batch_id) SELECT DISTINCT record->>''$.delivery_batch_id'' FROM s2b_stage');
CALL mysql_execute('my', 'DELETE FROM s2b_stage');

COMMIT;

Run it from cron or any scheduler, as often as you want fresh data:

duckdb < load_s2b.sql
  • read_ndjson_objects returns each line unchanged as one JSON value, so MySQL stores exactly the envelope Send2Bucket wrote.
  • stream=* skips the test objects that destination checks write to <prefix>/s2b/_verify/.
  • The date filter limits each run to the last few days of folders. Remove it for the first load. date is the UTC day Send2Bucket received the records, so a delivery that was held up for longer than the window, for example while a destination was blocked, lands outside it. After an outage, run once without the filter.
  • DuckDB's MySQL writer sends a value for every column of the target table, NULL for the ones you leave out, and MySQL rejects any value for a generated column. That is why DuckDB fills s2b_stage and mysql_execute runs the copy into s2b_records inside MySQL.
  • All the statements between BEGIN and COMMIT run in one MySQL transaction. If one fails, nothing is committed, and the next run loads the same batches.
  • Run one load at a time. Two overlapping runs share s2b_stage.

For Amazon S3 with an AWS profile or instance role, use CREATE SECRET s2b_bucket (TYPE s3, PROVIDER credential_chain, REGION '<region>'). For Google Cloud Storage and S3-compatible storage, create the secret as in DuckDB: Connect. The key needs to list and read <prefix>/s2b/.

From a table destination​

For Apache Iceberg, Amazon S3 Tables, Supabase Analytics Buckets and DuckLake, attach the catalog as in DuckDB: Connect and read payload instead of the objects. Replace the first INSERT in load_s2b.sql with this one, using the name you attached the catalog as (lake in the DuckLake example):

INSERT INTO my.s2b_stage (record)
SELECT payload
FROM lakehouse.<namespace>.<table>
WHERE ingested_at >= now() - INTERVAL 2 DAY
AND delivery_batch_id NOT IN (SELECT delivery_batch_id FROM my.s2b_loads);

payload holds the same envelope as an object line, so the tables and queries on this page work unchanged. Send2Bucket commits each delivery batch to the table in one commit, so delivery_batch_id marks a complete batch here too. The ingested_at window works like the date filter above.

note

We ran both DuckDB loads against a local S3-compatible store and an Iceberg REST catalog, with DuckDB 2.0 and MySQL 8.4. The other sources were not run here. They use the same DuckDB connections as the DuckDB page.

Load with a shell script​

Without DuckDB, the AWS CLI and the mysql client can do the same job one object at a time. Save this as load_s2b.sh:

#!/usr/bin/env bash
# Usage: load_s2b.sh s3://<bucket>/<prefix>/s2b/stream=<stream_id>/date=2026-11-19/
set -euo pipefail

src="$1"
bucket="${src#s3://}"; bucket="${bucket%%/*}"
work="$(mktemp -d)"

aws s3 ls "$src" --recursive | awk '{print $4}' | grep '\.ndjson\.gz$' |
while read -r key; do
aws s3 cp "s3://$bucket/$key" "$work/batch.ndjson.gz" --quiet
gunzip -f "$work/batch.ndjson.gz"

mysql --local-infile=1 analytics <<SQL
LOAD DATA LOCAL INFILE '$work/batch.ndjson'
INTO TABLE s2b_stage
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t' ESCAPED BY ''
LINES TERMINATED BY '\n'
(record);

START TRANSACTION;
INSERT INTO s2b_records (record)
SELECT s.record
FROM s2b_stage AS s
WHERE NOT EXISTS (SELECT 1 FROM s2b_loads AS l
WHERE l.delivery_batch_id = s.record->>'\$.delivery_batch_id');
INSERT IGNORE INTO s2b_loads (delivery_batch_id)
SELECT DISTINCT record->>'\$.delivery_batch_id' FROM s2b_stage;
DELETE FROM s2b_stage;
COMMIT;
SQL
echo "loaded $key"
done
  • ESCAPED BY '' matters. By default LOAD DATA treats a backslash as an escape character, so \" and \n inside JSON strings are rewritten and MySQL rejects the line as invalid JSON. With escaping off, each line reaches record byte for byte.
  • FIELDS TERMINATED BY '\t' keeps the whole line in one field. Send2Bucket writes compact JSON and escapes control characters inside strings, so a line never contains a tab.
  • gunzip checks the file before anything is loaded, so a cut-off download stops the script instead of loading part of a batch.
  • The copy skips batches already in s2b_loads, so running the script twice over the same folder loads nothing the second time. It still downloads every object again, so pass the narrowest folder you need, such as one day.
  • LOAD DATA LOCAL needs local_infile=ON on the server, which is off by default in MySQL 8.0 and later, and --local-infile=1 on the client.
  • The mysql client reads the host, user and password from ~/.my.cnf, or add -h, -u and -p options.

For Google Cloud Storage, use gcloud storage ls and gcloud storage cp. For S3-compatible storage, add --endpoint-url https://<endpoint> to both aws commands.

note

We ran this script against a local S3-compatible store with a stand-in for the AWS CLI. The aws s3 ls and aws s3 cp calls themselves were not run against AWS.

Example queries​

Records per signal:

SELECT signal_type, COUNT(*) AS records
FROM s2b_records
GROUP BY signal_type
ORDER BY records DESC;

JSON events by name, with revenue from order.paid. ->> returns text, so cast before adding:

SELECT event,
COUNT(*) AS events,
SUM(CASE WHEN event = 'order.paid' THEN CAST(data->>'$.cents' AS SIGNED) END) / 100 AS revenue_usd
FROM s2b_records
WHERE signal_type = 'event'
GROUP BY event
ORDER BY events DESC;
+------------+--------+-------------+
| event | events | revenue_usd |
+------------+--------+-------------+
| page.view | 177 | NULL |
| order.paid | 53 | 4161.0000 |
| email.sent | 47 | NULL |
| signup | 23 | NULL |
+------------+--------+-------------+

Log records by service and severity. service.name is one entry in the resource's attribute list, and JSON_TABLE turns that list into rows to join on:

SELECT svc.service,
r.data->>'$.severityText' AS severity,
COUNT(*) AS logs
FROM s2b_records AS r
JOIN JSON_TABLE(
r.resource, '$.attributes[*]'
COLUMNS (attr_key VARCHAR(255) PATH '$.key',
service VARCHAR(255) PATH '$.value.stringValue')
) AS svc ON svc.attr_key = 'service.name'
WHERE r.signal_type = 'log'
GROUP BY svc.service, severity
ORDER BY logs DESC;

The five slowest spans. Timestamps are nanosecond strings. They fit in a SIGNED (64-bit) integer until the year 2262:

SELECT data->>'$.name' AS span,
data->>'$.traceId' AS trace_id,
(CAST(data->>'$.endTimeUnixNano' AS SIGNED)
- CAST(data->>'$.startTimeUnixNano' AS SIGNED)) / 1e6 AS duration_ms
FROM s2b_records
WHERE signal_type = 'span'
ORDER BY duration_ms DESC
LIMIT 5;

Cast to SIGNED, not UNSIGNED: MySQL stops the query with an out-of-range error if an unsigned subtraction goes below zero, which a span with a bad clock can cause.

MariaDB​

The tables, both loads and the queries work on MariaDB 10.6 and later, the first release with JSON_TABLE, with these changes:

MySQLMariaDB
x->>'$.path'JSON_VALUE(x, '$.path')
x->'$.path'JSON_EXTRACT(x, '$.path')
JSON AS (...) VIRTUAL for data and resourceLONGTEXT AS (...) VIRTUAL
AS (...) STORED NOT NULLAS (...) STORED, since MariaDB allows no NOT NULL on a generated column

MariaDB has no -> or ->> operators. Its JSON type is an alias for LONGTEXT with a JSON_VALID check, so it stores each line as text instead of in a binary format. STR_TO_DATE in the received_at column works as written, but MariaDB does not allow CAST(... AS DATETIME) in a generated column. DuckDB's mysql extension connects to MariaDB the same way.

Notes​

  • Loads keep records exactly once per delivery batch. Send2Bucket itself delivers at least once, so a client that resent a request has its records twice, in two batches. See Delivery semantics to deduplicate on an ID of your own.
  • received_at is a DATETIME in UTC. Compare it with UTC values, such as UTC_TIMESTAMP(), not NOW(), unless your session time zone is UTC.
  • Filter on signal_type, event and received_at so MySQL can use the indexes. A filter on data->>'$.field' reads every row of the signal. Add a generated column for it instead.
  • MySQL stores rows, not columns. For aggregations over many millions of records, a table destination such as DuckLake or Apache Iceberg, queried in place, is faster.