December 2022 Summaries

ClickHouse has added powerful random functions since its 22.10 release, allowing developers to generate realistic test data with high flexibility. These functions include uniform distributions, canonical random, and non-uniform distributions such as normal and Poisson distributions. The random functions can be used to populate tables with test data, generating time-distributed data, values that depend on time, and even multi-modal distributions. Additionally, the functions can be used to generate binary states, random strings, and noisy data, making it possible to simulate real-world data in a controlled environment. By using these functions, developers can create realistic datasets for testing their solutions, reducing the need for manual data generation and increasing the efficiency of development processes.

ClickHouse Cloud provides a set of system tables that can be used to monitor and diagnose issues with INSERT statements, such as asynchronous inserts, synchronous inserts, and parts/partitions. The blog post shares some common SQL queries to benefit the self-managed community, including monitoring insert bulk requests, total written rows, memory and CPU usage for inserts, and parts/partition metrics. These queries can help identify potential issues early on, allowing users to deliver a better ClickHouse experience. By using these queries, users can also gain insights into resource consumption, partitioning, and part creation, which can inform tuning strategies and improve overall performance.

The system tables in ClickHouse provide valuable insights into the internal operations of the server, including database settings, query execution, and system performance. System tables can be used to debug issues, understand cluster usage, and identify potential bottlenecks. The most commonly used system tables include `system.databases`, `system.processes`, `system.query_log`, `system.settings`, `system.part_log`, `system.merges`, and `system.moves`. These tables offer a wealth of information about the server state, including metrics, events, and errors, allowing users to gain a deeper understanding of the system and optimize queries for better performance. By querying these tables using standard SQL syntax, users can retrieve rows from all nodes in a cluster with shards and replicas, providing a comprehensive view of the system's performance and usage.

ClickHouse is an open-source column-oriented OLAP database that excels in analytical queries due to its column-store architecture, whereas PostgreSQL is a relational database focused on extensibility and SQL compliance. The two databases complement each other, with ClickHouse suited for real-time analytics and Postgres ideal for transactional use cases. By leveraging the `postgresql` function or table engine in ClickHouse, users can push queries down to Postgres and exploit its indexes, while also utilizing ClickHouse's MergeTree tables for faster analytical queries. The performance difference between direct execution on Postgres versus using ClickHouse is significant, especially when exploiting primary keys and file system caches. With proper optimization, ClickHouse clearly outperforms PostgreSQL in linear scans and analytical-type queries.

The ClickHouse team has released version 22.12, which includes several significant new features and improvements, such as the introduction of `grace_hashJOINs`, a more efficient join algorithm that reduces memory usage while maintaining performance. Other notable additions include password complexity rules for enhanced security, BSON support for improved data storage and transfer, and a Prometheus endpoint for monitoring ClickHouse Keeper. The release also addresses some functional gaps in ClickHouse, such as the ability to use the `ALL` clause in GROUP BY clauses, making analytical queries simpler and more succinct to write.

The ClickHouse relational database management system is optimized for real-time analytics, allowing fast query execution with minimal tuning. To accelerate queries, utilize primary indexes to minimize disk reads, ensuring the table's rows are stored in a sorted order that can be efficiently searched by index. Properly utilizing primary indexes allows terabytes of data to be skipped and searched in a matter of seconds. Additionally, creating a second table with a different row order based on a different primary key can optimize queries for specific conditions, such as filtering by town. Furthermore, ClickHouse's Projections feature enables the creation of additional tables that automatically keep data in sync with the original table, allowing for incremental aggregation and multiple row orders. Increasing query processing parallelism also speeds up execution time by utilizing more threads to process data in parallel. By applying these techniques, users can optimize performance for specific queries and minimize disk reads, resulting in faster query execution times.

In this post, we'll demonstrate the value of investing time in your ClickHouse schema through strict types and codecs to minimize storage and improve query performance. We've reduced our uncompressed size from 135GB to around 36GB through type optimizations and reduced our compressed size from 4GB to 1.42GB with codecs, providing significant gains on storage and potentially accelerating queries.

ClickHouse Cloud is now generally available and production ready, offering a managed service for users to focus on using ClickHouse rather than managing it. The new features include SOC 2 Type II compliance, uptime status and SLAs, availability in the AWS Marketplace, a lower priced development tier, an interactive SQL console, free ClickHouse Academy training, and support for PostgreSQL and MySQL engines, dictionaries, and SQL UDFs. A new Development tier is available with prices starting at $50 per month, making it affordable for non-production development, staging, and QA environments. Upcoming events include a 6-hour ClickHouse Workshop, a ClickHouse v22.12 Release Webinar, Meetups in Tel Aviv and Seattle, and the release of ClickHouse v22.11 with new features such as composite time intervals, recursive globs in paths, retry keeper during inserts, Hudi and DeltaLake table engines, readonly S3 disks, and object (JSON) inside types. Projections are a convenient alternative to materialized views for querying data from the same table, offering faster query performance by analyzing whether to use original or projection data. A holiday reading list is available with articles on various ClickHouse topics, including JIT compilation, user-defined functions, using dictionaries to accelerate queries, and more.

