I've been evaluating the cost and performance of several LLM providers through Helicone. While their dashboard is useful for high-level monitoring, I needed to perform deeper, custom analysis on the request/response data. Specifically, I required the ability to run complex SQL queries for tasks like calculating per-token costs across different models, analyzing latency distributions, and correlating prompt structure with output quality.
The natural solution was to move the data into BigQuery. Since a bulk export feature wasn't available, I wrote a Python script to fetch logs via the Helicone API and stream them into a BigQuery table. This setup now allows for granular, SQL-based benchmarking.
The core script performs the following:
* Iterates through the Helicone logs API using the `created_at` cursor for pagination.
* Transforms and flattens the nested JSON response into a structured schema.
* Handles rate limits and implements retry logic for robustness.
* Streams records individually to BigQuery to avoid quota issues with large bulk inserts.
Here is the key configuration and function for the data pipeline:
```python
import requests
import pandas as pd
from google.cloud import bigquery
HELICONE_API_KEY = "your_heli_key"
HELICONE_BASE_URL = "https://api.hconeai.com/v1/logs"
BQ_CLIENT = bigquery.Client(project="your-gcp-project")
TABLE_ID = "your_dataset.helicone_logs"
def fetch_and_stream_logs(start_time):
headers = {"Authorization": f"Bearer {HELICONE_API_KEY}"}
params = {
"start": start_time,
"expand": True,
"limit": 100
}
while True:
response = requests.get(HELICONE_BASE_URL, headers=headers, params=params)
data = response.json()
logs = data.get("logs", [])
if not logs:
break
# Transformations: flatten 'requestBody' & 'responseBody', calculate derived fields
rows_to_insert = []
for log in logs:
row = {
"id": log.get("id"),
"created_at": log.get("createdAt"),
"model": log.get("model"),
"provider": log.get("provider"),
"total_tokens": log.get("totalTokens"),
"prompt_tokens": log.get("promptTokens"),
"completion_tokens": log.get("completionTokens"),
"latency_ms": log.get("duration"),
"user_id": log.get("userId"),
"request_body": str(log.get("requestBody", {})),
"response_body": str(log.get("responseBody", {})),
"cost_calculated": (log.get("totalTokens", 0) * 0.000002) # Example cost calculation
}
rows_to_insert.append(row)
# Stream to BigQuery
errors = BQ_CLIENT.insert_rows_json(TABLE_ID, rows_to_insert)
if errors:
print(f"Encountered errors while inserting rows: {errors}")
# Set cursor for next page
params["start"] = logs[-1]["createdAt"]
```
The primary benefits of this approach are:
* **Custom Cost Analysis:** I can now join pricing tables with token counts to compute exact spend per project, model, or user.
* **Performance Benchmarking:** Calculating P99 latency, throughput, and error rates over custom time windows is straightforward.
* **Prompt Engineering Studies:** By analyzing `request_body` and `response_body` at scale, I can correlate prompt patterns with factors like output length or latency.
A few caveats to note:
* The script requires managing the Helicone API rate limits.
* Schema management is manual; if Helicone adds new fields, the table definition and transformation logic must be updated.
* Historical backfills for large volumes of logs can be time-consuming.
This pipeline has become essential for my comparative model evaluations, moving beyond dashboard aggregates to data-driven decisions.
Benchmarks > marketing.
BenchMark