August 2024 Summaries

ClickPy is a free service built on ClickHouse that enables real-time analytics on Python Package Index (PyPi) package downloads. The underlying data for projects and downloads is available in BigQuery, but it takes several hours to export the data. Therefore, the data was exported into Google Cloud Storage buckets as Parquet files. The service has been live for around 9 months, with the main table in the database exceeding 1 trillion rows. ClickPy's frontend is written in Next.JS and React, allowing users to explore their favorite packages.

ClickHouse Cloud offers $300 in credits and volume-based discounts for users interested in its services. The author has been working on an embedded version of ClickHouse called chDB, which runs in-process, for almost two years. In this blog post, the author shares performance improvements made to chDB over the last few months. Initially, chDB was designed with simplicity in mind and required serialization, deserialization, and memory copying, leading to unsatisfactory performance. To improve efficiency, a Python table engine was introduced, allowing users to run SQL on DataFrame variables as tables. The chDB Python Table Engine is relatively simple but faced challenges related to the Global Interpreter Lock (GIL) and Object Reference Counting in CPython. Performance optimizations were made by reducing overhead, minimizing CPython API function calls, batching data copying, and rewriting Python string encoding and decoding logic in C++. These improvements led to a significant performance leap for Q23, reducing the time from 8.6 seconds to 0.56 seconds—a 15x improvement. Comparisons with DuckDB showed that chDB outperforms it when querying DataFrames containing 10 million rows of ClickBench data. The latest version of chDB, v2.0.2, introduces a mechanism for users to define their own table-returning logic using Python and includes an API for querying APIs that return JSON arrays as ClickHouse tables.

The text discusses how to model data in ClickHouse after replicating it from Postgres using PeerDB. It highlights that ClickHouse and Postgres differ in their data modeling due to being purpose-built databases optimized for specific workloads. The guide covers essential data modeling concepts in ClickHouse, such as replacing merge tree table engine, handling duplicates, nullable columns, data types, ordering key, primary key vs ordering key, modifying the ordering key, and handling DELETEs. It also mentions that more advanced topics will be covered in future blog posts.

This paragraph summarizes the key points of the text, focusing on the main topic of migrating data from PostgreSQL to ClickHouse. The text discusses various aspects of data modeling, including the differences between PostgreSQL and ClickHouse, how to optimize data replication, and best practices for creating efficient queries. It also touches on topics such as nullable columns, data types, ordering keys, and row-level policies. Overall, the text provides a comprehensive overview of the challenges and opportunities involved in migrating from PostgreSQL to ClickHouse.

The August ClickHouse newsletter highlights various updates and events related to real-time data warehouses. Key points include Alexey Milovidov, the creator and CTO of ClickHouse, embarking on a global tour delivering technical talks. Additionally, the newsletter features an upcoming PeerDB collaboration with ClickHouse, downsampling time series data improvements, join performance enhancements in the 24.7 release, and more. The newsletter also includes information about community members, upcoming events, and quick reads related to ClickHouse.

Beehiiv, a newsletter platform founded by three Morning Brew engineers, has transitioned its data operations from Postgres to ClickHouse Cloud, resulting in significant improvements in efficiency, scalability, and overall system reliability. The move was driven by the need for a more scalable solution as the platform grew, with occasional performance issues and increasing engineering resources required for managing data demands on Postgres. ClickHouse emerged as the ideal choice due to its unmatched performance as an OLAP database, excelling at handling billions of rows with lightning-fast query speeds. The new architecture is built around a distributed, microservices-based approach focused on scalability and efficiency, leveraging Kafka for data flow management in real-time. The transition has brought benefits across the organization, allowing engineers to focus on feature development rather than managing scalability issues, while business leaders and product managers now have direct access to raw data for generating their own metrics without depending on engineers. The shift has also laid the groundwork for future machine learning initiatives such as fraud detection, spam filtering, and ad network optimization. With ClickHouse at its core, beehiiv is well-positioned to lead in the newsletter industry, providing creators with advanced analytics capabilities and empowering them to build and monetize their email audiences effectively.

