Day 3

---

Step 3: Ingest (some) data into tables … still

The first order of business was figuring out the issue with my materialized view, event_count_by_actor_mv. If you’ll remember from last time, I got an unwanted result showing UNITED STATES with multiple records and multiple counts vs a nice single row showing a single count of how often the US was mentioned as an Actor in events.

RTFM

This requires a special engine type in ClickHouse: the SummingMergeTree. This replaces all the rows with the same ordering key with one row which contains summed values for the numeric columns.

Aaah, yes, that makes sense. I’d just let the underlying event_count_by_actor table use the default SharedMergeTree, which doesn’t do any summing for you, relying instead on the MV query to do it. But even that query was wrong because it was grouping first by ActorName vs date, and using a date value (DATEADDED) that had second-level resolution, meaning every new second would give me another distinct aggregated row.

Two fixes:

1. Change the table definition of event_count_by_actor
2. Change the MV select query to use the Day field (one value per day) and to group by Day first, then Actor

CREATE TABLE IF NOT EXISTS event_count_by_actor (
    `Day` Date,
    `ActorName` String,
    `Count` UInt32,
)
ENGINE = SummingMergeTree
ORDER BY (Day, ActorName);

CREATE MATERIALIZED VIEW IF NOT EXISTS gdelt.event_count_by_actor_mv TO gdelt.event_count_by_actor
    AS SELECT
        Day,
        arrayJoin([Actor1Name, Actor2Name]) as ActorName,
        count() AS Count
    FROM gdelt.events
    WHERE ActorName <> ''
    GROUP BY Day, ActorName;

…notice I also added a WHERE to the MV query to drop empty actor values. I didn’t like the empties, and not yet really sure they add any value.

clickhouse-cloud :) select * from gdelt.event_count_by_actor_mv order by Count desc limit 10;

SELECT *
FROM gdelt.event_count_by_actor_mv
ORDER BY Count DESC
LIMIT 10

Query id: 12b43aa3-f47a-48b2-8d27-bf70d9d397d4

    ┌────────Day─┬─ActorName─────┬─Count─┐
 1. │ 2026-09-24 │ UNITED STATES │   399 │
 2. │ 2026-09-24 │ NIGERIA       │   124 │
 3. │ 2026-09-24 │ ISRAEL        │    90 │
 4. │ 2026-09-24 │ IRAN          │    67 │
 5. │ 2026-09-24 │ POLICE        │    58 │
 6. │ 2026-09-24 │ PRESIDENT     │    52 │
 7. │ 2026-09-24 │ SCHOOL        │    42 │
 8. │ 2026-09-24 │ NEW YORK      │    38 │
 9. │ 2026-09-24 │ FRANCE        │    32 │
10. │ 2026-09-24 │ WASHINGTON    │    31 │
    └────────────┴───────────────┴───────┘

10 rows in set. Elapsed: 0.003 sec.

Well doesn’t that look nice! …maybe useless, but nice!

This means I have data in events, and event_count_by_actor, and incidentally mentions too:

clickhouse-cloud :) select (select count() from gdelt.events) as event_count, (select count() from gdelt.mentions) as mention_count, (select count() from gdelt.event_count_by_actor) as ecba_count;

SELECT
    (
        SELECT count()
        FROM gdelt.events
    ) AS event_count,
    (
        SELECT count()
        FROM gdelt.mentions
    ) AS mention_count,
    (
        SELECT count()
        FROM gdelt.event_count_by_actor
    ) AS ecba_count

Query id: 3e007ab7-dde3-49f1-83d2-90c9afb52da2

   ┌─event_count─┬─mention_count─┬─ecba_count─┐
1. │        1782 │          4998 │        391 │
   └─────────────┴───────────────┴────────────┘

1 row in set. Elapsed: 0.004 sec.

Ingest data ✅

Step 4: Make it live?

Before I deploy to Cloud Run, I’m back to the premature optimization, which maybe isn’t so premature. Having some sense of what queries I’ll want to run before I build some massive tables probably makes sense, especially because there does seem to be some pain involved in re-processing MVs. Of course, I don’t actually need MVs to run aggs. After all, speedy aggs is the whole point of ClickHouse.

So sure, let’s just deploy this sucker and we’ll optimize when the time is right: later.

Before I started, I ended up looking at the system.parts table to see what was what, partially because I was wondering if my single replica cloud environment would be able to process things efficiently and partly because I thought I should grow my familiarity with it. Two things stood out:

1. Data parts/partitions have nothing to do with cloud replicas (ie compute nodes)
2. I had chosen a bad partition for my events table, MonthYear, which is the month-year of the event, not the date the record was added to GDELT. The consequence is that the data is unevenly distributed into partitions because some of the recent test data actually has past (or incorrectly resolved) events.

clickhouse-cloud :) select partition, name, rows from system.parts where table = 'events';

SELECT
    partition,
    name,
    rows
FROM system.parts
WHERE `table` = 'events'

Query id: f6b14d6d-78ee-47bc-ac7d-1e7eb229dc57

   ┌─partition─┬─name─────────┬─rows─┐
1. │ 202509    │ 202509_0_0_0 │    2 │
2. │ 202608    │ 202608_0_0_0 │    2 │
3. │ 202609    │ 202609_0_0_0 │ 1778 │
   └───────────┴──────────────┴──────┘

3 rows in set. Elapsed: 0.002 sec.

3 parts, 3 partitions. Doh. I also noticed that my ORDER BY on both the events and mentions table used the “raw” date fields (DATEADDED, EventTimeDate, respectively), which is also a nono (generally) because it means

• I’ll end up with a lot more parts on ingest since the data groups will be tiny (costs CPU to merge)
• My index will be larger because I’ll have many more unique rows (high cardinality)

One more (surely not the last) change to those two tables:

CREATE TABLE gdelt.events
...
PARTITION BY toYYYYMM(DATEADDED)
ORDER BY (toYYYYMMDD(DATEADDED));

CREATE TABLE gdelt.mentions
...
PARTITION BY toYYYYMM(EventTimeDate)
ORDER BY (toYYYYMMDD(EventTimeDate));

…I got rid of the GLOBALEVENTID from the ORDER BY as well for the same reasons: I don’t think I’ll be doing much aggregating at the eventId level. A quick query showed 3,707 eventIds against 4,998 mention records for the last 15m data load, meaning they function closer to timestamps than useful buckets.

Blow it away, re-test. Done.

Before we go live with the new data fetcher…

Step 4a: Bulk load historical data

More fun, because more data: get me a new bulkload.py that will load in all records from 2026.

Few million rows in and we’re only to early March. Let’s see where this ends up on DAY 4!