July 2023 Summaries

We've built a unified data platform with ClickHouse that merges operational and analytical needs. Data powers every software system, but traditional systems often split into separate analytical and operational systems. We thought hard about this as we started Synq, and we power our entire user-facing application, internal analytics, data reliability monitoring alerts, in-application analytics, and machine learning models from a single platform - ClickHouse. To find the right platform, we set ambitious performance goals for backfilling data, created flexible internal transformations, and built a single platform that could store raw log data and act as a serving layer for most data use cases needed by our applications and APIs. We chose ClickHouse due to its elastic ingest performance, ability to create query-specific data models with Materialised Views, and stable infrastructure. Our ingestion microservice uses the Async API to avoid buffering in application code, and we've settled on the final approach after consultation with the ClickHouse team. With reliable and scalable ingestion in place, we can leverage Materialised Views to optimize performance. We've created specialized tables that transform our raw logs data to a format optimized for our queries, supported by adequate partitioning and ordering keys. Data between raw logs and specialized tables are kept in sync in real-time with materialized views. With dbt, we've enhanced testing of completeness, timeliness, and correctness of data we ingest on behalf of our customers. Our in-app analytics is as fast as any other application part, mainly thanks to optimized ClickHouse tables. We've achieved a lot not even a year into our journey with ClickHouse, and we're excited about the mindset of building modern cloud systems where analytics and operations are wholly integrated.

Ahrefs, a renowned web analytics company, has successfully transitioned to using ClickHouse for its massive data storage needs. Founded in 2010, Ahrefs processes immense volumes of web analytics data and has the world's largest index of live backlinks. Initially, they used custom solutions like Cassandra and Hypertable, but found them inadequate due to limitations such as inflexible query engines and scaling challenges. In 2019, they discovered ClickHouse, which offered superior performance, a SQL interface, and versatile I/O support, making it an ideal choice for their growing datasets. Ahrefs has since embedded ClickHouse deeply into its system, operating multiple clusters on hardware with hundreds of hosts, including geo-replicated main cluster for redundancy and task-specific efficiency. To handle large-scale data insertions, they use buffering techniques and extensive fetch and attach commands to move data efficiently across servers. They have also developed internal monitoring tools like the "Birdseye View Tool" and "Query Analyzer" to optimize query performance. Furthermore, Ahrefs contributed a solution for mark compression in ClickHouse, which eliminated the need for mutexes and reduced memory consumption by three to six times. The transition to ClickHouse has brought significant improvements in data handling and performance, allowing Ahrefs to manage massive data volumes more efficiently, while also enhancing query performance and saving time.

ClickHouse has released version 23.6 with 10 new features, 12 performance optimizations, and 31 bug fixes. This update includes support for MongoDB up to version 6.x, a transform function that can be used to map values of any data type, and an improved query of the month feature called windowFunnel, which allows users to analyze funnel-like behavior in their data. The newsletter also highlights interesting links and upcoming events, including a webinar on ClickHouse v23.7 release, meetups in various locations around the world, and free training sessions for ClickHouse Fundamentals and Cloud Onboarding.

We've simplified a real-time streaming use case using ClickHouse Cloud's new feature, ClickPipes, which reduces architectural complexity and minimizes maintenance overhead. ClickPipes is a fully managed ingestion service that connects to remote Kafka brokers, allowing users to leverage near-real-time data for insights and analytics. By utilizing ClickPipes, we can simplify the architecture used in a previous blog post, providing a more efficient and scalable solution for streaming data into ClickHouse Cloud. With ClickPipes, users can create pipelines with minimal code required, making it easier to integrate data from various sources into their ClickHouse services. The dataset used in this example is an Ethereum blockchain dataset made available by Google, which consists of four tables: Blocks, Transactions, Traces, and Contracts. The architecture uses Kafka Connect connectors to connect the public pub/sub topics to Confluent Cloud's infrastructure, reducing latency and providing a replay capability if required. ClickPipes simplifies this architecture further by allowing users to pull data from Kafka topics directly into their receiving tables, eliminating the need for deploying the Kafka Connector. By using ClickPipes, users can simplify their streaming architectures and take advantage of near-real-time data for insights and analytics without requiring extensive coding knowledge.

