April 2024 Summaries

Upsolver has announced support for ClickHouse Cloud as a destination, allowing customers to reliably ingest large amounts of data in near real-time from databases, files, and streaming sources into ClickHouse Cloud. The new integration makes it possible to simplify the architecture by eliminating Debezium for CDC, queues, and notification scripts for continuous file loading or complex transformations to handle incremental loading, updating, and deleting of rows. Upsolver's ClickHouse connector enables users to unify their data ingestion and movement, simplifying their architecture and delivering fast, reliable, and high-quality data to ClickHouse, Iceberg lakehouse, and operational systems. The integration allows customers to perform ETL at scale with ClickHouse while maintaining a simple, highly scalable, reliable, and cost-effective solution.

Alibaba Cloud has partnered with ClickHouse to provide a new offering called ApsaraDB for ClickHouse or ClickHouse Enterprise Edition. This collaboration aims to transform data analytics in China by combining ClickHouse's fast performance and Alibaba Cloud's robust infrastructure. The partnership enables users across Asia to access the same level of service and support from Alibaba Cloud as those using ClickHouse Cloud. Both companies are dedicated to pushing the boundaries of data technology and providing customers with advanced tools for data management and analysis.

Fivetran has introduced a new destination for ClickHouse Cloud, allowing users to quickly load data from over 500 sources. This integration simplifies the process of extracting and loading data by providing robust connectors that automatically adapt to schema and API changes. The destination is designed for use cases where moderate volumes of business data need to be loaded into ClickHouse from applications such as Salesforce, Slack, or Zendesk. It efficiently handles updates and deletes using the ReplacingMergeTree feature in ClickHouse. The code for this implementation is open-sourced under the Apache 2.0 license, and feedback and contributions are welcome.

A new interactive visualization tool has been developed using Automatic Dependent Surveillance-Broadcast (ADS-B) data, which broadcasts various flight data. The website aggregates and visualizes massive amounts of air traffic data hosted in a ClickHouse database. Users can tune the visualizations with custom SQL queries and drill down from 50 billion records to individual data records. This tool offers an entirely new art form and provides stunning imagery, allowing users to follow aircraft around the sky and even filter by manufacturer or airline.

LangSmith, a unified developer platform for LLM application observability and evaluation, has been made Generally Available by LangChain. The platform focuses on two primary challenges users encounter when developing LLM applications: Observability and Evaluation. LangSmith uses ClickHouse as the database to power user experience and ClickHouse Cloud as the service behind their hosted offering. The decision to use ClickHouse was driven by its ability to handle high insert workloads, low latency analytical queries, and its compatibility with all deployment models.

Didi, a globally distributed mobile transportation platform, has successfully migrated its log retrieval from Elasticsearch to ClickHouse, reducing hardware costs by over 30%. The company generates petabyte-level log data daily and needed a storage solution that could handle large data volumes, diverse query scenarios, and high QPS requirements. ClickHouse's distributed architecture, write performance, query performance, and storage cost made it the ideal choice for this migration. After the successful transition, Didi's ClickHouse logging cluster now has over 400 physical nodes, supporting approximately 15 million queries per day with a peak QPS of about 200. The new architecture involves only a single writing pipeline and simplifies operational processes.

ClickHouse, a popular open-source columnar database management system, has expanded its connectivity platform with new beta connectors for Amazon S3 and Google Cloud Storage (GCS). These connectors aim to improve the data loading process by ensuring resiliency against interruptions and offering continuous loading capabilities. The key behind this resilience lies in smart use of ClickHouse's destination service ingest capabilities, orchestration with temporary staging tables, a custom KeeperMap state for tracking progress, and the robust underlying infrastructure of ClickPipes. Currently, the beta connectors support JSON, CSV, TSV, and Parquet formats, as well as public and private buckets with various authentication methods. The platform is expected to evolve further with more updates and enhancements in the future.

The April ClickHouse newsletter highlights various updates and events in the realm of real-time data warehouses. Key points include the release of version 24.3, which includes an enabled analyzer by default for improved query analysis and optimization; a blog post on storing continuous profiling data using ClickHouse by Coroot's Nikolay Sivko; Releem's successful migration from MySQL to ClickHouse, resulting in significant performance improvements; Rory Crispin's experience building a 19 PiB logging platform with ClickHouse and saving millions; Brad Lhotsky's proof-of-concept for a rate limiter using ClickHouse; upcoming events such as meetups and training sessions; video presentations from recent meetups; and updates to the ClickHouse Cloud, including new regions and beta support for continuous data ingestion.

