# Broadcast to storage and warehouses

Broadcast sends a trace of each request to the destinations you configure. This page covers
the storage and warehouse kinds: `s3`, `azure_blob`, `bigquery` and `snowflake`. In the console,
open Build > Exports and alerts.

The routes and the `sampling`, `privacy` and `filter` fields are the same for every kind. See
[Broadcast destinations](api-management.md#broadcast-destinations).

Each trace is one JSON object, after your privacy rules apply. A trace leaves out prompt and
completion content unless `privacy.include_content` is `true`.

## Delivery

- The gateway sends traces in batches of up to 50, at least every 2 seconds.
- Each request has a 10-second timeout.
- The gateway retries a failed batch up to 3 times, after 0.5, 1 and 2 seconds.
- It retries on a `5xx`, `408` or `429` status, or a network error. It does not retry other
  errors.
- A retry sends the same batch with the same batch id. Storage writes the same object again.
  BigQuery and Snowflake drop the duplicate rows. See each kind below.

`POST /v1/broadcast/destinations/:id/test` sends one sample trace. It returns `{ok, status}`.
On a failure it returns `{ok: false, status?, error}`, with the reason in `error`.

## Object layout (`s3` and `azure_blob`)

Each batch is one object. Each line of the object is one trace (NDJSON).

```
<prefix>/dt=YYYY-MM-DD/hour=HH/<batch-id>.ndjson
<prefix>/dt=YYYY-MM-DD/hour=HH/<batch-id>.ndjson.gz    (with "gzip": true)
```

- The date and hour are the UTC time at which the gateway sent the batch.
- `<batch-id>` is a UUID. The same traces always give the same id.
- With no `prefix`, the key starts at `dt=`.
- A plain object has `Content-Type: application/x-ndjson`. A gzip object has
  `Content-Type: application/gzip`.

The `dt=` and `hour=` folders are Hive-style partitions. Athena, BigQuery external tables,
Snowflake external stages and Spark read them as columns.

## Amazon S3 and S3-compatible storage (`s3`)

```json
{
  "kind": "s3",
  "config": {
    "bucket": "acme-llm-traces",
    "region": "us-east-1",
    "prefix": "routerplus",
    "access_key_id": "AKIA...",
    "secret_access_key": "...",
    "gzip": true
  }
}
```

| Field | Rule |
|---|---|
| `bucket` | Required. An S3 bucket name. |
| `region` | Required. The bucket's region. Use `auto` for Cloudflare R2. |
| `prefix` | Optional. Folders before `dt=`. The gateway removes a leading and a trailing `/`. |
| `endpoint` | Optional. The origin of an S3-compatible store, such as `https://<account>.r2.cloudflarestorage.com`. https only, except `http://localhost` for MinIO. No path. |
| `access_key_id` | Required. |
| `secret_access_key` | Required. Secret. |
| `session_token` | Optional. For temporary credentials. Secret. |
| `gzip` | Optional. `true` compresses each object. Default `false`. |

- Requests use AWS Signature Version 4 with a signed payload hash.
- With no `endpoint`, the URL is `https://<bucket>.s3.<region>.amazonaws.com/<key>`.
- With an `endpoint`, or a bucket name with a dot, the URL is path style:
  `<endpoint>/<bucket>/<key>`.
- The access key needs `s3:PutObject` on `arn:aws:s3:::<bucket>/<prefix>/*`. It needs no read
  access.

## Azure Blob Storage (`azure_blob`)

```json
{
  "kind": "azure_blob",
  "config": { "account": "acmetraces", "container": "routerplus", "prefix": "prod", "sas_token": "sv=2022-11-02&ss=b&srt=o&sp=cw&sig=..." }
}
```

| Field | Rule |
|---|---|
| `account` | Required. The storage account name. |
| `container` | Required. The container must exist. |
| `prefix` | Optional. Folders before `dt=`. |
| `sas_token` | A SAS token with create and write permission. The gateway removes a leading `?`. Secret. |
| `account_key` | The storage account key (base64). The gateway signs requests with Shared Key. Secret. |
| `gzip` | Optional. `true` compresses each object. Default `false`. |

Send one of `sas_token` and `account_key`. Each batch is a Put Blob request with
`x-ms-blob-type: BlockBlob`.

## Google BigQuery (`bigquery`)

```json
{
  "kind": "bigquery",
  "config": {
    "project_id": "acme-analytics",
    "dataset": "llm",
    "table": "routerplus_events",
    "service_account_json": "{\"client_email\": \"...\", \"private_key\": \"-----BEGIN PRIVATE KEY-----\\n...\", \"token_uri\": \"https://oauth2.googleapis.com/token\"}"
  }
}
```

| Field | Rule |
|---|---|
| `project_id` | Required. |
| `dataset` | Required. |
| `table` | Required. The table must exist. |
| `service_account_json` | Required. The service account key file, as a string or an object. The gateway keeps `client_email`, `private_key`, `token_uri` and `private_key_id`. Secret. |

- The service account needs `bigquery.tables.updateData` on the table. The role
  `roles/bigquery.dataEditor` on the dataset has it.
- The gateway gets an access token with the
  `https://www.googleapis.com/auth/bigquery.insertdata` scope. It uses the token again until one
  minute before it expires.
- The gateway inserts rows with `tabledata.insertAll`. Each row's `insertId` is the event
  id, so BigQuery drops a row that arrives twice.
- A row that BigQuery rejects fails its batch. The gateway logs the row index and the reason.

Create the table:

```sql
CREATE TABLE `acme-analytics.llm.routerplus_events` (
  event_id STRING NOT NULL,      -- the attempt id (generation_id), else the request id
  received_at TIMESTAMP NOT NULL, -- when the gateway sent the batch
  event JSON NOT NULL             -- the trace
)
PARTITION BY DATE(received_at);
```

Query it:

```sql
SELECT JSON_VALUE(event, '$.model') AS model, SUM(CAST(JSON_VALUE(event, '$.cost_usd') AS FLOAT64)) AS cost_usd
FROM `acme-analytics.llm.routerplus_events`
WHERE DATE(received_at) = CURRENT_DATE()
GROUP BY model;
```

## Snowflake (`snowflake`)

```json
{
  "kind": "snowflake",
  "config": {
    "account": "xy12345.us-east-1",
    "user": "ROUTERPLUS_LOADER",
    "private_key_pem": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----",
    "database": "ANALYTICS",
    "schema": "LLM",
    "table": "ROUTERPLUS_EVENTS",
    "warehouse": "LOAD_WH",
    "role": "ROUTERPLUS_LOADER"
  }
}
```

| Field | Rule |
|---|---|
| `account` | Required. The account identifier from your Snowflake URL, such as `xy12345.us-east-1` or `myorg-myaccount`. |
| `user` | Required. |
| `private_key_pem` | Required. An unencrypted RSA private key in PEM (PKCS#8 or PKCS#1). Secret. |
| `database`, `schema`, `table` | Required. Unquoted identifiers: letters, digits, `_` and `$`. |
| `warehouse`, `role` | Optional. Default: the user's defaults. |

The gateway signs in with key-pair authentication. It sends one SQL API request
(`POST /api/v2/statements`) per batch: a parameterized multi-row insert with the traces as
bindings.

```sql
INSERT INTO ANALYTICS.LLM.ROUTERPLUS_EVENTS (event) SELECT PARSE_JSON(column1) FROM VALUES (?), (?), ...
```

Each request has a `requestId` from the batch id. A retry sets `retry=true`. Snowflake then
does not run the same batch twice.

Set up the user, the key and the table:

```sql
-- Generate the key pair (shell):
--   openssl genrsa 2048 | openssl pkcs8 -topk8 -nocrypt -out routerplus_key.p8
--   openssl rsa -in routerplus_key.p8 -pubout -out routerplus_key.pub
CREATE ROLE ROUTERPLUS_LOADER;
CREATE USER ROUTERPLUS_LOADER TYPE = SERVICE DEFAULT_ROLE = ROUTERPLUS_LOADER DEFAULT_WAREHOUSE = LOAD_WH
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';   -- routerplus_key.pub without the BEGIN and END lines
GRANT ROLE ROUTERPLUS_LOADER TO USER ROUTERPLUS_LOADER;

CREATE TABLE ANALYTICS.LLM.ROUTERPLUS_EVENTS (
  event VARIANT NOT NULL,
  received_at TIMESTAMP_LTZ NOT NULL DEFAULT CURRENT_TIMESTAMP()
);

GRANT USAGE ON WAREHOUSE LOAD_WH TO ROLE ROUTERPLUS_LOADER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE ROUTERPLUS_LOADER;
GRANT USAGE ON SCHEMA ANALYTICS.LLM TO ROLE ROUTERPLUS_LOADER;
GRANT INSERT ON TABLE ANALYTICS.LLM.ROUTERPLUS_EVENTS TO ROLE ROUTERPLUS_LOADER;
```

Query it:

```sql
SELECT event:model::string AS model, SUM(event:cost_usd::float) AS cost_usd
FROM ANALYTICS.LLM.ROUTERPLUS_EVENTS
WHERE received_at >= DATEADD(day, -1, CURRENT_TIMESTAMP())
GROUP BY model;
```

## Secrets

A read returns each secret as `"__secret__"`. To keep a stored secret on an update, send
`"__secret__"` in its place.

| `kind` | Secret fields |
|---|---|
| `s3` | `secret_access_key`, `session_token` |
| `azure_blob` | `sas_token`, `account_key` |
| `bigquery` | `service_account_json` |
| `snowflake` | `private_key_pem` |