January 2023 Summaries

The `TTL` clause in ClickHouse allows for automatic data management tasks such as deleting expired data, compaction, and movement between different storage types. By configuring the `TTL` statement at a table level, users can manage their data lifecycle more efficiently. The `TTL` clause supports various options including managing background removal, filtering which rows to delete, specifying multiple delete conditions, moving data to historical tables, compacting historical data using aggregations, changing compression levels, and column-level TTL. Additionally, ClickHouse provides storage management features such as disk policies to manage storage and offers the option of using ClickHouse Cloud for a reliable solution to scaling.

ClickHouse was chosen by TrillaBit over Apache Solr and Snowflake for its fast performance, low cost, and ease of use. TrillaBit utilizes ClickHouse's table engine features to connect to data from different stores and forms, and the MergeTree family provides useful features for storing and managing data. The platform also uses Airbyte for client integrations and JSON data ingestion. As TrillaBit grows, it is moving to ClickHouse Cloud for cluster management and security benefits. With ClickHouse, TrillaBit offers an end-to-end platform that allows users to ask questions and get immediate visual answers, with features such as metadata-driven exploration, drill-down capabilities, and export options. The platform also provides multi-tenant capabilities, scalability, and security features.

ClickHouse has released version 23.1, featuring 17 new features, 17 performance optimizations, and 78 bug fixes. The release includes experimental support for Inverted Full Text Indices, which can speed up token matching queries. This feature is still experimental and has limitations, such as not storing positions of terms and no relevancy calculation. Additionally, ClickHouse introduces Parametized Views, allowing users to create dynamic views based on parameters passed at query time. The release also includes a new Query Result Cache, which can cache the results of expensive SELECT queries, reducing latency and resource consumption.

The text discusses the integration of Postgres with ClickHouse, highlighting how data can be moved between these two databases using native ClickHouse functions and table engines. The post explores accelerating queries involving joins by powering dictionaries with Postgres, which enables fast lookups and avoids the need for a join in ClickHouse. It also demonstrates a "reverse ETL" process where results of analytical queries are pushed back to Postgres from ClickHouse, allowing for efficient data consumption in user-facing applications. The key points include using PostgreSQL-backed dictionary in ClickHouse to accelerate joins, utilizing the "postgres function" and table engine, and performing a reverse ETL operation to push query results back to Postgres.

ClickHouse supports various data formats for easy integration into any working pipeline. It provides tools for standard text formats such as CSV, JSON, and TSV, as well as custom formats like regular expressions and binary formats. The database also allows importing and exporting data in native formats, Apache formats including Parquet, SQL dumps, and specialized formats like Null format for performance testing. Additionally, ClickHouse offers features to prettify command line output, making it easier to perceive results from complex queries.

We have a modern enterprise with numerous security technologies, making it difficult to manage the vast amount of data generated by these tools. Traditional SIEMs struggle to handle this complexity due to immutable time series event data and lack of normalization, leading to performance issues and difficulties in querying the data. To overcome these challenges, Dassana, a security data lake, uses ClickHouse as its backbone, leveraging its flexibility, scalability, and cost-effectiveness. By utilizing advanced features like table engines, asynchronous inserts, and dictionaries, ClickHouse enables Dassana to normalize and query the disparate data sources, providing contextualized insights and simplifying data access at scale.

ClickHouse Fiddle is a new open-source SQL playground created specifically for ClickHouse, allowing users to run SQL queries in an arbitrary version of ClickHouse and share results via unique links. It provides a fast and easily accessible way to execute SQL queries from a browser, helping in various cases such as checking bug reproducibility, testing new SQL constructs, sharing snippets with execution results, and more. The platform's design features a simple web application, HTTP API, Docker containers, load balancing, and priority given to runners with pulled images to optimize latency. It has limitations compared to existing platforms, but its unique approach provides isolation via ephemeral containers, reducing resources required for running the whole platform.

ClickHouse is a popular open-source relational database management system that has recently released version 22.12 with several new features, including `grace hash join`, `async insert deduplication`, and `password complexity`. The new release also includes improvements to encryption, such as built-in encryption and decryption capabilities, which allow users to protect sensitive data. Additionally, ClickHouse is partnering with dbt Labs and Grafana for joint webinars in January 2023 to help users explore the database's capabilities. Users can stay up-to-date with the latest news and events by subscribing to the ClickHouse newsletter and registering for upcoming virtual events. The blog post also mentions the importance of monitoring ClickHouse system tables for detailed information about almost everything ClickHouse is doing, as well as essential monitoring queries to get started.

