08. SQL/tutorial/01. getting-started-with-sql-and-bigquery.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ab27771e",
   "metadata": {
    "papermill": {
     "duration": 0.013316,
     "end_time": "2021-11-09T00:04:19.090484",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.077168",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Structured Query Language, or **SQL**, is the programming language used with databases, and it is an important skill for any data scientist. In this course, you'll build your SQL skills using **BigQuery**, a web service that lets you apply SQL to huge datasets.\n",
    "\n",
    "In this lesson, you'll learn the basics of accessing and examining BigQuery datasets. After you have a handle on these basics, we'll come back to build your SQL skills.\n",
    "\n",
    "# Your first BigQuery commands\n",
    "\n",
    "To use BigQuery, we'll import the Python package below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "55f9ced7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:19.128613Z",
     "iopub.status.busy": "2021-11-09T00:04:19.124215Z",
     "iopub.status.idle": "2021-11-09T00:04:19.132978Z",
     "shell.execute_reply": "2021-11-09T00:04:19.132230Z"
    },
    "papermill": {
     "duration": 0.029965,
     "end_time": "2021-11-09T00:04:19.133172",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.103207",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from google.cloud import bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abfa2d35",
   "metadata": {
    "papermill": {
     "duration": 0.014232,
     "end_time": "2021-11-09T00:04:19.160041",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.145809",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "The first step in the workflow 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.  As you'll soon see, this `Client` object will play a central role in retrieving information from BigQuery datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "103cba77",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:19.190280Z",
     "iopub.status.busy": "2021-11-09T00:04:19.189141Z",
     "iopub.status.idle": "2021-11-09T00:04:19.193454Z",
     "shell.execute_reply": "2021-11-09T00:04:19.194052Z"
    },
    "papermill": {
     "duration": 0.02174,
     "end_time": "2021-11-09T00:04:19.194233",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.172493",
     "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": "f8e40c46",
   "metadata": {
    "papermill": {
     "duration": 0.012533,
     "end_time": "2021-11-09T00:04:19.219871",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.207338",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "We'll work with a dataset of posts on [Hacker News](https://news.ycombinator.com/), a website focusing on computer science and cybersecurity news.\n",
    "\n",
    "In BigQuery, each dataset is contained in a corresponding project.  In this case, our `hacker_news` dataset is contained in the `bigquery-public-data` project.  To access the dataset, \n",
    "- We begin by constructing a reference to the dataset with the [`dataset()`](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.client.Client.dataset.html#google.cloud.bigquery.client.Client.dataset) method.  \n",
    "- Next, we use the [`get_dataset()`](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.client.Client.get_dataset.html#google.cloud.bigquery.client.Client.get_dataset) method, along with the reference we just constructed, to fetch the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d9b6b682",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:19.249089Z",
     "iopub.status.busy": "2021-11-09T00:04:19.248455Z",
     "iopub.status.idle": "2021-11-09T00:04:19.591942Z",
     "shell.execute_reply": "2021-11-09T00:04:19.591358Z"
    },
    "papermill": {
     "duration": 0.35948,
     "end_time": "2021-11-09T00:04:19.592081",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.232601",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Construct a reference to the \"hacker_news\" dataset\n",
    "dataset_ref = client.dataset(\"hacker_news\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad773dba",
   "metadata": {
    "papermill": {
     "duration": 0.012667,
     "end_time": "2021-11-09T00:04:19.618452",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.605785",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Every dataset is just a collection of tables.  You can think of a dataset as a spreadsheet file containing multiple tables, all composed of rows and columns.\n",
    "\n",
    "We use the `list_tables()` method to list the tables in the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dbf99378",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:19.647229Z",
     "iopub.status.busy": "2021-11-09T00:04:19.646564Z",
     "iopub.status.idle": "2021-11-09T00:04:19.893073Z",
     "shell.execute_reply": "2021-11-09T00:04:19.892526Z"
    },
    "papermill": {
     "duration": 0.261952,
     "end_time": "2021-11-09T00:04:19.893246",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.631294",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "comments\n",
      "full\n",
      "full_201510\n",
      "stories\n"
     ]
    }
   ],
   "source": [
    "# List all the tables in the \"hacker_news\" dataset\n",
    "tables = list(client.list_tables(dataset))\n",
    "\n",
    "# Print names of all tables in the dataset (there are four!)\n",
    "for table in tables:  \n",
    "    print(table.table_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cda4afb",
   "metadata": {
    "papermill": {
     "duration": 0.013376,
     "end_time": "2021-11-09T00:04:19.920119",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.906743",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Similar to how we fetched a dataset, we can fetch a table.  In the code cell below, we fetch the `full` table in the `hacker_news` dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "22737a36",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:19.951465Z",
     "iopub.status.busy": "2021-11-09T00:04:19.950763Z",
     "iopub.status.idle": "2021-11-09T00:04:20.200928Z",
     "shell.execute_reply": "2021-11-09T00:04:20.201526Z"
    },
    "papermill": {
     "duration": 0.268019,
     "end_time": "2021-11-09T00:04:20.201707",
     "exception": false,
     "start_time": "2021-11-09T00:04:19.933688",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Construct a reference to the \"full\" table\n",
    "table_ref = dataset_ref.table(\"full\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8233a7c",
   "metadata": {
    "papermill": {
     "duration": 0.013156,
     "end_time": "2021-11-09T00:04:20.228476",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.215320",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "In the next section, you'll explore the contents of this table in more detail.  For now, take the time to use the image below to consolidate what you've learned so far.\n",
    "\n",
    "![first_commands](https://i.imgur.com/biYqbUB.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dc6ae66",
   "metadata": {
    "papermill": {
     "duration": 0.013756,
     "end_time": "2021-11-09T00:04:20.255697",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.241941",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Table schema\n",
    "\n",
    "The structure of a table is called its **schema**.  We need to understand a table's schema to effectively pull out the data we want. \n",
    "\n",
    "In this example, we'll investigate the `full` table that we fetched above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "991a844b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:20.286275Z",
     "iopub.status.busy": "2021-11-09T00:04:20.285639Z",
     "iopub.status.idle": "2021-11-09T00:04:20.292503Z",
     "shell.execute_reply": "2021-11-09T00:04:20.293028Z"
    },
    "papermill": {
     "duration": 0.023626,
     "end_time": "2021-11-09T00:04:20.293215",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.269589",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SchemaField('title', 'STRING', 'NULLABLE', 'Story title', (), None),\n",
       " SchemaField('url', 'STRING', 'NULLABLE', 'Story url', (), None),\n",
       " SchemaField('text', 'STRING', 'NULLABLE', 'Story or comment text', (), None),\n",
       " SchemaField('dead', 'BOOLEAN', 'NULLABLE', 'Is dead?', (), None),\n",
       " SchemaField('by', 'STRING', 'NULLABLE', \"The username of the item's author.\", (), None),\n",
       " SchemaField('score', 'INTEGER', 'NULLABLE', 'Story score', (), None),\n",
       " SchemaField('time', 'INTEGER', 'NULLABLE', 'Unix time', (), None),\n",
       " SchemaField('timestamp', 'TIMESTAMP', 'NULLABLE', 'Timestamp for the unix time', (), None),\n",
       " SchemaField('type', 'STRING', 'NULLABLE', 'Type of details (comment, comment_ranking, poll, story, job, pollopt)', (), None),\n",
       " SchemaField('id', 'INTEGER', 'NULLABLE', \"The item's unique id.\", (), None),\n",
       " SchemaField('parent', 'INTEGER', 'NULLABLE', 'Parent comment ID', (), None),\n",
       " SchemaField('descendants', 'INTEGER', 'NULLABLE', 'Number of story or poll descendants', (), None),\n",
       " SchemaField('ranking', 'INTEGER', 'NULLABLE', 'Comment ranking', (), None),\n",
       " SchemaField('deleted', 'BOOLEAN', 'NULLABLE', 'Is deleted?', (), None)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Print information on all the columns in the \"full\" table in the \"hacker_news\" dataset\n",
    "table.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "173d651f",
   "metadata": {
    "papermill": {
     "duration": 0.014063,
     "end_time": "2021-11-09T00:04:20.321461",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.307398",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Each [`SchemaField`](https://googleapis.github.io/google-cloud-python/latest/bigquery/generated/google.cloud.bigquery.schema.SchemaField.html#google.cloud.bigquery.schema.SchemaField) tells us about a specific column (which we also refer to as a **field**). In order, the information is:\n",
    "\n",
    "* The **name** of the column\n",
    "* The **field type** (or datatype) in the column\n",
    "* The **mode** of the column (`'NULLABLE'` means that a column allows NULL values, and is the default)\n",
    "* A **description** of the data in that column\n",
    "\n",
    "The first field has the SchemaField:\n",
    "\n",
    "`SchemaField('by', 'string', 'NULLABLE', \"The username of the item's author.\",())`\n",
    "\n",
    "This tells us:\n",
    "- the field (or column) is called `by`,\n",
    "- the data in this field is strings, \n",
    "- NULL values are allowed, and\n",
    "- it contains the usernames corresponding to each item's author.\n",
    "\n",
    "We can use the [`list_rows()`](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.client.Client.list_rows.html#google.cloud.bigquery.client.Client.list_rows) method to check just the first five lines of of the `full` table to make sure this is right. (Sometimes databases have outdated descriptions, so it's good to check.)  This returns a BigQuery [`RowIterator`](https://googleapis.github.io/google-cloud-python/latest/bigquery/generated/google.cloud.bigquery.table.RowIterator.html) object that can quickly be converted to a pandas DataFrame with the [`to_dataframe()`](https://google-cloud.readthedocs.io/en/latest/bigquery/generated/google.cloud.bigquery.table.RowIterator.to_dataframe.html) method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5a84be76",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:20.353324Z",
     "iopub.status.busy": "2021-11-09T00:04:20.352684Z",
     "iopub.status.idle": "2021-11-09T00:04:20.799209Z",
     "shell.execute_reply": "2021-11-09T00:04:20.799840Z"
    },
    "papermill": {
     "duration": 0.464251,
     "end_time": "2021-11-09T00:04:20.800008",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.335757",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:2: 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>title</th>\n",
       "      <th>url</th>\n",
       "      <th>text</th>\n",
       "      <th>dead</th>\n",
       "      <th>by</th>\n",
       "      <th>score</th>\n",
       "      <th>time</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>type</th>\n",
       "      <th>id</th>\n",
       "      <th>parent</th>\n",
       "      <th>descendants</th>\n",
       "      <th>ranking</th>\n",
       "      <th>deleted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>&amp;gt; Are you really going to argue that &amp;quot;...</td>\n",
       "      <td>None</td>\n",
       "      <td>Domenic_S</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1422154497</td>\n",
       "      <td>2015-01-25 02:54:57+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>8941906</td>\n",
       "      <td>8941871</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>My doc prescribed me 25,000 IU once a week for...</td>\n",
       "      <td>None</td>\n",
       "      <td>ryanmarsh</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1512668114</td>\n",
       "      <td>2017-12-07 17:35:14+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>15872018</td>\n",
       "      <td>15870676</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>All of this is anecdotal:&lt;p&gt;These past couple ...</td>\n",
       "      <td>None</td>\n",
       "      <td>Snackchez</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1512668115</td>\n",
       "      <td>2017-12-07 17:35:15+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>15872019</td>\n",
       "      <td>15871723</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>I don&amp;#x27;t know if anybody else has ever wit...</td>\n",
       "      <td>None</td>\n",
       "      <td>Glyptodon</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1383839755</td>\n",
       "      <td>2013-11-07 15:55:55+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>6690041</td>\n",
       "      <td>6689591</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>We have to remember that the NFL, after everyt...</td>\n",
       "      <td>None</td>\n",
       "      <td>zcdziura</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1422154427</td>\n",
       "      <td>2015-01-25 02:53:47+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>8941903</td>\n",
       "      <td>8941712</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  title   url                                               text  dead  \\\n",
       "0  None  None  &gt; Are you really going to argue that &quot;...  None   \n",
       "1  None  None  My doc prescribed me 25,000 IU once a week for...  None   \n",
       "2  None  None  All of this is anecdotal:<p>These past couple ...  None   \n",
       "3  None  None  I don&#x27;t know if anybody else has ever wit...  None   \n",
       "4  None  None  We have to remember that the NFL, after everyt...  None   \n",
       "\n",
       "          by  score        time                 timestamp     type        id  \\\n",
       "0  Domenic_S    NaN  1422154497 2015-01-25 02:54:57+00:00  comment   8941906   \n",
       "1  ryanmarsh    NaN  1512668114 2017-12-07 17:35:14+00:00  comment  15872018   \n",
       "2  Snackchez    NaN  1512668115 2017-12-07 17:35:15+00:00  comment  15872019   \n",
       "3  Glyptodon    NaN  1383839755 2013-11-07 15:55:55+00:00  comment   6690041   \n",
       "4   zcdziura    NaN  1422154427 2015-01-25 02:53:47+00:00  comment   8941903   \n",
       "\n",
       "     parent  descendants  ranking deleted  \n",
       "0   8941871          NaN      NaN    None  \n",
       "1  15870676          NaN      NaN    None  \n",
       "2  15871723          NaN      NaN    None  \n",
       "3   6689591          NaN      NaN    None  \n",
       "4   8941712          NaN      NaN    None  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preview the first five lines of the \"full\" table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4666a9a2",
   "metadata": {
    "papermill": {
     "duration": 0.014786,
     "end_time": "2021-11-09T00:04:20.830073",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.815287",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "The `list_rows()` method will also let us look at just the information in a specific column. If we want to see the first five entries in the `by` column, for example, we can do that!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f0e30810",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-11-09T00:04:20.863567Z",
     "iopub.status.busy": "2021-11-09T00:04:20.862897Z",
     "iopub.status.idle": "2021-11-09T00:04:21.200443Z",
     "shell.execute_reply": "2021-11-09T00:04:21.200982Z"
    },
    "papermill": {
     "duration": 0.355785,
     "end_time": "2021-11-09T00:04:21.201174",
     "exception": false,
     "start_time": "2021-11-09T00:04:20.845389",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:2: 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>title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  title\n",
       "0  None\n",
       "1  None\n",
       "2  None\n",
       "3  None\n",
       "4  None"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Preview the first five entries in the \"by\" column of the \"full\" table\n",
    "client.list_rows(table, selected_fields=table.schema[:1], max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a128b62b",
   "metadata": {
    "papermill": {
     "duration": 0.01566,
     "end_time": "2021-11-09T00:04:21.232838",
     "exception": false,
     "start_time": "2021-11-09T00:04:21.217178",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Disclaimer\n",
    "Before we go into the coding exercise, a quick disclaimer for those who already know some SQL:\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 commands you've seen so far won't demand a meaningful fraction of that limit. But some BiqQuery datasets are huge. So, if you already know SQL, wait to run SELECT queries until you've seen how to use your allotment effectively. If you are like most people reading this, you don't know how to write these queries yet, so you don't need to worry about this disclaimer.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3a31a9c",
   "metadata": {
    "papermill": {
     "duration": 0.015917,
     "end_time": "2021-11-09T00:04:21.264704",
     "exception": false,
     "start_time": "2021-11-09T00:04:21.248787",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Your turn\n",
    "Practice the commands you've seen to **[explore the structure of a dataset](https://www.kaggle.com/kernels/fork/1058477)** with crimes in the city of Chicago."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce8a753b",
   "metadata": {
    "papermill": {
     "duration": 0.015632,
     "end_time": "2021-11-09T00:04:21.296906",
     "exception": false,
     "start_time": "2021-11-09T00:04:21.281274",
     "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": 12.305583,
   "end_time": "2021-11-09T00:04:21.922474",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2021-11-09T00:04:09.616891",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}