ClickHouse Connections

ClickHouse connections enable agents to query ClickHouse analytical databases.

Recommended: Create and manage connections via the web UI at https://ui.{deployment}.firetigerapi.com/settings/connections

Network Configuration

Firetiger’s query servers must be able to reach your ClickHouse instance. Choose the appropriate networking setup based on your deployment:

1. ClickHouse Cloud

Scenario: Managed ClickHouse Cloud instance

Important: ClickHouse Cloud exposes two ports — 8443 (HTTP protocol) and 9440 (native protocol). Firetiger uses the native protocol, so you must connect on port 9440 with secure: true. Using port 8443 will result in a connection error.

Solution: Ensure Firetiger’s IP addresses are allowed in your ClickHouse Cloud service’s IP access list.

• Contact Firetiger support to obtain the static IP addresses for your deployment’s query servers
• Add these IPs in ClickHouse Cloud under Settings > Security > IP Access List

2. Private Network Database

Scenario: ClickHouse runs in a private VPC/network (AWS, GCP, Azure)

Solution: Set up a private network connection:

• AWS: Configure AWS PrivateLink
• GCP: Configure Private Service Connect

Contact Firetiger support to coordinate private network setup.

3. Public Instance with Standard Authentication

Scenario: ClickHouse accepts public connections with username/password authentication

Solution: No special networking configuration needed

• Ensure your ClickHouse instance accepts connections on the configured port
• Verify firewall allows inbound connections
• Enable TLS (secure: true) for encrypted connections

Connection Parameters

A ClickHouse connection requires the following configuration:

Required Parameters

Parameter	Type	Description
host	string	Database hostname (e.g., abc.us-east-1.aws.clickhouse.cloud)
database	string	Name of the database to connect to
username	string	Username for authentication
password	string	Password for authentication (stored securely as a secret)

Optional Parameters

Parameter	Type	Description	Default
port	int32	Database port number	9440
secure	bool	Enable TLS encryption	true

Ports and Protocols

Firetiger connects to ClickHouse using the native protocol. The default port depends on whether TLS is enabled:

Configuration	Port	Description
secure: true	9440	Native protocol with TLS (default)
secure: false	9000	Native protocol without TLS

ClickHouse Cloud uses port 9440 by default with TLS enabled.

TLS (Secure Mode)

The secure parameter controls whether the connection uses TLS encryption.

• true (default) - TLS enabled. Recommended for all environments, required for ClickHouse Cloud.
• false - TLS disabled. Only use for local development or testing.

For production environments, always use secure: true.

Description Field

The description should document your schema to help agents write effective queries.

Example:

ClickHouse analytics database for event tracking.

Tables:
- events: event_id (UUID), user_id (String), event_type (String), timestamp (DateTime), properties (String)
- page_views: view_id (UUID), user_id (String), url (String), referrer (String), timestamp (DateTime)
- sessions: session_id (UUID), user_id (String), start_time (DateTime), duration_seconds (UInt32)

Common patterns:
- Events in time range: WHERE timestamp >= '2024-01-01' AND timestamp < '2024-02-01'
- Aggregate by day: GROUP BY toDate(timestamp)
- Top events: SELECT event_type, count() FROM events GROUP BY event_type ORDER BY count() DESC

Example Connection

{
  "display_name": "Analytics ClickHouse",
  "description": "ClickHouse analytics database...",
  "connection_details": {
    "clickhouse": {
      "host": "abc.us-east-1.aws.clickhouse.cloud",
      "port": 9440,
      "database": "analytics",
      "username": "readonly_user",
      "password": "password",
      "secure": true
    }
  }
}

Query Support

Firetiger supports standard ClickHouse SQL syntax including:

• SELECT statements with all standard clauses (WHERE, JOIN, GROUP BY, ORDER BY, LIMIT, etc.)
• Common Table Expressions (CTEs) with WITH
• ClickHouse-specific functions (toDate, toDateTime, formatDateTime, etc.)
• Aggregate functions (count, sum, avg, uniq, quantile, etc.)
• Array and map functions
• SQL comments (both -- single-line and /* */ multi-line styles)

Query results are streamed with a timeout of 5 minutes.

Security Model

Firetiger provides multiple layers of security for ClickHouse connections:

1. Read-only users - Create a dedicated read-only ClickHouse user for Firetiger
2. TLS encryption - Use secure: true for encrypted connections
3. Network restrictions - Use IP allowlists or private networking to restrict access

Recommended approach: Create a dedicated read-only user in ClickHouse with access limited to the necessary databases and tables.

Example - Create a read-only user in ClickHouse:

CREATE USER firetiger_reader IDENTIFIED BY 'secure_password';
GRANT SELECT ON analytics.* TO firetiger_reader;

Best Practices

• Use a read-only user - Create a dedicated ClickHouse user with only SELECT permissions
• Enable TLS - Set secure: true for all production connections
• Document your schema - Include table/column information and common query patterns in the description field
• Limit database access - Only grant access to necessary databases and tables
• Use ClickHouse Cloud defaults - Port 9440 with secure: true for ClickHouse Cloud instances