LoopMe is a company that uses AI-powered ad tech solutions to help brands and agencies improve the performance of their advertising campaigns. The company's adoption of ClickHouse as its database management solution has allowed it to manage large datasets more efficiently, scalably, and cost-effectively. This has enabled LoopMe to focus on building innovative ad tech solutions that provide precise insights into consumer behavior and brand campaign impact. By using AI in their ad tech products, LoopMe helps brands tailor their advertising strategies to resonate with different audiences, leading to greater brand awareness and measurable results. The move to ClickHouse Cloud has further optimized data handling processes for LoopMe, allowing them to run a leaner, more cost-effective operation while maintaining high levels of performance and efficiency.

Vimeo, a digital video platform, has adopted ClickHouse as its primary database management system after struggling with slow query performance and frequent outages on its existing OLTP infrastructure built on Apache HBase and Apache Phoenix. After thorough evaluation, ClickHouse emerged as the frontrunner due to its reputation for handling large-scale data analytics with lightning-fast speed and efficiency. The implementation of ClickHouse has led to significant improvements in query performance, cost efficiency, and overall system reliability at Vimeo.

Azur Games migrated a 120 TB database to ClickHouse Cloud on AWS without causing any downtime in their analytics, achieving significant storage reliability and operational reliability improvements. The migration process took three months of active work from two engineers and one month of the author's time. Despite a slight increase in maintenance costs, the benefits gained more than compensated for this, as it freed up half the time of two engineers, reduced stress, and allowed them to focus on creation and more interesting tasks. The company also experienced faster completion times for various business tasks and improved overall system performance with minimal effort.

Coinhall is an omnichain trading terminal that provides real-time price charts and aggregates swap data from 23 different blockchain networks. Founded in 2021, the platform offers comprehensive trading tools and the best possible swap rates across decentralized exchanges. To manage vast amounts of blockchain data from multiple sources, Coinhall requires top-tier performance and scalability. Speed enables real-time analytics and ensures users can make timely trading decisions, while cost-efficiency keeps operating costs low, helping the company stay competitive, allocate resources effectively, and support future growth. Coinhall co-founder and CTO Aaron Choo led a transformation of Coinhall’s data architecture in 2021, transitioning from BigQuery to ClickHouse due to performance and cost issues. After evaluating various options, ClickHouse was chosen for its superior performance, particularly in handling candlestick queries, latest-price queries, and ASOF JOIN queries. Additionally, self-hosting ClickHouse proved to be the most cost-effective option compared to other managed services. Since implementing ClickHouse, Coinhall has made several optimizations aimed at boosting speed and efficiency, including performance tuning, efficient data retrieval, and managing complex joins. The team plans to continue to optimize and expand their use of ClickHouse, leveraging even more of its features to add new trading tools, improve the user experience, and position Coinhall for growth as a data platform for years to come.

The text discusses recent updates to enhance the speed, stability, and security of replicating data from Postgres to ClickHouse. PeerDB, a company specializing in Postgres CDC, was acquired last month and is actively being integrated into ClickPipes as a source connector. Efficient flushing of the replication slot, better memory handling on ClickHouse, row-level transformations, and improved security measures are among the key features released to improve the replication experience. PeerDB has been supporting multiple customers in real-time data replication from Postgres to ClickHouse, with billions of records being processed.

This is a summary of the text about Azur Games' migration to ClickHouse Cloud. The company realized that their existing technology solutions were no longer meeting their growing needs, and they needed a more reliable and scalable infrastructure. They considered using AWS as an alternative, but ultimately chose to migrate to ClickHouse Cloud due to its cost-effectiveness and ease of use. The team faced several challenges during the migration process, including data transfer issues and technical difficulties with MinIO. However, they were able to overcome these obstacles and successfully complete the migration within 3 months. The new infrastructure has improved the company's reliability and scalability, allowing them to focus on more strategic tasks. The team was able to leverage ClickHouse Cloud's features, such as its ability to automatically scale up or down, to improve their data processing performance. Overall, the migration was a success, and the company is now better equipped to handle its growing needs.

