NELA data on Ocelot

  • Written by Mauricio Gruppi on Jan 26, 2024
  • Here you will find information about the NELA data in Ocelot and how to access it using the shell.
  • Note: Once you sign in through ssh, you be in your home folder, the absolute path for which is /home/<your_username>/
  • The current NELA databases are in /data/nela/databases/

To check which databases are available list all files in this directory:

ls -lh /data/nela/databases
mbfc.db  nela_eng.db  nela_ps.db  nela_ptbr.db  nela_small.db  nela_small.sql  nela_us-local.db  pro-science.db

To open a database in SQLite 3:

sqlite3 /data/nela/databases/<database_name>.db

You are now on a SQLite 3 shell. Here are some basic commands you can run to explore the database:

List all the tables in the database:

sqlite> .tables
links        log          newsdata     source_feed  tweet        videos

List the database schema:

sqlite> .schema
CREATE TABLE newsdata (id text primary key,date text,source text,title text,content text,author text,url text,published text,published_utc integer,collection_utc integer);
CREATE TABLE tweet (id text primary key,article_id text,embedded_html text, tweet_id text, url text, author text, text text, date integer);
CREATE TABLE source_feed (source TEXT, url TEXT, most_recent INTEGER, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(source, url));
CREATE TABLE log (time_start TEXT, time_end TEXT, articles INT, sources INT, sources_total INT);
CREATE TABLE videos (id INTEGER PRIMARY KEY, article_id TEXT, src TEXT, provider TEXT, embed_code TEXT);
CREATE TABLE links (id INTEGER PRIMARY KEY, article_id, src TEXT, domain TEXT);

List the schema for a single table, e.g.: table newsdata:

sqlite>.schema newsdata
CREATE TABLE newsdata (id text primary key,date text,source text,title text,content text,author text,url text,published text,published_utc integer,collection_utc integer);

Running a query to select a few articles on a given date for sources CNN and The Guardian, ordering the results alphabetically by title (notice that a query line requires a semicolon at the end):

SELECT title, source, date FROM newsdata WHERE date='2022-12-01' AND (source='cnn' OR source='theguardian') ORDER BY title ASC
'Harry & Meghan' docuseries trailer debuts|cnn|2022-12-01
'I honestly felt like I was going to die': Jennifer Lopez on 'the biggest heartbreak' of her life|cnn|2022-12-01
'River of blood': Foxconn employee describes violent protests in China|cnn|2022-12-01
'Why haven't I got a question about Jerry Jones': LeBron calls out reporters|cnn|2022-12-01
0% intro APR until 2024 is 100% insane|cnn|2022-12-01
10 Cards Charging 0% Interest Until 2024|cnn|2022-12-01
35 of the coziest gifts for the person who'd rather just stay home|cnn|2022-12-01
40 gifts under $100 you're probably going to want to just buy for yourself|cnn|2022-12-01
43 useful products that caught the attention of our editors|cnn|2022-12-01

For more SELECT statements, see the following SQLite tutorial.

Using an INNER JOIN to select the source, title and URL (column src) from outgoing links in articles. Note: this query uses the published_utc column which stores a datetime object, therefore, we use the datetime function to convert a string to datetime in SQLite. More info here.

SELECT source,title,src FROM (links INNER JOIN newsdata ON links.article_id = newsdata.id) WHERE published_utc < datetime('2021-01-31', 'localtime') LIMIT 10;
abcnews|Today in History: March 21, Johann Sebastian Bach is born|https://abcnews.go.com/alerts/DonaldTrump
abcnews|Today in History: March 21, Johann Sebastian Bach is born|https://abcnews.go.com/alerts/Elections
abcnews|New law puts Wyoming at forefront of abortion pill bans|https://abcnews.go.com/alerts/RonDeSantis
abcnews|New law puts Wyoming at forefront of abortion pill bans|https://abcnews.go.com/alerts/Elections
abcnews|No telling how much more snow coming for Sierra Nevada|https://abcnews.go.com/alerts/Olympics
abcnews|Missouri to restrict gender-affirming care for minors|https://abcnews.go.com/alerts/Obamacare
abcnews|Missouri to restrict gender-affirming care for minors|https://abcnews.go.com/alerts/RonDeSantis
abcnews|Putin welcomes China's plan to settle 'acute crisis' in Ukraine at start of talks with Xi Jinping at the Kremlin|https://abcnews.go.com/alerts/Ukraine
activistpost|Dutch Farmers Rebel, Win Enough Senate Seats To Block Technocrats|https://www.activistpost.com/wp-content/uploads/2023/03/farmer-protest-tnat.jpg
activistpost|Dutch Farmers Rebel, Win Enough Senate Seats To Block Technocrats|https://www.activistpost.com/product/098637394X/US/permacultucom-20/?cart=y