Quickstart Guide: ClickHouse 101

Last Updated on July 3, 2024 by Pinax Team

Use this quick overview and easy step-by-step to get started with ClickHouse.

TL;DR: For developers new to ClickHouse: learn what it is, why you should care, and how you can use it, including instructions to get started. This is the first article in our analytics developer quickstart series.

Are you a developer looking for a database management system that is optimized for high-performance analytical queries?

ClickHouse can be your hero! This open-source SQL database management powerhouse enables lightning-fast queries on large datasets, delivering answers in milliseconds, not minutes.

In this article, we’ll share the key features and versatile use cases of ClickHouse, providing you with a stepping stone into its ecosystem.

What is ClickHouse?

ClickHouse is an open-source, column-oriented SQL database management system optimized for high-performance analytical queries. It is designed to handle large volumes of data and can perform complex queries quickly and efficiently. It does so by storing data in columns instead of rows.

Why ClickHouse?

ClickHouse isn’t just your average database management system. This robust platform offers some pretty impressive features:

1. Speed & performance: In ClickHouse, data is stored in columns, with values from the same columns grouped together. This approach results in processing queries that are up to 100 times faster compared to row-oriented database management systems (DBMS).
2. SQL support: ClickHouse supports a declarative query language based on SQL. This provides a powerful and user-friendly SQL interface, making interactions and queries straightforward and efficient.
3. Integration flexibility: ClickHouse seamlessly integrates with various data pipelines, ETL frameworks, and visualization tools. This flexibility ensures compatibility with a wide range of data processing and analysis workflows.
4. Cost-effective: ClickHouse’s open-source nature and efficient resource utilization make it affordable. It’s an especially viable option for small and medium organizations seeking a cost-effective yet powerful solution for their analytical needs.

ClickHouse use cases

ClickHouse is suitable for a range of applications across different domains. Its performance-oriented architecture and flexible design make it a preferred choice for many use cases:

1. Real-time analytics: ClickHouse excels in real-time analytics by ingesting large data volumes at high speed. This capability enables developers to create responsive dashboards and integrate with visualization tools like Grafana, Tableau, and Superset.
2. Storing logs, events, and traces: ClickHouse is ideal for storing logs, events, and traces from applications, servers, and networking devices. It has efficient compression algorithms and an optimized storage layout that minimize costs while ensuring rapid data ingestion and retrieval.
3. Machine learning & AI: ClickHouse serves as a reliable platform for storing and preprocessing large datasets used in machine learning and AI projects. Engineers can leverage ClickHouse to prepare data before feeding it into machine learning models.
4. Business intelligence: ClickHouse supports over 70 file formats, including Parquet and JSON, making it versatile for business intelligence (BI). Integration with popular visualization tools like Tableau and Power BI enhances its capabilities, making it an excellent choice for BI applications.

Getting started with ClickHouse

Follow the steps below to start using ClickHouse.

1. To download ClickHouse locally, run the following curl command.

It determines if your operating system is supported, and then downloads an appropriate ClickHouse binary. To learn more about other installation options, visit the official installation guide.

curl <https://clickhouse.com/> | sh

2. To start the server, navigate to the directory where the clickhouse binary is saved and run the following command.

./clickhouse server

When you run this command for the first time, it will also create the necessary files and folders in the current directory.

3. To connect to your ClickHouse service, open a new terminal, navigate to the directory where the clickhouse binary is saved, and paste the following command clickhouse-client.

./clickhouse client

4. Like most databases, ClickHouse logically groups tables into databases. To create a new database in ClickHouse, use the CREATE DATABASE command, and paste the following command to create your first database.

CREATE DATABASE IF NOT EXISTS helloworld

5. To create a new table, use the CREATE TABLE command. The following table is named my_first_table in the helloworld database.

CREATE TABLE helloworld.my_first_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp)

6. To insert data into your created table, use the INSERT INTO TABLE command.

INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )

7. ClickHouse is a SQL database, and you query your data by writing the same type of SELECT queries you’re already familiar with. To access the content of the above-created table, paste the following command.

SELECT * FROM helloworld.my_first_table

Wrapping up

That’s all you need to start with ClickHouse. Go ahead and try this powerful and user-friendly DBMS today. As you continue to explore, you’ll discover even more ways that ClickHouse can be a valuable tool for your data analysis needs.

Learn & explore more

1. Explore ClickHouse’s official docs. It’s your ultimate guide to mastering all things ClickHouse.
2. Elevate your skills through the dynamic learning experiences offered by ClickHouse Academy. Whether you prefer self-paced learning or live interactive sessions, there’s a pathway tailored just for you to become a ClickHouse expert.
3. Dive into the world of ClickHouse with instructional video content on the official ClickHouse YouTube channel.

💡 This article answers questions like:
- What is ClickHouse?
- What is ClickHouse used for?
- Why is ClickHouse so fast?
- When should a developer use ClickHouse?