ObsessionDB

https://obsessiondb.com/docs
Admin
ObsessionDB is a managed ClickHouse database platform with decoupled compute and storage, providing...
Tokens:21,323
Snippets:355
Trust Score:5.1
Update:1 month ago
Show doc for...
Tokens:
Raw
### Install dlt with ClickHouse support

Source: https://obsessiondb.com/docs/ingestion/dlt

Install the dlt library with the necessary ClickHouse dependencies.

```bash
pip install "dlt[clickhouse]"
```

--------------------------------

### Configure Redpanda Connect pipeline

Source: https://obsessiondb.com/docs/ingestion/redpanda-connect

Example YAML configuration for reading from Kafka and writing to ObsessionDB with mandatory batching settings.

```yaml
input:
  kafka_franz:
    seed_brokers: [ "localhost:9092" ]
    topics: [ "events" ]
    consumer_group: "obsessiondb"

output:
  sql_insert:
    driver: clickhouse
    dsn: "clickhouse://default:<password>@<your-host>.obsessiondb.com:9440/default?secure=true"
    table: events
    columns: [ timestamp, user_id, event ]
    args_mapping: |
      root = [ this.timestamp, this.user_id, this.event ]

    # Required. Without this it is one INSERT per message.
    batching:
      count: 100000
      period: 30s
```

--------------------------------

### Connect and Query with Elixir

Source: https://obsessiondb.com/docs/drivers-and-clients/ch-elixir

Establishes a connection to the database using Ch.start_link and executes a simple SQL query.

```elixir
{:ok, pid} = Ch.start_link(
  scheme: "https",
  hostname: "<your-host>",
  port: <your-port>,
  username: "<your-user>",
  password: "<your-password>"
)

Ch.query(pid, "SELECT version()")
```

--------------------------------

### Connect to ClickHouse with Go

Source: https://obsessiondb.com/docs/drivers-and-clients/clickhouse-go

Initialize a connection using the clickhouse.Open function with host, authentication, and TLS configuration.

```go
conn, err := clickhouse.Open(&clickhouse.Options{
	Addr: []string{"<your-host>:<your-port>"},
	Auth: clickhouse.Auth{
		Username: "<your-user>",
		Password: "<your-password>",
	},
	TLS: &tls.Config{},
})
```

--------------------------------

### Connect to ClickHouse with Rust

Source: https://obsessiondb.com/docs/drivers-and-clients/clickhouse-rs

Initialize a new ClickHouse client instance using the default builder pattern with URL, user, and password credentials.

```rust
use clickhouse::Client;

let client = Client::default()
    .with_url("https://<your-host>:<your-port>")
    .with_user("<your-user>")
    .with_password("<your-password>");
```

--------------------------------

### Implement the three-table pattern for Kafka ingestion

Source: https://obsessiondb.com/docs/ingestion/kafka-table-engine

Create a Kafka consumer table, a destination MergeTree table, and a Materialized View to pump data between them.

```sql
-- 1. The consumer.
CREATE TABLE events_queue(
    timestamp DateTime64(3),
    user_id   UInt64,
    event     String
)ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'pkc-xxxxx.eu-central-1.aws.confluent.cloud:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'obsessiondb-events',
    kafka_format = 'JSONEachRow',
    kafka_security_protocol = 'SASL_SSL',
    kafka_sasl_mechanism = 'PLAIN',
    kafka_sasl_username = '<api-key>',
    kafka_sasl_password = '<api-secret>',
    kafka_flush_interval_ms = 30000,
    kafka_max_block_size = 1048576;

-- 2. Where the data actually lives.
CREATE TABLE events(
    timestamp DateTime64(3),
    user_id   UInt64,
    event     String
)ENGINE = MergeTree
ORDER BY (timestamp, user_id);

-- 3. The pump between them.
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT * FROM events_queue;
```

--------------------------------

### Connect to ClickHouse using ClickHouse.Driver

Source: https://obsessiondb.com/docs/drivers-and-clients/clickhouse-driver-dotnet

Establishes a connection to a ClickHouse server using the ClickHouseConnectionStringBuilder and ClickHouseConnection classes.

```csharp
var builder = new ClickHouseConnectionStringBuilder
{
    Host = "<your-host>",
    Port = <your-port>,
    Username = "<your-user>",
    Password = "<your-password>",
    UseSession = false,
};

using var connection = new ClickHouseConnection(builder.ToString());
await connection.OpenAsync();
```

--------------------------------

### Define and run a dlt pipeline

Source: https://obsessiondb.com/docs/ingestion/dlt

Create a resource and run a pipeline to load data into the analytics dataset.

