08. SQL/tutorial/02. select-from-where.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2d16dac0",
   "metadata": {
    "papermill": {
     "duration": 0.018048,
     "end_time": "2021-11-09T00:03:59.484998",
     "exception": false,
     "start_time": "2021-11-09T00:03:59.466950",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Now that you know how to access and examine a dataset, you're ready to write your first SQL query!  As you'll soon see, SQL queries will help you sort through a massive dataset, to retrieve only the information that you need.  \n",
    "\n",
    "We'll begin by using the keywords **SELECT**, **FROM**, and **WHERE** to get data from specific columns based on conditions you specify. \n",
    "\n",
    "For clarity, we'll work with a small imaginary dataset `pet_records` which contains just one table, called `pets`. \n",
    "\n",
    "![](https://i.imgur.com/fI5Pvvp.png)\n",
    "\n",
    "# SELECT ... FROM\n",
    "\n",
    "The most basic SQL query selects a single column from a single table.  To do this, \n",
    "- specify the column you want after the word **SELECT**, and then \n",
    "- specify the table after the word **FROM**.  \n",
    "\n",
    "For instance, to select the `Name` column (from the `pets` table in the `pet_records` database in the `bigquery-public-data` project), our query would appear as follows:  \n",
    "\n",
    "![](https://i.imgur.com/c3GxYRt.png)\n",
    "\n",
    "Note that when writing an SQL query, the argument we pass to **FROM** is *not* in single or double quotation marks (' or \"). It is in backticks (\\`).\n",
    "\n",
    "# WHERE ...\n",
    "\n",
    "BigQuery datasets are large, so you'll usually want to return only the rows meeting specific conditions. You can do this using the **WHERE** clause.\n",
    "\n",
    "The query below returns the entries from the `Name` column that are in rows where the `Animal` column has the text `'Cat'`. \n",
    "\n",
    "![](https://i.imgur.com/HJOT8Kb.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5aa96ac5",
   "metadata": {
    "papermill": {
     "duration": 0.015991,
     "end_time": "2021-11-09T00:03:59.517989",
     "exception": false,
     "start_time": "2021-11-09T00:03:59.501998",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Example: What are all the U.S. cities in the OpenAQ dataset?\n",
    "\n",
    "Now that you've got the basics down, let's work through an example with a real dataset. We'll use an [OpenAQ](https://openaq.org) dataset about air quality.\n",
    "\n",
    "First, we'll set up everything we need to run queries and take a quick peek at what tables are in our database.  (_Since you learned how to do this in the previous tutorial, we have hidden the code.  But if you'd like to take a peek, you need only click on the \"Code\" button below._)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7cc13084",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2021-11-09T00:03:59.557551Z",
     "iopub.status.busy": "2021-11-09T00:03:59.556327Z",
     "iopub.status.idle": "2021-11-09T00:04:00.299174Z",
     "shell.execute_reply": "2021-11-09T00:04:00.298514Z"
    },
    "papermill": {
     "duration": 0.762942,
     "end_time": "2021-11-09T00:04:00.299358",
     "exception": false,
     "start_time": "2021-11-09T00:03:59.536416",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n",
      "global_air_quality\n"
     ]
    }
   ],
   "source": [
    "\n",
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"openaq\" dataset\n",
    "dataset_ref = client.dataset(\"openaq\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)\n",
    "\n",
    "# List all the tables in the \"openaq\" dataset\n",
    "tables = list(client.list_tables(dataset))\n",
    "\n",
    "# Print names of all tables in the dataset (there's only one!)\n",
    "for table in tables:  \n",
    "    print(table.table_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2449266",
   "metadata": {
    "papermill": {
     "duration": 0.016882,
     "end_time": "2021-11-09T00:04:00.333855",
     "exception": false,
     "start_time": "2021-11-09T00:04:00.316973",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "The dataset contains only one table, called `global_air_quality`.  We'll fetch the table and take a peek at the first few rows to see what sort of data it contains.  (_Again, we have hidden the code.  To take a peek, click on the \"Code\" button below._)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "41e0f919",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:00.374612Z",
     "iopub.status.busy": "2021-11-09T00:04:00.373899Z",
     "iopub.status.idle": "2021-11-09T00:04:01.001996Z",
     "shell.execute_reply": "2021-11-09T00:04:01.002581Z"
    },
    "papermill": {
     "duration": 0.651735,
     "end_time": "2021-11-09T00:04:01.002756",
     "exception": false,
     "start_time": "2021-11-09T00:04:00.351021",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:8: UserWarning: Cannot use bqstorage_client if max_results is set, reverting to fetching data with the tabledata.list endpoint.\n",
      "  \n"
     ]
    },
    {
     "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>location</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>pollutant</th>\n",
       "      <th>value</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>unit</th>\n",
       "      <th>source_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>averaged_over_in_hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BTM Layout, Bengaluru - KSPCB</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>IN</td>\n",
       "      <td>co</td>\n",
       "      <td>910.00</td>\n",
       "      <td>2018-02-22 03:00:00+00:00</td>\n",
       "      <td>µg/m³</td>\n",
       "      <td>CPCB</td>\n",
       "      <td>12.912811</td>\n",
       "      <td>77.60922</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BTM Layout, Bengaluru - KSPCB</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>IN</td>\n",
       "      <td>no2</td>\n",
       "      <td>131.87</td>\n",
       "      <td>2018-02-22 03:00:00+00:00</td>\n",
       "      <td>µg/m³</td>\n",
       "      <td>CPCB</td>\n",
       "      <td>12.912811</td>\n",
       "      <td>77.60922</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BTM Layout, Bengaluru - KSPCB</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>IN</td>\n",
       "      <td>o3</td>\n",
       "      <td>15.57</td>\n",
       "      <td>2018-02-22 03:00:00+00:00</td>\n",
       "      <td>µg/m³</td>\n",
       "      <td>CPCB</td>\n",
       "      <td>12.912811</td>\n",
       "      <td>77.60922</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BTM Layout, Bengaluru - KSPCB</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>IN</td>\n",
       "      <td>pm25</td>\n",
       "      <td>45.62</td>\n",
       "      <td>2018-02-22 03:00:00+00:00</td>\n",
       "      <td>µg/m³</td>\n",
       "      <td>CPCB</td>\n",
       "      <td>12.912811</td>\n",
       "      <td>77.60922</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BTM Layout, Bengaluru - KSPCB</td>\n",
       "      <td>Bengaluru</td>\n",
       "      <td>IN</td>\n",
       "      <td>so2</td>\n",
       "      <td>4.49</td>\n",
       "      <td>2018-02-22 03:00:00+00:00</td>\n",
       "      <td>µg/m³</td>\n",
       "      <td>CPCB</td>\n",
       "      <td>12.912811</td>\n",
       "      <td>77.60922</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        location       city country pollutant   value  \\\n",
       "0  BTM Layout, Bengaluru - KSPCB  Bengaluru      IN        co  910.00   \n",
       "1  BTM Layout, Bengaluru - KSPCB  Bengaluru      IN       no2  131.87   \n",
       "2  BTM Layout, Bengaluru - KSPCB  Bengaluru      IN        o3   15.57   \n",
       "3  BTM Layout, Bengaluru - KSPCB  Bengaluru      IN      pm25   45.62   \n",
       "4  BTM Layout, Bengaluru - KSPCB  Bengaluru      IN       so2    4.49   \n",
       "\n",
       "                  timestamp   unit source_name   latitude  longitude  \\\n",
       "0 2018-02-22 03:00:00+00:00  µg/m³        CPCB  12.912811   77.60922   \n",
       "1 2018-02-22 03:00:00+00:00  µg/m³        CPCB  12.912811   77.60922   \n",
       "2 2018-02-22 03:00:00+00:00  µg/m³        CPCB  12.912811   77.60922   \n",
       "3 2018-02-22 03:00:00+00:00  µg/m³        CPCB  12.912811   77.60922   \n",
       "4 2018-02-22 03:00:00+00:00  µg/m³        CPCB  12.912811   77.60922   \n",
       "\n",
       "   averaged_over_in_hours  \n",
       "0                    0.25  \n",
       "1                    0.25  \n",
       "2                    0.25  \n",
       "3                    0.25  \n",
       "4                    0.25  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Construct a reference to the \"global_air_quality\" table\n",
    "table_ref = dataset_ref.table(\"global_air_quality\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"global_air_quality\" table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40fc41f7",
   "metadata": {
    "papermill": {
     "duration": 0.018952,
     "end_time": "2021-11-09T00:04:01.039924",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.020972",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Everything looks good! So, let's put together a query. Say we want to select all the values from the `city` column that are in rows where the `country` column is `'US'` (for \"United States\")."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d0897266",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:01.080924Z",
     "iopub.status.busy": "2021-11-09T00:04:01.079887Z",
     "iopub.status.idle": "2021-11-09T00:04:01.083945Z",
     "shell.execute_reply": "2021-11-09T00:04:01.084624Z"
    },
    "papermill": {
     "duration": 0.026262,
     "end_time": "2021-11-09T00:04:01.084810",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.058548",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Query to select all the items from the \"city\" column where the \"country\" column is 'US'\n",
    "query = \"\"\"\n",
    "        SELECT city\n",
    "        FROM `bigquery-public-data.openaq.global_air_quality`\n",
    "        WHERE country = 'US'\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fe7741a",
   "metadata": {
    "papermill": {
     "duration": 0.018102,
     "end_time": "2021-11-09T00:04:01.121338",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.103236",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Take the time now to ensure that this query lines up with what you learned above.  \n",
    "\n",
    "# Submitting the query to the dataset\n",
    "\n",
    "We're ready to use this query to get information from the OpenAQ dataset.  As in the previous tutorial, the first step is to create a [`Client`](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.client.Client.html#google.cloud.bigquery.client.Client) object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c3b17cf7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:01.162158Z",
     "iopub.status.busy": "2021-11-09T00:04:01.161152Z",
     "iopub.status.idle": "2021-11-09T00:04:01.167248Z",
     "shell.execute_reply": "2021-11-09T00:04:01.166611Z"
    },
    "papermill": {
     "duration": 0.027575,
     "end_time": "2021-11-09T00:04:01.167430",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.139855",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    }
   ],
   "source": [
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c8c9e74",
   "metadata": {
    "papermill": {
     "duration": 0.018413,
     "end_time": "2021-11-09T00:04:01.204893",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.186480",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "We begin by setting up the query with the [`query()`](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.client.Client.query.html#google.cloud.bigquery.client.Client.query) method.  We run the method with the default parameters, but this method also allows us to specify more complicated settings that you can read about in [the documentation](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.client.Client.query.html#google.cloud.bigquery.client.Client.query).  We'll revisit this later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "02059269",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:01.250170Z",
     "iopub.status.busy": "2021-11-09T00:04:01.249418Z",
     "iopub.status.idle": "2021-11-09T00:04:01.715296Z",
     "shell.execute_reply": "2021-11-09T00:04:01.714747Z"
    },
    "papermill": {
     "duration": 0.491779,
     "end_time": "2021-11-09T00:04:01.715471",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.223692",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Set up the query\n",
    "query_job = client.query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47da487c",
   "metadata": {
    "papermill": {
     "duration": 0.018782,
     "end_time": "2021-11-09T00:04:01.753547",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.734765",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Next, we run the query and convert the results to a pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2d2a4eec",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:01.796328Z",
     "iopub.status.busy": "2021-11-09T00:04:01.795694Z",
     "iopub.status.idle": "2021-11-09T00:04:02.426261Z",
     "shell.execute_reply": "2021-11-09T00:04:02.426798Z"
    },
    "papermill": {
     "duration": 0.654395,
     "end_time": "2021-11-09T00:04:02.426997",
     "exception": false,
     "start_time": "2021-11-09T00:04:01.772602",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    }
   ],
   "source": [
    "# API request - run the query, and return a pandas DataFrame\n",
    "us_cities = query_job.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "672aa800",
   "metadata": {
    "papermill": {
     "duration": 0.019802,
     "end_time": "2021-11-09T00:04:02.467247",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.447445",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Now we've got a pandas DataFrame called `us_cities`, which we can use like any other DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "60ff9a30",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:02.517450Z",
     "iopub.status.busy": "2021-11-09T00:04:02.516616Z",
     "iopub.status.idle": "2021-11-09T00:04:02.519597Z",
     "shell.execute_reply": "2021-11-09T00:04:02.520072Z"
    },
    "papermill": {
     "duration": 0.033595,
     "end_time": "2021-11-09T00:04:02.520253",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.486658",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Phoenix-Mesa-Scottsdale                     88\n",
       "Houston                                     82\n",
       "Los Angeles-Long Beach-Santa Ana            68\n",
       "Riverside-San Bernardino-Ontario            60\n",
       "New York-Northern New Jersey-Long Island    60\n",
       "Name: city, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What five cities have the most measurements?\n",
    "us_cities.city.value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bf77b13",
   "metadata": {
    "papermill": {
     "duration": 0.019429,
     "end_time": "2021-11-09T00:04:02.559647",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.540218",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# More queries\n",
    "\n",
    "If you want multiple columns, you can select them with a comma between the names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "48dc0eb7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:02.605048Z",
     "iopub.status.busy": "2021-11-09T00:04:02.604040Z",
     "iopub.status.idle": "2021-11-09T00:04:02.607170Z",
     "shell.execute_reply": "2021-11-09T00:04:02.606619Z"
    },
    "papermill": {
     "duration": 0.027788,
     "end_time": "2021-11-09T00:04:02.607322",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.579534",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "        SELECT city, country\n",
    "        FROM `bigquery-public-data.openaq.global_air_quality`\n",
    "        WHERE country = 'US'\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4059ed20",
   "metadata": {
    "papermill": {
     "duration": 0.020898,
     "end_time": "2021-11-09T00:04:02.648955",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.628057",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "You can select all columns with a `*` like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "459dee1b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:02.694901Z",
     "iopub.status.busy": "2021-11-09T00:04:02.693966Z",
     "iopub.status.idle": "2021-11-09T00:04:02.696850Z",
     "shell.execute_reply": "2021-11-09T00:04:02.696196Z"
    },
    "papermill": {
     "duration": 0.027801,
     "end_time": "2021-11-09T00:04:02.696987",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.669186",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "        SELECT *\n",
    "        FROM `bigquery-public-data.openaq.global_air_quality`\n",
    "        WHERE country = 'US'\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf4459e8",
   "metadata": {
    "papermill": {
     "duration": 0.019716,
     "end_time": "2021-11-09T00:04:02.736838",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.717122",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Q&A: Notes on formatting\n",
    "\n",
    "The formatting of the SQL query might feel unfamiliar. If you have any questions, you can ask in the comments section at the bottom of this page.  Here are answers to two common questions:\n",
    "\n",
    "### **Question: What's up with the triple quotation marks (\"\"\")?**\n",
    "\n",
    "_Answer_: These tell Python that everything inside them is a single string, even though we have line breaks in it. The line breaks aren't necessary, but they make it easier to read your query.\n",
    "\n",
    "### **Question: Do you need to capitalize SELECT and FROM?**\n",
    "\n",
    "_Answer_: No, SQL doesn't care about capitalization. However, it's customary to capitalize your SQL commands, and it makes your queries a bit easier to read."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd7bd551",
   "metadata": {
    "papermill": {
     "duration": 0.019765,
     "end_time": "2021-11-09T00:04:02.776780",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.757015",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Working with big datasets\n",
    "\n",
    "BigQuery datasets can be huge. We allow you to do a lot of computation for free, but everyone has some limit.\n",
    "\n",
    "**Each Kaggle user can scan 5TB every 30 days for free. Once you hit that limit, you'll have to wait for it to reset.**\n",
    "\n",
    "The [biggest dataset currently on Kaggle](https://www.kaggle.com/github/github-repos) is 3TB, so you can go through your 30-day limit in a couple queries if you aren't careful.\n",
    "\n",
    "Don't worry though: we'll teach you how to avoid scanning too much data at once, so that you don't run over your limit.\n",
    "\n",
    "To begin,you can estimate the size of any query before running it. Here is an example using the (*very large!*) Hacker News dataset. To see how much data a query will scan, we create a `QueryJobConfig` object and set the `dry_run` parameter to `True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4f2e60f4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:02.824604Z",
     "iopub.status.busy": "2021-11-09T00:04:02.823854Z",
     "iopub.status.idle": "2021-11-09T00:04:03.225408Z",
     "shell.execute_reply": "2021-11-09T00:04:03.224778Z"
    },
    "papermill": {
     "duration": 0.428625,
     "end_time": "2021-11-09T00:04:03.225557",
     "exception": false,
     "start_time": "2021-11-09T00:04:02.796932",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This query will process 494391991 bytes.\n"
     ]
    }
   ],
   "source": [
    "# Query to get the score column from every row where the type column has value \"job\"\n",
    "query = \"\"\"\n",
    "        SELECT score, title\n",
    "        FROM `bigquery-public-data.hacker_news.full`\n",
    "        WHERE type = \"job\" \n",
    "        \"\"\"\n",
    "\n",
    "# Create a QueryJobConfig object to estimate size of query without running it\n",
    "dry_run_config = bigquery.QueryJobConfig(dry_run=True)\n",
    "\n",
    "# API request - dry run query to estimate costs\n",
    "dry_run_query_job = client.query(query, job_config=dry_run_config)\n",
    "\n",
    "print(\"This query will process {} bytes.\".format(dry_run_query_job.total_bytes_processed))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f8516e6",
   "metadata": {
    "papermill": {
     "duration": 0.020562,
     "end_time": "2021-11-09T00:04:03.266975",
     "exception": false,
     "start_time": "2021-11-09T00:04:03.246413",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "You can also specify a parameter when running the query to limit how much data you are willing to scan. Here's an example with a low limit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "18120feb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:03.316620Z",
     "iopub.status.busy": "2021-11-09T00:04:03.315961Z",
     "iopub.status.idle": "2021-11-09T00:04:03.875067Z",
     "shell.execute_reply": "2021-11-09T00:04:03.874481Z"
    },
    "papermill": {
     "duration": 0.587337,
     "end_time": "2021-11-09T00:04:03.875228",
     "exception": false,
     "start_time": "2021-11-09T00:04:03.287891",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "ename": "InternalServerError",
     "evalue": "500 Query exceeded limit for bytes billed: 1000000. 494927872 or higher required.\n\n(job ID: 89759fc8-1481-4761-b263-74fab214ded1)\n\n             -----Query Job SQL Follows-----             \n\n    |    .    |    .    |    .    |    .    |    .    |\n   1:\n   2:        SELECT score, title\n   3:        FROM `bigquery-public-data.hacker_news.full`\n   4:        WHERE type = \"job\" \n   5:        \n    |    .    |    .    |    .    |    .    |    .    |",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInternalServerError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m/tmp/ipykernel_21/2063017411.py\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;31m# API request - try to run the query, and return a pandas DataFrame\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 9\u001b[0;31m \u001b[0msafe_query_job\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_dataframe\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\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/job.py\u001b[0m in \u001b[0;36mto_dataframe\u001b[0;34m(self, bqstorage_client, dtypes, progress_bar_type, create_bqstorage_client, date_as_object)\u001b[0m\n\u001b[1;32m   3403\u001b[0m             \u001b[0mValueError\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mIf\u001b[0m \u001b[0mthe\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m`\u001b[0m\u001b[0mpandas\u001b[0m\u001b[0;31m`\u001b[0m \u001b[0mlibrary\u001b[0m \u001b[0mcannot\u001b[0m \u001b[0mbe\u001b[0m \u001b[0mimported\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3404\u001b[0m         \"\"\"\n\u001b[0;32m-> 3405\u001b[0;31m         return self.result().to_dataframe(\n\u001b[0m\u001b[1;32m   3406\u001b[0m             \u001b[0mbqstorage_client\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mbqstorage_client\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3407\u001b[0m             \u001b[0mdtypes\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdtypes\u001b[0m\u001b[0;34m,\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/job.py\u001b[0m in \u001b[0;36mresult\u001b[0;34m(self, page_size, max_results, retry, timeout, start_index)\u001b[0m\n\u001b[1;32m   3232\u001b[0m         \"\"\"\n\u001b[1;32m   3233\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3234\u001b[0;31m             \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mQueryJob\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3235\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3236\u001b[0m             \u001b[0;31m# Return an iterator instead of returning the job.\u001b[0m\u001b[0;34m\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/job.py\u001b[0m in \u001b[0;36mresult\u001b[0;34m(self, retry, timeout)\u001b[0m\n\u001b[1;32m    819\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_begin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mretry\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    820\u001b[0m         \u001b[0;31m# TODO: modify PollingFuture so it can pass a retry argument to done().\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 821\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_AsyncJob\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mtimeout\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    822\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    823\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mcancelled\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\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/api_core/future/polling.py\u001b[0m in \u001b[0;36mresult\u001b[0;34m(self, timeout, retry)\u001b[0m\n\u001b[1;32m    133\u001b[0m             \u001b[0;31m# pylint: disable=raising-bad-type\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    134\u001b[0m             \u001b[0;31m# Pylint doesn't recognize that this is valid in this case.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 135\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    136\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    137\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInternalServerError\u001b[0m: 500 Query exceeded limit for bytes billed: 1000000. 494927872 or higher required.\n\n(job ID: 89759fc8-1481-4761-b263-74fab214ded1)\n\n             -----Query Job SQL Follows-----             \n\n    |    .    |    .    |    .    |    .    |    .    |\n   1:\n   2:        SELECT score, title\n   3:        FROM `bigquery-public-data.hacker_news.full`\n   4:        WHERE type = \"job\" \n   5:        \n    |    .    |    .    |    .    |    .    |    .    |"
     ]
    }
   ],
   "source": [
    "# Only run the query if it's less than 1 MB\n",
    "ONE_MB = 1000*1000\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=ONE_MB)\n",
    "\n",
    "# Set up the query (will only run if it's less than 1 MB)\n",
    "safe_query_job = client.query(query, job_config=safe_config)\n",
    "\n",
    "# API request - try to run the query, and return a pandas DataFrame\n",
    "safe_query_job.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9ea5c7f",
   "metadata": {
    "papermill": {
     "duration": 0.020991,
     "end_time": "2021-11-09T00:04:03.918017",
     "exception": false,
     "start_time": "2021-11-09T00:04:03.897026",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "In this case, the query was cancelled, because the limit of 1 MB was exceeded.  However, we can increase the limit to run the query successfully!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "12384567",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:03.964660Z",
     "iopub.status.busy": "2021-11-09T00:04:03.963935Z",
     "iopub.status.idle": "2021-11-09T00:04:07.751971Z",
     "shell.execute_reply": "2021-11-09T00:04:07.751302Z"
    },
    "papermill": {
     "duration": 3.812686,
     "end_time": "2021-11-09T00:04:07.752131",
     "exception": false,
     "start_time": "2021-11-09T00:04:03.939445",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.7846262840949345"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Only run the query if it's less than 1 GB\n",
    "ONE_GB = 1000*1000*1000\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=ONE_GB)\n",
    "\n",
    "# Set up the query (will only run if it's less than 1 GB)\n",
    "safe_query_job = client.query(query, job_config=safe_config)\n",
    "\n",
    "# API request - try to run the query, and return a pandas DataFrame\n",
    "job_post_scores = safe_query_job.to_dataframe()\n",
    "\n",
    "# Print average score for job posts\n",
    "job_post_scores.score.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6eb8f22",
   "metadata": {
    "papermill": {
     "duration": 0.021518,
     "end_time": "2021-11-09T00:04:07.796888",
     "exception": false,
     "start_time": "2021-11-09T00:04:07.775370",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Your turn\n",
    "\n",
    "Writing **SELECT** statements is the key to using SQL. So **[try your new skills](https://www.kaggle.com/kernels/fork/681989)**!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e26ccd6d",
   "metadata": {
    "papermill": {
     "duration": 0.021319,
     "end_time": "2021-11-09T00:04:07.839993",
     "exception": false,
     "start_time": "2021-11-09T00:04:07.818674",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/intro-to-sql/discussion) to chat with other learners.*"
   ]
  }
 ],
 "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.7.10"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 18.429016,
   "end_time": "2021-11-09T00:04:08.471595",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2021-11-09T00:03:50.042579",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}