1BRC in SQL with Databend Cloud(<10.4s, ~2.2GB/s from S3)

[BohuTANG]

SQL and read the 1RBC data from AWS S3.

Databend Cloud is a cost-effective cloud warehouse(alternative to Snowflake).

We delve into a practical case where a medium-sized data warehouse, equipped with 2 nodes (each node powered by 16vCPUs, in AWS us-east-2), tackles the challenge of analyzing 1BRC file, which is in parquet within AWS S3( 5.1GB, us-east-2), generated follow the steps similar to Robin Moffatt for generating the raw data and exporting it to a Parquet file.

This is the query(You can run and verified on Databend Cloud):

CREATE STAGE IF NOT EXISTS wizardbend
    URL = 's3://wizardbend/'
    CONNECTION = (ALLOW_ANONYMOUS = 'true');

WITH AGG AS (
    SELECT
        station_name,
        MIN(measurement) AS min_measurement,
        AVG(measurement) AS mean_measurement,
        MAX(measurement) AS max_measurement
    FROM
        @wizardbend/1brc/measurements.parquet
    GROUP BY
        station_name
    ORDER BY
        station_name
)
SELECT
    STRING_AGG(station_name || '=' || CONCAT(min_measurement::STRING, '/', mean_measurement::STRING, '/', max_measurement::STRING) || ', ')
FROM
    AGG;

Reference:
[The One Billion Rows Challenge with Snowflake and Databend]: https://www.databend.com/blog/2024-01-05-1brows/

[BohuTANG]

With Large-sized(64 vCPU) warehouse, the query is 5.5s:

[BohuTANG]

With XLarge-sized(128 vCPU) warehouse, the query is 2.7s:

[abduldjafar]

how about join multiple table with billion rows ? have you test it?

[BohuTANG]

It leverages the join spill feature of Databend, which spills to S3, since the join operation—assuming the join order is correct—requires substantial memory when dealing with billions of rows.