Call it my ramp project: ingesting and analyzing GDELT data on ClickHouse Cloud. I thought it fitting for a few reasons:

It’s big
It’s relatively simple: three tables with some lookups if you want to translate the CAMEO codes
It’s a good mix of datatypes: dates, entities, locations, sentiment, relationships
It’s free
It’s surely been done before (rediscovering the obvious, yup)

This will likely span a few days/weeks as I build out and share what I’ve done. But here’s where things landed on Day 1

Day 1

Step 1: Find the data

Easy step, for the most part. The top level menu on the GDELT page has a “Data” link, which goes through the sundry datasets…took a tick to figure out what was what. GDELT 2.0 sounded better than GDELT 1.0, so LFG.

The GDELT 2.0 docs linked from the Data page has raw data files referenced in hosted index files updated every 15m:

Resist peeking at the master list, it’s ~128MB. But the 15m list shows the structure:

<bytes> <md5> <url>

The most recent as of this exact minute is:

62040 e8030929c921562c97eaefb87d0bcd01 http://data.gdeltproject.org/gdeltv2/20260918211500.export.CSV.zip
109244 d22cf493a8be9f6b9e34fa3b5ae1783e http://data.gdeltproject.org/gdeltv2/20260918211500.mentions.CSV.zip
5296974 f7d54cc7e45609043b967fc683af2777 http://data.gdeltproject.org/gdeltv2/20260918211500.gkg.csv.zip

Data found ✅

Step 2: Build a ClickHouse database and tables

Well, perhaps understanding the data model first would help, but let’s just call that a subtask. The GDELT 2.0 Event Database Codebook had me covered, though in a somewhat unusual format that is heavy on narrative (excellent!) and light on spec-styled structure (bogus!).

The DDL

SQL

CREATE DATABASE gdelt;

CREATE TABLE gdelt.events (
    `GLOBALEVENTID` UInt32,
    `Day` Date,
    `MonthYear` UInt32,
    `Year` UInt16,
    `FractionDate` Float32,
    `Actor1Code` String,
    `Actor1Name` String,
    `Actor1CountryCode` String,
    `Actor1KnownGroupCode` String,
    `Actor1EthnicCode` String,
    `Actor1Religion1Code` String,
    `Actor1Religion2Code` String,
    `Actor1Type1Code` String,
    `Actor1Type2Code` String,
    `Actor1Type3Code` String,
    `Actor2Code` String,
    `Actor2Name` String,
    `Actor2CountryCode` String,
    `Actor2KnownGroupCode` String,
    `Actor2EthnicCode` String,
    `Actor2Religion1Code` String,
    `Actor2Religion2Code` String,
    `Actor2Type1Code` String,
    `Actor2Type2Code` String,
    `Actor2Type3Code` String,
    `isRootEvent` Bool,
    `EventCode` String,
    `EventBaseCode` String,
    `EventRootCode` String,
    `QuadClass` UInt8,
    `GoldsteinScale` Decimal32(1),
    `NumMentions` UInt32,
    `NumSources` UInt32,
    `NumArticles` UInt32,
    `AvgTone` Float64,
    `Actor1Geo_Type` Int8,
    `Actor1Geo_Fullname` String,
    `Actor1Geo_CountryCode` String,
    `Actor1Geo_ADM1Code` String,
    `Actor1Geo_ADM2Code` String,
    `Actor1Geo_Lat` Float64,
    `Actor1Geo_Long` Float64,
    `Actor1Geo_FeatureID` String,
    `Actor2Geo_Type` Int8,
    `Actor2Geo_Fullname` String,
    `Actor2Geo_CountryCode` String,
    `Actor2Geo_ADM1Code` String,
    `Actor2Geo_ADM2Code` String,
    `Actor2Geo_Lat` Float64,
    `Actor2Geo_Long` Float64,
    `Actor2Geo_FeatureID` String,
    `ActionGeo_Type` Int8,
    `ActionGeo_FullName` String,
    `ActionGeo_CountryCode` String,
    `ActionGeo_ADM1Code` String,
    `ActionGeo_ADM2Code` String,
    `ActionGeo_Lat` Float64,
    `ActionGeo_Long` Float64,
    `ActionGeo_FeatureID` String,
    `DATEADDED` DateTime,
    `SOURCEURL` String
    ) 
    PARTITION BY MonthYear 
    ORDER BY (GLOBALEVENTID, DATEADDED);
    
