March 2023 Summaries

The OpenTelemetry project is a vendor-neutral open-source framework for collecting, transforming, and sending Observability data to a backend, such as ClickHouse. ClickHouse is used for storing Observability data, including logs, metrics, and traces. This blog post focuses on using OpenTelemetry to collect trace data for storage in ClickHouse. It explores how trace data can be collected, stored, and queried in ClickHouse, and provides examples of instrumentation libraries, OTEL Collector configurations, and schema optimizations. The collector accepts data from Observability sources, processes this data, and exports it to the target backend. The exporter uses a community contribution and is supported through a Docker image. The blog post also discusses compression rates, query complexity, materialized views, and future work for the project.

In recognition of the talented women who joined ClickHouse in its early days, this blog post highlights the stories of successful women who helped shape the company. Dorota Szeremeta, VP of Operations, was instrumental in creating the structure and processes required to bring the business to life from scratch. With relentless optimism and problem-solving skills, she led her team with energy and positivity, ensuring smooth operations across the organization. Three months later, Anne Krechmer joined as the first recruiter, bringing smart and approachable style to the hiring process, while also setting up processes for the People team. Claire Lucas, a versatile operations expert, contributed to multiple areas of the company, while Shavoyne McCowan provided responsive Executive Assistant support to the CEO and President. Lauren Ausmus, Director of Finance, worked tirelessly to analyse key data, ensure finance operations ran smoothly, and advise leadership with professionalism and a sense of humor. The stories of these women demonstrate the importance of sharing their experiences to inspire other women in tech careers.

ClickHouse, Inc. creators of ClickHouse online analytical processing (OLAP) database, and Alibaba Cloud have announced a partnership that will enable Alibaba Cloud to offer ClickHouse as an enterprise, first-party service on its platform in mainland China. This partnership is exclusive between the two companies and aims to provide unmatched performance and cost efficiency for modern enterprises. ClickHouse is known for its blazing fast speed with peak processing performance standing at more than two terabytes per second, allowing users to query billions of rows in milliseconds. Alibaba Cloud provides a comprehensive suite of cloud computing services and has been named a Leader in the Gartner Magic Quadrant for Cloud Database Management Systems for the third consecutive year. The new enterprise ClickHouse service on Alibaba Cloud will be powered by the proprietary cloud native engine developed by ClickHouse, Inc., offering exclusive serverless capabilities that greatly reduce getting-started steps for building analytical applications.

Amazon Redshift is a cloud data warehouse that provides reporting and analytics capabilities for structured and semi-structured data. It was designed to handle analytical workloads on big data sets using column-oriented database principles similar to ClickHouse. While attractive to existing AWS users due to its tight integration with the Amazon ecosystem, Redshift users who adopt it to power real-time analytics applications find themselves in need of a more optimized solution for this purpose. As a result, they increasingly turn to ClickHouse to benefit from superior query performance and data compression. Redshift differs from ClickHouse in terms of its engine optimization for data warehousing workloads requiring complex reporting and analytical queries. However, ClickHouse achieves lower query latencies, including for varied query patterns, under high concurrency and while subjected to streaming inserts. It also places much higher limits on concurrent queries, which is vital for real-time application experiences. Additionally, ClickHouse offers superior data compression, allowing users to reduce their total storage (and thus cost) or persist more data at the same cost and derive more real-time insights from their data. Users appreciate ClickHouse for its wide-ranging support of real-time analytical capabilities, such as large range of specialized analytical functions designed to shorten and simplify query syntax, SQL query syntax designed to make analytical queries easier, superior data types support, file and data formats support, federated querying capabilities, secondary indexes & projections, etc. Redshift deployment options include serverless and provisioned instances, each with strengths and weaknesses for different workloads. The Ethereum dataset, which is not offered by AWS, can be generated using the excellent Ethereum ETL tooling or downloaded from a public bucket. ClickHouse vs Redshift storage efficiency comparison shows that ClickHouse compresses data more efficiently than the optimal Redshift schema, resulting in a combined rate of 2x for this dataset. Benchmarks compare query performance between ClickHouse and Redshift, with ClickHouse Cloud node completing queries in less time than a comparative Redshift cluster. Migrating Redshift tables to ClickHouse involves mapping equivalent ClickHouse types for each Redshift type, handling data types, compression, sorting keys, and primary key concepts. The approach of exporting data from Redshift to S3 using the UNLOAD command and then importing it into ClickHouse has limitations, such as relying on the latest timestamp in ClickHouse and potentially causing delays between export and import. Using AWS Lambda or an external script can help mitigate these issues by running periodically after exports are completed. Furthermore, ClickHouse provides superior data compression, allowing users to reduce their total storage (and thus cost) or persist more data at the same cost and derive more real-time insights from their data. ClickHouse vs Redshift query comparison shows that ClickHouse completes queries in less time than Redshift for various queries, such as Ethereum gas used by week and total Ethereum market capitalization. In conclusion, moving data to ClickHouse from Redshift can accelerate queries for real-time analytics, and leveraging ClickHouse for real-time analytics on top of this data can provide superior performance and insights.

We've been busy at ClickHouse, releasing new software, sharing internals, and hosting events. The latest version, v23.2, includes 18 new features, 30 performance optimizations, and 43 bug fixes. A notable feature is the introduction of multi-stage PREWHERE, which reduces the number of rows a query needs to read. Another significant update is support for Apache Iceberg, a popular data lake format. Additionally, ClickHouse now supports computing correlation matrices and has released a query of the month showcasing its capabilities in real-time analytics. The newsletter also features articles on building ClickHouse Cloud, women who inspire the community, fintech leader Juspay's use case, handling updates and deletes, and upcoming events.