Materialized views are a powerful tool in ClickHouse that can improve query performance and extend data management capabilities by storing the result of a SELECT query on data as it is inserted into a target table. This allows for faster retrieval of aggregated data without having to execute complex queries every time. Materialized views can be used to accelerate queries, filter out invalid data, route data to different tables based on certain conditions, and even perform transformations on data. They can also be used with JOINs, but this can significantly downgrade insert performance. By using materialized views, developers can optimize their data management workflows and improve the overall performance of their applications.

Materialized ViewはClickHouseでデータを保存し、クエリの高速化に役立つツールです。 Materialized Viewを作成することで、特定のデータがソーステーブルに挿入されるタイミングで、そのデータに対するSELECTクエリ結果をターゲットテーブルに保存します。これにより、複雑なクエリを簡単に高速化できます。また、Materialized ViewはJOINもサポートし、ソーステーブルへの挿入をトリガーとして動作します。ただし、大きなテーブル同士のJOINを使用するとパフォーマンスが低下する可能性があります。 Materialized Viewはクエリ結果に基づいて保存されるため、ClickHouseの豊富な関数を使ってデータを自由に変換できます。 また、Materialized Viewはソーステーブルとターゲットテーブルを結びつけることでデータを複数のテーブルに振り分けます。 Materialized Viewを作成する際には、ソーステーブルへの書き込みを一時停止し、Dataを反映し、ソーステーブルへの書き込みを再開します。 また、Materialized ViewはJOIN先など外部のテーブルの挿入はトリガーされないため、JOINに限らず、他テーブルへのIN SELECTなどでも同様です。 Materialized ViewはClickHouseでクエリパフォーマンスを向上させたり、データ管理機能を拡張したりするうえで非常に強力なツールであることを示しています。

The WebXT team from Microsoft presented two of their analytics products, Titan and Microsoft Clarity, using ClickHouse at a community meetup on January 18th, 2023. The team showcased how they can analyze petabytes of data in seconds and create custom dashboards with just a few clicks, thanks to the high-performance capabilities of ClickHouse. The key goal for Titan was to provide self-service analytics with response times within seconds, allowing employees to interactively slice and dice data and save it as dashboards. The team chose ClickHouse as the data engine due to its speed, cost-effectiveness, and inbuilt telemetry features, which made monitoring data usage and storage more accessible. The presentation also covered optimization techniques for ClickHouse, including a near real-time pipeline and query optimizers, which resulted in significant improvements in query performance. Overall, the event highlighted the importance of self-serve analytics in Microsoft's success and demonstrated how ClickHouse can support such initiatives.

The Microsoft Clarity team presented their use of ClickHouse at a community meetup on January 18th, 2023. They showcased two of their analytics products, Microsoft Clarity and Titan, which utilize ClickHouse for efficient data handling and analysis. The presentation highlighted the architecture of Clarity, its ability to handle petabytes of data and billions of page views daily, as well as its features such as session recordings, heat maps, and GDPR-compliance. The team also discussed challenges with backup and recovery on Azure, which they overcame using ClickHouse's Incremental Snapshots feature. Microsoft's Senior Director of Data and Analytics expressed enthusiasm for ClickHouse, looking forward to continued collaboration in the future.

The post explains how to set up a Windows security stack using ClickHouse, Grafana, Fluent Bit, and Sysmon. It focuses on advanced log analysis for Microsoft Windows, leveraging the capabilities of these tools to identify malicious or anomalous activity. The authors provide step-by-step instructions on deploying Fluent Bit for Windows Event log collection, configuring Sysmon, collecting and processing logs with Fluent Bit, and visualizing data using Grafana. They also demonstrate testing stability and performance using simulation tools like SysmonSimulator and large-scale event ingestion. By following these steps, users can create a comprehensive security stack that efficiently stores and analyzes Windows Event Logs, providing valuable insights into potential threats.

The ClickHouse Cloud team has announced the beta release of an open-source Kafka Connect Sink for ClickHouse, a new official connector that allows users to push data from Apache Kafka into ClickHouse with exactly-once delivery semantics. The connector is designed to address the limitations of existing solutions, which typically offer at-least-once delivery and do not support all ClickHouse types. The new connector exploits the insert deduplication features of ClickHouse, using a state machine to formulate consistent batches for insertion and providing guaranteed deduplication in case of failure. It also leverages ClickHouse Keeper for strongly consistent storage, which is ideal for persisting small data quantities when highly consistent storage is required. The connector is designed to be architecturally simple, with minimal dependencies, and can be used with the Kafka Connect framework, making it a valuable addition to the ClickHouse ecosystem.

