{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gNz_7idNEdlE"
      },
      "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": "IXbw-R0ZGiWf"
      },
      "source": [
        "# Analyzing movie posters in BigQuery with Gemini\n",
        "\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/use-cases/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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%2Fapplying-llms-to-data%2Fanalyze-poster-images-in-bigquery%2Fposter_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.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": "4qTZ1THsriwh"
      },
      "source": [
        "| Author |\n",
        "| --- |\n",
        "| [Alicia Williams](https://github.com/aliciawilliams) |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_cGtn8TvG7SB"
      },
      "source": [
        "## Overview\n",
        "\n",
        "The objective is to demonstrate how to use the Gemini models for analysis in BigQuery, including:\n",
        "\n",
        "\n",
        "*   Completing setup steps for accessing Vertex AI (including Gemini models) from BigQuery\n",
        "*   Creating an Object Table for the unstructured dataset being analyzed\n",
        "*   Creating the remote model in BigQuery for Gemini\n",
        "*   Performing analysis over the object table using the Gemini model\n",
        "*   Joining analysis results back to structured table for deeper insights\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sf3ioTDBzuHR"
      },
      "source": [
        "## About the dataset\n",
        "\n",
        "The dataset of movie poster images used in this demo are stored in a public Google Cloud Storage bucket: [`gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters`](https://console.cloud.google.com/storage/browser/cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters)\n",
        "\n",
        "![Movie Posters](https://storage.googleapis.com/github-repo/generative-ai/gemini/use-cases/applying-llms-to-data/movie-posters.jpeg)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "E8sI7ir1fVgI"
      },
      "source": [
        "## Services and Costs\n",
        "\n",
        "This tutorial uses the following Google Cloud data analytics and ML services, they are billable components of Google Cloud:\n",
        "\n",
        "* BigQuery & BigQuery ML [(pricing)](https://cloud.google.com/bigquery/pricing)\n",
        "* Vertex AI API [(pricing)](https://cloud.google.com/vertex-ai/pricing)\n",
        "\n",
        "Use the [Pricing Calculator](https://cloud.google.com/products/calculator/) to generate a cost estimate based on your projected usage.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P10XEmXlzeOF"
      },
      "source": [
        "# Setup steps for accessing Vertex AI models from BigQuery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6xxnNycUFs8Z"
      },
      "source": [
        "## Enable the Vertex AI and BigQuery Connection APIs"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "82RF-HLRFhVn"
      },
      "outputs": [],
      "source": [
        "!gcloud services enable aiplatform.googleapis.com bigqueryconnection.googleapis.com"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DyOBjt1yFuKA"
      },
      "source": [
        "## Create a Cloud resource connection"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "SoFIvJiFFxd-"
      },
      "outputs": [],
      "source": [
        "!bq mk --connection --location=us \\\n",
        "    --connection_type=CLOUD_RESOURCE gemini_conn"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oo3okAPNF7QD"
      },
      "source": [
        "## Grant the \"Vertex AI User\" role to the service account used by the Cloud resource connection\n",
        "*Note: This demo analyzes a dataset that sits in a public Cloud Storage bucket. When analyzing a dataset in a non-public bucket, the service account will also need \"Storage Object Viewer\" role on that storage bucket.*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "KG5mQbFKF4VD"
      },
      "outputs": [],
      "source": [
        "SERVICE_ACCT = !bq show --format=prettyjson --connection us.gemini_conn | grep \"serviceAccountId\" | cut -d '\"' -f 4\n",
        "SERVICE_ACCT_EMAIL = SERVICE_ACCT[-1]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "id": "3EM-9XxtGDBq"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "\n",
        "PROJECT_ID = os.environ[\"GOOGLE_CLOUD_PROJECT\"]\n",
        "!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role=roles/aiplatform.user"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0TU6VxJA9bRI"
      },
      "source": [
        "# Create an object table for the movie poster images"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fm840uHo4kHP"
      },
      "source": [
        "## Create a new dataset named `'gemini_demo'`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "metadata": {
        "id": "fdSiqoR04jeQ"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE SCHEMA\n",
        "  `gemini_demo` OPTIONS (location = 'US');"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PaLtG8c3GZFi"
      },
      "source": [
        "## Create an object table referencing Google Cloud Storage bucket"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {
        "id": "T97do1tjGZsm"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE EXTERNAL TABLE\n",
        "  `gemini_demo.movie_posters`\n",
        "WITH CONNECTION `us.gemini_conn`\n",
        "OPTIONS (\n",
        "  object_metadata = 'SIMPLE',\n",
        "  uris = ['gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/*']\n",
        "  );"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xCbjG3fb3def"
      },
      "source": [
        "# Create the remote model in BigQuery ML"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "diwD_Bv0F_Sa"
      },
      "source": [
        "## Create the remote model for Gemini in BigQuery ML"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 43,
      "metadata": {
        "id": "KxAuo3YyGHp2"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE MODEL `gemini_demo.gemini_2_0_flash`\n",
        "REMOTE WITH CONNECTION `us.gemini_conn`\n",
        "OPTIONS (endpoint = 'gemini-2.0-flash')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JcYVG4qNKAuY"
      },
      "source": [
        "# Perform analysis on the movie poster images"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KCZ-nme8GjTN"
      },
      "source": [
        "## Prompt Gemini to analyze the movie poster image object table"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 44,
      "metadata": {
        "id": "1VPgFMlBGi_5"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE TABLE\n",
        "  `gemini_demo.movie_posters_results` AS (\n",
        "  SELECT\n",
        "    uri,\n",
        "    REGEXP_EXTRACT(ml_generate_text_llm_result, r'```json\\n([\\s\\S]*?)\\n```') AS ml_generate_text_llm_result\n",
        "  FROM\n",
        "    ML.GENERATE_TEXT( MODEL `gemini_demo.gemini_2_0_flash`,\n",
        "      TABLE `gemini_demo.movie_posters`,\n",
        "      STRUCT( 0.2 AS temperature,\n",
        "        'For the movie represented by this poster, what is the movie title and year of release? Answer in JSON format with two keys: title, year. title should be string, year should be integer.' AS PROMPT,\n",
        "        TRUE AS FLATTEN_JSON_OUTPUT)));"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 45,
      "metadata": {
        "id": "8bcyGsNWHPeZ"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "SELECT uri FROM `gemini_demo.movie_posters_results`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ceUN_Mgm0vQa"
      },
      "source": [
        "## Format model responses into new structured columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "metadata": {
        "id": "Gi1WMtnp1Tvh"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE TABLE\n",
        "  `gemini_demo.movie_posters_results_formatted` AS (\n",
        "  SELECT\n",
        "    uri,\n",
        "    JSON_VALUE(ml_generate_text_llm_result, \"$.title\") AS title,\n",
        "    JSON_VALUE(ml_generate_text_llm_result, \"$.year\") AS year\n",
        "  FROM\n",
        "    `gemini_demo.movie_posters_results` results )"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 47,
      "metadata": {
        "id": "frkWQTU2HF9i"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "SELECT * FROM `gemini_demo.movie_posters_results_formatted`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "M4swkWSH08yE"
      },
      "source": [
        "## Prompt Gemini to provide movie summaries for each movie poster image"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "metadata": {
        "id": "Znj6z0nstGBy"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "SELECT\n",
        "  uri,\n",
        "  title,\n",
        "  year,\n",
        "  prompt,\n",
        "  ml_generate_text_llm_result\n",
        "FROM\n",
        "  ML.GENERATE_TEXT( MODEL `gemini_demo.gemini_2_0_flash`,\n",
        "    (\n",
        "    SELECT\n",
        "      CONCAT('Provide a short summary of movie titled ',title, ' from the year ',year,'.') AS prompt,\n",
        "      uri,\n",
        "      title,\n",
        "      year\n",
        "    FROM\n",
        "      `gemini_demo.movie_posters_results_formatted`\n",
        "    LIMIT\n",
        "      10 ),\n",
        "    STRUCT(0.2 AS temperature,\n",
        "      TRUE AS FLATTEN_JSON_OUTPUT));"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Vj7gGTPy2qpQ"
      },
      "source": [
        "# Join analysis results with structured data for deeper insights\n",
        "Using text embeddings to perform similarity joins of movie poster images to a movie reviews dataset in BigQuery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RaMqFT5TzpNO"
      },
      "source": [
        "## Create remote model for generating text embeddings"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 49,
      "metadata": {
        "id": "0bLIpD71z5x_"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE MODEL `gemini_demo.text_embedding`\n",
        "REMOTE WITH CONNECTION `us.gemini_conn`\n",
        "OPTIONS (endpoint = 'text-multilingual-embedding-002')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "woZDvRuV11IQ"
      },
      "source": [
        "## Generate text embeddings for title and year associated with movie posters"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 50,
      "metadata": {
        "id": "lHdoqp40z7Yo"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE TABLE\n",
        "  `gemini_demo.movie_posters_results_embeddings` AS (\n",
        "  SELECT\n",
        "    *\n",
        "  FROM\n",
        "    ML.GENERATE_EMBEDDING(\n",
        "      MODEL `gemini_demo.text_embedding`,\n",
        "      (\n",
        "      SELECT\n",
        "        CONCAT('The movie titled ', title, ' from the year ', year,'.') AS content,\n",
        "        title,\n",
        "        year,\n",
        "        uri\n",
        "      FROM\n",
        "        `gemini_demo.movie_posters_results_formatted` ),\n",
        "      STRUCT(TRUE AS flatten_json_output)));"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 51,
      "metadata": {
        "id": "0PDZT8T52B95"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "SELECT * FROM `gemini_demo.movie_posters_results_embeddings`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7UUZPBpuGHPO"
      },
      "source": [
        "## Generate text embeddings for subset of IMDB dataset"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 52,
      "metadata": {
        "id": "4Jh7AJ2pIZuB"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "# Create table containing movie_id, title, and year for movies in IMDB reviews dataset that were release prior to 1935\n",
        "CREATE OR REPLACE VIEW\n",
        "  `gemini_demo.imdb_movies` AS (\n",
        "  WITH\n",
        "    reviews AS (\n",
        "      SELECT\n",
        "        reviews.movie_id AS movie_id,\n",
        "        title.primary_title AS title,\n",
        "        title.start_year AS year,\n",
        "        reviews.review AS review\n",
        "      FROM\n",
        "        `bigquery-public-data.imdb.reviews` reviews\n",
        "      LEFT JOIN\n",
        "        `bigquery-public-data.imdb.title_basics` title\n",
        "      ON\n",
        "        reviews.movie_id = title.tconst)\n",
        "  SELECT\n",
        "    DISTINCT(movie_id),\n",
        "    title,\n",
        "    year\n",
        "  FROM\n",
        "    reviews\n",
        "  WHERE\n",
        "    year < 1935)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 53,
      "metadata": {
        "id": "QKAfC0NfGKc3"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "CREATE OR REPLACE TABLE\n",
        "  `gemini_demo.imdb_movies_embeddings` AS (\n",
        "  SELECT\n",
        "    *\n",
        "  FROM\n",
        "    ML.GENERATE_EMBEDDING( MODEL `gemini_demo.text_embedding`,\n",
        "      (\n",
        "      SELECT\n",
        "        CONCAT('The movie titled ', title, ' from the year ', year,'.') AS content,\n",
        "        title,\n",
        "        year,\n",
        "        movie_id\n",
        "      FROM\n",
        "        `gemini_demo.imdb_movies` ),\n",
        "      STRUCT(TRUE AS flatten_json_output) )\n",
        "  WHERE\n",
        "    ml_generate_embedding_status = '' );"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1SZ1OmVc2IN5"
      },
      "source": [
        "## Match movie poster images to IMDB `movie_id` using BigQuery `VECTOR_SEARCH`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 54,
      "metadata": {
        "id": "N4pBoQPP2Y3k"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "SELECT\n",
        "  query.uri AS poster_uri,\n",
        "  query.title AS poster_title,\n",
        "  query.year AS poster_year,\n",
        "  base.title AS imdb_title,\n",
        "  base.year AS imdb_year,\n",
        "  base.movie_id AS imdb_movie_id,\n",
        "  distance\n",
        "FROM\n",
        "  VECTOR_SEARCH( TABLE `gemini_demo.imdb_movies_embeddings`,\n",
        "    'ml_generate_embedding_result',\n",
        "    TABLE `gemini_demo.movie_posters_results_embeddings`,\n",
        "    'ml_generate_embedding_result',\n",
        "    top_k => 1,\n",
        "    distance_type => 'COSINE');"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RCoxBtpSLxNi"
      },
      "source": [
        "Now let's join some additional information on ratings..."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 55,
      "metadata": {
        "id": "pbz_nMtdY7S6"
      },
      "outputs": [],
      "source": [
        "%%bigquery\n",
        "SELECT\n",
        "  query.uri AS poster_uri,\n",
        "  query.title AS poster_title,\n",
        "  query.year AS poster_year,\n",
        "  base.title AS imdb_title,\n",
        "  base.year AS imdb_year,\n",
        "  base.movie_id AS imdb_movie_id,\n",
        "  distance,\n",
        "  imdb.average_rating,\n",
        "  imdb.num_votes\n",
        "FROM\n",
        "  VECTOR_SEARCH( TABLE `gemini_demo.imdb_movies_embeddings`,\n",
        "    'ml_generate_embedding_result',\n",
        "    TABLE `gemini_demo.movie_posters_results_embeddings`,\n",
        "    'ml_generate_embedding_result',\n",
        "    top_k => 1,\n",
        "    distance_type => 'COSINE') DATA\n",
        "LEFT JOIN\n",
        "  `bigquery-public-data.imdb.title_ratings` imdb\n",
        "ON\n",
        "  base.movie_id = imdb.tconst\n",
        "ORDER BY\n",
        "  imdb.average_rating DESC"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UB8VKTHJJlKx"
      },
      "source": [
        "# Cleaning up\n",
        "\n",
        "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n",
        "\n",
        "Otherwise, you can delete the individual resources you created in this tutorial by uncommenting the below:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 56,
      "metadata": {
        "id": "nSF-ZhhPMjfs"
      },
      "outputs": [],
      "source": [
        "#\n",
        "# !bq rm -r -f $PROJECT_ID:gemini_demo\n",
        "# !bq rm --connection --project_id=$PROJECT_ID --location=us gemini_conn\n",
        "#"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gFWBsvpbhGtE"
      },
      "source": [
        "# Wrap up\n",
        "\n",
        "In this you have seen an example of how to integrate BQML with Vertex AI LLMs, and given examples of how the `ML.GENERATE_TEXT` function can be applied directly to multimodal data stored in BigQuery, as well as how to generate embeddings with `ML.GENERATE_EMBEDDING`.\n",
        "\n",
        "Check out our BigQuery ML documentation on [generating text](https://cloud.google.com/bigquery/docs/generate-text) and [generating embeddings](https://cloud.google.com/bigquery/docs/generate-text-embedding) to learn more about generative AI in BigQuery."
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "poster_image_analysis.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