```python
import dlt

@dlt.resource(name="events", write_disposition="append")
def events():
    yield from fetch_events()

pipeline = dlt.pipeline(
    pipeline_name="events_to_obsessiondb",
    destination="clickhouse",
    dataset_name="analytics",
)

print(pipeline.run(events()))
```

--------------------------------

### Verify Connection with SQL

Source: https://obsessiondb.com/docs/getting-started

Execute a simple query to confirm the connection to the cluster is active.

```sql
SELECT version(), now();
```

--------------------------------

### Configure the Kafka Connect sink

Source: https://obsessiondb.com/docs/ingestion/clickhouse-kafka-connect-sink

Recommended configuration for the ClickHouseSinkConnector, focusing on batching settings for optimal performance.

```json
{
  "name": "obsessiondb-sink",
  "connector.class": "com.clickhouse.kafka.connect.ClickHouseSinkConnector",
  "tasks.max": "1",
  "topics": "events",
  "hostname": "<your-host>.obsessiondb.com",
  "port": "8443",
  "ssl": "true",
  "database": "default",
  "username": "default",
  "password": "<your-password>",

  "exactlyOnce": "false",
  "ignorePartitionsWhenBatching": "true",
  "bufferCount": "100000",
  "bufferFlushTime": "30",

  "key.converter": "org.apache.kafka.connect.storage.StringConverter",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "value.converter.schemas.enable": "false"
}
```

--------------------------------

### Connect to ClickHouse with TypeScript

Source: https://obsessiondb.com/docs/drivers-and-clients/clickhouse-js

Initialize the client with connection credentials and execute a query using the JSONEachRow format.

```typescript
import { createClient } from '@clickhouse/client'

const client = createClient({
  url: 'https://<your-host>:<your-port>',
  username: '<your-user>',
  password: '<your-password>',
})

const rs = await client.query({ query: 'SELECT version()', format: 'JSONEachRow' })
console.log(await rs.json())
```

--------------------------------

### Configure Spark Session for ObsessionDB

Source: https://obsessiondb.com/docs/ingestion/apache-spark-connector

Initializes a Spark session with the necessary dependencies and catalog configuration for ObsessionDB.

```python
spark = (
    SparkSession.builder
    .config("spark.jars.packages",
            "com.clickhouse.spark:clickhouse-spark-runtime-3.5_2.12:0.8.0,"
            "com.clickhouse:clickhouse-client:0.6.3,"
            "com.clickhouse:clickhouse-http-client:0.6.3,"
            "org.apache.httpcomponents.client5:httpclient5:5.3.1")
    .config("spark.sql.catalog.obsessiondb",
            "com.clickhouse.spark.ClickHouseCatalog")
    .config("spark.sql.catalog.obsessiondb.host", "<your-host>.obsessiondb.com")
    .config("spark.sql.catalog.obsessiondb.http_port", "8443")
    .config("spark.sql.catalog.obsessiondb.protocol", "http")
    .config("spark.sql.catalog.obsessiondb.option.ssl", "true")
    .config("spark.sql.catalog.obsessiondb.user", "default")
    .config("spark.sql.catalog.obsessiondb.password", "<your-password>")
    .config("spark.sql.catalog.obsessiondb.database", "default")
    .getOrCreate()
)
```

--------------------------------

### Create Table with ORDER BY

Source: https://obsessiondb.com/docs/ingestion/apache-spark-connector

Defines a table with an explicit sorting key to ensure optimal performance in MergeTree engines.

```sql
CREATE TABLE obsessiondb.default.events (    timestamp TIMESTAMP,    user_id   BIGINT,    event     STRING)TBLPROPERTIES (    'order_by' = 'timestamp, user_id');
```

--------------------------------

### Connect to ObsessionDB via CLI

Source: https://obsessiondb.com/docs/getting-started

Use the official clickhouse-client to connect to your cluster using the provided host, port, and credentials.

```bash
clickhouse-client \
  --host <your-host> \
  --port <your-port> \
  --secure \
  --user <your-user> \
  --password
```

--------------------------------

### Enable asynchronous inserts

Source: https://obsessiondb.com/docs/ingesting-data

Configure the server to buffer small inserts into larger parts. Setting wait_for_async_insert to 1 ensures durability acknowledgement.

```sql
SET async_insert = 1,
    wait_for_async_insert = 1;   -- keep durability acknowledgement
```

--------------------------------

### Enable async inserts

Source: https://obsessiondb.com/docs/ingestion/clickhouse-kafka-connect-sink

Pass ClickHouse settings to the connector to enable server-side coalescing of inserts.

```json
"clickhouseSettings": "async_insert=1,wait_for_async_insert=1"
```

