October 2022 Summaries

At ClickHouse, we believe that Culture is above all being shaped and nurtured by our colleagues. It’s the way that we interact, work with each other, communicate and help each other learn along the way. Instead of stating what we think our culture should be in principle, we prefer to share our team members’ views and direct experiences of what it’s really like to work at ClickHouse: positives and challenges included. The company values honesty among its colleagues, which is seen as a key aspect of their culture. This allows for the diversity of thought that comes with sharing honest views, which is precious to them. Most employees are distributed across the world, allowing for flexible work arrangements. Niek Lok, a Commercial Account Executive- EMEA, shares his experiences and values, including working in a team that is supportive, compassionate, and caring. He also mentions his passion for personal development, nature, and volunteering to help combat loneliness and depression among younger people.

Fluent Bit is used to collect Kubernetes logs and send them to ClickHouse Cloud for analysis. The process involves deploying Fluent Bit on a Kubernetes cluster, modifying the Helm chart configuration to enrich log messages with namespace and pod information, and creating a table in ClickHouse to store the logs. The logs are then sent to ClickHouse using the HTTP output plugin, where they can be analyzed using SQL queries or visualized using tools like Grafana. The use of Fluent Bit's experimental JSON type allows for dynamic columns to be created automatically, but it is recommended to explicitly define types for expected fields and remove unused fields to minimize storage. Additionally, tuning the flush time for batches can help avoid common issues with high log volumes.

ClickHouse is a complex piece of software that introduces new concepts to users, leading to common issues such as "Too many parts" due to poorly chosen partitioning keys or excessive materialized views, which can slow down inserts. Users should also be aware of the limitations of primary key selection, overuse of data skipping indices, and the behavior of LIMIT clauses in queries. Additionally, ClickHouse has features like memory overcommit capabilities that allow more memory-intensive queries to run when server resources are available, but require careful tuning at a server and user level. Users should also be cautious when using experimental features, as they may not be production-ready or may introduce risks. Overall, managing a ClickHouse cluster in production requires attention to these common pitfalls to avoid performance issues and ensure the speed and power of the software.

The ClickHouse DBMS has been integrated with Rust programming language, allowing for the use of libraries in other compiled languages. A package manager called Cargo was used to download dependencies and compile Rust code, while ClickHouse uses CMake and Ninja build system. The integration process involved implementing a custom CMake function or using an existing utility called Corrosion-rs to simplify the integration. The BLAKE3 hash function was added as an example library, which demonstrated improved performance compared to other hash functions in ClickHouse. However, issues arose during the integration process, including compatibility problems with C++ Memory Sanitizer and multiplatform building. Despite these challenges, the integration was successful, allowing for optional use of Rust libraries and enabling future additions of more libraries, such as BLAKE3, which provided improved speed and security features.

Fluent Bit is a fast, lightweight, and highly scalable logging and metrics processor and forwarder that has solved the challenge of ingesting data from various sources into popular backends like ClickHouse. To set up Fluent Bit to route logs to ClickHouse in under 5 minutes, users need to create a database and tables in ClickHouse, configure Fluent Bit on Ubuntu, specify inputs, filters, and outputs, run Fluent Bit with the configuration file, and search the logs in ClickHouse. The setup allows for ad-hoc queries for analysis without designating any schema, enabling flexible data exploration.

ClickHouse has introduced an official, open-source Node.js client to join its family of language clients, providing blazing-fast performance and ease of use in the Node.js ecosystem. The client was written entirely in TypeScript with strong typing and supports various data input and output formats, as well as a majority of ClickHouse data types. Its primary goal is to assist the community in nurturing an open ecosystem around ClickHouse by formalizing a common specification for language client architecture and functionality, aiming to reduce maintenance and developer onboarding costs through standardization. The client's first versions have been released, and feedback and contributions are welcomed to improve its features and API ergonomics.

This blog post continues its series on visualizing data in ClickHouse by exploring Metabase as a popular tool for business users who are less comfortable with writing SQL. Metabase offers a simpler experience, focusing on fast data exploration and a cleaner interface, but with less breadth of visuals and customizability compared to previous tools. The author demonstrates how to get started with Metabase, creating analytics using the questions feature, which allows users to build simple visualizations quickly without needing to write SQL queries. The tool also offers an SQL editor for more complex visualizations and supports various visualization types, including combo charts, funnel visualizations, and Pin maps. The author concludes that Metabase is a great option for those who want a simpler getting-started experience, but notes that the choice of tool ultimately depends on personal preference and data requirements.

