January 2024 Summaries

ClickHouse, an analytics database, has been used with new tools such as ClickHouse Local and chDB to analyze data in a more efficient manner. The author of the article uses these tools to query GitHub metrics alongside PyPi package download data from ClickHouse Cloud, achieving a hybrid query execution that combines local and remote data sources. This allows for faster performance and more accurate results compared to traditional querying methods. The example showcases how to join data from ClickHouse Cloud with ClickHouse Local using remoteSecure table functions, enabling users to explore new use cases for this analytics database.

Trackingplan is a comprehensive testing solution for web, iOS, Android, and backend analytics that powers ClickHouse, an analytical database capable of serving dynamic queries on the full data. The company initially used DynamoDB but recognized its limitations in handling complex queries and data inconsistencies. After evaluating alternatives, Trackingplan chose ClickHouse due to its unparalleled performance, flexible querying capabilities, and ability to answer unplanned queries. The adoption of ClickHouse simplified tracking plan's development process, allowing them to focus on optimizing queries using Materialized Views and leveraging the scaling API of ClickHouse Cloud to ensure compliance with local privacy regulations. With a growing 30 Billion rows in ClickHouse, Trackingplan continues to leverage the database's capabilities to improve their product offerings and expand into new use cases, including historical analysis.

A Java program was written by Decodable to solve a challenge of retrieving temperature measurement values from a 1 billion row text file and calculating the min, mean, and max temperature per weather station. To do this, they used ClickHouse Local, a local binary version of ClickHouse that can be used for ad hoc data analysis. The program read the data from a CSV file using the `file` function in ClickHouse Local, then grouped the results by city and calculated the min, mean, and max temperature per city using SQL queries. They also explored alternative approaches, including loading the data into a table before running the query, but found that this did not offer significant performance benefits. The final program used a custom-separated output format to get the desired result in 19 seconds, which is comparable to the Java baseline on the same hardware.

A feature store is a centralized repository for storing and managing feature data, providing a consistent view of features for training and inference across development to production environments. It aims to improve collaboration and reusability between data scientists and engineers by centralizing features and their transformation logic, reducing model iteration time, governance, and compliance through rules and versioning, and improving model performance and reliability by abstracting complexity from data engineering. ClickHouse can be used to power a feature store by acting as a data source, transformation engine, offline store, online store, and vector database, simplifying the architecture and allowing features to be built and deployed faster with superior performance and reduced management overhead. The integration of ClickHouse with Featureform enables a virtual feature store that leverages the strengths of both systems to provide an efficient and scalable solution for MLOps workflows.

ClickHouse version 23.12 has been released, bringing 21 new features, 18 performance optimizations, and 37 bug fixes to the popular open-source relational database management system. The release includes a significant feature for Refreshable Materialized Views, which allows users to create views that periodically run queries and update target tables atomically. This feature can be used to solve complex data processing problems efficiently. Additionally, ClickHouse has optimized its performance with improved vectorization using SIMD instructions, faster aggregation, and a new PASTE JOIN feature. The release also includes improvements for integrations with PowerBI, Metabase, dbt, Apache Beam, and Kafka connectors.

ClickHouse provides SQL-based observability of itself through system tables that continuously write metrics, logs, traces, and other data into these tables. The `system.query_log`, `system.metrics`, `system.asynchronous_insert_log`, and `system.part_log` tables contain relevant information for monitoring asynchronous inserts. Several queries are provided to introspect the execution stages of asynchronous inserts, including part creations, buffer flushes, insert errors during buffer flushes, and pending flushes. These queries can be used to identify root causes of issues with asynchronous insert configurations, such as incorrect flush thresholds or partitioning key usage, and to optimize performance. The queries are parameterized views that can be configured with various parameters to suit specific use cases. By using these queries, developers can gain a deeper understanding of their ClickHouse cluster's behavior and make data-driven decisions to improve its performance.