--------------------------------

### Create the destination table

Source: https://obsessiondb.com/docs/ingestion/clickhouse-kafka-connect-sink

Define the target MergeTree table before the connector begins ingesting data.

```sql
CREATE TABLE events(    timestamp DateTime64(3),    user_id   UInt64,    event     String,    payload   String)ENGINE = MergeTreeORDER BY (timestamp, user_id);
```

--------------------------------

### Configure Claude Desktop for ObsessionDB

Source: https://obsessiondb.com/docs/mcp-server/claude

Add the ObsessionDB MCP server configuration to the Claude Desktop settings file to enable local process communication.

```json
{
  "mcpServers": {
    "obsessiondb": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://console-api.obsessiondb.com/mcp"]
    }
  }
}
```

--------------------------------

### Configure dlt credentials

Source: https://obsessiondb.com/docs/ingestion/dlt

Define the connection settings for ObsessionDB in the .dlt/secrets.toml file.

```toml
[destination.clickhouse.credentials]
database = "default"
username = "default"
password = "<your-password>"
host = "<your-host>.obsessiondb.com"
port = 9440
http_port = 8443
secure = 1
```

--------------------------------

### Verify ingestion performance

Source: https://obsessiondb.com/docs/ingestion/clickhouse-kafka-connect-sink

Query the system query log to ensure inserts are being batched effectively.

```sql
SELECT    count() AS inserts,    avg(rows) AS avg_rows_per_insertFROM clusterAllReplicas('cluster', system.query_log)WHERE type = 'QueryFinish'  AND query_kind = 'Insert'  AND event_time > now() - INTERVAL 10 MINUTE;
```

--------------------------------

### Create a PeerDB Mirror

Source: https://obsessiondb.com/docs/ingestion/peerdb

Defines the replication mirror between a Postgres source and the ObsessionDB peer with specific table mappings and sync intervals.

```sql
CREATE MIRROR pg_to_obsessiondbFROM postgres_source TO obsessiondbWITH TABLE MAPPING (public.users:users, public.orders:orders)WITH (  do_initial_copy = true,  sync_interval = 60);
```

--------------------------------

### Connect Streamlit to ObsessionDB

Source: https://obsessiondb.com/docs/bi-and-sql-tools/streamlit

Use the clickhouse-connect library to establish a connection. Wrap the client initialization in @st.cache_resource to prevent redundant reconnections during app reruns.

```python
import streamlit as st
import clickhouse_connect

@st.cache_resource
def get_client():
    return clickhouse_connect.get_client(
        host="<your-host>", port=<your-port>,
        username="<your-user>", password="<your-password>",
        secure=True,
    )

df = get_client().query_df("SELECT * FROM my_table LIMIT 1000")
st.dataframe(df)
```

--------------------------------

### Configure VS Code MCP settings

Source: https://obsessiondb.com/docs/mcp-server/vscode-copilot

Create this file in your workspace to define the ObsessionDB MCP server endpoint.

```json
{
  "servers": {
    "obsessiondb": {
      "type": "http",
      "url": "https://console-api.obsessiondb.com/mcp"
    }
  }
}
```

--------------------------------

### Insert Data

Source: https://obsessiondb.com/docs/getting-started

Add records to the table. Batching inserts is recommended for optimal performance.

```sql
INSERT INTO events VALUES
  (now(), 1, 'click', '{}'),
  (now(), 2, 'view',  '{}');
```

--------------------------------

### Determine cache convergence

Source: https://obsessiondb.com/docs/distributed-cache

Logic to evaluate if the active dataset fits within the disk cache capacity, accounting for a 20% headroom buffer.

```text
if  working_set_per_node  ≤  per_node_disk_cache_size × 0.8
    → the ring converges on ~100% hit rate after warm-up
else
    → a small tail of reads falls through to object storage; partition pruning bounds it
```

--------------------------------

### Configure Vector ClickHouse Sink

Source: https://obsessiondb.com/docs/ingestion/vector

Define the sink configuration in your Vector TOML file to point to an ObsessionDB instance.

```toml
[sinks.obsessiondb]
type = "clickhouse"
inputs = ["my_source"]
endpoint = "https://<your-host>:<your-port>"
database = "default"
table = "my_table"
auth.strategy = "basic"
auth.user = "<your-user>"
auth.password = "<your-password>"
```

--------------------------------

### Configure OpenCode MCP integration

Source: https://obsessiondb.com/docs/mcp-server/opencode

Add this configuration to your opencode.json file to enable the remote ObsessionDB MCP server.

