{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "import bq_helper\n",
    "# create a helper object for our bigquery dataset\n",
    "chicago_crime = bq_helper.BigQueryHelper(active_project= \"bigquery-public-data\", \n",
    "                                       dataset_name = \"chicago_crime\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [],
   "source": [
    "chicago_crime.list_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "0eab1a310dc8431b3fff2faf50712664ca3bacac"
   },
   "outputs": [],
   "source": [
    "chicago_crime.table_schema(\"crime\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "356755f66a9d35b683de97b198d485c5fc86cb53"
   },
   "outputs": [],
   "source": [
    "chicago_crime.head(\"crime\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "b62b9026a0f6b99b4b0c907fa327cc465d46f785"
   },
   "outputs": [],
   "source": [
    "chicago_crime.head(\"crime\", selected_columns=\"iucr\", num_rows=10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "a2fb7a60fc2e70e892bf09d2b24876094ed1f47d"
   },
   "outputs": [],
   "source": [
    "# What's up with the triple quotation marks (\"\"\")? \n",
    "# These tell Python that everything inside them is a single string, even though we have line breaks in it. \n",
    "query = \"\"\"SELECT case_number\n",
    "            FROM `bigquery-public-data.chicago_crime.crime`\n",
    "            WHERE primary_type = \"ROBBERY\" \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "66bb1819f2cbf49dd7e57ccc0265fc07a2997b7d"
   },
   "outputs": [],
   "source": [
    "chicago_crime.estimate_query_size(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "5153f1cdd7145196ca8402c89cb121d7386f5b65"
   },
   "outputs": [],
   "source": [
    "chicago_crime.query_to_pandas_safe(query, max_gb_scanned=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "2358d153d64875d8ddb641b4c91118d3a417491c"
   },
   "outputs": [],
   "source": [
    "query2 = \"\"\"SELECT case_number\n",
    "            FROM `bigquery-public-data.chicago_crime.crime`\n",
    "            WHERE primary_type = \"ROBBERY\" AND arrest = TRUE \"\"\"\n",
    "chicago_crime.query_to_pandas_safe(query2, max_gb_scanned=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "f739bc56f4ec5f96a08e411cd9f4680679960216"
   },
   "outputs": [],
   "source": [
    "chicago_crime.estimate_query_size(query2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "0a94ebbfa93630103eceb614de393b661136f061"
   },
   "outputs": [],
   "source": [
    "query2 = \"\"\"SELECT case_number\n",
    "            FROM `bigquery-public-data.chicago_crime.crime`\n",
    "            WHERE primary_type = \"ROBBERY\" AND arrest = TRUE; \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "f638b22a7f68b5e065e4144ee476e109d92291cd"
   },
   "outputs": [],
   "source": [
    "chicago_crime.query_to_pandas_safe(query2, max_gb_scanned=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "c8a26dbde0948bb08560ecdae6f8d092b79dbf87"
   },
   "outputs": [],
   "source": [
    "chicago_crime.estimate_query_size(query2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "dc9269f03cffe8b073a735445c58c8b39b9dc37d"
   },
   "outputs": [],
   "source": [
    "query2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "638cbed70e58edb4c36521234bc41f2d4eae33f3"
   },
   "outputs": [],
   "source": [
    "# create a helper object for this dataset\n",
    "open_aq = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n",
    "                                   dataset_name=\"openaq\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "f99aa721902a11d99a2ccd4abdf59e30c8a22989"
   },
   "outputs": [],
   "source": [
    "# print all the tables in this dataset (there's only one!)\n",
    "chicago_crime.list_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "24c888a2e1127b8d04c851ce09ffca63b5202874"
   },
   "outputs": [],
   "source": [
    "chicago_crime.head(\"crime\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "21ae8884e5db0ee98f182e4ff6df5ca0449306a0"
   },
   "outputs": [],
   "source": [
    "open_aq = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n",
    "                                   dataset_name=\"openaq\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "_uuid": "4886f1cf75b3eb42dffb8358687296a3c268ad33"
   },
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}