Google BigQuery + GKG 2.0: Sample Queries

SELECT theme, COUNT(*) as count
FROM (
select UNIQUE(REGEXP_REPLACE(SPLIT(V2Themes,';'), r',.*', ")) theme
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Netanyahu%'
)
group by theme
ORDER BY 2 DESC
LIMIT 300

This approach can also be applied to the V2Persons, V2Organizations, and AllNames fields.  For example, the following query returns those person names mentioned in the most articles about Prime Minister Netanyahu's visit to the US by just changing the SPLIT() function to use the "V2Persons" field instead of "V2Themes":

SELECT person, COUNT(*) as count
FROM (
select UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) person
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Netanyahu%'
)
group by person
ORDER BY 2 DESC
LIMIT 300

The substantial references to Winston Churchill are intriguing:

Finally, you can also use this approach to topically characterize entire outlets:

select SourceCommonName, theme, count from (
SELECT SourceCommonName, theme, COUNT(*) as count
FROM (
select SourceCommonName, UNIQUE(REGEXP_REPLACE(SPLIT(V2Themes,';'), r',.*', ")) theme
from [gdelt-bq:gdeltv2.gkg] where DATE >= 20150200000000 AND DATE< 20151099999999
)
group by SourceCommonName, theme
) where count > 100
ORDER BY count DESC

COMPARING LANGUAGES

Often it is useful to compare how a situation is being contextualized differently across languages.  Recall that GDELT 2.0 now live-translates the world's news in 65 languages in realtime.  We can use the "TranslationInfo" field to compare the topical focus of Hebrew vs Arabic-language coverage of Netanyahu's visit.

The query below repeats the topical histogram query of earlier, but this time adds an additional filter to the WHERE clause to restrict the results to only Hebrew-language news coverage:

SELECT theme, COUNT(*) as count
FROM (
select UNIQUE(REGEXP_REPLACE(SPLIT(V2Themes,';'), r',.*', ")) theme
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and AllNames like '%Netanyahu%' and TranslationInfo like '%srclc:heb%'
)
group by theme
ORDER BY 2 DESC
LIMIT 300

This generates the following themes, focused primarily on political violence, conflict, the Palestinians, but also, of particular interest with the upcoming Israeli elections, domestic issues such as unemployment:

LEADER
ARMEDCONFLICT
TAX_RELIGION_JEWS
WB_2462_POLITICAL_VIOLENCE_AND_WAR
WB_2432_FRAGILITY_CONFLICT_AND_VIOLENCE
TAX_FNCACT_MINISTER
TAX_ETHNICITY_ARAB
TAX_FNCACT_PRIME_MINISTER
WB_2433_CONFLICT_AND_VIOLENCE
ELECTION
TAX_ETHNICITY_PALESTINIANS
TAX_FNCACT_POLICE
SECURITY_SERVICES
WB_2670_JOBS
TAX_FNCACT_SOLDIERS
WB_2747_UNEMPLOYMENT
TAX_RELIGION_JEW
TAX_POLITICAL_PARTY_LIKUD
TAX_RELIGION_JEWISH
WB_739_POLITICAL_VIOLENCE_AND_CIVIL_WAR

Now, let's repeat this query, but this time for Arabic-language coverage:

SELECT theme, COUNT(*) as count
FROM (
select UNIQUE(REGEXP_REPLACE(SPLIT(V2Themes,';'), r',.*', ")) theme
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and AllNames like '%Netanyahu%' and TranslationInfo like '%srclc:ara%'
)
group by theme
ORDER BY 2 DESC
LIMIT 300

The resulting thematic breakdown paints a very different picture of reaction to his visit to the US:

GENERAL_GOVERNMENT
LEADER
TAX_FNCACT_PRESIDENT
WB_2432_FRAGILITY_CONFLICT_AND_VIOLENCE
TAX_ETHNICITY_IRANIAN
WB_696_PUBLIC_SECTOR_MANAGEMENT
WB_840_JUSTICE
NEGOTIATIONS
WB_2470_PEACE_OPERATIONS_AND_CONFLICT_MANAGEMENT
WB_2471_PEACEKEEPING
WB_843_DISPUTE_RESOLUTION
WB_936_ALTERNATIVE_DISPUTE_RESOLUTION
WB_2473_DIPLOMACY_AND_NEGOTIATIONS
WB_939_NEGOTIATION
TAX_FNCACT_MINISTER
MEDIA_MSM
WMD
ELECTION
TAX_FNCACT_FOREIGN_MINISTER
ARMEDCONFLICT

Arabic-language coverage of his visit seems to be much more strongly focused on dispute resolution and diplomatic negotiations, with far less coverage of political violence and conflict.

Of course, comparing topical breakdowns across languages requires a lot of careful consideration regarding possible differences in language and narrative (for example discussion of "Iran the country" versus "Iranians the people"), which can affect which themes are triggered and even complexities in how certain topics may or may not map ideally into each language.  At the very least, however, such comparisons can provide very useful unexpected patterns or results for further human investigation.

 

GEOGRAPHIC HISTOGRAMS

Creating geographic histograms using the V2Locations field is more complex than the other fields due to the larger number of attributes recorded for each location and the additional need for certain queries to filter by those attributes.  Creating histograms of the geographic fields requires more extensive use of BigQuery's regular expression capabilities, especially its REGEXP_EXTRACT() function, which allows the use of regular expressions to extract information from a field as part of a SELECT().  BigQuery's regular expression syntax supports incredibly powerful queries, though it does not support all of the capability of PERL or similar regular expressions.

Per the GKG 2.0 file format documentation, the format of the V2Location field is semicolon delimited (each mention of a location is separated by a semicolon), with the details of each location mention separated by the pound symbol ("#").  For each location mention, the details recorded (in order of appearance) are:

• Location Type. (integer) This field specifies the geographic resolution of the match type and holds one of the following values: 1=COUNTRY (match was at the country level), 2=USSTATE (match was to a US state), 3=USCITY (match was to a US city or landmark), 4=WORLDCITY (match was to a city or landmark outside the US), 5=WORLDSTATE (match was to an Administrative Division 1 outside the US – roughly equivalent to a US state). This can be used to filter counts by geographic specificity, for example, extracting only those counts with a landmark-level geographic resolution for mapping. Note that matches with codes 1 (COUNTRY), 2 (USSTATE), and 5 (WORLDSTATE) will still provide a latitude/longitude pair, which will be the centroid of that country or state, but the FeatureID field below will contain its textual country or ADM1 code instead of a numeric featureid.
• Location FullName. (text) This is the full human-readable name of the matched location. In the case of a country it is simply the country name. For US and World states it is in the format of “State, Country Name”, while for all other matches it is in the format of “City/Landmark, State, Country”. This can be used to label locations when placing counts on a map. Note: this field reflects the precise name used to refer to the location in the text itself, meaning it may contain multiple spellings of the same location – use the FeatureID column to determine whether two location names refer to the same place.
• Location CountryCode. (text) This is the 2-character FIPS10-4 country code for the location. Note: GDELT continues to use the FIPS10-4 codes under USG guidance while GNS continues its formal transition to the successor Geopolitical Entities, Names, and Codes (GENC) Standard (the US Government profile of ISO 3166).  Note that from 2/19/2015 through midday 3/1/2015 there was an error that affected some records, causing them to have an invalid value in this field.
• Location ADM1Code. (text) This is the 2-character FIPS10-4 country code followed by the 2-character FIPS10-4 administrative division 1 (ADM1) code for the administrative division housing the landmark. In the case of the United States, this is the 2-character shortform of the state’s name (such as “TX” for Texas). Note: see the notice above for CountryCode regarding the FIPS10-4 / GENC transition.
• Location ADM2Code (integer).  This is is the numeric GAUL code for the administrative division 2 (ADM2) for the administrative division housing the landmark (if available).  For more details on this field, please see the following announcement.
• Location Latitude. (floating point number) This is the centroid latitude of the landmark for mapping. In the case of a country or administrative division this will reflect the centroid of that entire country/division.
• Location Longitude. (floating point number) This is the centroid longitude of the landmark for mapping. In the case of a country or administrative division this will reflect the centroid of that entire country/division.
• Location FeatureID. (text OR signed integer) This is the numeric GNS or GNIS FeatureID for this location OR a textual country or ADM1 code. More information on these values can be found in Leetaru (2012).4 Note: This field will be blank or contain a textual ADM1 code for country or ADM1-level matches – see above. Note: For numeric GNS or GNIS FeatureIDs, this field can contain both positive and negative numbers, see Leetaru (2012) for more information on this.
• Character Offset.  (integer)  This is the approximate character offset within the document where the location was mentioned.

Thus, a typical entry might look like "4#Berlin, Berlin, Germany#GM#GM16#16538#52.5167#13.4#-1746443#1340", indicating a reference to Berlin, Germany.

To start things off, here is a simple query that returns a histogram of locations mentioned in coverage of Greek Prime Minister Tsipras during the same period as the theme query from earlier:

SELECT location, COUNT(*)
FROM (
select REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^.*?#(.*?)#') as location
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
)
group by location
ORDER BY 2 DESC
LIMIT 100

As might be expected, many of the top results are country-level locations like "Greece" and "Spain", which are likely of less interest for many queries.  Instead, the following query modifies the REGEXP_EXTRACT slightly to add "[2-5]" at the start of the query instead of the original ".*?" (filtering each location mention to require a value between 2 and 5 for the "Location Type" field (anything higher-resolution than a country-level match).

SELECT location, COUNT(*)
FROM (
select REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#(.*?)#') as location
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
)
where location is not null
group by location
ORDER BY 2 DESC
LIMIT 100

Of course, not all of those results are in Greece, so by adding an additional filter to also require "GR" (the country code for Greece) in the "Location CountryCode" field, the following query returns a histogram of all city-level locations in Greece mentioned in coverage of the Prime Minister:

SELECT location, COUNT(*)
FROM (
select REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#(.*?)#GR#') as location
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
)
where location is not null
group by location
ORDER BY 2 DESC
LIMIT 100

The following query expands this a bit, allowing city-level matches from Greece ("GR"), Germany ("GM") and Spain ("SP"), using a "non-capture group" in the regular expression:

SELECT location, COUNT(*)
FROM (
select REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#(.*?)#(?:GR|GM|SP)#') as location
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
)
where location is not null
group by location
ORDER BY 2 DESC
LIMIT 100

Finally, for mapping purposes, you might want to export just a latitude/longitude histogram to pass to a mapping program:

SELECT coord, COUNT(*)
FROM (
select REGEXP_REPLACE(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?#.*?)#'), '^(.*?)#(.*?)', '\1;\2') as coord
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
)
where coord is not null
group by coord
ORDER BY 2 DESC
LIMIT 100

Finally, this query modifies the one above to add in the name of the city into each match by modifying the REGEXP_EXTRACT to return multiple attributes and then using REGEXP_REPLACE to parse them out into the final results (NOTE that in cases of highly geographically proximate features, or multiple alternative names for a given feature, the query below may yield multiple records with the same lat/long, but different names):

SELECT namedcoord, COUNT(*)
FROM (
select REGEXP_REPLACE(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#(.*?#.*?#.*?#.*?#.*?#.*?)#'), '^(.*?)#.*?#.*?#.*?#(.*?)#(.*?)', '\1;\2;\3') as namedcoord
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
)
where namedcoord is not null
group by namedcoord
ORDER BY 2 DESC
LIMIT 100

 

NETWORK ANALYSIS

Finally, often it is the connections among entities that is of greatest interest, rather than just their frequency of occurrence.  For example, what if you wanted to understand the web of connections surrounding Greek Prime Minister Alexis Tsipras, creating a network diagram showing all of the people he is most closely connected to in the press during a particular time period or in relation to a particular topic?  The following query creates a cooccurence network diagram of the people appearing in coverage of Prime Minister Tsipras during the period March 2, 2015 to March 4, 2015:

SELECT a.name, b.name, COUNT(*) as count
FROM (FLATTEN(
SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name
FROM [gdelt-bq:gdeltv2.gkg]
WHERE DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
,name)) a
JOIN EACH (
SELECT GKGRECORDID, UNIQUE(REGEXP_REPLACE(SPLIT(V2Persons,';'), r',.*', ")) name
FROM [gdelt-bq:gdeltv2.gkg]
WHERE DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Tsipras%'
) b
ON a.GKGRECORDID=b.GKGRECORDID
WHERE a.name<b.name
GROUP EACH BY 1,2
ORDER BY 3 DESC
LIMIT 250

The query is a bit complex, but to modify it to generate a network of person names around any query of interest, just change the first two WHERE() clauses to your query of interest.  Essentially what the query does is to compile the list of person names from each matching article and record how many articles each pair of names appear together anywhere in the article.  The final results of the query is a histogram of all pairs of names and how many articles they appeared together – essentially the "edge list" of the cooccurance network of persons.

Once you run the query above in BigQuery, click on the "Download as CSV" button at the top right of the query results table.  This will download a CSV file to your local computer containing the results.  Open the file in Excel and rename the first column to be "Source", the second to be "Target".  Create a new column called "Type" and fill it in as "Undirected" for every row.  Create another new column and call it "Weight".  Set it to be the formula "=C2/SUM(C:C)" and copy-paste to all rows so that the value of this column for each row is essentially its percent of the total of column C (the "count" column).  Basically this converts the raw article count of the "counts" column  into a floating point value from 0 to 1 that Gephi prefers.  The spreadsheet should now look like:

Source	Target	count	Type	Weight
Alexis Tsipras	Mariano Rajoy	496	Undirected	0.056998
Alexis Tsipras	Jeroen Dijsselbloem	334	Undirected	0.038382
Alexis Tsipras	Simone Boitelle	229	Undirected	0.026316
Jeroen Dijsselbloem	Simone Boitelle	229	Undirected	0.026316
Alexis Tsipras	Wolfgang Schaeuble	221	Undirected	0.025396
Alexis Tsipras	Yanis Varoufakis	199	Undirected	0.022868
Jeroen Dijsselbloem	Mariano Rajoy	175	Undirected	0.02011

Finally, save the file somewhere on your computer where you can find it again in a moment.

Download a copy of the open source Gephi package and install it on your computer.  Run Gephi and choose "New Project" from the "File" menu.  Click on the "Data Laboratory" button in the top left right under the menu bar.  At top left under the "Data Table" tab you should see a row of buttons – click on the "Import Spreadsheet" button and on the popup that appears browse for the spreadsheet you just saved.  Under the "As table" dropdown on the popup, choose "Edges table".  Then click the "Next" button on the popup.  Then click the "Finish" button on the popup.  You should now see a spreadsheet.  Click on the "Nodes" button towards the top-left right under the "Data Table" tab (in the same row where you found "Import Spreadsheet").  At the bottom of the window, click on the "Copy data to other column" button and choose "ID".  From the popup that appears, choose "Label" and click "OK".  This tells Gephi to use the name of each node as its human-readable label.

Now click on the "Overview" button at the top-left of Gephi (immediately to the left of the "Data Laboratory" button you clicked earlier).  On the right side of the screen, click on the "Run" button beside "Modularity" (about midway down the list in the tab on the right side of the screen).  Click OK on the popup that appears and then Gephi will pause for a few moments, and then click "Close" on the next popup that appears.  Now, on the left side of the screen, towards the top, you should see a "Partition" tab – click on it.  Then click on the twin circular green arrows button (the refresh button).  This will populate the dropdown to its right – choose "Modularity Class" from the dropdown.  Then click the "Apply" button on the lower right of that tab.  The nodes on the network should now be colored.

Now click on the "Ranking" tab right beside the "Partition" tab.  Click on the little icon that looks like a red diamond (if you mouse over it it says "Size/Weight").  Choose "Degree" from the dropdown and set "Min size" to "7" and "Max size" to "40" and then click "Apply".  The nodes should now be different sizes based on their importance in the network.

Now go down to the "Layout" tab (bottom half of the left side of the window) and choose "Force Atlas 2" from the dropdown.  Then click on the "Run" button.  Let it run for around 10-15 seconds or until it looks like it has unfolded into some kind of legible structure and click on the "Run" button again (it says "Stop" while it is running).  Note that this is called "graph layout" and this algorithm will continue forever if left alone (it doesn't have an ending point), so you have to manually stop it once it looks "right".

Now go back to the top of the window and click on the "Preview" button (to the right of the "Data Laboratory" button).  On the left side there is a set of configuration options for the graph.  Set them to be the following:

Now, finally, click on the "Refresh" button at the bottom left of the window.  You should now see a network diagram that looks like the following (the colors and positions of nodes will be different each time you run Gephi, since it randomizes these).  Congratulations, you have now visualized the co-occurance network of people co-occuring in coverage of the Prime Minister of Greece over a two day period!  Looking through the network below the names most closely associated with the Prime Minister are those most closely involved in either Greece's negotiations with the EU or the related narrative surrounding Greece's debt restructuring and austerity discussions.

 

We hope that this has given you some ideas of how to leverage the enormous power of BigQuery to not only conduct realtime complex queries over the GDELT GKG, but to go much further, having BigQuery even parse and histogram the results for you, delivering the final analytic results.  In this way, using the GKG with BigQuery is an example of loading massive CSV data into BigQuery to provide realtime analytics over highly structured flattened data.  We'd like to thank Felipe Hoffa again for his tremendous help in navigating how to process the GKG's complex delimited structure into BigQuery's advanced string functions and in formulating and tuning these queries.  We can't wait to see what you're able to accomplish!