Day 2

Welcome to Day 2 of my lil adventure to try and build a GDELT demo on ClickHouse as ramp project.

I ended Day 1 without much; I have a few tables, a materialized view that may or may not work, and an unused dictionary. I mean, it’s all unused because I skipped over the data ingest test. The skipping was on purpose though, and did lead to more learning.

Creating the materialized view before inserting data was a choice based on how materialized views (MV) are populated. With each insert into the source table, the MV runs whatever transformation you’ve defined. But what if the data is already present when the MV is created? No data is inserted into the MV. To populate the MV from existing data, you need to do some tricks. I found this page on backfilling useful.

Anyway, let’s go “back” to Step 3 and insert some data

Step 3: Ingest (some) data into tables

Aaand it failed.

Yes, the work from yesterday was close, but no cigar. I ran into a few issues related to both my (Claude’s) ingest approach and with the table schemas.

Issue 1: Timezones

GDELT data is all in UTC. But, the clickhouse_connect() method from the ClickHouse Python client takes an un-zoned date as being local to the timezone of the machine running it, which is currently EDT for my laptop. The consequence was that the fetch logic broke because it uses the last fetched timestamp from an ingest_log table it maintains to decide if we need to fetch more data, and that timestamp comes from the last fetched data files, which is in UTC.

No biggie: added an extra timezone attribute to the few date functions in the ingest logic.

Issue 2: Empty Lat/Long fields

ClickHouse expects all column rows to have a value. While it’s possible to specify something as Nullable, it’s ideally avoided using default values instead (eg 0. '', etc.) because of how the nulls impact performance. I’ve not gone super deep on exactly how large that impact is, but I think the main cause is that the nulls require a separate file with NULL masks to track which specific rows have the null. Consequently, it causes more disk to be used and causes another file to be read for any given query.

However, I made these nullable anyway!

...
`Actor1Geo_Lat` Nullable(Float64),
`Actor1Geo_Long` Nullable(Float64),
`Actor2Geo_Lat` Nullable(Float64),
`Actor2Geo_Long` Nullable(Float64),
`ActionGeo_Lat` Nullable(Float64),
`ActionGeo_Long` Nullable(Float64),
...

Issue 3: CharOffset field types

It turns out that the *CharOffset fields in the Mentions dataset can be -1, which means I need a signed integer field vs the UInt32 I tried to use.

...
`Actor1CharOffset` Int32,
`Actor2CharOffset` Int32,
`ActionCharOffset` Int32,
...

Issue 4: Deduplication

On failure — and during iterative testing like I’ve been doing — we need a way to ensure we don’t duplicate data if we re-insert data. This is particularly important for materialized views depending on the table engine we’re using. You’ll note from Day 1 that I didn’t specify the engine (“blah blah adds as-yet unneeded complexity blah blah”), and the default SharedMergeTree powering the MVs underlying table doesn’t do anything with DELETE. That means if we tried to delete data from events, our aggregation counts would be wrong.

Also, in general, the ingest should be as idempotent as possible: INSERT IF NOT EXISTS, which isn’t a real thing, btw. ClickHouse has a few ways of dealing with this (including SharedReplacingMergeTree engine …ok, it’s wise to learn a bit about engines), but I went with the insert_deduplication_token approach. This approach assigns a token to the insert such that any attempt to run the insert again with the same token will be ignored within a period of time (the replicated deduplication window, which defaults to 1hr).

The updated ingest logic assigns the file timestamp as the token so that all the data in that file can only be inserted once.

Step 3 Mark II: Ingest (some) data into tables

Since the script is meant for GCP Cloud Run, local testing is a bit ugly:

source .venv/bin/activate
functions-framework --target=main --debug
curl -X POST http://localhost:8080

But…

clickhouse-cloud :) select count() from gdelt.events;

SELECT count()
FROM gdelt.events

Query id: 0af2ac63-9fd9-4f2b-8e5c-c70c106e53cd

   ┌─count()─┐
1. │    1356 │
   └─────────┘

1 row in set. Elapsed: 0.002 sec.

clickhouse-cloud :) select count() from gdelt.mentions;

SELECT count()
FROM gdelt.mentions

Query id: a5e6cc5e-a489-4d2d-b65a-bc3f504d12d2

   ┌─count()─┐
1. │    3682 │
   └─────────┘

1 row in set. Elapsed: 0.002 sec.

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: 2bfe0516-f805-4d8a-aa91-0d927c4f43ba

    ┌───LastEventTimeDate─┬─ActorName─────┬─Count─┐
 1. │ 2026-09-21 20:00:00 │               │   515 │
 2. │ 2026-09-21 21:15:00 │               │   473 │
 3. │ 2026-09-21 21:00:00 │               │   444 │
 4. │ 2026-09-21 19:45:00 │               │   405 │
 5. │ 2026-09-21 21:00:00 │ UNITED STATES │   318 │
 6. │ 2026-09-21 20:00:00 │ UNITED STATES │   263 │
 7. │ 2026-09-21 21:15:00 │ UNITED STATES │   254 │
 8. │ 2026-09-21 19:45:00 │ UNITED STATES │   185 │
 9. │ 2026-09-21 21:15:00 │ NIGERIA       │    97 │
10. │ 2026-09-21 21:00:00 │ CANADA        │    80 │
    └─────────────────────┴───────────────┴───────┘

10 rows in set. Elapsed: 0.003 sec. Processed 1.27 thousand rows, 24.00 KB (443.06 thousand rows/s., 8.37 MB/s.)
Peak memory usage: 355.19 KiB.

Hmm…that MV doesn’t look right, at least not what I’m trying to do.

Looks like a task for Day 3.