extract_from_dwh.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oYMQr4Nl-DYg"
      },
      "source": [
        "<div align=\"center\">\n",
        "<h1><img width=\"30\" src=\"https://madewithml.com/static/images/rounded_logo.png\">&nbsp;<a href=\"https://madewithml.com/\">Made With ML</a></h1>\n",
        "Applied ML · MLOps · Production\n",
        "<br>\n",
        "Join 30K+ developers in learning how to responsibly <a href=\"https://madewithml.com/about/\">deliver value</a> with ML.\n",
        "    <br>\n",
        "</div>\n",
        "\n",
        "<br>\n",
        "\n",
        "<div align=\"center\">\n",
        "    <a target=\"_blank\" href=\"https://madewithml.com\"><img src=\"https://img.shields.io/badge/Subscribe-40K-brightgreen\"></a>&nbsp;\n",
        "    <a target=\"_blank\" href=\"https://github.com/GokuMohandas/MadeWithML\"><img src=\"https://img.shields.io/github/stars/GokuMohandas/MadeWithML.svg?style=social&label=Star\"></a>&nbsp;\n",
        "    <a target=\"_blank\" href=\"https://www.linkedin.com/in/goku\"><img src=\"https://img.shields.io/badge/style--5eba00.svg?label=LinkedIn&logo=linkedin&style=social\"></a>&nbsp;\n",
        "    <a target=\"_blank\" href=\"https://twitter.com/GokuMohandas\"><img src=\"https://img.shields.io/twitter/follow/GokuMohandas.svg?label=Follow&style=social\"></a>\n",
        "    <br>\n",
        "    🔥&nbsp; Among the <a href=\"https://github.com/GokuMohandas/MadeWithML\" target=\"_blank\">top MLOps</a> repositories on GitHub\n",
        "</div>\n",
        "\n",
        "<br>\n",
        "<hr>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4F-xqh9hcaqD"
      },
      "source": [
        "\n",
        "# Data stack\n",
        "\n",
        "This notebook complements the [data stack lesson](https://madewithml.com/courses/mlops/data-stack/) where we learn how to contrust a modern data stack for analytics and machine learning applications. All the concepts mentioned here are covered in much more detail and tied to software engineering best practices for building ML systems. So be sure to check out the [lesson](https://madewithml.com/courses/mlops/data-stack/) if you haven't already."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "<div align=\"left\">\n",
        "<a target=\"_blank\" href=\"https://madewithml.com/courses/mlops/testing/\"><img src=\"https://img.shields.io/badge/📖 Read-lesson-9cf\"></a>&nbsp;\n",
        "<a href=\"https://github.com/GokuMohandas/data-stack/blob/main/data-stack.ipynb\" role=\"button\"><img src=\"https://img.shields.io/static/v1?label=&amp;message=View%20On%20GitHub&amp;color=586069&amp;logo=github&amp;labelColor=2f363d\"></a>&nbsp;\n",
        "<a href=\"https://colab.research.google.com/github/GokuMohandas/data-stack/blob/main/data-stack.ipynb\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"></a>\n",
        "</div>"
      ],
      "metadata": {
        "id": "H15IJGqnp323"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Set up"
      ],
      "metadata": {
        "id": "QcODvh86_sw4"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Follow the instructions in our [data-stack repository](https://github.com/GokuMohandas/data-stack) to do the following:\n",
        "\n",
        "1. Create data sources (CSV file) and destinations ([BigQuery](https://cloud.google.com/bigquery) [DWH](https://madewithml.com/courses/mlops/data-stack/#data-warehouse)).\n",
        "2. Establish connections between data sources and destinations via [Airbyte](https://airbyte.com/).\n",
        "3. Create a [dbt](https://www.getdbt.com/) repository to [transform](https://madewithml.com/courses/mlops/data-stack/#transform) our data in our DWH."
      ],
      "metadata": {
        "id": "CADqxtq8dAQP"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Consumers"
      ],
      "metadata": {
        "id": "QGTxcIJ6kpuU"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Once we have our transformed data in our data warehouse, it's ready for downstream [applications](https://madewithml.com/courses/mlops/data-stack/#applications) such as dashboarding, machine learning, etc. In this notebook, we'll establish a connection to our BigQuery DWH and make a call to extract data."
      ],
      "metadata": {
        "id": "vcpoDFW5kvs0"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!pip install google-cloud-bigquery==1.21.0 -q"
      ],
      "metadata": {
        "id": "BdHW-7xRks_P"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from google.cloud import bigquery\n",
        "from google.colab import files\n",
        "from google.oauth2 import service_account"
      ],
      "metadata": {
        "id": "j5_VMaXSpNNJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "In order to access our data warehouse (or any cloud service), we need a service account with the appropriate privileges. We already created a service account key JSON file earlier when we set up the DWH destination so we can just upload that here. Here are the steps to create a service account and retrieve the JSON file for Google BigQuery:\n",
        "\n",
        "1. Go to [service accounts](https://console.cloud.google.com/iam-admin/serviceaccounts) under IAM & Admin in our Google Cloud console.\n",
        "2. Click on our project (ex. `made-with-ml-XXXXXX`).\n",
        "3. Press **+**`Create Service Account` at the top and give it a name and an `Owner` role.\n",
        "4. Once the service account is created, we should see it on our [project](https://console.cloud.google.com/iam-admin/serviceaccounts?project=made-with-ml&supportedpurview=project) page. Click on `Actions` > `Manage keys`.\n",
        "5. Create a new key by pressing `ADD KEY` > `Create new key` > `JSON`. This will download a JSON file to our local system, which we can upload here."
      ],
      "metadata": {
        "id": "71nQ6KtSqCgV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Upload service account key JSON file\n",
        "uploaded = files.upload()"
      ],
      "metadata": {
        "id": "yV7-JHaWrVCY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!ls"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QZaaBCHSvhp1",
        "outputId": "2b24a195-7a50-4468-a27c-56c5de9ad85d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "made-with-ml-359923-9df280204d63.json  sample_data\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Replace these with your own values\n",
        "PROJECT_ID = \"made-with-ml-XXXXX\" # REPLACE\n",
        "SERVICE_ACCOUNT_KEY_JSON = \"made-with-ml-XXXXXX-XXXXXXXXXXXX.json\" # REPLACE"
      ],
      "metadata": {
        "id": "c_UN2DxSpNPg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Establish connection\n",
        "credentials = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_KEY_JSON)\n",
        "client = bigquery.Client(credentials=credentials, project=PROJECT_ID)"
      ],
      "metadata": {
        "id": "7VYrW3KvpNSd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Query data\n",
        "query_job = client.query(\"\"\"\n",
        "   SELECT *\n",
        "   FROM mlops_course.labeled_projects\"\"\")\n",
        "results = query_job.result()\n",
        "results.to_dataframe().head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "av7NyhFZUD3D",
        "outputId": "883444e5-f37f-4988-f581-1cf00a5bb1fd"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       id           created_on  \\\n",
              "0  1994.0  2020-07-29 04:51:30   \n",
              "1  1506.0  2020-06-19 06:26:17   \n",
              "2   807.0  2020-05-11 02:25:51   \n",
              "3  1204.0  2020-06-05 22:56:38   \n",
              "4  1706.0  2020-07-04 11:05:28   \n",
              "\n",
              "                                               title  \\\n",
              "0  Understanding the Effectivity of Ensembles in ...   \n",
              "1      Using GitHub Actions for MLOps & Data Science   \n",
              "2   Introduction to Machine Learning Problem Framing   \n",
              "3     Snaked: Classifying Snake Species using Images   \n",
              "4                                            PokeZoo   \n",
              "\n",
              "                                         description              tag  \n",
              "0  The report explores the ideas presented in Dee...  computer-vision  \n",
              "1  A collection of resources on how to facilitate...            mlops  \n",
              "2  This course helps you frame machine learning (...            mlops  \n",
              "3  Proof of concept that it is possible to identi...  computer-vision  \n",
              "4  A deep learning based web-app developed using ...  computer-vision  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-56794b0f-93ba-45ca-9002-31db6652d865\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>id</th>\n",
              "      <th>created_on</th>\n",
              "      <th>title</th>\n",
              "      <th>description</th>\n",
              "      <th>tag</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1994.0</td>\n",
              "      <td>2020-07-29 04:51:30</td>\n",
              "      <td>Understanding the Effectivity of Ensembles in ...</td>\n",
              "      <td>The report explores the ideas presented in Dee...</td>\n",
              "      <td>computer-vision</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1506.0</td>\n",
              "      <td>2020-06-19 06:26:17</td>\n",
              "      <td>Using GitHub Actions for MLOps &amp; Data Science</td>\n",
              "      <td>A collection of resources on how to facilitate...</td>\n",
              "      <td>mlops</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>807.0</td>\n",
              "      <td>2020-05-11 02:25:51</td>\n",
              "      <td>Introduction to Machine Learning Problem Framing</td>\n",
              "      <td>This course helps you frame machine learning (...</td>\n",
              "      <td>mlops</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1204.0</td>\n",
              "      <td>2020-06-05 22:56:38</td>\n",
              "      <td>Snaked: Classifying Snake Species using Images</td>\n",
              "      <td>Proof of concept that it is possible to identi...</td>\n",
              "      <td>computer-vision</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1706.0</td>\n",
              "      <td>2020-07-04 11:05:28</td>\n",
              "      <td>PokeZoo</td>\n",
              "      <td>A deep learning based web-app developed using ...</td>\n",
              "      <td>computer-vision</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-56794b0f-93ba-45ca-9002-31db6652d865')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-56794b0f-93ba-45ca-9002-31db6652d865 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-56794b0f-93ba-45ca-9002-31db6652d865');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Orchestration"
      ],
      "metadata": {
        "id": "2432ovnFktcR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Be sure to check out our [orchestration lesson](https://madewithml.com/courses/mlops/orchestration/) where we use our data stack to create our DataOps workflows. In that lesson, we programatically execute all the ELT operations using the data stack we established in this lesson."
      ],
      "metadata": {
        "id": "u0IhJFYZku0T"
      }
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "name": "extract_from_dwh.ipynb",
      "provenance": []
    },
    "interpreter": {
      "hash": "83775aec2432b80a3e3a7273058d2ca65ee45d2724db98f63d82dc93e779d1d6"
    },
    "kernelspec": {
      "display_name": "Python 3.7.13 ('venv': venv)",
      "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.13"
    },
    "orig_nbformat": 4
  },
  "nbformat": 4,
  "nbformat_minor": 0
}