college_ingest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ingest data from college dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset_id = \"college_staging\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BigQuery error in mk operation: Dataset 'cs327e-sp2020:college_staging' already\n",
      "exists.\n"
     ]
    }
   ],
   "source": [
    "!bq --location=US mk --dataset {dataset_id}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### creating tables and loading data into tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r311507a717a547be_000001700db9b8d4_1 ... (3s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Classes 'gs://college_data_2020/Classes.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r540c74fddaa32f46_000001700dbbe6d4_1 ... (1s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.Current_Students 'gs://college_data_2020/Current_Students.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r3c67ae9c772108d5_000001700dbc3c29_1 ... (1s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "!bq --location=US load --autodetect --skip_leading_rows=1 \\\n",
    "--source_format=CSV {dataset_id}.New_Students 'gs://college_data_2020/New_Students.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### get record count for all 3 tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0     13"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "select count(*) as count from college_staging.Classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0     13"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "select count(*) as count from college_staging.Current_Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0      7"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "select count(*) as count from college_staging.New_Students"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### read sample records from tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tid</th>\n",
       "      <th>instructor</th>\n",
       "      <th>dept</th>\n",
       "      <th>cno</th>\n",
       "      <th>cname</th>\n",
       "      <th>credits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fares</td>\n",
       "      <td>Fares Fraij</td>\n",
       "      <td>CS</td>\n",
       "      <td>CS331E</td>\n",
       "      <td>Elements of Software Engineering</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>abraham</td>\n",
       "      <td>Prof. Sarah Abraham</td>\n",
       "      <td>CS</td>\n",
       "      <td>CS329E</td>\n",
       "      <td>Elements of Mobile Computing</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mitra</td>\n",
       "      <td>Mitra, Shyamal</td>\n",
       "      <td>CS</td>\n",
       "      <td>CS313E</td>\n",
       "      <td>Elements of Software Design</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>cannata</td>\n",
       "      <td>PHILIP CANNATA</td>\n",
       "      <td>CS</td>\n",
       "      <td>CS326E</td>\n",
       "      <td>Elements of Networking</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>cannata</td>\n",
       "      <td>Cannata, Philip</td>\n",
       "      <td>CS</td>\n",
       "      <td>CS347</td>\n",
       "      <td>Data Management</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       tid           instructor dept     cno  \\\n",
       "0    fares          Fares Fraij   CS  CS331E   \n",
       "1  abraham  Prof. Sarah Abraham   CS  CS329E   \n",
       "2    mitra       Mitra, Shyamal   CS  CS313E   \n",
       "3  cannata       PHILIP CANNATA   CS  CS326E   \n",
       "4  cannata      Cannata, Philip   CS   CS347   \n",
       "\n",
       "                              cname  credits  \n",
       "0  Elements of Software Engineering        3  \n",
       "1      Elements of Mobile Computing        3  \n",
       "2       Elements of Software Design        3  \n",
       "3            Elements of Networking        3  \n",
       "4                   Data Management        3  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "select * from college_staging.Classes\n",
    "limit 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>fname</th>\n",
       "      <th>lname</th>\n",
       "      <th>dob</th>\n",
       "      <th>cno</th>\n",
       "      <th>cname</th>\n",
       "      <th>credits</th>\n",
       "      <th>grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>kev18</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>Lin</td>\n",
       "      <td>05/10/1999</td>\n",
       "      <td>CS329E</td>\n",
       "      <td>Elements of Web Programming</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>paulg</td>\n",
       "      <td>Paul</td>\n",
       "      <td>Gore</td>\n",
       "      <td>2000-09-17</td>\n",
       "      <td>CS329E</td>\n",
       "      <td>Elements of Web Programming</td>\n",
       "      <td>3</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>paulg</td>\n",
       "      <td>Paul</td>\n",
       "      <td>Gore</td>\n",
       "      <td>2000-09-17</td>\n",
       "      <td>CS326E - Fall18</td>\n",
       "      <td>Elements of Networking</td>\n",
       "      <td>3</td>\n",
       "      <td>A-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>paulg</td>\n",
       "      <td>Paul</td>\n",
       "      <td>Gore</td>\n",
       "      <td>09/17/2000</td>\n",
       "      <td>CS313E</td>\n",
       "      <td>Elements of Software Design</td>\n",
       "      <td>3</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bzen26</td>\n",
       "      <td>Biswa</td>\n",
       "      <td>Zen</td>\n",
       "      <td>04/22/1998</td>\n",
       "      <td>CS313E - Fall18</td>\n",
       "      <td>Elements of Software Design</td>\n",
       "      <td>3</td>\n",
       "      <td>B+</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sid  fname lname         dob              cno  \\\n",
       "0   kev18  Kevin   Lin  05/10/1999           CS329E   \n",
       "1   paulg   Paul  Gore  2000-09-17           CS329E   \n",
       "2   paulg   Paul  Gore  2000-09-17  CS326E - Fall18   \n",
       "3   paulg   Paul  Gore  09/17/2000           CS313E   \n",
       "4  bzen26  Biswa   Zen  04/22/1998  CS313E - Fall18   \n",
       "\n",
       "                         cname  credits grade  \n",
       "0  Elements of Web Programming        3  None  \n",
       "1  Elements of Web Programming        3     A  \n",
       "2       Elements of Networking        3    A-  \n",
       "3  Elements of Software Design        3  None  \n",
       "4  Elements of Software Design        3    B+  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "select * from college_staging.Current_Students\n",
    "limit 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>fname</th>\n",
       "      <th>lname</th>\n",
       "      <th>dob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>aprilz</td>\n",
       "      <td>April</td>\n",
       "      <td>Lopez</td>\n",
       "      <td>2000-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sudeepa4</td>\n",
       "      <td>Sudeepa</td>\n",
       "      <td>Roy</td>\n",
       "      <td>2000-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ryan22</td>\n",
       "      <td>Ryan</td>\n",
       "      <td>Watson</td>\n",
       "      <td>2000-11-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dchen</td>\n",
       "      <td>David</td>\n",
       "      <td>Chen</td>\n",
       "      <td>2000-08-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>natea</td>\n",
       "      <td>Nathan</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>2000-04-08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        sid    fname     lname         dob\n",
       "0    aprilz    April     Lopez  2000-01-10\n",
       "1  sudeepa4  Sudeepa       Roy  2000-10-01\n",
       "2    ryan22     Ryan    Watson  2000-11-07\n",
       "3     dchen    David      Chen  2000-08-22\n",
       "4     natea   Nathan  Anderson  2000-04-08"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "select * from college_staging.New_Students\n",
    "limit 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}