Attached is the modified query that I used to create the daily update tables for GDELT. So far it seems to work (for MS SQL). Looking at the data, it looks like the one supplied by GDELT is not correct as a number of fields appear to be the wrong size. Hopefully this is useful to someone. This seems to fix the issues above.

Attachments:

SQL.tablecreate.txt

Hi Rohit,

I've been playing around with it for another while and it looks like there are a few problems with the table setup. I downloaded the table SQL from https://www.gdeltproject.org/data/lookups/SQL.tablecreate.txt

The column definitions seem to be inconsistent with the data. I started increasing the size of some columns from char(3) to char(6) and got the first 2 rows to insert (I also had to delete all the other rows in the TSV file for it to work). There were no issues with the part of the query that had commas so it looks like its just a badly defined or out-of-date table structure provided by GDELT.

The next issue I'm seeing is that one of the rows has text where it should have a number so it looks like I'll have to do some data cleaning before making the insert. My guess was that the data generated in this project would be a little more robust but maybe its prone to some errors?

Have you worked with GDELT data before?

Regards,
Sean.