ClickHouse

What‘s ClickHouse?

ClickHouse® is a high-performance, column-oriented SQL database management system (DBMS) for online analytical processing (OLAP). It is available as both an open-source software and a cloud offering.

ClickHouse是一个高性能，面向列的SQL数据库管理系统（DBMS），用于在线分析处理OLAP

Key Characteristics

• column-oriented SQL database

Quick Start

Create Database

CREATE DATABASE IF NOT EXISTS helloworld

Create Table

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

Insert

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 )

Query

SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp

Updating and Deleting ClickHouse Data

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

ALTER TABLE website.clicks UPDATE visitor_id = getDict(‘visitors’, ‘new_visitor_id’, visitor_id) WHERE visit_date < ‘2022-01-01’

Integrating MySQL with ClickHouse

The MySQL table engine allows you to connect ClickHouse to MySQL. SELECT and INSERT statements can be made in either ClickHouse or in the MySQL table. This article illustrates the basic methods of how to use the MySQL table engine.

graph LR
    A[Configure MySQL] --> B[Define a Table in ClickHouse]
    B --> C[Test the Integration]

---

Reference

• Fast Open-Source OLAP DBMS - ClickHouse

• ClickHouse Playground

• CryptoHouse