ClickHouse Client

SELECT
    c.name AS category,
    COUNT(DISTINCT o.order_id) AS order_count
FROM sales_db.orders o
JOIN sales_db.products p ON o.product_id = p.product_id
JOIN sales_db.categories c ON p.category_id = c.category_id
GROUP BY c.name
ORDER BY order_count DESC
```

### Configuration[​](#ai-sql-generation-configuration "Direct link to Configuration")

AI SQL generation requires configuring an AI provider in your ClickHouse Client configuration file. You can use either OpenAI, Anthropic, or any OpenAI-compatible API service.

#### Environment-based fallback[​](#ai-sql-generation-fallback "Direct link to Environment-based fallback")

If no AI configuration is specified in the config file, ClickHouse Client will automatically try to use environment variables:

1. First checks for `OPENAI_API_KEY` environment variable
2. If not found, checks for `ANTHROPIC_API_KEY` environment variable
3. If neither is found, AI features will be disabled

This allows quick setup without configuration files:

```
# Using OpenAI
export OPENAI_API_KEY=your-openai-key
clickhouse-client

# Using Anthropic
export ANTHROPIC_API_KEY=your-anthropic-key
clickhouse-client
```

#### Configuration file[​](#ai-sql-generation-configuration-file "Direct link to Configuration file")

For more control over AI settings, configure them in your ClickHouse Client configuration file located at:

- `$XDG_CONFIG_HOME/clickhouse/config.xml` (or `~/.config/clickhouse/config.xml` if `XDG_CONFIG_HOME` is not set) (XML format)
- `$XDG_CONFIG_HOME/clickhouse/config.yaml` (or `~/.config/clickhouse/config.yaml` if `XDG_CONFIG_HOME` is not set) (YAML format)
- `~/.clickhouse-client/config.xml` (XML format, legacy location)
- `~/.clickhouse-client/config.yaml` (YAML format, legacy location)
- Or specify a custom location with `--config-file`

- XML- YAML

```
<config>
    <ai>
        <!-- Required: Your API key (or set via environment variable) -->
        <api_key>your-api-key-here</api_key>

        <!-- Required: Provider type (openai, anthropic) -->
        <provider>openai</provider>

        <!-- Model to use (defaults vary by provider) -->
        <model>gpt-4o</model>

        <!-- Optional: Custom API endpoint for OpenAI-compatible services -->
        <!-- <base_url>https://openrouter.ai/api</base_url> -->

        <!-- Schema exploration settings -->
        <enable_schema_access>true</enable_schema_access>

        <!-- Generation parameters -->
        <temperature>0.0</temperature>
        <max_tokens>1000</max_tokens>
        <timeout_seconds>30</timeout_seconds>
        <max_steps>10</max_steps>

        <!-- Optional: Custom system prompt -->
        <!-- <system_prompt>You are an expert ClickHouse SQL assistant...</system_prompt> -->
    </ai>
</config>
```

```
ai:
  # Required: Your API key (or set via environment variable)
  api_key: your-api-key-here

  # Required: Provider type (openai, anthropic)
  provider: openai

  # Model to use
  model: gpt-4o

  # Optional: Custom API endpoint for OpenAI-compatible services
  # base_url: https://openrouter.ai/api

  # Enable schema access - allows AI to query database/table information
  enable_schema_access: true

  # Generation parameters
  temperature: 0.0      # Controls randomness (0.0 = deterministic)
  max_tokens: 1000      # Maximum response length
  timeout_seconds: 30   # Request timeout
  max_steps: 10         # Maximum schema exploration steps

  # Optional: Custom system prompt
  # system_prompt: |
  #   You are an expert ClickHouse SQL assistant. Convert natural language to SQL.
  #   Focus on performance and use ClickHouse-specific optimizations.
  #   Always return executable SQL without explanations.
```

  

**Using OpenAI-compatible APIs (e.g., OpenRouter):**

```
ai:
  provider: openai  # Use 'openai' for compatibility
  api_key: your-openrouter-api-key
  base_url: https://openrouter.ai/api/v1
  model: anthropic/claude-3.5-sonnet  # Use OpenRouter model naming
```

**Minimal configuration examples:**

```
# Minimal config - uses environment variable for API key
ai:
  provider: openai  # Will use OPENAI_API_KEY env var

# No config at all - automatic fallback
# (Empty or no ai section - will try OPENAI_API_KEY then ANTHROPIC_API_KEY)

# Only override model - uses env var for API key
ai:
  provider: openai
  model: gpt-3.5-turbo
```

### Parameters[​](#ai-sql-generation-parameters "Direct link to Parameters")

Required parameters

- `api_key` - Your API key for the AI service. Can be omitted if set via environment variable:
  - OpenAI: `OPENAI_API_KEY`
  - Anthropic: `ANTHROPIC_API_KEY`
  - Note: API key in config file takes precedence over environment variable
- `provider` - The AI provider: `openai` or `anthropic`
  - If omitted, uses automatic fallback based on available environment variables

Model configuration

- `model` - The model to use (default: provider-specific)
  - OpenAI: `gpt-4o`, `gpt-4`, `gpt-3.5-turbo`, etc.
  - Anthropic: `claude-3-5-sonnet-20241022`, `claude-3-opus-20240229`, etc.
  - OpenRouter: Use their model naming like `anthropic/claude-3.5-sonnet`

Connection settings

- `base_url` - Custom API endpoint for OpenAI-compatible services (optional)
- `timeout_seconds` - Request timeout in seconds (default: `30`)

Schema exploration

- `enable_schema_access` - Allow AI to explore database schemas (default: `true`)
- `max_steps` - Maximum tool-calling steps for schema exploration (default: `10`)

Generation parameters

- `temperature` - Controls randomness, 0.0 = deterministic, 1.0 = creative (default: `0.0`)
- `max_tokens` - Maximum response length in tokens (default: `1000`)
- `system_prompt` - Custom instructions for the AI (optional)

### How it works[​](#ai-sql-generation-how-it-works "Direct link to How it works")

The AI SQL generator uses a multi-step process:

Schema Discovery

**Schema Discovery**

The AI uses built-in tools to explore your database

- Lists available databases
- Discovers tables within relevant databases
- Examines table structures via `CREATE TABLE` statements

Query Generation

**Query Generation**

Based on the discovered schema, the AI generates SQL that:

- Matches your natural language intent
- Uses correct table and column names
- Applies appropriate joins and aggregations

Execution

**Execution**

The generated SQL is automatically executed and results are displayed

### Limitations[​](#ai-sql-generation-limitations "Direct link to Limitations")

- Requires an active internet connection
- API usage is subject to rate limits and costs from the AI provider
- Complex queries may require multiple refinements
- The AI has read-only access to schema information, not actual data

### Security[​](#ai-sql-generation-security "Direct link to Security")

- API keys are never sent to ClickHouse servers
- The AI only sees schema information (table/column names and types), not actual data
- All generated queries respect your existing database permissions

## Connection string[​](#connection_string "Direct link to Connection string")

### Usage[​](#connection-string-usage "Direct link to Usage")

ClickHouse Client alternatively supports connecting to a ClickHouse server using a connection string similar to [MongoDB](https://www.mongodb.com/docs/manual/reference/connection-string/), [PostgreSQL](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING), [MySQL](https://dev.mysql.com/doc/refman/8.0/en/connecting-using-uri-or-key-value-pairs.html#connecting-using-uri). It has the following syntax:

```
clickhouse:[//[user[:password]@][hosts_and_ports]][/database][?query_parameters]
```