1

I wondering if it possible to query the GDELT instability data from Big Query. I know you can go to the GDELT site for the data, but is it possible to query it directly from big query. And if it is possible to access more than 180 days of it.

Thanks!

blong
  • 2,815
  • 8
  • 44
  • 110
fletchr
  • 646
  • 2
  • 8
  • 25

1 Answers1

2

You can largely reproduce the Stability API using the top BigQuery query on this page: https://blog.gdeltproject.org/assessing-turkeys-physical-stability-and-societal-mood/ and the second query there shows you how to use the emotional data to generate emotional timelines indicative of various stability undercurrents. Note that the second query, the emotional query, will consume a LOT of data and so you should use the partitioned GKG table and limit your query to just the time period you need, or just stick with the event query to start with.

SELECT substr(string(DATE),0,8) as daydate, count(1), avg(float(REGEXP_REPLACE(V2Tone, r',.*', ""))) tone
  , avg(float(REGEXP_EXTRACT(GCAM, r'v19.1:([-d.]+)'))) anew,sum(integer(REGEXP_EXTRACT(GCAM, r'c8.3:([-d.]+)'))) ridanxietycnt, sum(integer(REGEXP_EXTRACT(GCAM, r'wc:(d+)'))) wordcount   
FROM [gdeltv2.gkg] 
where V2Locations like '%Turkey%Turkey%' 
group by daydate
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325