Having fun with BigQuery and real-time Reddit data
#Having fun with BigQuery
I wanted to have some fun today since the subreddits r/sweden and r/the_donald were going at it today. Since comments and submissions are placed into BigQuery in real-time, we can run a variety of different reports against the data.
You can run your own inquiries! It's easy!
To get started, check out my blog posting
Let's start with the past 86,400 seconds of data. Remember, when you run a SQL query against a table that is constantly updating, you can restrict your queries to a specific number of seconds and save yourself in the amount of data processed. Since Google gives you a free terabyte of data processing per month when using BigQuery, you can run some interesting reports (and a lot of them) when you take advantage of these caches. Let's start with a simple one.
How do you use the cache?
Normally, when you run a query against a table, it looks like this:
SELECT * FROM [pushshift:rt_reddit.comments]
To use the cache and restrict the amount of time to look back at data, you simply use a table decorator:
SELECT * FROM [pushshift:rt_reddit.comments@-86400000-]
The number is in milliseconds, so simply add three zeros to the number of seconds you want to use. BigQuery's cache goes back to about a week. In the example above, I'm using the previous 24 hours of data. Using table decorators is a great way to practice and fine-tune your SQL statements without using up all your data processing quota!
Hint: Don't forget to include the leading and trailing dash around the number of seconds you use!
Additional hint: This trick really only works with tables that are constantly updating at a steady pace. If you do a huge upload into BigQuery, using the cache isn't as effective. Remember, using the cache in this way isn't a substitute for a where clause to restrict the time you want to look at, but in this case, it's extremely close because the Reddit real-time tables update approximately at the same speed as comments and posts are made to Reddit.
##Which subreddits mentioned Sweden the most today?
SELECT subreddit, count(*) count FROM [pushshift:rt_reddit.comments@-86400000-]
WHERE REGEXP_MATCH(lower(body),r'sweden')
GROUP BY subreddit
ORDER BY 2 DESC;
| Subreddit | Number of comments |
|---|---|
| sweden | 3372 |
| The_Donald | 2651 |
| OutOfTheLoop | 245 |
| SubredditDrama | 205 |
| europe | 179 |
| european | 97 |
| AskReddit | 95 |
| pics | 73 |
| explainlikeimfive | 73 |
That previous query used 426 Megabytes of data processing -- so I could run 1,000 of them and still have over half of my free monthly quota left!
##What about mentions of trump?
SELECT subreddit, count(*) count FROM [pushshift:rt_reddit.comments@-86400000-]
WHERE REGEXP_MATCH(lower(body),r'trump')
GROUP BY subreddit
ORDER BY 2 DESC;
| Subreddit | Number of comments |
|---|---|
| The_Donald | 3052 |
| sweden | 2723 |
| politics | 2112 |
| PoliticalDiscussion | 785 |
| news | 603 |
| SandersForPresident | 544 |
| AskReddit | 521 |
| AskTrumpSupporters | 362 |
| pics | 256 |
##So just how many more comments did r/sweden receive today compared to the previous week?
SELECT count, CAST(SEC_TO_TIMESTAMP(time*86400) as STRING) FROM
(SELECT count(*) count,FLOOR(TIMESTAMP_TO_SEC(created_utc)/86400) time FROM
[pushshift:rt_reddit.comments@-604800000-]
WHERE lower(subreddit) = 'the_donald'
AND REGEXP_MATCH(lower(body),r'sweden')
GROUP BY 2)
ORDER BY 2 ASC
| Comments | Date |
|---|---|
| 48 | 2016-04-08 00:00:00 UTC |
| 28 | 2016-04-09 00:00:00 UTC |
| 51 | 2016-04-10 00:00:00 UTC |
| 54 | 2016-04-11 00:00:00 UTC |
| 36 | 2016-04-12 00:00:00 UTC |
| 313 | 2016-04-13 00:00:00 UTC |
| 2349 | 2016-04-14 00:00:00 UTC |
| 357 | 2016-04-15 00:00:00 UTC |
Wow! r/sweden has gotten over 50x more comments than it usually does!
##What were some of the more popular words on r/sweden that occurred with a much higher frequency than the rest of Reddit during that time period?
SELECT word, s_count, s_ratio, g_count, g_ratio, s_to_g_ratio, ROUND(weight,2) FROM (
SELECT s.word word, s.c s_count, ROUND(s.ratio,4) s_ratio, g.c g_count, ROUND(g.ratio,4)
g_ratio,ROUND(s.ratio/g.ratio,2) s_to_g_ratio, ROUND(s.ratio/g.ratio,2) * s.c weight FROM
(SELECT c, word, ssum, (c/ssum)*100 ratio FROM
(SELECT c,word, sum(c) over () as ssum FROM
(SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(word,r'([a-z0-9\-\']*)') word FROM
(SELECT SPLIT(lower(body),' ') word FROM [pushshift:rt_reddit.comments@-86400000-] WHERE
lower(subreddit)="sweden"))
GROUP BY word))) s
JOIN EACH
(SELECT c, word, gsum, (c/gsum)*100 ratio FROM
(SELECT c,word, sum(c) over () as gsum FROM
(SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(word,r'([a-z0-9\-\']*)') word FROM
(SELECT SPLIT(lower(body),' ') word FROM [pushshift:rt_reddit.comments@-86400000-]))
GROUP BY word))) g
ON g.word = s.word
WHERE s.word NOT IN ('gt','lt','amp')
)
WHERE s_to_g_ratio > 4
ORDER BY s_ratio DESC;
| word | s_count | s_ratio | g_count | g_ratio | s_to_g_ratio | weight |
|---|---|---|---|---|---|---|
| trump | 69426 | 9.3121 | 83977 | 0.1147 | 81.21 | 5638085.46 |
| donald | 64536 | 8.6562 | 66842 | 0.0913 | 94.84 | 6120594.24 |
| cuck | 4094 | 0.5491 | 6419 | 0.0088 | 62.65 | 256489.1 |
| drumpf | 1500 | 0.2012 | 1644 | 0.0022 | 89.63 | 134445.0 |
| country | 1213 | 0.1627 | 16666 | 0.0228 | 7.15 | 8672.95 |
##What about the words in r/the_donald?
SELECT word, s_count, s_ratio, g_count, g_ratio, s_to_g_ratio, ROUND(weight,2) FROM (
SELECT s.word word, s.c s_count, ROUND(s.ratio,4) s_ratio, g.c g_count, ROUND(g.ratio,4)
g_ratio,ROUND(s.ratio/g.ratio,2) s_to_g_ratio, ROUND(s.ratio/g.ratio,2) * s.c weight FROM
(SELECT c, word, ssum, (c/ssum)*100 ratio FROM
(SELECT c,word, sum(c) over () as ssum FROM
(SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(word,r'([a-z0-9\-\']*)') word FROM
(SELECT SPLIT(lower(body),' ') word FROM [pushshift:rt_reddit.comments@-86400000-] WHERE
lower(subreddit)="the_donald"))
GROUP BY word))) s
JOIN EACH
(SELECT c, word, gsum, (c/gsum)*100 ratio FROM
(SELECT c,word, sum(c) over () as gsum FROM
(SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(word,r'([a-z0-9\-\']*)') word FROM
(SELECT SPLIT(lower(body),' ') word FROM [pushshift:rt_reddit.comments@-86400000-]))
GROUP BY word))) g
ON g.word = s.word
WHERE s.word NOT IN ('gt','lt','amp')
)
WHERE s_to_g_ratio > 4
ORDER BY s_ratio DESC;
| word | s_count | s_ratio | g_count | g_ratio | s_to_g_ratio | weight |
|---|---|---|---|---|---|---|
| sweden | 137542 | 15.8176 | 141776 | 0.1932 | 81.89 | 1.126331438E7 |
| swedish | 2025 | 0.2329 | 3680 | 0.005 | 46.45 | 94061.25 |
| sverige | 1899 | 0.2184 | 2169 | 0.003 | 73.9 | 140336.1 |
| finland | 1480 | 0.1702 | 1867 | 0.0025 | 66.91 | 99026.8 |
| balls | 1464 | 0.1684 | 4487 | 0.0061 | 27.54 | 40318.56 |
##What were the most popular posts in r/sweden?
SELECT count(*),FIRST(title),FIRST(permalink) FROM
[pushshift:rt_reddit.comments@-86400000-] t1
LEFT JOIN [pushshift:rt_reddit.submissions@-86400000-] t2 ON t1.link_id = t2.id
WHERE lower(t1.subreddit) = 'sweden'
GROUP BY link_id
ORDER BY 1 DESC
LIMIT 10
| Comments | Title | Link |
|---|---|---|
| 2097 | Trumpinators gör ett svårt val | link |
| 1596 | /r/all | link |
| 1558 | Hur duellen med The_Donald kommer att utspela sig | link |
| 1416 | Dear Sweden - Thank you for smacking down r/The_Donald. Sincerely - The rest of America. | link |
| 1290 | All this hate from r/The_Donald is breaking my heart | link |
| 893 | En vild r/the_donald angängare framträder | link |
| 847 | So I just came home from work and then I see this... | link |
| 828 | Vad jag ser när användare från r/the_donald kommenterar på sweddit | link |
| 786 | How i feel as a swede on reddit today | link |
| 785 | När r/The_Donald märker att dem har förlorat | link |
##What were the most popular posts in r/the_donald?
SELECT count(*),FIRST(title),FIRST(permalink) FROM
[pushshift:rt_reddit.comments@-86400000-] t1
LEFT JOIN [pushshift:rt_reddit.submissions@-186400000-] t2 ON t1.link_id = t2.id
WHERE lower(t1.subreddit) = 'the_donald'
GROUP BY link_id
ORDER BY 1 DESC
LIMIT 10
| Comments | Title | Link |
|---|---|---|
| 1419 | LIVE Stream: Donald Trump at the 2016 New York State Republican Gala (4-14-16) | link |
| 970 | How an army of pro-Donald Trump trolls are taking over Reddit | link |
| 826 | "In order to properly educate r/Sweden about who exactly they are letting fuck their wives (and their goats) our ""no racism"" rule will no longer be enforced at all with regards to the middle east." | link |
| 825 | LIVE: Donald Trump Town Hall with Sean Hannity | link |
| 669 | "Hey, r/Sweden, I see your anti-Trump posts on r/all. Shouldn't you be taking care of yourselves before you worry about others?" | link |
| 599 | "My university wants people to vote on which ""Social Justice Leader"" this important and historic building on campus should be. Please vote Donald J. Trump" | link |
| 577 | We are Sli.mg - AMA | link |
| 559 | "I made a post a few hours ago saying that as a poor minority, Bernie's plans would hurt me. Here are PMs I got from BernieBots wishing for the death of my family and insulting me for not having been able to go to college." | link |
| 546 | Can we just bring attention to the fact that one of r/Sweden's posts was glorifying 9/11 and shitting on the idea that it made Trump upset? | link |
| 545 | "@ r/Sweden... Congratulations you played yourself." | link |
#Other useful BigQuery SQL commands for Reddit data exploration
Find the top 25 links by number of comments over the past 24 hours
Approximate data usage: 75 megs
SELECT count(*),FIRST(title),CONCAT('[link](',FIRST(permalink),')') FROM
[pushshift:rt_reddit.comments@-86400000-] t1
LEFT JOIN [pushshift:rt_reddit.submissions@-150000000-] t2 ON t1.link_id = t2.id
GROUP BY link_id
ORDER BY 1 DESC
LIMIT 25
Find the top 25 links by number of comments where comments contains a certain word (science in this example)
Approximate data usage: 500 megs
SELECT count(*),FIRST(title),CONCAT('[link](',FIRST(permalink),')') FROM
[pushshift:rt_reddit.comments@-86400000-] t1
LEFT JOIN [pushshift:rt_reddit.submissions@-150000000-] t2 ON t1.link_id = t2.id
WHERE REGEXP_MATCH(lower(body),r'science')
GROUP BY link_id
ORDER BY 1 DESC
LIMIT 25
Find popular 2 word n-grams within a specific subreddit (Search for current trending subjects)
Approximate data usage: 500 megs
SELECT z.word word, z.c subreddit_count, ROUND(z.ratio,4) subreddit_ratio, g.c global_count,
ROUND(g.ratio,4) global_ratio,ROUND(z.ratio/g.ratio,2) subreddit_to_global_freq FROM
(SELECT c,word, sum, (c/sum)*100 ratio FROM (SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(body,r'([A-Z][A-Za-z]*\s[A-Z][A-Za-z]*)') word FROM
[rt_reddit.comments@-36400000-] WHERE LOWER(subreddit) = "news")
GROUP BY word) a
CROSS JOIN (
SELECT sum(c) sum FROM (
SELECT c,word FROM (SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(body,r'([A-Z][A-Za-z]*\s[A-Z][A-Za-z]*)') word FROM
[pushshift:rt_reddit.comments@-36400000-] WHERE LOWER(subreddit) = "news")
GROUP BY word))
) b) z
JOIN EACH
(SELECT c,word, sum, (c/sum)*100 ratio FROM (SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(body,r'([A-Z][A-Za-z]*\s[A-Z][A-Za-z]*)') word FROM
[pushshift:rt_reddit.comments@-36400000-])
GROUP BY word) a
CROSS JOIN (
SELECT sum(c) sum FROM (
SELECT c,word FROM (SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(body,r'([A-Z][A-Za-z]*\s[A-Z][A-Za-z]*)') word FROM
[pushshift:rt_reddit.comments@-36400000-])
GROUP BY word))
) f) g
ON g.word = z.word
WHERE z.ratio > g.ratio*10
ORDER BY z.ratio DESC
Find words that are related to another word (in this example, we use the word "atomic")
Approximate Data Usage: 2 GB
SELECT s.word word, s.c s_count, ROUND(s.ratio,4) s_ratio, g.c g_count, ROUND(g.ratio,4)
g_ratio,ROUND(s.ratio/g.ratio,2) s_to_g_ratio FROM
(SELECT c, word, ssum, (c/ssum)*100 ratio FROM
(SELECT c,word, sum(c) over () as ssum FROM
(SELECT count(*) c, word FROM
(SELECT REGEXP_REPLACE(word,r'[^A-Za-z0-9\-]','') word FROM
(SELECT SPLIT(body,' ') word FROM [pushshift:rt_reddit.comments@-500000000-] WHERE
REGEXP_MATCH(LOWER(body), r"atomic")))
GROUP BY word))) s
JOIN EACH
(SELECT c, word, gsum, (c/gsum)*100 ratio FROM
(SELECT c,word, sum(c) over () as gsum FROM
(SELECT count(*) c, word FROM
(SELECT REGEXP_EXTRACT(word,r'([A-Za-z0-9\-\']*)') word FROM
(SELECT SPLIT(body,' ') word FROM [pushshift:rt_reddit.comments@-500000000-]))
GROUP BY word))) g
ON g.word = s.word
WHERE s.ratio > g.ratio*20
ORDER BY s.ratio DESC
Find words that end in 'ology' over the past 1,000 minutes and rank them by their frequency of occurrence
Approximate Data Usage: 300 Megabytes
SELECT count(*) count, words FROM
(SELECT split(lower(body),' ') words FROM [pushshift:rt_reddit.comments@-60000000-])
WHERE REGEXP_MATCH(words,r'^[a-z]+ology$')
GROUP BY words
ORDER BY 1 DESC;
Find all Reddit username mentions over the past 1,000 minutes and rank them by their frequency of occurrence
Approximate Data Usage: 300 Megabytes
SELECT count(*), words FROM
(SELECT split(lower(body),' ') words FROM [pushshift:rt_reddit.comments@-60000000-])
WHERE REGEXP_MATCH(words,r'^/u/[a-z0-9_-]+$')
GROUP BY words
ORDER BY 1 DESC;
Find all Reddit subreddit mentions over the past 1,000 minutes and rank them by their frequency of occurrence
Approximate Data Usage: 300 Megabytes
SELECT count(*), words FROM
(SELECT split(lower(body),' ') words FROM [pushshift:rt_reddit.comments@-60000000-])
WHERE REGEXP_MATCH(words,r'^/r/[a-z0-9_-]+$')
GROUP BY words
ORDER BY 1 DESC;
Rank subreddits based on the frequency of use of a specific word (Great for finding subreddits that relate to something). This example uses the word 'music'
Approximate Data Usage: 300 Megabytes
SELECT count(*) count, subreddit FROM
(SELECT split(lower(body),' ') words, subreddit FROM
[pushshift:rt_reddit.comments@-60000000-])
WHERE REGEXP_MATCH(words,r'^music$')
GROUP BY subreddit
ORDER BY 1 DESC;
| count | subreddit |
|---|---|
| 681 | Music |
| 474 | AskReddit |
| 125 | hiphopheads |
| 122 | listentothis |
| 72 | musictheory |
| 68 | anime |
| 63 | WeAreTheMusicMakers |
| 62 | radiohead |
| 59 | indieheads |
Find the most prolific users over the past day (Great for finding bots)
Approximate Data Usage: 30 Megabytes
SELECT count(*), author FROM [pushshift:rt_reddit.comments@-86400000-]
GROUP BY 2
ORDER BY 1 DESC
Find the most prolific users over the past day by number of comments that have mentioned 'sweden'
Approximate Data Usage: 400 Megabytes
SELECT count(*), author FROM [pushshift:rt_reddit.comments@-86400000-]
WHERE REGEXP_MATCH(lower(body),r'sweden')
GROUP BY 2
ORDER BY 1 DESC
Find the most active subreddits over the past day where no comments in that subreddit ever mentioned a word (sweden in this case)
This means that not one comment for the entire day mentioned sweden in these subreddits
Approximate Data Usage: 400 Megabytes
SELECT t1.count, t1.subreddit FROM
(SELECT count(*) count, subreddit FROM [pushshift:rt_reddit.comments@-86400000-]
WHERE NOT REGEXP_MATCH(lower(body),r'sweden')
GROUP BY 2) t1
JOIN EACH
(SELECT count(*) count, subreddit FROM [pushshift:rt_reddit.comments@-86400000-]
GROUP BY 2) t2
ON t1.subreddit = t2.subreddit
WHERE t1.count = t2.count
ORDER BY t1.count DESC
| Count | Subreddit |
|---|---|
| 29907 | darksouls3 |
| 20264 | movies |
| 19403 | nfl |
| 14411 | SquaredCircle |
| 13350 | GlobalOffensiveTrade |
| 12436 | relationships |
| 12250 | DestinyTheGame |
| 11790 | hearthstone |
| 6615 | Eve |
Posted Apr 15, 2016, 2:17 AM UTC · edited