clickhouse-cloud-connection
Test and validate ClickHouse Cloud connection using clickhouse-connect for gapless-crypto-clickhouse. Use when validating connectivity, troubleshooting connection issues, or verifying environment configuration. Includes version check and query validation.
When & Why to Use This Skill
This Claude skill provides a comprehensive automated workflow for validating and troubleshooting ClickHouse Cloud connections using the clickhouse-connect library. It streamlines the process of loading secure credentials via Doppler, configuring mandatory TLS settings, and executing diagnostic queries to verify database accessibility, user permissions, and service performance.
Use Cases
- Initial Connection Validation: Verifying that a new ClickHouse Cloud instance is reachable and correctly configured during the setup phase.
- Connectivity Troubleshooting: Diagnosing common network issues such as 'Connection Refused', 'SSL/TLS errors', or timeouts specifically for cloud-hosted databases.
- Environment Configuration Audit: Confirming that Doppler secrets and environment variables are correctly injected and mapped to the database client.
- Post-Deployment Health Checks: Running automated verification scripts after credential rotations or infrastructure changes to ensure zero downtime for data services.
- Performance Readiness Testing: Checking if a serverless ClickHouse instance is active or resuming from an idle state before initiating heavy data migrations.
| name | clickhouse-cloud-connection |
|---|---|
| description | Test and validate ClickHouse Cloud connection using clickhouse-connect for gapless-crypto-clickhouse. Use when validating connectivity, troubleshooting connection issues, or verifying environment configuration. Includes version check and query validation. |
ClickHouse Cloud Connection
Validate ClickHouse Cloud connectivity and troubleshoot connection issues for gapless-crypto-clickhouse service.
Purpose
Test connection to ClickHouse Cloud service using clickhouse-connect library with credentials from Doppler. This skill guides the workflow of:
- Loading credentials from Doppler environment
- Configuring clickhouse-connect client (
secure=Truefor Cloud) - Testing connection with diagnostic queries
- Validating service accessibility and performance
When to Use
Use this skill when:
- Connection validation: Verifying ClickHouse Cloud service is accessible
- Troubleshooting: Diagnosing connection errors or authentication failures
- Environment verification: Confirming Doppler credentials are correctly configured
- Post-deployment testing: Validating service after setup or credential rotation
Triggers: User mentions "test connection", "connection failed", "ClickHouse unreachable", "validate credentials"
Prerequisites
Required Environment Variables (loaded from Doppler aws-credentials/prd):
CLICKHOUSE_HOST: Service hostname (e.g.,ebmf8f35lu.us-west-2.aws.clickhouse.cloud)CLICKHOUSE_PORT: HTTPS port (8443)CLICKHOUSE_USER: Database user (default)CLICKHOUSE_PASSWORD: Database password
Required Library:
# Install clickhouse-connect (if not already installed)
uv pip install clickhouse-connect
Workflow
Step 1: Load Credentials from Doppler
# Run Python with Doppler environment
doppler run --project aws-credentials --config prd -- python test_connection.py
Environment Loading: Doppler injects all CLICKHOUSE_* secrets as environment variables
Step 2: Configure clickhouse-connect Client
import os
import clickhouse_connect
# Load from environment (set by Doppler)
client = clickhouse_connect.get_client(
host=os.getenv("CLICKHOUSE_HOST"),
port=int(os.getenv("CLICKHOUSE_PORT", "8443")),
username=os.getenv("CLICKHOUSE_USER", "default"),
password=os.getenv("CLICKHOUSE_PASSWORD"),
secure=True # CRITICAL: Required for ClickHouse Cloud
)
Key Configuration:
secure=True: Enforces TLS/SSL for ClickHouse Cloud (HTTPS endpoint)- Port
8443: HTTPS protocol (not 8123 for HTTP) - Environment fallbacks: Defaults for port and user
Step 3: Test Connection with Version Query
# Query ClickHouse version and current user
result = client.query("SELECT version() as version, currentUser() as user")
version = result.result_rows[0][0]
current_user = result.result_rows[0][1]
print(f"✅ Connection successful!")
print(f" ClickHouse version: {version}")
print(f" User: {current_user}")
Expected Output:
✅ Connection successful!
ClickHouse version: 25.8.1.8702
User: default
Step 4: Validate Service Accessibility
# Test query: Count tables in system database
result = client.query("SELECT count() FROM system.tables")
table_count = result.result_rows[0][0]
print(f" Tables visible: {table_count}")
Success Criteria: Query executes without errors, returns numeric count
Step 5: Test Data Query Performance (Optional)
# Test query: Fetch first row from gapless_crypto.klines (if exists)
try:
result = client.query("SELECT * FROM gapless_crypto.klines LIMIT 1")
print(f" Data accessible: ✅ (gapless_crypto.klines)")
except Exception as e:
print(f" Data accessible: ⚠️ (table not yet created)")
Example Connection Test Script
See: references/connection-test.py for complete executable example
Quick Test (one-liner):
doppler run --project aws-credentials --config prd -- python -c "
import os, clickhouse_connect
client = clickhouse_connect.get_client(
host=os.getenv('CLICKHOUSE_HOST'),
port=int(os.getenv('CLICKHOUSE_PORT')),
username=os.getenv('CLICKHOUSE_USER'),
password=os.getenv('CLICKHOUSE_PASSWORD'),
secure=True
)
print('✅ Connected:', client.query('SELECT version()').result_rows[0][0])
"
Success Criteria
- ✅ Connection established without authentication errors
- ✅ Version query returns ClickHouse version 25.8+
- ✅ User query confirms
defaultuser - ✅ Table count query executes successfully
- ✅ Service response time <1 second (idle service may take 5-10s to resume)
Troubleshooting
Issue: "Connection refused" or "Timeout"
- Check: Verify
CLICKHOUSE_HOSTis correct (should be*.aws.clickhouse.cloud) - Check: Port is
8443(HTTPS), not8123(HTTP) - Check: Service state is
running(may be paused due to idle scaling) - Action: Check ClickHouse Cloud console for service status
Issue: "Authentication failed"
- Check: Verify
CLICKHOUSE_PASSWORDin Doppler matches console password - Action: Reset password in ClickHouse Cloud console → Settings → Reset Password
- Verify: Store new password in Doppler and 1Password
Issue: "SSL/TLS error"
- Check:
secure=Trueparameter is set inget_client() - Verify: ClickHouse Cloud requires TLS, cannot connect without
secure=True
Issue: "Query slow (>10 seconds)"
- Check: Service may be resuming from idle state (first query after 15min idle)
- Expected: Subsequent queries should be fast (<1s)
- Action: Wait for service to fully resume, retry query
Issue: "Table not found (gapless_crypto.klines)"
- Status: Table not yet created (expected for new service)
- Action: Run schema migration from local Docker to ClickHouse Cloud
Connection Parameters Reference
| Parameter | Value | Description |
|---|---|---|
host |
ebmf8f35lu.us-west-2.aws.clickhouse.cloud |
Service hostname (us-west-2) |
port |
8443 |
HTTPS port (not 8123) |
username |
default |
Default database user |
password |
(from Doppler) | Database password |
secure |
True |
Required for ClickHouse Cloud (TLS/SSL) |
Service Details
- Service ID:
a3163f31-21f4-4e22-844e-ef3fbc26ace2 - Organization: "TE's Organization" (
2404d339-6921-4f1c-bf80-b07d5e23b91a) - Region: us-west-2 (AWS)
- Idle Scaling: Enabled (15 minutes)
- Expected latency: <100ms (active), 5-10s (resuming from idle)
References
- Connection Test Script:
references/connection-test.py - clickhouse-connect Documentation: https://clickhouse.com/docs/en/integrations/python
- ClickHouse Cloud Console: https://clickhouse.cloud/services/a3163f31-21f4-4e22-844e-ef3fbc26ace2
- Doppler Dashboard: https://dashboard.doppler.com/workplace/13e9e4203ede563b1d37/projects/aws-credentials
Next Steps
After successful connection validation:
- Schema Migration: Import schema from local Docker to ClickHouse Cloud
- Data Ingestion: Configure gapless-crypto-clickhouse package to use Cloud endpoints
- Production Testing: Validate data collection and query performance