December 2023 Summaries

ClickHouse has released version 23.11 with a wide range of exciting features and improvements, including the ability to concatenate arbitrary types, a fileCluster function, keeper improvements, asynchronous loading of tables, an index on system.numbers, concurrency control mechanisms, aggressive retries of requests on S3, and a smaller binary size. The release also includes new contributors, who have contributed significantly to ClickHouse's growth and popularity. Additionally, ClickHouse has made significant progress in its implementation of window functions, enabling parallel execution of these functions, which should improve performance for users. Other notable features include column statistics, which enable better query optimization, and the ability to use column statistics to make low-cardinality data types an automatic decision. The release also includes a new feature called Parallel Window Functions, which enables parallel execution of window functions, improving performance. Overall, ClickHouse continues to improve its capabilities and performance, making it an attractive option for users who need powerful data analysis tools.

As a company rooted in open-source, Goldsky leverages Redpanda, Apache Flink, and ClickHouse to deliver a first-in-class blockchain analytics service. By transforming their data into the Avro format, they utilize Redpanda's tiered storage architecture to efficiently stream data to downstream destinations like ClickHouse. Apache Flink is used for processing, providing complex stream processing capabilities such as instream joins and TopN counts, while maintaining high insertion performance to ClickHouse. ClickHouse is chosen for its unrivaled query performance, cost efficiency, and enhanced SQL capabilities, making it ideal for analytics on large datasets. The architecture allows Goldsky to efficiently process and deliver any crypto dataset to potentially thousands of ClickHouse clusters, abstracting complexity through a simple interface or API. By utilizing this technology combination, Goldsky aims to provide a valuable illustration of the efficiency achieved when leveraging ClickHouse and their platform for blockchain analytics.

As the year draws to a close, ClickHouse Cloud has expressed gratitude to its users for their support in shaping an incredible year of growth. The platform expanded its availability to Google Cloud and Azure, with new region support and a dedicated instances service providing advanced isolation and protection of data. Performance improvements were made through the introduction of a new Engine, SharedMergeTree, and enhancements to scaling algorithms. New capabilities were introduced for SQL Console experience, API support, and integrations with services like ClickPipes and Kafka Connect Sink. The platform also prioritized security with features such as Customer Managed Encryption Keys, secure endpoints, S3 access security, and compliance certifications. Users have shared their success stories of migrating to ClickHouse Cloud, including cost reductions and improved performance, and the company is extending its gratitude to its community for supporting its growth.

This article demonstrates how to use a simple linear regression function within ClickHouse to predict delivery times based on two variables: distance of delivery and pickup hour. The dataset used is a subset of the last-mile delivery dataset by Hugging Face, containing 2,293 orders delivered by a single courier in Jilin, China. The model takes into account both distance and pickup hour as input variables, with the goal of predicting the time elapsed between pickup and delivery. The article showcases how to prepare the data, train the model using ClickHouse's stochasticLinearRegression function, evaluate its performance, and visualize the results. The model performs reasonably well for shorter distances but begins to break down for longer journeys. Despite this, it shows some predictive capability and may perform better in other datasets and domains.

The text discusses the concept of SQL-based Observability, which is a real-time analytics-oriented task that can be achieved using SQL principles. The author highlights how SQL's longevity and popularity are an exception in the field of technology, with many other programming languages being created in the 1990s or later. The democratization of self-driving cars, deep learning, and computer vision have led to breakthroughs in AI and engineering. The text also explores the parallel backgrounds of two established paradigms: SQL and Observability. SQL has been used for over 8 centuries by Mediterranean traders, while Observability is a relatively new field that emerged from the convergence of various technologies. The author argues that treating observability as another data use case leads to its commoditization, accelerating its global adoption. The resulting SQL-based observability stack is simple and unopinionated, leaving many options for users to personalize and adapt within an existing IT environment.

The text explores a novel approach to building vector indices in ClickHouse, a columnar database management system, using SQL. The approach is based on Local Sensitive Hashing (LSH) and random projections, which allows for efficient estimation of distances between vectors without the need for exact nearest neighbor searches. The authors demonstrate how this approach can be used to accelerate nearest-neighbor searches in ClickHouse by creating a bit hash for each vector and using a hamming distance calculation to estimate distances. They also discuss the benefits and limitations of this approach, including its potential speedup of up to 10x over traditional brute-force search methods, but also acknowledge that it may not be as effective as graph-based approaches such as HNSW in certain cases. The authors provide guidance on tuning parameters and experimenting with different values for the number of bits/planes used in the LSH approach. Overall, this post provides an interesting and practical solution for accelerating nearest-neighbor searches in ClickHouse using SQL.