> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kubiks.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# ClickHouse

> OpenTelemetry instrumentation for ClickHouse database operations

## Overview

`@kubiks/otel-clickhouse` provides comprehensive OpenTelemetry instrumentation for [ClickHouse](https://clickhouse.com/). Add distributed tracing to your ClickHouse database queries with a single line of code—perfect for analytics workloads and OLAP queries.

<Frame>
  <img src="https://mintcdn.com/kubiks-fb8cce26/jnl4fI3kCg3EDrBq/images/otel/otel-clickhouse-trace.png?fit=max&auto=format&n=jnl4fI3kCg3EDrBq&q=85&s=2bab76862ba7f60f34270674316c6316" alt="ClickHouse Trace Visualization" width="3378" height="2386" data-path="images/otel/otel-clickhouse-trace.png" />
</Frame>

<Note>
  Visualize your ClickHouse queries with detailed span information including query text, execution time, and performance metrics.
</Note>

## Installation

<CodeGroup>
  ```bash npm theme={null}
  npm install @kubiks/otel-clickhouse
  ```

  ```bash pnpm theme={null}
  pnpm add @kubiks/otel-clickhouse
  ```

  ```bash yarn theme={null}
  yarn add @kubiks/otel-clickhouse
  ```
</CodeGroup>

<Warning>
  **Peer Dependencies:** `@opentelemetry/api` >= 1.9.0, `@clickhouse/client` >= 0.2.0
</Warning>

## Supported Frameworks

Works with any TypeScript framework and Node.js runtime:

<CardGroup cols={3}>
  <Card title="Next.js" icon="react">
    App Router & Pages Router
  </Card>

  <Card title="Fastify" icon="bolt">
    High-performance server
  </Card>

  <Card title="NestJS" icon="n">
    Enterprise framework
  </Card>

  <Card title="Express" icon="e">
    Classic Node.js server
  </Card>

  <Card title="Remix" icon="music">
    Full-stack framework
  </Card>

  <Card title="SvelteKit" icon="fire">
    Modern web framework
  </Card>
</CardGroup>

## Supported Platforms

Works with any observability platform that supports OpenTelemetry:

* [Kubiks](https://kubiks.ai)
* [Sentry](https://sentry.io)
* [Axiom](https://axiom.co)
* [Datadog](https://www.datadoghq.com)
* [New Relic](https://newrelic.com)
* [SigNoz](https://signoz.io)
* And many more...

## Quick Start

Use `ClickHouseInstrumentation` to add tracing to your ClickHouse client:

```typescript theme={null}
import { createClient } from '@clickhouse/client';
import { ClickHouseInstrumentation } from '@kubiks/otel-clickhouse';
import { registerOTel } from '@vercel/otel';

// Register OpenTelemetry with ClickHouse instrumentation
export function register() {
  registerOTel({
    serviceName: 'your-app',
    instrumentations: [
      new ClickHouseInstrumentation(),
    ],
  });
}

// Create your ClickHouse client as usual
const client = createClient({
  host: process.env.CLICKHOUSE_HOST,
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
  database: process.env.CLICKHOUSE_DB,
});

// All queries are now automatically traced
const result = await client.query({
  query: 'SELECT * FROM events WHERE timestamp > now() - INTERVAL 1 HOUR',
});
```

<Tip>
  This is the simplest approach—just add the instrumentation and all ClickHouse queries are automatically traced!
</Tip>

## Configuration Options

```typescript theme={null}
new ClickHouseInstrumentation({
  captureQueryText: true,       // Include SQL in traces (default: true)
  maxQueryTextLength: 1000,     // Max SQL length (default: 1000)
  captureParameters: false,     // Include query parameters (default: false)
})
```

<Info>
  By default, SQL queries are captured in spans. You can disable this by setting `captureQueryText: false` for sensitive environments.
</Info>

## What You Get

Each ClickHouse query automatically creates a span with rich telemetry data:

<AccordionGroup>
  <Accordion title="Span Information">
    * **Span name**: `clickhouse.query`, `clickhouse.insert`, etc.
    * **Operation type**: `db.operation` attribute (SELECT, INSERT, CREATE TABLE, etc.)
    * **SQL query text**: Full query statement captured in `db.statement` (configurable)
    * **Database system**: `db.system` attribute (clickhouse)
    * **Database name**: `db.name` attribute
    * **Server address**: `server.address` and `server.port` attributes
  </Accordion>

  <Accordion title="Performance Metrics">
    * Query execution time
    * Number of rows read
    * Number of bytes processed
    * Network latency
  </Accordion>

  <Accordion title="Error Tracking">
    * Exceptions are recorded with stack traces
    * Proper span status (OK, ERROR)
    * Error messages and ClickHouse error codes
  </Accordion>
</AccordionGroup>

## Span Attributes

The instrumentation adds the following attributes to each span following [OpenTelemetry semantic conventions](https://opentelemetry.io/docs/specs/semconv/database/):

| Attribute        | Description        | Example                   |
| ---------------- | ------------------ | ------------------------- |
| `db.operation`   | SQL operation type | `SELECT`                  |
| `db.statement`   | Full SQL query     | `SELECT * FROM events...` |
| `db.system`      | Database system    | `clickhouse`              |
| `db.name`        | Database name      | `analytics`               |
| `server.address` | Server hostname    | `clickhouse.example.com`  |
| `server.port`    | Server port        | `8123`                    |

## Usage Examples

### Basic Queries

<CodeGroup>
  ```typescript Select theme={null}
  import { createClient } from '@clickhouse/client';

  const client = createClient({
    host: process.env.CLICKHOUSE_HOST,
  });

  // Traced as: clickhouse.query
  const result = await client.query({
    query: 'SELECT count(*) FROM events WHERE date = today()',
    format: 'JSONEachRow',
  });

  const data = await result.json();
  ```

  ```typescript Insert theme={null}
  import { createClient } from '@clickhouse/client';

  const client = createClient({
    host: process.env.CLICKHOUSE_HOST,
  });

  // Traced as: clickhouse.insert
  await client.insert({
    table: 'events',
    values: [
      { user_id: 123, event_name: 'page_view', timestamp: new Date() },
      { user_id: 456, event_name: 'button_click', timestamp: new Date() },
    ],
    format: 'JSONEachRow',
  });
  ```

  ```typescript Aggregations theme={null}
  import { createClient } from '@clickhouse/client';

  const client = createClient({
    host: process.env.CLICKHOUSE_HOST,
  });

  // Complex aggregation query - fully traced
  const result = await client.query({
    query: `
      SELECT 
        user_id,
        count(*) as event_count,
        uniq(session_id) as session_count
      FROM events
      WHERE date >= today() - 7
      GROUP BY user_id
      ORDER BY event_count DESC
      LIMIT 100
    `,
    format: 'JSONEachRow',
  });
  ```
</CodeGroup>

### Streaming Queries

```typescript theme={null}
import { createClient } from '@clickhouse/client';

const client = createClient({
  host: process.env.CLICKHOUSE_HOST,
});

// Stream large result sets - traced from start to finish
const stream = await client.query({
  query: 'SELECT * FROM large_table',
  format: 'JSONEachRow',
});

const reader = stream.stream();
for await (const rows of reader) {
  // Process rows in chunks
  console.log(rows);
}
```

### Parameterized Queries

```typescript theme={null}
import { createClient } from '@clickhouse/client';

const client = createClient({
  host: process.env.CLICKHOUSE_HOST,
});

// Use query parameters for safety
const result = await client.query({
  query: 'SELECT * FROM events WHERE user_id = {userId:UInt64} AND date >= {startDate:Date}',
  query_params: {
    userId: 12345,
    startDate: '2024-01-01',
  },
  format: 'JSONEachRow',
});
```

## Complete Integration Example

Here's a complete example of ClickHouse with OpenTelemetry in a Next.js application:

```typescript lib/clickhouse.ts theme={null}
import { createClient } from '@clickhouse/client';

export const clickhouse = createClient({
  host: process.env.CLICKHOUSE_HOST,
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
  database: process.env.CLICKHOUSE_DB,
});
```

```typescript instrumentation.ts theme={null}
import { registerOTel } from '@vercel/otel';
import { ClickHouseInstrumentation } from '@kubiks/otel-clickhouse';

export function register() {
  registerOTel({
    serviceName: 'your-app',
    instrumentations: [
      new ClickHouseInstrumentation({
        captureQueryText: true,
        maxQueryTextLength: 2000,
      }),
    ],
  });
}
```

```typescript app/api/analytics/route.ts theme={null}
import { NextRequest, NextResponse } from 'next/server';
import { clickhouse } from '@/lib/clickhouse';

export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url);
  const days = searchParams.get('days') || '7';

  // Automatically traced
  const result = await clickhouse.query({
    query: `
      SELECT 
        toDate(timestamp) as date,
        count(*) as events
      FROM analytics_events
      WHERE timestamp >= now() - INTERVAL {days:UInt32} DAY
      GROUP BY date
      ORDER BY date
    `,
    query_params: { days: parseInt(days) },
    format: 'JSONEachRow',
  });

  const data = await result.json();
  return NextResponse.json(data);
}
```

## Best Practices

<AccordionGroup>
  <Accordion title="Use Connection Pooling">
    ClickHouse client handles connection pooling automatically:

    ```typescript theme={null}
    const client = createClient({
      host: process.env.CLICKHOUSE_HOST,
      max_open_connections: 10,
    });
    ```
  </Accordion>

  <Accordion title="Configure Query Timeouts">
    Set appropriate timeouts for your queries:

    ```typescript theme={null}
    const result = await client.query({
      query: 'SELECT * FROM large_table',
      query_params: {
        max_execution_time: 30, // seconds
      },
    });
    ```
  </Accordion>

  <Accordion title="Use Batch Inserts">
    For high-throughput scenarios, batch your inserts:

    ```typescript theme={null}
    await client.insert({
      table: 'events',
      values: largeArrayOfEvents, // Insert in batches
      format: 'JSONEachRow',
    });
    ```
  </Accordion>

  <Accordion title="Monitor Query Performance">
    Use traces to identify slow queries and optimize them with appropriate indexes and table engines.
  </Accordion>
</AccordionGroup>

## Performance Considerations

<AccordionGroup>
  <Accordion title="Minimal Overhead">
    The instrumentation adds minimal overhead (\~1-2ms per query) for tracing operations.
  </Accordion>

  <Accordion title="Sampling">
    Use OpenTelemetry sampling to reduce data volume in high-traffic applications:

    ```typescript theme={null}
    import { TraceIdRatioBasedSampler } from '@opentelemetry/sdk-trace-base';

    registerOTel({
      serviceName: 'your-app',
      sampler: new TraceIdRatioBasedSampler(0.1), // Sample 10% of traces
    });
    ```
  </Accordion>
</AccordionGroup>

## Troubleshooting

<AccordionGroup>
  <Accordion title="Spans Not Appearing">
    Ensure OpenTelemetry is initialized before making ClickHouse queries:

    ```typescript theme={null}
    // In instrumentation.ts or instrumentation.node.ts
    export function register() {
      registerOTel({
        serviceName: 'your-app',
        instrumentations: [new ClickHouseInstrumentation()],
      });
    }
    ```
  </Accordion>

  <Accordion title="SQL Not Captured">
    Check that `captureQueryText` is enabled:

    ```typescript theme={null}
    new ClickHouseInstrumentation({
      captureQueryText: true,
      maxQueryTextLength: 2000,
    })
    ```
  </Accordion>

  <Accordion title="Connection Errors">
    Verify your ClickHouse connection settings:

    ```typescript theme={null}
    const client = createClient({
      host: process.env.CLICKHOUSE_HOST,
      username: process.env.CLICKHOUSE_USER,
      password: process.env.CLICKHOUSE_PASSWORD,
      // Test the connection
    });

    await client.ping(); // Should return true
    ```
  </Accordion>
</AccordionGroup>

## Resources

<CardGroup cols={2}>
  <Card title="ClickHouse Documentation" icon="book" href="https://clickhouse.com/docs">
    Learn more about ClickHouse
  </Card>

  <Card title="GitHub Repository" icon="github" href="https://github.com/kubiks-inc/otel/tree/main/packages/otel-clickhouse">
    View source code and examples
  </Card>

  <Card title="npm Package" icon="box" href="https://www.npmjs.com/package/@kubiks/otel-clickhouse">
    View package on npm
  </Card>

  <Card title="Report Issues" icon="circle-exclamation" href="https://github.com/kubiks-inc/otel/issues">
    Found a bug? Let us know!
  </Card>
</CardGroup>

## License

MIT