Last week we released an aligned dataset of Trump tweets appearing on television news using the Visual Global Entity Graph 2.0 (VGEG). But what if you want to do more complex filtering? below is a simple PERL script that can be used to quickly generate SQL queries for BigQuery. Save each keyword you wish to search for to a UTF8 ASCII text file, one word per line. Make sure you have all conjugations and forms of the word of interest, one per row. Then run this script on that file and it will generate the proper SQL. You can uncomment the first SQL line and comment out the second if you want LIKE syntax that allows matches of the keywords anywhere in the OCR text, including inside of other words, whereas as-is it will search each keyword as a regular expression requiring a word boundary on either side of each keyword.

#!/usr/bin/perl

if ($ARGV[0] eq '' || (!-e $ARGV[0])) { print "FATAL: No input file!\n"; }

open(FILE, $ARGV[0]); binmode(FILE, ":utf8");
while(<FILE>) {
$_=~s/\s+$//;
#$SQL .= "LOWER(OCRText) like '%" . lc($_) . "%' OR "; #use LIKE syntax...
$_=~s/'/\\'/g; $SQL .= "REGEXP_CONTAINS(LOWER(OCRText), r'\\b" . lc($_) . "\\b') OR "; #use REGEXP syntax...
}
close(FILE);

$SQL=~s/ OR $//;
$SQL = "SELECT date, station, showName, iaShowId, iaClipUrl, OCRText FROM `gdelt-bq.gdeltv2.vgegv2_iatv` WHERE DATE(date) >= \"2020-01-01\" and (LOWER(OCRText) like '%realdonald%' OR LOWER(OCRText) like '%donald j. trump retweeted%') and ($SQL) order by date asc";

open(OUT, ">$ARGV[0].sql"); binmode(OUT, ":utf8");
print OUT $SQL;
close(OUT);

Save the script above into a file called "makebqterms.pl" Then save the following keyword list to a file called "keywords.txt":

 

iran
iranian
iranians
democrats
democrat
covid
coronavirus
pandemic
wuhan

Then run the script over this keyword list:

./makebqterms.pl ./keywords.txt

This will output the necessary SQL query to use with BigQuery:

SELECT date, station, showName, iaShowId, iaClipUrl, OCRText FROM `gdelt-bq.gdeltv2.vgegv2_iatv` WHERE DATE(date) >= "2020-01-01" and (LOWER(OCRText) like '%realdonald%' OR LOWER(OCRText) like '%donald j. trump retweeted%') and (REGEXP_CONTAINS(LOWER(OCRText), r'\biran\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\biranian\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\biranians\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\bdemocrats\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\bdemocrat\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\bcovid\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\bcoronavirus\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\bpandemic\b') OR REGEXP_CONTAINS(LOWER(OCRText), r'\bwuhan\b')) order by date asc

We hope this is useful to you in making it easier to search the VGEG 2.0 for tweets!