September 2022 Summaries

This blog post continues the series "Getting Data Into ClickHouse" and explores ways to get data into ClickHouse, focusing on using Amazon S3 storage as both a source of data and as a queryable data repository in its own right. The authors provide examples of querying forex data from S3 using ClickHouse for ad-hoc queries where users don't need to access the dataset frequently, and inserting the data into a table to utilize ClickHouse's full capabilities for real-time analytics. They also demonstrate how to visualize forex trading insights using Grafana, including candlestick charts and Bollinger Bands. The post highlights the benefits of using S3 as a source of data for ad-hoc queries and insertion into ClickHouse for real-time analytics, and provides examples of how to optimize query performance and schema configuration for improved results.

ClickHouse has released its 22.8 Long Term Support (LTS) version, which includes several new features such as the Lightweight Deletes feature, Extended date ranges for Date32 and DateTime64 data types, Parallel distributed insert from S3, JSON logging, Infer dates and numbers in schema inference, and more Pretty formats for query output. The new DELETE query allows users to mark rows as deleted without physically deleting them, making it a cost-effective alternative to the previous method. Additionally, ClickHouse has upcoming events such as the v22.9 Release Webinar, Silicon Valley ClickHouse Meetup, and AWS re:Invent, where attendees can meet the team and discuss various topics related to ClickHouse.

At ClickHouse, culture is shaped by its team members' experiences and interactions. The company shares stories from its employees, such as San Tran, an Application Security Engineer who has adapted to a new role within the company, highlighting the importance of teamwork, flexibility, and open communication in overcoming challenges. San emphasizes the need to be proactive and helpful to others, rather than being a "blocker," and shares his approach to providing clear explanations about security topics. He also appreciates the flexible working hours and technology at ClickHouse, which allows him to balance work and family life. Despite the rapidly evolving nature of technology, San finds it challenging to keep up with the latest developments, but is proud to work alongside seasoned developers on exciting projects that push the boundaries of cloud-scale products.

The journey of loading a real-world dataset into ClickHouse involves sampling, preparing, enriching, and optimizing the schema for specific queries. The NOAA Global Historical Climatology Network dataset was used, which contains 1 billion rows of climate data from 1900 to 2022. The dataset was downloaded in compressed format, filtered for relevant measurements, and then loaded into a ClickHouse instance. The data was enriched with additional information such as country names, latitudes, and longitudes using the `clickhouse-local` tool. A dictionary-based query system was implemented to efficiently search for weather events within specific geographical regions, reducing query execution time by orders of magnitude.

The new version 2.0 of the Grafana ClickHouse plugin offers several significant improvements, including a major change in query parsing that should help resolve common issues with ad-hoc filters. The plugin now supports using HTTP as the transport protocol and enhanced support for ClickHouse data types, such as JSON. Additionally, the plugin has been rewritten to use the `additional_table_filters` feature, which allows any filter to be sent with a query as part of the SETTINGS, making it easier to inject filters into queries. The plugin also adds support for rendering JSON columns in charts and improves handling of ClickHouse types within the plugin. Changes have also been made to variables, including the use of `__conditionalAll` to optimize IN conditions. However, this release includes some breaking changes, such as the removal of the AST parser, which may require users to manually optimize certain queries or upgrade their ClickHouse version.

This blog post explores the reading of JSON datasets and the JSON object type in ClickHouse, a popular open-source relational database management system. It demonstrates how ClickHouse can automatically infer column types from JSON data, allowing for flexible schema design. The post showcases the JSONAsObject format, which treats each row as a JSON object, enabling automatic creation of sub-columns with inferred types. This allows for efficient querying and storage of semi-structured data. However, the post also notes limitations, such as the inability to use JSON columns as primary keys, and provides workarounds, including separating structured fields from JSON columns. The experimental nature of this feature is highlighted, and users are encouraged to provide feedback as it moves towards production readiness.