October 2023 Summaries

ClickHouse allows users to perform statistical analysis and data science tasks without the need for programming languages like Python or R. By utilizing ClickHouse's built-in machine learning functions, such as stochasticLinearRegression and evalMLMethod, users can train models directly within the database, reducing the amount of code required and enabling faster processing of larger datasets. This approach is particularly useful for forecasting tasks, where it can capture time-varying patterns and seasonality with ease. The example demonstrates how to create a forecasting model using ClickHouse's ML functions, which can be used to generate forecasts beyond the last date in the dataset, providing real-time possibilities for data analysis and analytics.

The integration of ChatGPT APIs into Cloud's SQL console allows users to provide context to queries and improve accuracy, making it a valuable tool for data analysts, engineers, and database administrators. Users of all skill levels can benefit from this feature, which lowers the barrier to writing queries against ClickHouse and improves productivity for experienced experts. The query assistant can generate valid queries based on user prompts, providing a basis for complex questions or syntax that may be forgotten. While not perfect, the service can still provide helpful suggestions and correct errors, making it an interesting development in the field of Generative AI and Large Language Models.

ClickHouse 23.9 release features a wide range of improvements and new capabilities, including type inference for JSON data, which allows users to automatically infer the schema from structured data without manual definition. This feature is particularly useful for users with well-structured JSON data that is predictable in nature. Additionally, ClickHouse now supports the GCD codec, which can significantly improve compression on decimal values by reducing their scale and increasing the opportunity for other codecs like Delta to further compress the data. The release also includes a new authentication method using SSH keys, allowing users to authenticate without entering a password each time they connect to a server. Furthermore, ClickHouse is introducing workload scheduling, which enables users to isolate query workloads and define resource limits to minimize their impact on other queries or business-critical applications. These features aim to improve the overall usability, performance, and reliability of ClickHouse, making it an attractive option for data engineers and database administrators.

To achieve maximum ingestion performance with ClickHouse, it's essential to choose the right gear (insert block size) and acceleration level (number of parallel insert threads) based on the available horsepower (CPU cores and RAM). The ideal combination depends on trade-offs between these factors, including resource contention and background merge threads. By experimenting with different settings and analyzing performance metrics, it's possible to find a sweet spot that maximizes ingestion speed. A formula can be used to calculate the optimal settings for top speed, which involves choosing half of available CPU cores for insert threads and an intended peak memory usage. Additionally, ClickHouse Cloud's SharedMergeTree table engine and seamless cluster scaling enable linear scalability, allowing large data inserts to be run as fast as required by adding more servers or CPU cores.

ClickHouse Cloud optimized their Kubernetes and EKS (Elastic Kubernetes Service) setup to reduce costs by improving pod allocation and increasing resource utilization. They analyzed CPU/Memory utilization in their EKS cluster nodes, identified the root cause of low utilization due to the LeastAllocated scoring policy favoring sparse distribution of pods on cluster nodes. They then explored alternative solutions, including tuning cluster autoscaler and overprovisioning, but ultimately chose to change the kube-scheduler scoring policy from LeastAllocated to MostAllocated to pack their clusters more efficiently. This solution implemented the bin-packing paradigm for their pods, favoring nodes with higher utilization ratios, reducing total cost. They set up a custom scheduler in their Kubernetes cluster using the most-allocated scoring policy and ensured high availability by defining three pods with leader election enabled. The new setup resulted in a 20-30% increase in EKS cluster resource utilization and significant cost savings on EC2 instances, with an estimated reduction of over $10 million annually.

ClickHouse has introduced compatibility with third-party business intelligence tools and data visualization platforms through its MySQL interface in ClickHouse Cloud. This allows users to leverage the performance and versatility of ClickHouse while using tools like Superset, Metabase, and Grafana. The feature is now available for use with Google Looker Studio and Tableau online, with support for Amazon QuickSight under active development. Users can enable the MySQL interface in their ClickHouse Cloud service and connect it to these tools, providing fast and meaningful dashboards and reports. The compatibility was achieved through extensive testing and addressing numerous issues, with plans to continue evaluating and improving the feature based on user feedback.

The ClickHouse team has made significant efforts to improve compatibility with the MySQL protocol, enabling users to connect their favorite business intelligence (BI) tools, such as Looker Studio and Tableau online, to ClickHouse without modifying their existing code. This achievement was made possible through collaboration between multiple teams within ClickHouse, including the integrations and core teams, as well as contributions from the wider ClickHouse community. The improvements include support for various MySQL functions, such as SHOW COLUMNS, NULL safe equal, MAKEDATE, STR_TO_DATE, REGEXP, INSTR(str,substr), TO_DAYS, DATE_FORMAT, and Prepared Statements. These enhancements allow users to write queries with a familiar syntax while still benefiting from ClickHouse's analytical functions and efficient query execution. The team also acknowledges the challenges of supporting MySQL in their Istio Proxy layer and provides guidance on when to use the MySQL interface versus native ClickHouse integrations.

Vectorization is an optimization technique where data is processed using vector operations instead of scalar operations, allowing modern CPUs to take advantage of SIMD instructions. The compiler can automatically vectorize certain loops, but manual vectorization or designing SIMD-oriented algorithms are also possible. However, this can compromise portability if targeting older hardware. Runtime CPU dispatching allows for dynamic selection of the most performant implementation based on the available instruction set, which can improve performance by compiling parts of code multiple times for different architectures. Performance tests and configuring compilers with various options can help identify places to optimize, and applying CPU dispatch manually or using a framework like ClickHouse's can lead to significant improvements in certain cases, such as aggregate functions like sum and avg, and unary functions like roundDuration.