Materialized views in ClickHouse facilitate the transformation and storage of data by automatically executing queries whenever new rows are added to a source table. Initially, two separate materialized views were used to handle raw event data and aggregation states from a Kafka source, but a suggestion was made to chain these views, optimizing the process by having the aggregation state view read from pre-extracted raw events. Using the Wiki recent changes feed as a data source, a detailed setup is provided: a Kafka table engine is created to ingest data, followed by the creation of a raw events table and a materialized view to store extracted data. To enable incremental aggregation, an aggregate state table is defined using unique counts and running totals for users, pages, and updates, with materialized views designed to populate these tables. An innovative approach of chaining views is employed to efficiently process data in one-minute and ten-minute intervals, demonstrating how to backfill and query aggregated data for real-time analytics, thus optimizing performance with increased data volumes.

This article provides a detailed walkthrough on how to perform K-Means clustering using SQL queries with ClickHouse, an open-source columnar database management system. The author explains the theory behind K-Means clustering and demonstrates its implementation in SQL. They also discuss feature selection, choosing the optimal value of K, and visualizing the clusters formed. The article includes a sample dataset from NYC taxis and provides code snippets for performing various operations related to K-Means clustering. The author also compares the performance of their ClickHouse implementation with scikit-learn, a popular machine learning library in Python, on a larger dataset. Overall, this article is an excellent resource for anyone interested in implementing K-Means clustering using SQL queries and provides valuable insights into various aspects of the algorithm.

ClickHouse version 24.3 has been released with new features, performance optimizations, and bug fixes. The Analyzer feature is now enabled by default, providing better compatibility and feature completeness for complex query optimizations. Additionally, the ATTACH PARTITION command allows attaching data from a different/remote disk, improving efficiency when copying databases. S3 Express One Zone Support has also been added, offering lower latency and higher reads per second but at a much higher cost with less availability.

ClickHouse has released a major update to its Cloud service after nine months of rethinking and redesigning. The SQL console is now fully integrated at the top of the navigation menu, with significant UI improvements and performance enhancements. Data ingestion processes have been streamlined, making it easier for users to upload files and manage streaming data. Settings area has been added for common operational actions, and account-level controls are still easily accessible. Both light and dark themes are available in ClickHouse Cloud. The new design system ensures a consistent aesthetic throughout the service.

This blog post explores the use of ClickHouse as a feature store in conjunction with Featureform, an open-source Python library that enables collaboration on machine learning features and their transformations. The author demonstrates how to use SQL queries to perform data transformations and scaling operations before training logistic regression and decision tree models using incremental techniques. The post begins by providing a brief overview of feature stores and their role in the machine learning pipeline, emphasizing the importance of collaboration and reusability when working with large datasets. It then introduces ClickHouse as an ideal candidate for serving features due to its ability to handle complex queries over massive datasets quickly. Next, the author demonstrates how to use Featureform to define entities consisting of a set of features and a class label. These entities are used to register training sets, which can be efficiently iterated using Featureform's APIs. The post also explores how to split these training sets into separate training and validation datasets for model evaluation purposes. The author then proceeds to train logistic regression and decision tree models using incremental techniques such as Stochastic Gradient Descent (SGD) and the Hoeffding Adaptive Tree classifier from the River library. The performance of these models is evaluated using metrics like accuracy, confusion matrix, precision, recall, and F1-score. Finally, the post discusses how Featureform manages state and versioning by tracking lineage through Directed Acyclic Graphs (DAG) and employing techniques similar to tools such as dbt. It also highlights how this allows for collaboration on feature engineering tasks and reduces model iteration time. Overall, this blog post provides a comprehensive overview of using ClickHouse as a feature store with Featureform for training machine learning models. The author demonstrates the effectiveness of SQL-based data transformations and incremental model training techniques while emphasizing the importance of collaboration and reusability in the machine learning pipeline.

In this article, we discussed how ClickHouse was used to build an observability platform that handles over 19 petabytes of data. The architecture and key technical decisions behind the platform were reviewed, including the use of OpenTelemetry for collection and aggregation, a custom Grafana plugin for log exploration, and cross-region querying. We also demonstrated that ClickHouse is at least 200x less expensive than Datadog for an observability workload like ours - the projected cost of Datadog for a 30-day retention period having been a staggering ~$26M per month!