1BRC in ClickHouse SQL (<7.5 Seconds)

[YunfanZhang42]

-- Change to your number of physical CPU threads for the best performance.
SET max_insert_threads = 28;
SET max_threads = 28;

DROP TABLE IF EXISTS weather_measurements;

CREATE TABLE weather_measurements (
    location String,
    temperature Float32
) ENGINE = Memory;

SET format_csv_delimiter = ';';

INSERT INTO weather_measurements
SELECT * FROM file('measurements.txt', 'CSV', 'location String, temperature Float32');

SELECT 
    concat('{', arrayStringConcat(groupArray(formatted_result), ', '), '}') AS final_output
FROM (
    SELECT 
        format('{}={}/{}/{}', location, toDecimalString(min(temperature), 1), toDecimalString(avg(temperature), 1), toDecimalString(max(temperature), 1)) AS formatted_result
    FROM weather_measurements
    GROUP BY location
    ORDER BY location
)

Did not check the exact output format, but should be close. Finishes at 7.192s on my i7-14700K + 64GB RAM machine on a hot run, while the baseline Java solution takes ~90s. Most of the time is spent on ingesting the text file. Contributions and fixes welcome!

[jbuchberger]

Awsome. Really like, how concise and readable this is!

[qoega]

For more complete picture you can also show if approach without Memory table works faster or slower on your hardware:

-- Change to your number of physical CPU threads for the best performance.
SET max_insert_threads = 28;
SET max_threads = 28;
SET format_csv_delimiter = ';';

SELECT 
    concat('{', arrayStringConcat(groupArray(formatted_result), ', '), '}') AS final_output
FROM (
    SELECT 
        format('{}={}/{}/{}', location, toDecimalString(min(temperature), 1), toDecimalString(avg(temperature), 1), toDecimalString(max(temperature), 1)) AS formatted_result
    FROM file('measurements.txt', 'CSV', 'location String, temperature Float32');
    GROUP BY location
    ORDER BY location
)

[YunfanZhang42]

@qoega I ran the query without the memory table and it seems the query indeed became faster. I am getting around 6.7s on a hot run so there is a small improvement. Maybe ClickHouse has different query plans if the data do not need to persist?

-- Change to your number of physical CPU threads for the best performance.
SET max_insert_threads = 28;
SET max_threads = 28;
SET format_csv_delimiter = ';';

SELECT 
    concat('{', arrayStringConcat(groupArray(formatted_result), ', '), '}') AS final_output
FROM (
    SELECT 
        format('{}={}/{}/{}', location, toDecimalString(min(temperature), 1), toDecimalString(avg(temperature), 1), toDecimalString(max(temperature), 1)) AS formatted_result
    FROM file('measurements.txt', 'CSV', 'location String, temperature Float32')
    GROUP BY location
    ORDER BY location
)

[qoega]

I think it is just less work as persisting blocks in memory table requires at least some work. Plus I think it will copy memory more times vs just processing parsed block.
Memory table does not have per column compression as MergeTree table does, so sometimes MergeTree show even faster results vs Memory.

[Alex-Cheng]

The top 1 is amazing, why Java is faster?