ClickHouse: Present and Future

Author: Alexey Milovidov, 2021-12-06.

ClickHouse:
Present and Future

I Will Tell You:

— why ClickHouse is a good system;

— why ClickHouse is a bad system;

— and what to do about it.

Impala Spark SQL Presto/Trino Drill Hawq Actian Vortex Kylin Kudu MonetDB Vectorwise Actian Vector Actian Matrix Redshift Snowflake Citus Greenplum OmniSci (mapD) Brytlyt HyPER Druid Pinot kdb+ Shakti jd MariaDB CS Exasol MemSQL (SingleStore) Vertica SAP HANA Teradata Sybase IQ MS SQL with CS index Oracle Exadata IBM Netezza, IBM BLU TiDB

ClickHouse is a Good System

ClickHouse Doesn't Lag

The system was created from practical tasks, for real-world use.

Developed "bottom-up" based on specific scenarios.

Attention to detail and specialization for workload scenarios.

Talk "Secrets of ClickHouse Performance Optimization"
https://www.youtube.com/watch?v=ltg8vstuHUU

ClickHouse is a Reliable System

— cross-datacenter master-master replication;

— reliable writing and data storage;

— protection against hardware failures;

— protection against user errors;

— protection against configuration errors;

— traffic and stored data encryption, authentication;

— all available testing tools in CI;

ClickHouse is a Convenient System

SQL language adapted for analytics convenience:

— aliases anywhere in the query;
— arrays, tuples, lambda functions;
— aggregate function combinators;
— LIMIT BY, ASOF JOIN, ANY/SEMI JOIN, argMin/argMax;

Domain-specific functions out of the box:

— click-stream: URL and IP address processing functions.
— performance monitoring: quantiles;
— geospatial: geoDistance, pointInPolygon, H3, S2;

ClickHouse is a Flexible System

Web analytics.Mobile app analytics. Ads analytics.Realtime bidding. E-commerce analytics.Retail. Games analytics.Video streaming analytics. Media & news analytics. Social recommendations.Classifieds. Dating. Search engine optimization. Telecom traffic analysis.DPI analysis. CDR records analysis. Fraud & spam detection.DDoS protection. Application performance monitoring.Logs & metrics. Security events and logs. SIEM.Analytics of corporate networks. Telemetry.Industrial monitoring.Sensor data.IoT.Self-driving cars.Agriculture. Smart cities, surveillance.Delivery.Taxi.Food tech.HoReCa. Scientific datasets.Genomics.Particle physics.Astronomy & astrophysics.Econometrics. ML feature analytics and research. Trading & financial data.Fintech.Insurance. Investment banking.Blockchain. Gambling.Adult. Business intelligence.

ClickHouse is an Accessible System

ClickHouse can be deployed:

— on your own servers;
— in clouds; with Kubernetes;
— on customer infrastructure;
— on a personal laptop.

ClickHouse is available for different platforms:

— x86_64, aarch64 (ARM), PowerPC 64, RISC-V;
— Linux, FreeBSD, mac OS.

ClickHouse is True Open-Source

Source code is publicly available.

Patches from the community are accepted.

Open development processes.

Low barrier to entry for contributors.

Maximum encouragement and community engagement.

Talk "How to Build a Thriving Community Around an Open-Source Product"
https://www.youtube.com/watch?v=xddKLojmkus&t=4165s

ClickHouse is a Bad* System

* — not perfect.

Replication Requires ZooKeeper

ZooKeeper — a component separate from ClickHouse,
written in Java, requiring careful configuration
and separate servers.

ZooKeeper is Going Away!

1. clickhouse-keeper — 100% compatible with ZooKeeper
by protocol and data model.

— compressed logs and snapshots;
— no issues with zxid overflow;
— no issues with large packets;
— better memory usage;
— no issues with GC and Java heap;

2. Can be run embedded in clickhouse-server.

— no need for a separate service;

Developer: Alexander Sapin.

Data Must Be Inserted in Batches

You can insert millions of rows per second per server.

But those rows must be in just a few batches per second.

You can use Kafka or RabbitMQ tables.

Asynchronous INSERT Queries

Ability to make many frequent INSERTs.

From multiple parallel connections.

Without Kafka and without Buffer tables!

Multiple small INSERTs are combined together
into one batch in memory.

Inserts are reliable by default:
client receives a response when data is written to the table.

Already in production! Developers: Anton Popov, Ivan Lezhankin.

No Transaction Support

Why do we need transactions in ClickHouse?

— for atomic insertion into multiple tables and views.

— for atomic cluster insertion.

— for executing multiple SELECTs from one snapshot.

In development, planned for Q2 2022.

Insufficient SQL Compatibility

SQL language in ClickHouse was initially made non-standard for convenience.

Due to a complex name and type resolution mechanism,
queries are difficult to analyze.

There is a way to support all standard capabilities
and keep all ClickHouse extensions!

2021: Window Functions, ANY/ALL, EXISTS, GROUPING SETS...