ClickHouse, an open-source columnar database management system, has partnered with AWS Activate to provide startups with the tools they need for effective data analysis. The collaboration aims to help startups turn their massive volumes of data into actionable insights while maintaining agility and cost-efficiency. With features such as unmatched performance, cost-efficiency, scalability, ease of use, and a supportive community, ClickHouse Cloud is designed to empower startups in their growth journey.

ClickHouse version 24.7 introduces new features and optimizations to improve performance and fix bugs. The release includes 18 new features, 12 performance optimizations, and 76 bug fixes. Notable improvements include optimizations for reading in order, faster parallel hash join, ASOF JOIN with the full sorting merge join algorithm, percent_rank function, and automatic named tuples. The update also welcomes several new contributors to the ClickHouse community.

This tutorial demonstrates how to use ClickHouse as an offline feature store for machine learning models, focusing on data modeling techniques that can be used to accelerate pipelines and enable the fast building of features over potentially billions of rows. The tutorial covers the following steps: 1. Selecting a subset of data for feature engineering. 2. Creating feature tables using ClickHouse's materialized views. 3. Generating model data by joining and aligning features. 4. Generating test and training sets from the model data. 5. Using ClickHouse as an online store to serve precomputed features during inference time. By following these steps, users can build a scalable feature store that enables efficient feature engineering and accelerates machine learning pipelines.

This is a summary of the provided text in one paragraph: The article discusses using ClickHouse as an offline feature store and data transformer. It covers various aspects, including why ClickHouse is chosen, how to create a feature store, and how to generate model-ready data. The article also provides examples of creating a feature store, generating features, and merging them with the original data. Additionally, it discusses strategies for improving performance and scalability, such as using materialized views and aggregating data. Finally, it touches on the importance of selecting the right approach depending on the specific use case, whether it's building from scratch, using an existing feature store, or leveraging a combination of both.

CryptoHouse is a free blockchain analytics service powered by ClickHouse that offers real-time analytics, democratizing access by enabling instant query responses. Users can use SQL to query this data, which is updated in real-time, thanks to Goldsky. The custom UI allows for saving and sharing queries and basic charting, with examples to get users started. As of today, users of CryptoHouse can query Solana blocks, transactions, token_transfers, block_rewards, accounts, and tokens for free. Similar datasets are available for Ethereum. The service aims to provide a public platform for real-time analytics on popular blockchains while addressing the challenges of data engineering and ClickHouse usage quotas.

Cognitiv, an AI company specializing in real-time bidding on advertising opportunities, has successfully implemented ClickHouse to improve its data management and processing capabilities. The integration of ClickHouse has allowed Cognitiv's data team to develop more advanced machine learning models, solidifying the company's position as a leader and innovator in programmatic ad buying. By transitioning to ClickHouse Cloud, Cognitiv has streamlined its operations and reduced costs while maintaining high performance even as data volumes increase. The company plans to continue exploring ClickHouse's features and functionalities to optimize their data strategy further.

Ibis is an open-source data frame library that facilitates the interaction with various data systems using a Pythonic interface, supporting over 20 backends, including ClickHouse. The library is part of the composable data ecosystem, using SQL as an intermediate representation to enhance compatibility with different backends. The blog post demonstrates how to use Ibis with ClickHouse, including installation instructions and a practical example of importing and exploring the nycflights13_flights dataset. Users can perform complex queries and data manipulations using Ibis, such as calculating average delays across airports or determining the destinations with the most incoming flights. Ibis expressions are evaluated lazily, allowing for flexible data operations and integrations with existing ClickHouse tables, such as analyzing UK house prices from a hosted dataset. The post suggests potential future exploration of Ibis ML, a new feature designed to enhance machine learning capabilities with ClickHouse data.