CREATE TABLE gdelt.mentions (
    `GLOBALEVENTID` UInt32,
    `EventTimeDate` DateTime,
    `MentionTimeDate` DateTime,
    `MentionType` String,
    `MentionSourceName` String,
    `MentionIdentifier` String,
    `SentenceID` UInt32,
    `Actor1CharOffset` UInt32,
    `Actor2CharOffset` UInt32,
    `ActionCharOffset` UInt32,
    `InRawText` UInt8,
    `Confidence` UInt8,
    `MentionDocLen` UInt32,
    `MentionDocTone` Float64,
    `MentionDocTranslationInfo` String,
    `Extras` String
    ) 
    PARTITION BY toYYYYMM(EventTimeDate) 
    ORDER BY (GLOBALEVENTID, EventTimeDate);

The sharp eyed (and ClickHouse-experienced) will note that there’s no ENGINE specified. That’s because I’m running this on ClickHouse Cloud, which defaults to using the SharedMergeTree engine. Engines are a whole thing, but for the beginners among us, I think it adds as-yet unneeded complexity. So, I’ve held off on thinking about it too much.

I did need to give some thought to datatypes … or at least, I took this as a good opportunity to learn a bit about my options. You’ll note they’re not too exotic, though I did try to use the smallest Int types I could, googling a few times for “UInt8 max value” to ensure the data would fit into the type. I also learned that, thankfully, Bool will do what you’d hope and convert 1/0 to true/false.

Database & tables ✅

Step 3: Ingest data into tables

B-e-a-uuutiful use for your LLM of choice. This GCP Cloud Run project was the result…though haven’t run it yet, because…

Ingest ❎

Step 2.5: Prematurely optimize

I’m supposed to be learning something, right? And I plan to aggregate things, yes? Aren’t Materialized Views supposed to pre-compute aggs at ingest and make using them nice and speedy? What about Projections for giving more freedom (meaning more speed) to group queries by non-primary key / order by fields?

Of course, doing this right would require me to have a complete picture of all the analytic questions I want to ask, which is to say a complete list of all the aggregations and such I need to run. I don’t have that…premature optimization, remember. But, I do know I’ll want to at least aggregate on the Actor (who/what), so let’s run with that.

CREATE TABLE event_count_by_actor (
    `LastEventDateTime` DateTime,    
    `ActorName` String,
    `Count` UInt32,
)
ORDER BY (ActorName, LastEventDateTime);

CREATE MATERIALIZED VIEW event_count_by_actor_mv TO event_count_by_actor
    AS SELECT
        DATEADDED as LastEventDateTime,
        arrayJoin([Actor1Name, Actor2Name]) as ActorName,
        count() AS Count
    FROM events
    GROUP BY ActorName, LastEventDateTime;

…and maybe I’ll want to aggregate by EventCode, the CAMEO(PDF) code for the event mentioning the entity. But the code itself is unusable, so I want to enrich it with the description text using their decoder ring

This takes us to the question: how do we do data enrichment in ClickHouse? Joins, sure. But given it’s a pretty small fixed list, I found Dictionaries, that should allow very fast insert or query time enrichment.

Dictionaries were interesting because of how they’re built and run in the background, meaning the definition involves authentication since they’re not run as “you”, the querying user. Named Collections are probably the right way to define the credentials, but I just jammed it directly into the DDL.

CREATE USER dict_reader IDENTIFIED BY '{PASSWORD}';
GRANT SELECT ON gdelt.cameo TO dict_reader;
CREATE DICTIONARY cameo_dict (
    code UInt16,
    description String
    )
    PRIMARY KEY code
    SOURCE(
        CLICKHOUSE(TABLE 'cameo' USER 'dict_reader' PASSWORD '{PASSWORD}')
    )
    LAYOUT(FLAT())
    LIFETIME(MIN 0 MAX 0);

I used the File Upload feature in ClickHouse Cloud to upload the above TSV into a cameo table.

Materialized view & Dictionary ❓

Step 4: Test it.

…yeah, that’s a job for tomorrow. I strongly doubt I got it all right so far, especially the premature aggs and dictionary and such. Stay tuned for a set of corrections!