ClickHouse

"Show me the top 10 products by sales from the orders table"

SELECT
    product_name,
    SUM(quantity * price) as total_sales
FROM orders
WHERE date >= today() - INTERVAL 30 DAY
GROUP BY product_name
ORDER BY total_sales DESC
LIMIT 10

{
  "columns": ["product_name", "total_sales"],
  "rows": [
    ["Widget Pro", 125000.50],
    ["Gadget Plus", 98500.25],
    ["Tool Kit", 87300.00]
  ]
}

"What databases are available in ClickHouse?"

"List all databases"

[
  "default",
  "system",
  "analytics",
  "logs",
  "events"
]

"Show me all tables in the analytics database"

"List all user-related tables in the default database"

"Show me production tables in analytics, excluding any temp tables"

[
  {
    "database": "analytics",
    "name": "events",
    "engine": "MergeTree",
    "total_rows": 1250000000,
    "total_bytes": 45000000000,
    "primary_key": "date, user_id",
    "sorting_key": "date, user_id, event_type",
    "comment": "User behavior events",
    "columns": [
      {
        "name": "date",
        "column_type": "Date",
        "comment": "Event date"
      },
      {
        "name": "user_id",
        "column_type": "UInt64",
        "comment": "User identifier"
      },
      {
        "name": "event_type",
        "column_type": "String",
        "comment": "Type of event"
      }
    ]
  }
]

"Use chDB to analyze this CSV: SELECT * FROM file('data.csv', CSV) LIMIT 10"

"Use chDB to get data from this URL: SELECT * FROM url('https://example.com/data.json', JSONEachRow)"

"Use chDB to calculate: SELECT sum(value) FROM (SELECT 1 as value UNION ALL SELECT 2 UNION ALL SELECT 3)"

[
  {"id": 1, "name": "Product A", "sales": 1000},
  {"id": 2, "name": "Product B", "sales": 1500},
  {"id": 3, "name": "Product C", "sales": 2000}
]

-- Create read-only user
CREATE USER studio_readonly IDENTIFIED BY 'secure_password_here';

-- Grant SELECT permission on specific databases
GRANT SELECT ON analytics.* TO studio_readonly;
GRANT SELECT ON logs.* TO studio_readonly;

-- Allow listing databases and tables
GRANT SHOW DATABASES ON *.* TO studio_readonly;
GRANT SHOW TABLES ON *.* TO studio_readonly;

CLICKHOUSE_SECURE=true
CLICKHOUSE_VERIFY=true
CLICKHOUSE_PORT=8443

-- Recent queries
SELECT
    user,
    query,
    query_start_time,
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_start_time DESC
LIMIT 100;

Connection refused - unable to connect to ClickHouse
ERROR - Cannot connect to ClickHouse: [errno] Connection refused

401 Authentication failed
Code: 516. DB::Exception: Invalid credentials

Query timed out after 30 seconds
Timeout exceeded: elapsed 30.001 seconds

DB::Exception: Table database.table_name doesn't exist
DB::Exception: Database 'database_name' doesn't exist

DB::Exception: Memory limit exceeded for query
Code: 241. DB::Exception: Memory limit (for query) exceeded

DB::Exception: Syntax error
Code: 62. DB::Exception: Syntax error: unexpected token

SSL certificate verification failed
Certificate verification error

SELECT
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(*) as order_count
FROM sales
WHERE date >= toStartOfMonth(now())
GROUP BY product_category
ORDER BY total_revenue DESC

-- Slow: Scans entire table
SELECT * FROM events

-- Fast: Filters first
SELECT * FROM events
WHERE date >= today() - 30

SELECT * FROM large_table
WHERE conditions
LIMIT 1000  -- Stop after 1000 rows

-- Instead of returning millions of raw rows:
SELECT * FROM events

-- Return summary:
SELECT date, COUNT(*) as events
FROM events
GROUP BY date

-- If table sorted by (date, user_id):
SELECT * FROM table
WHERE date = today()
ORDER BY date, user_id  -- Fast!

SELECT expensive_columns
FROM table
PREWHERE cheap_filter = value  -- Evaluated first
WHERE expensive_filter = value  -- Evaluated after

SELECT * FROM billion_row_table
SAMPLE 0.01  -- 1% sample = 10M rows
WHERE conditions

CREATE TABLE events (
    date Date,
    timestamp DateTime,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()  -- Most common
ORDER BY (date, user_id)
PARTITION BY toYYYYMM(date);

-- If you always filter by date and user_id:
ORDER BY (date, user_id)

-- If you query by user_id first:
ORDER BY (user_id, date)

PARTITION BY toYYYYMM(date)  -- Monthly partitions

-- Good: Right-sized types
user_id UInt64
status Enum8('active'=1, 'inactive'=2)
date Date

-- Bad: Oversized types
user_id String  -- Numbers as strings are slow
status String   -- Use Enum instead

-- Instead of JOINs:
SELECT u.name, e.event
FROM events e
JOIN users u ON e.user_id = u.id

-- Denormalize:
SELECT user_name, event
FROM events_denormalized

CREATE MATERIALIZED VIEW daily_stats
ENGINE = SummingMergeTree()
ORDER BY (date, user_id)
AS SELECT
    date,
    user_id,
    COUNT(*) as event_count
FROM events
GROUP BY date, user_id

Application Database (PostgreSQL)
↓ Real-time streaming
Analytics Database (ClickHouse)