Analytics Database Basic Auth

ClickHouse REST API

High-performance columnar database for real-time analytics

ClickHouse is an open-source column-oriented database management system designed for online analytical processing (OLAP). It enables real-time analytics on petabytes of data with sub-second query response times. Developers use ClickHouse for building analytics dashboards, event logging systems, time-series analysis, and business intelligence applications that require fast aggregations and complex analytical queries.

Base URL https://your-cluster.clickhouse.cloud:8443

API Endpoints

Method	Endpoint	Description
GET	`/`	Execute a SELECT query and retrieve results in various formats (JSON, CSV, TabSeparated)
POST	`/`	Execute INSERT, CREATE, DROP, ALTER and other SQL statements via HTTP POST
GET	`/?query={sql}`	Execute a query using URL parameter with automatic format detection
POST	`/?query=INSERT INTO table FORMAT JSONEachRow`	Insert data in JSON format (one JSON object per line)
GET	`/?query=SELECT * FROM system.tables`	List all tables in the database using system tables
GET	`/?query=SELECT * FROM system.databases`	List all databases in the ClickHouse instance
GET	`/?query=SHOW CREATE TABLE {table}`	Get the CREATE TABLE statement for a specific table
GET	`/?query=SELECT * FROM system.columns WHERE table='{table}'`	Get column information and metadata for a specific table
GET	`/?query=SELECT * FROM system.query_log`	Retrieve query execution history and performance metrics
POST	`/?query=CREATE TABLE {table}`	Create a new table with specified schema and engine
POST	`/?query=ALTER TABLE {table}`	Modify table structure, add/drop columns, or change settings
GET	`/?query=SELECT * FROM system.parts`	Get information about data parts in MergeTree tables for optimization
GET	`/?query=SELECT * FROM system.metrics`	Retrieve current system metrics and performance indicators
POST	`/?query=OPTIMIZE TABLE {table}`	Trigger manual optimization and merging of table data parts
GET	`/ping`	Health check endpoint to verify server availability

Code Examples

# Query data from ClickHouse
curl -u default:your_password \
  'https://your-cluster.clickhouse.cloud:8443/?query=SELECT%20count()%20FROM%20events%20WHERE%20date%20=%20today()%20FORMAT%20JSON'

# Insert data into ClickHouse
curl -u default:your_password \
  -X POST 'https://your-cluster.clickhouse.cloud:8443/?query=INSERT%20INTO%20events%20FORMAT%20JSONEachRow' \
  -d '{"user_id":123,"event_type":"click","timestamp":"2024-01-15 10:30:00"}'

# Create a table
curl -u default:your_password \
  -X POST 'https://your-cluster.clickhouse.cloud:8443/' \
  -d 'CREATE TABLE analytics.events (user_id UInt64, event_type String, timestamp DateTime) ENGINE = MergeTree() ORDER BY timestamp'

// Query ClickHouse using fetch
const queryClickHouse = async (sql) => {
  const auth = btoa('default:your_password');
  const response = await fetch(
    `https://your-cluster.clickhouse.cloud:8443/?query=${encodeURIComponent(sql)}&default_format=JSON`,
    {
      headers: {
        'Authorization': `Basic ${auth}`
      }
    }
  );
  return await response.json();
};

// Execute a SELECT query
const results = await queryClickHouse(
  'SELECT count() as total FROM events WHERE date = today()'
);
console.log(results);

// Insert data
const insertData = async (records) => {
  const auth = btoa('default:your_password');
  const body = records.map(r => JSON.stringify(r)).join('\n');
  await fetch(
    'https://your-cluster.clickhouse.cloud:8443/?query=INSERT INTO events FORMAT JSONEachRow',
    {
      method: 'POST',
      headers: {
        'Authorization': `Basic ${auth}`,
        'Content-Type': 'application/x-ndjson'
      },
      body: body
    }
  );
};

await insertData([
  {user_id: 123, event_type: 'click', timestamp: '2024-01-15 10:30:00'},
  {user_id: 456, event_type: 'view', timestamp: '2024-01-15 10:31:00'}
]);

import requests
from requests.auth import HTTPBasicAuth
import json

# Initialize connection
url = 'https://your-cluster.clickhouse.cloud:8443'
auth = HTTPBasicAuth('default', 'your_password')

# Execute a SELECT query
def query_clickhouse(sql):
    response = requests.get(
        url,
        params={'query': sql, 'default_format': 'JSON'},
        auth=auth
    )
    return response.json()

# Get event count
result = query_clickhouse(
    "SELECT count() as total FROM events WHERE date = today()"
)
print(f"Total events: {result['data'][0]['total']}")

# Insert data
def insert_data(table, records):
    data = '\n'.join(json.dumps(r) for r in records)
    response = requests.post(
        url,
        params={'query': f'INSERT INTO {table} FORMAT JSONEachRow'},
        auth=auth,
        data=data.encode('utf-8')
    )
    return response.status_code == 200

# Insert events
events = [
    {'user_id': 123, 'event_type': 'click', 'timestamp': '2024-01-15 10:30:00'},
    {'user_id': 456, 'event_type': 'view', 'timestamp': '2024-01-15 10:31:00'}
]
insert_data('events', events)

# Create a table
def create_table():
    sql = '''
    CREATE TABLE IF NOT EXISTS analytics.events (
        user_id UInt64,
        event_type String,
        timestamp DateTime,
        properties String
    ) ENGINE = MergeTree()
    ORDER BY (timestamp, user_id)
    '''
    requests.post(url, params={'query': sql}, auth=auth)

create_table()

Connect ClickHouse to AI

Deploy a ClickHouse MCP server on IOX Cloud and connect it to Claude, ChatGPT, Cursor, or any AI client. Your AI assistant gets direct access to ClickHouse through these tools:

clickhouse_query Execute analytical SQL queries on ClickHouse databases and return aggregated results in JSON format

clickhouse_insert Insert event data, logs, or metrics into ClickHouse tables with automatic batching and format conversion

clickhouse_schema_inspector Inspect database schemas, list tables, columns, and data types to understand available data structures

clickhouse_performance_analyzer Analyze query performance, execution times, and resource usage from system tables for optimization recommendations

clickhouse_table_manager Create, modify, and optimize ClickHouse tables with appropriate engines and partitioning strategies

Deploy in 60 seconds

Describe what you need, AI generates the code, and IOX deploys it globally.

Deploy ClickHouse MCP Server →

ClickHouse REST API

API Endpoints

Code Examples

Connect ClickHouse to AI

Deploy in 60 seconds

Related APIs