August 2023 Summaries

The ClickHouse Community has developed chDB, an embedded SQL OLAP Engine powered by ClickHouse, allowing users to exploit the full power and performance of ClickHouse directly in their Python code. This model of database interaction has shown to be increasingly popular amongst data scientists and data engineers, providing a first-class integration and addressing common challenges such as performance, memory copies, and parallel pipelines. With chDB, users can query local and remote files with the full functionality of ClickHouse, integrate SQL into wider logic, and use ClickHouse with standard data manipulation and analysis libraries like Pandas. The project has already opened doors to interesting possibilities, including making AWS Lambda functions with ClickHouse simpler to write and deploy, providing a stateless experience for testing and fast data analysis, and unlocking new scales of local analytics. The community plans to extend chDB's capabilities, including support for User-Defined Functions (UDFs), ARM64/AARCH64 support, and ensuring prompt updates to support the latest versions of ClickHouse.

The latest release of ClickHouse, version 23.7, introduces several exciting features, including 31 new features, 16 performance optimizations, and 47 bug fixes. The release also includes improvements to Parquet writing, which is now up to 6x faster, and sparse columns are enabled by default for better compression and query performance. Additionally, ClickHouse now supports experimental support for PRQL (Pipelined Relational Query Language), a simple, powerful, pipelined SQL replacement. The experimental feature allows users to experiment with a public playground and explore its potential use cases in search and discovery exercises. Overall, the release aims to improve the performance, scalability, and usability of ClickHouse while providing more flexibility for developers to work with the database.

At ClickHouse, they appreciate open-source communities and the value they contribute to innovation. They have explored clickhouse-local – a version of ClickHouse designed and optimized for data analysis using local compute resources on laptops or workstations. This tool offers SQL query capabilities similar to a server installation but is available as a single executable binary. The Hugging Face API provides well-documented and consistent APIs, which has been fundamental in its adoption and position as the de facto means of sharing models and datasets. ClickHouse supports additional analytical functions to simplify queries, including correlation matrices, hypothesis testing, student t-tests, Welch t-tests, Cramer's V, and Theil's U. These statistical tests can be used to analyze the properties and distribution of data in a dataset, such as danceability, energy, popularity, and acousticness. Additionally, users can create user-defined functions (UDFs) to query Hugging Face datasets by only their name, making it easier to explore these large datasets on laptops or workstations. By using clickhouse-local, users can take advantage of the performance of ClickHouse while still being able to perform basic statistical analysis and data exploration without writing code in SQL.

Ongage, an automation marketing company based in Israel, recognized the need for real-time insights to make or break their digital marketing campaigns. They transitioned from a data warehouse that couldn't offer real-time capabilities to ClickHouse, which provided a significant leap in data processing and analytics capabilities. The team at Ongage explored various solutions, including SingleStore, before choosing ClickHouse due to its cost-effectiveness and features such as Materialized Views. By leveraging ClickHouse's MySQL engine compatibility, materialized views, and SummingMergeTree tables, Ongage was able to streamline their data analysis process, improve system performance, and make informed decisions faster than ever before. The company plans to continue migrating more of their processes to ClickHouse, taking advantage of its scalability, cost-effectiveness, and real-time analytics capabilities.

The SharedMergeTree table engine is a cloud-native solution designed for ClickHouse Cloud, which stores data in virtually limitless shared object storage. It provides seamless cluster scaling, improved durability for inserts, and more lightweight strong consistency for select queries. The engine enables the introduction of Lightweight Updates, which allow users to instantly make results of ALTER UPDATE queries available while using fewer resources. This feature boosts performance and makes updates easier to use for ClickHouse users. The SharedMergeTree table engine is now the default table engine in ClickHouse Cloud for new Development tier services, and it supports a wide range of features that benefit from its improved throughput and scalability of background merges and mutations.

MessageBird relies heavily on ClickHouse for real-time analytics, performance monitoring, and efficient operations. The platform uses ClickHouse to deliver insights that power its customer-facing dashboards and APIs, enabling the company to understand customer behavior and provide timely information. With ClickHouse, MessageBird can track SMS delivery performance, identify anomalies, and ensure operational excellence. The move to ClickHouse Cloud has allowed the company to scale more efficiently, reduce costs by 60-70%, and respond quickly to performance issues. ClickHouse's unique features, such as Projections, have also become cost-effective for MessageBird, aligning with its commitment to innovation and efficiency.

The ClickHouse database has been employing fuzzing techniques for a while, and recent collaborations with security teams and researchers have led to the discovery of new issues and edge cases. The WINGFUZZ team's innovative fuzzing engine generates semantically correct SQL sequences with abundant types by utilizing metadata-based grammar-free mutation and type-affinity based sequence generation. This approach allows ClickHouse to explore a larger input space, generating more test cases than other fuzzers, and has been successfully integrated into the company's daily development process, ensuring security and reliability in each version of the product.

ClickHouse hosted a "ClickHouse and AI - A Summer Meetup" event where Justin Torre from Helicone.ai shared their experience of using ClickHouse for Large Language Model (LLM) observability. Initially, Helicone used Postgres but faced scaling challenges with its dashboards, particularly with aggregation calls that took over 30 seconds to complete. The team migrated to ClickHouse, which provided impressive results, slashing dashboard query times from over 100 seconds to just 0.5 seconds. This migration showcased ClickHouse's efficiency and aligns with Helicone's commitment to open-source. The switch also allowed for the creation of a dual-insertion approach to populate both Postgres views and ClickHouse tables simultaneously. Justin appreciated the support from the ClickHouse team, which helped solve issues efficiently.

ClickHouse is designed to handle high ingest throughput with its synchronous data inserts mechanism, but this can lead to issues like the "Too many parts error" when too many small inserts are sent in quick succession. To mitigate this, ClickHouse introduces asynchronous data inserts, which shift the batching of data from the client side to the server side, allowing for more flexibility and scalability in scenarios where client-side batching is not feasible. Asynchronous inserts provide a buffer that collects data from multiple queries before writing it to storage, reducing the need for frequent part creations and minimizing I/O and CPU cycles used for data ingestion. The return behavior of asynchronous inserts can be configured to either wait for the next buffer flush or immediately return an acknowledgment, with different modes offering varying levels of durability and throughput. By using asynchronous inserts, clients can achieve high ingest throughput while minimizing cluster resource utilization, making it a suitable solution for scenarios where data loss is not critical.