BigQuery Reddit数据集：帖子表与评论表的关联方式技术问询

Hey there! Let's walk through how to properly join Reddit's posts (submissions) and comments tables in BigQuery—this trip-up is super common because the linking fields aren't a direct match at first glance.

Key Association Logic #

First, let's clarify the fields that connect the two tables:

• In the posts table (typically bigquery-public-data.reddit.submissions, or year-specific tables like fh-bigquery.reddit_posts.2024), the id field is the unique identifier for each post (e.g., k1b3xq).
• In the comments table (bigquery-public-data.reddit.comments or fh-bigquery.reddit_comments.2024), the link_id field points to the parent post—but it includes a Reddit type prefix: t3_<post_id> (the t3_ denotes a post/submission).

How to Join Them #

To link the tables, you need to strip the t3_ prefix from link_id so it matches the post's id field. You have two efficient ways to do this:

Option 1: Using SUBSTR (Faster for Large Datasets)

Since the prefix is exactly 3 characters long, you can extract everything starting from the 4th character:

SELECT
  -- Post details
  s.id AS post_id,
  s.title AS post_title,
  s.subreddit AS post_subreddit,
  s.author AS post_author,
  -- Comment details
  c.id AS comment_id,
  c.body AS comment_content,
  c.author AS comment_author,
  c.created_utc AS comment_timestamp
FROM
  `bigquery-public-data.reddit.submissions` s
-- Use LEFT JOIN if you want to include posts with no comments
INNER JOIN
  `bigquery-public-data.reddit.comments` c
ON
  s.id = SUBSTR(c.link_id, 4)
-- Add filters to reduce data scanned (critical for large datasets!)
WHERE
  s.subreddit = 'machinelearning'
  AND s.created_utc >= TIMESTAMP('2024-01-01')
LIMIT 200;

Option 2: Using REGEXP_REPLACE (More Flexible)

If you want to handle any potential prefix variations (though t3_ is standard for posts), use regex to remove the prefix:

SELECT
  s.id AS post_id,
  s.title AS post_title,
  c.id AS comment_id,
  c.body AS comment_content
FROM
  `bigquery-public-data.reddit.submissions` s
LEFT JOIN
  `bigquery-public-data.reddit.comments` c
ON
  s.id = REGEXP_REPLACE(c.link_id, r'^t3_', '')
WHERE
  s.created_utc BETWEEN TIMESTAMP('2023-06-01') AND TIMESTAMP('2023-06-30')
LIMIT 100;

Pro Tips #

• Dataset Consistency: If you're using historical archived tables (like those in fh-bigquery), make sure you're joining the same year/month tables for posts and comments to avoid mismatches.
• Cost & Performance: Reddit's dataset is massive—always add WHERE clauses to filter by subreddit, date range, or other criteria to minimize the amount of data scanned.
• Join Type: Use INNER JOIN if you only want posts that have at least one comment, or LEFT JOIN if you want to include posts with no comments (with comment fields showing as NULL).

内容的提问来源于stack exchange，提问作者Nick Gideo