UDP_Connect_using_Credentials_File.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "UDP: Connect using Credentials File",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/lsloan/udp-notebooks/blob/main/UDP_Connect_using_Credentials_File.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R78dqGH7U2lE"
      },
      "source": [
        "# UDP: Connect Using a JSON Credentials File"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_2-7wloFPMAg"
      },
      "source": [
        "## Introduction"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gUCBdx_1PRGe"
      },
      "source": [
        "Usually, Jupyter Notebooks documents in Google Colaboratory can access Google BigQuery databases with little effort using the \"`bigquery`\" Python module.  Using that, you can access any DB for which access has been granted to your individual Google account.\n",
        "\n",
        "However, UDP's DB in BigQuery is different.  Unizin does not grant access to individual Google accounts.  Instead, they issue service account credentials personalized for each user.  These credentials are in the form of JSON files.  They are usually named something like \"<code>**_YOUR_ORGNAME_HERE_**-**_YOUR_USERNAME_HERE_**-**_PROD_TEST_HERE_**.json</code>\"\n",
        "\n",
        "To use this notebook for querying Caliper events from UDP's BigQuery DB, you will need to upload your credentials JSON file into the \"Files\" section in the left pane of the Colab UI with the name \"<code>**credentials.json**</code>\".\n",
        "\n",
        "ℹ️ Note: Items in the \"Files\" section of this notebook may be lost each time the runtime interpreter is restarted.  If you need to restart this notebook, you will also need to upload your \"<code>**credentials.json**</code>\" file again before you can connect to the DB and run queries."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RQEGGQyu_P0e"
      },
      "source": [
        "## Initialize Environment"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wKgUIp4GHIqW"
      },
      "source": [
        "from google.cloud import bigquery\n",
        "from google.oauth2 import service_account\n",
        "import pandas as pd\n",
        "\n",
        "# Enable Google Colab option for Pandas dataframes pretty print\n",
        "# (Use \"reload_ext\" instead of \"load_ext\" to avoid warning about reloading.)\n",
        "%reload_ext google.colab.data_table\n",
        "\n",
        "# Declare variable types and initial values\n",
        "credentialsFileName: str = 'credentials.json'\n",
        "credentials: service_account.Credentials = None\n",
        "client: bigquery.Client = None"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yn0z51OH-pqP"
      },
      "source": [
        "## Prepare Credentials"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "F_fDUMHk-pqX"
      },
      "source": [
        "# Reads credential file in the \"File\" section of this Google Colab notebook\n",
        "credentials = service_account.Credentials.\\\n",
        "    from_service_account_file(credentialsFileName)\n",
        "\n",
        "credentials"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mzAnejeH-p0u"
      },
      "source": [
        "## Connect to DB"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GuoLu1MU-p0v"
      },
      "source": [
        "client = bigquery.Client(\n",
        "        credentials=credentials,\n",
        "        project=credentials.project_id,\n",
        "    )\n",
        "\n",
        "project = client.project\n",
        "\n",
        "print(f'Connected to BigQuery project \"{project}\".')\n",
        "client"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mycu7VxOdlES"
      },
      "source": [
        "## Test Query"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1ak2pZE-0wHI"
      },
      "source": [
        "Try a query to show the connection to the DB is ready to use.\n",
        "\n",
        "💡 Note: In the SQL query below, the variable \"`project`\" (defined in a code cell above), can be used to refer to the project of your DB."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5icA3y1odlEW"
      },
      "source": [
        "### Most Recent Event by Store Time"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0f-gvz1MscxG"
      },
      "source": [
        "Each event UDP stores includes the time it was stored in a column called \"`store_time`\".  Each event is *highly likely* to have a unique value in this column because it has nanosecond precision.  By getting the maximum value of that column, we can use it to find the one event that was most recently stored.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "f0YOkijSy-TJ"
      },
      "source": [
        "recentEventStoredQuery: str = f'''\n",
        "    SELECT\n",
        "        id, event_time, store_time, ed_app, `type`, `action`\n",
        "    FROM\n",
        "        `{project}`.event_store.events\n",
        "    WHERE\n",
        "        store_time = (\n",
        "            SELECT max(store_time) FROM `{project}`.event_store.events\n",
        "        )\n",
        "    ORDER BY\n",
        "        store_time\n",
        "'''\n",
        "\n",
        "dfRecentEventStored: pd.DataFrame = \\\n",
        "    client.query(recentEventStoredQuery).to_dataframe()\n",
        "\n",
        "dfRecentEventStored"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wYR8BxmX_vPQ"
      },
      "source": [
        "# Next: Timestamp Differences"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "M_p0noum_9K2"
      },
      "source": [
        "In the next notebook, I'll expand on the query by latest \"`store_time`\" and compare that with a query by latest \"`event_time`\"."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8QT57YOdgn1t",
        "cellView": "form",
        "outputId": "ca774bab-f42d-4d44-f506-420a735ae167",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 41
        }
      },
      "source": [
        "#@title See the UDP Notebook Collection\n",
        "%%html\n",
        "<svg xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" width=\"144\" height=\"20\" ><style>a:hover #llink{fill:url(#b);stroke:#ccc}a:hover #rlink{fill:#4183c4}</style><linearGradient id=\"a\" x2=\"0\" y2=\"100%\"><stop offset=\"0\" stop-color=\"#fcfcfc\" stop-opacity=\"0\"/><stop offset=\"1\" stop-opacity=\".1\"/></linearGradient><linearGradient id=\"b\" x2=\"0\" y2=\"100%\"><stop offset=\"0\" stop-color=\"#ccc\" stop-opacity=\".1\"/><stop offset=\"1\" stop-opacity=\".1\"/></linearGradient><g stroke=\"#d5d5d5\"><rect stroke=\"none\" fill=\"#fcfcfc\" x=\"0.5\" y=\"0.5\" width=\"142\" height=\"19\" rx=\"2\"/></g><image x=\"5\" y=\"3\" width=\"14\" height=\"14\" xlink:href=\"data:image/svg+xml;base64,PHN2ZyBmaWxsPSIjMTgxNzE3IiByb2xlPSJpbWciIHZpZXdCb3g9IjAgMCAyNCAyNCIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj48dGl0bGU+R2l0SHViIGljb248L3RpdGxlPjxwYXRoIGQ9Ik0xMiAuMjk3Yy02LjYzIDAtMTIgNS4zNzMtMTIgMTIgMCA1LjMwMyAzLjQzOCA5LjggOC4yMDUgMTEuMzg1LjYuMTEzLjgyLS4yNTguODItLjU3NyAwLS4yODUtLjAxLTEuMDQtLjAxNS0yLjA0LTMuMzM4LjcyNC00LjA0Mi0xLjYxLTQuMDQyLTEuNjFDNC40MjIgMTguMDcgMy42MzMgMTcuNyAzLjYzMyAxNy43Yy0xLjA4Ny0uNzQ0LjA4NC0uNzI5LjA4NC0uNzI5IDEuMjA1LjA4NCAxLjgzOCAxLjIzNiAxLjgzOCAxLjIzNiAxLjA3IDEuODM1IDIuODA5IDEuMzA1IDMuNDk1Ljk5OC4xMDgtLjc3Ni40MTctMS4zMDUuNzYtMS42MDUtMi42NjUtLjMtNS40NjYtMS4zMzItNS40NjYtNS45MyAwLTEuMzEuNDY1LTIuMzggMS4yMzUtMy4yMi0uMTM1LS4zMDMtLjU0LTEuNTIzLjEwNS0zLjE3NiAwIDAgMS4wMDUtLjMyMiAzLjMgMS4yMy45Ni0uMjY3IDEuOTgtLjM5OSAzLS40MDUgMS4wMi4wMDYgMi4wNC4xMzggMyAuNDA1IDIuMjgtMS41NTIgMy4yODUtMS4yMyAzLjI4NS0xLjIzLjY0NSAxLjY1My4yNCAyLjg3My4xMiAzLjE3Ni43NjUuODQgMS4yMyAxLjkxIDEuMjMgMy4yMiAwIDQuNjEtMi44MDUgNS42MjUtNS40NzUgNS45Mi40Mi4zNi44MSAxLjA5Ni44MSAyLjIyIDAgMS42MDYtLjAxNSAyLjg5Ni0uMDE1IDMuMjg2IDAgLjMxNS4yMS42OS44MjUuNTdDMjAuNTY1IDIyLjA5MiAyNCAxNy41OTIgMjQgMTIuMjk3YzAtNi42MjctNS4zNzMtMTItMTItMTIiLz48L3N2Zz4=\"/><g aria-hidden=\"false\" fill=\"#333\" text-anchor=\"middle\" font-family=\"Helvetica Neue,Helvetica,Arial,sans-serif\" text-rendering=\"geometricPrecision\" font-weight=\"700\" font-size=\"110px\" line-height=\"14px\"><a target=\"_blank\" xlink:href=\"https://github.com/lsloan/udp-notebooks\"><text aria-hidden=\"true\" x=\"795\" y=\"150\" fill=\"#fff\" transform=\"scale(.1)\" textLength=\"1150\">​lsloan/udp-notebooks</text><text x=\"795\" y=\"140\" transform=\"scale(.1)\" textLength=\"1150\">lsloan/udp-notebooks</text><rect id=\"llink\" stroke=\"#d5d5d5\" fill=\"url(#a)\" x=\".5\" y=\".5\" width=\"142\" height=\"19\" rx=\"2\" /></a></g></svg>\n"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<svg xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\" width=\"144\" height=\"20\" ><style>a:hover #llink{fill:url(#b);stroke:#ccc}a:hover #rlink{fill:#4183c4}</style><linearGradient id=\"a\" x2=\"0\" y2=\"100%\"><stop offset=\"0\" stop-color=\"#fcfcfc\" stop-opacity=\"0\"/><stop offset=\"1\" stop-opacity=\".1\"/></linearGradient><linearGradient id=\"b\" x2=\"0\" y2=\"100%\"><stop offset=\"0\" stop-color=\"#ccc\" stop-opacity=\".1\"/><stop offset=\"1\" stop-opacity=\".1\"/></linearGradient><g stroke=\"#d5d5d5\"><rect stroke=\"none\" fill=\"#fcfcfc\" x=\"0.5\" y=\"0.5\" width=\"142\" height=\"19\" rx=\"2\"/></g><image x=\"5\" y=\"3\" width=\"14\" height=\"14\" xlink:href=\"data:image/svg+xml;base64,PHN2ZyBmaWxsPSIjMTgxNzE3IiByb2xlPSJpbWciIHZpZXdCb3g9IjAgMCAyNCAyNCIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIj48dGl0bGU+R2l0SHViIGljb248L3RpdGxlPjxwYXRoIGQ9Ik0xMiAuMjk3Yy02LjYzIDAtMTIgNS4zNzMtMTIgMTIgMCA1LjMwMyAzLjQzOCA5LjggOC4yMDUgMTEuMzg1LjYuMTEzLjgyLS4yNTguODItLjU3NyAwLS4yODUtLjAxLTEuMDQtLjAxNS0yLjA0LTMuMzM4LjcyNC00LjA0Mi0xLjYxLTQuMDQyLTEuNjFDNC40MjIgMTguMDcgMy42MzMgMTcuNyAzLjYzMyAxNy43Yy0xLjA4Ny0uNzQ0LjA4NC0uNzI5LjA4NC0uNzI5IDEuMjA1LjA4NCAxLjgzOCAxLjIzNiAxLjgzOCAxLjIzNiAxLjA3IDEuODM1IDIuODA5IDEuMzA1IDMuNDk1Ljk5OC4xMDgtLjc3Ni40MTctMS4zMDUuNzYtMS42MDUtMi42NjUtLjMtNS40NjYtMS4zMzItNS40NjYtNS45MyAwLTEuMzEuNDY1LTIuMzggMS4yMzUtMy4yMi0uMTM1LS4zMDMtLjU0LTEuNTIzLjEwNS0zLjE3NiAwIDAgMS4wMDUtLjMyMiAzLjMgMS4yMy45Ni0uMjY3IDEuOTgtLjM5OSAzLS40MDUgMS4wMi4wMDYgMi4wNC4xMzggMyAuNDA1IDIuMjgtMS41NTIgMy4yODUtMS4yMyAzLjI4NS0xLjIzLjY0NSAxLjY1My4yNCAyLjg3My4xMiAzLjE3Ni43NjUuODQgMS4yMyAxLjkxIDEuMjMgMy4yMiAwIDQuNjEtMi44MDUgNS42MjUtNS40NzUgNS45Mi40Mi4zNi44MSAxLjA5Ni44MSAyLjIyIDAgMS42MDYtLjAxNSAyLjg5Ni0uMDE1IDMuMjg2IDAgLjMxNS4yMS42OS44MjUuNTdDMjAuNTY1IDIyLjA5MiAyNCAxNy41OTIgMjQgMTIuMjk3YzAtNi42MjctNS4zNzMtMTItMTItMTIiLz48L3N2Zz4=\"/><g aria-hidden=\"false\" fill=\"#333\" text-anchor=\"middle\" font-family=\"Helvetica Neue,Helvetica,Arial,sans-serif\" text-rendering=\"geometricPrecision\" font-weight=\"700\" font-size=\"110px\" line-height=\"14px\"><a target=\"_blank\" xlink:href=\"https://github.com/lsloan/udp-notebooks\"><text aria-hidden=\"true\" x=\"795\" y=\"150\" fill=\"#fff\" transform=\"scale(.1)\" textLength=\"1150\">​lsloan/udp-notebooks</text><text x=\"795\" y=\"140\" transform=\"scale(.1)\" textLength=\"1150\">lsloan/udp-notebooks</text><rect id=\"llink\" stroke=\"#d5d5d5\" fill=\"url(#a)\" x=\".5\" y=\".5\" width=\"142\" height=\"19\" rx=\"2\" /></a></g></svg>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    }
  ]
}