ETL_GCP_initial.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "cannot import name 'collections_abc' from 'six.moves' (unknown location)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-8-14293adf6bd3>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mClient\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/__init__.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     33\u001b[0m \u001b[0m__version__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_distribution\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"google-cloud-bigquery\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mversion\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     34\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 35\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclient\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mClient\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     36\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mAccessEntry\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     37\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDataset\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     56\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_helpers\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0m_verify_job_config_type\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     57\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_http\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mConnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 58\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0m_pandas_helpers\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     59\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDataset\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     60\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDatasetListItem\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/_pandas_helpers.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     38\u001b[0m     \u001b[0mpyarrow\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     39\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 40\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     41\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     42\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/schema.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     15\u001b[0m \u001b[0;34m\"\"\"Schemas for BigQuery tables / queries.\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     16\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 17\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0msix\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmoves\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mcollections_abc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     18\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     19\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery_v2\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mtypes\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mImportError\u001b[0m: cannot import name 'collections_abc' from 'six.moves' (unknown location)"
     ]
    }
   ],
   "source": [
    "# https://cloud.google.com/python/setup\n",
    "# pip install --upgrade google-cloud-bigquery\n",
    "\n",
    "# import sys\n",
    "# !{sys.executable} -m pip install google-cloud-bigquery\n",
    "\n",
    "import os\n",
    "from google.cloud.bigquery import Client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def initialize_connection_to_GCP():\n",
    "    os.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = os.path.expanduser('~/.credentials/MyGCPCredentials.json')\n",
    "    client = bigquery.Client()\n",
    "    project_id = 'calotrack-1050-final'\n",
    "    dataset_id = 'nutrition'\n",
    "    branded_table_id = 'branded_table'\n",
    "    common_table_id = 'common_table'\n",
    "    exercise_table_id = 'exercise_table'\n",
    "    return client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "CREATING DATASET \n",
    "https://cloud.google.com/bigquery/docs/datasets\n",
    "Only run once to create the nutrition dataset that houses the tables. \n",
    "\"\"\"\n",
    "def create_dataset():\n",
    "    dataset_id = \"{}.nutrition\".format(client.project)\n",
    "    dataset = bigquery.Dataset(dataset_id)\n",
    "    dataset.location = \"US\"\n",
    "    dataset = client.create_dataset(dataset) #make an API request\n",
    "    print(\"Created dataset {}.{}\".format(client.project, dataset.dataset_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Creating a table\n",
    "https://cloud.google.com/bigquery/docs/schemas\n",
    "https://cloud.google.com/bigquery/docs/tables\n",
    "\"\"\"\n",
    "\n",
    "def make_branded_table():\n",
    "    table_id = \"calotrack-1050-final.nutrition.branded_table\"\n",
    "\n",
    "    schema = [\n",
    "        bigquery.SchemaField(\"food_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"brand_name_item_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"brand_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"serving_unit\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"serving_qty\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"nf_calories\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    ]\n",
    "\n",
    "    table = bigquery.Table(table_id, schema=schema)\n",
    "    table = client.create_table(table)  # Make an API request.\n",
    "    print(\n",
    "        \"Created table {}.{}.{}\".format(table.project, table.dataset_id, table.table_id)\n",
    "    )\n",
    "    \n",
    "def make_common_table():\n",
    "    table_id = \"calotrack-1050-final.nutrition.common_table\"\n",
    "\n",
    "    schema = [\n",
    "        bigquery.SchemaField(\"food_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"tag_name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"serving_unit\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"serving_qty\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"nf_calories\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    ]\n",
    "\n",
    "    table = bigquery.Table(table_id, schema=schema)\n",
    "    table = client.create_table(table)  # Make an API request.\n",
    "    print(\n",
    "        \"Created table {}.{}.{}\".format(table.project, table.dataset_id, table.table_id)\n",
    "    )\n",
    "    \n",
    "def make_exercise_table():\n",
    "    table_id = \"calotrack-1050-final.nutrition.exercise_table\"\n",
    "\n",
    "    schema = [\n",
    "        bigquery.SchemaField(\"name\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"duration_min\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"met\", \"STRING\", mode=\"REQUIRED\"),\n",
    "        bigquery.SchemaField(\"nf_calories\", \"STRING\", mode=\"REQUIRED\"),\n",
    "    ]\n",
    "\n",
    "    table = bigquery.Table(table_id, schema=schema)\n",
    "    table = client.create_table(table)  # Make an API request.\n",
    "    print(\n",
    "        \"Created table {}.{}.{}\".format(table.project, table.dataset_id, table.table_id)\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "Uploading the data to the table\n",
    "https://cloud.google.com/bigquery/docs/loading-data-local#loading_data_from_a_local_data_source\n",
    "\n",
    "csv_name = local path to csv (e.g. 'exercises.csv')\n",
    "table_id = string of table id (e.g. 'exercise_table')\n",
    "\"\"\"\n",
    "def upload_csv(csv_name, table_id):\n",
    "    filename = '/home/jovyan/data1050/1050-final/' + csv_name\n",
    "    dataset_id = 'nutrition'\n",
    "    table_id = table_id\n",
    "\n",
    "    dataset_ref = client.dataset(dataset_id)\n",
    "    table_ref = dataset_ref.table(table_id)\n",
    "    job_config = bigquery.LoadJobConfig()\n",
    "    job_config.source_format = bigquery.SourceFormat.CSV\n",
    "    # job_config.skip_leading_rows = 1\n",
    "    # job_config.autodetect = True\n",
    "\n",
    "\n",
    "    with open(filename, \"rb\") as source_file:\n",
    "        job = client.load_table_from_file(source_file, table_ref, job_config=job_config)\n",
    "\n",
    "    job.result()  # Waits for table load to complete.\n",
    "\n",
    "    print(\"Loaded {} rows into {}:{}.\".format(job.output_rows, dataset_id, table_id))\n",
    "    # see errors in job.errors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "food_name : nf_calories calories\n",
      "food_name : nf_calories calories\n",
      "food_name : nf_calories calories\n",
      "Parma Hash : 990.0 calories\n",
      "Eggstravaganza : 990.0 calories\n",
      "Calzone Carne Piccante : 970.0 calories\n",
      "Spicy Jambalaya : 970.0 calories\n",
      "Pancakes - Banana : 970.0 calories\n",
      "Lamb Rogan Josh : 960.0 calories\n",
      "Pancakes - Chocolate : 960.0 calories\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "EXAMPLE QUERY\n",
    "https://cloud.google.com/bigquery/docs/bigquery-storage-python-pandas\n",
    "\"\"\"\n",
    "client = initialize_connection_to_GCP()\n",
    "query_job = client.query(\"\"\"\n",
    "    SELECT *\n",
    "    FROM `calotrack-1050-final.nutrition.branded_table`\n",
    "    ORDER BY nf_calories DESC\n",
    "    LIMIT 10\"\"\")\n",
    "\n",
    "results = query_job.result()  # Waits for job to complete.\n",
    "\n",
    "for row in results:\n",
    "    print(\"{} : {} calories\".format(row.food_name, row.nf_calories))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "cannot import name 'collections_abc' from 'six.moves' (unknown location)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mImportError\u001b[0m                               Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-9d0d9eabffde>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mETL\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/data1050/calotrack-1050-final/ETL.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mfunctools\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mlogging\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0;32mimport\u001b[0m \u001b[0mETL_GCP\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mimportlib\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/data1050/calotrack-1050-final/ETL_GCP.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m# pip install --upgrade google-cloud-bigquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mos\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mbigquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mstorage\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/__init__.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     33\u001b[0m \u001b[0m__version__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_distribution\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"google-cloud-bigquery\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mversion\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     34\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 35\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclient\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mClient\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     36\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mAccessEntry\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     37\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDataset\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     56\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_helpers\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0m_verify_job_config_type\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     57\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_http\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mConnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 58\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0m_pandas_helpers\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     59\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDataset\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     60\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdataset\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mDatasetListItem\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/_pandas_helpers.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     38\u001b[0m     \u001b[0mpyarrow\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     39\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 40\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     41\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     42\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/schema.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m     15\u001b[0m \u001b[0;34m\"\"\"Schemas for BigQuery tables / queries.\"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     16\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 17\u001b[0;31m \u001b[0;32mfrom\u001b[0m \u001b[0msix\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmoves\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mcollections_abc\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     18\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     19\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mgoogle\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcloud\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbigquery_v2\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mtypes\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mImportError\u001b[0m: cannot import name 'collections_abc' from 'six.moves' (unknown location)"
     ]
    }
   ],
   "source": [
    "import ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'ETL' from '/home/jovyan/data1050/1050-final/ETL.py'>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import importlib\n",
    "importlib.reload(ETL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'exercises': [{'tag_id': 750,\n",
       "   'user_input': 'ballet',\n",
       "   'duration_min': 15,\n",
       "   'met': 7.8,\n",
       "   'nf_calories': 136.5,\n",
       "   'photo': {'highres': 'https://d2xdmhkmkbyw75.cloudfront.net/exercise/750_highres.jpg',\n",
       "    'thumb': 'https://d2xdmhkmkbyw75.cloudfront.net/exercise/750_thumb.jpg',\n",
       "    'is_user_uploaded': False},\n",
       "   'compendium_code': 3031,\n",
       "   'name': 'dancing',\n",
       "   'description': None,\n",
       "   'benefits': None}]}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ETL.search_exercises('ballet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "DEBUG:root:List of foods:\n",
      "DEBUG:root:['haggis']\n",
      "DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): trackapi.nutritionix.com:443\n",
      "DEBUG:urllib3.connectionpool:https://trackapi.nutritionix.com:443 \"POST /v2/search/instant HTTP/1.1\" 200 906\n"
     ]
    }
   ],
   "source": [
    "a = ETL.search_foods('haggis')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): trackapi.nutritionix.com:443\n",
      "DEBUG:urllib3.connectionpool:https://trackapi.nutritionix.com:443 \"POST /v2/natural/exercise HTTP/1.1\" 200 286\n",
      "DEBUG:root:UPDATING EXERCISE TABLE...\n",
      "DEBUG:urllib3.util.retry:Converted retries value: 3 -> Retry(total=3, connect=None, read=None, redirect=None, status=None)\n",
      "DEBUG:google.auth.transport.requests:Making request: POST https://oauth2.googleapis.com/token\n",
      "DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): oauth2.googleapis.com:443\n",
      "DEBUG:urllib3.connectionpool:https://oauth2.googleapis.com:443 \"POST /token HTTP/1.1\" 200 None\n",
      "DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): storage.googleapis.com:443\n",
      "DEBUG:urllib3.connectionpool:https://storage.googleapis.com:443 \"GET /storage/v1/b/calotrack-data?projection=noAcl HTTP/1.1\" 200 639\n",
      "DEBUG:urllib3.connectionpool:https://storage.googleapis.com:443 \"POST /upload/storage/v1/b/calotrack-data/o?uploadType=multipart HTTP/1.1\" 200 754\n",
      "DEBUG:root:File /home/jovyan/data1050/1050-final/update_data/exercise_to_add.csv uploaded to exercise_to_add.csv.\n",
      "DEBUG:urllib3.util.retry:Converted retries value: 3 -> Retry(total=3, connect=None, read=None, redirect=None, status=None)\n",
      "DEBUG:google.auth.transport.requests:Making request: POST https://oauth2.googleapis.com/token\n",
      "DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): oauth2.googleapis.com:443\n",
      "DEBUG:urllib3.connectionpool:https://oauth2.googleapis.com:443 \"POST /token HTTP/1.1\" 200 None\n",
      "DEBUG:urllib3.connectionpool:Starting new HTTPS connection (1): bigquery.googleapis.com:443\n",
      "DEBUG:urllib3.connectionpool:https://bigquery.googleapis.com:443 \"POST /bigquery/v2/projects/calotrack-1050-final/jobs HTTP/1.1\" 200 None\n",
      "DEBUG:root:Starting job 1c0ad0fb-97ed-4ca0-b4ae-d981840007ec\n",
      "DEBUG:urllib3.connectionpool:https://bigquery.googleapis.com:443 \"GET /bigquery/v2/projects/calotrack-1050-final/jobs/1c0ad0fb-97ed-4ca0-b4ae-d981840007ec?location=US HTTP/1.1\" 200 None\n",
      "DEBUG:google.api_core.retry:Retrying due to , sleeping 1.0s ...\n",
      "DEBUG:urllib3.connectionpool:https://bigquery.googleapis.com:443 \"GET /bigquery/v2/projects/calotrack-1050-final/jobs/1c0ad0fb-97ed-4ca0-b4ae-d981840007ec?location=US HTTP/1.1\" 200 None\n",
      "DEBUG:google.api_core.retry:Retrying due to , sleeping 2.3s ...\n",
      "DEBUG:urllib3.connectionpool:https://bigquery.googleapis.com:443 \"GET /bigquery/v2/projects/calotrack-1050-final/jobs/1c0ad0fb-97ed-4ca0-b4ae-d981840007ec?location=US HTTP/1.1\" 200 None\n",
      "DEBUG:root:Job finished.\n",
      "DEBUG:urllib3.connectionpool:https://bigquery.googleapis.com:443 \"GET /bigquery/v2/projects/calotrack-1050-final/datasets/nutrition/tables/exercise_table HTTP/1.1\" 200 None\n",
      "DEBUG:root:Loaded 46 rows.\n"
     ]
    }
   ],
   "source": [
    "ETL.user_exercise_query('crossfit, cleaning')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## In response to project being suspended\n",
    "\n",
    "Preparing to make an authorized API call\n",
    "After you obtain the client email address and private key from the API Console, complete the following steps:\n",
    "\n",
    "Install the required libraries:\n",
    "\n",
    "pip install google-auth google-auth-httplib2 google-api-python-client\n",
    "Create a Credentials object from the service account's credentials and the scopes your application needs access to. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "calotrack_venv",
   "language": "python",
   "name": "calotrack_venv"
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}