ClickHouse is a single binary that enables fast processing on local and remote files using SQL, effectively providing database features without a database. It supports the full breadth of ClickHouse functions and JSON file format. A benchmark was conducted by Daniel Moura to compare various command-line tools for querying large files, with results showing that clickhouse-local outperformed other tools, including SPySQL, jq, trdsql, Miller, OctoSQL, spqrk-sql, Pandas, and DSQ. The benchmark focused on three challenges: Map, Aggregation/Reduce, and Subset/Filter, which assess the tool's ability to process files quickly. ClickHouse recently introduced features such as semi-structured data storage and automatic schema inference, making it easy to process JSON files and reproduce this benchmark. The updated results show clickhouse-local to be significantly faster than most other tools for querying large JSON files, with OctoSQL excelling on smaller files.

This month, the team at ClickHouse has made significant progress in developing their managed service, ClickHouse Cloud, which is now available in Public Beta. This service simplifies data storage and management by using shared object storage that can be accessed by all ClickHouse nodes, eliminating the need for manual configuration of replication and sharding. ClickHouse Cloud offers a lower price point compared to traditional block storage and provides 24/7/365 monitoring and support. The team has also released new features in version 22.10, including vector search, KeeperMap table engine, automatically selecting quorum, and an official Node.js client. Additionally, the team is sharing tips on how to optimize performance with ClickHouse, such as using the `max_threads` setting correctly.

Apache Superset is an Apache Open Source visualization tool launched in 2017 as a result of an initiative at AirBnB. It offers a rich BI experience, supporting SQL-based data stores and providing users with a wide range of visualization types, including tree maps and box plots. The tool provides a simple getting started experience through docker-compose and can be accessed via http://localhost:8088 after installation. Superset allows users to create virtual "datasets" which can provide a connection to a specific table or capture the results of a query. It enables users to visualize data using various chart types, including line charts, treemaps, and box plots, as well as perform text analysis with word clouds. The tool also offers the ability to combine chart types and combines classical visualization techniques with modern chart types, such as line and bar charts. Superset is highly useful for both technical and business users, providing a simple experience for rapid data investigation and offering advanced features that can be used by power users.

We'd like to welcome Contentsquare as a guest to our blog. At Contentsquare, they used to run their main SaaS application on top of Elasticsearch 5 years ago, when they started migrating all their analytics applications to ClickHouse to improve horizontal scalability, stability, and efficiency. They chose ClickHouse over Elasticsearch because it offered minimal latency for queries, rich query language, fast and efficient performance with spinning disks, and simple deployment and operation. Contentsquare split their migration effort into three phases: building a new product to get familiar with the technology, mirroring existing features with custom tooling, and migrating clients one by one. They built automation and CI/CD tooling for ClickHouse deployments and set up alerting and monitoring. The migration allowed Contentsquare to reduce infrastructure costs by 11 times, improve performance by 10x, and increase data retention periods. Contentsquare made two major adaptations to their architecture: a custom ingestion component and representing queries as abstract syntax trees. They also implemented an optimized ingestion pipeline using Kafka and a clickin component to handle insertions. The query optimizer simplified redundant expressions and reduced slow queries by 10x. Contentsquare's experience highlights the importance of automation, understanding MergeTree engines, and making data in a single shard for efficient scalability.

Grafana is a popular visualization tool used with ClickHouse to visualize data in real-time effortlessly, thanks to the official OSS ClickHouse plugin. It's particularly useful for Observability use cases and offers features like dedicated views for logs and traces. Users can create dashboards, add visual panels, and specify their data source, including ClickHouse. Visualizations require familiarity with SQL concepts but offer a visual query builder to assist with constructing ClickHouse SQL for most chart types. Examples include bar charts, multi-line charts, heatmap, and candlestick visualizations, showcasing the flexibility of Grafana in handling different datasets and use cases.

ClickHouse Cloud is a serverless offering that allows users to leverage the capabilities of ClickHouse without managing server infrastructure, providing a simple and intuitive user interface for provisioning services within minutes. This simplifies the use of ClickHouse for developers, data engineers, and analysts, allowing them to start building instantly without sizing and scaling their cluster. The platform offers flexible scaling, optimized object storage, multi-level caching, improved performance, cost efficiency, and security features such as secure password generation, IP filtering, strong encryption, and activity logging. It also provides a seamless integration with other systems and tools, including data sources, user interfaces, and programming languages. ClickHouse Cloud delivers near parity with a well-tuned self-managed database in terms of query and data load speeds, while offering the best price to performance ratio in the industry through its efficiency gains.