Using BigQuery To Make A 38 Year Timeline Of World Conflict From Half A Billion Records In 3 Seconds

Three years ago we showed off the results of a singularly incredible BigQuery query, courtesy of the one and only Felipe Hoffa, that processes the entire GDELT 1.0 Event database and compiles a timeline spanning the last four decades, listing the top five countries that experienced the greatest increase in their instability level each year. In essence, the query tallies the total number of instability events by country each year, uses this to rank all countries globally by their overall instability that year, compares each country's rank against its rank from the previous year and finally outputs a master list of the top five countries experiencing the greatest jump in instability in that year compared with the previous year. Since the query only measures change in instability, rather than absolute instability levels, it surfaces the key underlying instability stories of each year, rather than simply reporting the same conflict zones year after year. While the query does not perform any kind of normalization, the results are a powerful glimpse into the macro level global instability patterns captured in GDELT's nearly four decades of data.

We decided to rerun the query to see what the results look like looking back over what is now 39 years of data and a total of just over half a billion events in the GDELT 1.0 Event database. As a testament to just how powerful BigQuery really is, the single block of SQL code below processes 514 million records spanning 39 years in just 3.1 seconds, consuming just 9.55GB of query data. You read that right – a 514M record database of global human society processed in just over 3 seconds to yield a master summary of the biggest instability stories of each year 1980-present.

SELECT YEAR, GROUP_CONCAT(country) countries,
 GROUP_CONCAT(STRING(rank)) rankings, GROUP_CONCAT(STRING(jump)) jumps
FROM
 (SELECT YEAR, country, rank, jump
 FROM
 (SELECT YEAR, country, rank, prevrank, prevrank-rank jump, (prevrank-rank)/rank jumpratio,
 RANK() OVER(PARTITION BY YEAR ORDER BY jumpratio DESC) jumprank
 FROM
 ( SELECT YEAR, ActionGeo_CountryCode, rank, 
 LAG(rank) OVER(PARTITION BY ActionGeo_CountryCode ORDER BY YEAR) prevrank
 FROM
 (SELECT YEAR, ActionGeo_CountryCode,
 RANK() OVER(PARTITION BY YEAR ORDER BY c DESC) rank, c
 FROM
 (SELECT YEAR, ActionGeo_CountryCode, COUNT(*) c
 FROM [gdelt-bq:full.events]
 WHERE ActionGeo_CountryCode IS NOT NULL and QuadClass=4
 GROUP BY 1, 2))) a
 JOIN [gdelt-bq:extra.countryinfo] b ON ActionGeo_CountryCode=fips
 WHERE prevrank-rank > 5 AND rank < 50)
 WHERE jumprank<6
 ORDER BY 1,rank)
GROUP BY 1
ORDER BY 1 DESC

And here is the table itself, showing the top countries by year (2018 numbers are as of May 11, 2018). If you look closely, you will see a mixture of interstate conflicts, armed rebellions, terrorist attacks and labor uprisings.

It is important to remember that the rankings below measure change in instability from the previous year, meaning that countries that experience consistently high conflict, such as Syria in recent years, or which have back-to-back events, such as Turkey's 2016 coup which followed the deadliest terror attack in its history in 2015, which in turn followed armed confrontations the year before that, will not typically have a high ranking, since while their instability levels are high, they do not change enough to yield a high "change" score. In essence, the countries below represent those that experienced sudden change from the status quo, while countries that experienced a succession of major situations will not typically make the list.

Using the query above, you can adjust the ranking criteria to come up with all sorts of different country rankings and timelines, including ones that will surface sustained or multiyear instability situations, all while yielding results in just a few seconds over half a billion records.

Explore!