ClickHouse, a cloud analytical database provider, has built an internal data warehouse (DWH) using ClickHouse Cloud as the main database, Airflow as a scheduler, and various other tools such as Superset for business intelligence. The DWH stores data from multiple sources, including customer data, financial information, and usage metrics, and uses a hierarchical architecture to manage the data. To ensure security and compliance with regulations, the system implements strict access controls, uses Google groups to control user permissions, and has a process in place for handling sensitive data such as PII and GDPR requirements. While the DWH has been successful in delivering an experience that users love, there are areas for improvement, including introducing a third logical layer to manage complex metrics, integrating Airflow with DBT for additional functionality, and refining naming conventions to improve user understanding. Overall, the system has achieved impressive results, with over 70 monthly active users, hundreds of dashboards, and thousands of charts, and stores approximately 13 TB of data in a compressed format.

Today at ClickHouse, we are delighted to announce the release of ClickPipes for Kafka. This new ClickHouse Cloud experience enables users to simply connect to remote Kafka brokers and start ingesting data into their ClickHouse services right away. By leveraging native capabilities with Confluent, a leading enterprise Kafka provider, ClickPipes unlocks the full potential of ClickHouse Cloud and enables users to leverage near real-time data for insights and analytics. With ClickPipes, users can abstract away complexity by providing a turnkey data ingestion experience that takes less than a minute to set up, allowing them to focus on extracting value from their data. The platform supports Confluent Cloud and Apache Kafka, with plans to expand support for other streaming technologies and data sources in the future.

The text discusses Vantage's transition from Redshift and Postgres to ClickHouse Cloud, a cloud cost optimization platform. Brooke McKim, co-founder and CTO of Vantage, shared their experience of transitioning to ClickHouse, discussing the challenges with their previous architecture, the decision to switch to ClickHouse, and the benefits they've seen since making the move. Vantage initially started with Postgres, which seemed adequate at first but quickly encountered performance issues as they began to scale. They then switched to Redshift, but it presented its own set of challenges, including high cloud costs and limited user experience. The team eventually discovered ClickHouse Cloud, which offered a more cost-effective, high-performance solution that has enabled them to grow and improve their platform. With ClickHouse, Vantage no longer needed to delete and reinsert data for their nightly report generation, reducing operational overhead and cutting costs while maintaining high performance.

ClickHouse, an open-source relational database management system, is being used as a target for automated tests through its new Terraform provider. The goal is to provide a simple, cost-efficient, and isolated testing environment for ClickHouse Cloud services. Terraform allows users to define infrastructure as code using a declarative configuration language. A ClickHouse Cloud service can be provisioned using the Terraform provider with variables such as organization ID, token key, token secret, service name, and service password. The service is ephemeral, meaning it exists only during the test run, reducing costs and complexity. The CI/CD workflow in Github actions provisions a new service for each test run, allowing tests to be isolated from each other and reducing conflicts between resources. This approach enables high-quality clients to be provided to users while minimizing costs and improving observability.

This is an introduction to ClickHouse from Tim, a Developer Advocate at ClickHouse, highlighting the various resources available for learning and getting help with the product. The resources include ClickHouse Academy, documentation, YouTube channel, Subreddit, ClickHouse Community Slack, blog, and community meetups. These resources provide a wealth of free content, including tutorials, videos, quickstarts, and customer stories, as well as opportunities to connect with other users and experts in the field. Tim encourages readers to explore these resources and reach out if they have any questions or want to discuss topics related to ClickHouse.

PostHog has developed an open-source suite of tools called HouseWatch to monitor and manage ClickHouse. PostHog itself is a platform that combines multiple analytics tools into one, providing insights into user behavior through features like session replay and feature flags. The company has optimized its performance by introducing materialized columns and the person overrides table, which have significantly improved query performance. PostHog's HouseWatch tool streamlines ClickHouse administration by offering real-time query monitoring, operations management, and simplified operations features, enabling users to quickly identify and resolve issues, making data analysis faster and more efficient.

The ClickHouse team has released a new version, 23.6, with several exciting features and improvements, including support for MongoDB up to version 6.x, which enables users to query and analyze data from popular NoSQL databases using ClickHouse's table functions. The transform function now supports all data types, including DateTime64 and Decimal, and can handle Null values. Additionally, the team has improved sorting almost sorted data, allowing for better query performance. The release also includes 10 new features, 12 performance optimizations, and 31 bug fixes. A community call is scheduled for July 27th to discuss the upcoming release of version 23.7.