November 2023 Summaries

The text discusses the use of ClickHouse, a database management system, to perform various operations on a dataset. The author starts by importing the NYC taxis dataset and creating a table in ClickHouse using the MergeTree engine. They then demonstrate how to dynamically select columns from the table using regular expressions, apply functions to all or specific columns, replace values in certain columns while leaving others unchanged, and exclude columns from selection. These clauses enable efficient data manipulation without having to manually specify every column of interest.

ClickHouse and Superset are utilized to supercharge website analytics by allowing for fast and flexible querying of raw data from Google Analytics at a minimal cost. A natural language interface is explored using Large Language Models (LLMs) and Retrieval-Augmented Generation (RAG) techniques, which can simplify the application interface for less technical users. The goal is to enable users to ask questions in natural language and receive accurate SQL responses that ultimately answer their question with underlying data. The LLM model used is Amazon's `titan-embed-text-v1`, while the RAG technique involves combining the power of pre-trained language models with information retrieval systems. The system aims to provide a more accessible means of exploring Google Analytics data, particularly for users who may not be familiar with SQL or technical aspects of analytics. By utilizing a natural language interface and providing context through examples and schema, the system can help users refine their questions and generate accurate queries. However, challenges remain in terms of prompt engineering and ensuring that the generated SQL is accurate and relevant to the user's query. The system has shown promising results but requires further refinement to improve accuracy and performance, particularly for more complex join queries or sub-filtering. To address these challenges, exploring lighter-weight models, refining existing models, and developing a test harness with diverse example problems are necessary next steps.

Google Analytics is used by most marketing functions for statistical reporting, but it has limitations such as flexibility, performance issues, and sampling problems. The authors of the blog post moved their Google Analytics data to ClickHouse, a real-time data warehouse supporting SQL, which provided query flexibility, fast analytics, and infinite retention. They achieved this using BigQuery's export feature to transfer data to ClickHouse Cloud, and implemented various optimizations, such as materialized views, projections, and dictionaries, to improve performance and scalability. The solution costs less than $20 a month and can store around 110 years of data for a site the size of clickhouse.com, making it an attractive alternative to Google Analytics for organizations with large datasets.

The Python library chDB enables users to query Pandas DataFrames using an OLAP database like ClickHouse, taking advantage of its query engine and SQL support. With chDB, users can join DataFrames together, aggregate data, and export results back to Pandas. The library is available via PyPI and requires installation of additional libraries such as Pandas and PyArrow. Users can explore various examples, including querying individual DataFrames, joining them together, and exporting results to Pandas DataFrames. Additionally, chDB allows users to query tables using SQL and convert tables back to Pandas DataFrames for further analysis.

ClickHouse Cloud Development tier provides a cost-efficient logging solution with compression rates over 10x, allowing users to store large volumes of log data without incurring high costs. The development tier offers a limited instance type that can comfortably host over 1.5TiB of log data and compresses it down to under 100GiB. With a fully parallelized query execution engine and low-level optimizations, query performance remains under 1s for most typical SRE queries on these volumes. The cost analysis shows that the storage cost is minimal, thanks to the usage of object storage in ClickHouse cloud, and users can retain data for longer periods without accumulating high costs. The CGW stack presents a cost-effective alternative that can easily scale to multi-terabyte scales without hurting costs, offering predictability and advantage over traditional solutions like Datadog and Elastic Cloud.

ClickHouse has released several exciting features in version 23.10, including 23 new features, 26 performance optimizations, and 60 bug fixes. One notable feature is the introduction of support for NumPy files, which allows users to load large datasets such as the LAION dataset into ClickHouse without having to write custom Python code. The largest triangle three buckets algorithm has also been added, which can be used to downsample data for easier visualization. Additionally, ClickHouse now supports a new array function called `arrayFold`, which provides a powerful way to process arrays in a concise and declarative manner. This feature is demonstrated through an example where it is used to reconstruct the "git blame" command. The LAION dataset has been successfully loaded into ClickHouse using this new feature, allowing users to easily query and analyze the data.

ClickHouse is a distributed database designed for OLAP applications that has gained the author's conviction as the de-facto standard due to its high write throughput, fast aggregations, and simplicity of operation. The database's "bottoms-up" approach allows it to take full advantage of modern hardware capabilities. It uses the Log Structured Merge Tree (LSM Tree), invented in 1991 by Patrick O'Neil, which is also used by prominent databases like Bigtable, HBase, LevelDB, Apache Cassandra, and InfluxDB. ClickHouse integrates many optimizations that make it efficient on hardware, including SIMD instructions and CPU-cache efficient data structures. The database is easy to learn and use, with a simple packaging that makes it familiar to people having experience with relational/SQL databases. The author's experience using ClickHouse has shown its power in aggregating HTTP analytics, building metrics and analytics for Segment's Protocols product, and providing instant value to companies at all maturity levels.

The cloud data warehouse era is coming to an end as companies move away from a "one-size-fits-all" approach and towards real-time data warehouses that can handle interactive, data-driven applications. Traditional data warehouses are not optimized for real-time analytics, leading to poor performance, high costs, and low query concurrency. Real-time data warehouses address these challenges by providing a converged data platform optimized for running data-intensive interactive applications serving both internal and external audiences. The unbundling of the cloud data warehouse is a trend that will continue as organizations move towards a more vendor-neutral data lake approach over time. ClickHouse Cloud is an example of a real-time data warehouse that outperforms traditional data warehouses by orders of magnitude in real-time applications, with features such as turnkey continuous data loading, continuously-updating materialized views, and millisecond performance on top of billions of rows.

Loading trillions of rows into ClickHouse can be challenging due to transient issues like network glitches that can interrupt and stop the data load, leading to delays and potential failures. To address this challenge, ClickHouse Cloud offers ClickPipes, a managed integration solution with built-in support for continuous, fast, resilient, and scalable data ingestion from external systems such as Apache Kafka. For external data sources not supported by ClickPipes, ClickLoad is a script that can be used to load large datasets incrementally and reliably over time by utilizing object storage buckets and a stateful orchestration of the data transfer with automatic retries. The script uses a queue-worker approach to parallelize the file load process, ensuring efficient scalability and reliability in loading trillions of rows into ClickHouse tables, including support for projections, materialized views, and partitioning keys.