```json
{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "obsessiondb": {
      "type": "remote",
      "url": "https://console-api.obsessiondb.com/mcp",
      "enabled": true
    }
  }
}
```

--------------------------------

### Connect to ObsessionDB using SimPod PSR-18 Client

Source: https://obsessiondb.com/docs/drivers-and-clients/php-clients

Initializes a PSR-18 compliant HTTP client for ObsessionDB. Requires an existing HTTP client, request factory, and stream factory.

```php
$client = new SimPod\ClickHouseClient\Client\Http\Psr18Client(
    $httpClient, $requestFactory, $streamFactory,
    'https://<your-host>:<your-port>', '<your-user>', '<your-password>'
);
```

--------------------------------

### Import data from S3

Source: https://obsessiondb.com/docs/migrate-from-clickhouse

Use s3Cluster to read data from object storage in parallel across the cluster nodes.

```sql
INSERT INTO events
SELECT * FROM s3Cluster(
    'cluster',
    'https://bucket.s3.amazonaws.com/export/events/*.parquet',
    'Parquet'
);
```

--------------------------------

### Query cluster-wide metrics

Source: https://obsessiondb.com/docs/monitoring-system-tables

Use the clusterAllReplicas table function to aggregate metrics from every node in the cluster simultaneously.

```sql
-- Live metrics from every node at once
SELECT hostName() AS node, metric, value
FROM clusterAllReplicas('cluster', system.metrics)
WHERE metric IN ('Query', 'Merge')
ORDER BY node, metric;
```

--------------------------------

### Consistent Hashing for Parallel Replica Work Assignment

Source: https://obsessiondb.com/docs/locality-reads

Maps segments of work to replicas using a consistent hash based on part name, segment range, and scan mode.

```cpp
auto hash = SipHash();
hash.update(part_name);
hash.update(segment_begin);
hash.update(scan_mode);
return ConsistentHashing(hash.get64(), replicas_count);
```

--------------------------------

### Connect to ObsessionDB using Python

Source: https://obsessiondb.com/docs/drivers-and-clients

Establishes a secure connection to ObsessionDB using the clickhouse-connect library. Ensure host, port, and credentials are provided from your dashboard.

```python
import clickhouse_connect

client = clickhouse_connect.get_client(
    host="<your-host>",      # from your dashboard
    port=<your-port>,
    username="<your-user>",
    password="<your-password>",
    secure=True,
)

print(client.query("SELECT version()").result_rows)
```

--------------------------------

### Connect Dash to ObsessionDB

Source: https://obsessiondb.com/docs/bi-and-sql-tools/plotly-dash

Uses the clickhouse_connect driver to execute SQL queries within a Dash callback and render the resulting dataframe.

```python
import clickhouse_connect
from dash import Dash, dcc, html, Input, Output
import plotly.express as px

client = clickhouse_connect.get_client(
    host="<your-host>", port=<your-port>,
    username="<your-user>", password="<your-password>",
    secure=True,
)

app = Dash(__name__)

@app.callback(Output("chart", "figure"), Input("interval", "n_intervals"))
def update(_):
    df = client.query_df("SELECT event_date, count() AS n FROM events GROUP BY event_date")
    return px.line(df, x="event_date", y="n")
```

--------------------------------

### Create a Table

Source: https://obsessiondb.com/docs/getting-started

Define a table using the MergeTree engine, which is automatically served by SharedMergeTree on ObsessionDB.

```sql
CREATE TABLE events
(
    ts      DateTime,
    user_id UInt64,
    type    LowCardinality(String),
    payload String
)
ENGINE = MergeTree            -- served by SharedMergeTree on ObsessionDB
ORDER BY (user_id, ts);
```

--------------------------------

### Connect via HTTP Endpoint

Source: https://obsessiondb.com/docs/mcp-server/other-clients

Use this URL directly in MCP clients that support remote HTTP connections.

```text
https://console-api.obsessiondb.com/mcp
```

--------------------------------

### Configure ObsessionDB MCP in Zed

Source: https://obsessiondb.com/docs/mcp-server/zed

Add this configuration to your Zed settings.json file to enable the ObsessionDB MCP server.

```json
{
  "context_servers": {
    "obsessiondb": {
      "url": "https://console-api.obsessiondb.com/mcp"
    }
  }
}
```

--------------------------------

### Create the target table

Source: https://obsessiondb.com/docs/ingestion/redpanda-connect

Define the schema for the events table in ObsessionDB before running the pipeline.

```sql
CREATE TABLE events(    timestamp DateTime64(3),    user_id   UInt64,    event     String)ENGINE = MergeTreeORDER BY (timestamp, user_id);
```
Tokens:
Raw