{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hztiLWXYHkhZ"
      },
      "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": "6tZ0mZ5Hg8_M"
      },
      "source": [
        "# Evaluating prompts at scale with Gemini Batch Prediction API\n",
        " <table align=\"left\">\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/generative-ai/blob/main/gemini/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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%2Fevaluation%2Fevaltask_approach%2Fevaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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/evaluation/evaltask_approach/evaluating_prompts_at_scale_with_gemini_batch_prediction_api.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>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "T90FYh6-htfF"
      },
      "source": [
        "| | |\n",
        "|-|-|\n",
        "|Author(s) | [Ariel Jassan](https://github.com/arieljassan) |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wKSYzhrTOCBG"
      },
      "source": [
        "## Introduction\n",
        "\n",
        "This tutorial guides you through the process of evaluating the effectiveness of your prompts at scale using the Gemini Batch Prediction API via Vertex AI. Even though in this tutorial we will do image classification, it can be extended to other cases as well. One of the benefits of using the Gemini Batch Prediction API is that you can evaluate your prompts and setup in Gemini using hundreds of examples with one single request.\n",
        "\n",
        "You can find more information about the Gemini Batch Prediction API [here](https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/batch-prediction-api).\n",
        "\n",
        "For the purpose of this tutorial, we will execute a prompt to classify images into classes of sports. The data is based on an excerpt of the dataset that can be found in https://www.kaggle.com/datasets/gpiosenka/sports-classification.\n",
        "\n",
        "\n",
        "## Steps\n",
        "\n",
        "1. **Prepare the data in BigQuery and GCS**\n",
        "    * Upload sample images to Google Cloud Storage and create ground truth table in BigQuery.\n",
        "    \n",
        "2. **Run Gemini Batch Prediction API**\n",
        "    * Send prompts to Gemini for batch prediction and get results in BigQuery.\n",
        "\n",
        "3. **Analyze results in BigQuery and Looker Studio**\n",
        "    * Present findings, focusing on prompt/dataset strengths and weaknesses."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cQX5j8RYJEXs"
      },
      "source": [
        "## Getting started"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uOgj6IyiJVkV"
      },
      "source": [
        "### Install dependencies"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3sCnE9GIUkts"
      },
      "outputs": [],
      "source": [
        "%pip install --upgrade -q google-cloud-aiplatform google-cloud-bigquery bigframes pandas pandas-gbq"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f9nGCMsxJZ_v"
      },
      "source": [
        "### Restart Colab"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "LIWOo23oVGmV"
      },
      "outputs": [],
      "source": [
        "# You will see a notification of Colab crashing. It is the expected behavior.\n",
        "import IPython\n",
        "\n",
        "app = IPython.Application.instance()\n",
        "app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o_Z7Y63KJelC"
      },
      "source": [
        "### Authenticate your notebook environment (Colab only)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UV7jgeu9VImB"
      },
      "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": "m-FTvlzlJ8lY"
      },
      "source": [
        "### Define constants"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "HDVVYzTISvGv"
      },
      "outputs": [],
      "source": [
        "PROJECT_ID = \"your-project-id\"\n",
        "LOCATION = \"us-central1\"\n",
        "\n",
        "# Generative model.\n",
        "MODEL_ID = \"gemini-2.0-flash\"\n",
        "\n",
        "# BigQuery tables.\n",
        "BQ_DATASET_ID = \"gemini_batch_predictions\"\n",
        "BQ_DATASET = f\"{PROJECT_ID}.{BQ_DATASET_ID}\"\n",
        "FILES_TABLE = f\"{BQ_DATASET_ID}.sports_files\"\n",
        "PROMPTS_TABLE = f\"{BQ_DATASET}.temp_prompts\"\n",
        "TEXT_GENERATION_TABLE_PREFIX = f\"{BQ_DATASET}.results\"\n",
        "\n",
        "# BigQuery views.\n",
        "RESULTS_VIEW = f\"{BQ_DATASET}.extraction_results\"\n",
        "EVALUATION_VIEW = f\"{BQ_DATASET}.evaluation\"\n",
        "\n",
        "# File containing ground truth data in GCS.\n",
        "BUCKET_NAME = \"github-repo\"\n",
        "FOLDER = \"generative-ai/gemini/evaluation/sports_files\"\n",
        "GCS_PREFIX = f\"gs://{BUCKET_NAME}/{FOLDER}\"\n",
        "SPORTS_FILE = \"sports_files.csv\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WePqYxwP4L6y"
      },
      "source": [
        "### Import libraries and initialize clients"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "mUxxhafP4KM9"
      },
      "outputs": [],
      "source": [
        "import datetime\n",
        "import json\n",
        "import time\n",
        "\n",
        "import bigframes.pandas as bpd\n",
        "from google.cloud import bigquery, storage\n",
        "import pandas as pd\n",
        "import vertexai\n",
        "from vertexai.generative_models import GenerationConfig, GenerativeModel, Part\n",
        "from vertexai.preview.batch_prediction import BatchPredictionJob"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fb983d347169"
      },
      "outputs": [],
      "source": [
        "# BigQuery client.\n",
        "bq_client = bigquery.Client(project=PROJECT_ID)\n",
        "\n",
        "# Google Cloud Storage client.\n",
        "storage_client = storage.Client()\n",
        "\n",
        "# Initialize Vertex AI SDK.\n",
        "vertexai.init(project=PROJECT_ID, location=LOCATION)\n",
        "\n",
        "# Set BigQuery Pandas options.\n",
        "bpd.options.bigquery.project = PROJECT_ID\n",
        "bpd.options.bigquery.location = LOCATION"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VwhKPE19djbK"
      },
      "source": [
        "## Data preparation\n",
        "\n",
        "In this section we will create the dataset in BigQuery, load the table with ground truth, and create the views that will serve for analysis of the results from Gemini and reporting in Looker Studio."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zOKg8sxIZnzm"
      },
      "source": [
        "### Create BigQuery dataset and load table with ground truth"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZjWGl6V9ZvhV"
      },
      "outputs": [],
      "source": [
        "def create_dataset(dataset_id: str, location: str) -> None:\n",
        "    \"\"\"Creates a BigQuery dataset in a location.\"\"\"\n",
        "    dataset = bigquery.Dataset(dataset_id)\n",
        "    dataset.location = location\n",
        "\n",
        "    dataset = bq_client.create_dataset(dataset, timeout=30)\n",
        "    print(f\"Created dataset {bq_client.project}.{dataset.dataset_id}\")\n",
        "\n",
        "\n",
        "def load_files_table_from_uri(files_table: str, uri: str) -> None:\n",
        "    \"\"\"Load ground truth into a BigQuery table from a GCS URI.\"\"\"\n",
        "    job_config = bigquery.LoadJobConfig(\n",
        "        schema=[\n",
        "            bigquery.SchemaField(\"path\", \"STRING\"),\n",
        "            bigquery.SchemaField(\"label\", \"STRING\"),\n",
        "        ],\n",
        "        skip_leading_rows=1,\n",
        "        source_format=bigquery.SourceFormat.CSV,\n",
        "        write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,\n",
        "    )\n",
        "    load_job = bq_client.load_table_from_uri(uri, files_table, job_config=job_config)\n",
        "    load_job.result()\n",
        "\n",
        "    destination_table = bq_client.get_table(files_table)\n",
        "    print(f\"Loaded {destination_table.num_rows} rows.\")\n",
        "\n",
        "\n",
        "create_dataset(dataset_id=BQ_DATASET, location=LOCATION)\n",
        "load_files_table_from_uri(files_table=FILES_TABLE, uri=f\"{GCS_PREFIX}/{SPORTS_FILE}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OW4EngZRLlts"
      },
      "source": [
        "### Test image URIs are retrieved from BigQuery"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tRgVfFZIN7ae"
      },
      "outputs": [],
      "source": [
        "ground_truth_df = bpd.read_gbq(FILES_TABLE)\n",
        "ground_truth_df[\"path\"][:2]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xn2t64pkbQHq"
      },
      "source": [
        "## Define prompt and execute it via Vertex AI Gemini Batch Prediction API"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xbq75NqxRXBO"
      },
      "source": [
        "### Define the prompt"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "id": "bk-EpTp0uJR4"
      },
      "outputs": [],
      "source": [
        "prompt = \"\"\"\\\n",
        "- Classify the sport from the image below in one of the following categories:\n",
        "* baseball\n",
        "* basketball\n",
        "* tennis\n",
        "* volleyball\n",
        "\n",
        "- Provide an answer in JSON format.\n",
        "\n",
        "Example response:\n",
        "{\"sport\": \"baseball\"}\n",
        "\n",
        "- Image:\n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XCGQhmmSRbgc"
      },
      "source": [
        "### Classify one image using the Python SDK"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Kchsx7bdrGgf"
      },
      "outputs": [],
      "source": [
        "def classify_image(model_id: str, prompt: str, gcs_prefix: str, blob_name: str) -> str:\n",
        "    \"\"\"Classifies an image.\"\"\"\n",
        "    model = GenerativeModel(\n",
        "        model_id,\n",
        "        generation_config=GenerationConfig(response_mime_type=\"application/json\"),\n",
        "    )\n",
        "    image_content = Part.from_uri(\n",
        "        uri=f\"{gcs_prefix}/{blob_name}\", mime_type=\"image/jpeg\"\n",
        "    )\n",
        "    contents = [prompt, image_content]\n",
        "    return model.generate_content(contents).text\n",
        "\n",
        "\n",
        "blob_name = ground_truth_df.iloc[0][\"path\"]\n",
        "response = classify_image(\n",
        "    model_id=MODEL_ID,\n",
        "    prompt=prompt,\n",
        "    gcs_prefix=GCS_PREFIX,\n",
        "    blob_name=blob_name,\n",
        ")\n",
        "print(f\"blob_name: {blob_name}\")\n",
        "print(f\"response: {response}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q79hx4JINXZh"
      },
      "source": [
        "### Create a BigQuery table applying the prompt to each of the images\n",
        "\n",
        "In this section, an `evaluation_id` variable is created to identify the execution run."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_Dn4SStFOBak"
      },
      "outputs": [],
      "source": [
        "# Use current time as identifier of the evaluation.\n",
        "now = datetime.datetime.now()\n",
        "evaluation_ts = str(now)\n",
        "evaluation_id = f\"{now.year}_{now.month}_{now.day}_{now.hour}_{now.minute}\"\n",
        "json_file_name = f\"/tmp/{evaluation_id}.json\"\n",
        "\n",
        "# Get URIs of the images from the ground truth table in BigQuery.\n",
        "ground_truth_df = bpd.read_gbq(FILES_TABLE)\n",
        "\n",
        "prompts_df = pd.DataFrame(\n",
        "    [\n",
        "        {\n",
        "            \"evaluation_ts\": evaluation_ts,\n",
        "            \"evaluation_id\": evaluation_id,\n",
        "            \"prompt_text\": prompt,\n",
        "            \"gcs_uri\": image_uri,\n",
        "            \"request\": json.dumps(\n",
        "                {\n",
        "                    \"contents\": [\n",
        "                        {\n",
        "                            \"role\": \"user\",\n",
        "                            \"parts\": [\n",
        "                                {\"text\": prompt},\n",
        "                                {\n",
        "                                    \"fileData\": {\n",
        "                                        \"mimeType\": \"image/jpeg\",\n",
        "                                        \"fileUri\": f\"{GCS_PREFIX}/{image_uri}\",\n",
        "                                    }\n",
        "                                },\n",
        "                            ],\n",
        "                        }\n",
        "                    ],\n",
        "                    \"generationConfig\": {\"responseMimeType\": \"application/json\"},\n",
        "                }\n",
        "            ),\n",
        "        }\n",
        "        for image_uri in ground_truth_df[\"path\"].values\n",
        "    ]\n",
        ")\n",
        "\n",
        "# Save JSONL file\n",
        "prompts_df.to_json(json_file_name, lines=True)\n",
        "\n",
        "# Upload to BQ\n",
        "prompts_df.to_gbq(PROMPTS_TABLE, PROJECT_ID)\n",
        "\n",
        "table = bq_client.get_table(PROMPTS_TABLE)\n",
        "print(\n",
        "    f\"Loaded {table.num_rows} rows and {len(table.schema)} columns to \"\n",
        "    f\"{PROMPTS_TABLE}\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LveMID56Ngo6"
      },
      "source": [
        "### Launch a Gemini Batch Prediction request"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qAe5v1N1TOw1"
      },
      "outputs": [],
      "source": [
        "# Define table to store results from Gemini Batch Prediction.\n",
        "text_generation_table = f\"{TEXT_GENERATION_TABLE_PREFIX}_{evaluation_id}\"\n",
        "\n",
        "# Create batch prediction job.\n",
        "batch_job = BatchPredictionJob.submit(\n",
        "    source_model=MODEL_ID,\n",
        "    input_dataset=f\"bq://{PROMPTS_TABLE}\",\n",
        "    output_uri_prefix=f\"bq://{text_generation_table}\",\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "acbe70fd175d"
      },
      "source": [
        "To check the status of the job, run this cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "06982be54458"
      },
      "outputs": [],
      "source": [
        "# Refresh the job until complete\n",
        "while not batch_job.has_ended:\n",
        "    time.sleep(10)\n",
        "    batch_job.refresh()\n",
        "\n",
        "# Check if the job succeeds\n",
        "if batch_job.has_succeeded:\n",
        "    print(\"Job succeeded!\")\n",
        "else:\n",
        "    print(f\"Job failed: {batch_job.error}\")\n",
        "\n",
        "# Check the location of the output\n",
        "print(f\"Job output location: {batch_job.output_location}\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "QTl8-Cbg7Ll5"
      },
      "source": [
        "### List sample of text generation results from BigQuery"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Y0jHGtZEUK88"
      },
      "outputs": [],
      "source": [
        "text_generation_df = bpd.read_gbq(text_generation_table)\n",
        "for row in text_generation_df[\"response\"][:5]:\n",
        "    print(json.loads(row)[\"candidates\"][0][\"content\"][\"parts\"][0][\"text\"])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q96WpoTw4Kr6"
      },
      "source": [
        "## Create Views in BigQuery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_hVJ_n3P6AF4"
      },
      "source": [
        "### Create view of text generation results\n",
        "\n",
        "Run this only once to create the view"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JbT4qLbTQ7iD"
      },
      "outputs": [],
      "source": [
        "def create_text_generation_view(\n",
        "    text_generation_table_prefix: str, results_view: str\n",
        ") -> None:\n",
        "    \"\"\"Creates a view of text extraction results.\"\"\"\n",
        "\n",
        "    view = bigquery.Table(results_view)\n",
        "\n",
        "    view.view_query = rf\"\"\"\n",
        "    SELECT\n",
        "        evaluation_id,\n",
        "        evaluation_ts,\n",
        "        prompt_text,\n",
        "        gcs_uri,\n",
        "        JSON_VALUE(JSON_VALUE(response, '$.candidates[0].content.parts[0].text'), \"$.sport\") AS label\n",
        "    FROM `{text_generation_table_prefix}_*`\n",
        "    \"\"\"\n",
        "\n",
        "    # Make an API request to create the view.\n",
        "    view = bq_client.create_table(view, exists_ok=False)\n",
        "    print(f\"Created {view.table_type}: {str(view.reference)}\")\n",
        "\n",
        "\n",
        "create_text_generation_view(\n",
        "    text_generation_table_prefix=TEXT_GENERATION_TABLE_PREFIX, results_view=RESULTS_VIEW\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VcyIaLMC62rs"
      },
      "source": [
        "### Create view of experiment evaluation\n",
        "\n",
        "Run this only once to create the view."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lcZrWnI13H4F"
      },
      "outputs": [],
      "source": [
        "def create_evaluation_view(\n",
        "    evaluation_view: str, files_table: str, results_view: str\n",
        ") -> None:\n",
        "    \"\"\"Creates a view of experiment evaluation.\"\"\"\n",
        "\n",
        "    view = bigquery.Table(evaluation_view)\n",
        "\n",
        "    view.view_query = f\"\"\"\n",
        "      WITH t1 AS (\n",
        "        SELECT\n",
        "          e.evaluation_id,\n",
        "          e.evaluation_ts,\n",
        "          e.prompt_text,\n",
        "          f.path,\n",
        "          f.label,\n",
        "          e.gcs_uri,\n",
        "          f.label = e.label AS correct\n",
        "        FROM `{files_table}` f\n",
        "        JOIN `{results_view}` e\n",
        "          ON f.path = e.gcs_uri\n",
        "      )\n",
        "\n",
        "      SELECT\n",
        "        evaluation_id,\n",
        "        evaluation_ts,\n",
        "        prompt_text,\n",
        "        path,\n",
        "        label,\n",
        "        correct\n",
        "      FROM t1\"\"\"\n",
        "\n",
        "    # Make an API request to create the view.\n",
        "    view = bq_client.create_table(view, exists_ok=False)\n",
        "    print(f\"Created {view.table_type}: {str(view.reference)}\")\n",
        "\n",
        "\n",
        "create_evaluation_view(\n",
        "    evaluation_view=EVALUATION_VIEW, files_table=FILES_TABLE, results_view=RESULTS_VIEW\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "01saqXPQbuIS"
      },
      "source": [
        "## Analyze results in BigQuery and Looker Studio"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VJvttnjyZxIs"
      },
      "source": [
        "### Copy a Looker Studio dashboard to analyze results\n",
        "\n",
        "1. Make a copy of this [Looker Studio dashboard](https://lookerstudio.google.com/reporting/caba1b62-2820-467a-bbe7-bd852d538de8/preview)\n",
        "1. Connect dashboard to your view"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "evaluating_prompts_at_scale_with_gemini_batch_prediction_api.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
