November 2022 Summaries

We are here for the data. Data gives us insights. It helps us tell compelling stories. It drives innovation. We developed ClickHouse Cloud to put the world’s fastest database at your fingertips. To enable you to do your best work without worrying about all the things that come with delivering solid infrastructure. That is why we are excited to announce ClickHouse Cloud is now SOC 2 Type II compliant! SOC 2 Type II is an independent, third-party attestation that confirms our commitment to security, availability, processing integrity, and confidentiality of customer environments. The report provides a plain language description of our environment and specifically designed controls tailored to the best way to run ClickHouse in the cloud. This compliance milestone removes a barrier for companies considering ClickHouse Cloud, allowing them to start trials and take advantage of our free 6-hour training workshop to give their 2023 plans a head start.

JIT (Just-in-time) compilation is a technique used to generate machine code and execute it at runtime, improving performance by compiling dynamic configurations into static ones. In ClickHouse, JIT compilation is used for expression evaluation, aggregate functions, and ORDER BY comparators. The compiler uses LLVM infrastructure to achieve this, with various optimizations and techniques such as fusing multiple aggregate functions into one, reducing virtual function calls, and using SIMD instructions. While JIT compilation has its costs, including compilation time, memory, and maintenance, it can greatly improve performance in many cases, particularly for expressions, aggregations, and ORDER BY comparators, with improvements ranging from 1.15 to 3 times or more depending on the specific use case.

In this post, the authors analyzed data from the ClickHouse Git repository using window and array functions to solve a sequence problem. They created a query that used two powerful features of ClickHouse: window and array functions. The query first grouped commits by author and day, then created a window per author to determine if each commit was consecutive with the previous one. Finally, they used an array function to count the length of sequences of consecutive days for each author, identifying the longest sequence of uninterrupted work. The authors demonstrated how these techniques can be applied to other datasets to find sequential patterns, and concluded that similar techniques can be used to solve sequence problems in ClickHouse.

The `git-import` tool is a distributed tool with ClickHouse that extracts information from Git repositories for analytics purposes. It generates three main tables: commits, file changes, and line changes, which can be loaded into ClickHouse or other DBMS. The generated data includes over 7 million rows of commits, 53 million rows of file changes, and 2.7 GB of line changes, providing a wealth of information for analysis. The tool proposes several questions for users to answer, including the reconstruction of the `git blame` command, which is particularly challenging due to ClickHouse's lack of arrayFold or arrayReduce functions. Users are encouraged to solve this query and others using the generated data, with a t-shirt prize offered for the first correct solution. The tool also provides some tips for users to keep in mind when working with the data, including checking out the same commit for comparison and considering file renames that affect change history.

ClickHouse has simplified its pricing for Cloud, charging only for compute and storage. The company also offers an additional $500 in credits until November 15th. Upcoming events include webinars, onboarding workshops, meetups with talks from ClickHouse users and experts, and the release of new features in ClickHouse 22.10, including support for S3 backups, a reset setting, and improved performance. The company has also introduced an obfuscator to make it easier to share sensitive data without revealing actual values. Additionally, ClickHouse is being compared to other tools such as GraphQL Hive and PostgreSQL, with some companies reporting significant speed improvements when switching to ClickHouse.

The GraphQL Hive team, behind The Guild, was using Elasticsearch as their database solution but faced scalability issues and a steep learning curve due to its query language. They were looking for an alternative that could handle high cardinality, support analytics and aggregation, and be easy to learn and maintain. After researching and testing several databases, they decided to switch to ClickHouse, which offered superior performance, a built-in TTL, and a type system. The migration was done gradually with zero downtime, resulting in a 100x improvement on average compared to their previous data pipeline, allowing them to scale from millions to billions of rows monthly without issues.