Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use ClickHouse to store time series data", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use ClickHouse to store time series data.

Brief introduction

We need to store structured time series data, the time interval is 5 minutes or 1 minute, calculate 95 peak, 995 peak, maximum value and other indicators, and display in the web page.

MySQL

At the beginning of the project development, in order to rapidly develop the prototype and verify the product, we used MySQL as the storage of the whole project. The problem is that the time series database scope analysis query takes a long time, and it takes 30 slots to calculate the 30-day data, which is intolerable. Even when creating indexes and using BitInt to store timestamps, there is almost no performance improvement.

Later, other colleagues in our group said that ClickHouse would be used to store time series data, so we started the journey of replacement.

ClickHouse

ClickHouse is a column database oriented to OLAP (online Analytical processing) and compatible with SQL standards. The main disadvantage is that it does not support transactions. So we are not currently migrating the entire storage to ClickHouse, but only storing the timing data.

I thought the replacement process would be very troublesome, and it might modify a lot of code and logic, but it was actually very fast, because the logical design of the interface before was very reasonable, so I only replaced the database ORM library, from gorm to sqlx, which took 1 day (I will talk nonsense about the early reconstruction logic for a week).

More importantly, ClickHouse provides many aggregate functions. In the past, two queries were needed to calculate 95 values, but now only one query is needed. The corresponding SQL is as follows:

Select d.en_name, max (d.in_value) as peak_in

Max (d.out_value) as peak_out, max (d.max_value) as peak_max

QuantileExact 0.95 (d.out_value) as peak_95

QuantileExact (0.995) (d.out_value) as peak_995

QuantileExact (0.999) (d.out_value) as peak_999

From table_value d where d.record_time > = '2020-01-01 00 and d.record_time

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.