Keep clickhouse integration new relic queryable

New Relic's default logging retention is 30 days on the Original data option and 120 days on Data Plus. Finance, security, and product analytics often need 6–13 months of queryable history. ClickHouse^® fills that gap. A clickhouse integration new relic project fails when teams treat NerdGraph like an ingest API or poll NRQL every minute instead of fanning out at the source. Retention economics and hybrid architecture patterns are in real-time analytics: a definitive guide.

New Relic owns NRQL alerts, entity UI, and applied intelligence on fresh telemetry. ClickHouse owns SQL aggregations, joins to product tables, and customer-facing analytics over months of logs and spans.

The retention cliff and who owns what

Default retention windows from New Relic docs:

Once telemetry is reported, New Relic documents that it cannot be edited or deleted until it expires. Plan ClickHouse ingest before data you need falls off the cliff.

NRQL alerts vs ClickHouse rollups: one owner per window

Do not alert on the same threshold in both systems. NRQL owns short-window paging. ClickHouse owns long-window reporting and product analytics.

NRQL alert (canonical for paging):

SELECT filter(count(*), WHERE level = 'ERROR')
FROM Log
WHERE service = 'checkout'

Use filter() patterns in NRQL alert conditions so zero-error windows return 0 instead of NULL.

ClickHouse rollup (canonical for reporting):

