clickhousectl-local-dev

by clickhouse

Use when a user wants to build an application with ClickHouse, set up a local ClickHouse development environment, install ClickHouse, create a local server,… npx skills add https://github.com/clickhouse/agent-skills --skill clickhousectl-local-dev Download ZIPGitHub482

When to Apply

Use this skill when the user wants to:

• Build an application that needs an analytical database or ClickHouse specifically

• Set up a local ClickHouse instance for development

• Install ClickHouse on their machine

• Create tables and start querying ClickHouse locally

• Prototype or experiment with ClickHouse

Step 3: Initialize the project

From the user's project root directory:

clickhousectl local init

This creates a standard folder structure:

clickhouse/
 tables/ # CREATE TABLE statements
 materialized_views/ # Materialized view definitions
 queries/ # Saved queries
 seed/ # Seed data / INSERT statements

Note: This step is optional. If the user already has their own folder structure for SQL files, skip this and adapt the later steps to use their paths.

Step 4: Start a local server

clickhousectl local server start --name 

This starts a ClickHouse server in the background.

To check running servers and see their exposed ports:

clickhousectl local server list

Step 5: Create the schema

Based on the user's application requirements, write CREATE TABLE SQL files.

Write each table definition to its own file in clickhouse/tables/:

# Example: clickhouse/tables/events.sql

CREATE TABLE IF NOT EXISTS events (
 timestamp DateTime,
 user_id UInt32,
 event_type LowCardinality(String),
 properties String
)
ENGINE = MergeTree()
ORDER BY (event_type, timestamp)

When designing schemas, if the clickhouse-best-practices skill is available, consult it for guidance on ORDER BY column selection, data types, and partitioning.

Apply the schema to the running server:

clickhousectl local client --name --queries-file clickhouse/tables/events.sql

Step 6: Seed data (optional)

If the user needs sample data for development, write INSERT statements to clickhouse/seed/:

# Example: clickhouse/seed/events.sql

INSERT INTO events (timestamp, user_id, event_type, properties) VALUES
 ('2024-01-01 00:00:00', 1, 'page_view', '{"page": "/home"}'),
 ('2024-01-01 00:01:00', 2, 'click', '{"button": "signup"}');

Apply seed data:

clickhousectl local client --name --queries-file clickhouse/seed/events.sql