Tips/BigQuery - Python Client.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8469d3de",
   "metadata": {},
   "source": [
    "![tracker](https://us-central1-vertex-ai-mlops-369716.cloudfunctions.net/pixel-tracking?path=statmike%2Fvertex-ai-mlops%2FTips&file=BigQuery+-+Python+Client.ipynb)\n",
    "<!--- header table --->\n",
    "<table align=\"left\">\n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://colab.research.google.com/github/statmike/vertex-ai-mlops/blob/main/Tips/BigQuery%20-%20Python%20Client.ipynb\">\n",
    "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Google Colaboratory logo\">\n",
    "      <br>Run in<br>Colab\n",
    "    </a>\n",
    "  </td>\n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://console.cloud.google.com/vertex-ai/colab/import/https%3A%2F%2Fraw.githubusercontent.com%2Fstatmike%2Fvertex-ai-mlops%2Fmain%2FTips%2FBigQuery%2520-%2520Python%2520Client.ipynb\">\n",
    "      <img width=\"32px\" src=\"https://lh3.googleusercontent.com/JmcxdQi-qOpctIvWKgPtrzZdJJK-J3sWE1RsfjZNwshCFgE_9fULcNpuXYTilIR2hjwN\" alt=\"Google Cloud Colab Enterprise logo\">\n",
    "      <br>Run in<br>Colab Enterprise\n",
    "    </a>\n",
    "  </td>      \n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://github.com/statmike/vertex-ai-mlops/blob/main/Tips/BigQuery%20-%20Python%20Client.ipynb\">\n",
    "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\">\n",
    "      <br>View on<br>GitHub\n",
    "    </a>\n",
    "  </td>\n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/statmike/vertex-ai-mlops/main/Tips/BigQuery%20-%20Python%20Client.ipynb\">\n",
    "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\">\n",
    "      <br>Open in<br>Vertex AI Workbench\n",
    "    </a>\n",
    "  </td>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a032014-d485-43b2-8b71-b6b72efd59ec",
   "metadata": {},
   "source": [
    "# Python BigQuery Client(s)\n",
    "\n",
    "This notebook covers many ways of interacting with BigQuery tables from Python, including:\n",
    "- ways unique to Jupyter notebooks like magics\n",
    "- built in methods for pandas like pandas-gbq methods\n",
    "- the BigQuery Python clinet\n",
    "- BigQuery storage api for fast, multiprocessing, reads directly from BigQuery storage\n",
    "\n",
    "\n",
    "**Notes:**\n",
    "- The `LIMIT 5` statement does limit the number of rows returned by BigQuery to 5 but BigQuery still does a full table scan.  If you have a table larger than 1GB and want to limit the rows scanned for a quick review then replacing `LIMIT 5` with `TABLESAMPLE SYSTEM (1 PERCENT)` would be more efficient.  For tables under 1GB it will still return the full table.  More on [Table Sampling](https://cloud.google.com/bigquery/docs/table-sampling)\n",
    "- Each of the examples below run the same query in BigQuery.  The query is cached on the first run for up to 24 hours.  This means the subsequent, identical queries will not scan the data and instead use the cached results table.  More information on [Using cached query results](https://cloud.google.com/bigquery/docs/cached-results).\n",
    "\n",
    "\n",
    "\n",
    "**Resources:**\n",
    "- [BigQuery Python Client](https://cloud.google.com/python/docs/reference/bigquery/latest)\n",
    "    - Interact with BigQuery compute to run queries\n",
    "- [BigQuery Storage API Python Client](https://cloud.google.com/python/docs/reference/bigquerystorage/latest)\n",
    "    - directly read from BigQuery storage through streams which support multiprocessing\n",
    "- Using BigQuery From Python, Notebooks in This Repository\n",
    "    - [01 - Data Sources/01 - BigQuery - Table Data Sources](../01%20-%20Data%20Sources/01%20-%20BigQuery%20-%20Table%20Data%20Source.ipynb)\n",
    "    - [03 - BigQuery ML (BQML)/03 - Introduction to BigQuery ML (BQML)](../03%20-%20BigQuery%20ML%20(BQML)/03%20-%20Introduction%20to%20BigQuery%20ML%20(BQML).ipynb)\n",
    "    - [Applied Forecasting/BigQuery Time Series Forecasting Data Review and Preparation](../Applied%20Forecasting/BigQuery%20Time%20Series%20Forecasting%20Data%20Review%20and%20Preparation.ipynb)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15273ec6-33bf-47ec-b4c9-a54fa591a9e8",
   "metadata": {},
   "source": [
    "---\n",
    "## Colab Setup\n",
    "\n",
    "To run this notebook in Colab click [![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/statmike/vertex-ai-mlops/blob/main/Tips/BigQuery%20-%20Python%20Client.ipynb) and run the cells in this section.  Otherwise, skip this section.\n",
    "\n",
    "This cell will authenticate to GCP (follow prompts in the popup)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4da7ce6e-6e6d-466e-ba89-d766f7478cd8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "PROJECT_ID = 'statmike-mlops-349915' # replace with project ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ea333ef3-602e-4051-8bab-6ca5b2999c62",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    import google.colab\n",
    "    from google.colab import auth\n",
    "    auth.authenticate_user()\n",
    "    !gcloud config set project {PROJECT_ID}\n",
    "except Exception:\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2434067-646e-4954-80eb-a4a868162c9a",
   "metadata": {},
   "source": [
    "---\n",
    "## Installs\n",
    "\n",
    "The list `packages` contains tuples of package import names and install names.  If the import name is not found then the install name is used to install quitely for the current user."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a6163f5f-e7dc-4364-88e5-3dc079f20e0a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# tuples of (import name, install name)\n",
    "packages = [\n",
    "    ('google.cloud.bigquery', 'google-cloud-bigquery'),\n",
    "    ('google.cloud.bigquery_storage', 'google-cloud-bigquery-storage', '2.24.0'),\n",
    "    ('bigframes', 'bigframes'),\n",
    "    ('pandas_gbq', 'pandas-gbq'),\n",
    "    ('tensorflow', 'tensorflow', '2.10'),\n",
    "    ('tensorflow_io', '--no-deps tensorflow-io'),\n",
    "]\n",
    "\n",
    "import importlib\n",
    "install = False\n",
    "for package in packages:\n",
    "    if not importlib.util.find_spec(package[0]):\n",
    "        print(f'installing package {package[1]}')\n",
    "        install = True\n",
    "        !pip install {package[1]} -U -q --user\n",
    "    elif len(package) == 3:\n",
    "        if importlib.metadata.version(package[0]) < package[2]:\n",
    "            print(f'updating package {package[1]}')\n",
    "            install = True\n",
    "            !pip install {package[1]} -U -q --user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ee80153-5c7e-47c2-91ea-7377d18a3fa7",
   "metadata": {},
   "source": [
    "### Restart Kernel (If Installs Occured)\n",
    "\n",
    "After a kernel restart the code submission can start with the next cell after this one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7f09eae6-92ed-464b-afa8-b0659e7108fe",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if install:\n",
    "    import IPython\n",
    "    app = IPython.Application.instance()\n",
    "    app.kernel.do_shutdown(True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ec0ef1d-7a2c-4b03-ace7-bbb311b0bb28",
   "metadata": {},
   "source": [
    "---\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08612e49-0b3e-42e7-8a47-a6b6999baabd",
   "metadata": {},
   "source": [
    "inputs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "21be77b6-15c9-4804-bf28-63f2b631d991",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'statmike-mlops-349915'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "project = !gcloud config get-value project\n",
    "PROJECT_ID = project[0]\n",
    "PROJECT_ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4cff6ba1-1a6e-4b08-91d2-360c3eb5897c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# source data\n",
    "BQ_PROJECT = 'bigquery-public-data'\n",
    "BQ_DATASET = 'ml_datasets'\n",
    "BQ_TABLE = 'ulb_fraud_detection'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ca9b255-408d-4aa6-86b0-c1a1e9888dee",
   "metadata": {},
   "source": [
    "packages:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "186b0b32-ff87-4ad1-8df6-4d6c4c1fbb51",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ['TF_CPP_MIN_LOG_LEVEL'] = '3'\n",
    "\n",
    "import timeit\n",
    "import concurrent.futures\n",
    "\n",
    "import pandas as pd\n",
    "from google.cloud import bigquery\n",
    "from google.cloud import bigquery_storage\n",
    "import bigframes.pandas as bpd\n",
    "\n",
    "from tensorflow.python.framework import dtypes\n",
    "from tensorflow_io.bigquery import BigQueryClient\n",
    "import tensorflow as tf"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0376d608-b804-42b5-a36f-ac697daecf06",
   "metadata": {},
   "source": [
    "clients:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5f4a0155-53f6-4e1a-a037-90c9392e1088",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "bq = bigquery.Client(project = PROJECT_ID)\n",
    "bqstorage = bigquery_storage.BigQueryReadClient()\n",
    "bpd.options.bigquery.project = PROJECT_ID"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b04f84cd-6069-4e29-93fe-3007095bb55c",
   "metadata": {},
   "source": [
    "parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b646b3c6-45ef-4620-bb97-855840c4e8cb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "BQ_SOURCE = f'{BQ_PROJECT}.{BQ_DATASET}.{BQ_TABLE}'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f46a37d-0c08-4f5b-85d9-2c0ab6f385ce",
   "metadata": {},
   "source": [
    "---\n",
    "## BigQuery From Jupyter"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "062ed6b0-45a0-452a-92d1-97acea6cc703",
   "metadata": {},
   "source": [
    "### BigQuery Cell Magic\n",
    "\n",
    "See the documentation for [IPython Magics for BigQuery](https://cloud.google.com/python/docs/reference/bigquery/latest/magics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e0ac0688-85fa-4676-b46e-f8a2de501d4d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5a1fcd65f17b43b9b4e95b2119a5949d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "34334b61f9b04b089d3e092565b114ac",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Time</th>\n",
       "      <th>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "      <th>V6</th>\n",
       "      <th>V7</th>\n",
       "      <th>V8</th>\n",
       "      <th>V9</th>\n",
       "      <th>...</th>\n",
       "      <th>V21</th>\n",
       "      <th>V22</th>\n",
       "      <th>V23</th>\n",
       "      <th>V24</th>\n",
       "      <th>V25</th>\n",
       "      <th>V26</th>\n",
       "      <th>V27</th>\n",
       "      <th>V28</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8748.0</td>\n",
       "      <td>-1.070416</td>\n",
       "      <td>0.304517</td>\n",
       "      <td>2.777064</td>\n",
       "      <td>2.154061</td>\n",
       "      <td>0.254450</td>\n",
       "      <td>-0.448529</td>\n",
       "      <td>-0.398691</td>\n",
       "      <td>0.144672</td>\n",
       "      <td>1.070900</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.122032</td>\n",
       "      <td>-0.182351</td>\n",
       "      <td>0.019576</td>\n",
       "      <td>0.626023</td>\n",
       "      <td>-0.018518</td>\n",
       "      <td>-0.263291</td>\n",
       "      <td>-0.198600</td>\n",
       "      <td>0.098435</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27074.0</td>\n",
       "      <td>1.165628</td>\n",
       "      <td>0.423671</td>\n",
       "      <td>0.887635</td>\n",
       "      <td>2.740163</td>\n",
       "      <td>-0.338578</td>\n",
       "      <td>-0.142846</td>\n",
       "      <td>-0.055628</td>\n",
       "      <td>-0.015325</td>\n",
       "      <td>-0.213621</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081184</td>\n",
       "      <td>-0.025694</td>\n",
       "      <td>-0.076609</td>\n",
       "      <td>0.414687</td>\n",
       "      <td>0.631032</td>\n",
       "      <td>0.077322</td>\n",
       "      <td>0.010182</td>\n",
       "      <td>0.019912</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28292.0</td>\n",
       "      <td>1.050879</td>\n",
       "      <td>0.053408</td>\n",
       "      <td>1.364590</td>\n",
       "      <td>2.666158</td>\n",
       "      <td>-0.378636</td>\n",
       "      <td>1.382032</td>\n",
       "      <td>-0.766202</td>\n",
       "      <td>0.486126</td>\n",
       "      <td>0.152611</td>\n",
       "      <td>...</td>\n",
       "      <td>0.083467</td>\n",
       "      <td>0.624424</td>\n",
       "      <td>-0.157228</td>\n",
       "      <td>-0.240411</td>\n",
       "      <td>0.573061</td>\n",
       "      <td>0.244090</td>\n",
       "      <td>0.063834</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28488.0</td>\n",
       "      <td>1.070316</td>\n",
       "      <td>0.079499</td>\n",
       "      <td>1.471856</td>\n",
       "      <td>2.863786</td>\n",
       "      <td>-0.637887</td>\n",
       "      <td>0.858159</td>\n",
       "      <td>-0.687478</td>\n",
       "      <td>0.344146</td>\n",
       "      <td>0.459561</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048067</td>\n",
       "      <td>0.534713</td>\n",
       "      <td>-0.098645</td>\n",
       "      <td>0.129272</td>\n",
       "      <td>0.543737</td>\n",
       "      <td>0.242724</td>\n",
       "      <td>0.065070</td>\n",
       "      <td>0.023500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31392.0</td>\n",
       "      <td>-3.680953</td>\n",
       "      <td>-4.183581</td>\n",
       "      <td>2.642743</td>\n",
       "      <td>4.263802</td>\n",
       "      <td>4.643286</td>\n",
       "      <td>-0.225053</td>\n",
       "      <td>-3.733637</td>\n",
       "      <td>1.273037</td>\n",
       "      <td>0.015661</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649051</td>\n",
       "      <td>1.054124</td>\n",
       "      <td>0.795528</td>\n",
       "      <td>-0.901314</td>\n",
       "      <td>-0.425524</td>\n",
       "      <td>0.511675</td>\n",
       "      <td>0.125419</td>\n",
       "      <td>0.243671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Time        V1        V2        V3        V4        V5        V6  \\\n",
       "0   8748.0 -1.070416  0.304517  2.777064  2.154061  0.254450 -0.448529   \n",
       "1  27074.0  1.165628  0.423671  0.887635  2.740163 -0.338578 -0.142846   \n",
       "2  28292.0  1.050879  0.053408  1.364590  2.666158 -0.378636  1.382032   \n",
       "3  28488.0  1.070316  0.079499  1.471856  2.863786 -0.637887  0.858159   \n",
       "4  31392.0 -3.680953 -4.183581  2.642743  4.263802  4.643286 -0.225053   \n",
       "\n",
       "         V7        V8        V9  ...       V21       V22       V23       V24  \\\n",
       "0 -0.398691  0.144672  1.070900  ... -0.122032 -0.182351  0.019576  0.626023   \n",
       "1 -0.055628 -0.015325 -0.213621  ... -0.081184 -0.025694 -0.076609  0.414687   \n",
       "2 -0.766202  0.486126  0.152611  ...  0.083467  0.624424 -0.157228 -0.240411   \n",
       "3 -0.687478  0.344146  0.459561  ...  0.048067  0.534713 -0.098645  0.129272   \n",
       "4 -3.733637  1.273037  0.015661  ...  0.649051  1.054124  0.795528 -0.901314   \n",
       "\n",
       "        V25       V26       V27       V28  Amount  Class  \n",
       "0 -0.018518 -0.263291 -0.198600  0.098435     0.0      0  \n",
       "1  0.631032  0.077322  0.010182  0.019912     0.0      0  \n",
       "2  0.573061  0.244090  0.063834  0.010981     0.0      0  \n",
       "3  0.543737  0.242724  0.065070  0.023500     0.0      0  \n",
       "4 -0.425524  0.511675  0.125419  0.243671     0.0      0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT *\n",
    "FROM bigquery-public-data.ml_datasets.ulb_fraud_detection # this cannot be parameterized with magics\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d601bceb-2164-4006-b69e-af489e6bb27a",
   "metadata": {},
   "source": [
    "---\n",
    "## BigQuery From Python With BigFrames API\n",
    "\n",
    "Use the [BigQuery BigFrames API](https://cloud.google.com/python/docs/reference/bigframes/latest) to query BigQuery and directly interact with BigQuery objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "944a4072-3f8d-48ea-9467-bdea13aaedf4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Query job ff71911a-9804-4f88-bfa7-ff0a8ce2cf2a is DONE. 70.6 MB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=statmike-mlops-349915&j=bq:US:ff71911a-9804-4f88-bfa7-ff0a8ce2cf2a&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Query job b7eae00e-a500-456d-ad91-8b23edc90501 is DONE. 1.2 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=statmike-mlops-349915&j=bq:US:b7eae00e-a500-456d-ad91-8b23edc90501&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "Query job f641a450-6c94-4361-8f3c-b108e5bc2144 is DONE. 1.2 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=statmike-mlops-349915&j=bq:US:f641a450-6c94-4361-8f3c-b108e5bc2144&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Time</th>\n",
       "      <th>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "      <th>V6</th>\n",
       "      <th>V7</th>\n",
       "      <th>V8</th>\n",
       "      <th>V9</th>\n",
       "      <th>...</th>\n",
       "      <th>V21</th>\n",
       "      <th>V22</th>\n",
       "      <th>V23</th>\n",
       "      <th>V24</th>\n",
       "      <th>V25</th>\n",
       "      <th>V26</th>\n",
       "      <th>V27</th>\n",
       "      <th>V28</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8748.0</td>\n",
       "      <td>-1.070416</td>\n",
       "      <td>0.304517</td>\n",
       "      <td>2.777064</td>\n",
       "      <td>2.154061</td>\n",
       "      <td>0.25445</td>\n",
       "      <td>-0.448529</td>\n",
       "      <td>-0.398691</td>\n",
       "      <td>0.144672</td>\n",
       "      <td>1.0709</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.122032</td>\n",
       "      <td>-0.182351</td>\n",
       "      <td>0.019576</td>\n",
       "      <td>0.626023</td>\n",
       "      <td>-0.018518</td>\n",
       "      <td>-0.263291</td>\n",
       "      <td>-0.1986</td>\n",
       "      <td>0.098435</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>31392.0</td>\n",
       "      <td>-3.680953</td>\n",
       "      <td>-4.183581</td>\n",
       "      <td>2.642743</td>\n",
       "      <td>4.263802</td>\n",
       "      <td>4.643286</td>\n",
       "      <td>-0.225053</td>\n",
       "      <td>-3.733637</td>\n",
       "      <td>1.273037</td>\n",
       "      <td>0.015661</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649051</td>\n",
       "      <td>1.054124</td>\n",
       "      <td>0.795528</td>\n",
       "      <td>-0.901314</td>\n",
       "      <td>-0.425524</td>\n",
       "      <td>0.511675</td>\n",
       "      <td>0.125419</td>\n",
       "      <td>0.243671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28292.0</td>\n",
       "      <td>1.050879</td>\n",
       "      <td>0.053408</td>\n",
       "      <td>1.36459</td>\n",
       "      <td>2.666158</td>\n",
       "      <td>-0.378636</td>\n",
       "      <td>1.382032</td>\n",
       "      <td>-0.766202</td>\n",
       "      <td>0.486126</td>\n",
       "      <td>0.152611</td>\n",
       "      <td>...</td>\n",
       "      <td>0.083467</td>\n",
       "      <td>0.624424</td>\n",
       "      <td>-0.157228</td>\n",
       "      <td>-0.240411</td>\n",
       "      <td>0.573061</td>\n",
       "      <td>0.24409</td>\n",
       "      <td>0.063834</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28488.0</td>\n",
       "      <td>1.070316</td>\n",
       "      <td>0.079499</td>\n",
       "      <td>1.471856</td>\n",
       "      <td>2.863786</td>\n",
       "      <td>-0.637887</td>\n",
       "      <td>0.858159</td>\n",
       "      <td>-0.687478</td>\n",
       "      <td>0.344146</td>\n",
       "      <td>0.459561</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048067</td>\n",
       "      <td>0.534713</td>\n",
       "      <td>-0.098645</td>\n",
       "      <td>0.129272</td>\n",
       "      <td>0.543737</td>\n",
       "      <td>0.242724</td>\n",
       "      <td>0.06507</td>\n",
       "      <td>0.0235</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>27074.0</td>\n",
       "      <td>1.165628</td>\n",
       "      <td>0.423671</td>\n",
       "      <td>0.887635</td>\n",
       "      <td>2.740163</td>\n",
       "      <td>-0.338578</td>\n",
       "      <td>-0.142846</td>\n",
       "      <td>-0.055628</td>\n",
       "      <td>-0.015325</td>\n",
       "      <td>-0.213621</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081184</td>\n",
       "      <td>-0.025694</td>\n",
       "      <td>-0.076609</td>\n",
       "      <td>0.414687</td>\n",
       "      <td>0.631032</td>\n",
       "      <td>0.077322</td>\n",
       "      <td>0.010182</td>\n",
       "      <td>0.019912</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>[5 rows x 31 columns in total]"
      ],
      "text/plain": [
       "      Time        V1        V2        V3        V4        V5        V6  \\\n",
       "0   8748.0 -1.070416  0.304517  2.777064  2.154061   0.25445 -0.448529   \n",
       "1  31392.0 -3.680953 -4.183581  2.642743  4.263802  4.643286 -0.225053   \n",
       "2  28292.0  1.050879  0.053408   1.36459  2.666158 -0.378636  1.382032   \n",
       "3  28488.0  1.070316  0.079499  1.471856  2.863786 -0.637887  0.858159   \n",
       "4  27074.0  1.165628  0.423671  0.887635  2.740163 -0.338578 -0.142846   \n",
       "\n",
       "         V7        V8        V9  ...       V21       V22       V23       V24  \\\n",
       "0 -0.398691  0.144672    1.0709  ... -0.122032 -0.182351  0.019576  0.626023   \n",
       "1 -3.733637  1.273037  0.015661  ...  0.649051  1.054124  0.795528 -0.901314   \n",
       "2 -0.766202  0.486126  0.152611  ...  0.083467  0.624424 -0.157228 -0.240411   \n",
       "3 -0.687478  0.344146  0.459561  ...  0.048067  0.534713 -0.098645  0.129272   \n",
       "4 -0.055628 -0.015325 -0.213621  ... -0.081184 -0.025694 -0.076609  0.414687   \n",
       "\n",
       "        V25       V26       V27       V28  Amount  Class  \n",
       "0 -0.018518 -0.263291   -0.1986  0.098435     0.0      0  \n",
       "1 -0.425524  0.511675  0.125419  0.243671     0.0      0  \n",
       "2  0.573061   0.24409  0.063834  0.010981     0.0      0  \n",
       "3  0.543737  0.242724   0.06507    0.0235     0.0      0  \n",
       "4  0.631032  0.077322  0.010182  0.019912     0.0      0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_bigframes = bpd.read_gbq(f'SELECT * FROM `{BQ_SOURCE}` LIMIT 5')\n",
    "bq_data_bigframes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "4c3c7733-d1b7-4696-bfe4-609bd9c2b684",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bigframes.dataframe.DataFrame"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(bq_data_bigframes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "6a887945-47b2-4f7e-872f-63a43de9452f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Query job acd60a38-b2fd-4422-8e2b-3e4c8f22f4a9 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=statmike-mlops-349915&j=bq:US:acd60a38-b2fd-4422-8e2b-3e4c8f22f4a9&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "(5, 31)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_bigframes.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "fa9c7e75-9203-442d-98d2-6f99c27e7ddb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "Query job d1cfc564-3c34-49b5-a467-3ac313e7123f is DONE. 1.2 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=statmike-mlops-349915&j=bq:US:d1cfc564-3c34-49b5-a467-3ac313e7123f&page=queryresults\">Open Job</a>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_bigframes = bq_data_bigframes.head().to_pandas()\n",
    "type(bq_data_bigframes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "180d1889-9220-4649-ba87-3017a43c69fd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 31)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_bigframes.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c1364e6-6ab9-490e-81b9-da8efbb6bfeb",
   "metadata": {},
   "source": [
    "---\n",
    "## BigQuery From Python\n",
    "\n",
    "Use the [BigQuery Python Client](https://cloud.google.com/python/docs/reference/bigquery/latest) to query BigQuery and return results as Pandas dataframes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb5aec96-54ee-4dda-be61-291b4cbad95b",
   "metadata": {},
   "source": [
    "### BigQuery API From Python\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f6cc5b3d-7088-47d2-96b3-dcaa957f0e54",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "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>Time</th>\n",
       "      <th>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "      <th>V6</th>\n",
       "      <th>V7</th>\n",
       "      <th>V8</th>\n",
       "      <th>V9</th>\n",
       "      <th>...</th>\n",
       "      <th>V21</th>\n",
       "      <th>V22</th>\n",
       "      <th>V23</th>\n",
       "      <th>V24</th>\n",
       "      <th>V25</th>\n",
       "      <th>V26</th>\n",
       "      <th>V27</th>\n",
       "      <th>V28</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8748.0</td>\n",
       "      <td>-1.070416</td>\n",
       "      <td>0.304517</td>\n",
       "      <td>2.777064</td>\n",
       "      <td>2.154061</td>\n",
       "      <td>0.254450</td>\n",
       "      <td>-0.448529</td>\n",
       "      <td>-0.398691</td>\n",
       "      <td>0.144672</td>\n",
       "      <td>1.070900</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.122032</td>\n",
       "      <td>-0.182351</td>\n",
       "      <td>0.019576</td>\n",
       "      <td>0.626023</td>\n",
       "      <td>-0.018518</td>\n",
       "      <td>-0.263291</td>\n",
       "      <td>-0.198600</td>\n",
       "      <td>0.098435</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27074.0</td>\n",
       "      <td>1.165628</td>\n",
       "      <td>0.423671</td>\n",
       "      <td>0.887635</td>\n",
       "      <td>2.740163</td>\n",
       "      <td>-0.338578</td>\n",
       "      <td>-0.142846</td>\n",
       "      <td>-0.055628</td>\n",
       "      <td>-0.015325</td>\n",
       "      <td>-0.213621</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081184</td>\n",
       "      <td>-0.025694</td>\n",
       "      <td>-0.076609</td>\n",
       "      <td>0.414687</td>\n",
       "      <td>0.631032</td>\n",
       "      <td>0.077322</td>\n",
       "      <td>0.010182</td>\n",
       "      <td>0.019912</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28292.0</td>\n",
       "      <td>1.050879</td>\n",
       "      <td>0.053408</td>\n",
       "      <td>1.364590</td>\n",
       "      <td>2.666158</td>\n",
       "      <td>-0.378636</td>\n",
       "      <td>1.382032</td>\n",
       "      <td>-0.766202</td>\n",
       "      <td>0.486126</td>\n",
       "      <td>0.152611</td>\n",
       "      <td>...</td>\n",
       "      <td>0.083467</td>\n",
       "      <td>0.624424</td>\n",
       "      <td>-0.157228</td>\n",
       "      <td>-0.240411</td>\n",
       "      <td>0.573061</td>\n",
       "      <td>0.244090</td>\n",
       "      <td>0.063834</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28488.0</td>\n",
       "      <td>1.070316</td>\n",
       "      <td>0.079499</td>\n",
       "      <td>1.471856</td>\n",
       "      <td>2.863786</td>\n",
       "      <td>-0.637887</td>\n",
       "      <td>0.858159</td>\n",
       "      <td>-0.687478</td>\n",
       "      <td>0.344146</td>\n",
       "      <td>0.459561</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048067</td>\n",
       "      <td>0.534713</td>\n",
       "      <td>-0.098645</td>\n",
       "      <td>0.129272</td>\n",
       "      <td>0.543737</td>\n",
       "      <td>0.242724</td>\n",
       "      <td>0.065070</td>\n",
       "      <td>0.023500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31392.0</td>\n",
       "      <td>-3.680953</td>\n",
       "      <td>-4.183581</td>\n",
       "      <td>2.642743</td>\n",
       "      <td>4.263802</td>\n",
       "      <td>4.643286</td>\n",
       "      <td>-0.225053</td>\n",
       "      <td>-3.733637</td>\n",
       "      <td>1.273037</td>\n",
       "      <td>0.015661</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649051</td>\n",
       "      <td>1.054124</td>\n",
       "      <td>0.795528</td>\n",
       "      <td>-0.901314</td>\n",
       "      <td>-0.425524</td>\n",
       "      <td>0.511675</td>\n",
       "      <td>0.125419</td>\n",
       "      <td>0.243671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Time        V1        V2        V3        V4        V5        V6  \\\n",
       "0   8748.0 -1.070416  0.304517  2.777064  2.154061  0.254450 -0.448529   \n",
       "1  27074.0  1.165628  0.423671  0.887635  2.740163 -0.338578 -0.142846   \n",
       "2  28292.0  1.050879  0.053408  1.364590  2.666158 -0.378636  1.382032   \n",
       "3  28488.0  1.070316  0.079499  1.471856  2.863786 -0.637887  0.858159   \n",
       "4  31392.0 -3.680953 -4.183581  2.642743  4.263802  4.643286 -0.225053   \n",
       "\n",
       "         V7        V8        V9  ...       V21       V22       V23       V24  \\\n",
       "0 -0.398691  0.144672  1.070900  ... -0.122032 -0.182351  0.019576  0.626023   \n",
       "1 -0.055628 -0.015325 -0.213621  ... -0.081184 -0.025694 -0.076609  0.414687   \n",
       "2 -0.766202  0.486126  0.152611  ...  0.083467  0.624424 -0.157228 -0.240411   \n",
       "3 -0.687478  0.344146  0.459561  ...  0.048067  0.534713 -0.098645  0.129272   \n",
       "4 -3.733637  1.273037  0.015661  ...  0.649051  1.054124  0.795528 -0.901314   \n",
       "\n",
       "        V25       V26       V27       V28  Amount  Class  \n",
       "0 -0.018518 -0.263291 -0.198600  0.098435     0.0      0  \n",
       "1  0.631032  0.077322  0.010182  0.019912     0.0      0  \n",
       "2  0.573061  0.244090  0.063834  0.010981     0.0      0  \n",
       "3  0.543737  0.242724  0.065070  0.023500     0.0      0  \n",
       "4 -0.425524  0.511675  0.125419  0.243671     0.0      0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = f\"\"\"\n",
    "    SELECT * \n",
    "    FROM `{BQ_SOURCE}`\n",
    "    LIMIT 5\n",
    "\"\"\"\n",
    "preview = bq.query(query = query).to_dataframe()\n",
    "preview"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6f24720-dccf-4e37-8001-d8108ddc8694",
   "metadata": {},
   "source": [
    "### BigQuery Python Client: Helper Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "cf30dd14-c99e-4cdf-a545-54c09d42a8fa",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def bq_runner(query):\n",
    "    return bq.query(query = query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "cf643294-e707-4e7f-a75e-2cd53762ab3f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "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>Time</th>\n",
       "      <th>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "      <th>V6</th>\n",
       "      <th>V7</th>\n",
       "      <th>V8</th>\n",
       "      <th>V9</th>\n",
       "      <th>...</th>\n",
       "      <th>V21</th>\n",
       "      <th>V22</th>\n",
       "      <th>V23</th>\n",
       "      <th>V24</th>\n",
       "      <th>V25</th>\n",
       "      <th>V26</th>\n",
       "      <th>V27</th>\n",
       "      <th>V28</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8748.0</td>\n",
       "      <td>-1.070416</td>\n",
       "      <td>0.304517</td>\n",
       "      <td>2.777064</td>\n",
       "      <td>2.154061</td>\n",
       "      <td>0.254450</td>\n",
       "      <td>-0.448529</td>\n",
       "      <td>-0.398691</td>\n",
       "      <td>0.144672</td>\n",
       "      <td>1.070900</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.122032</td>\n",
       "      <td>-0.182351</td>\n",
       "      <td>0.019576</td>\n",
       "      <td>0.626023</td>\n",
       "      <td>-0.018518</td>\n",
       "      <td>-0.263291</td>\n",
       "      <td>-0.198600</td>\n",
       "      <td>0.098435</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27074.0</td>\n",
       "      <td>1.165628</td>\n",
       "      <td>0.423671</td>\n",
       "      <td>0.887635</td>\n",
       "      <td>2.740163</td>\n",
       "      <td>-0.338578</td>\n",
       "      <td>-0.142846</td>\n",
       "      <td>-0.055628</td>\n",
       "      <td>-0.015325</td>\n",
       "      <td>-0.213621</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081184</td>\n",
       "      <td>-0.025694</td>\n",
       "      <td>-0.076609</td>\n",
       "      <td>0.414687</td>\n",
       "      <td>0.631032</td>\n",
       "      <td>0.077322</td>\n",
       "      <td>0.010182</td>\n",
       "      <td>0.019912</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28292.0</td>\n",
       "      <td>1.050879</td>\n",
       "      <td>0.053408</td>\n",
       "      <td>1.364590</td>\n",
       "      <td>2.666158</td>\n",
       "      <td>-0.378636</td>\n",
       "      <td>1.382032</td>\n",
       "      <td>-0.766202</td>\n",
       "      <td>0.486126</td>\n",
       "      <td>0.152611</td>\n",
       "      <td>...</td>\n",
       "      <td>0.083467</td>\n",
       "      <td>0.624424</td>\n",
       "      <td>-0.157228</td>\n",
       "      <td>-0.240411</td>\n",
       "      <td>0.573061</td>\n",
       "      <td>0.244090</td>\n",
       "      <td>0.063834</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28488.0</td>\n",
       "      <td>1.070316</td>\n",
       "      <td>0.079499</td>\n",
       "      <td>1.471856</td>\n",
       "      <td>2.863786</td>\n",
       "      <td>-0.637887</td>\n",
       "      <td>0.858159</td>\n",
       "      <td>-0.687478</td>\n",
       "      <td>0.344146</td>\n",
       "      <td>0.459561</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048067</td>\n",
       "      <td>0.534713</td>\n",
       "      <td>-0.098645</td>\n",
       "      <td>0.129272</td>\n",
       "      <td>0.543737</td>\n",
       "      <td>0.242724</td>\n",
       "      <td>0.065070</td>\n",
       "      <td>0.023500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31392.0</td>\n",
       "      <td>-3.680953</td>\n",
       "      <td>-4.183581</td>\n",
       "      <td>2.642743</td>\n",
       "      <td>4.263802</td>\n",
       "      <td>4.643286</td>\n",
       "      <td>-0.225053</td>\n",
       "      <td>-3.733637</td>\n",
       "      <td>1.273037</td>\n",
       "      <td>0.015661</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649051</td>\n",
       "      <td>1.054124</td>\n",
       "      <td>0.795528</td>\n",
       "      <td>-0.901314</td>\n",
       "      <td>-0.425524</td>\n",
       "      <td>0.511675</td>\n",
       "      <td>0.125419</td>\n",
       "      <td>0.243671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Time        V1        V2        V3        V4        V5        V6  \\\n",
       "0   8748.0 -1.070416  0.304517  2.777064  2.154061  0.254450 -0.448529   \n",
       "1  27074.0  1.165628  0.423671  0.887635  2.740163 -0.338578 -0.142846   \n",
       "2  28292.0  1.050879  0.053408  1.364590  2.666158 -0.378636  1.382032   \n",
       "3  28488.0  1.070316  0.079499  1.471856  2.863786 -0.637887  0.858159   \n",
       "4  31392.0 -3.680953 -4.183581  2.642743  4.263802  4.643286 -0.225053   \n",
       "\n",
       "         V7        V8        V9  ...       V21       V22       V23       V24  \\\n",
       "0 -0.398691  0.144672  1.070900  ... -0.122032 -0.182351  0.019576  0.626023   \n",
       "1 -0.055628 -0.015325 -0.213621  ... -0.081184 -0.025694 -0.076609  0.414687   \n",
       "2 -0.766202  0.486126  0.152611  ...  0.083467  0.624424 -0.157228 -0.240411   \n",
       "3 -0.687478  0.344146  0.459561  ...  0.048067  0.534713 -0.098645  0.129272   \n",
       "4 -3.733637  1.273037  0.015661  ...  0.649051  1.054124  0.795528 -0.901314   \n",
       "\n",
       "        V25       V26       V27       V28  Amount  Class  \n",
       "0 -0.018518 -0.263291 -0.198600  0.098435     0.0      0  \n",
       "1  0.631032  0.077322  0.010182  0.019912     0.0      0  \n",
       "2  0.573061  0.244090  0.063834  0.010981     0.0      0  \n",
       "3  0.543737  0.242724  0.065070  0.023500     0.0      0  \n",
       "4 -0.425524  0.511675  0.125419  0.243671     0.0      0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_runner(\n",
    "    query = f\"\"\"\n",
    "        SELECT * \n",
    "        FROM `{BQ_SOURCE}`\n",
    "        LIMIT 5\n",
    "    \"\"\"\n",
    ").to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84047824-5f9a-464b-aea1-e98049264dfb",
   "metadata": {},
   "source": [
    "### BigQuery Python Client: Using Query Job Attributes and Methods\n",
    "\n",
    "Query Jobs have Methods and Attributes that can benefit the Python workflow:\n",
    "- Query Job [Methods](https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.job.QueryJob.html#google.cloud.bigquery.job.QueryJob:~:text=for%20accurate%20signature.-,Methods,-__init__(job_id%2C%C2%A0query)\n",
    "- Query Job [Attributes](https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.job.QueryJob.html#google.cloud.bigquery.job.QueryJob:~:text=from%20a%20QueryJob-,Attributes,-allow_large_results)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee620e57-ba1c-4971-952a-60cc00218bf1",
   "metadata": {},
   "source": [
    "BigQuery Query Job (using helper function):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2960bb08-50d5-48af-92f7-bb7c0f0c8b2b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "job = bq_runner(\n",
    "    query = f\"\"\"\n",
    "        SELECT * \n",
    "        FROM `{BQ_SOURCE}`\n",
    "        LIMIT 5\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1304cb8-aa40-4c2b-b2e6-3a5abd12a0be",
   "metadata": {},
   "source": [
    "Using Query Job Atrributes to get timing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "894c81a6-95e8-4c86-993b-ce697655bc1e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.216"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job.result()\n",
    "(job.ended-job.started).total_seconds()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9fad109-ece5-4a38-9afd-0a20e3432464",
   "metadata": {},
   "source": [
    "Using Query Job Methods to retrieve result to Pandas dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "34d63e6f-de58-4004-bc33-1c4111308137",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "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>Time</th>\n",
       "      <th>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "      <th>V6</th>\n",
       "      <th>V7</th>\n",
       "      <th>V8</th>\n",
       "      <th>V9</th>\n",
       "      <th>...</th>\n",
       "      <th>V21</th>\n",
       "      <th>V22</th>\n",
       "      <th>V23</th>\n",
       "      <th>V24</th>\n",
       "      <th>V25</th>\n",
       "      <th>V26</th>\n",
       "      <th>V27</th>\n",
       "      <th>V28</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8748.0</td>\n",
       "      <td>-1.070416</td>\n",
       "      <td>0.304517</td>\n",
       "      <td>2.777064</td>\n",
       "      <td>2.154061</td>\n",
       "      <td>0.254450</td>\n",
       "      <td>-0.448529</td>\n",
       "      <td>-0.398691</td>\n",
       "      <td>0.144672</td>\n",
       "      <td>1.070900</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.122032</td>\n",
       "      <td>-0.182351</td>\n",
       "      <td>0.019576</td>\n",
       "      <td>0.626023</td>\n",
       "      <td>-0.018518</td>\n",
       "      <td>-0.263291</td>\n",
       "      <td>-0.198600</td>\n",
       "      <td>0.098435</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27074.0</td>\n",
       "      <td>1.165628</td>\n",
       "      <td>0.423671</td>\n",
       "      <td>0.887635</td>\n",
       "      <td>2.740163</td>\n",
       "      <td>-0.338578</td>\n",
       "      <td>-0.142846</td>\n",
       "      <td>-0.055628</td>\n",
       "      <td>-0.015325</td>\n",
       "      <td>-0.213621</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081184</td>\n",
       "      <td>-0.025694</td>\n",
       "      <td>-0.076609</td>\n",
       "      <td>0.414687</td>\n",
       "      <td>0.631032</td>\n",
       "      <td>0.077322</td>\n",
       "      <td>0.010182</td>\n",
       "      <td>0.019912</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28292.0</td>\n",
       "      <td>1.050879</td>\n",
       "      <td>0.053408</td>\n",
       "      <td>1.364590</td>\n",
       "      <td>2.666158</td>\n",
       "      <td>-0.378636</td>\n",
       "      <td>1.382032</td>\n",
       "      <td>-0.766202</td>\n",
       "      <td>0.486126</td>\n",
       "      <td>0.152611</td>\n",
       "      <td>...</td>\n",
       "      <td>0.083467</td>\n",
       "      <td>0.624424</td>\n",
       "      <td>-0.157228</td>\n",
       "      <td>-0.240411</td>\n",
       "      <td>0.573061</td>\n",
       "      <td>0.244090</td>\n",
       "      <td>0.063834</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28488.0</td>\n",
       "      <td>1.070316</td>\n",
       "      <td>0.079499</td>\n",
       "      <td>1.471856</td>\n",
       "      <td>2.863786</td>\n",
       "      <td>-0.637887</td>\n",
       "      <td>0.858159</td>\n",
       "      <td>-0.687478</td>\n",
       "      <td>0.344146</td>\n",
       "      <td>0.459561</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048067</td>\n",
       "      <td>0.534713</td>\n",
       "      <td>-0.098645</td>\n",
       "      <td>0.129272</td>\n",
       "      <td>0.543737</td>\n",
       "      <td>0.242724</td>\n",
       "      <td>0.065070</td>\n",
       "      <td>0.023500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31392.0</td>\n",
       "      <td>-3.680953</td>\n",
       "      <td>-4.183581</td>\n",
       "      <td>2.642743</td>\n",
       "      <td>4.263802</td>\n",
       "      <td>4.643286</td>\n",
       "      <td>-0.225053</td>\n",
       "      <td>-3.733637</td>\n",
       "      <td>1.273037</td>\n",
       "      <td>0.015661</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649051</td>\n",
       "      <td>1.054124</td>\n",
       "      <td>0.795528</td>\n",
       "      <td>-0.901314</td>\n",
       "      <td>-0.425524</td>\n",
       "      <td>0.511675</td>\n",
       "      <td>0.125419</td>\n",
       "      <td>0.243671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Time        V1        V2        V3        V4        V5        V6  \\\n",
       "0   8748.0 -1.070416  0.304517  2.777064  2.154061  0.254450 -0.448529   \n",
       "1  27074.0  1.165628  0.423671  0.887635  2.740163 -0.338578 -0.142846   \n",
       "2  28292.0  1.050879  0.053408  1.364590  2.666158 -0.378636  1.382032   \n",
       "3  28488.0  1.070316  0.079499  1.471856  2.863786 -0.637887  0.858159   \n",
       "4  31392.0 -3.680953 -4.183581  2.642743  4.263802  4.643286 -0.225053   \n",
       "\n",
       "         V7        V8        V9  ...       V21       V22       V23       V24  \\\n",
       "0 -0.398691  0.144672  1.070900  ... -0.122032 -0.182351  0.019576  0.626023   \n",
       "1 -0.055628 -0.015325 -0.213621  ... -0.081184 -0.025694 -0.076609  0.414687   \n",
       "2 -0.766202  0.486126  0.152611  ...  0.083467  0.624424 -0.157228 -0.240411   \n",
       "3 -0.687478  0.344146  0.459561  ...  0.048067  0.534713 -0.098645  0.129272   \n",
       "4 -3.733637  1.273037  0.015661  ...  0.649051  1.054124  0.795528 -0.901314   \n",
       "\n",
       "        V25       V26       V27       V28  Amount  Class  \n",
       "0 -0.018518 -0.263291 -0.198600  0.098435     0.0      0  \n",
       "1  0.631032  0.077322  0.010182  0.019912     0.0      0  \n",
       "2  0.573061  0.244090  0.063834  0.010981     0.0      0  \n",
       "3  0.543737  0.242724  0.065070  0.023500     0.0      0  \n",
       "4 -0.425524  0.511675  0.125419  0.243671     0.0      0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a4e8e5d-d753-4aa4-86ec-9a6fc21ec6ca",
   "metadata": {},
   "source": [
    "### Indirect use with pandas-gbq\n",
    "\n",
    "When working with [Pandas](https://pandas.pydata.org/docs/user_guide/index.html#user-guide) the methods above show the client returning data to pandas dataframes.  This section will show a pandas mudule, [pandas-gbq](https://pandas-gbq.readthedocs.io/en/latest/) that wraps the BigQuery client so that pandas can retrieve BigQuery data to dataframes.\n",
    "\n",
    "References:\n",
    "- [Comparison of BigQuery Client with pandas-gbq](https://cloud.google.com/bigquery/docs/pandas-gbq-migration)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62e68b60-d21e-476a-8454-c4aa43bdd9de",
   "metadata": {},
   "source": [
    "#### Using pandas-gbq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "47816cbf-d9ad-491f-9e12-246a03da5f61",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = f\"\"\"\n",
    "SELECT * \n",
    "FROM `{BQ_SOURCE}`\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "df = pd.read_gbq(query, project_id = PROJECT_ID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "4cb087a8-b994-41ad-adbf-6e954b99473c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "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>Time</th>\n",
       "      <th>V1</th>\n",
       "      <th>V2</th>\n",
       "      <th>V3</th>\n",
       "      <th>V4</th>\n",
       "      <th>V5</th>\n",
       "      <th>V6</th>\n",
       "      <th>V7</th>\n",
       "      <th>V8</th>\n",
       "      <th>V9</th>\n",
       "      <th>...</th>\n",
       "      <th>V21</th>\n",
       "      <th>V22</th>\n",
       "      <th>V23</th>\n",
       "      <th>V24</th>\n",
       "      <th>V25</th>\n",
       "      <th>V26</th>\n",
       "      <th>V27</th>\n",
       "      <th>V28</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8748.0</td>\n",
       "      <td>-1.070416</td>\n",
       "      <td>0.304517</td>\n",
       "      <td>2.777064</td>\n",
       "      <td>2.154061</td>\n",
       "      <td>0.254450</td>\n",
       "      <td>-0.448529</td>\n",
       "      <td>-0.398691</td>\n",
       "      <td>0.144672</td>\n",
       "      <td>1.070900</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.122032</td>\n",
       "      <td>-0.182351</td>\n",
       "      <td>0.019576</td>\n",
       "      <td>0.626023</td>\n",
       "      <td>-0.018518</td>\n",
       "      <td>-0.263291</td>\n",
       "      <td>-0.198600</td>\n",
       "      <td>0.098435</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>27074.0</td>\n",
       "      <td>1.165628</td>\n",
       "      <td>0.423671</td>\n",
       "      <td>0.887635</td>\n",
       "      <td>2.740163</td>\n",
       "      <td>-0.338578</td>\n",
       "      <td>-0.142846</td>\n",
       "      <td>-0.055628</td>\n",
       "      <td>-0.015325</td>\n",
       "      <td>-0.213621</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.081184</td>\n",
       "      <td>-0.025694</td>\n",
       "      <td>-0.076609</td>\n",
       "      <td>0.414687</td>\n",
       "      <td>0.631032</td>\n",
       "      <td>0.077322</td>\n",
       "      <td>0.010182</td>\n",
       "      <td>0.019912</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28292.0</td>\n",
       "      <td>1.050879</td>\n",
       "      <td>0.053408</td>\n",
       "      <td>1.364590</td>\n",
       "      <td>2.666158</td>\n",
       "      <td>-0.378636</td>\n",
       "      <td>1.382032</td>\n",
       "      <td>-0.766202</td>\n",
       "      <td>0.486126</td>\n",
       "      <td>0.152611</td>\n",
       "      <td>...</td>\n",
       "      <td>0.083467</td>\n",
       "      <td>0.624424</td>\n",
       "      <td>-0.157228</td>\n",
       "      <td>-0.240411</td>\n",
       "      <td>0.573061</td>\n",
       "      <td>0.244090</td>\n",
       "      <td>0.063834</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28488.0</td>\n",
       "      <td>1.070316</td>\n",
       "      <td>0.079499</td>\n",
       "      <td>1.471856</td>\n",
       "      <td>2.863786</td>\n",
       "      <td>-0.637887</td>\n",
       "      <td>0.858159</td>\n",
       "      <td>-0.687478</td>\n",
       "      <td>0.344146</td>\n",
       "      <td>0.459561</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048067</td>\n",
       "      <td>0.534713</td>\n",
       "      <td>-0.098645</td>\n",
       "      <td>0.129272</td>\n",
       "      <td>0.543737</td>\n",
       "      <td>0.242724</td>\n",
       "      <td>0.065070</td>\n",
       "      <td>0.023500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>31392.0</td>\n",
       "      <td>-3.680953</td>\n",
       "      <td>-4.183581</td>\n",
       "      <td>2.642743</td>\n",
       "      <td>4.263802</td>\n",
       "      <td>4.643286</td>\n",
       "      <td>-0.225053</td>\n",
       "      <td>-3.733637</td>\n",
       "      <td>1.273037</td>\n",
       "      <td>0.015661</td>\n",
       "      <td>...</td>\n",
       "      <td>0.649051</td>\n",
       "      <td>1.054124</td>\n",
       "      <td>0.795528</td>\n",
       "      <td>-0.901314</td>\n",
       "      <td>-0.425524</td>\n",
       "      <td>0.511675</td>\n",
       "      <td>0.125419</td>\n",
       "      <td>0.243671</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Time        V1        V2        V3        V4        V5        V6  \\\n",
       "0   8748.0 -1.070416  0.304517  2.777064  2.154061  0.254450 -0.448529   \n",
       "1  27074.0  1.165628  0.423671  0.887635  2.740163 -0.338578 -0.142846   \n",
       "2  28292.0  1.050879  0.053408  1.364590  2.666158 -0.378636  1.382032   \n",
       "3  28488.0  1.070316  0.079499  1.471856  2.863786 -0.637887  0.858159   \n",
       "4  31392.0 -3.680953 -4.183581  2.642743  4.263802  4.643286 -0.225053   \n",
       "\n",
       "         V7        V8        V9  ...       V21       V22       V23       V24  \\\n",
       "0 -0.398691  0.144672  1.070900  ... -0.122032 -0.182351  0.019576  0.626023   \n",
       "1 -0.055628 -0.015325 -0.213621  ... -0.081184 -0.025694 -0.076609  0.414687   \n",
       "2 -0.766202  0.486126  0.152611  ...  0.083467  0.624424 -0.157228 -0.240411   \n",
       "3 -0.687478  0.344146  0.459561  ...  0.048067  0.534713 -0.098645  0.129272   \n",
       "4 -3.733637  1.273037  0.015661  ...  0.649051  1.054124  0.795528 -0.901314   \n",
       "\n",
       "        V25       V26       V27       V28  Amount  Class  \n",
       "0 -0.018518 -0.263291 -0.198600  0.098435     0.0      0  \n",
       "1  0.631032  0.077322  0.010182  0.019912     0.0      0  \n",
       "2  0.573061  0.244090  0.063834  0.010981     0.0      0  \n",
       "3  0.543737  0.242724  0.065070  0.023500     0.0      0  \n",
       "4 -0.425524  0.511675  0.125419  0.243671     0.0      0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40b72a43-ea3a-41e5-a32b-ccce77afe013",
   "metadata": {},
   "source": [
    "## TensorFlow I/O BigQuery Reader\n",
    "\n",
    "https://www.tensorflow.org/io"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "533a673d-209a-4326-9c4f-c5c35e1503f1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = f\"SELECT * FROM {BQ_PROJECT}.{BQ_DATASET}.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{BQ_TABLE}'\"\n",
    "schema = bq.query(query).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "4d8a3eb0-5758-4959-8a7b-67e8d4bedc20",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['FLOAT64', 'INT64']"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schema.data_type.unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "d768c04d-4b80-4268-a6c9-f0b2e5446cc4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "types = {\n",
    "    'FLOAT64' : dtypes.float64,\n",
    "    'INT64' : dtypes.int64,\n",
    "    'STRING' : dtypes.string\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "53f9914a-7c3f-40d7-be9f-e7b92f2e5771",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tensorflow_io.python.ops.bigquery_dataset_ops.BigQueryClient"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_tfio = BigQueryClient()\n",
    "type(bq_data_tfio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "e1c7efc4-a744-431b-bedb-b1f3ec5f1bed",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tensorflow_io.python.ops.bigquery_dataset_ops.BigQueryReadSession"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_tfio = bq_data_tfio.read_session(\n",
    "    parent = f\"projects/{PROJECT_ID}\",\n",
    "    project_id = BQ_PROJECT,\n",
    "    table_id = BQ_TABLE,\n",
    "    dataset_id = BQ_DATASET,\n",
    "    selected_fields = schema.column_name.tolist(),\n",
    "    output_types = [types[x] for x in schema.data_type.tolist()],\n",
    "    #row_restriction = f\"splits='{split}'\",\n",
    "    requested_streams = 3\n",
    ")\n",
    "type(bq_data_tfio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "51648acc-abac-429d-8771-b587594625b2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tensorflow.python.data.ops.interleave_op._ParallelInterleaveDataset"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_tfio = bq_data_tfio.parallel_read_rows(sloppy = True, num_parallel_calls = tf.data.experimental.AUTOTUNE)\n",
    "type(bq_data_tfio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "717cc7af-99b6-42cd-9277-b8f56e70ad20",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tensorflow.python.data.ops.batch_op._BatchDataset"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq_data_tfio = bq_data_tfio.batch(5)\n",
    "type(bq_data_tfio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "53a6483d-a5d8-4a28-a6b7-2c82dd056d3f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Amount', 'Class', 'Time', 'V1', 'V10', 'V11', 'V12', 'V13', 'V14', 'V15', 'V16', 'V17', 'V18', 'V19', 'V2', 'V20', 'V21', 'V22', 'V23', 'V24', 'V25', 'V26', 'V27', 'V28', 'V3', 'V4', 'V5', 'V6', 'V7', 'V8', 'V9']\n",
      "('Amount', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([0., 0., 0., 0., 0.])>)\n",
      "('Class', <tf.Tensor: shape=(5,), dtype=int64, numpy=array([0, 0, 0, 0, 0])>)\n",
      "('Time', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 8748., 27074., 28292., 28488., 31392.])>)\n",
      "('V1', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-1.07041642,  1.16562783,  1.05087886,  1.07031571, -3.68095312])>)\n",
      "('V10', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.31050073,  0.51726104,  0.49667421,  0.34086927,  0.63552321])>)\n",
      "('V11', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.09397115, -0.99559462,  0.3419823 , -1.14145224, -0.44290293])>)\n",
      "('V12', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-2.3937614 ,  0.23533528,  1.22483121,  0.3995012 ,  0.78233308])>)\n",
      "('V13', <tf.Tensor: shape=(5,), dtype=float64, numpy=\n",
      "array([ 1.59764667e+00,  3.14963854e-04,  2.64504286e-01, -1.69193603e-01,\n",
      "        2.18806508e-01])>)\n",
      "('V14', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.82894549, -0.21233819, -0.60555558, -0.6907271 , -0.41242782])>)\n",
      "('V15', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-1.66541352, -0.91059433, -1.95902219, -1.23202674,  0.43786598])>)\n",
      "('V16', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.69308042,  0.08897322,  0.00923392, -0.26137547, -2.1030061 ])>)\n",
      "('V17', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.16089981, -0.15486972, -0.11289605,  0.2836126 ,  1.89761056])>)\n",
      "('V18', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.0193183 , -0.53688058, -0.20986476, -0.75588249, -1.76242689])>)\n",
      "('V19', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-1.78306585, -0.61584876, -0.51406509, -0.92429028, -0.29850178])>)\n",
      "('V2', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.30451734,  0.42367133,  0.05340835,  0.07949856, -4.18358129])>)\n",
      "('V20', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.19555588, -0.17767519, -0.17042455, -0.22167668,  1.17757334])>)\n",
      "('V21', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.12203215, -0.08118352,  0.0834673 ,  0.04806741,  0.64905082])>)\n",
      "('V22', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.18235147, -0.02569424,  0.62442413,  0.53471307,  1.05412427])>)\n",
      "('V23', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.0195759 , -0.07660875, -0.15722779, -0.09864464,  0.79552799])>)\n",
      "('V24', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.62602328,  0.41468671, -0.24041107,  0.12927157, -0.90131426])>)\n",
      "('V25', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.01851757,  0.63103248,  0.57306089,  0.54373694, -0.42552427])>)\n",
      "('V26', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.26329053,  0.07732208,  0.24408998,  0.24272419,  0.51167489])>)\n",
      "('V27', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.19860014,  0.01018155,  0.06383414,  0.06507029,  0.12541936])>)\n",
      "('V28', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([0.09843517, 0.01991206, 0.01098072, 0.02350022, 0.24367101])>)\n",
      "('V3', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([2.7770644 , 0.88763475, 1.36459018, 1.47185559, 2.64274269])>)\n",
      "('V4', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([2.15406147, 2.74016301, 2.66615783, 2.86378616, 4.26380207])>)\n",
      "('V5', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.2544503 , -0.33857787, -0.37863581, -0.63788684,  4.6432857 ])>)\n",
      "('V6', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.44852947, -0.14284579,  1.38203151,  0.8581592 , -0.22505283])>)\n",
      "('V7', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([-0.39869079, -0.05562784, -0.7662021 , -0.68747791, -3.73363726])>)\n",
      "('V8', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 0.14467154, -0.01532457,  0.4861256 ,  0.34414609,  1.27303676])>)\n",
      "('V9', <tf.Tensor: shape=(5,), dtype=float64, numpy=array([ 1.07090041, -0.21362101,  0.15261147,  0.45956062,  0.0156615 ])>)\n"
     ]
    }
   ],
   "source": [
    "for rows in bq_data_tfio.take(1):\n",
    "    print(list(rows.keys()))\n",
    "    for item in rows.items():\n",
    "        print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53a3283e-4f29-449e-853a-84e093ad4b35",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## BigQuery Storage API From Python\n",
    "\n",
    "BigQuery has an API for directly reading and writing to storage - no need for a query which uses the compute side of BigQuery.  This interacts directly with a single table, not a view, and reads/write rows with some basic filterting capabilities to limit the returned records.  Since BigQuery is columnar storage you can boost efficiency by only reading the columns needed with these methods!\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a71a6acb-330c-4587-8bc9-065a09f314f6",
   "metadata": {},
   "source": [
    "### Create A Read Session\n",
    "\n",
    "Use the [BigQueryReadClient](https://cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.client.BigQueryReadClient) to create a read session with [create_read_session](https://cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.client.BigQueryReadClient#google_cloud_bigquery_storage_v1_client_BigQueryReadClient_create_read_session).\n",
    "\n",
    ">`max_stream_count` set the upper limit for the number of streams returned.  The actual number will be determined based on the tables size and storage layout and could be less than the value provided.  Specifying `0` indicates the system can determine the best value for the `max_stream_counts` and uses a default limit of 1000.\n",
    "\n",
    "The `read_session` is an [bigquery_storage.types.ReadSession()](https://cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ReadSession).\n",
    "\n",
    "Within the`read_session` the `read_options` is an [bigquery_storage.types.ReadSession.TableReadOptions()](https://cloud.google.com/python/docs/reference/bigquerystorage/latest/google.cloud.bigquery_storage_v1.types.ReadSession.TableReadOptions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6e72f854-0712-4237-bef7-c51c935d8beb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "read_session = bqstorage.create_read_session(\n",
    "    request = dict(\n",
    "        parent = f'projects/{PROJECT_ID}',\n",
    "        read_session = dict(\n",
    "            table = f\"projects/{BQ_PROJECT}/datasets/{BQ_DATASET}/tables/{BQ_TABLE}\",\n",
    "            data_format = bigquery_storage.types.DataFormat.ARROW,\n",
    "            read_options = dict(\n",
    "                #row_restriction = \"Amount > 0\",\n",
    "                selected_fields = [f'V{n+1}' for n in range(28)]\n",
    "            )\n",
    "        ),\n",
    "        max_stream_count = 0\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dec5664-6f0f-4aa2-9c00-1e18af57ccf7",
   "metadata": {},
   "source": [
    "Check for the number of streams returned.  A value of would mean all records would be read through a single stream.  A value larger than 1 indicates different streams would be used to read different parts of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "34db65db-9206-4b77-9cfb-448729ae4a29",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(read_session.streams)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fec64003-00ad-4678-84c9-7d3e89f9c1fa",
   "metadata": {},
   "source": [
    "### Read the table: One Stream At A Time\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "8f447bc4-de50-4892-bb31-ec0aaeeb01b5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def read_stream(stream):\n",
    "    # setup a reader\n",
    "    reader = bqstorage.read_rows(\n",
    "        name = stream.name\n",
    "    )\n",
    "\n",
    "    # start timer\n",
    "    start = timeit.default_timer()\n",
    "\n",
    "    # read rows from reader into a dataframe.  Note this is actually multiple operations - read and convert\n",
    "    train = reader.to_dataframe()#.drop(['splits', 'transaction_id'], axis = 1)\n",
    "\n",
    "    # stop timer and calculate elabsed time\n",
    "    execution_time = timeit.default_timer() - start\n",
    "\n",
    "    # report data shape and elapsed time\n",
    "    print(f'The stream ({stream.name}) read {train.shape[0]} rows and {train.shape[1]} columns in {execution_time} seconds')\n",
    "\n",
    "    return train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "9a2959c5-fe31-44f7-92ab-0d68d87c0974",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The stream (projects/statmike-mlops-349915/locations/us/sessions/CAISDFlUNnNySnJkeERUehoCamQaAmpxGgJpchoCb2oaAmpzGgJuYRoCb3caAnB6GgJpdxoCaWMaAmpyGgJqYxoCamYaAnBsGgJvdhoCamkaAmlhGgJweRoCb3MaAnB4/streams/GgJqZBoCanEaAmlyGgJvahoCanMaAm5hGgJvdxoCcHoaAml3GgJpYxoCanIaAmpjGgJqZhoCcGwaAm92GgJqaRoCaWEaAnB5GgJvcxoCcHgoAg) read 142736 rows and 28 columns in 0.33875093500046205 seconds\n"
     ]
    }
   ],
   "source": [
    "train0 = read_stream(read_session.streams[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "ff9b1d5e-5ff2-41b2-8836-8288e61b1aa1",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The stream (projects/statmike-mlops-349915/locations/us/sessions/CAISDFlUNnNySnJkeERUehoCamQaAmpxGgJpchoCb2oaAmpzGgJuYRoCb3caAnB6GgJpdxoCaWMaAmpyGgJqYxoCamYaAnBsGgJvdhoCamkaAmlhGgJweRoCb3MaAnB4/streams/CAEaAmpkGgJqcRoCaXIaAm9qGgJqcxoCbmEaAm93GgJwehoCaXcaAmljGgJqchoCamMaAmpmGgJwbBoCb3YaAmppGgJpYRoCcHkaAm9zGgJweCgC) read 142071 rows and 28 columns in 0.1601793429999816 seconds\n"
     ]
    }
   ],
   "source": [
    "train1 = read_stream(read_session.streams[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6291d760-183b-4cda-989f-8969a92dbcad",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "284807"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train0.shape[0] + train1.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "7f46a759-d612-4846-81d7-40b4877b0145",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "train = pd.concat([train0, train1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a3b1ab7c-d1e1-4189-b51d-8fd7d25525f7",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(284807, 28)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bd87756-9228-4979-a8c5-9a9cb4368abb",
   "metadata": {},
   "source": [
    "### Read the table: Async Streams"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "0af2a684-2945-4d28-9acb-c6d200cdfc81",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The stream (projects/statmike-mlops-349915/locations/us/sessions/CAISDFlUNnNySnJkeERUehoCamQaAmpxGgJpchoCb2oaAmpzGgJuYRoCb3caAnB6GgJpdxoCaWMaAmpyGgJqYxoCamYaAnBsGgJvdhoCamkaAmlhGgJweRoCb3MaAnB4/streams/GgJqZBoCanEaAmlyGgJvahoCanMaAm5hGgJvdxoCcHoaAml3GgJpYxoCanIaAmpjGgJqZhoCcGwaAm92GgJqaRoCaWEaAnB5GgJvcxoCcHgoAg) read 142736 rows and 28 columns in 0.34685096599969256 seconds\n",
      "The stream (projects/statmike-mlops-349915/locations/us/sessions/CAISDFlUNnNySnJkeERUehoCamQaAmpxGgJpchoCb2oaAmpzGgJuYRoCb3caAnB6GgJpdxoCaWMaAmpyGgJqYxoCamYaAnBsGgJvdhoCamkaAmlhGgJweRoCb3MaAnB4/streams/CAEaAmpkGgJqcRoCaXIaAm9qGgJqcxoCbmEaAm93GgJwehoCaXcaAmljGgJqchoCamMaAmpmGgJwbBoCb3YaAmppGgJpYRoCcHkaAm9zGgJweCgC) read 142071 rows and 28 columns in 0.3689056610000989 seconds\n",
      "The total elapsed time was: 0.5045286029999261\n"
     ]
    }
   ],
   "source": [
    "start = timeit.default_timer()\n",
    "\n",
    "train = []\n",
    "with concurrent.futures.ThreadPoolExecutor(max_workers=2) as executor:\n",
    "    futures = {\n",
    "        executor.submit(read_stream, stream): stream for stream in read_session.streams\n",
    "    }\n",
    "    for future in concurrent.futures.as_completed(futures):\n",
    "        stream = futures[future]\n",
    "        train.append(future.result())\n",
    "        \n",
    "print(f'The total elapsed time was: {timeit.default_timer() - start}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "54795599-8441-4bbb-9ba0-f8bfc9c401dc",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "5b6eac02-0c9c-4e21-8297-5660eb3e704e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((142736, 28), (142071, 28))"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train[0].shape, train[1].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "207a4cb1-de8a-4ddc-85b3-9653ae2f8a44",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(284807, 28)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = pd.concat(train)\n",
    "train.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "269b32f6-1028-43e7-827f-c948dff348d3",
   "metadata": {},
   "source": [
    "### Full Example With Bigger Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "434b90ad-b198-4cda-8eda-b12ea8633616",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "read_session = bqstorage.create_read_session(\n",
    "    request = dict(\n",
    "        parent = f'projects/{PROJECT_ID}',\n",
    "        read_session = dict(\n",
    "            table = f\"projects/{BQ_PROJECT}/datasets/google_trends/tables/top_terms\",\n",
    "            data_format = bigquery_storage.types.DataFormat.ARROW,\n",
    "            read_options = dict(\n",
    "                #row_restriction = \"rank > 10\",\n",
    "                selected_fields = ['dma_id', 'term', 'week', 'score']\n",
    "            )\n",
    "        ),\n",
    "        max_stream_count = 2\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "c3fe0e5f-b930-4041-8578-227ac13b239d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(read_session.streams)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "094e557f-3ddb-47cb-a8ca-79143b4a28a3",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The stream (projects/statmike-mlops-349915/locations/us/sessions/CAISDGxReG1zUTdnRkpmTxoCamQaAmpxGgJpchoCb2oaAmpzGgJuYRoCb3caAnB6GgJpdxoCaWMaAmpyGgJqYxoCamYaAnBsGgJvdhoCamkaAmlhGgJweRoCb3MaAnB4/streams/GgJqZBoCanEaAmlyGgJvahoCanMaAm5hGgJvdxoCcHoaAml3GgJpYxoCanIaAmpjGgJqZhoCcGwaAm92GgJqaRoCaWEaAnB5GgJvcxoCcHgoAg) read 21186824 rows and 4 columns in 5.984298192000097 seconds\n",
      "The stream (projects/statmike-mlops-349915/locations/us/sessions/CAISDGxReG1zUTdnRkpmTxoCamQaAmpxGgJpchoCb2oaAmpzGgJuYRoCb3caAnB6GgJpdxoCaWMaAmpyGgJqYxoCamYaAnBsGgJvdhoCamkaAmlhGgJweRoCb3MaAnB4/streams/CAEaAmpkGgJqcRoCaXIaAm9qGgJqcxoCbmEaAm93GgJwehoCaXcaAmljGgJqchoCamMaAmpmGgJwbBoCb3YaAmppGgJpYRoCcHkaAm9zGgJweCgC) read 22535176 rows and 4 columns in 7.12685243299984 seconds\n",
      "The total elapsed time was: 7.403345630000331\n"
     ]
    }
   ],
   "source": [
    "start = timeit.default_timer()\n",
    "\n",
    "train = []\n",
    "with concurrent.futures.ThreadPoolExecutor(max_workers=2) as executor:\n",
    "    futures = {\n",
    "        executor.submit(read_stream, stream): stream for stream in read_session.streams\n",
    "    }\n",
    "    for future in concurrent.futures.as_completed(futures):\n",
    "        stream = futures[future]\n",
    "        train.append(future.result())\n",
    "        \n",
    "print(f'The total elapsed time was: {timeit.default_timer() - start}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "09f49c74-87f5-429f-8b11-c188b4585f24",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "ee48c6f9-d106-4217-a9ae-5152ec367aee",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((21186824, 4), (22535176, 4))"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train[0].shape, train[1].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "817269ac-d2e5-4bd9-a672-26634b278d7a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(43722000, 4)"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = pd.concat(train)\n",
    "train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "160a5e0a-7846-47cb-8075-b95d5650dc04",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "conda-root-py",
   "name": "workbench-notebooks.m113",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/workbench-notebooks:m113"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel) (Local)",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}