bigquery_data_extraction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Format the Google BigQuery query\n",
    "\n",
    "It's strongly recommendeded that you first test your query in Google's BigQuery Sandbox (online). It returns results ~100 times faster, will yell at you pre-runtime if you introduce syntax errors, and it also shows a preview of expected data usage for the given query.\n",
    "\n",
    "TODO: extend query to include Oct/Nov/Dec 2021, not just 2022+ [completed in data dump version]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "QUERY = \"\"\"\n",
    "SELECT\n",
    "  q.id,\n",
    "  q.title,\n",
    "  q.body,\n",
    "  q.accepted_answer_id,\n",
    "  q.view_count,\n",
    "  q.tags,\n",
    "  q.answer_count,\n",
    "  q.score AS question_score,\n",
    "  a.score AS answer_score,\n",
    "  a.body AS stackoverflow_answer\n",
    "FROM\n",
    "  bigquery-public-data.stackoverflow.posts_questions q\n",
    "LEFT JOIN\n",
    "  bigquery-public-data.stackoverflow.posts_answers a\n",
    "ON\n",
    "  q.accepted_answer_id = a.id\n",
    "WHERE\n",
    "  q.answer_count > 0\n",
    "  AND q.accepted_answer_id > 0\n",
    "  AND EXTRACT(YEAR FROM q.creation_date) >= 2022\n",
    "  AND a.score >= 0\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fetch dataset using Google BigQuery\n",
    "\n",
    "Authentication info: https://googleapis.dev/python/google-api-core/latest/auth.html (the query will fail if you don't set up authentication first).\n",
    "\n",
    "To use server-side sampling with BigQuery (culls data server-side, reducing download size), use `TABLESAMPLE SYSTEM` e.g.:\n",
    "\n",
    "`bigquery-public-data.stackoverflow.posts_questions q TABLESAMPLE SYSTEM (10 PERCENT)`\n",
    "\n",
    "`bigquery-public-data.stackoverflow.posts_answers a TABLESAMPLE SYSTEM (50 PERCENT)`\n",
    "\n",
    "(be aware that using less than 100% for both of questions and answers can result in no matches between the two if unlucky with rng, causing the query to return no results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install google-cloud-bigquery\n",
    "%pip install db-dtypes\n",
    "\n",
    "import db_dtypes\n",
    "import json\n",
    "import os\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "from google.cloud import bigquery as bq\n",
    "\n",
    "# Get project name from local secrets.json file\n",
    "def load_project_ID(secrets_file=\"secrets.json\"):\n",
    "    with open(secrets_file) as f:\n",
    "        secrets = json.load(f)\n",
    "    return secrets[\"BQ_PROJECT_ID\"]\n",
    "\n",
    "# Load a saved copy of the dataset from local disk (if it exists), otherwise run query and save it\n",
    "DATASET_FILE = \"saved_dataset.csv\"      # The file name of the saved dataset (saved on / loaded from local disk)\n",
    "cwd = Path().absolute()                 # Current working directory (note: possibly different from execution directory)\n",
    "\n",
    "# See if we have a copy of the dataset on local disk; if we do load that\n",
    "try:\n",
    "    dataset_path = os.path.join(cwd, DATASET_FILE)\n",
    "    results = pd.read_csv(dataset_path)\n",
    "    print(\"Saved copy of dataset loaded from local disk.\")\n",
    "\n",
    "# If not, run the query (tested runtime was up to 1 hour for full query!)\n",
    "except FileNotFoundError:\n",
    "    print(\"Saved copy of dataset (\" + DATASET_FILE + \") not found - running query...\")\n",
    "\n",
    "    # prepare query\n",
    "    client = bq.Client(project=load_project_ID())\n",
    "\n",
    "    # run the query and save result to a dataframe\n",
    "    results = client.query(QUERY.format(offset=0)).result().to_dataframe()\n",
    "    print(\"Dataset has been downloaded...\")\n",
    "\n",
    "    # Save the dataframe for persistent copy on local storage\n",
    "    results.to_csv(DATASET_FILE)\n",
    "    print(\"Dataset has been saved to local storage. File name: \" + DATASET_FILE)\n",
    "\n",
    "# Yell if there's an *unexpected* error\n",
    "except:\n",
    "    raise Exception(\"Error while loading dataset.\")\n",
    "\n",
    "# Dump some extra info\n",
    "print(\"Number of questions:\", len(results))\n",
    "results.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "testenv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}