GoogleBigQueryTests-generic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BigQuery Public Datasets Tests and Samples"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Notebook to demonstrate ability to run queries on public datasets.\n",
    "\n",
    "Assumes all steps in GoogleConnectandTests notebook have been completed\n",
    "\n",
    "## Start by setting variables and importing bigquery package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Must be run again after restarting kernel\n",
    "import os\n",
    "os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"]=\"/<path-to-your-key>/<keyname>.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.cloud import bigquery  #enable BigQuery - rerun at kernel restart"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test to see if we can connect to a public dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tables contained in 'bigquery-public-data.covid19_aha':\n",
      "bigquery-public-data.covid19_aha.hospital_beds\n",
      "bigquery-public-data.covid19_aha.staffing\n"
     ]
    }
   ],
   "source": [
    "dataset_id = 'bigquery-public-data.covid19_aha' #specify the dataset you want to work with,located in bigquery-public-data \n",
    "\n",
    "client = bigquery.Client()\n",
    "\n",
    "tables = client.list_tables(dataset_id)  # Make an API request.\n",
    "\n",
    "print(\"Tables contained in '{}':\".format(dataset_id))\n",
    "for table in tables:\n",
    "    print(\"{}.{}.{}\".format(table.project, table.dataset_id, table.table_id))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get Column Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "county_fips_code \n",
      "county_name \n",
      "state_name \n",
      "cbsa_code \n",
      "total_hospital_beds \n",
      "acute_long_term_care_beds \n",
      "alcohol_drug_abuse_dependency_inpatient_care_beds \n",
      "burn_care_beds \n",
      "cardiac_intensive_care_beds \n",
      "gen_medical_surgical_adult_beds \n",
      "gen_medical_surgical_pediatric_beds \n",
      "intermediate_nursing_care_beds \n",
      "medical_surgical_intensive_care_beds \n",
      "neonatal_intensive_care_beds \n",
      "neonatal_intermediate_care_beds \n",
      "obstetric_care_beds \n",
      "other_care_beds \n",
      "other_intensive_care_beds \n",
      "other_long_term_care_beds \n",
      "other_special_care_beds \n",
      "pediatric_intensive_care_beds \n",
      "physical_rehabilitation_care_beds \n",
      "psychiatric_care_beds \n",
      "skilled_nursing_care_beds \n",
      "num_airborne_infection_isolation_rooms \n"
     ]
    }
   ],
   "source": [
    "query_job = client.query(\n",
    "    \"\"\"\n",
    "   SELECT * FROM `bigquery-public-data.covid19_aha.INFORMATION_SCHEMA.COLUMNS` WHERE table_name='hospital_beds'\"\"\"  #Limit here is just for display purposes. This can also be viewed in the GCP Console\n",
    ")\n",
    "results = query_job.result()  # Waits for job to complete.\n",
    "\n",
    "for row in results:\n",
    "    print(\"{} \".format(row.column_name))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run a query on the public dataset and print the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Windsor County : Vermont : 70 \n",
      "Apache County : Arizona : 143 \n",
      "Iberville Parish : Louisiana : 8 \n",
      "Adair County : Oklahoma : 34 \n",
      "Graves County : Kentucky : 227 \n",
      "Platte County : Nebraska : 51 \n",
      "Lincoln Parish : Louisiana : 177 \n",
      "Lawrence County : Tennessee : 99 \n",
      "Geneva County : Alabama : 147 \n",
      "Hamilton County : Illinois : 25 \n"
     ]
    }
   ],
   "source": [
    "query_job = client.query(\n",
    "    \"\"\"\n",
    "   SELECT county_name, state_name, total_hospital_beds FROM `bigquery-public-data.covid19_aha.hospital_beds` LIMIT 10\"\"\"\n",
    ")\n",
    "results = query_job.result()  # Waits for job to complete.\n",
    "\n",
    "for row in results:\n",
    "    print(\"{} : {} : {} \".format(row.county_name, row.state_name, row.total_hospital_beds))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# We have now successfully connected to and queried a public dataset using BigQuery."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "ename": "AssertionError",
     "evalue": "DO NOT REMOVE THIS LINE",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAssertionError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-13-cef8011cb395>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32massert\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"DO NOT REMOVE THIS LINE\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mAssertionError\u001b[0m: DO NOT REMOVE THIS LINE"
     ]
    }
   ],
   "source": [
    "assert False, \"DO NOT REMOVE THIS LINE\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[main a4d4b63] Added connect to, describe, and query a public dataset\n",
      " 2 files changed, 261 insertions(+), 6 deletions(-)\n",
      " create mode 100644 GoogleBigQueryTests-generic.ipynb\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "To github.com:jasondeden/GCP-Jupyter.git\n",
      "   8039cfa..a4d4b63  main -> main\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "git add GoogleBigQueryTests-generic.ipynb\n",
    "git commit -a -m \"Added connect to, describe, and query a public dataset\"\n",
    "git push"
   ]
  },
  {
   "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.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}