October 2024 Summaries

ClickHouse has introduced sql.clickhouse.com, a new SQL playground featuring over 35 datasets and 220 example queries for users to explore. The platform also includes simple charting capabilities, with plans for improvement, and the ability to save and share queries. Built using NextJS and React, the playground allows users to query ClickHouse with SQL from Javascript, supports syntax highlighting, autocomplete, query parameters, results export, basic charting, and rich sharing features. The platform aims to help users get started with ClickHouse and share their problems. Future improvements will focus on live datasets, a sharing widget, and simplifying the sharing process.

The text discusses a common reference architecture where customers use Postgres and ClickHouse together to address most of their data challenges. Postgres is an open source transactional database, while ClickHouse is an open source analytical database. Both are well-suited for different types of applications. The author mentions the acquisition of PeerDB by ClickHouse, which enables seamless replication of transactional data from Postgres to ClickHouse. Furthermore, a partnership with Supabase, a leading managed service provider for Postgres, is announced. This partnership aims to enable customers to integrate Supabase and ClickHouse more easily, helping them build out their data stack and drastically reduce time-to-market for different applications. The text also discusses the integration of PeerDB into ClickPipes, a native ingestion service of ClickHouse, which will power the Postgres CDC connector in ClickPipes. Lastly, it mentions the support for IPV6 in PeerDB Cloud and clickhouse_fdw extension provided by Supabase to start querying data in ClickHouse from within Postgres.

The Metropolitan Transportation Authority (MTA) has launched an Open Data Challenge for developers and data enthusiasts to create projects using MTA datasets. One of the largest datasets available is the turnstile dataset, which contains information on entry/exit values for turnstiles in New York City over several years. ClickHouse, an OLAP database designed for scale, has made this dataset available in their new playground where users can query the data for free. The text provides a detailed guide on how to load and clean the MTA transit dataset using ClickHouse, including schema improvements, handling cumulative values and outliers, and dealing with missing or inconsistent station names.

The article discusses the implementation of a new JSON data type for ClickHouse, an analytical database known for its speed and efficiency. This new JSON data type addresses several challenges that come with handling JSON data at scale, such as true column-oriented storage, dynamically changing data without type unification, prevention of avalanche of column data files on disk, and dense storage. The Variant and Dynamic types were first introduced as building blocks for the new JSON data type. The Variant type allows efficient storage of values with different data types within the same table column without any unification into a least common type. The Dynamic type can store values of any data type inside a single table column without knowing and having to specify all the types in advance, and it supports limiting the number of types that are stored as separate column data files. The new JSON data type allows for storage of JSON objects with any structure and reading every JSON value from it using the JSON path as a subcolumn. It also supports reading nested JSON objects as subcolumns with type JSON using special syntax JSON_column.^some.path. The article concludes by stating that this new JSON data type is currently released as experimental for testing purposes, and its feature set will be expanded in future updates.

SUMMARY: ClickHouseの新しいJSON型は、非構造化データの保存と操作を可能にします。これにより、ClickHouseがより幅広いアプリケーションで活用できるようになります。新しいJSON型には、動的なデータ変化、スケーラビリティ、チューニング、パフォーマンス向上などの機能が含まれます。

The October ClickHouse newsletter highlights various updates and features in real-time data warehouses. This issue includes first impressions of using ClickHouse for embedded analytics, handling high-volume data pipeline processing with asynchronous updates, the APPEND clause for refreshable materialized views, the pancake SQL pattern, and more. Additionally, it covers upcoming events, such as release calls, workshops, and meetups in various regions. The newsletter also features a community member, Duc-Canh Le, who has contributed significantly to ClickHouse's code base.

Adopting a "client only" architecture for web applications can simplify development and enable faster iteration by reducing the need for complex backend infrastructure and simplified scalability. This approach has been popularized by Firebase, particularly through its Firebase Realtime Database, which introduced browser-based access with security rules that could be managed via tokens, including anonymous authentication tokens. ClickHouse can also be deployed in this architecture with a little configuration, allowing developers to directly query the database from the client side while ensuring the database remains secure and isn't overwhelmed with requests. Key features of ClickHouse that enable it to be used in a client-only architecture include an HTTP interface & REST API, output formats, query parameters, role-based access control, restrictions on query complexity, and quotas.