This post showcases the use of User-Defined Functions (UDFs) in ClickHouse to solve a complex query problem involving file renames. The author demonstrates how to create a recursive UDF, `file_path_history`, to collect the full path history of a file by recursively traversing the rename hierarchy. Despite the limitation that recursion is not currently supported in ClickHouse UDFs, the authors work around this limitation by specifying the function N times, where each function calls another. The resulting query can be used to get the full commit history of a file and solve the original question. The post concludes by highlighting the potential for UDFs to extend the behavior of ClickHouse and encouraging readers to share their own usage of UDFs in ClickHouse Cloud.

In this summary, ClickHouse enables users to improve query performance by utilizing dictionaries in data modeling and optimization. The `stations` table is normalized with a separate table for country codes, states, and locations. A dictionary called `stations_dict` is created using the `stations` table as its source. This dictionary can be used to accelerate queries that require location-based lookups. By leveraging the primary key of the `country_code` column in the `stations_dict`, users can optimize their queries for faster execution times. The example demonstrates how to use dictionaries to improve query performance, especially when dealing with complex data models and large datasets.

Bloomberg, a leading data provider for financial markets, leverages ClickHouse to optimize data ingestion and analysis capabilities. The company's unique challenge is to provide accurate and timely information to institutional investors while managing large datasets. ClickHouse's columnar storage, ease of use, and ability to write complex calculations with simple mathematics make it an excellent choice for Bloomberg's use case. By optimizing data ingestion rather than query speed, the team has efficiently ingested vast amounts of data while ensuring data quality. The company's approach to using buffer tables, creating new tables for changes, and testing disaster recovery simulations demonstrates the flexibility and reliability of ClickHouse in supporting their specific requirements.

ClickHouse Cloud is now Generally Available, offering a serverless architecture and scalable performance that adapts to the needs of your workload. The service provides SOC 2 Type II compliance, uptime SLAs, and is available in the AWS Marketplace with $300 in free credits for trials. ClickHouse Cloud also features Development services, a cloud-native SQL console, and improved integrations with various tools such as Metabase and Tableau. Additionally, the service supports PostgreSQL and MySQL engines, dictionaries, and SQL UDFs, and offers many other improvements and enhancements.

Rokt, a global marketing technology company, faced challenges with their reporting system due to its complexity and limited customer data slicing capabilities. They evaluated several databases, including Elasticsearch, Apache Pinot, Druid, Citus Data, StarRocks, Snowflake, and ClickHouse, before deciding on ClickHouse as the best option. ClickHouse provided better support for group-by capabilities, filtering, and joins, making it easier to query their large dataset of views, clicks, purchases, and events. The company found that ClickHouse was three times less expensive than Redshift without cache, outperformed Redshift in terms of performance, and could handle up to 200 queries per second. Rokt plans to migrate to ClickHouse Cloud for its advantages in backups and data analytics, including solving problems related to replication, sharding, and scalability.

Disney's Observability team uses ClickHouse to process and analyze access logs for their content distribution system, which has a client-server model. This allows them to identify issues like latency and make sense of the data. The team chose ClickHouse over other options due to its simplicity and lightweight architecture, which provides impressive performance with 3 million rows written per second and 2 billion rows read per second. They are planning to upgrade to a larger cluster to improve their data management capabilities. ClickHouse's flexibility and automation features make it easy to monitor various aspects of the system and replace other tools like Influx. The team finds it an ideal option for processing access logs and providing analytics on Disney+'s content distribution system.

ClickHouse has announced the general availability of its cloud-based database offering, ClickHouse Cloud, which simplifies and accelerates insights and analytics for modern digital enterprises. The new service provides a lightning-fast architecture that decouples storage and compute, scaling automatically to accommodate modern workloads. With SOC 2 Type II compliance and uptime SLAs for production workloads, ClickHouse Cloud is production-ready and available in the AWS Marketplace. The company has also acquired Arctype IP and team, enabling the launch of a new SQL console, and has gained over 100 paying customers during its two-month beta phase. This launch includes a host of new product features enhancing security, reliability, and usability. ClickHouse Cloud is designed to enable businesses to access this technology on demand for the first time, allowing them to turn their data into insights in near real-time. The company has received fresh investment from Thrive Capital as an extension to its Series B funding.

The latest ClickHouse release, 22.11, is now available with several new features, including Composite time intervals, support for Glob patterns, functions for Spark compatibility, retries on INSERT, Data Lake support via Apache Hudi and Delta Lake for SELECT queries, and numerous performance improvements. The addition of retries on INSERT allows inserts to survive reconnections to the keeper and potential restarts, while the support for Glob patterns enables users to target specific subsets of data using s3 functions, reducing the amount of data that needs to be read to answer a query. This feature is particularly valuable when reading from local storage or S3 buckets to perform ad-hoc analysis or selectively insert data into ClickHouse instances. The release also includes substantive changes in the python client and updates to both the go and js clients.