Achieving Real-time Analytics with ChistaDATA’s ClickHouse

Introduction: Achieving Real-time Analytics

Once upon a time, in a rapidly evolving digital landscape, businesses faced the challenge of delivering real-time analytics to gain valuable insights and stay ahead of the competition. One company, ChistaDATA, emerged as a global leader in developing high-performance real-time analytics solutions, revolutionising the way organisations harnessed the power of their data.

In the vast ocean of data, companies struggled to navigate through the complexities, longing for a solution that could unlock the true potential of their information. ChistaDATA, with its innovative approach and cutting-edge technology, became the guiding light for these enterprises.

Just like a skilled captain guiding a ship through treacherous waters, ChistaDATA provided businesses with a comprehensive suite of real-time analytics tools, enabling them to make informed decisions with lightning speed. With their expertise, ChistaDATA helped companies overcome the challenges of traditional analytics by empowering them with timely insights and actionable intelligence.

In another instance, a telecommunications giant sought to enhance its network performance and deliver uninterrupted services to its customers. With ChistaDATA’s real-time analytics solution, the company gained a holistic view of its network infrastructure, monitoring every aspect in real-time. Armed with this knowledge, they could proactively identify and resolve network issues, ensuring a seamless experience for their customers. The company’s reputation soared, attracting new customers and solidifying their position as an industry leader.

ChistaDATA’s impact extended beyond specific industries. They partnered with organizations across sectors, including finance, healthcare, and manufacturing, among others, helping them unlock the true potential of their data and achieve remarkable results. The power of real-time analytics transformed businesses, enabling them to adapt swiftly to market changes, identify emerging trends, and make data-driven decisions that shaped their future success.

As the world continued to evolve, ChistaDATA remained at the forefront of innovation, constantly pushing the boundaries of what was possible. Their commitment to delivering high-performance real-time analytics solutions fueled their clients’ success and positioned ChistaDATA as a trusted advisor in the realm of data analytics.

A Worked Example: Achieving Real-time Analytics on Ethereum Blockchain Data

Stories are one thing; a proof of concept with real numbers is another. In one of our POCs we used ClickHouse to process Ethereum transaction data at a scale where the public RPC endpoints, with their per-call limits, could not be queried directly for analytics. The pipeline we built is simple to describe: pull blocks and receipts over the Ethereum JSON-RPC interface, land them in Amazon S3 as files, and ingest from S3 into ClickHouse, where the aggregation, dashboarding and feature extraction for ML models actually happen. Achieving real-time analytics on chain data then becomes a ClickHouse problem rather than an RPC-throttling problem.

How big the tables got

The first thing we check on any large ingest is the on-disk footprint and the compression ratio, straight from system.columns:

SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed_size,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE database = 'default'
  AND table IN ('blocks_data_all', 'receipts_json_local_untuple')
GROUP BY table;

┌─table───────────────────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ blocks_data_all             │ 389.59 GiB      │ 5.02 TiB          │ 13.21 │
│ receipts_json_local_untuple │ 100.32 GiB      │ 1.40 TiB          │ 14.32 │
└─────────────────────────────┴─────────────────┴───────────────────┴───────┘

Roughly 6.4 TiB of raw chain data compressed to under 500 GiB, a 13 to 14x ratio, which is typical for the repetitive hex strings and small integers that make up block and receipt records. These figures come from that POC on ClickHouse 23.4; ratios on the same data with today’s default codecs are similar.

The join that ran out of memory

Blocks and receipts have to be joined to produce one denormalised row per transaction. Both sides are large, and the first attempt at an INNER JOIN across the two tables died within seconds:

SELECT a.*, b.*
FROM blocks_data_all AS a
INNER JOIN receipts_json_local_untuple AS b
    ON  a.blockHash        = b.logs_blockHash
    AND a.blockNumber      = b.blockNumber
    AND a.from             = b.from
    AND a.to               = b.to
    AND a.transactionIndex = b.transactionIndex;

-- Progress: 12.73 million rows, 19.04 GB (2.48 million rows/s.) (5.5 CPU, 34.53 GB RAM) 1%
-- Code: 241. DB::Exception: Memory limit (total) exceeded: would use 27.73 GiB
--   (attempt to allocate chunk of 12330048 bytes), maximum: 27.52 GiB.
--   OvercommitTracker decision: Query was selected to stop by OvercommitTracker.
--   (MEMORY_LIMIT_EXCEEDED)

The default hash join builds the entire right-hand table in RAM before it probes a single left-hand row. With a 100 GiB compressed right side on a node with under 28 GiB available to the query, that can never succeed, and no amount of join-order shuffling changes the arithmetic.

What fixed it: grace hash join

Switching the query to join_algorithm = 'grace_hash' let the same statement complete. Grace hash partitions both inputs into buckets on the join key, spills the buckets to disk, and then processes one bucket at a time, so peak memory is bounded by the size of a single bucket rather than the whole right side. We raised the initial bucket count so each bucket stayed comfortably inside the limit, and lifted the client timeouts because the job was now a long-running INSERT ... SELECT rather than an interactive query:

INSERT INTO blocks_receipts_merge
SELECT a.*, b.*
FROM blocks_data_all AS a
INNER JOIN receipts_json_local_untuple AS b
    ON  a.blockHash        = b.logs_blockHash
    AND a.blockNumber      = b.blockNumber
    AND a.from             = b.from
    AND a.to               = b.to
    AND a.transactionIndex = b.transactionIndex
SETTINGS
    join_algorithm                  = 'grace_hash',
    grace_hash_join_initial_buckets = 200,
    send_timeout                    = 30000000000,
    receive_timeout                 = 30000000000;

-- Progress: 3.09 billion rows, 9.74 TB (290.65 thousand rows/s., 916.13 MB/s.) (27.96 GB RAM) 99%

Peak memory stayed just under 28 GB for the full 3.09 billion row pass. The trade is throughput: grace hash reads and writes every bucket to disk, so it is slower than an in-memory hash join that fits. For a one-off denormalisation that is the right trade; for a hot query path we would instead pre-aggregate the right side or materialise the join once. On ClickHouse 26.5 and later the same protection is on by default through max_bytes_ratio_before_external_join = 0.5, which spills a hash join automatically once it passes half the query memory limit, but on the 23.x release we were running the explicit setting was required.

File format on S3 matters more than expected

Because every byte crossed the network from S3, we also benchmarked the landing format: JSON, CSV, gzipped CSV, and Parquet with several internal compression codecs. Parquet was the clear winner for ingestion time and bytes transferred, which is not surprising for a columnar engine reading a columnar file, but it is worth stating because the convenient default from most chain-export tooling is JSON. If you are achieving real-time analytics over object storage, land Parquet.

As with every setting in this post, test on a production-sized copy first. Memory limits, bucket counts and timeouts that fit one cluster will not fit another, and a spilling join on a node with slow local disk can be worse than the failure it replaces.

Conclusion

ChistaDATA’s ClickHouse empowers businesses to achieve real-time analytics, providing them with actionable insights to navigate the complexities of the digital landscape. With cutting-edge technology and expertise, ChistaDATA revolutionizes how organizations harness the power of their data, driving success and fostering innovation across industries.

To know more about Real-Time Analytics with ClickHouse, do visit the following articles:

• Real-time Analytics for Digital Transformation with ChistaDATA’s ClickHouse
• The Need for Real-time Analytics in Telecommunications
• From Batch Processing to Real-time Analytics with ChistaDATA’s ClickHouse
• 5 Reasons to use ChistaDATA’s ClickHouse for Real-time Analytics
• What is real-time analytics?