{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "ur8xi4C7S06n"
      },
      "outputs": [],
      "source": [
        "# Copyright 2024 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3f179f5f22d8"
      },
      "source": [
        "# Scaled Video Analysis with the YouTube Data API, Gemini, and Batch Prediction"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JAPoU8Sm5E6e"
      },
      "source": [
        "<table align=\"left\">\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://www.gstatic.com/pantheon/images/bigquery/welcome_page/colab-logo.svg\" alt=\"Google Colaboratory logo\"><br> Open in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/colab/import/https:%2F%2Fraw.githubusercontent.com%2FGoogleCloudPlatform%2Fgenerative-ai%2Fmain%2Fgemini%2Fuse-cases%2Fvideo-analysis%2Fvideo_analysis_with_youtube_data_api_and_batch_prediction.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://lh3.googleusercontent.com/JmcxdQi-qOpctIvWKgPtrzZdJJK-J3sWE1RsfjZNwshCFgE_9fULcNpuXYTilIR2hjwN\" alt=\"Google Cloud Colab Enterprise logo\"><br> Open in Colab Enterprise\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/GoogleCloudPlatform/generative-ai/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\">\n",
        "      <img src=\"https://www.gstatic.com/images/branding/gcpiconscolors/vertexai/v1/32px.svg\" alt=\"Vertex AI logo\"><br> Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/bigquery/import?url=https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\">\n",
        "      <img src=\"https://www.gstatic.com/images/branding/gcpiconscolors/bigquery/v1/32px.svg\" alt=\"BigQuery Studio logo\"><br> Open in BigQuery Studio\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://raw.githubusercontent.com/primer/octicons/refs/heads/main/icons/mark-github-24.svg\" alt=\"GitHub logo\"><br> View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "</table>\n",
        "\n",
        "<div style=\"clear: both;\"></div>\n",
        "\n",
        "<b>Share to:</b>\n",
        "\n",
        "<a href=\"https://www.linkedin.com/sharing/share-offsite/?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/8/81/LinkedIn_icon.svg\" alt=\"LinkedIn logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://bsky.app/intent/compose?text=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/7/7a/Bluesky_Logo.svg\" alt=\"Bluesky logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://twitter.com/intent/tweet?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/5a/X_icon_2.svg\" alt=\"X logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://reddit.com/submit?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://redditinc.com/hubfs/Reddit%20Inc/Brand/Reddit_Logo.png\" alt=\"Reddit logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://www.facebook.com/sharer/sharer.php?u=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/video-analysis/video_analysis_with_youtube_data_api_and_batch_prediction.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/51/Facebook_f_logo_%282019%29.svg\" alt=\"Facebook logo\">\n",
        "</a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "84f0f73a0f76"
      },
      "source": [
        "| | |\n",
        "|-|-|\n",
        "| Author(s) | [Alok Pattani](https://github.com/alokpattani/) |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tvgnzT1CKxrO"
      },
      "source": [
        "## Overview\n",
        "\n",
        "In this notebook, you'll explore how to search and analyze publicly available [YouTube](https://www.youtube.com/) videos at scale, using the [YouTube Data API](https://developers.google.com/youtube/v3/getting-started), [Vertex AI Batch Prediction with Gemini](https://cloud.google.com/vertex-ai/generative-ai/docs/multimodal/batch-prediction-gemini), and [BigQuery](https://cloud.google.com/bigquery).\n",
        "\n",
        "You will complete the following tasks:\n",
        "- Use the YouTube Data API to find videos of interest, including by search query and channel.\n",
        "- Summarize those YouTube videos from a specific query or channel using Gemini, with both online and batch prediction.\n",
        "- Use batch prediction to extract a specific set of structured outputs from a larger set of YouTube videos.\n",
        "- Get information about and extract insights from those videos by aggregating Gemini's extracted results in BigQuery."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "61RBz8LLbxCR"
      },
      "source": [
        "## Get started"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "No17Cw5hgx12"
      },
      "source": [
        "### Install Vertex AI SDK and other required packages\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "tFy3H3aPgx12"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Note: you may need to restart the kernel to use updated packages.\n"
          ]
        }
      ],
      "source": [
        "%pip install --upgrade --user --quiet google-cloud-aiplatform google-api-python-client google-auth-oauthlib google-auth-httplib2"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R5Xep4W9lq-Z"
      },
      "source": [
        "### Restart runtime\n",
        "\n",
        "To use the newly installed packages in this Jupyter runtime, you must restart the runtime. You can do this by running the cell below, which restarts the current kernel.\n",
        "\n",
        "The restart might take a minute or longer. After it's restarted, continue to the next step."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XRvKdaPDTznN"
      },
      "outputs": [],
      "source": [
        "import IPython\n",
        "\n",
        "app = IPython.Application.instance()\n",
        "app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SbmM4z7FOBpM"
      },
      "source": [
        "<div class=\"alert alert-block alert-warning\">\n",
        "<b>⚠️ The kernel is going to restart. Wait until it's finished before continuing to the next step. ⚠️</b>\n",
        "</div>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dmWOrTJ3gx13"
      },
      "source": [
        "### Authenticate your notebook environment (Colab only)\n",
        "\n",
        "If you're running this notebook on Google Colab, run the cell below to authenticate your environment."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "NyKGtVQjgx13"
      },
      "outputs": [],
      "source": [
        "import sys\n",
        "\n",
        "if \"google.colab\" in sys.modules:\n",
        "    from google.colab import auth\n",
        "\n",
        "    auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DF4l8DTdWgPY"
      },
      "source": [
        "### Set Google Cloud project information and initialize Vertex AI SDK\n",
        "\n",
        "To get started using Vertex AI, you must have an existing Google Cloud project and [enable the Vertex AI API](https://console.cloud.google.com/flows/enableapi?apiid=aiplatform.googleapis.com).\n",
        "\n",
        "Learn more about [setting up a project and a development environment](https://cloud.google.com/vertex-ai/docs/start/cloud-environment)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "Nqwi-5ufWp_B"
      },
      "outputs": [],
      "source": [
        "# Use the environment variable if the user doesn't provide Project ID.\n",
        "import os\n",
        "\n",
        "import vertexai\n",
        "\n",
        "# fmt: off\n",
        "PROJECT_ID = \"[your-project-id]\"  # @param {type:\"string\", isTemplate: true}\n",
        "# fmt: on\n",
        "if PROJECT_ID == \"[your-project-id]\":\n",
        "    PROJECT_ID = str(os.environ.get(\"GOOGLE_CLOUD_PROJECT\"))\n",
        "\n",
        "LOCATION = os.environ.get(\"GOOGLE_CLOUD_REGION\", \"us-central1\")\n",
        "\n",
        "API_ENDPOINT = f\"{LOCATION}-aiplatform.googleapis.com\"\n",
        "\n",
        "vertexai.init(project=PROJECT_ID, location=LOCATION)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EdvJRUWRNGHE"
      },
      "source": [
        "## Set up libraries, YouTube Data API, and Gemini models"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5303c05f7aa6"
      },
      "source": [
        "### Import libraries"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6fc324893334"
      },
      "outputs": [],
      "source": [
        "import json\n",
        "import time\n",
        "\n",
        "import googleapiclient.discovery\n",
        "import googleapiclient.errors\n",
        "import pandas as pd\n",
        "from IPython.display import HTML, Markdown, display\n",
        "from google.cloud import bigquery\n",
        "from vertexai.batch_prediction import BatchPredictionJob\n",
        "from vertexai.generative_models import GenerativeModel, Part"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5e60382d1d63"
      },
      "source": [
        "### Set YouTube Data API v3 Info\n",
        "\n",
        "You need an API key to access the YouTube Data API for use in this notebook. Please follow [these instructions](https://developers.google.com/youtube/v3/getting-started#before-you-start) to perform the appropriate setup and get your key if you don't already have one."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "f2b734a622a0"
      },
      "outputs": [],
      "source": [
        "# Enter your YouTube data API key here\n",
        "# fmt: off\n",
        "YOUTUBE_DATA_API_KEY = \"[your-youtube-data-api-key]\"  # @param {type:\"string\"}\n",
        "# fmt: on"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "e43229f3ad4f"
      },
      "source": [
        "### Load models\n",
        "Pick from the various Gemini models that support batch prediction [listed here](https://cloud.google.com/vertex-ai/generative-ai/docs/multimodal/batch-prediction-gemini#models_that_support_batch_predictions)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "cf93d5f0ce00"
      },
      "outputs": [],
      "source": [
        "# Set Gemini Flash and Pro models to be used in this notebook\n",
        "# fmt: off\n",
        "GEMINI_FLASH_MODEL_ID = \"gemini-2.0-flash\"  # @param {type:\"string\"}\n",
        "GEMINI_PRO_MODEL_ID = \"gemini-2.0-flash\"  # @param {type:\"string\"}\n",
        "# fmt: on\n",
        "\n",
        "gemini_flash_model = GenerativeModel(GEMINI_FLASH_MODEL_ID)\n",
        "gemini_pro_model = GenerativeModel(GEMINI_PRO_MODEL_ID)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a002b236db75"
      },
      "source": [
        "### Setup BigQuery client, dataset, and tables"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "id": "7e7adaec209f"
      },
      "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",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "Empty DataFrame\n",
              "Columns: []\n",
              "Index: []"
            ]
          },
          "execution_count": 6,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Create BQ client\n",
        "BQ_CLIENT = bigquery.Client(project=PROJECT_ID)\n",
        "\n",
        "# Function to run BQ query using created client and return results as data frame\n",
        "\n",
        "\n",
        "def get_bq_query_results_as_df(query_text):\n",
        "    bq_results_table = BQ_CLIENT.query(query_text).to_dataframe()\n",
        "\n",
        "    return bq_results_table\n",
        "\n",
        "\n",
        "# Names of BQ dataset and tables to be created/used in this notebook\n",
        "BQ_DATASET = \"youtube_video_analysis\"  # @param {type:\"string\"}\n",
        "\n",
        "# fmt: off\n",
        "BATCH_PREDICTION_REQUESTS_TABLE = \"video_analysis_batch_requests\"  # @param {type:\"string\"}\n",
        "\n",
        "BATCH_PREDICTION_RESULTS_TABLE = \"video_analysis_batch_results\"  # @param {type:\"string\"}\n",
        "# fmt: on\n",
        "\n",
        "# Create BQ dataset if it doesn't already exist\n",
        "create_dataset_if_nec_query = f\"\"\"\n",
        "    CREATE SCHEMA IF NOT EXISTS `{BQ_DATASET}`\n",
        "    OPTIONS(\n",
        "      location='{LOCATION}'\n",
        "    );\n",
        "    \"\"\"\n",
        "\n",
        "get_bq_query_results_as_df(create_dataset_if_nec_query)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "109111fae02c"
      },
      "source": [
        "## Search for videos from YouTube Data API and summarize them with Gemini"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3de12c9fe2ed"
      },
      "source": [
        "### Use YouTube Data API to find videos by query\n",
        "\n",
        "Set various parameters in the cell below to conduct a search on YouTube using [the Data API's search functionality](https://developers.google.com/youtube/v3/docs/search/list).\n",
        "\n",
        "The default parameters in this notebook create a search for 3 short (<4-minute) videos of ice cream flavor reviews from the last 30 days, sorted by relevance."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "metadata": {
        "id": "9aaf3698e27c"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "{'kind': 'youtube#searchListResponse', 'etag': '-8SX2xhpIww5lrEK_5NvfbEa9TQ', 'nextPageToken': 'CAMQAA', 'regionCode': 'US', 'pageInfo': {'totalResults': 26207, 'resultsPerPage': 3}, 'items': [{'kind': 'youtube#searchResult', 'etag': 'beJoEcBaUkM8RJ5SFKkbm9U2Fmc', 'id': {'kind': 'youtube#video', 'videoId': 'mmkkJ2xyXzI'}, 'snippet': {'publishedAt': '2024-11-30T17:00:49Z', 'channelId': 'UCpb-2N1mSSaiiIsFxaM4KcQ', 'title': 'NEW Salt &amp; Straw Holiday Flavors Review 🍨🎅🏽 | Dessert Review #icecream #tastetest', 'description': \"morganchomps #holidayfood #dessert #saltandstraw #christmascookies #eggnog Trying Salt & Straw's NEW Holiday flavor ...\", 'thumbnails': {'default': {'url': 'https://i.ytimg.com/vi/mmkkJ2xyXzI/default.jpg', 'width': 120, 'height': 90}, 'medium': {'url': 'https://i.ytimg.com/vi/mmkkJ2xyXzI/mqdefault.jpg', 'width': 320, 'height': 180}, 'high': {'url': 'https://i.ytimg.com/vi/mmkkJ2xyXzI/hqdefault.jpg', 'width': 480, 'height': 360}}, 'channelTitle': 'Morgan Chomps', 'liveBroadcastContent': 'none', 'publishTime': '2024-11-30T17:00:49Z'}}, {'kind': 'youtube#searchResult', 'etag': 'ef_43ZewZxgioFz1ejqnHD43i7Q', 'id': {'kind': 'youtube#video', 'videoId': '3tKn3EclDpA'}, 'snippet': {'publishedAt': '2024-12-03T01:10:41Z', 'channelId': 'UCuuLy9wn33DAE7qNt7oVGww', 'title': 'I Ate The Weirdest Ice Cream Flavors', 'description': '', 'thumbnails': {'default': {'url': 'https://i.ytimg.com/vi/3tKn3EclDpA/default.jpg', 'width': 120, 'height': 90}, 'medium': {'url': 'https://i.ytimg.com/vi/3tKn3EclDpA/mqdefault.jpg', 'width': 320, 'height': 180}, 'high': {'url': 'https://i.ytimg.com/vi/3tKn3EclDpA/hqdefault.jpg', 'width': 480, 'height': 360}}, 'channelTitle': 'DanCookedIt', 'liveBroadcastContent': 'none', 'publishTime': '2024-12-03T01:10:41Z'}}, {'kind': 'youtube#searchResult', 'etag': '3ASuBPa5DVybtYl8D_yfZuv8Pzk', 'id': {'kind': 'youtube#video', 'videoId': '0M3CkYFclgw'}, 'snippet': {'publishedAt': '2024-11-14T23:16:26Z', 'channelId': 'UCep1MVEsZM7gCFLypGkf63A', 'title': 'Alec’s Ice Cream Review #icecream #peanutbutter #snackreview @alecsicecream', 'description': '', 'thumbnails': {'default': {'url': 'https://i.ytimg.com/vi/0M3CkYFclgw/default.jpg', 'width': 120, 'height': 90}, 'medium': {'url': 'https://i.ytimg.com/vi/0M3CkYFclgw/mqdefault.jpg', 'width': 320, 'height': 180}, 'high': {'url': 'https://i.ytimg.com/vi/0M3CkYFclgw/hqdefault.jpg', 'width': 480, 'height': 360}}, 'channelTitle': 'Max Leventer', 'liveBroadcastContent': 'none', 'publishTime': '2024-11-14T23:16:26Z'}}]}\n"
          ]
        }
      ],
      "source": [
        "# Set various parameters to search YouTube for relevant videos\n",
        "search_query = \"ice cream flavor reviews\"  # @param {type:\"string\"}\n",
        "\n",
        "# fmt: off\n",
        "video_duration_type = \"short\"  # @param {type:\"string\"}['any', 'long', 'medium', 'short']\n",
        "\n",
        "# To get newer/fresher videos, modify to lower # of days\n",
        "published_within_last_X_days = 30  # @param {type:\"integer\"}\n",
        "\n",
        "# Different ways to order results\n",
        "order_criteria = \"relevance\"  # @param {type:\"string\"}['date', 'rating', 'relevance', 'title', 'viewCount']\n",
        "# fmt: on\n",
        "\n",
        "# # of results to be returned - max is 50 results on 1 API call\n",
        "num_results = 3  # @param {type:\"integer\"}\n",
        "\n",
        "# Function to get response from YouTube API given specific query & various other parameters\n",
        "\n",
        "\n",
        "def get_yt_data_api_response_for_search_query(\n",
        "    query, video_duration, max_num_days_ago, channel_id, video_order, num_video_results\n",
        "):\n",
        "    api_service_name = \"youtube\"\n",
        "    api_version = \"v3\"\n",
        "    developer_key = YOUTUBE_DATA_API_KEY\n",
        "\n",
        "    youtube = googleapiclient.discovery.build(\n",
        "        api_service_name, api_version, developerKey=developer_key\n",
        "    )\n",
        "\n",
        "    published_after_timestamp = (\n",
        "        (pd.Timestamp.now() - pd.DateOffset(days=max_num_days_ago))\n",
        "        .tz_localize(\"UTC\")\n",
        "        .isoformat()\n",
        "    )\n",
        "\n",
        "    # Using Search:list - https://developers.google.com/youtube/v3/docs/search/list\n",
        "    yt_data_api_request = youtube.search().list(\n",
        "        part=\"id,snippet\",\n",
        "        type=\"video\",\n",
        "        q=query,\n",
        "        videoDuration=video_duration,\n",
        "        maxResults=num_video_results,\n",
        "        publishedAfter=published_after_timestamp,\n",
        "        channelId=channel_id,\n",
        "        order=video_order,\n",
        "    )\n",
        "\n",
        "    yt_data_api_response = yt_data_api_request.execute()\n",
        "\n",
        "    return yt_data_api_response\n",
        "\n",
        "\n",
        "yt_data_api_results = get_yt_data_api_response_for_search_query(\n",
        "    query=search_query,\n",
        "    video_duration=video_duration_type,\n",
        "    max_num_days_ago=published_within_last_X_days,\n",
        "    channel_id=None,\n",
        "    video_order=order_criteria,\n",
        "    num_video_results=num_results,\n",
        ")\n",
        "\n",
        "print(yt_data_api_results)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "metadata": {
        "id": "6b2569eae88a"
      },
      "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>videoId</th>\n",
              "      <th>videoURL</th>\n",
              "      <th>videoTitle</th>\n",
              "      <th>videoDescription</th>\n",
              "      <th>channelId</th>\n",
              "      <th>channelTitle</th>\n",
              "      <th>publishedAt</th>\n",
              "      <th>thumbnailURL</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>mmkkJ2xyXzI</td>\n",
              "      <td>https://www.youtube.com/watch?v=mmkkJ2xyXzI</td>\n",
              "      <td>NEW Salt &amp;amp; Straw Holiday Flavors Review 🍨🎅...</td>\n",
              "      <td>morganchomps #holidayfood #dessert #saltandstr...</td>\n",
              "      <td>UCpb-2N1mSSaiiIsFxaM4KcQ</td>\n",
              "      <td>Morgan Chomps</td>\n",
              "      <td>2024-11-30T17:00:49Z</td>\n",
              "      <td>https://i.ytimg.com/vi/mmkkJ2xyXzI/default.jpg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>3tKn3EclDpA</td>\n",
              "      <td>https://www.youtube.com/watch?v=3tKn3EclDpA</td>\n",
              "      <td>I Ate The Weirdest Ice Cream Flavors</td>\n",
              "      <td></td>\n",
              "      <td>UCuuLy9wn33DAE7qNt7oVGww</td>\n",
              "      <td>DanCookedIt</td>\n",
              "      <td>2024-12-03T01:10:41Z</td>\n",
              "      <td>https://i.ytimg.com/vi/3tKn3EclDpA/default.jpg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>0M3CkYFclgw</td>\n",
              "      <td>https://www.youtube.com/watch?v=0M3CkYFclgw</td>\n",
              "      <td>Alec’s Ice Cream Review #icecream #peanutbutte...</td>\n",
              "      <td></td>\n",
              "      <td>UCep1MVEsZM7gCFLypGkf63A</td>\n",
              "      <td>Max Leventer</td>\n",
              "      <td>2024-11-14T23:16:26Z</td>\n",
              "      <td>https://i.ytimg.com/vi/0M3CkYFclgw/default.jpg</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       videoId                                     videoURL  \\\n",
              "0  mmkkJ2xyXzI  https://www.youtube.com/watch?v=mmkkJ2xyXzI   \n",
              "1  3tKn3EclDpA  https://www.youtube.com/watch?v=3tKn3EclDpA   \n",
              "2  0M3CkYFclgw  https://www.youtube.com/watch?v=0M3CkYFclgw   \n",
              "\n",
              "                                          videoTitle  \\\n",
              "0  NEW Salt &amp; Straw Holiday Flavors Review 🍨🎅...   \n",
              "1               I Ate The Weirdest Ice Cream Flavors   \n",
              "2  Alec’s Ice Cream Review #icecream #peanutbutte...   \n",
              "\n",
              "                                    videoDescription  \\\n",
              "0  morganchomps #holidayfood #dessert #saltandstr...   \n",
              "1                                                      \n",
              "2                                                      \n",
              "\n",
              "                  channelId   channelTitle           publishedAt  \\\n",
              "0  UCpb-2N1mSSaiiIsFxaM4KcQ  Morgan Chomps  2024-11-30T17:00:49Z   \n",
              "1  UCuuLy9wn33DAE7qNt7oVGww    DanCookedIt  2024-12-03T01:10:41Z   \n",
              "2  UCep1MVEsZM7gCFLypGkf63A   Max Leventer  2024-11-14T23:16:26Z   \n",
              "\n",
              "                                     thumbnailURL  \n",
              "0  https://i.ytimg.com/vi/mmkkJ2xyXzI/default.jpg  \n",
              "1  https://i.ytimg.com/vi/3tKn3EclDpA/default.jpg  \n",
              "2  https://i.ytimg.com/vi/0M3CkYFclgw/default.jpg  "
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Function to convert YouTube API response into data frame w/ specific schema\n",
        "\n",
        "\n",
        "def convert_yt_data_api_response_to_df(yt_data_api_response):\n",
        "    # Convert API response into data frame for further analysis\n",
        "    yt_data_api_response_items_df = pd.json_normalize(yt_data_api_response[\"items\"])\n",
        "\n",
        "    yt_data_api_response_df = yt_data_api_response_items_df.assign(\n",
        "        videoURL=\"https://www.youtube.com/watch?v=\"\n",
        "        + yt_data_api_response_items_df[\"id.videoId\"]\n",
        "    )[\n",
        "        [\n",
        "            \"id.videoId\",\n",
        "            \"videoURL\",\n",
        "            \"snippet.title\",\n",
        "            \"snippet.description\",\n",
        "            \"snippet.channelId\",\n",
        "            \"snippet.channelTitle\",\n",
        "            \"snippet.publishedAt\",\n",
        "            \"snippet.thumbnails.default.url\",\n",
        "        ]\n",
        "    ].rename(\n",
        "        columns={\n",
        "            \"id.videoId\": \"videoId\",\n",
        "            \"snippet.title\": \"videoTitle\",\n",
        "            \"snippet.description\": \"videoDescription\",\n",
        "            \"snippet.channelId\": \"channelId\",\n",
        "            \"snippet.channelTitle\": \"channelTitle\",\n",
        "            \"snippet.publishedAt\": \"publishedAt\",\n",
        "            \"snippet.thumbnails.default.url\": \"thumbnailURL\",\n",
        "        }\n",
        "    )\n",
        "\n",
        "    return yt_data_api_response_df\n",
        "\n",
        "\n",
        "yt_data_api_results_df = convert_yt_data_api_response_to_df(yt_data_api_results)\n",
        "\n",
        "display(yt_data_api_results_df.head())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7588589cb372"
      },
      "source": [
        "### Get summary from Gemini for each video\n",
        "\n",
        "Next, we can ask Gemini to summarize each video using the URLs created from the results of our YouTube search, then display 1 of the videos with its summary for inspection (if desired)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "8d711a9169bc"
      },
      "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>videoId</th>\n",
              "      <th>videoURL</th>\n",
              "      <th>videoTitle</th>\n",
              "      <th>videoDescription</th>\n",
              "      <th>channelId</th>\n",
              "      <th>channelTitle</th>\n",
              "      <th>publishedAt</th>\n",
              "      <th>thumbnailURL</th>\n",
              "      <th>geminiVideoSummary</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>mmkkJ2xyXzI</td>\n",
              "      <td>https://www.youtube.com/watch?v=mmkkJ2xyXzI</td>\n",
              "      <td>NEW Salt &amp;amp; Straw Holiday Flavors Review 🍨🎅...</td>\n",
              "      <td>morganchomps #holidayfood #dessert #saltandstr...</td>\n",
              "      <td>UCpb-2N1mSSaiiIsFxaM4KcQ</td>\n",
              "      <td>Morgan Chomps</td>\n",
              "      <td>2024-11-30T17:00:49Z</td>\n",
              "      <td>https://i.ytimg.com/vi/mmkkJ2xyXzI/default.jpg</td>\n",
              "      <td>In this video, the creator presents Salt &amp; Str...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>3tKn3EclDpA</td>\n",
              "      <td>https://www.youtube.com/watch?v=3tKn3EclDpA</td>\n",
              "      <td>I Ate The Weirdest Ice Cream Flavors</td>\n",
              "      <td></td>\n",
              "      <td>UCuuLy9wn33DAE7qNt7oVGww</td>\n",
              "      <td>DanCookedIt</td>\n",
              "      <td>2024-12-03T01:10:41Z</td>\n",
              "      <td>https://i.ytimg.com/vi/3tKn3EclDpA/default.jpg</td>\n",
              "      <td>This video shows a man trying various flavors ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>0M3CkYFclgw</td>\n",
              "      <td>https://www.youtube.com/watch?v=0M3CkYFclgw</td>\n",
              "      <td>Alec’s Ice Cream Review #icecream #peanutbutte...</td>\n",
              "      <td></td>\n",
              "      <td>UCep1MVEsZM7gCFLypGkf63A</td>\n",
              "      <td>Max Leventer</td>\n",
              "      <td>2024-11-14T23:16:26Z</td>\n",
              "      <td>https://i.ytimg.com/vi/0M3CkYFclgw/default.jpg</td>\n",
              "      <td>The presenter reviews two flavors of Alec’s ic...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       videoId                                     videoURL  \\\n",
              "0  mmkkJ2xyXzI  https://www.youtube.com/watch?v=mmkkJ2xyXzI   \n",
              "1  3tKn3EclDpA  https://www.youtube.com/watch?v=3tKn3EclDpA   \n",
              "2  0M3CkYFclgw  https://www.youtube.com/watch?v=0M3CkYFclgw   \n",
              "\n",
              "                                          videoTitle  \\\n",
              "0  NEW Salt &amp; Straw Holiday Flavors Review 🍨🎅...   \n",
              "1               I Ate The Weirdest Ice Cream Flavors   \n",
              "2  Alec’s Ice Cream Review #icecream #peanutbutte...   \n",
              "\n",
              "                                    videoDescription  \\\n",
              "0  morganchomps #holidayfood #dessert #saltandstr...   \n",
              "1                                                      \n",
              "2                                                      \n",
              "\n",
              "                  channelId   channelTitle           publishedAt  \\\n",
              "0  UCpb-2N1mSSaiiIsFxaM4KcQ  Morgan Chomps  2024-11-30T17:00:49Z   \n",
              "1  UCuuLy9wn33DAE7qNt7oVGww    DanCookedIt  2024-12-03T01:10:41Z   \n",
              "2  UCep1MVEsZM7gCFLypGkf63A   Max Leventer  2024-11-14T23:16:26Z   \n",
              "\n",
              "                                     thumbnailURL  \\\n",
              "0  https://i.ytimg.com/vi/mmkkJ2xyXzI/default.jpg   \n",
              "1  https://i.ytimg.com/vi/3tKn3EclDpA/default.jpg   \n",
              "2  https://i.ytimg.com/vi/0M3CkYFclgw/default.jpg   \n",
              "\n",
              "                                  geminiVideoSummary  \n",
              "0  In this video, the creator presents Salt & Str...  \n",
              "1  This video shows a man trying various flavors ...  \n",
              "2  The presenter reviews two flavors of Alec’s ic...  "
            ]
          },
          "execution_count": 9,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Get summary from Gemini for each video in data frame\n",
        "\n",
        "\n",
        "def get_gemini_summary_from_youtube_video_url(video_url):\n",
        "    video_summary_prompt = \"Summarize this video.\"\n",
        "\n",
        "    # Gemini for highest quality (can change to Flash if latency/cost are of concern)\n",
        "    video_summary_response = gemini_pro_model.generate_content(\n",
        "        [video_summary_prompt, Part.from_uri(mime_type=\"video/webm\", uri=video_url)]\n",
        "    )\n",
        "\n",
        "    summary_text = video_summary_response.text\n",
        "\n",
        "    return summary_text\n",
        "\n",
        "\n",
        "yt_data_api_results_df[\"geminiVideoSummary\"] = yt_data_api_results_df[\"videoURL\"].apply(\n",
        "    get_gemini_summary_from_youtube_video_url\n",
        ")\n",
        "\n",
        "yt_data_api_results_df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 10,
      "metadata": {
        "id": "5c8a32e14eec"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "<iframe width=\"560\" height=\"315\" src=\"https://www.youtube.com/embed/0M3CkYFclgw\"\n",
              "title=\"YouTube video player\" frameborder=\"0\" allow=\"accelerometer; autoplay; \n",
              "clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen>\n",
              "</iframe>\n"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/markdown": [
              "<b>Summary of Video from Gemini:</b><br>The presenter reviews two flavors of Alec’s ice cream. He used coupons from Alec’s for two flavors that looked interesting to him: Peanut Butter Fudge Honeycomb and Nutty Butter Brittle. Both flavors are bought at Whole Foods.\n",
              "\n",
              "The Peanut Butter Fudge Honeycomb is similar in flavor to Ben and Jerry’s Tonight Dough. The presenter rates the ice cream 8.3 out of 10, but wishes it had a bit more crunch.\n",
              "\n",
              "The Nutty Butter Brittle flavor is also good, and described as unique. The presenter gave it an 8.1 out of 10, noting the ice cream is not as creamy or as rich as they would like. \n",
              "\n",
              "The presenter recommends viewers try both flavors."
            ],
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Pick 1 video above to display video and its summary together\n",
        "sample_video = yt_data_api_results_df.sample(1).iloc[0].to_dict()\n",
        "\n",
        "sample_video_embed_url = sample_video[\"videoURL\"].replace(\"/watch?v=\", \"/embed/\")\n",
        "\n",
        "# Create HTML code to directly embed video\n",
        "sample_video_embed_html_code = f\"\"\"\n",
        "<iframe width=\"560\" height=\"315\" src=\"{sample_video_embed_url}\"\n",
        "title=\"YouTube video player\" frameborder=\"0\" allow=\"accelerometer; autoplay; \n",
        "clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen>\n",
        "</iframe>\n",
        "\"\"\"\n",
        "\n",
        "# Display embedded YouTube video\n",
        "display(HTML(sample_video_embed_html_code))\n",
        "\n",
        "display(\n",
        "    Markdown(\n",
        "        f\"<b>Summary of Video from Gemini:</b><br>{sample_video['geminiVideoSummary']}\"\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fabaaaeb6606"
      },
      "source": [
        "## Get a larger set of videos using YouTube Data API, prepare to analyze them in batch\n",
        "\n",
        "In this section, we'll search for and set up to analyze a larger set of videos from YouTube.  \n",
        "\n",
        "The default parameters in this notebook search the [Major League Baseball (MLB) YouTube channel](https://www.youtube.com/@MLB), channel ID `UCoLrcjPV5PbUrUyXq5mjc_A`, for medium-length videos from the last 365 days, returning the top 50 videos (the max from 1 API call) by view count."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "id": "9bd742163fc7"
      },
      "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>videoId</th>\n",
              "      <th>videoURL</th>\n",
              "      <th>videoTitle</th>\n",
              "      <th>videoDescription</th>\n",
              "      <th>channelId</th>\n",
              "      <th>channelTitle</th>\n",
              "      <th>publishedAt</th>\n",
              "      <th>thumbnailURL</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>-jYfC4YYXIw</td>\n",
              "      <td>https://www.youtube.com/watch?v=-jYfC4YYXIw</td>\n",
              "      <td>GREATEST GAME EVER?!? Shohei Ohtani goes 6-FOR...</td>\n",
              "      <td>Is this the greatest game in baseball history?...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-09-20T00:01:31Z</td>\n",
              "      <td>https://i.ytimg.com/vi/-jYfC4YYXIw/default.jpg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>j3ykZoQMJLI</td>\n",
              "      <td>https://www.youtube.com/watch?v=j3ykZoQMJLI</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 5 Highli...</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 5 full g...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-31T05:24:46Z</td>\n",
              "      <td>https://i.ytimg.com/vi/j3ykZoQMJLI/default.jpg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>jSgk6wjhY7Q</td>\n",
              "      <td>https://www.youtube.com/watch?v=jSgk6wjhY7Q</td>\n",
              "      <td>Yankees vs. Dodgers World Series Game 1 Highli...</td>\n",
              "      <td>Yankees vs. Dodgers World Series Game 1 full g...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-26T05:34:00Z</td>\n",
              "      <td>https://i.ytimg.com/vi/jSgk6wjhY7Q/default.jpg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>jrDacPNWBGA</td>\n",
              "      <td>https://www.youtube.com/watch?v=jrDacPNWBGA</td>\n",
              "      <td>FREDDIE FREEMAN HITS A WALK-OFF GRAND SLAM TO ...</td>\n",
              "      <td>I DON'T BELIEVE WHAT I JUST SAW! Don't forget ...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-26T03:52:45Z</td>\n",
              "      <td>https://i.ytimg.com/vi/jrDacPNWBGA/default.jpg</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>CbiRxl4OMPc</td>\n",
              "      <td>https://www.youtube.com/watch?v=CbiRxl4OMPc</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 3 Highli...</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 3 full g...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-29T04:52:50Z</td>\n",
              "      <td>https://i.ytimg.com/vi/CbiRxl4OMPc/default.jpg</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       videoId                                     videoURL  \\\n",
              "0  -jYfC4YYXIw  https://www.youtube.com/watch?v=-jYfC4YYXIw   \n",
              "1  j3ykZoQMJLI  https://www.youtube.com/watch?v=j3ykZoQMJLI   \n",
              "2  jSgk6wjhY7Q  https://www.youtube.com/watch?v=jSgk6wjhY7Q   \n",
              "3  jrDacPNWBGA  https://www.youtube.com/watch?v=jrDacPNWBGA   \n",
              "4  CbiRxl4OMPc  https://www.youtube.com/watch?v=CbiRxl4OMPc   \n",
              "\n",
              "                                          videoTitle  \\\n",
              "0  GREATEST GAME EVER?!? Shohei Ohtani goes 6-FOR...   \n",
              "1  Dodgers vs. Yankees World Series Game 5 Highli...   \n",
              "2  Yankees vs. Dodgers World Series Game 1 Highli...   \n",
              "3  FREDDIE FREEMAN HITS A WALK-OFF GRAND SLAM TO ...   \n",
              "4  Dodgers vs. Yankees World Series Game 3 Highli...   \n",
              "\n",
              "                                    videoDescription  \\\n",
              "0  Is this the greatest game in baseball history?...   \n",
              "1  Dodgers vs. Yankees World Series Game 5 full g...   \n",
              "2  Yankees vs. Dodgers World Series Game 1 full g...   \n",
              "3  I DON'T BELIEVE WHAT I JUST SAW! Don't forget ...   \n",
              "4  Dodgers vs. Yankees World Series Game 3 full g...   \n",
              "\n",
              "                  channelId channelTitle           publishedAt  \\\n",
              "0  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-09-20T00:01:31Z   \n",
              "1  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-31T05:24:46Z   \n",
              "2  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-26T05:34:00Z   \n",
              "3  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-26T03:52:45Z   \n",
              "4  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-29T04:52:50Z   \n",
              "\n",
              "                                     thumbnailURL  \n",
              "0  https://i.ytimg.com/vi/-jYfC4YYXIw/default.jpg  \n",
              "1  https://i.ytimg.com/vi/j3ykZoQMJLI/default.jpg  \n",
              "2  https://i.ytimg.com/vi/jSgk6wjhY7Q/default.jpg  \n",
              "3  https://i.ytimg.com/vi/jrDacPNWBGA/default.jpg  \n",
              "4  https://i.ytimg.com/vi/CbiRxl4OMPc/default.jpg  "
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Intentionally leaving default empty to search for all videos w/in a channel\n",
        "search_query = \"\"  # @param {type:\"string\"}\n",
        "\n",
        "# fmt: off\n",
        "video_duration_type = \"medium\"  # @param {type:\"string\"}['any', 'long', 'medium', 'short']\n",
        "\n",
        "published_within_last_X_days = 365  # @param {type:\"integer\"}\n",
        "\n",
        "# Default value of 'UCoLrcjPV5PbUrUyXq5mjc_A' is for specific MLB channel\n",
        "channel_id = \"UCoLrcjPV5PbUrUyXq5mjc_A\"  # @param {type:\"string\"}\n",
        "\n",
        "order_criteria = \"viewCount\"  # @param {type:\"string\"}['date', 'rating', 'relevance', 'title', 'viewCount']\n",
        "# fmt: on\n",
        "\n",
        "# Max is 50 results on 1 API call\n",
        "num_results = 50  # @param {type:\"integer\"}\n",
        "\n",
        "yt_data_api_channel_results = get_yt_data_api_response_for_search_query(\n",
        "    query=search_query,\n",
        "    video_duration=video_duration_type,\n",
        "    max_num_days_ago=published_within_last_X_days,\n",
        "    channel_id=channel_id,\n",
        "    video_order=order_criteria,\n",
        "    num_video_results=num_results,\n",
        ")\n",
        "\n",
        "yt_data_api_channel_results_df = convert_yt_data_api_response_to_df(\n",
        "    yt_data_api_channel_results\n",
        ")\n",
        "\n",
        "display(yt_data_api_channel_results_df.head())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ad035bd59e65"
      },
      "source": [
        "The next cell has the various specifics for our Gemini video extraction task to be applied to all the video results from YouTube: system instruction, prompt, and response schema for [controlled generation](https://cloud.google.com/vertex-ai/generative-ai/docs/multimodal/control-generated-output) (i.e. creating structured outputs for further analysis).\n",
        "\n",
        "The defaults in this notebook are tailored to the use case of counting the number of appearances or various people and teams in videos from the MLB channel (based on the default YouTube Data API search results above). Be sure to modify each of these pieces for your specific use case.\n",
        "\n",
        "Using those pieces, we create a single Gemini cURL request per row - 1 for each YouTube video - in order to set up for using batch prediction."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "id": "d5a93cd5d2fa"
      },
      "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>videoId</th>\n",
              "      <th>videoURL</th>\n",
              "      <th>videoTitle</th>\n",
              "      <th>videoDescription</th>\n",
              "      <th>channelId</th>\n",
              "      <th>channelTitle</th>\n",
              "      <th>publishedAt</th>\n",
              "      <th>thumbnailURL</th>\n",
              "      <th>request</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>-jYfC4YYXIw</td>\n",
              "      <td>https://www.youtube.com/watch?v=-jYfC4YYXIw</td>\n",
              "      <td>GREATEST GAME EVER?!? Shohei Ohtani goes 6-FOR...</td>\n",
              "      <td>Is this the greatest game in baseball history?...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-09-20T00:01:31Z</td>\n",
              "      <td>https://i.ytimg.com/vi/-jYfC4YYXIw/default.jpg</td>\n",
              "      <td>{\"system_instruction\": {\"parts\": [{\"text\": \"Yo...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>j3ykZoQMJLI</td>\n",
              "      <td>https://www.youtube.com/watch?v=j3ykZoQMJLI</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 5 Highli...</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 5 full g...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-31T05:24:46Z</td>\n",
              "      <td>https://i.ytimg.com/vi/j3ykZoQMJLI/default.jpg</td>\n",
              "      <td>{\"system_instruction\": {\"parts\": [{\"text\": \"Yo...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>jSgk6wjhY7Q</td>\n",
              "      <td>https://www.youtube.com/watch?v=jSgk6wjhY7Q</td>\n",
              "      <td>Yankees vs. Dodgers World Series Game 1 Highli...</td>\n",
              "      <td>Yankees vs. Dodgers World Series Game 1 full g...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-26T05:34:00Z</td>\n",
              "      <td>https://i.ytimg.com/vi/jSgk6wjhY7Q/default.jpg</td>\n",
              "      <td>{\"system_instruction\": {\"parts\": [{\"text\": \"Yo...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>jrDacPNWBGA</td>\n",
              "      <td>https://www.youtube.com/watch?v=jrDacPNWBGA</td>\n",
              "      <td>FREDDIE FREEMAN HITS A WALK-OFF GRAND SLAM TO ...</td>\n",
              "      <td>I DON'T BELIEVE WHAT I JUST SAW! Don't forget ...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-26T03:52:45Z</td>\n",
              "      <td>https://i.ytimg.com/vi/jrDacPNWBGA/default.jpg</td>\n",
              "      <td>{\"system_instruction\": {\"parts\": [{\"text\": \"Yo...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>CbiRxl4OMPc</td>\n",
              "      <td>https://www.youtube.com/watch?v=CbiRxl4OMPc</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 3 Highli...</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 3 full g...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-29T04:52:50Z</td>\n",
              "      <td>https://i.ytimg.com/vi/CbiRxl4OMPc/default.jpg</td>\n",
              "      <td>{\"system_instruction\": {\"parts\": [{\"text\": \"Yo...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       videoId                                     videoURL  \\\n",
              "0  -jYfC4YYXIw  https://www.youtube.com/watch?v=-jYfC4YYXIw   \n",
              "1  j3ykZoQMJLI  https://www.youtube.com/watch?v=j3ykZoQMJLI   \n",
              "2  jSgk6wjhY7Q  https://www.youtube.com/watch?v=jSgk6wjhY7Q   \n",
              "3  jrDacPNWBGA  https://www.youtube.com/watch?v=jrDacPNWBGA   \n",
              "4  CbiRxl4OMPc  https://www.youtube.com/watch?v=CbiRxl4OMPc   \n",
              "\n",
              "                                          videoTitle  \\\n",
              "0  GREATEST GAME EVER?!? Shohei Ohtani goes 6-FOR...   \n",
              "1  Dodgers vs. Yankees World Series Game 5 Highli...   \n",
              "2  Yankees vs. Dodgers World Series Game 1 Highli...   \n",
              "3  FREDDIE FREEMAN HITS A WALK-OFF GRAND SLAM TO ...   \n",
              "4  Dodgers vs. Yankees World Series Game 3 Highli...   \n",
              "\n",
              "                                    videoDescription  \\\n",
              "0  Is this the greatest game in baseball history?...   \n",
              "1  Dodgers vs. Yankees World Series Game 5 full g...   \n",
              "2  Yankees vs. Dodgers World Series Game 1 full g...   \n",
              "3  I DON'T BELIEVE WHAT I JUST SAW! Don't forget ...   \n",
              "4  Dodgers vs. Yankees World Series Game 3 full g...   \n",
              "\n",
              "                  channelId channelTitle           publishedAt  \\\n",
              "0  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-09-20T00:01:31Z   \n",
              "1  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-31T05:24:46Z   \n",
              "2  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-26T05:34:00Z   \n",
              "3  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-26T03:52:45Z   \n",
              "4  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-29T04:52:50Z   \n",
              "\n",
              "                                     thumbnailURL  \\\n",
              "0  https://i.ytimg.com/vi/-jYfC4YYXIw/default.jpg   \n",
              "1  https://i.ytimg.com/vi/j3ykZoQMJLI/default.jpg   \n",
              "2  https://i.ytimg.com/vi/jSgk6wjhY7Q/default.jpg   \n",
              "3  https://i.ytimg.com/vi/jrDacPNWBGA/default.jpg   \n",
              "4  https://i.ytimg.com/vi/CbiRxl4OMPc/default.jpg   \n",
              "\n",
              "                                             request  \n",
              "0  {\"system_instruction\": {\"parts\": [{\"text\": \"Yo...  \n",
              "1  {\"system_instruction\": {\"parts\": [{\"text\": \"Yo...  \n",
              "2  {\"system_instruction\": {\"parts\": [{\"text\": \"Yo...  \n",
              "3  {\"system_instruction\": {\"parts\": [{\"text\": \"Yo...  \n",
              "4  {\"system_instruction\": {\"parts\": [{\"text\": \"Yo...  "
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Set up pieces (system instruction, prompt, response schema, config) for Gemini video extraction API calls\n",
        "\n",
        "video_extraction_system_instruction = \"\"\"You are a video analyst that carefully looks \n",
        "    through all frames of provided videos, extracting out the pieces necessary to respond to\n",
        "    user prompts. Make sure to look through and listen to the whole video, start to finish.\n",
        "    Only reference information in the video itself in your response.\"\"\"\n",
        "\n",
        "video_extraction_prompt = \"\"\"Provide a 2-3 sentence summary of the key themes from this video,\n",
        "    and also provide a list of each athlete, manager/coach, and team that is referenced or\n",
        "    shown. Use full names for people and teams - e.g. \"Shohei Ohtani\" instead of just \"Ohtani\"\n",
        "    and \"Los Angeles Dodgers\" instead of just \"Dodgers.\" Make sure to count only those involved\n",
        "    in the actual baseball in the video, and output only 1 entity per row.\"\"\"\n",
        "\n",
        "video_extraction_response_schema = {\n",
        "    \"type\": \"array\",\n",
        "    \"items\": {\n",
        "        \"type\": \"object\",\n",
        "        \"properties\": {\n",
        "            \"summary\": {\"type\": \"string\"},\n",
        "            \"references\": {\n",
        "                \"type\": \"array\",\n",
        "                \"items\": {\n",
        "                    \"type\": \"object\",\n",
        "                    \"properties\": {\n",
        "                        \"entity_name\": {\"type\": \"string\"},\n",
        "                        \"entity_type\": {\n",
        "                            \"type\": \"string\",\n",
        "                            \"enum\": [\"athlete\", \"manager or coach\", \"team\"],\n",
        "                        },\n",
        "                    },\n",
        "                },\n",
        "            },\n",
        "        },\n",
        "    },\n",
        "}\n",
        "\n",
        "video_extraction_generation_config = {\n",
        "    \"temperature\": 0.0,\n",
        "    \"max_output_tokens\": 8192,\n",
        "    \"response_mime_type\": \"application/json\",\n",
        "    \"response_schema\": video_extraction_response_schema,\n",
        "}\n",
        "\n",
        "# Function to build CURL request for given YT link, using pieces above\n",
        "\n",
        "\n",
        "def get_video_extraction_curl_request_for_yt_video_link(youtube_video_link):\n",
        "    video_extraction_curl_request_dict = {\n",
        "        \"system_instruction\": {\n",
        "            \"parts\": [{\"text\": video_extraction_system_instruction}]\n",
        "        },\n",
        "        \"contents\": [\n",
        "            {\n",
        "                \"role\": \"user\",\n",
        "                \"parts\": [\n",
        "                    {\"text\": video_extraction_prompt},\n",
        "                    {\n",
        "                        \"file_data\": {\n",
        "                            \"mimeType\": \"video/*\",\n",
        "                            \"fileUri\": youtube_video_link,\n",
        "                        }\n",
        "                    },\n",
        "                ],\n",
        "            }\n",
        "        ],\n",
        "        \"generation_config\": video_extraction_generation_config,\n",
        "    }\n",
        "\n",
        "    video_extraction_curl_request = json.dumps(video_extraction_curl_request_dict)\n",
        "\n",
        "    return video_extraction_curl_request\n",
        "\n",
        "\n",
        "# Create Gemini API CURL request for each YT video\n",
        "yt_data_api_channel_results_df[\"request\"] = yt_data_api_channel_results_df.apply(\n",
        "    lambda row: get_video_extraction_curl_request_for_yt_video_link(row[\"videoURL\"]),\n",
        "    axis=1,\n",
        ")\n",
        "\n",
        "display(yt_data_api_channel_results_df.head())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "metadata": {
        "id": "67c89d129646"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "LoadJob<project=gcp-data-science-demo, location=us-central1, id=307c3e4e-dbf2-4ef9-b4ca-2c05c54c89b6>"
            ]
          },
          "execution_count": 14,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Output table with YouTube API results and corresponding Gemini requests to BigQuery\n",
        "\n",
        "yt_api_results_with_bp_requests_table_load_job = BQ_CLIENT.load_table_from_dataframe(\n",
        "    yt_data_api_channel_results_df,\n",
        "    f\"{BQ_DATASET}.{BATCH_PREDICTION_REQUESTS_TABLE}\",\n",
        "    job_config=bigquery.LoadJobConfig(write_disposition=\"WRITE_TRUNCATE\"),\n",
        ")\n",
        "\n",
        "# Wait for the load job to complete\n",
        "yt_api_results_with_bp_requests_table_load_job.result()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0ba3921a86ce"
      },
      "source": [
        "## Submit batch prediction job to analyze multiple YouTube videos at once"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "666778f05678"
      },
      "source": [
        "### Set parameters for batch prediction request\n",
        "\n",
        "You create a batch prediction job using the BatchPredictionJob.submit() method. To make a batch prediction request, you specify a source model ID, an input source, and an output location - either Cloud Storage or BigQuery - where Vertex AI stores the batch prediction results.\n",
        "\n",
        "To learn more, see the [batch prediction API page](https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/batch-prediction-api).\n",
        "\n",
        "In the default case in this notebook, we'll use the BigQuery table with requests created in the previous section as input, and output results to another BigQuery table for further analysis."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "74317115557a"
      },
      "outputs": [],
      "source": [
        "# BQ URI of input table in form bq://PROJECT_ID.DATASET.TABLE\n",
        "# or Cloud Storage bucket URI\n",
        "INPUT_URI = f\"bq://{PROJECT_ID}.{BQ_DATASET}.{BATCH_PREDICTION_REQUESTS_TABLE}\"\n",
        "\n",
        "# BQ URI of target output table in form bq://PROJECT_ID.DATASET.TABLE\n",
        "# If the table doesn't already exist, then it is created for you\n",
        "OUTPUT_URI = f\"bq://{PROJECT_ID}.{BQ_DATASET}.{BATCH_PREDICTION_RESULTS_TABLE}\"\n",
        "\n",
        "# Pick which Gemini model to use here (default Flash)\n",
        "# fmt: off\n",
        "MODEL_ID = GEMINI_FLASH_MODEL_ID  # @param {type:\"raw\"} ['GEMINI_FLASH_MODEL_ID', 'GEMINI_PRO_MODEL_ID']\n",
        "# fmt: on"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cc59ef3c28d9"
      },
      "source": [
        "### Submit batch prediction request using Vertex AI SDK"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fdaf581f741b"
      },
      "outputs": [],
      "source": [
        "# Submit batch prediction request using Vertex AI SDK\n",
        "batch_prediction_job = BatchPredictionJob.submit(\n",
        "    source_model=MODEL_ID, input_dataset=INPUT_URI, output_uri_prefix=OUTPUT_URI\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6063eaae4589"
      },
      "source": [
        "If the batch prediction job goes through, the output above should contain a link you can use to monitor the job in [the Vertex AI Batch predictions page in the Google Cloud console](https://console.cloud.google.com/vertex-ai/batch-predictions).\n",
        "\n",
        "You can also print out the job status and other properties, as shown below."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "d1fcc2bda1d3"
      },
      "outputs": [],
      "source": [
        "print(f\"Job resource name: {batch_prediction_job.resource_name}\")\n",
        "print(f\"Model resource name: {batch_prediction_job.model_name}\")\n",
        "print(f\"Job state: {batch_prediction_job.state.name}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f5cd84eb86fc"
      },
      "source": [
        "Depending on the number of input items that you submitted, a batch generation task can take some time to complete. You can use the following code to check the job status and wait for the job to complete."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "id": "6e64a791e575"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Job succeeded!\n"
          ]
        }
      ],
      "source": [
        "# Refresh batch prediction job until complete\n",
        "while not batch_prediction_job.has_ended:\n",
        "    time.sleep(5)\n",
        "    batch_prediction_job.refresh()\n",
        "\n",
        "# Check if the job succeeds\n",
        "if batch_prediction_job.has_succeeded:\n",
        "    print(\"Job succeeded!\")\n",
        "else:\n",
        "    print(f\"Job failed: {batch_prediction_job.error}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "368f55b22883"
      },
      "source": [
        "### Check sample of results in BigQuery\n",
        "\n",
        "Once the batch prediction job has finished successfully, we can run the following cell to check a sample of our results."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "id": "e97f8b725576"
      },
      "outputs": [
        {
          "data": {
            "text/markdown": [
              "<b>Batch Prediction BigQuery Results Table"
            ],
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>videoId</th>\n",
              "      <th>videoURL</th>\n",
              "      <th>videoTitle</th>\n",
              "      <th>videoDescription</th>\n",
              "      <th>channelId</th>\n",
              "      <th>channelTitle</th>\n",
              "      <th>publishedAt</th>\n",
              "      <th>thumbnailURL</th>\n",
              "      <th>status</th>\n",
              "      <th>processed_time</th>\n",
              "      <th>request</th>\n",
              "      <th>response</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>0VjYP_73TkY</td>\n",
              "      <td>https://www.youtube.com/watch?v=0VjYP_73TkY</td>\n",
              "      <td>FULL INNING: Dodgers win Game 1 after Freeman ...</td>\n",
              "      <td>Freddie Freeman wins Game 1 for the Dodgers. S...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-26T04:02:23Z</td>\n",
              "      <td>https://i.ytimg.com/vi/0VjYP_73TkY/default.jpg</td>\n",
              "      <td></td>\n",
              "      <td>2024-12-10 10:49:03.802000+00:00</td>\n",
              "      <td>{\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...</td>\n",
              "      <td>{\"candidates\":[{\"avgLogprobs\":-0.0343309741422...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>cwZ8fI8T4Kc</td>\n",
              "      <td>https://www.youtube.com/watch?v=cwZ8fI8T4Kc</td>\n",
              "      <td>Royals vs. Yankees ALDS Game 1 Highlights (10/...</td>\n",
              "      <td>Royals vs. Yankees ALDS Game 1 full game highl...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-06T04:09:41Z</td>\n",
              "      <td>https://i.ytimg.com/vi/cwZ8fI8T4Kc/default.jpg</td>\n",
              "      <td></td>\n",
              "      <td>2024-12-10 10:50:31.271000+00:00</td>\n",
              "      <td>{\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...</td>\n",
              "      <td>{\"candidates\":[{\"avgLogprobs\":-0.0297037133877...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>-jYfC4YYXIw</td>\n",
              "      <td>https://www.youtube.com/watch?v=-jYfC4YYXIw</td>\n",
              "      <td>GREATEST GAME EVER?!? Shohei Ohtani goes 6-FOR...</td>\n",
              "      <td>Is this the greatest game in baseball history?...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-09-20T00:01:31Z</td>\n",
              "      <td>https://i.ytimg.com/vi/-jYfC4YYXIw/default.jpg</td>\n",
              "      <td></td>\n",
              "      <td>2024-12-10 10:50:03.239000+00:00</td>\n",
              "      <td>{\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...</td>\n",
              "      <td>{\"candidates\":[{\"avgLogprobs\":-0.0680019415698...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2ABGYpVo41k</td>\n",
              "      <td>https://www.youtube.com/watch?v=2ABGYpVo41k</td>\n",
              "      <td>FULL INNING: Dodgers TAKE THE LEAD for the fir...</td>\n",
              "      <td>The Los Angeles Dodgers take the lead vs. the ...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-31T03:37:04Z</td>\n",
              "      <td>https://i.ytimg.com/vi/2ABGYpVo41k/default.jpg</td>\n",
              "      <td></td>\n",
              "      <td>2024-12-10 10:51:08.335000+00:00</td>\n",
              "      <td>{\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...</td>\n",
              "      <td>{\"candidates\":[{\"avgLogprobs\":-0.0415774981180...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Jr5AiJ0K9Og</td>\n",
              "      <td>https://www.youtube.com/watch?v=Jr5AiJ0K9Og</td>\n",
              "      <td>Yankees vs. Guardians ALCS Game 4 Highlights (...</td>\n",
              "      <td>Yankees vs. Guardians ALCS Game 4 full game hi...</td>\n",
              "      <td>UCoLrcjPV5PbUrUyXq5mjc_A</td>\n",
              "      <td>MLB</td>\n",
              "      <td>2024-10-19T05:27:40Z</td>\n",
              "      <td>https://i.ytimg.com/vi/Jr5AiJ0K9Og/default.jpg</td>\n",
              "      <td></td>\n",
              "      <td>2024-12-10 10:49:03.594000+00:00</td>\n",
              "      <td>{\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...</td>\n",
              "      <td>{\"candidates\":[{\"avgLogprobs\":-0.0225860974827...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "       videoId                                     videoURL  \\\n",
              "0  0VjYP_73TkY  https://www.youtube.com/watch?v=0VjYP_73TkY   \n",
              "1  cwZ8fI8T4Kc  https://www.youtube.com/watch?v=cwZ8fI8T4Kc   \n",
              "2  -jYfC4YYXIw  https://www.youtube.com/watch?v=-jYfC4YYXIw   \n",
              "3  2ABGYpVo41k  https://www.youtube.com/watch?v=2ABGYpVo41k   \n",
              "4  Jr5AiJ0K9Og  https://www.youtube.com/watch?v=Jr5AiJ0K9Og   \n",
              "\n",
              "                                          videoTitle  \\\n",
              "0  FULL INNING: Dodgers win Game 1 after Freeman ...   \n",
              "1  Royals vs. Yankees ALDS Game 1 Highlights (10/...   \n",
              "2  GREATEST GAME EVER?!? Shohei Ohtani goes 6-FOR...   \n",
              "3  FULL INNING: Dodgers TAKE THE LEAD for the fir...   \n",
              "4  Yankees vs. Guardians ALCS Game 4 Highlights (...   \n",
              "\n",
              "                                    videoDescription  \\\n",
              "0  Freddie Freeman wins Game 1 for the Dodgers. S...   \n",
              "1  Royals vs. Yankees ALDS Game 1 full game highl...   \n",
              "2  Is this the greatest game in baseball history?...   \n",
              "3  The Los Angeles Dodgers take the lead vs. the ...   \n",
              "4  Yankees vs. Guardians ALCS Game 4 full game hi...   \n",
              "\n",
              "                  channelId channelTitle           publishedAt  \\\n",
              "0  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-26T04:02:23Z   \n",
              "1  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-06T04:09:41Z   \n",
              "2  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-09-20T00:01:31Z   \n",
              "3  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-31T03:37:04Z   \n",
              "4  UCoLrcjPV5PbUrUyXq5mjc_A          MLB  2024-10-19T05:27:40Z   \n",
              "\n",
              "                                     thumbnailURL status  \\\n",
              "0  https://i.ytimg.com/vi/0VjYP_73TkY/default.jpg          \n",
              "1  https://i.ytimg.com/vi/cwZ8fI8T4Kc/default.jpg          \n",
              "2  https://i.ytimg.com/vi/-jYfC4YYXIw/default.jpg          \n",
              "3  https://i.ytimg.com/vi/2ABGYpVo41k/default.jpg          \n",
              "4  https://i.ytimg.com/vi/Jr5AiJ0K9Og/default.jpg          \n",
              "\n",
              "                    processed_time  \\\n",
              "0 2024-12-10 10:49:03.802000+00:00   \n",
              "1 2024-12-10 10:50:31.271000+00:00   \n",
              "2 2024-12-10 10:50:03.239000+00:00   \n",
              "3 2024-12-10 10:51:08.335000+00:00   \n",
              "4 2024-12-10 10:49:03.594000+00:00   \n",
              "\n",
              "                                             request  \\\n",
              "0  {\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...   \n",
              "1  {\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...   \n",
              "2  {\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...   \n",
              "3  {\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...   \n",
              "4  {\"contents\":[{\"parts\":[{\"text\":\"Provide a 2-3 ...   \n",
              "\n",
              "                                            response  \n",
              "0  {\"candidates\":[{\"avgLogprobs\":-0.0343309741422...  \n",
              "1  {\"candidates\":[{\"avgLogprobs\":-0.0297037133877...  \n",
              "2  {\"candidates\":[{\"avgLogprobs\":-0.0680019415698...  \n",
              "3  {\"candidates\":[{\"avgLogprobs\":-0.0415774981180...  \n",
              "4  {\"candidates\":[{\"avgLogprobs\":-0.0225860974827...  "
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Pick sampling % and # of results for check of BQ results table - can leave\n",
        "# 100% & total # of results for big tables, likely sample down for larger ones\n",
        "\n",
        "# fmt: off\n",
        "sampling_percentage = 100  # @param {type:\"number\"}\n",
        "\n",
        "num_results = 50  # @param {type:\"integer\"}\n",
        "# fmt: on\n",
        "\n",
        "batch_prediction_results_sample_query = f\"\"\"\n",
        "    SELECT * \n",
        "    FROM `{BQ_DATASET}.{BATCH_PREDICTION_RESULTS_TABLE}`\n",
        "    TABLESAMPLE SYSTEM ({sampling_percentage} PERCENT)\n",
        "    LIMIT {num_results}\n",
        "    \"\"\"\n",
        "\n",
        "bq_results_table = get_bq_query_results_as_df(batch_prediction_results_sample_query)\n",
        "\n",
        "display(Markdown(\"<b>Batch Prediction BigQuery Results Table\"))\n",
        "\n",
        "display(bq_results_table.head())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ab055091c7e1"
      },
      "source": [
        "The results above should show new fields `status`, `processed_time`, and `response` that come from batch prediction with Gemini, with the latter being the one with the results we want to extract."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2f873f23e6f1"
      },
      "source": [
        "## Further analysis of Gemini video extraction results\n",
        "\n",
        "With our results from Gemini video extraction in BigQuery, we can pull out various pieces that might interest us. It's possible to do this further analysis in Python or directly in BigQuery - we'll choose the latter here since the results are already there, and [BigQuery's native JSON functionality](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions) provides convenient ways to pull out relevant outputs at scale."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8b971344d40f"
      },
      "source": [
        "### Extract summaries for each YouTube video"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "id": "f9463aae1352"
      },
      "outputs": [
        {
          "data": {
            "text/markdown": [
              "<b>Batch YouTube Video Analysis Summary Results</b>"
            ],
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>url</th>\n",
              "      <th>title</th>\n",
              "      <th>description</th>\n",
              "      <th>geminiSummary</th>\n",
              "      <th>publishedAt</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>https://www.youtube.com/watch?v=j3ykZoQMJLI</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 5 Highlights (10/30/24) | MLB Highlights</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 5 full game highlights from 10/30/24, presented by @evanwilliamsbourbon Don't forget ...</td>\n",
              "      <td>The Los Angeles Dodgers defeated the New York Yankees in game 5 of the 2024 World Series.  The Yankees had a 5-0 lead early in the game, but the Dodgers came back to win 7-6.  This win secured the World Series title for the Dodgers.</td>\n",
              "      <td>2024-10-31T05:24:46Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>https://www.youtube.com/watch?v=YezVM5dbSN0</td>\n",
              "      <td>THE DODGERS ARE 2024 WORLD CHAMPIONS! (FULL FINAL INNING OF THEIR CLINCH!)</td>\n",
              "      <td>The Dodgers' 5-run comeback is the largest ever in a #WorldSeries clinching victory. Don't forget to subscribe!</td>\n",
              "      <td>The Los Angeles Dodgers won the World Series in a 7-6 victory over the New York Yankees.  This win was notable for being the largest comeback in a clinching game in World Series history, overcoming a 5-0 deficit.  The video focuses on the performance of Walker Buehler, who pitched in relief despite limited rest.</td>\n",
              "      <td>2024-10-31T04:26:28Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>https://www.youtube.com/watch?v=2ABGYpVo41k</td>\n",
              "      <td>FULL INNING: Dodgers TAKE THE LEAD for the first time in the 8th inning of Game 5!</td>\n",
              "      <td>The Los Angeles Dodgers take the lead vs. the New York Yankees in Game 5 of the 2024 World Series. Don't forget to subscribe!</td>\n",
              "      <td>This video shows the end of game 5 of the World Series between the Los Angeles Dodgers and the New York Yankees. The Dodgers came back from being down 5-0 to win 7-6, and the video highlights the tension and excitement of the game.</td>\n",
              "      <td>2024-10-31T03:37:04Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>https://www.youtube.com/watch?v=9vZVDUjWerI</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 4 Highlights (10/29/24) | MLB Highlights</td>\n",
              "      <td>Dodgers vs. Yankees World Series Game 4 full game highlights from 10/29/24, presented by @evanwilliamsbourbon Don't forget ...</td>\n",
              "      <td>The Los Angeles Dodgers defeated the New York Yankees in game four of the 2024 World Series.  The Yankees had a strong performance, highlighted by a grand slam from Anthony Volpe, but ultimately fell short against the Dodgers' powerful offense.</td>\n",
              "      <td>2024-10-30T05:26:52Z</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>https://www.youtube.com/watch?v=GGzMqkjvq_Q</td>\n",
              "      <td>GRAND SLAM!! Anthony Volpe gives the Yankees THE LEAD in World Series Game 4!</td>\n",
              "      <td>Anthony Volpe hits a World Series grand slam! Don't forget to subscribe! https://www.youtube.com/mlb Follow us elsewhere too: ...</td>\n",
              "      <td>The video focuses on the 2024 World Series game between the Los Angeles Dodgers and the New York Yankees.  The commentary discusses the Yankees' offensive strategy and the Dodgers' bullpen performance.  A key moment is Anthony Volpe's grand slam home run, which gives the Yankees their first lead since game one.</td>\n",
              "      <td>2024-10-30T01:21:20Z</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                           url  \\\n",
              "0  https://www.youtube.com/watch?v=j3ykZoQMJLI   \n",
              "1  https://www.youtube.com/watch?v=YezVM5dbSN0   \n",
              "2  https://www.youtube.com/watch?v=2ABGYpVo41k   \n",
              "3  https://www.youtube.com/watch?v=9vZVDUjWerI   \n",
              "4  https://www.youtube.com/watch?v=GGzMqkjvq_Q   \n",
              "\n",
              "                                                                                title  \\\n",
              "0      Dodgers vs. Yankees World Series Game 5 Highlights (10/30/24) | MLB Highlights   \n",
              "1          THE DODGERS ARE 2024 WORLD CHAMPIONS! (FULL FINAL INNING OF THEIR CLINCH!)   \n",
              "2  FULL INNING: Dodgers TAKE THE LEAD for the first time in the 8th inning of Game 5!   \n",
              "3      Dodgers vs. Yankees World Series Game 4 Highlights (10/29/24) | MLB Highlights   \n",
              "4       GRAND SLAM!! Anthony Volpe gives the Yankees THE LEAD in World Series Game 4!   \n",
              "\n",
              "                                                                                                                         description  \\\n",
              "0     Dodgers vs. Yankees World Series Game 5 full game highlights from 10/30/24, presented by @evanwilliamsbourbon Don't forget ...   \n",
              "1                    The Dodgers' 5-run comeback is the largest ever in a #WorldSeries clinching victory. Don't forget to subscribe!   \n",
              "2      The Los Angeles Dodgers take the lead vs. the New York Yankees in Game 5 of the 2024 World Series. Don't forget to subscribe!   \n",
              "3     Dodgers vs. Yankees World Series Game 4 full game highlights from 10/29/24, presented by @evanwilliamsbourbon Don't forget ...   \n",
              "4  Anthony Volpe hits a World Series grand slam! Don't forget to subscribe! https://www.youtube.com/mlb Follow us elsewhere too: ...   \n",
              "\n",
              "                                                                                                                                                                                                                                                                                                               geminiSummary  \\\n",
              "0                                                                                   The Los Angeles Dodgers defeated the New York Yankees in game 5 of the 2024 World Series.  The Yankees had a 5-0 lead early in the game, but the Dodgers came back to win 7-6.  This win secured the World Series title for the Dodgers.   \n",
              "1  The Los Angeles Dodgers won the World Series in a 7-6 victory over the New York Yankees.  This win was notable for being the largest comeback in a clinching game in World Series history, overcoming a 5-0 deficit.  The video focuses on the performance of Walker Buehler, who pitched in relief despite limited rest.   \n",
              "2                                                                                    This video shows the end of game 5 of the World Series between the Los Angeles Dodgers and the New York Yankees. The Dodgers came back from being down 5-0 to win 7-6, and the video highlights the tension and excitement of the game.   \n",
              "3                                                                       The Los Angeles Dodgers defeated the New York Yankees in game four of the 2024 World Series.  The Yankees had a strong performance, highlighted by a grand slam from Anthony Volpe, but ultimately fell short against the Dodgers' powerful offense.   \n",
              "4   The video focuses on the 2024 World Series game between the Los Angeles Dodgers and the New York Yankees.  The commentary discusses the Yankees' offensive strategy and the Dodgers' bullpen performance.  A key moment is Anthony Volpe's grand slam home run, which gives the Yankees their first lead since game one.   \n",
              "\n",
              "            publishedAt  \n",
              "0  2024-10-31T05:24:46Z  \n",
              "1  2024-10-31T04:26:28Z  \n",
              "2  2024-10-31T03:37:04Z  \n",
              "3  2024-10-30T05:26:52Z  \n",
              "4  2024-10-30T01:21:20Z  "
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Query to extract summary for each video from JSON Gemini API response\n",
        "video_summaries_query = f\"\"\"\n",
        "    SELECT\n",
        "      videoUrl AS url,\n",
        "      videoTitle AS title,\n",
        "      videoDescription AS description,\n",
        "\n",
        "      JSON_EXTRACT_SCALAR(\n",
        "        JSON_EXTRACT_ARRAY(\n",
        "          JSON_VALUE(response, '$.candidates[0].content.parts[0].text')\n",
        "          )[OFFSET(0)],\n",
        "        '$.summary'\n",
        "        ) AS geminiSummary,\n",
        "\n",
        "      publishedAt\n",
        "\n",
        "    FROM\n",
        "      `{BQ_DATASET}.{BATCH_PREDICTION_RESULTS_TABLE}`\n",
        "\n",
        "    ORDER BY\n",
        "      publishedAt DESC\n",
        "    \"\"\"\n",
        "\n",
        "video_summaries = get_bq_query_results_as_df(video_summaries_query)\n",
        "\n",
        "# Change column width to be able to read all summary text for each row\n",
        "pd.set_option(\"display.max_colwidth\", 500)\n",
        "\n",
        "# Display results\n",
        "display(Markdown(\"<b>Batch YouTube Video Analysis Summary Results</b>\"))\n",
        "\n",
        "display(video_summaries.head())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "918294ae77c8"
      },
      "source": [
        "### Find most frequently appearing entities across videos\n",
        "In the final step of our process of going from unstructured videos to structured data results from analyzing all those videos, we'll use BigQuery to count up the number of references to each entity across videos, and return those that appear most frequently\n",
        "\n",
        "In the default case in this notebook, this counts the number of appearances for each athlete, manager/coach, or team in the specific videos we pulled from the MLB channel in the steps above, and shows the ones with the most appearances across those videos. Think of this as a list of \"who mattered most\" in Major League Baseball - at least based on content on the league's own YouTube channel - over the last year."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "id": "0597891dbd1f"
      },
      "outputs": [
        {
          "data": {
            "text/markdown": [
              "<b>Most Referenced Entities in Videos Analyzed</b>"
            ],
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>name</th>\n",
              "      <th>type</th>\n",
              "      <th>num_videos</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Los Angeles Dodgers</td>\n",
              "      <td>team</td>\n",
              "      <td>37</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Mookie Betts</td>\n",
              "      <td>athlete</td>\n",
              "      <td>31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Shohei Ohtani</td>\n",
              "      <td>athlete</td>\n",
              "      <td>28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Caleb Ferguson</td>\n",
              "      <td>athlete</td>\n",
              "      <td>26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>New York Yankees</td>\n",
              "      <td>team</td>\n",
              "      <td>26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>Alex Vesia</td>\n",
              "      <td>athlete</td>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>Evan Phillips</td>\n",
              "      <td>athlete</td>\n",
              "      <td>24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>Brusdar Graterol</td>\n",
              "      <td>athlete</td>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>Will Smith</td>\n",
              "      <td>athlete</td>\n",
              "      <td>22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>Aaron Judge</td>\n",
              "      <td>athlete</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>Dave Roberts</td>\n",
              "      <td>manager or coach</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>Ryan Brasier</td>\n",
              "      <td>athlete</td>\n",
              "      <td>20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>Caleb Homesley</td>\n",
              "      <td>athlete</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>Chris Martin</td>\n",
              "      <td>athlete</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>Freddie Freeman</td>\n",
              "      <td>athlete</td>\n",
              "      <td>19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>Chris Taylor</td>\n",
              "      <td>athlete</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>Max Muncy</td>\n",
              "      <td>athlete</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>Tommy Edman</td>\n",
              "      <td>athlete</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>Anthony Volpe</td>\n",
              "      <td>athlete</td>\n",
              "      <td>12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>Giancarlo Stanton</td>\n",
              "      <td>athlete</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>Aaron Boone</td>\n",
              "      <td>manager or coach</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>San Diego Padres</td>\n",
              "      <td>team</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>Cleveland Guardians</td>\n",
              "      <td>team</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>Gleyber Torres</td>\n",
              "      <td>athlete</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>Austin Wells</td>\n",
              "      <td>athlete</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                   name              type  num_videos\n",
              "0   Los Angeles Dodgers              team          37\n",
              "1          Mookie Betts           athlete          31\n",
              "2         Shohei Ohtani           athlete          28\n",
              "3        Caleb Ferguson           athlete          26\n",
              "4      New York Yankees              team          26\n",
              "5            Alex Vesia           athlete          24\n",
              "6         Evan Phillips           athlete          24\n",
              "7      Brusdar Graterol           athlete          22\n",
              "8            Will Smith           athlete          22\n",
              "9           Aaron Judge           athlete          20\n",
              "10         Dave Roberts  manager or coach          20\n",
              "11         Ryan Brasier           athlete          20\n",
              "12       Caleb Homesley           athlete          19\n",
              "13         Chris Martin           athlete          19\n",
              "14      Freddie Freeman           athlete          19\n",
              "15         Chris Taylor           athlete          17\n",
              "16            Max Muncy           athlete          17\n",
              "17          Tommy Edman           athlete          17\n",
              "18        Anthony Volpe           athlete          12\n",
              "19    Giancarlo Stanton           athlete          11\n",
              "20          Aaron Boone  manager or coach          10\n",
              "21     San Diego Padres              team          10\n",
              "22  Cleveland Guardians              team           9\n",
              "23       Gleyber Torres           athlete           9\n",
              "24         Austin Wells           athlete           8"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Query to extract entity references from Gemini results, count most frequently appearing\n",
        "most_referenced_entities_query = \"\"\"\n",
        "    WITH\n",
        "    ExtractedText AS\n",
        "    (\n",
        "      SELECT\n",
        "        *,\n",
        "        JSON_EXTRACT_ARRAY(JSON_VALUE(response, '$.candidates[0].content.parts[0].text'))[OFFSET(0)]\n",
        "          AS extracted_text\n",
        "\n",
        "      FROM\n",
        "        `youtube_video_analysis.video_analysis_batch_results`\n",
        "    ),\n",
        "\n",
        "    ExtractedRows AS\n",
        "    (\n",
        "      SELECT\n",
        "        ARRAY(\n",
        "          SELECT AS STRUCT \n",
        "            JSON_EXTRACT_SCALAR(references, '$.entity_name') AS entity_name,\n",
        "            JSON_EXTRACT_SCALAR(references, '$.entity_type') AS entity_type\n",
        "\n",
        "          FROM \n",
        "            UNNEST(JSON_EXTRACT_ARRAY(extracted_text, '$.references')) AS references\n",
        "          ) AS reference,\n",
        "\n",
        "      FROM\n",
        "        ExtractedText\n",
        "    )\n",
        "\n",
        "    SELECT\n",
        "      References.entity_name AS name,\n",
        "      LOWER(References.entity_type) AS type,\n",
        "      COUNT(*) AS num_videos\n",
        "\n",
        "    FROM\n",
        "      ExtractedRows,\n",
        "      UNNEST(ExtractedRows.reference) AS References\n",
        "\n",
        "    GROUP BY\n",
        "      entity_name, entity_type\n",
        "\n",
        "    ORDER BY\n",
        "      num_videos DESC,\n",
        "      name\n",
        "    \"\"\"\n",
        "\n",
        "most_referenced_entities = get_bq_query_results_as_df(most_referenced_entities_query)\n",
        "\n",
        "# Display results\n",
        "display(Markdown(\"<b>Most Referenced Entities in Videos Analyzed</b>\"))\n",
        "\n",
        "display(most_referenced_entities.head(25))"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "video_analysis_with_youtube_data_api_and_batch_prediction.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
