{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "7add2e44", "metadata": { "id": "XZpKUoHjXw3_" }, "outputs": [], "source": [ "# Copyright 2026 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", "id": "ee509844", "metadata": { "id": "SEKzWP6jW9Oj" }, "source": [ "# Analyzing movie posters with BigQuery Dataframe AI functions" ] }, { "cell_type": "markdown", "id": "81b8de8d", "metadata": {}, "source": [ "\n", "\n", " \n", " \n", " \n", "
\n", " \n", " \"Colab Run in Colab\n", " \n", " \n", " \n", " \"GitHub\n", " View on GitHub\n", " \n", " \n", " \n", " \"BQ\n", " Open in BQ Studio\n", " \n", "
" ] }, { "cell_type": "markdown", "id": "256b6c02", "metadata": { "id": "c9CCKXG5XTb-" }, "source": [ "BigQuery Dataframe provides a Pythonic way to use AI functions directly with your dataframes. In this notebook, you will use these functions to analyze old\n", "movie posters. These posters are images stored in a public Google Cloud Storage bucket: `gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters`" ] }, { "cell_type": "markdown", "id": "3f71d3cb", "metadata": { "id": "CUJDa_7MPbL9" }, "source": [ "## Set up" ] }, { "cell_type": "markdown", "id": "547145f5", "metadata": { "id": "D3iYtBSkYpCK" }, "source": [ "Before you begin, you need to\n", "\n", "* Set up your permissions for generative AI functions with [these instructions](https://docs.cloud.google.com/bigquery/docs/permissions-for-ai-functions)\n", "* Set up your Cloud Resource connection by following [these instructions](https://docs.cloud.google.com/bigquery/docs/create-cloud-resource-connection)\n", "\n", "Once you have the permissions set up, import the `bigframes.pandas` package, and\n", "set your cloud project ID." ] }, { "cell_type": "code", "execution_count": 2, "id": "d9cd6da8", "metadata": { "id": "6nqoRHYbPAx3" }, "outputs": [], "source": [ "import bigframes.pandas as bpd\n", "\n", "MY_PROJECT_ID = \"bigframes-dev\" # @param {type:\"string\"}\n", "LOCATION = \"us\" # @param {type:\"string\"}\n", "\n", "bpd.options.bigquery.project = MY_PROJECT_ID\n", "bpd.options.bigquery.location = LOCATION" ] }, { "cell_type": "markdown", "id": "015a63c1", "metadata": { "id": "2XHcNHtvPhNW" }, "source": [ "## Load data" ] }, { "cell_type": "markdown", "id": "254561e0", "metadata": { "id": "eS-9A7DijfoQ" }, "source": [ "First, you load the data from the GCS bucket to a BigQuery Dataframe:" ] }, { "cell_type": "code", "execution_count": 3, "id": "47acbbfe", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "ZNPzFjCyPap0", "outputId": "346d20b2-d615-4094-d24e-2d40e5c90ee2" }, "outputs": [ { "data": { "text/html": [ "\n", " Query processed 0 Bytes in a moment of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stderr", "output_type": "stream", "text": [ "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/dtypes.py:1044: JSONDtypeWarning: JSON columns will be represented as pandas.ArrowDtype(pyarrow.json_())\n", "instead of using `db_dtypes` in the future when available in pandas\n", "(https://github.com/pandas-dev/pandas/issues/60958) and pyarrow.\n", " warnings.warn(msg, bigframes.exceptions.JSONDtypeWarning)\n" ] }, { "data": { "text/html": [ "\n", " Query processed 0 Bytes in 18 seconds of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " Query processed 0 Bytes in 8 seconds of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
poster
0
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Replace with your own connection name.\n", "MY_CONNECTION = 'bigframes-default-connection' # @param {type:\"string\"}\n", "FULL_CONNECTION_ID = f\"{MY_PROJECT_ID}.{LOCATION}.{MY_CONNECTION}\"\n", "\n", "import gcsfs\n", "import bigframes\n", "import bigframes.pandas as bpd\n", "import bigframes.bigquery as bbq\n", "import json\n", "from IPython.display import HTML, display\n", "\n", "session = bpd.get_global_session()\n", "\n", "# Configure global display parameters \n", "bigframes.options.display.blob_display_width = 200\n", "\n", "def get_runtime_json_str(series, mode=\"R\", with_metadata=False):\n", " s = bbq.obj.fetch_metadata(series) if with_metadata else series\n", " runtime = bbq.obj.get_access_url(s, mode=mode)\n", " return bbq.to_json_string(runtime)\n", "\n", "def get_read_url(series):\n", " runtime = bbq.obj.get_access_url(series, mode=\"R\")\n", " return bbq.json_value(runtime, \"$.access_urls.read_url\")\n", "\n", "def render_images(df):\n", " \"\"\"Helper to display BigFrames DataFrame with rendered image previews.\"\"\"\n", " from bigframes import dtypes\n", " if isinstance(df, bpd.Series):\n", " df = df.to_frame()\n", " \n", " object_cols = [col for col, dtype in zip(df.columns, df.dtypes) if dtype == dtypes.OBJ_REF_DTYPE]\n", " if not object_cols:\n", " display(df)\n", " return\n", "\n", " limit = bigframes.options.display.max_rows or 10\n", " view_df = df.head(limit)\n", " runtime_cols = {\n", " col: get_runtime_json_str(view_df[col], mode=\"R\", with_metadata=False) \n", " for col in object_cols\n", " }\n", " \n", " pandas_json_df = bpd.DataFrame(runtime_cols).to_pandas()\n", " final_pd = view_df.to_pandas()\n", " width = bigframes.options.display.blob_display_width or 200\n", " \n", " def format_cell_html(raw_json):\n", " if not raw_json: return \"\"\n", " try:\n", " obj_rt = json.loads(raw_json)\n", " if \"access_urls\" not in obj_rt: return \"Error fetching URL\"\n", " uri = obj_rt.get(\"objectref\", {}).get(\"uri\", \"\")\n", " url = obj_rt[\"access_urls\"][\"read_url\"]\n", " if str(uri).lower().endswith((\".png\", \".jpg\", \".jpeg\", \".webp\")):\n", " return f''\n", " return f'{uri}'\n", " except: return \"Format Error\"\n", "\n", " for col in object_cols:\n", " final_pd[col] = pandas_json_df[col].map(format_cell_html)\n", " display(HTML(final_pd.to_html(escape=False)))\n", "\n", "# List files using gcsfs\n", "fs = gcsfs.GCSFileSystem(anon=True)\n", "uris = fs.glob(\"gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/*\")\n", "\n", "# Ensure URIs have gs:// prefix\n", "uris = [u if u.startswith(\"gs://\") else f\"gs://{u}\" for u in uris]\n", "\n", "# Read the URIs into a BigQuery DataFrame\n", "movies = bpd.read_gbq(f\"SELECT uri FROM UNNEST({uris[:5]}) as uri\")\n", "\n", "# Create the object reference column using the fully qualified connection ID\n", "movies['poster'] = bbq.obj.make_ref(movies['uri'], authorizer=FULL_CONNECTION_ID)\n", "movies = movies[['poster']]\n", "render_images(movies.head(1))" ] }, { "cell_type": "markdown", "id": "f1096d2f", "metadata": { "id": "EfkdDH08QnYw" }, "source": [ "## Extract titles from posters" ] }, { "cell_type": "code", "execution_count": 4, "id": "bb30d47c", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 1000 }, "id": "6CoZZ5tSQm1r", "outputId": "1b3915ce-eb83-4be9-b1c1-d9a326dc9408" }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/dtypes.py:1044: JSONDtypeWarning: JSON columns will be represented as pandas.ArrowDtype(pyarrow.json_())\n", "instead of using `db_dtypes` in the future when available in pandas\n", "(https://github.com/pandas-dev/pandas/issues/60958) and pyarrow.\n", " warnings.warn(msg, bigframes.exceptions.JSONDtypeWarning)\n" ] }, { "data": { "text/html": [ "\n", " Query processed 0 Bytes in 23 seconds of slot time. [Job bigframes-dev:US.job_ZKfuxLQE1U49whg7fgakYFYfiz34 details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " Query processed 0 Bytes in 40 seconds of slot time. [Job bigframes-dev:US.job_VwLv_BxDFdE4adNx1bpnvvM5vfZd details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
postertitle
0The movie title for this poster image is **Au Secours!** (Help!).
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import bigframes.bigquery as bbq\n", "\n", "movies['title'] = bbq.ai.generate(\n", " (\"What is the movie title for this poster image?\", get_read_url(movies['poster']))\n", ").struct.field(\"result\")\n", "render_images(movies.head(1))" ] }, { "cell_type": "markdown", "id": "eb9eb261", "metadata": { "id": "cFQHQ9S2lr6t" }, "source": [ "Notice that `ai.generate()` has a `struct` return type, which holds not only the LLM response, but also the status. If you do not provide a field name for your answer, `\"result\"` will be the default name. You can access LLM response content with the struct accessor (e.g. `my_response.struct.filed(\"result\")`);." ] }, { "cell_type": "markdown", "id": "ea29eb21", "metadata": { "id": "R8kkUhgoS5Xz" }, "source": [ "## Get movie release year\n", "\n", "In the example below, you will use `ai.generate_int()` to find the release year for each movie poster:" ] }, { "cell_type": "code", "execution_count": 5, "id": "bf426247", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 976 }, "id": "cKZdHq0XS1iW", "outputId": "72cbad57-4518-4e1e-97bb-333d424dba73" }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/dtypes.py:1044: JSONDtypeWarning: JSON columns will be represented as pandas.ArrowDtype(pyarrow.json_())\n", "instead of using `db_dtypes` in the future when available in pandas\n", "(https://github.com/pandas-dev/pandas/issues/60958) and pyarrow.\n", " warnings.warn(msg, bigframes.exceptions.JSONDtypeWarning)\n", "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/core/logging/log_adapter.py:229: ApiDeprecationWarning: The blob accessor is deprecated and will be removed in a future release. Use bigframes.bigquery.obj functions instead.\n", " return prop(*args, **kwargs)\n" ] }, { "data": { "text/html": [ "\n", " Query processed 0 Bytes in 51 seconds of slot time. [Job bigframes-dev:US.3cf4ab5b-c360-4b7c-9def-4cd03135a547 details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " Query processed 1.2 kB in a moment of slot time.\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
postertitleyear
0The movie title is **Au Secours!**1924
\n", "

1 rows × 3 columns

\n", "
[1 rows x 3 columns in total]" ], "text/plain": [ " poster \\\n", "0 {\"access_urls\":{\"expiry_time\":\"2026-05-09T03:1... \n", "\n", " title year \n", "0 The movie title is **Au Secours!** 1924 \n", "\n", "[1 rows x 3 columns]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "movies['year'] = bbq.ai.generate_int(\n", " (\"What is the release year for this movie?\", movies['title']),\n", " endpoint='gemini-2.5-pro'\n", ").struct.field(\"result\")\n", "\n", "movies.head(1)" ] }, { "cell_type": "code", "execution_count": 6, "id": "8bf12352", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 250 }, "id": "yqRiNRY8_8fs", "outputId": "efa60107-6883-4f5c-8e40-43c7287ea7fb" }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/dtypes.py:1044: JSONDtypeWarning: JSON columns will be represented as pandas.ArrowDtype(pyarrow.json_())\n", "instead of using `db_dtypes` in the future when available in pandas\n", "(https://github.com/pandas-dev/pandas/issues/60958) and pyarrow.\n", " warnings.warn(msg, bigframes.exceptions.JSONDtypeWarning)\n" ] }, { "data": { "text/plain": [ "poster structSQL
WITH `bfcte_0` AS (\n",
       "  SELECT\n",
       "    *\n",
       "  FROM UNNEST(ARRAY<STRUCT<`bfcol_0` STRING, `bfcol_1` INT64, `bfcol_2` INT64>>[STRUCT(\n",
       "    'gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/au_secours.jpeg',\n",
       "    0,\n",
       "    0\n",
       "  ), STRUCT(\n",
       "    'gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/barque_sortant_du_port.jpeg',\n",
       "    1,\n",
       "    1\n",
       "  ), STRUCT(\n",
       "    'gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/battling_butler.jpg',\n",
       "    2,\n",
       "    2\n",
       "  ), STRUCT(\n",
       "    'gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/brown_of_harvard.jpeg',\n",
       "    3,\n",
       "    3\n",
       "  ), STRUCT(\n",
       "    'gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/der_student_von_prag.jpg',\n",
       "    4,\n",
       "    4\n",
       "  )])\n",
       ")\n",
       "SELECT\n",
       "  `bfcol_1` AS `bfuid_col_60`,\n",
       "  TO_JSON_STRING(\n",
       "    OBJ.GET_ACCESS_URL(OBJ.MAKE_REF(`bfcol_0`, 'bigframes-dev.us.bigframes-default-connection'), 'R')\n",
       "  ) AS `bfuid_col_66`\n",
       "FROM `bfcte_0`\n",
       "WHERE\n",
       "  AI.IF(\n",
       "    prompt => (\n",
       "      'The movie ',\n",
       "      AI.GENERATE(\n",
       "        prompt => (\n",
       "          'What is the movie title for this poster image?',\n",
       "          JSON_VALUE(\n",
       "            OBJ.GET_ACCESS_URL(OBJ.MAKE_REF(`bfcol_0`, 'bigframes-dev.us.bigframes-default-connection'), 'R'),\n",
       "            '$.access_urls.read_url'\n",
       "          )\n",
       "        ),\n",
       "        request_type => 'UNSPECIFIED'\n",
       "      ).`result`,\n",
       "      ' was made in US'\n",
       "    ),\n",
       "    optimization_mode => 'MINIMIZE_COST'\n",
       "  )\n",
       "ORDER BY\n",
       "  `bfcol_2` ASC NULLS LAST\n",
       "LIMIT 1
\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " Query processed 0 Bytes in 3 minutes of slot time. [Job bigframes-dev:US.job_NBILG5qU14Aitas81nPCCtYM9KdM details]\n", " " ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
postertitleyear
2The movie title for the poster image is **Battling Butler**.1926
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "us_movies = movies[bbq.ai.if_(\n", " (\"The movie \", movies['title'], \" was made in US\")\n", ")]\n", "render_images(us_movies.head(1))" ] } ], "metadata": { "colab": { "provenance": [] }, "kernelspec": { "display_name": ".venv", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.0" } }, "nbformat": 4, "nbformat_minor": 0 }