Embrace is an AI-driven mobile app observability solution provider based on OpenTelemetry (OTel). They use ClickHouse for time series analytics products. One of the key features they offer is sorting app versions according to semantic versioning rules, which are not supported by ClickHouse out of the box. To overcome this limitation, Embrace uses User-Defined Functions (UDFs) introduced in ClickHouse v21.10. They have created a UDF called `sortableSemVer` that converts semantic version strings into arrays of integers for sorting purposes. This function handles complex version strings and can be customized further to suit specific needs.

NOVO Energy, a joint venture between Northvolt and Volvo Cars, discovered ClickHouse at AWS Summit Stockholm in 2023. They were attracted by its speed, scalability, and flexibility. The company's software engineering team uses ClickHouse to democratize access to research data, enabling efficient analysis and comparison across different experiments. Key requirements for the database/store component included ownership and control of stored data, reasonable query performance, low maintenance, and good language support. After evaluating alternatives like TimescaleDB and AWS S3 with Amazon Redshift, NOVO Energy chose ClickHouse Cloud on AWS due to its managed solution offering. The company is currently monitoring performance as the volume of tests and test records grows over time. They also use ClickHouse for log storage from their services.

ClickHouse version 24.9 introduces new features, performance optimizations, and bug fixes. The APPEND clause for refreshable materialized views has been added, allowing new rows to be appended to the end of a table rather than replacing the entire view. Additionally, new functions have been introduced for the JSON data type, and the Variant type can now be returned by automatic schema inference. New contributors are also welcomed in this release, contributing to ClickHouse's growing popularity.

GENIEE, a Tokyo-based ad tech company founded in 2010, has expanded its use of ClickHouse to include data aggregation, leading to faster reporting, improved system stability, and lower costs. The decision was made after facing challenges with their old system that relied on Apache Kafka for streaming data and Apache Flink for data aggregation. GENIEE's new data architecture is centered around two ClickHouse clusters working in tandem to streamline and stabilize their data processing. This has resulted in significant improvements in speed, stability, and scalability, allowing the company to deliver more timely and valuable insights to clients.

GENIEEは、パブリッシャー、広告主、代理店、アプリ開発者の広告戦略を最適化し、広告収益を最大化するための広告技術とデジタルマーケティングソリューションを提供しています。データはKafkaを介してストリーミングされ、ClickHouseで集計され、リアルタイムレポート用に保存されます。このシステムでは、GENIEEは大量の広告パフォーマンスデータを迅速かつ正確に処理できるようになりました。 ClickHouseのMaterialized ViewとSummingMergeTreeエンジンを活用することで、GENIEEはインプレッションやコストのようなメトリクスの集計を完全に自動化しました。これにより、旧システムを悩ませていた手動処理や再集計を避けることができました。新しいClickHouse中心のデータアーキテクチャでは、レポートの生成が数分から数時間まで短縮され、システムの安定性と信頼性も大幅に改善されました。また、コスト効率も高まり、GENIEEはメトリクスの集計を自動化し、エンジニアが戦略的で高価値のあるタスクに集中できるようになりました。

Nansen, a leading blockchain analytics platform, has transitioned its data infrastructure from BigQuery to ClickHouse Cloud for improved performance, cost efficiency, scalability, and streamlined operations. The move has allowed Nansen to run complex queries without racking up high processing fees, handle increasing data volumes, and focus more on developing new features and improving their analytics platform. With ClickHouse Cloud's exceptional speed and real-time streaming capabilities, Nansen continues to empower crypto investors with detailed insights and analytics in the ever-evolving blockchain ecosystem.

The text discusses an integration between Perspective, a high-performance data analytics and visualization tool, and ClickHouse, an open-source columnar OLAP database management system. It explains how the two can be used together to handle real-time and streaming datasets efficiently. The author demonstrates this by building a simple demo application that provides rich visualization capabilities by streaming Forex data using Apache Arrow straight to the browser. They also discuss some limitations of ClickHouse's ability to handle streaming data and suggest future improvements.

Lens Protocol is an open social protocol built on the blockchain, providing users with ownership and control over their profile, social graph, and data. The platform relies heavily on machine learning for feed ranking algorithms and bot detection mechanisms. Initially using Postgres and then Rockset to manage its data needs, Lens faced challenges such as data ingestion delays and query concurrency limits. To overcome these issues, the team migrated to ClickHouse Cloud, which has significantly improved their data operations by enabling near-instantaneous data processing and handling a high volume of concurrent queries without performance degradation. The migration to ClickHouse has provided Lens with scalability and confidence in its ability to support future growth and development of social products.