01_load_data_from_bigquery.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyPwRiUH6+1baAn6Bo/4j7pR",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/MIT-LCP/datathon-gossis/blob/main/01_load_data_from_bigquery.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Notebook 1: Loading data from BigQuery\n",
        "\n",
        "The aim of this notebook is to demonstrate how to load data hosted on BigQuery (a Google database engine)."
      ],
      "metadata": {
        "id": "6_XXgy_gjgnl"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Prerequisites\n",
        "\n",
        "- If you do not have a Gmail account, please create one at http://www.gmail.com.\n",
        "- If you have not yet signed the data use agreement (DUA) sent by the organizers, please do so now to get access to the dataset."
      ],
      "metadata": {
        "id": "3QJ5WvM9j0rl"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Load libraries and connect to the data\n",
        "\n",
        "Run the following cells to load the Google Cloud libraries needed to connect to the database."
      ],
      "metadata": {
        "id": "fibnXVVkj6le"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Access data using Google BigQuery.\n",
        "from google.colab import auth\n",
        "from google.cloud import bigquery\n",
        "import os"
      ],
      "metadata": {
        "id": "x0ZVSJFZj_za"
      },
      "execution_count": 16,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Before running any queries, you need to first authenticate with Google Cloud. You will be asked to log in using your Gmail account. You will be given a string of verification code, which you will copy and paste into the notebook."
      ],
      "metadata": {
        "id": "53V7iB9WkKDq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "auth.authenticate_user()"
      ],
      "metadata": {
        "id": "1yuPbvkbkheH"
      },
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "We'll also set the project details (used for billing)"
      ],
      "metadata": {
        "id": "uzM_VdG7km96"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "project_id='gossis-datathon'\n",
        "os.environ[\"GOOGLE_CLOUD_PROJECT\"]=project_id"
      ],
      "metadata": {
        "id": "CRh8Y0KlkkUV"
      },
      "execution_count": 18,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# \"Querying\" our database with SQL\n",
        "\n",
        "Now we can start exploring the data. We'll begin by running a simple query to load all columns of the `patient` table to a Pandas DataFrame. The query is written in SQL, a common language for extracting data from databases. The structure of an SQL query is:\n",
        "\n",
        "```sql\n",
        "SELECT <columns>\n",
        "FROM <table>\n",
        "WHERE <criteria, optional>\n",
        "```\n",
        "\n",
        "`*` is a wildcard that indicates all columns"
      ],
      "metadata": {
        "id": "C9xli8l6kvPj"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# BigQuery\n",
        "\n",
        "Our dataset is stored on BigQuery, Google's database engine. We can run our query on the database using some special (\"magic\") [BigQuery syntax](https://googleapis.dev/python/bigquery/latest/magics.html)."
      ],
      "metadata": {
        "id": "K6NjczHXkz-B"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%bigquery patient --project physionet-data\n",
        "\n",
        "SELECT *\n",
        "FROM `physionet-data.widsdatathon2020.training_v2`"
      ],
      "metadata": {
        "id": "SZxisphFkxWW"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "We have now assigned the output to our query to a variable called `patient`. Let's use the `head` method to view the first few rows of our data."
      ],
      "metadata": {
        "id": "zUYNQgXhk_Eq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# view the top few rows of the patient data\n",
        "patient.head()"
      ],
      "metadata": {
        "id": "8QTEN1YblAui"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}