February 2023 Summaries

Google BigQuery and ClickHouse are complementary technologies that can be used together for real-time analytics. ClickHouse excels at delivering sub-second performance on predictable query access patterns, while BigQuery is optimized for ad-hoc querying with low QPS scenarios. To synchronize data between BigQuery and ClickHouse, users can deploy ClickHouse alongside BigQuery to speed up queries or use scheduled queries, Google DataFlow, or manual exports/import operations. ClickHouse storage efficiency is better than BigQuery's, achieving 8x compression, but requires more granular precision in data types and schemas. The authors provide a step-by-step guide on how to load Ethereum blockchain data into ClickHouse from BigQuery, including bulk loading, incremental loading, and handling failures, as well as examples of optimized queries that can be executed on top of the loaded data.

The text discusses the challenges of testing the performance of the ClickHouse database management system by generating realistic test data. The author faced difficulties in creating anonymized and compressed data that preserved the original properties, such as compression ratio, cardinality, and probability distributions. To solve this problem, they explored various approaches, including explicit probabilistic models, neural networks, and Markov models. Ultimately, they developed a tool called clickhouse-obfuscator, which uses random permutations and parametrized Markov models to generate obfuscated data that meets their requirements. The tool is easy to use and can be applied to any database dump, making it a valuable resource for performance testing and optimization of ClickHouse algorithms.

ClickHouse has made its plugin available in Metabase Cloud as part of its commitment to supporting its open-source ecosystem. The integration allows users to build visualizations on large datasets with ease, using a simple drag-and-drop interface and without needing to write SQL. The plugin's journey began as a community project and was later sponsored by ClickHouse after addressing compatibility issues and improving tests and CI. The release in Metabase Cloud is the result of a collaboration between ClickHouse and Metabase, with minimal code review required due to the historical efforts of the community. With the plugin now stable, ClickHouse plans to address minor issues in the coming weeks, while welcoming community feedback and ideas for future development.

The latest release of ClickHouse, version 23.2, is expected to be exciting with upcoming webinars, in-person events, and a new feature called the Query Result Cache. The current release, 23.1, introduced several important features such as Inverted Full Text Indices, Parameterized Views, and Query Results Cache. These features aim to improve performance and provide more flexibility for users. The Query of the Month highlights the use of SAMPLE BY, which allows users to execute queries on a random fraction of data, reducing the need for full table scans and improving performance. The ClickHouse team encourages users to explore these new features and share their experiences with the community.

We recently delivered a webinar with our friends at Grafana, introducing the official ClickHouse Grafana plugin, which was released exactly 10 years after the first Git commit to Grafana. We presented an analysis of the Grafana repository due to frustration over limited GitHub analytics and compared it to the data provided by the ClickHouse OSS OLAP database and data visualization tool. Using public datasets such as GH Archive and generating commit histories with the git-import tool, we built visualizations that aimed to answer questions about the project's history, including the number of commits per day and popular issues. The final dashboard was presented in Grafana's public catalog, and we gathered community feedback on features they would like to see supported in the ClickHouse datasource for Grafana.

To achieve maximum performance, analytical databases optimize every step of their internal data storage and processing pipeline. ClickHouse uses caching extensively to avoid unnecessary work by storing the results of earlier computation or remote data, which is expensive to access. The Query Cache in ClickHouse is a new feature that caches the result of expensive SELECT queries, reducing latency and resource consumption. It stores query results based on their Abstract Syntax Tree (AST) instead of query text, making caching agnostic to upper and lowercase changes. The cache uses a default time-to-live (TTL) of 60 seconds for query results, but this can be specified using the `query_cache_ttl` setting. To enable caching for a query, run it with the `use_query_cache` setting. The Query Cache is transactionally inconsistent by design, tolerating slightly inaccurate query results and avoiding scalability issues that plagued MySQL's query cache. Future improvements include more sophisticated eviction strategies, compression of cache entries, paging on disk, caching of subqueries, and additional configuration settings.

Aggregation function combinators bring almost limitless possibilities to analytical querying on top of any data structure in ClickHouse. We can add conditions to aggregations, apply functions to array elements or get intermediate states to store data in aggregated form but still available for querying.

AWS VPC Flow Logs allow you to capture detailed information about the IP traffic going to and from network interfaces in your VPC. It contains the source and destination IPs, source and destination ports, start and end time, the protocol used, bytes sent and a few other metrics. This data can be useful for debugging security group issues, monitoring ingress and egress traffic as well as checking cross availability zone traffic which helps reduce your cloud bill. ClickHouse is an open-source column-oriented DBMS for online analytical processing that allows users to generate analytical reports using SQL queries in real-time.

ClickHouse is a company that values its culture, which is shaped by its colleagues' interactions, work habits, and experiences. The company's distributed global structure allows employees to connect with each other despite geographical distances. This connection was fostered through the sharing of coffee among colleagues, starting with the company's founder, who discovered a high-quality coffee shop in Rome, Italy, while living abroad. As more employees joined the company, they shared their favorite coffee beans, creating a sense of community and camaraderie within the organization.

Inverted indexes enable fast and powerful searching in vast collections of text documents by compiling a database of terms with pointers to the documents that contain these terms. ClickHouse, an open-source relational database management system, has recently introduced experimental support for inverted indexes, which can significantly improve query performance when used effectively. Inverted indexes are stored as three files: metadata, dictionary, and posting list files, each serving specific purposes in the index construction process. The dictionary file contains a minimized Finite State Transducer (FST) that translates terms to posting list offsets, while the posting list file stores compressed lists of row positions for each term. By utilizing state-of-the-art roaring bitmaps format for compression, ClickHouse can efficiently store and retrieve posting lists, enabling fast disjunctions or conjunctions searches. The experimental support for inverted indexes in ClickHouse has been demonstrated on a real-world dataset, showcasing improved query performance with an index compared to without one.

The dbt (data build tool) is an open-source analytics platform that enables users to transform data in their warehouses by writing select statements. It handles materializing these select statements into objects in the database in the form of tables and views, performing the T of Extract Load and Transform (ELT). dbt allows users to define models representing concepts, which can be combined to produce more complex models, with the boilerplate SQL required to connect models automatically generated. The ClickHouse plugin for dbt supports an adapter plugin API to allow users to connect to data platforms not included in the core offering, and has been developed to support ClickHouse's capabilities. This plugin enables users to utilize dbt's capabilities with ClickHouse, allowing for the creation of views, tables, and incremental models that can be versioned, documented, and tested. The plugin also supports materialization using various methods such as view materialization, table materialization, and incremental materialization. Recent developments in the plugin include improved incremental materialization strategies, including the use of lightweight deletes, which perform significantly better than previous approaches. These improvements enable users to manage data integration tasks as code, track changes to rows in ClickHouse, and migrate data from other OLTP databases to ClickHouse for real-time analytical workloads.