2022: Correlated Subqueries

Lack of JOIN Optimizations

Table sorting is not considered for JOIN.

No cost-based optimizer for JOIN reordering.

No grace hash algorithm for JOIN.

No shuffle for distributed JOINs.

And in general, distributed JOINs work poorly.

No UPSERT

No point UPDATE and DELETE,
as well as UNIQUE KEY CONSTRAINT.

Implementing unique key in a distributed system
— is a non-trivial task.

Scaling Complexity

ClickHouse scales perfectly
to thousands of servers and 100 PB of data.

But changing the number of servers in a cluster is painful.

You need to worry about shard and replica configuration.

Data resharding is done manually.

Solution — cloud-native ClickHouse.

By the way, what does that mean?

Resource Separation Complexity

Separation of CPU and IO between queries.

Query priorities.

Memory overcommit.

Insufficient Integration Capabilities

No native UI for ClickHouse

Not enough official integrations with BI and ETL.

Insufficient Awareness in USA and Europe

Insufficiently developed documentation.

Lack of educational materials, courses, and support.

And What to Do About It?

ClickHouse, Inc

Created together with Yandex.

Already raised $300M at a $2B valuation.

ClickHouse Inc Work Directions

  1. Create a cloud service for ClickHouse
    in serverless format with dynamic scaling.
  2. Development and support of ClickHouse in open-source
    to increase ClickHouse market size.
  3. Research and experiments
    to find new niches and opportunities for ClickHouse.

New Horizons for ClickHouse

Support for semistructured data.
Natural language text processing functions.
Streaming queries and complex event processing.
Key-value data marts, incremental aggregation in memory.
Query execution using GPU.
Integration with ML & AI.
Graph processing.
Batch jobs.
Data Hub.

Support For Semistructured Data

JSON data type:

CREATE TABLE games (data JSON) ENGINE = MergeTree;

You can insert arbitrary nested JSONs.

Types are automatically inferred on INSERT and merge.

Data is stored in columnar format: columns and subcolumns.

Query nested data naturally.

Support For Semistructured Data

Example: NBA games dataset

CREATE TABLE games (data String) ENGINE = MergeTree ORDER BY tuple(); SELECT JSONExtractString(data, 'teams', 1, 'name') FROM games;

— 0.520 sec.

CREATE TABLE games (data JSON) ENGINE = MergeTree; SELECT data.teams.name[1] FROM games;

— 0.015 sec.

Support For Semistructured Data

DESCRIBE TABLE games SETTINGS describe_extend_object_types = 1 name: data type: Tuple( <-- inferred type `_id.$oid` String, `date.$date` String, `teams.abbreviation` Array(String), `teams.city` Array(String), `teams.home` Array(UInt8), `teams.name` Array(String), `teams.players.ast` Array(Array(Int8)), `teams.players.blk` Array(Array(Int8)), `teams.players.drb` Array(Array(Int8)), `teams.players.fg` Array(Array(Int8)), `teams.players.fg3` Array(Array(Int8)), `teams.players.fg3_pct` Array(Array(String)), `teams.players.fg3a` Array(Array(Int8)), `teams.players.fg_pct` Array(Array(String)), `teams.players.fga` Array(Array(Int8)), `teams.players.ft` Array(Array(Int8)), `teams.players.ft_pct` Array(Array(String)), `teams.players.fta` Array(Array(Int8)), `teams.players.mp` Array(Array(String)), `teams.players.orb` Array(Array(Int8)), `teams.players.pf` Array(Array(Int8)), `teams.players.player` Array(Array(String)), `teams.players.plus_minus` Array(Array(String)), `teams.players.pts` Array(Array(Int8)), `teams.players.stl` Array(Array(Int8)), `teams.players.tov` Array(Array(Int8)), `teams.players.trb` Array(Array(Int8)), `teams.results.ast` Array(Int8), `teams.results.blk` Array(Int8), `teams.results.drb` Array(Int8), `teams.results.fg` Array(Int8), `teams.results.fg3` Array(Int8), `teams.results.fg3_pct` Array(String), `teams.results.fg3a` Array(Int8), `teams.results.fg_pct` Array(String), `teams.results.fga` Array(Int16), `teams.results.ft` Array(Int8), `teams.results.ft_pct` Array(String), `teams.results.fta` Array(Int8), `teams.results.mp` Array(Int16), `teams.results.orb` Array(Int8), `teams.results.pf` Array(Int8), `teams.results.plus_minus` Array(String), `teams.results.pts` Array(Int16), `teams.results.stl` Array(Int8), `teams.results.tov` Array(Int8), `teams.results.trb` Array(Int8), `teams.score` Array(Int16), `teams.won` Array(Int8))

Support For Semistructured Data

Flexible schema.

You can have columns with strict and flexible schema in one table.

Queries work as fast as with predefined types!

Planned for Q1 2022.
Developer: Anton Popov.

Conclusions

Together with the new company and the open-source community
we will make ClickHouse the best analytical DBMS in the world!