CREATE TABLE nr.logs_error_rate_1m
(
    minute   DateTime,
    service  LowCardinality(String),
    total    UInt64,
    errors   UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (service, minute);

CREATE MATERIALIZED VIEW nr.logs_error_rate_1m_mv TO nr.logs_error_rate_1m AS
SELECT
    toStartOfMinute(event_time) AS minute,
    service,
    count() AS total,
    countIf(level = 'ERROR') AS errors
FROM nr.app_logs
GROUP BY minute, service;

Compare NRQL alert counts to ClickHouse errors weekly. Drift above 1–2% means field mapping or timezone bugs, not "ClickHouse is wrong."

Log API dual-write and its hard limits

When producers POST logs to New Relic, the Log API is the HTTP path. US endpoint: https://log-api.newrelic.com/log/v1. Authenticate with the Api-Key header (license key).

Limits that shape dual-write design:

New Relic warns against calling the Log API synchronously from customer-facing code. Log shipping is async side traffic.

async function emitLog(record) {
  const nrPayload = {
    timestamp: Date.now(),
    message: JSON.stringify(record),
    attributes: { service: record.service, level: record.level, trace_id: record.trace_id },
  };
  const headers = { "Content-Type": "application/json" };
  await Promise.allSettled([
    fetch("https://log-api.newrelic.com/log/v1", {
      method: "POST",
      headers: { ...headers, "Api-Key": process.env.NR_LICENSE_KEY },
      body: JSON.stringify(nrPayload),
    }),
    fetch("https://api.tinybird.co/v0/events?name=app_logs", {
      method: "POST",
      headers: { ...headers, Authorization: `Bearer ${process.env.TB_TOKEN}` },
      body: JSON.stringify({ event_time: new Date().toISOString(), ...record }),
    }),
  ]);
}

New Relic flattens nested JSON inside message (e.g. user.id becomes a queryable attribute). ClickHouse should receive typed columns at ingest, not JSON parsing in every dashboard query.

CREATE TABLE nr.app_logs
(
    event_time   DateTime64(3),
    ingest_time  DateTime64(3) DEFAULT now64(3),
    service      LowCardinality(String),
    level        LowCardinality(String),
    message      String,
    trace_id     String,
    span_id      String,
    hostname     LowCardinality(String),
    attributes   String
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (service, level, event_time);

Reserved attributes and allowlists

New Relic reserves attribute names (eventType, entity.guid, entity.name, hostname, message, timestamp). Strip or rename conflicting keys before ClickHouse ingest. Maintain an allowlist of 20–40 hot attributes as typed columns; park the long tail in attributes as JSON string.

Infrastructure agent: second output, not a native bridge

The infrastructure agent log forwarder uses Fluent Bit with logging.yml under /etc/newrelic-infra/logging.d/. Sources include file, systemd, syslog, tcp, and Windows event logs.

The agent ships to New Relic. It does not forward to ClickHouse. Add a second Fluent Bit [OUTPUT] to HTTP/Tinybird, or route through Kafka.

The Fluent Bit output plugin (out_newrelic.so) targets the same Log API endpoints. Configure licenseKey, maxBufferSize (default 256000 bytes), and maxRecords (default 1024) per plugin docs.

Dual-output Fluent Bit sketch:

[INPUT]
    Name tail
    Path /var/log/app/*.log

[OUTPUT]
    Name newrelic
    Match *
    licenseKey ${NR_LICENSE_KEY}
    endpoint https://log-api.newrelic.com/log/v1

[OUTPUT]
    Name http
    Match *
    URI https://api.tinybird.co/v0/events?name=app_logs
    Header Authorization Bearer ${TB_TOKEN}
    Format json

Keep maxRecords batches under the 1 MB Log API payload cap when dual-writing.

OTLP Collector as the modern fan-out point

New Relic recommends native OTLP ingest as the preferred path for OpenTelemetry data. US HTTP endpoint: https://otlp.nr-data.net with api-key header set to your license key. OTLP/HTTP with protobuf is preferred over gRPC when possible.

The OpenTelemetry Collector reference architecture describes filtering, enriching, and exporting to multiple destinations. That is how you keep New Relic as one sink and ClickHouse as another without dual instrumentation in application code.

receivers:
  otlp:
    protocols:
      http:
        endpoint: 0.0.0.0:4318
processors:
  batch:
    timeout: 5s
    send_batch_size: 8192
exporters:
  otlphttp/newrelic:
    endpoint: https://otlp.nr-data.net
    headers:
      api-key: ${NR_LICENSE_KEY}
  clickhouse:
    endpoint: tcp://clickhouse:9000
    database: otel
service:
  pipelines:
    logs:
      receivers: [otlp]
      processors: [batch]
      exporters: [otlphttp/newrelic, clickhouse]
    traces:
      receivers: [otlp]
      processors: [batch]
      exporters: [otlphttp/newrelic, clickhouse]

Align batch sizes with OTLP limits: 1 MB max payload, compression (gzip or zstd), and OTEL_EXPORTER_OTLP_METRICS_TEMPORALITY_PREFERENCE=delta per New Relic guidance.

Critical: Running New Relic APM agents and OpenTelemetry tooling in the same process is unsupported. Pick one instrumentation stack per service.

Keep traces, metrics, and logs in separate ClickHouse tables with shared resource columns (service.name, deployment.environment). Do not merge signals into one wide table. Per-signal schemas and cardinality budgets are in ClickHouse integration OpenTelemetry.

Trace table sketch:

CREATE TABLE nr.traces
(
    start_time     DateTime64(3),
    trace_id       String,
    span_id        String,
    parent_span_id String,
    service        LowCardinality(String),
    span_name      LowCardinality(String),
    duration_ms    UInt32,
    status_code    LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toDate(start_time)
ORDER BY (service, start_time, trace_id);

Join logs to spans on trace_id for incident replay beyond New Relic trace retention.

Kafka when HTTP dual-write cannot scale

New Relic has no first-class "consume from Kafka" log input. The pattern is fan-out before New Relic:

Producers ──► Kafka topic
                 ├── ClickHouse Kafka engine ──► MergeTree
                 └── Fluent Bit kafka INPUT ──► newrelic OUTPUT ──► Log API

See the Kafka table engine docs for queue table + materialized view setup. Kafka fan-out patterns are in stream Kafka to ClickHouse. Keep Fluent Bit batches under Log API payload limits.

NerdGraph: query and export, never live ingest

NerdGraph is not an ingest API. For pushing custom events in, use the Event API. Use NerdGraph for programmatic NRQL, cross-account queries, and batch export of data already in New Relic.

Interactive NRQL via GraphQL (https://api.newrelic.com/graphql, API-Key header):

{
  actor {
    account(id: YOUR_ACCOUNT_ID) {
      nrql(query: "SELECT count(*) FROM Log WHERE service = 'checkout' SINCE 1 HOUR AGO") {
        results
      }
    }
  }
}

Suits compliance scripts and small scheduled pulls. Not a substitute for streaming ingest at volume.

Historical data export (backfill only)

Organizations on Data Plus can use historical data export via NerdGraph to retrieve raw events as downloadable JSON. Exports require Data Plus, must end at least 12 hours in the past, allow fewer than ~200 million events, reject aggregations and FACET, and exclude metric timeslice data.

mutation {
  historicalDataExportCreateExport(
    accountId: YOUR_ACCOUNT_ID
    nrql: "FROM Log SELECT timestamp, message, service, level WHERE service = 'checkout' SINCE '2026-08-01 00:00:00+0000' UNTIL '2026-08-10 00:00:00+0000'"
  ) {
    id
    status
    percentComplete
  }
}

Poll until COMPLETE_SUCCESS, download gzipped JSON from signed URLs, batch-insert into ClickHouse with idempotent keys (event_time, message, service, hostname). Use ReplacingMergeTree or a dedupe hash column so re-runs do not inflate rollups. Backfill rollup gaps after export loads per backfilling materialized views.

Data Plus also supports streaming data export to object storage for continuous replication, then s3() or S3Queue into ClickHouse. Do not poll NerdGraph every minute to simulate live ingest.

Tinybird for New Relic-adjacent telemetry

When ClickHouse should power product analytics without operating Kafka engines yourself, Tinybird is managed ClickHouse with streaming ingest and sub-second SQL APIs. New Relic agents and NRQL alerts stay the operational layer. Tinybird holds months of logs and spans with HTTP APIs your product team can call directly.

Ingest paths aligned to New Relic fan-out

1. Log API dual-write — POST the same typed JSON you send to New Relic (minus reserved attributes like eventType) to the Events API. Default limits: 100 requests per second per Data Source; 10 MB per request on Free plans, 100 MB on paid plans. Batch and gzip on both sinks.
2. Kafka fan-out — Connect the canonical bus topic with the Kafka connector (Confluent Platform and Cloud, Redpanda, AWS MSK).
3. OTLP Collector export — Land spans and logs in separate Data Sources; do not merge signals into one wide table.

Rollups that mirror NRQL alert windows

Build 1-minute and 5-minute rollups as SQL Pipes aligned to the NRQL conditions that page on-call. Report from Tinybird for 90-day error budgets and product analytics; page from NRQL for short-window SLOs.

Example Pipe for 15-minute error rates by service:

NODE error_rate_by_service
SQL >
    SELECT
        service,
        countIf(level IN ('error', 'ERROR', 'critical', 'fatal')) AS errors,
        count() AS total,
        errors / total AS error_rate
    FROM app_logs
    WHERE event_time >= now() - INTERVAL 15 MINUTE
    GROUP BY service
    ORDER BY error_rate DESC

TYPE endpoint

After NerdGraph historical export backfills raw logs, run schema iteration to add new typed columns without downtime, then rebuild rollup Pipes on a Branch before promoting to production.

For customer-facing usage dashboards over long retention windows, follow patterns in Vercel user-facing analytics. Use JWT fixed parameters for tenant scoping on product endpoints.

Monitor workspace ingest via Service Data Sources. Canva reports 3.6 PB processed per month and 54 ms p99 query latency on Tinybird's product page. Resend reports 100 TB processed per month and 62 ms p90 query latency without relying on cache. Tinybird is SOC 2 Type II certified.

Use New Relic for live investigation and NRQL alerts. Use Tinybird when product or security analytics need SQL with longer retention.

Acceptance tests before production

1. Dual-write: Log API and ClickHouse ingest succeed under gzip batch load test
2. Parity: Weekly NRQL error counts match ClickHouse rollups within 1–2%
3. Retention: ClickHouse holds data past New Relic expiry for a pilot service
4. OTLP: Collector fan-out delivers identical span counts to both sinks in staging
5. Export: Historical export backfill completes with idempotent inserts
6. Query perf: Top product dashboard queries filter on service and time per five rules for faster SQL

5 operational mistakes on New Relic ClickHouse integrations

1. Treating NerdGraph as an ingest pipe

NerdGraph queries data already in New Relic. Cron jobs that poll GraphQL every minute time out and leave ClickHouse stale.

Fix: Ingest at the source via Log API, Kafka, or OTLP fan-out.

2. Dual-writing without compression or batching

Log API throughput caps are real. Unbatched dual writes hit limits and add latency to emit paths.

Fix: Batch and gzip on both sinks. Keep emit paths async.

3. Copying NRQL aggregations into export jobs

Historical export rejects aggregated NRQL. Export raw attributes; aggregate in ClickHouse materialized views.

Fix: Run FACET and TIMESERIES in ClickHouse SQL, not in export definitions.

4. Running New Relic APM and OpenTelemetry SDK together

Competing runtime hooks in one process produce unpredictable behavior.

Fix: One stack per service. Fan out from a single OTel path.

5. Alerting on both systems for the same threshold

Duplicate NRQL and ClickHouse alerts page twice and disagree during partial outages.

Fix: NRQL owns short-window pages; ClickHouse owns long-window analytics.

What the integration comes down to

New Relic integration with ClickHouse is a hybrid telemetry architecture driven by retention limits and query economics. New Relic keeps NRQL alerts and entity UI on fresh data. ClickHouse holds months of logs and spans for SQL, product joins, and customer-facing analytics.

Ingest at the source via Log API dual-write, Kafka fan-out, or OTLP Collector multi-export. NerdGraph and historical export are backfill tools, not live pipes. One instrumentation stack per process; fan out from there.

Frequently Asked Questions (FAQs)

Can ClickHouse replace New Relic?

For entity-centric APM UI, applied intelligence, and turnkey NRQL alerts, New Relic remains the better operational console. ClickHouse wins for long-retention aggregations and SQL APIs. Most teams run hybrid architectures.

Does New Relic forward logs directly to ClickHouse?

Not natively. You need dual-write to Log API and HTTP ingest, Kafka fan-out, OTLP Collector exporters, or Data Plus export to object storage followed by ClickHouse batch load.

Why use Kafka if New Relic has the Log API?

Kafka decouples producers from multiple consumers and enables replay. Choose it when volume or consumer count justifies the ops cost.

Does Tinybird replace the New Relic agent?

No. New Relic agents and NRQL alerts remain the operational layer. Tinybird is the analytical store and API layer for long-retention queries.