The text discusses the use of ClickHouse as a high-performance OLAP database for storing Observability data, specifically logs, metrics, and traces. The authors explore how to collect, store, query, and visualize these logs using various agents such as Open Telemetry Collector, Vector, Fluent Bit, and Fluentd. They provide examples of each agent's configuration and schema design, highlighting the importance of choosing the right stack for interoperability and performance. The text also touches on topics like compression, query optimization, and visualization tools, recommending Grafana for visualizing log data using the official ClickHouse plugin. Overall, the post provides a comprehensive guide to storing and querying logs in ClickHouse, with a focus on scalability, performance, and ease of use.

I can generate a neutral and interesting summary of the provided text in 1 paragraph: ClickHouse is a powerful tool for storing and processing time-series data efficiently. It allows users to query and analyze data with ease, making it an ideal solution for various use cases such as tracking website visitors or monitoring CPU load. The database provides various features like aggregating data by periods, filling empty groups, rolling time windows, counting counters, gauges, and histograms. To optimize storage efficiency, ClickHouse uses optimal data types, codecs, and materialized views. Additionally, the database is scalable to Petabytes while maintaining performance and simplicity, making it suitable for large-scale applications. By leveraging ClickHouse's capabilities, users can efficiently manage time-series data without requiring additional extensions or tools.

We have switched from BigQuery to ClickHouse to aggregate royalties data from disparate revenue streams in order to provide critical financial insights for our members, enabling them to make better decisions that drive their careers forward. We ingest a large amount of royalty data and needed a fast open-source column-oriented database management system that could handle this volume of data quickly. ClickHouse meets these requirements, processing billions of rows and tens of gigabytes of data per server per second, allowing us to load the data in under a few seconds. The speed of ClickHouse also benefits from calculating internal metrics such as total royalties and indicators like total royalties paid out in the last year. However, operations like joins can be challenging due to differences with traditional relational database management systems. We overcame these challenges by using a join engine table and optimizing our queries. We still use PostgreSQL for non-royalty data but encountered issues with LEFT joining from PG data to a big ClickHouse table until we found an optimized solution. Additionally, we faced library issues with the NodeJS client for ClickHouse which exposed a bug that resulted in empty strings being inserted into the table even when the actual raw value was NULL. We have since updated the code to store NULLs correctly.

ClickHouse development team provides a solution to collect data from GitHub Actions using GitHub Webhooks and store it in ClickHouse for analysis. The necessary tools include GitHub Webhooks, which can be used to receive workflow job events in JSON format. An AWS Lambda function can be used to receive and insert data into a storage layer like ClickHouse. In ClickHouse, a table needs to be created to receive the events, and queries can be run to analyze the data. The result includes various metrics such as number of jobs queued or waiting, total time spent in the queue by the time of day, and current ClickHouse queue size for self-managed GitHub Runners.

clickhouse-local is a tool that allows users to analyze and transform data in local or remote files using the power of SQL and ClickHouse functions, without needing to deploy a full database server or write custom Python code. It supports various input and output formats, including CSV, Parquet, JSON, and BSON, and can work with large files, compressions, and random data generation. clickhouse-local also inherits ClickHouse's ability to communicate with external data sources like MySQL, Postgres, and MongoDB via functions or table engines. The tool is designed for data scientists and engineers working with data in various scenarios, providing a productive way to analyze and transform data without the need for extensive IT infrastructure. With its high-performance capabilities, clickhouse-local can handle complex SQL queries on local files, making it an ideal solution for data analysis and transformation tasks.

This blog post provides several ways to monitor and troubleshoot SELECT queries in ClickHouse, including global overviews of the cluster setup, monitoring expensive queries, comparing metrics between two queries, and performing a deep dive into query execution. It also discusses troubleshooting common issues such as TOO_MANY_SIMULTANEOUS_QUERIES, stuck or failed queries, and provides guidance on using the ClickHouse SQL-console to inspect query execution details. The post recommends proactively monitoring query performance and behavior, and suggests tools like Grafana for alerting and visualization. Overall, this post aims to help users better understand their SELECT queries, identify performance issues, and improve query optimization.