We built ClickHouse Cloud from scratch in under a year, leveraging open-source tools and prioritizing simplicity of use, scalability, reliability, and security. We designed a "shared everything" architecture with separated storage and compute, using object storage as the primary store and local SSDs for caching and metadata. Our Control Plane runs on AWS, but we aim to deploy the Data Plane across all major cloud providers. We used Kubernetes for compute infrastructure, managed Kubernetes services for ease of management, Cilium for network isolation, and SQS for message broker communication. ClickHouse Cloud provides a usage-based pricing model with metering and billing engine, and we use Segment for product analytics and Apache Superset for visualization. We learned that cloud is not truly elastic, reliability and security are features too, automating everything is crucial, setting aggressive goals is essential, focusing on time to market is key, and listening to users is vital.

This is my summary: As a Customer and Partner Marketing Manager at ClickHouse, I have been working in the tech industry for 18 years, transitioning from programming to customer-facing roles, including sales and customer success, before joining the startup in November 2021. I enjoy the challenges of working for a new company with no processes in place, helping to lay the foundation while ensuring scalability. I find inspiration in the leadership of women in tech, particularly our VP of Product, Tanya Bragin, who has made a significant impact on the company's direction and success. Balancing work and family life is crucial, and I appreciate the flexibility offered by working for a distributed company, allowing me to balance social contact with remote work. Despite challenges, I strive to maintain a positive attitude and prioritize my well-being during stressful moments.

Juspay, an Indian fintech company, leverages ClickHouse for its end-to-end payment solutions and real-time merchant dashboards, handling over 50 million daily transactions. With a mission to streamline online payments, Juspay needed a solution that could provide monitoring and analytics services to ensure seamless transaction environments. ClickHouse was chosen for its ability to handle large volumes of data and power A/B testing and monitoring, enabling data-driven decisions and frequent releases without impacting the payment system. The platform's advanced data processing capabilities and efficient cost structure made it an attractive choice, achieving a 10x cost reduction in operating expenses compared to BigQuery. By optimizing its infrastructure and following best practices, Juspay maximized the benefits of ClickHouse, providing real-time analytics and dashboards for merchants while reducing costs.

ClickHouse, the world's fastest database for real-time analytics, offers multiple ways to efficiently update and delete data in analytical environments, depending on the specific use case. Lightweight Deletes via the DELETE FROM syntax are the most efficient way to remove data from ClickHouse, but may not provide immediate disk space savings if deleted data "exists" on disk. Mutation-based deletes via ALTER...DELETE can be used when immediate disk space savings are required, such as for compliance purposes. Updates and deletes can also be performed using ALTER...UPDATE and ALTER...DELETE mutations, respectively, which are asynchronous by default but can be made synchronous with the mutations_sync parameter set to 1 or 2. Other methods include using TTLs (time-to-live) for regular data removal, CollapsingMergeTree for frequent updates or removal of individual rows, ReplacingMergeTree for upsert operations based on versioning, and dropping partitions when removing large blocks of data regularly. Additionally, creating new columns and dropping old ones can be a more efficient way to update entire tables.

The latest release of ClickHouse brings several exciting updates, including 18 new features, 30 performance optimizations, and 43 bug fixes. One notable feature is the introduction of support for Apache Iceberg, a high-performance table format that allows for efficient querying and management of data in data lakes. This feature enables schema evolution, automatic partitioning, and metadata management, making it easier to work with large datasets. Additionally, ClickHouse now supports computing correlation matrices, which can help summarize large datasets and identify strongly correlated columns. The release also includes support for Amazon MSK, a fully compatible Kafka Connect Sink for ClickHouse, and the GA release of the Metabase plugin for ClickHouse. These updates demonstrate ClickHouse's commitment to supporting its open-source ecosystem and providing users with powerful tools for data analysis and visualization.

This International Women's Day, ClickHouse is celebrating the inspirational women in their lives by recognizing and appreciating the accomplishments of talented women in their open-source community, software development teams, and on their board. The company has decided to launch a series of posts over the next four weeks to acknowledge the contributions of these remarkable women. He Wen Ting, a contributor to ClickHouse's open-source community, is being highlighted for her valuable additions to the technology, including the Map Data Type that helps store unstructured data in a table. Two team members, Kseniia and Smita, are also recognized for their recent contributions to ClickHouse, including introducing features such as filesystem cache and parameterized views. Additionally, Roopa Tangirala, the leader of the Cloud team, is being acknowledged for her vision and leadership in making ClickHouse Cloud a reality, while Caryn Marooney, a board member, is recognized for her extensive experience and guidance in helping ClickHouse grow and succeed. By celebrating these women's achievements, ClickHouse aims to foster a culture free from gender bias and promote equality in the industry.

ClickHouse is an open-source column-oriented DBMS that supports all standard SQL JOIN types and provides additional specialized joins for analytical queries and time-series analysis. The supported join types include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, LEFT SEMI JOIN, RIGHT SEMI JOIN, INNER ANY JOIN, and ASOF JOIN. Each join type has its own behavior and use cases, and ClickHouse adapts classical join algorithms to its query pipeline for optimal execution. The blog post demonstrates the different join types using example queries and datasets, providing insights into how ClickHouse can be used to optimize analytical queries and time-series analytics.