architectures/tracking/setup/pixel/prod-tracking-pixel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a6c6a26d",
   "metadata": {},
   "source": [
    "![tracker](https://us-central1-vertex-ai-mlops-369716.cloudfunctions.net/pixel-tracking?path=statmike%2Fvertex-ai-mlops%2Farchitectures%2Ftracking%2Fsetup%2Fpixel&file=prod-tracking-pixel.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/architectures/tracking/setup/pixel/prod-tracking-pixel.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%2Farchitectures%2Ftracking%2Fsetup%2Fpixel%2Fprod-tracking-pixel.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/architectures/tracking/setup/pixel/prod-tracking-pixel.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/architectures/tracking/setup/pixel/prod-tracking-pixel.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": "57e06f39-be48-44fe-aeb4-2d26abecdb6c",
   "metadata": {},
   "source": [
    "# Production: Custom Tracking With Data Privacy\n",
    "\n",
    "The builds on the learning and experimentation in:\n",
    "- [developing-tracking-pixel](../../pixel/developing-tracking-pixel.ipynb)\n",
    "\n",
    "**Note:** This was developed to replace and migrate away from the GA4 method covered in [../ga4/readme.md](../ga4/readme.md)\n",
    "\n",
    "Read more at: [readme](./readme.md)\n",
    "\n",
    "\n",
    "## Production\n",
    "\n",
    "Create BigQuery Table:\n",
    "- `pixel-tracking-data`\n",
    "\n",
    "Create PubSub Topic:\n",
    "- `pixel-tracking-data`\n",
    "\n",
    "Create Two cloud functions:\n",
    "- `pixel-tracking`\n",
    "    - receives request, validates that it is from repository, returns a generic tracking pixel\n",
    "    - send validates request to pubsub topic: `pixel-tracking-data`\n",
    "- `pixel-tracking-data`\n",
    "    - receives data via Pubsub topic `pixel-tracking-data`\n",
    "    - apped records to BigQuery Table `pixel-tracking-data`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11a2c684-6d79-45a4-b217-bed104a0f5cf",
   "metadata": {
    "id": "od_UkDpvRmgD",
    "tags": []
   },
   "source": [
    "---\n",
    "## Colab Setup\n",
    "\n",
    "When running this notebook in [Colab](https://colab.google/) or [Colab Enterprise](https://cloud.google.com/colab/docs/introduction), this section will authenticate to GCP (follow prompts in the popup) and set the current project for the session."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b4f42c55-54a8-4f83-90f3-377bb65943f7",
   "metadata": {
    "executionInfo": {
     "elapsed": 195,
     "status": "ok",
     "timestamp": 1683726184843,
     "user": {
      "displayName": "Mike Henderson",
      "userId": "07691629187611687318"
     },
     "user_tz": 240
    },
    "id": "8UO9FnqyKBlF",
    "tags": []
   },
   "outputs": [],
   "source": [
    "PROJECT_ID = 'statmike-mlops-349915' # replace with project ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "89a2bc07-e38e-4bb3-8624-53f5d81236fc",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 68869,
     "status": "ok",
     "timestamp": 1683726253709,
     "user": {
      "displayName": "Mike Henderson",
      "userId": "07691629187611687318"
     },
     "user_tz": 240
    },
    "id": "N98-KK7LRkjm",
    "outputId": "09ec5008-0def-4e1a-c349-c598ee752f78",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not a Colab Environment\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    from google.colab import auth\n",
    "    auth.authenticate_user(project_id = PROJECT_ID)\n",
    "    print('Colab authorized to GCP')\n",
    "except Exception:\n",
    "    print('Not a Colab Environment')\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e38a30c-0279-4125-bf98-69d801238483",
   "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": "2af65f62-336a-410c-b425-393c51b93bb6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# tuples of (import name, install name, min_version)\n",
    "packages = [\n",
    "    ('google.cloud.functions', 'google-cloud-functions'),\n",
    "    ('google.cloud.storage', 'google-cloud-storage'),\n",
    "    ('google.cloud.pubsub', 'google-cloud-pubsub'),\n",
    "    ('google.cloud.bigquery', 'google-cloud-bigquery'),\n",
    "    ('flask', 'flask'),\n",
    "    ('PIL', 'Pillow'),\n",
    "    ('zipfile', 'zipfile')\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": "1b29df8c-fc40-425e-a979-d66a4cb68cfe",
   "metadata": {},
   "source": [
    "## API Enablement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "af674f9e-03b6-493d-8786-6c58ae281e58",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "!gcloud services enable cloudfunctions.googleapis.com\n",
    "!gcloud services enable run.googleapis.com\n",
    "!gcloud services enable pubsub.googleapis.com\n",
    "!gcloud services enable eventarc.googleapis.com"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c686307e-4c50-491a-a4f3-7dea25112028",
   "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": 5,
   "id": "b3ce480a-51f1-44f6-b131-2e9a446351db",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if install:\n",
    "    import IPython\n",
    "    app = IPython.Application.instance()\n",
    "    app.kernel.do_shutdown(True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bc6a286-2437-4a3f-a74a-e9589cd469b6",
   "metadata": {
    "id": "appt8-yVRtJ1"
   },
   "source": [
    "---\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2a93a48-ae90-45b5-8e42-33ce1f59ee19",
   "metadata": {
    "id": "63mx2EozRxFP"
   },
   "source": [
    "Inputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bfb759d7-72c7-48c5-8fce-6c0181fa3806",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 35
    },
    "executionInfo": {
     "elapsed": 2124,
     "status": "ok",
     "timestamp": 1683726390544,
     "user": {
      "displayName": "Mike Henderson",
      "userId": "07691629187611687318"
     },
     "user_tz": 240
    },
    "id": "xzcoXjM5Rky5",
    "outputId": "b3bdcbc1-70d5-472e-aea2-42c74a42efde",
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'statmike-mlops-349915'"
      ]
     },
     "execution_count": 6,
     "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": 9,
   "id": "c882a60b-8786-46f3-8306-5f228a0d7e06",
   "metadata": {
    "executionInfo": {
     "elapsed": 2,
     "status": "ok",
     "timestamp": 1683726390712,
     "user": {
      "displayName": "Mike Henderson",
      "userId": "07691629187611687318"
     },
     "user_tz": 240
    },
    "id": "IxWrFtqYMfku",
    "tags": []
   },
   "outputs": [],
   "source": [
    "REGION = 'us-central1'\n",
    "\n",
    "SERIES = 'tracking'\n",
    "EXPERIMENT = 'pixel'\n",
    "\n",
    "GCS_BUCKET = PROJECT_ID"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "680ef491-f9db-4b42-aa88-5d178aae77d1",
   "metadata": {
    "id": "LuajVwCiO6Yg"
   },
   "source": [
    "Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "10440d63-8861-4be2-9020-fa31722e3f19",
   "metadata": {
    "executionInfo": {
     "elapsed": 17761,
     "status": "ok",
     "timestamp": 1683726409304,
     "user": {
      "displayName": "Mike Henderson",
      "userId": "07691629187611687318"
     },
     "user_tz": 240
    },
    "id": "LVC7zzSLRk2C",
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import datetime\n",
    "\n",
    "import PIL, PIL.Image\n",
    "import zipfile\n",
    "\n",
    "from google.cloud import functions_v2\n",
    "from google.cloud import pubsub_v1\n",
    "from google.cloud import storage\n",
    "from google.cloud import bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db0240ad-f698-4509-81b9-ea63980e6c47",
   "metadata": {
    "id": "EyAVFG9TO9H-"
   },
   "source": [
    "Clients"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fe0ddc78-492a-4ba4-93c1-b70164a4b2bc",
   "metadata": {
    "executionInfo": {
     "elapsed": 19,
     "status": "ok",
     "timestamp": 1683726409306,
     "user": {
      "displayName": "Mike Henderson",
      "userId": "07691629187611687318"
     },
     "user_tz": 240
    },
    "id": "L0RPE13LOZce",
    "tags": []
   },
   "outputs": [],
   "source": [
    "# cloud functions\n",
    "functions_client = functions_v2.FunctionServiceClient()\n",
    "\n",
    "# pubsub client\n",
    "pubsub_pubclient = pubsub_v1.PublisherClient()\n",
    "\n",
    "# gcs\n",
    "gcs = storage.Client(project = PROJECT_ID)\n",
    "bucket = gcs.bucket(GCS_BUCKET)\n",
    "\n",
    "# bigquery\n",
    "bq = bigquery.Client(project = PROJECT_ID)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c39540d5-e09f-4f37-8cae-743a16b01c26",
   "metadata": {},
   "source": [
    "parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b144b5bf-a393-4cde-9a0b-6d9f19e0c577",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "DIR = f\"cloud-functions\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "74776782-3491-4892-adde-1d72b481d1d5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1026793852137-compute@developer.gserviceaccount.com'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SERVICE_ACCOUNT = !gcloud config list --format='value(core.account)' \n",
    "SERVICE_ACCOUNT = SERVICE_ACCOUNT[0]\n",
    "SERVICE_ACCOUNT"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec493ad4-9080-4991-ba97-ceff2e4a9554",
   "metadata": {},
   "source": [
    "environment:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7e1c90b9-d318-4e18-aa76-859a4c93d79d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if not os.path.exists(DIR):\n",
    "    os.makedirs(DIR)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b26bc29-3381-46cb-b788-b410f4cb2514",
   "metadata": {},
   "source": [
    "---\n",
    "## Create BigQuery Table: `pixel-tracking-data`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19f49030-4d30-43c2-8788-10377ea56f3b",
   "metadata": {},
   "source": [
    "### Create A BigQuery Dataset\n",
    "\n",
    "Create a new [BigQuery Dataset](https://cloud.google.com/bigquery/docs/datasets) as a working location for this workflow:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6495a054-94e4-4ede-a58b-b3511a09b713",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f503b6fe860>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job = bq.query(f'''\n",
    "    CREATE SCHEMA IF NOT EXISTS `{PROJECT_ID}.pixel_tracking`\n",
    "        OPTIONS(\n",
    "            location = 'US'\n",
    "        )''')\n",
    "job.result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "726f2dca-2d26-4d53-a182-6ddebb2d4e40",
   "metadata": {},
   "source": [
    "### Create A BigQuery Table\n",
    "\n",
    "Simple Schema\n",
    "\n",
    "Partitioned By Timestamp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "4272787d-a833-412b-a0de-5a190a47e53d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f501a4a67a0>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job = bq.query(f'''\n",
    "CREATE OR REPLACE TABLE `{PROJECT_ID}.pixel_tracking.pixel-tracking-data` (event_timestamp TIMESTAMP, file_path STRING, file_name STRING, client STRING, source STRING)\n",
    "PARTITION BY TIMESTAMP_TRUNC(event_timestamp, DAY)\n",
    "''')\n",
    "job.result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4cf8f52-026d-4bd4-9f24-8c12e508a1d2",
   "metadata": {},
   "source": [
    "## Create Pubsub Topic: `pixel-tracking-data`\n",
    "\n",
    "The main concepts:\n",
    "- Topic - a feed of messages\n",
    "     - Publish - send a new message to a topic\n",
    "     - Subscription - receive messages that arrive on topic\n",
    "          - Push - the subscriber has new messages pushed to it\n",
    "          - Pull - the subscriber request new messages by pulling them\n",
    "          \n",
    "In this example, a topic will be set up for receiving new event entries for the tracking pixel.  Publishing a new message to this topic will trigger a data load to BigQuery by the Cloud Function (setup below).  The Cloud Funtion will have a push subscription to the topic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c6c1aa71-d0ae-4ea5-a8a1-f5f5a8337c99",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'pixel-tracking-data'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PUBSUB_TOPIC = 'pixel-tracking-data'\n",
    "PUBSUB_TOPIC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d1c3fda0-1d5d-481c-9b9a-e3fef977f622",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "projects/statmike-mlops-349915/topics/pixel-tracking-data\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    topic = pubsub_pubclient.get_topic(\n",
    "        topic = pubsub_pubclient.topic_path(PROJECT_ID, PUBSUB_TOPIC)\n",
    "    )\n",
    "    print(topic.name)\n",
    "except Exception:\n",
    "    topic = pubsub_pubclient.create_topic(\n",
    "        name = pubsub_pubclient.topic_path(PROJECT_ID, PUBSUB_TOPIC)\n",
    "    )\n",
    "    print(topic.name)   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81e320e5-956d-429e-912c-4e12bb786997",
   "metadata": {},
   "source": [
    "## Create Cloud Function: `pixel-tracking-data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "24809d03-0e1c-488f-8c41-75cab2c6dde4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "function_name = 'pixel-tracking-data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ce722293-d228-4a98-ace3-d7f8629fa0cc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if not os.path.exists(DIR + f'/{function_name}'):\n",
    "    os.makedirs(DIR + f'/{function_name}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ba0ec0de-4e5d-4aac-aabf-bc3fa07dbbef",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting cloud-functions/pixel-tracking-data/main.py\n"
     ]
    }
   ],
   "source": [
    "%%writefile {DIR}/{function_name}/main.py\n",
    "import base64\n",
    "import json\n",
    "from google.cloud import bigquery\n",
    "\n",
    "PROJECT_ID = 'statmike-mlops-349915'\n",
    "bq = bigquery.Client(project = PROJECT_ID)\n",
    "\n",
    "# Triggered from a message on pubsub topic, which is sent by cloud function that collect events\n",
    "def pixel_tracking_data(event, context):\n",
    "    \n",
    "    # decode the data input, convert to python dictionary\n",
    "    function_input = json.loads(\n",
    "        base64.b64decode(event['data']).decode('utf-8')\n",
    "    )\n",
    "    \n",
    "    load_job = bq.load_table_from_json(\n",
    "        json_rows = [function_input],\n",
    "        destination = bigquery.TableReference(\n",
    "            dataset_ref = bigquery.DatasetReference(PROJECT_ID, 'pixel_tracking'),\n",
    "            table_id = f'pixel-tracking-data'\n",
    "        ),\n",
    "        job_config = bigquery.LoadJobConfig(\n",
    "            source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,\n",
    "            write_disposition = bigquery.WriteDisposition.WRITE_APPEND\n",
    "        )\n",
    "    )\n",
    "    load_job.result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "ea61700d-4f5c-44fa-91e5-244e7c43c782",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting cloud-functions/pixel-tracking-data/requirements.txt\n"
     ]
    }
   ],
   "source": [
    "%%writefile {DIR}/{function_name}/requirements.txt\n",
    "google-cloud-bigquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "7c7e278a-512f-4125-ba9e-7b95b11c6f8e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "with zipfile.ZipFile(f'{DIR}/{function_name}/{function_name}.zip', mode = 'w') as archive:\n",
    "    archive.write(f'{DIR}/{function_name}/main.py', 'main.py')\n",
    "    archive.write(f'{DIR}/{function_name}/requirements.txt', 'requirements.txt')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "b0f39573-5893-400a-97c4-8719b3eff360",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File Name                                             Modified             Size\n",
      "main.py                                        2024-03-29 15:15:38          935\n",
      "requirements.txt                               2024-03-29 15:15:40           22\n"
     ]
    }
   ],
   "source": [
    "with zipfile.ZipFile(f'{DIR}/{function_name}/{function_name}.zip', mode = 'r') as zip:\n",
    "    zip.printdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "3a45265b-cda2-4b90-9c07-a9fe3e6a81fc",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "blob = bucket.blob(f'architectures/{SERIES}/{EXPERIMENT}/{function_name}.zip')\n",
    "blob.upload_from_filename(f'{DIR}/{function_name}/{function_name}.zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "2399e3fd-808a-4c37-9359-cc3b119811e8",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name: \"projects/statmike-mlops-349915/locations/us-central1/functions/pixel-tracking-data\"\n",
       "build_config {\n",
       "  build: \"projects/1026793852137/locations/us-central1/builds/cc1f5a20-5118-4c7e-bb54-c232f8ef99f8\"\n",
       "  runtime: \"python312\"\n",
       "  entry_point: \"pixel_tracking_data\"\n",
       "  source {\n",
       "    storage_source {\n",
       "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
       "      object_: \"pixel-tracking-data/function-source.zip\"\n",
       "      generation: 1711725415878589\n",
       "    }\n",
       "  }\n",
       "  docker_repository: \"projects/statmike-mlops-349915/locations/us-central1/repositories/gcf-artifacts\"\n",
       "  source_provenance {\n",
       "    resolved_storage_source {\n",
       "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
       "      object_: \"pixel-tracking-data/function-source.zip\"\n",
       "      generation: 1711725415878589\n",
       "    }\n",
       "  }\n",
       "  docker_registry: ARTIFACT_REGISTRY\n",
       "}\n",
       "service_config {\n",
       "  service: \"projects/statmike-mlops-349915/locations/us-central1/services/pixel-tracking-data\"\n",
       "  timeout_seconds: 60\n",
       "  max_instance_count: 50\n",
       "  ingress_settings: ALLOW_ALL\n",
       "  uri: \"https://pixel-tracking-data-urlxi72dpa-uc.a.run.app\"\n",
       "  service_account_email: \"1026793852137-compute@developer.gserviceaccount.com\"\n",
       "  available_memory: \"128Mi\"\n",
       "  all_traffic_on_latest_revision: true\n",
       "  revision: \"pixel-tracking-data-00002-cuw\"\n",
       "  max_instance_request_concurrency: 1\n",
       "  available_cpu: \"0.0833\"\n",
       "}\n",
       "event_trigger {\n",
       "  trigger: \"projects/statmike-mlops-349915/locations/us-central1/triggers/pixel-tracking-data-132933\"\n",
       "  trigger_region: \"us-central1\"\n",
       "  event_type: \"google.cloud.pubsub.topic.v1.messagePublished\"\n",
       "  pubsub_topic: \"projects/statmike-mlops-349915/topics/pixel-tracking-data\"\n",
       "  service_account_email: \"1026793852137-compute@developer.gserviceaccount.com\"\n",
       "  retry_policy: RETRY_POLICY_DO_NOT_RETRY\n",
       "}\n",
       "state: ACTIVE\n",
       "update_time {\n",
       "  seconds: 1711725465\n",
       "  nanos: 431716026\n",
       "}\n",
       "environment: GEN_2\n",
       "url: \"https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking-data\""
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    function = functions_client.get_function(\n",
    "        name = f\"projects/{PROJECT_ID}/locations/{REGION}/functions/{function_name.replace('_', '-')}\"\n",
    "    )\n",
    "except Exception:\n",
    "    function = ''\n",
    "    \n",
    "function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "6f4f7857-7a73-4f8a-9400-7bc18a926d56",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "functionDef = functions_v2.Function(\n",
    "    name = f\"projects/{PROJECT_ID}/locations/{REGION}/functions/{function_name.replace('_', '-')}\",\n",
    "    build_config = functions_v2.BuildConfig(\n",
    "        runtime = 'python312',\n",
    "        entry_point = f\"{function_name.replace('-', '_')}\",\n",
    "        source = functions_v2.Source(\n",
    "            storage_source = functions_v2.StorageSource(\n",
    "                bucket = bucket.name,\n",
    "                object_ = blob.name\n",
    "            )\n",
    "        ),\n",
    "    ),\n",
    "    service_config = functions_v2.ServiceConfig(\n",
    "        timeout_seconds = 60,\n",
    "        available_memory = '128Mi',\n",
    "        max_instance_count = 50,\n",
    "        max_instance_request_concurrency = 1\n",
    "    ),\n",
    "    event_trigger = functions_v2.EventTrigger(\n",
    "        event_type = 'google.cloud.pubsub.topic.v1.messagePublished',\n",
    "        pubsub_topic = topic.name\n",
    "    ),\n",
    "    environment = functions_v2.Environment(2) \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "bc9526a7-8902-4d93-9e20-7a5c76d54132",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'projects/statmike-mlops-349915/topics/pixel-tracking-data'"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "topic.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "101b7581-9223-4bf2-a284-79f3aa4e3beb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'pixel-tracking-data'"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "function_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "67aed317-6782-494e-98fc-fe424dcf1c78",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if function:\n",
    "    operation = functions_client.update_function(\n",
    "        function = functionDef\n",
    "    )\n",
    "else:\n",
    "    operation = functions_client.create_function(\n",
    "        parent = f\"projects/{PROJECT_ID}/locations/{REGION}\",\n",
    "        function = functionDef,\n",
    "        function_id = function_name.replace('_', '-')\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "629995bd-60ce-4624-92f2-997e99bbf35c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: \"projects/statmike-mlops-349915/locations/us-central1/functions/pixel-tracking-data\"\n",
      "build_config {\n",
      "  build: \"projects/1026793852137/locations/us-central1/builds/cc1f5a20-5118-4c7e-bb54-c232f8ef99f8\"\n",
      "  runtime: \"python312\"\n",
      "  entry_point: \"pixel_tracking_data\"\n",
      "  source {\n",
      "    storage_source {\n",
      "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
      "      object_: \"pixel-tracking-data/function-source.zip\"\n",
      "      generation: 1711725415878589\n",
      "    }\n",
      "  }\n",
      "  docker_repository: \"projects/statmike-mlops-349915/locations/us-central1/repositories/gcf-artifacts\"\n",
      "  source_provenance {\n",
      "    resolved_storage_source {\n",
      "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
      "      object_: \"pixel-tracking-data/function-source.zip\"\n",
      "      generation: 1711725415878589\n",
      "    }\n",
      "  }\n",
      "  docker_registry: ARTIFACT_REGISTRY\n",
      "}\n",
      "service_config {\n",
      "  service: \"projects/statmike-mlops-349915/locations/us-central1/services/pixel-tracking-data\"\n",
      "  timeout_seconds: 60\n",
      "  max_instance_count: 50\n",
      "  ingress_settings: ALLOW_ALL\n",
      "  uri: \"https://pixel-tracking-data-urlxi72dpa-uc.a.run.app\"\n",
      "  service_account_email: \"1026793852137-compute@developer.gserviceaccount.com\"\n",
      "  available_memory: \"128Mi\"\n",
      "  all_traffic_on_latest_revision: true\n",
      "  revision: \"pixel-tracking-data-00002-cuw\"\n",
      "  max_instance_request_concurrency: 1\n",
      "  available_cpu: \"0.0833\"\n",
      "}\n",
      "event_trigger {\n",
      "  trigger: \"projects/statmike-mlops-349915/locations/us-central1/triggers/pixel-tracking-data-132933\"\n",
      "  trigger_region: \"us-central1\"\n",
      "  event_type: \"google.cloud.pubsub.topic.v1.messagePublished\"\n",
      "  pubsub_topic: \"projects/statmike-mlops-349915/topics/pixel-tracking-data\"\n",
      "  service_account_email: \"1026793852137-compute@developer.gserviceaccount.com\"\n",
      "  retry_policy: RETRY_POLICY_DO_NOT_RETRY\n",
      "}\n",
      "state: ACTIVE\n",
      "update_time {\n",
      "  seconds: 1711725415\n",
      "  nanos: 959174779\n",
      "}\n",
      "environment: GEN_2\n",
      "url: \"https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking-data\"\n",
      "\n"
     ]
    }
   ],
   "source": [
    "response = operation.result()\n",
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "6c76d0f5-945c-40d0-91e2-ef5e29c24560",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'projects/statmike-mlops-349915/locations/us-central1/functions/pixel-tracking-data'"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f84dfa4f-b753-463b-b913-7b69c8a7d819",
   "metadata": {},
   "source": [
    "## Create Cloud Function: `pixel-tracking`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "580bc5bc-4ff3-4e96-a483-bfa68569a48b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "function_name = 'pixel-tracking'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "ea7a2df7-5d55-414c-8bba-58542f9cf2ac",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if not os.path.exists(DIR + f'/{function_name}'):\n",
    "    os.makedirs(DIR + f'/{function_name}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a199331-eb1a-42c3-925c-e0f46cd3e054",
   "metadata": {},
   "source": [
    "### Create A Pixel\n",
    "\n",
    "0,0,0 is black and alpha chanel of 0 also black makes this transparent..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "e10b6cb0-ad48-4697-8e34-439ca448dd4f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "pixel = PIL.Image.new(mode = 'RGBA', size = (1,1), color = (0,0,0,0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "6908e215-9b51-421e-a6ec-9125c8b5ae4a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "pixel.save(f'{DIR}/{function_name}/pixel.png')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62942f2d-c8e0-4953-b216-19eba284727a",
   "metadata": {},
   "source": [
    "### Create Custom Service Account\n",
    "\n",
    "This is going to be a public facing cloud function.  Reduce its permission to the bare minimum which is ability to write to the one pubsub topic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "62af986e-be41-4ece-835f-6b01a2def8cb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created service account [pixel-tracking].\n"
     ]
    }
   ],
   "source": [
    "!gcloud iam service-accounts create pixel-tracking --display-name='pixel-tracking'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "ada1af6d-9dca-476b-a5db-82c45e7ecb61",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DISPLAY NAME    EMAIL                                                         DISABLED\n",
      "pixel-tracking  pixel-tracking@statmike-mlops-349915.iam.gserviceaccount.com  False\n"
     ]
    }
   ],
   "source": [
    "!gcloud iam service-accounts list --filter='display_name=pixel-tracking'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "d9725fdf-fda8-4a8b-a0d7-2fae6eb49cf8",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Updated IAM policy for project [statmike-mlops-349915].\n",
      "bindings:\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-aiplatform-cc.iam.gserviceaccount.com\n",
      "  role: roles/aiplatform.customCodeServiceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-aiplatform-vm.iam.gserviceaccount.com\n",
      "  role: roles/aiplatform.notebookServiceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-aiplatform.iam.gserviceaccount.com\n",
      "  role: roles/aiplatform.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:bqcx-1026793852137-bmph@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  - serviceAccount:bqcx-1026793852137-d2h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  - serviceAccount:bqcx-1026793852137-dyw1@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  - serviceAccount:bqcx-1026793852137-pdxa@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  - serviceAccount:bqcx-1026793852137-te86@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  - serviceAccount:bqcx-1026793852137-tqpc@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  - serviceAccount:bqcx-1026793852137-zfly@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  role: roles/aiplatform.user\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-artifactregistry.iam.gserviceaccount.com\n",
      "  role: roles/artifactregistry.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137-compute@developer.gserviceaccount.com\n",
      "  role: roles/bigquery.admin\n",
      "- members:\n",
      "  - serviceAccount:1026793852137@cloudservices.gserviceaccount.com\n",
      "  role: roles/bigquery.dataOwner\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-bigquerydatatransfer.iam.gserviceaccount.com\n",
      "  role: roles/bigquerydatatransfer.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137@cloudbuild.gserviceaccount.com\n",
      "  role: roles/cloudbuild.builds.builder\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-cloudbuild.iam.gserviceaccount.com\n",
      "  role: roles/cloudbuild.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcf-admin-robot.iam.gserviceaccount.com\n",
      "  role: roles/cloudfunctions.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-cloudscheduler.iam.gserviceaccount.com\n",
      "  role: roles/cloudscheduler.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:bqcx-1026793852137-d2h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  role: roles/cloudtranslate.user\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@compute-system.iam.gserviceaccount.com\n",
      "  role: roles/compute.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@containerregistry.iam.gserviceaccount.com\n",
      "  role: roles/containerregistry.ServiceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@dataflow-service-producer-prod.iam.gserviceaccount.com\n",
      "  role: roles/dataflow.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-dataform.iam.gserviceaccount.com\n",
      "  role: roles/dataform.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@dataproc-accounts.iam.gserviceaccount.com\n",
      "  role: roles/dataproc.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-dialogflow.iam.gserviceaccount.com\n",
      "  role: roles/dialogflow.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-discoveryengine.iam.gserviceaccount.com\n",
      "  role: roles/discoveryengine.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@dlp-api.iam.gserviceaccount.com\n",
      "  role: roles/dlp.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:bqcx-1026793852137-te86@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  role: roles/documentai.apiUser\n",
      "- members:\n",
      "  - serviceAccount:bqcx-1026793852137-te86@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  role: roles/documentai.viewer\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-prod-dai-core.iam.gserviceaccount.com\n",
      "  role: roles/documentaicore.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137@cloudservices.gserviceaccount.com\n",
      "  role: roles/editor\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-eventarc.iam.gserviceaccount.com\n",
      "  role: roles/eventarc.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-networkmanagement.iam.gserviceaccount.com\n",
      "  role: roles/networkmanagement.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-notebooks.iam.gserviceaccount.com\n",
      "  role: roles/notebooks.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137-compute@developer.gserviceaccount.com\n",
      "  - serviceAccount:1026793852137@cloudservices.gserviceaccount.com\n",
      "  - user:admin@statmike.altostrat.com\n",
      "  role: roles/owner\n",
      "- members:\n",
      "  - serviceAccount:pixel-tracking@statmike-mlops-349915.iam.gserviceaccount.com\n",
      "  role: roles/pubsub.publisher\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-pubsub.iam.gserviceaccount.com\n",
      "  role: roles/pubsub.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137-compute@developer.gserviceaccount.com\n",
      "  role: roles/run.admin\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@serverless-robot-prod.iam.gserviceaccount.com\n",
      "  role: roles/run.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137-compute@developer.gserviceaccount.com\n",
      "  role: roles/secretmanager.secretAccessor\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@service-networking.iam.gserviceaccount.com\n",
      "  role: roles/servicenetworking.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:bqcx-1026793852137-d2h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  role: roles/serviceusage.serviceUsageConsumer\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-speech.iam.gserviceaccount.com\n",
      "  role: roles/speech.serviceAgent\n",
      "- members:\n",
      "  - serviceAccount:1026793852137-compute@developer.gserviceaccount.com\n",
      "  role: roles/storage.objectAdmin\n",
      "- members:\n",
      "  - serviceAccount:bqcx-1026793852137-te86@gcp-sa-bigquery-condel.iam.gserviceaccount.com\n",
      "  role: roles/storage.objectViewer\n",
      "- members:\n",
      "  - user:showvik@statmike.altostrat.com\n",
      "  role: roles/viewer\n",
      "- members:\n",
      "  - serviceAccount:service-1026793852137@gcp-sa-workstations.iam.gserviceaccount.com\n",
      "  role: roles/workstations.serviceAgent\n",
      "etag: BwYUz24vTFE=\n",
      "version: 1\n"
     ]
    }
   ],
   "source": [
    "!gcloud projects add-iam-policy-binding $PROJECT_ID \\\n",
    "--member=\"serviceAccount:pixel-tracking@{PROJECT_ID}.iam.gserviceaccount.com\" \\\n",
    "--role=\"roles/pubsub.publisher\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "d7671a2b-a86c-4dc9-88f1-4af04b200448",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ROLE\n",
      "roles/pubsub.publisher\n"
     ]
    }
   ],
   "source": [
    "!gcloud projects get-iam-policy $PROJECT_ID \\\n",
    "--filter=\"bindings.members:pixel-tracking@{PROJECT_ID}.iam.gserviceaccount.com\" \\\n",
    "--format='table(bindings.role)' --flatten=\"bindings[].members\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "7a189fee-f93c-42a6-9e82-548c832b7849",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# delete the service account:\n",
    "#!gcloud iam service-accounts delete 'pixel-tracking@{PROJECT_ID}.iam.gserviceaccount.com' -q"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cbb3118d-9231-42e6-9ce2-d19843b4de68",
   "metadata": {},
   "source": [
    "### Create The Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "23bb2816-8658-40fa-a19e-1c6cd764cc15",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting cloud-functions/pixel-tracking/main.py\n"
     ]
    }
   ],
   "source": [
    "%%writefile {DIR}/{function_name}/main.py\n",
    "import datetime\n",
    "import json\n",
    "import flask\n",
    "from flask import abort\n",
    "from google.cloud import pubsub_v1\n",
    "\n",
    "PROJECT_ID = 'statmike-mlops-349915'\n",
    "pubsub_pubclient = pubsub_v1.PublisherClient() \n",
    "\n",
    "def pixel_tracking(request: flask.Request) -> flask.Response:\n",
    "    \n",
    "    repo_path = request.args.get('path', 'direct')\n",
    "    repo_file = request.args.get('file', 'direct')\n",
    "    application = request.headers.get('User-Agent')\n",
    "    source = 'custom'\n",
    "    \n",
    "    if repo_path.startswith('statmike') and len(repo_path) < 500:\n",
    "        if len(repo_file) > 5 and len(repo_file) < 500:\n",
    "            data = dict(\n",
    "                event_timestamp = datetime.datetime.now(datetime.timezone.utc).strftime(\"%Y-%m-%d %H:%M:%S.%f %Z\"),\n",
    "                file_path = repo_path,\n",
    "                file_name = repo_file,\n",
    "                client = application,\n",
    "                source = source\n",
    "            )\n",
    "            message = json.dumps(data).encode('utf-8')\n",
    "            future = pubsub_pubclient.publish(\n",
    "                f'projects/{PROJECT_ID}/topics/pixel-tracking-data',\n",
    "                message,\n",
    "                trigger = 'manual'\n",
    "            )\n",
    "        else:\n",
    "            return abort(406) # not acceptable\n",
    "    else:\n",
    "        return abort(404) # bad request\n",
    "    \n",
    "    return flask.send_file('pixel.png', max_age=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "20392e6d-6d3a-4620-b363-7ad5493dd33a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Overwriting cloud-functions/pixel-tracking/requirements.txt\n"
     ]
    }
   ],
   "source": [
    "%%writefile {DIR}/{function_name}/requirements.txt\n",
    "google-cloud-pubsub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "efde848b-04c5-40fa-ae2b-67a6f0eef978",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "with zipfile.ZipFile(f'{DIR}/{function_name}/{function_name}.zip', mode = 'w') as archive:\n",
    "    archive.write(f'{DIR}/{function_name}/main.py', 'main.py')\n",
    "    archive.write(f'{DIR}/{function_name}/requirements.txt', 'requirements.txt')\n",
    "    archive.write(f'{DIR}/{function_name}/pixel.png', 'pixel.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "14dea090-ac78-4aab-a23f-96ec81860e93",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File Name                                             Modified             Size\n",
      "main.py                                        2024-03-29 16:50:06         1272\n",
      "requirements.txt                               2024-03-29 16:50:14           20\n",
      "pixel.png                                      2024-03-29 16:49:46           70\n"
     ]
    }
   ],
   "source": [
    "with zipfile.ZipFile(f'{DIR}/{function_name}/{function_name}.zip', mode = 'r') as zip:\n",
    "    zip.printdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "7834911f-23a8-4865-919c-faf0fd88b9f4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "blob = bucket.blob(f'architectures/{SERIES}/{EXPERIMENT}/{function_name}.zip')\n",
    "blob.upload_from_filename(f'{DIR}/{function_name}/{function_name}.zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "adb7c251-f42d-4940-ba95-6380dab9f97b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name: \"projects/statmike-mlops-349915/locations/us-central1/functions/pixel-tracking\"\n",
       "build_config {\n",
       "  build: \"projects/1026793852137/locations/us-central1/builds/1a8a0406-6287-42ce-abbf-2cab4ea8f2ef\"\n",
       "  runtime: \"python312\"\n",
       "  entry_point: \"pixel_tracking\"\n",
       "  source {\n",
       "    storage_source {\n",
       "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
       "      object_: \"pixel-tracking/function-source.zip\"\n",
       "      generation: 1711728461470537\n",
       "    }\n",
       "  }\n",
       "  docker_repository: \"projects/statmike-mlops-349915/locations/us-central1/repositories/gcf-artifacts\"\n",
       "  source_provenance {\n",
       "    resolved_storage_source {\n",
       "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
       "      object_: \"pixel-tracking/function-source.zip\"\n",
       "      generation: 1711728461470537\n",
       "    }\n",
       "  }\n",
       "  docker_registry: ARTIFACT_REGISTRY\n",
       "}\n",
       "service_config {\n",
       "  service: \"projects/statmike-mlops-349915/locations/us-central1/services/pixel-tracking\"\n",
       "  timeout_seconds: 10\n",
       "  max_instance_count: 10\n",
       "  ingress_settings: ALLOW_ALL\n",
       "  uri: \"https://pixel-tracking-urlxi72dpa-uc.a.run.app\"\n",
       "  service_account_email: \"1026793852137-compute@developer.gserviceaccount.com\"\n",
       "  available_memory: \"128Mi\"\n",
       "  all_traffic_on_latest_revision: true\n",
       "  revision: \"pixel-tracking-00001-qub\"\n",
       "  max_instance_request_concurrency: 1\n",
       "  available_cpu: \"0.0833\"\n",
       "}\n",
       "state: ACTIVE\n",
       "update_time {\n",
       "  seconds: 1711728515\n",
       "  nanos: 984821896\n",
       "}\n",
       "environment: GEN_2\n",
       "url: \"https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking\""
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    function = functions_client.get_function(\n",
    "        name = f\"projects/{PROJECT_ID}/locations/{REGION}/functions/{function_name.replace('_', '-')}\"\n",
    "    )\n",
    "except Exception:\n",
    "    function = ''\n",
    "    \n",
    "function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "87c577a0-e962-4b76-8cdf-717206a042f6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "functionDef = functions_v2.Function(\n",
    "    name = f\"projects/{PROJECT_ID}/locations/{REGION}/functions/{function_name.replace('_', '-')}\",\n",
    "    build_config = functions_v2.BuildConfig(\n",
    "        runtime = 'python312',\n",
    "        entry_point = f\"{function_name.replace('-', '_')}\",\n",
    "        source = functions_v2.Source(\n",
    "            storage_source = functions_v2.StorageSource(\n",
    "                bucket = bucket.name,\n",
    "                object_ = blob.name\n",
    "            )\n",
    "        ),\n",
    "    ),\n",
    "    service_config = functions_v2.ServiceConfig(\n",
    "        timeout_seconds = 10,\n",
    "        available_memory = '128Mi',\n",
    "        max_instance_count = 10,\n",
    "        max_instance_request_concurrency = 1,\n",
    "        service_account_email = f'pixel-tracking@{PROJECT_ID}.iam.gserviceaccount.com'\n",
    "    ),\n",
    "    environment = functions_v2.Environment(2) \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "349f265d-52db-48d4-bc4b-aeeb9ed67319",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'projects/statmike-mlops-349915/topics/pixel-tracking-data'"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "topic.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "id": "05697793-f73e-49ca-a3a4-343e4464aa73",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'pixel-tracking'"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "function_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "id": "e97e5def-5b61-43b5-bb35-03bec1ab21d9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "if function:\n",
    "    operation = functions_client.update_function(\n",
    "        function = functionDef\n",
    "    )\n",
    "else:\n",
    "    operation = functions_client.create_function(\n",
    "        parent = f\"projects/{PROJECT_ID}/locations/{REGION}\",\n",
    "        function = functionDef,\n",
    "        function_id = function_name.replace('_', '-')\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "id": "9acafbcb-f668-4598-a243-bdde89760b50",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: \"projects/statmike-mlops-349915/locations/us-central1/functions/pixel-tracking\"\n",
      "build_config {\n",
      "  build: \"projects/1026793852137/locations/us-central1/builds/14497353-bc5f-4cdc-b3cb-947e09670685\"\n",
      "  runtime: \"python312\"\n",
      "  entry_point: \"pixel_tracking\"\n",
      "  source {\n",
      "    storage_source {\n",
      "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
      "      object_: \"pixel-tracking/function-source.zip\"\n",
      "      generation: 1711731227681419\n",
      "    }\n",
      "  }\n",
      "  docker_repository: \"projects/statmike-mlops-349915/locations/us-central1/repositories/gcf-artifacts\"\n",
      "  source_provenance {\n",
      "    resolved_storage_source {\n",
      "      bucket: \"gcf-v2-sources-1026793852137-us-central1\"\n",
      "      object_: \"pixel-tracking/function-source.zip\"\n",
      "      generation: 1711731227681419\n",
      "    }\n",
      "  }\n",
      "  docker_registry: ARTIFACT_REGISTRY\n",
      "}\n",
      "service_config {\n",
      "  service: \"projects/statmike-mlops-349915/locations/us-central1/services/pixel-tracking\"\n",
      "  timeout_seconds: 10\n",
      "  max_instance_count: 10\n",
      "  ingress_settings: ALLOW_ALL\n",
      "  uri: \"https://pixel-tracking-urlxi72dpa-uc.a.run.app\"\n",
      "  service_account_email: \"pixel-tracking@statmike-mlops-349915.iam.gserviceaccount.com\"\n",
      "  available_memory: \"128Mi\"\n",
      "  all_traffic_on_latest_revision: true\n",
      "  revision: \"pixel-tracking-00002-ceb\"\n",
      "  max_instance_request_concurrency: 1\n",
      "  available_cpu: \"0.0833\"\n",
      "}\n",
      "state: ACTIVE\n",
      "update_time {\n",
      "  seconds: 1711731227\n",
      "  nanos: 720308190\n",
      "}\n",
      "environment: GEN_2\n",
      "url: \"https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking\"\n",
      "\n"
     ]
    }
   ],
   "source": [
    "response = operation.result()\n",
    "print(response)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "b2f477a9-b054-4af1-9945-52d6afbc0a01",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'projects/statmike-mlops-349915/locations/us-central1/functions/pixel-tracking'"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "730079f9-a24f-4742-9163-8b61d6bee0e9",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking'"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.url"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6db707a5-d552-423e-bd16-171b6ac23bcb",
   "metadata": {},
   "source": [
    "### Make Public\n",
    "\n",
    "- https://cloud.google.com/functions/docs/securing/managing-access-iam#allowing_unauthenticated_http_function_invocation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "f008d452-faed-49ae-ac08-c8af99f2ad7b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "bindings:\n",
      "- members:\n",
      "  - allUsers\n",
      "  role: roles/run.invoker\n",
      "etag: BwYUztvsC90=\n",
      "version: 1\n"
     ]
    }
   ],
   "source": [
    "!gcloud run services get-iam-policy {function_name.replace('_', '-')} --region=$REGION #--format=json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "dffdd215-f0ac-4f7a-abc6-171a0a0cc7e7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#json.loads(''.join(test))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8bab9e8-68c4-4c83-bc97-e5419ab418e2",
   "metadata": {},
   "source": [
    "if missing `allUsers` with `roles/run.invoker` the add it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "a06590f5-bd16-40dc-8357-7d2dd4032a0a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Updated IAM policy for service [pixel-tracking].\n",
      "bindings:\n",
      "- members:\n",
      "  - allUsers\n",
      "  role: roles/run.invoker\n",
      "etag: BwYUz4AEwpM=\n",
      "version: 1\n"
     ]
    }
   ],
   "source": [
    "!gcloud run services add-iam-policy-binding {function_name.replace('_', '-')} --region=$REGION --member=\"allUsers\" --role=\"roles/run.invoker\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee1ba969-e1a4-43f0-b71c-bcf25d4b61dc",
   "metadata": {},
   "source": [
    "To remove open access use the following:\n",
    "\n",
    "Note: this can take a minute to stop the requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "dd4bcb9d-e1a6-4b7f-be69-31bb688b2811",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "#!gcloud run services remove-iam-policy-binding {function_name.replace('_', '-')} --region=$REGION --member=\"allUsers\" --role=\"roles/run.invoker\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "893518b8-1a60-4727-8df7-b5cf542bb5df",
   "metadata": {},
   "source": [
    "## Test Tracking\n",
    "\n",
    "- directly test cloud function `pixel-tracking-data`\n",
    "- directly test cloud function `pixel-tracking`\n",
    "- remove test data from BigQuery table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d2745a5-11a3-4677-be17-fffc828062fc",
   "metadata": {},
   "source": [
    "### Test `pixel-tracking-data` directly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "35221b31-c223-4099-9cec-a3ca32b2d2c4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = dict(\n",
    "    event_timestamp = datetime.datetime.now(datetime.timezone.utc).strftime(\"%Y-%m-%d %H:%M:%S.%f %Z\"),\n",
    "    file_path = 'statmike/path/to/file',\n",
    "    file_name = f'my notebook.ipynb',\n",
    "    client = 'test',\n",
    "    source = 'test'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "bb3eac52-e28f-4a77-9892-68d1ba977a08",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'10808208139134898'"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "future = pubsub_pubclient.publish(\n",
    "    topic.name,\n",
    "    json.dumps(data).encode('utf-8'),\n",
    "    trigger = 'manual'\n",
    ")\n",
    "future.result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9af02b40-5c3b-4c76-a470-87dae949e08b",
   "metadata": {},
   "source": [
    "Note - it can take a few seconds for the insert to happen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "a6b3915f-79c5-45ae-a4cd-1af54f2b71b5",
   "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>event_timestamp</th>\n",
       "      <th>file_path</th>\n",
       "      <th>file_name</th>\n",
       "      <th>client</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-03-29 15:45:32.867219+00:00</td>\n",
       "      <td>statmike/path/to/file</td>\n",
       "      <td>my notebook.ipynb</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-03-29 15:45:32.867219+00:00</td>\n",
       "      <td>statmike/path/to/file</td>\n",
       "      <td>my notebook.ipynb</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-03-29 16:09:10.197998+00:00</td>\n",
       "      <td>statmike/path/to/file</td>\n",
       "      <td>my notebook.ipynb</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   event_timestamp              file_path          file_name  \\\n",
       "0 2024-03-29 15:45:32.867219+00:00  statmike/path/to/file  my notebook.ipynb   \n",
       "1 2024-03-29 15:45:32.867219+00:00  statmike/path/to/file  my notebook.ipynb   \n",
       "2 2024-03-29 16:09:10.197998+00:00  statmike/path/to/file  my notebook.ipynb   \n",
       "\n",
       "  client source  \n",
       "0   test   test  \n",
       "1   test   test  \n",
       "2   test   test  "
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.query(f'SELECT * FROM `{PROJECT_ID}.pixel_tracking.pixel-tracking-data`').to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11338ef5-e8e3-46eb-b1e3-01e4ec60fd75",
   "metadata": {},
   "source": [
    "## Test System Via HTTPS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "b72d0277-b714-4203-832a-89e7739138e0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking'"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response.url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "1040029b-377e-42e1-bd4f-55a0682b85ae",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['�PNG',\n",
       " '\\x1a',\n",
       " '\\x00\\x00\\x00',\n",
       " 'IHDR\\x00\\x00\\x00\\x01\\x00\\x00\\x00\\x01\\x08\\x06\\x00\\x00\\x00\\x1f\\x15ĉ\\x00\\x00\\x00',\n",
       " 'IDATx�c````\\x00\\x00\\x00\\x05\\x00\\x01��E@\\x00\\x00\\x00\\x00IEND�B`�']"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_response = !curl -s POST '{response.url}?path=statmike&file=newtest.ipynb'\n",
    "test_response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "cab15d43-3007-4231-a63e-407abdb22f17",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "open this link:\n",
      "https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking?path=statmike&file=tester\n"
     ]
    }
   ],
   "source": [
    "print(f\"open this link:\\n{response.url}?path=statmike&file=tester\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "id": "49b313d0-affa-4107-92e9-961a4bd2a582",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "open this link:\n",
      "https://us-central1-statmike-mlops-349915.cloudfunctions.net/pixel-tracking?path=notstatmike&file=tester\n"
     ]
    }
   ],
   "source": [
    "print(f\"open this link:\\n{response.url}?path=notstatmike&file=tester\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3228ef05-13b3-4db1-ad57-9bbb675b1dd6",
   "metadata": {},
   "source": [
    "Note - it can take a few seconds for the record inserts to happend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 193,
   "id": "8082fb71-bb31-448b-b19c-6e9e28ddf6d9",
   "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>event_timestamp</th>\n",
       "      <th>file_path</th>\n",
       "      <th>file_name</th>\n",
       "      <th>client</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-03-29 15:45:32.867219+00:00</td>\n",
       "      <td>statmike/path/to/file</td>\n",
       "      <td>my notebook.ipynb</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-03-29 16:10:17.951834+00:00</td>\n",
       "      <td>statmike</td>\n",
       "      <td>test.ipynb</td>\n",
       "      <td>curl/7.74.0</td>\n",
       "      <td>custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-03-29 16:56:05.100753+00:00</td>\n",
       "      <td>statmike</td>\n",
       "      <td>newtest.ipynb</td>\n",
       "      <td>curl/7.74.0</td>\n",
       "      <td>custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-03-29 16:10:48.995500+00:00</td>\n",
       "      <td>statmike</td>\n",
       "      <td>tester</td>\n",
       "      <td>Mozilla/5.0 (X11; CrOS x86_64 14541.0.0) Apple...</td>\n",
       "      <td>custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-03-29 15:45:32.867219+00:00</td>\n",
       "      <td>statmike/path/to/file</td>\n",
       "      <td>my notebook.ipynb</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2024-03-29 16:55:14.154529+00:00</td>\n",
       "      <td>statmike</td>\n",
       "      <td>test.ipynb</td>\n",
       "      <td>curl/7.74.0</td>\n",
       "      <td>custom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2024-03-29 16:09:10.197998+00:00</td>\n",
       "      <td>statmike/path/to/file</td>\n",
       "      <td>my notebook.ipynb</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   event_timestamp              file_path          file_name  \\\n",
       "0 2024-03-29 15:45:32.867219+00:00  statmike/path/to/file  my notebook.ipynb   \n",
       "1 2024-03-29 16:10:17.951834+00:00               statmike         test.ipynb   \n",
       "2 2024-03-29 16:56:05.100753+00:00               statmike      newtest.ipynb   \n",
       "3 2024-03-29 16:10:48.995500+00:00               statmike             tester   \n",
       "4 2024-03-29 15:45:32.867219+00:00  statmike/path/to/file  my notebook.ipynb   \n",
       "5 2024-03-29 16:55:14.154529+00:00               statmike         test.ipynb   \n",
       "6 2024-03-29 16:09:10.197998+00:00  statmike/path/to/file  my notebook.ipynb   \n",
       "\n",
       "                                              client  source  \n",
       "0                                               test    test  \n",
       "1                                        curl/7.74.0  custom  \n",
       "2                                        curl/7.74.0  custom  \n",
       "3  Mozilla/5.0 (X11; CrOS x86_64 14541.0.0) Apple...  custom  \n",
       "4                                               test    test  \n",
       "5                                        curl/7.74.0  custom  \n",
       "6                                               test    test  "
      ]
     },
     "execution_count": 193,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.query(f'SELECT * FROM `{PROJECT_ID}.pixel_tracking.pixel-tracking-data`').to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "924ef301-5954-4d88-98a3-e1fd5ab64fd6",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "QueryJob<project=statmike-mlops-349915, location=US, id=622b7609-197f-434c-ae14-b8a1eaf22ce5>"
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.query(f\"DELETE FROM `{PROJECT_ID}.pixel_tracking.pixel-tracking-data` WHERE file_path IN ('statmike', 'statmike/path/to/file')\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "id": "df203444-1ec6-4211-963a-a16558324423",
   "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>event_timestamp</th>\n",
       "      <th>file_path</th>\n",
       "      <th>file_name</th>\n",
       "      <th>client</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [event_timestamp, file_path, file_name, client, source]\n",
       "Index: []"
      ]
     },
     "execution_count": 195,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.query(f'SELECT * FROM `{PROJECT_ID}.pixel_tracking.pixel-tracking-data`').to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97c62089-f4f1-4ebb-8b32-678fe88bd825",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "conda-root-py",
   "name": "workbench-notebooks.m115",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/workbench-notebooks:m115"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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
}