{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "9edad7a6", "metadata": {}, "outputs": [], "source": [ "# Copyright 2025 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": "816ab253", "metadata": { "id": "YOrUAvz6DMw-" }, "source": [ "# BigFrames Multimodal DataFrame\n", "\n", "\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", "
\n" ] }, { "cell_type": "markdown", "id": "77d821d4", "metadata": {}, "source": [ "This notebook is introducing BigFrames Multimodal features:\n", "1. Create Multimodal DataFrame\n", "2. Combine unstructured data with structured data\n", "3. Conduct image transformations\n", "4. Use LLM models to ask questions and generate embeddings on images\n", "5. PDF chunking function\n", "6. Transcribe audio\n", "7. Extract EXIF metadata from images" ] }, { "cell_type": "markdown", "id": "75ab1c13", "metadata": { "id": "PEAJQQ6AFg-n" }, "source": [ "## Setup" ] }, { "cell_type": "markdown", "id": "750954c4", "metadata": {}, "source": [ "Install the latest bigframes package if bigframes version < 2.4.0" ] }, { "cell_type": "code", "execution_count": null, "id": "2a6fafb1", "metadata": {}, "outputs": [], "source": [ "# !pip install bigframes --upgrade" ] }, { "cell_type": "code", "execution_count": null, "id": "df561d04", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "bGyhLnfEeB0X", "outputId": "83ac8b64-3f44-4d43-d089-28a5026cbb42" }, "outputs": [], "source": [ "PROJECT = \"bigframes-dev\" # replace with your project. \n", "# Refer to https://cloud.google.com/bigquery/docs/multimodal-data-dataframes-tutorial#required_roles for your required permissions\n", "\n", "LOCATION = \"us\" # replace with your location.\n", "\n", "# Dataset where the UDF will be created.\n", "DATASET_ID = \"bigframes_samples\" # replace with your dataset ID.\n", "\n", "OUTPUT_BUCKET = \"bigframes_blob_test\" # replace with your GCS bucket. \n", "# The connection (or bigframes-default-connection of the project) must have read/write permission to the bucket. \n", "# Refer to https://cloud.google.com/bigquery/docs/multimodal-data-dataframes-tutorial#grant-permissions for setting up connection service account permissions.\n", "# In this Notebook it uses bigframes-default-connection by default. You can also bring in your own connections in each method.\n", "\n", "FULL_CONNECTION_ID = f\"{PROJECT}.{LOCATION}.bigframes-default-connection\"\n", "\n", "import bigframes\n", "# Setup project\n", "bigframes.options.bigquery.project = PROJECT\n", "bigframes.options.bigquery.location = LOCATION\n", "\n", "# Display options\n", "bigframes.options.display.blob_display_width = 300\n", "bigframes.options.display.progress_bar = None\n", "\n", "import bigframes.pandas as bpd\n", "import bigframes.bigquery as bbq" ] }, { "cell_type": "code", "execution_count": 35, "id": "35bd6e6e", "metadata": {}, "outputs": [], "source": [ "import bigframes.bigquery as bbq\n", "\n", "def get_runtime_json_str(series, mode=\"R\", with_metadata=False):\n", " \"\"\"\n", " Get the runtime (contains signed URL to access gcs data) and apply the\n", " ToJSONSTring transformation.\n", " \n", " Args:\n", " series: bigframes.series.Series to operate on.\n", " mode: \"R\" for read, \"RW\" for read/write.\n", " with_metadata: Whether to fetch and include blob metadata.\n", " \"\"\"\n", " # 1. Optionally fetch metadata\n", " s = (\n", " bbq.obj.fetch_metadata(series)\n", " if with_metadata\n", " else series\n", " )\n", " \n", " # 2. Retrieve the access URL runtime object\n", " runtime = bbq.obj.get_access_url(s, mode=mode)\n", " \n", " # 3. Convert the runtime object to a JSON string\n", " return bbq.to_json_string(runtime)\n", "\n", "def get_metadata(series):\n", " # Fetch metadata and extract GCS metadata from the details JSON field\n", " metadata_obj = bbq.obj.fetch_metadata(series)\n", " return bbq.json_query(metadata_obj.struct.field(\"details\"), \"$.gcs_metadata\")\n", "\n", "def get_content_type(series):\n", " return bbq.json_value(get_metadata(series), \"$.content_type\")\n", "\n", "def get_size(series):\n", " return bbq.json_value(get_metadata(series), \"$.size\").astype(\"Int64\")\n", "\n", "def get_updated(series):\n", " return bpd.to_datetime(bbq.json_value(get_metadata(series), \"$.updated\").astype(\"Int64\"), unit=\"us\", utc=True)\n", "\n", "from IPython.display import HTML, display\n", "\n", "def render_images(df):\n", " \"\"\"Helper to display BigFrames DataFrame with rendered image previews.\"\"\"\n", " import bigframes.pandas as bpd\n", " import bigframes.bigquery as bbq\n", " import bigframes\n", " from bigframes import dtypes\n", " import json\n", " \n", " if isinstance(df, bpd.Series):\n", " df = df.to_frame()\n", " \n", " # 1. Auto-detect columns holding ObjectRefs\n", " object_cols = [\n", " col for col, dtype in zip(df.columns, df.dtypes)\n", " if dtype == dtypes.OBJ_REF_DTYPE\n", " ]\n", " \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", " \n", " # 2. Bulk-fetch access runtime URLs ONLY (disable with_metadata to bypass potential \n", " # race conditions on new files where BigQuery may error before async writes finalize)\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", " \n", " width = bigframes.options.display.blob_display_width or 300\n", " IMAGE_EXTENSIONS = (\".png\", \".jpg\", \".jpeg\", \".gif\", \".webp\")\n", " \n", " def format_cell_html(raw_json):\n", " if not raw_json:\n", " return \"\"\n", " try:\n", " obj_rt = json.loads(raw_json)\n", " \n", " if \"access_urls\" not in obj_rt:\n", " err = obj_rt.get(\"errors\", [{\"message\": \"URL Generation Failed\"}])[0].get(\"message\")\n", " return f'Error: {err}'\n", " \n", " uri = obj_rt.get(\"objectref\", {}).get(\"uri\", \"\")\n", " url = obj_rt[\"access_urls\"][\"read_url\"]\n", " \n", " # Safely infer type from extension to guarantee immediate display availability\n", " if uri and str(uri).lower().endswith(IMAGE_EXTENSIONS):\n", " return f''\n", " \n", " return f'{uri if uri else \"view\"}'\n", " except:\n", " return \"Format Error\"\n", "\n", " for col in object_cols:\n", " final_pd[col] = pandas_json_df[col].map(format_cell_html)\n", " \n", " display(HTML(final_pd.to_html(escape=False)))" ] }, { "cell_type": "markdown", "id": "be9ce892", "metadata": { "id": "ifKOq7VZGtZy" }, "source": [ "To create a Multimodal DataFrame, you can use `bigframes.bigquery.obj.make_ref` on a series of URIs. You can get the URIs from a BigQuery table or by listing them from Cloud Storage.\n", "\n", "In this example, we use `gcsfs` to list the files from Cloud Storage, and then use `read_gbq` to load them into a BigQuery DataFrame before creating the object reference." ] }, { "cell_type": "code", "execution_count": 36, "id": "871d02f4", "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "fx6YcZJbeYru", "outputId": "d707954a-0dd0-4c50-b7bf-36b140cf76cf" }, "outputs": [], "source": [ "import gcsfs\n", "import bigframes.bigquery as bbq\n", "\n", "# List files using gcsfs (public bucket)\n", "fs = gcsfs.GCSFileSystem(anon=True)\n", "uris = fs.glob(\"gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*\")\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 using UNNEST\n", "# We take the first 5 for this example\n", "df_image = bpd.read_gbq(f\"SELECT uri FROM UNNEST({uris[:5]}) as uri\")\n", "\n", "# Create the object reference column\n", "df_image['image'] = bbq.obj.make_ref(df_image['uri'], authorizer=FULL_CONNECTION_ID)\n", "df_image = df_image[['image']]" ] }, { "cell_type": "code", "execution_count": 37, "id": "2e0436b0", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 487 }, "id": "HhCb8jRsLe9B", "outputId": "03081cf9-3a22-42c9-b38f-649f592fdada" }, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
image
0
1
2
3
4
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Take only the 5 images to deal with. Preview the content of the Mutimodal DataFrame\n", "df_image = df_image.head(5)\n", "render_images(df_image)" ] }, { "cell_type": "markdown", "id": "429b0117", "metadata": { "id": "b6RRZb3qPi_T" }, "source": [ "### 2. Combine unstructured data with structured data" ] }, { "cell_type": "markdown", "id": "991fa065", "metadata": { "id": "4YJCdmLtR-qu" }, "source": [ "Now you can put more information into the table to describe the files. Such as author info from inputs, or other metadata from the gcs object itself." ] }, { "cell_type": "code", "execution_count": 38, "id": "08722ec5", "metadata": { "id": "YYYVn7NDH0Me" }, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
imageauthorcontent_typesizeupdated
0aliceimage/png7157662025-03-20 17:44:38+00:00
1bobimage/png11674062025-03-20 17:44:38+00:00
2bobimage/png11508922025-03-20 17:44:39+00:00
3aliceimage/png17365332025-03-20 17:44:39+00:00
4bobimage/png4397402025-03-20 17:44:39+00:00
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Combine unstructured data with structured data\n", "df_image = df_image.head(5)\n", "df_image[\"author\"] = [\"alice\", \"bob\", \"bob\", \"alice\", \"bob\"] # type: ignore\n", "df_image[\"content_type\"] = get_content_type(df_image[\"image\"])\n", "df_image[\"size\"] = get_size(df_image[\"image\"])\n", "df_image[\"updated\"] = get_updated(df_image[\"image\"])\n", "render_images(df_image)" ] }, { "cell_type": "markdown", "id": "f90826f6", "metadata": {}, "source": [ "### 3. Conduct image transformations" ] }, { "cell_type": "markdown", "id": "e24c9f8c", "metadata": {}, "source": [ "This section demonstrates how to perform image transformations like blur, resize, and normalize using custom BigQuery Python UDFs and the `opencv-python` library." ] }, { "cell_type": "code", "execution_count": 39, "id": "db665049", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 487 }, "id": "HhCb8jRsLe9B", "outputId": "03081cf9-3a22-42c9-b38f-649f592fdada" }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/pandas/__init__.py:211: PreviewWarning: udf is in preview.\n", " return global_session.with_default_session(\n", "/usr/local/google/home/shuowei/src/google-cloud-python/google-cloud-python/packages/bigframes/bigframes/dataframe.py:4695: FunctionAxisOnePreviewWarning: DataFrame.apply with parameter axis=1 scenario is in preview.\n", " warnings.warn(msg, category=bfe.FunctionAxisOnePreviewWarning)\n", "/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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
imageblurred
0
1
2
3
4
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Construct the canonical connection ID\n", "FULL_CONNECTION_ID = f\"{PROJECT}.{LOCATION}.bigframes-default-connection\"\n", "\n", "@bpd.udf(\n", " input_types=[str, str, int, int],\n", " output_type=str,\n", " dataset=DATASET_ID,\n", " name=\"image_blur_v2\",\n", " bigquery_connection=FULL_CONNECTION_ID,\n", " packages=[\"opencv-python-headless\", \"numpy\", \"requests\"],\n", ")\n", "def image_blur(src_rt: str, dst_rt: str, kx: int, ky: int) -> str:\n", " import json\n", " import cv2 as cv\n", " import numpy as np\n", " import requests\n", " import base64\n", "\n", " src_obj = json.loads(src_rt)\n", " if \"access_urls\" not in src_obj:\n", " raise ValueError(f\"Missing 'access_urls' in source object. Response: {src_obj}\")\n", " src_url = src_obj[\"access_urls\"][\"read_url\"]\n", " \n", " response = requests.get(src_url, timeout=30)\n", " response.raise_for_status()\n", " \n", " img = cv.imdecode(np.frombuffer(response.content, np.uint8), cv.IMREAD_UNCHANGED)\n", " if img is None:\n", " raise ValueError(\"cv.imdecode failed\")\n", " \n", " kx, ky = int(kx), int(ky)\n", " img_blurred = cv.blur(img, ksize=(kx, ky))\n", " \n", " success, encoded = cv.imencode(\".jpeg\", img_blurred)\n", " if not success:\n", " raise ValueError(\"cv.imencode failed\")\n", " \n", " # Handle two output modes\n", " if dst_rt: # GCS/Series output mode\n", " dst_obj = json.loads(dst_rt)\n", " if \"access_urls\" not in dst_obj:\n", " raise ValueError(f\"Missing 'access_urls' in destination object. Verify authorizer permissions. Response: {dst_obj}\")\n", " dst_url = dst_obj[\"access_urls\"][\"write_url\"]\n", " \n", " requests.put(dst_url, data=encoded.tobytes(), headers={\"Content-Type\": \"image/jpeg\"}, timeout=30).raise_for_status()\n", " \n", " uri = dst_obj[\"objectref\"][\"uri\"]\n", " return uri\n", " \n", " else: # BigQuery bytes output mode \n", " image_bytes = encoded.tobytes()\n", " return base64.b64encode(image_bytes).decode()\n", "\n", "def apply_transformation(series, dst_folder, udf, *args, verbose=False):\n", " import os\n", " dst_folder = os.path.join(dst_folder, \"\")\n", " # Fetch metadata to get the URI\n", " metadata = bbq.obj.fetch_metadata(series)\n", " current_uri = metadata.struct.field(\"uri\")\n", " dst_uri = current_uri.str.replace(r\"^.*\\/(.*)$\", rf\"{dst_folder}\\1\", regex=True)\n", " \n", " # To avoid synchronous 404 validation checks on files that don't exist yet, \n", " # bypass the validator by explicitly constructing an objectref JSON.\n", " dst_blob_df = bpd.DataFrame({\"uri\": dst_uri})\n", " dst_blob_df[\"authorizer\"] = FULL_CONNECTION_ID\n", " dst_blob = bbq.obj.make_ref(bbq.to_json(bbq.struct(dst_blob_df)))\n", "\n", " df_transform = bpd.DataFrame({\n", " \"src_rt\": get_runtime_json_str(series, mode=\"R\"),\n", " \"dst_rt\": get_runtime_json_str(dst_blob, mode=\"RW\"),\n", " })\n", " res = df_transform[[\"src_rt\", \"dst_rt\"]].apply(\n", " udf, axis=1, args=args\n", " )\n", " \n", " if verbose:\n", " return res\n", " \n", " # Final return MUST also use JSON bypass to eliminate temporary 404 validation \n", " # errors from embedded ObjectRefs during fused query execution pipelines.\n", " res_df = bpd.DataFrame({\"uri\": res})\n", " res_df[\"authorizer\"] = FULL_CONNECTION_ID\n", " return bbq.obj.make_ref(bbq.to_json(bbq.struct(res_df)))\n", "\n", "# Apply transformations\n", "df_image[\"blurred\"] = apply_transformation(\n", " df_image[\"image\"], f\"gs://{OUTPUT_BUCKET}/image_blur_transformed/\",\n", " image_blur, 20, 20\n", ")\n", "render_images(df_image[[\"image\", \"blurred\"]])" ] }, { "cell_type": "markdown", "id": "11fcc6ec", "metadata": { "id": "Euk5saeVVdTP" }, "source": [ "### 4. Use LLM models to ask questions and generate embeddings on images" ] }, { "cell_type": "code", "execution_count": null, "id": "793b2f45", "metadata": { "id": "mRUGfcaFVW-3" }, "outputs": [], "source": [ "from bigframes.ml import llm\n", "gemini = llm.GeminiTextGenerator()" ] }, { "cell_type": "code", "execution_count": null, "id": "13d7cb93", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 657 }, "id": "DNFP7CbjWdR9", "outputId": "3f90a062-0abc-4bce-f53c-db57b06a14b9" }, "outputs": [], "source": [ "# Ask the same question on the images\n", "answer = gemini.predict(df_image, prompt=[\"what item is it?\", \"what color is the picture?\"])\n", "render_images(answer[[\"ml_generate_text_llm_result\", \"image\"]])" ] }, { "cell_type": "code", "execution_count": null, "id": "68857305", "metadata": { "id": "IG3J3HsKhyBY" }, "outputs": [], "source": [ "# Ask different questions\n", "df_image[\"question\"] = [\n", " \"what item is it?\",\n", " \"what color is the picture?\",\n", " \"what is the product name?\",\n", " \"is it for pets?\",\n", " \"what is the weight of the product?\",\n", "]" ] }, { "cell_type": "code", "execution_count": null, "id": "829afc69", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 657 }, "id": "qKOb765IiVuD", "outputId": "731bafad-ea29-463f-c8c1-cb7acfd70e5d" }, "outputs": [], "source": [ "answer_alt = gemini.predict(df_image, prompt=[df_image[\"question\"], df_image[\"image\"]])\n", "render_images(answer_alt[[\"ml_generate_text_llm_result\", \"image\"]])" ] }, { "cell_type": "code", "execution_count": null, "id": "e75df430", "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 300 }, "id": "KATVv2CO5RT1", "outputId": "6ec01f27-70b6-4f69-c545-e5e3c879480c" }, "outputs": [], "source": [ "# Generate embeddings.\n", "embed_model = llm.MultimodalEmbeddingGenerator()\n", "embeddings = embed_model.predict(df_image[\"image\"])\n", "embeddings" ] }, { "cell_type": "markdown", "id": "23892b0e", "metadata": { "id": "iRUi8AjG7cIf" }, "source": [ "### 5. PDF extraction and chunking function\n", "\n", "This section demonstrates how to extract text and chunk text from PDF files using custom BigQuery Python UDFs and the `pypdf` library." ] }, { "cell_type": "code", "execution_count": null, "id": "136a18b8", "metadata": {}, "outputs": [], "source": [ "# Construct the canonical connection ID\n", "FULL_CONNECTION_ID = f\"{PROJECT}.{LOCATION}.bigframes-default-connection\"\n", "\n", "@bpd.udf(\n", " input_types=[str],\n", " output_type=str,\n", " dataset=DATASET_ID,\n", " name=\"pdf_extract\",\n", " bigquery_connection=FULL_CONNECTION_ID,\n", " packages=[\"pypdf\", \"requests\", \"cryptography\"],\n", ")\n", "def pdf_extract(src_obj_ref_rt: str) -> str:\n", " import io\n", " import json\n", " from pypdf import PdfReader\n", " import requests\n", " src_obj_ref_rt_json = json.loads(src_obj_ref_rt)\n", " src_url = src_obj_ref_rt_json[\"access_urls\"][\"read_url\"]\n", " response = requests.get(src_url, timeout=30, stream=True)\n", " response.raise_for_status()\n", " pdf_bytes = response.content\n", " pdf_file = io.BytesIO(pdf_bytes)\n", " reader = PdfReader(pdf_file, strict=False)\n", " all_text = \"\"\n", " for page in reader.pages:\n", " page_extract_text = page.extract_text()\n", " if page_extract_text:\n", " all_text += page_extract_text\n", " return all_text\n", "\n", "@bpd.udf(\n", " input_types=[str, int, int],\n", " output_type=list[str],\n", " dataset=DATASET_ID,\n", " name=\"pdf_chunk\",\n", " bigquery_connection=FULL_CONNECTION_ID,\n", " packages=[\"pypdf\", \"requests\", \"cryptography\"],\n", ")\n", "def pdf_chunk(src_obj_ref_rt: str, chunk_size: int, overlap_size: int) -> list[str]:\n", " import io\n", " import json\n", " from pypdf import PdfReader\n", " import requests\n", " src_obj_ref_rt_json = json.loads(src_obj_ref_rt)\n", " src_url = src_obj_ref_rt_json[\"access_urls\"][\"read_url\"]\n", " response = requests.get(src_url, timeout=30, stream=True)\n", " response.raise_for_status()\n", " pdf_bytes = response.content\n", " pdf_file = io.BytesIO(pdf_bytes)\n", " reader = PdfReader(pdf_file, strict=False)\n", " all_text_chunks = []\n", " curr_chunk = \"\"\n", " for page in reader.pages:\n", " page_text = page.extract_text()\n", " if page_text:\n", " curr_chunk += page_text\n", " while len(curr_chunk) >= chunk_size:\n", " split_idx = curr_chunk.rfind(\" \", 0, chunk_size)\n", " if split_idx == -1:\n", " split_idx = chunk_size\n", " actual_chunk = curr_chunk[:split_idx]\n", " all_text_chunks.append(actual_chunk)\n", " overlap = curr_chunk[split_idx + 1 : split_idx + 1 + overlap_size]\n", " curr_chunk = overlap + curr_chunk[split_idx + 1 + overlap_size :]\n", " if curr_chunk:\n", " all_text_chunks.append(curr_chunk)\n", " return all_text_chunks" ] }, { "cell_type": "code", "execution_count": null, "id": "234a5f86", "metadata": {}, "outputs": [], "source": [ "import gcsfs\n", "import bigframes.bigquery as bbq\n", "\n", "# List files using gcsfs\n", "fs = gcsfs.GCSFileSystem(anon=True)\n", "uris = fs.glob(\"gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/documents/*\")\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", "df_pdf = bpd.read_gbq(f\"SELECT uri FROM UNNEST({uris[:5]}) as uri\")\n", "\n", "# Create the object reference column\n", "df_pdf['pdf'] = bbq.obj.make_ref(df_pdf['uri'], authorizer=FULL_CONNECTION_ID)\n", "df_pdf = df_pdf[['pdf']]\n", "\n", "# Generate a JSON string containing the runtime information (including signed read URLs)\n", "access_urls = get_runtime_json_str(df_pdf[\"pdf\"], mode=\"R\")\n", "\n", "# Apply PDF extraction\n", "df_pdf[\"extracted_text\"] = access_urls.apply(pdf_extract)\n", "\n", "# Apply PDF chunking\n", "df_pdf[\"chunked\"] = access_urls.apply(pdf_chunk, args=(2000, 200))\n", "\n", "df_pdf[[\"extracted_text\", \"chunked\"]]" ] }, { "cell_type": "code", "execution_count": null, "id": "d80effbe", "metadata": {}, "outputs": [], "source": [ "# Explode the chunks to see each chunk as a separate row\n", "chunked = df_pdf[\"chunked\"].explode()\n", "chunked" ] }, { "cell_type": "markdown", "id": "118cf1c7", "metadata": {}, "source": [ "### 6. Audio transcribe" ] }, { "cell_type": "code", "execution_count": null, "id": "1794c54f", "metadata": {}, "outputs": [], "source": [ "import gcsfs\n", "import bigframes.bigquery as bbq\n", "\n", "audio_gcs_path = \"gs://bigframes_blob_test/audio/*\"\n", "\n", "# List files using gcsfs\n", "fs = gcsfs.GCSFileSystem()\n", "uris = fs.glob(audio_gcs_path)\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", "# If the bucket is empty or doesn't exist, this will result in an empty DataFrame\n", "if not uris:\n", " # Fallback to a dummy list or just let it be empty\n", " uris = [\"gs://bigframes_blob_test/audio/dummy.mp3\"]\n", "\n", "df = bpd.read_gbq(f\"SELECT uri FROM UNNEST({uris[:5]}) as uri\")\n", "\n", "# Create the object reference column\n", "df['audio'] = bbq.obj.make_ref(df['uri'], authorizer=FULL_CONNECTION_ID)\n", "df = df[['audio']]" ] }, { "cell_type": "code", "execution_count": null, "id": "c9f9d484", "metadata": {}, "outputs": [], "source": [ "# The audio_transcribe function is a convenience wrapper around bigframes.bigquery.ai.generate.\n", "# Here's how to perform the same operation directly:\n", "\n", "audio_series = df[\"audio\"]\n", "prompt_text = (\n", " \"**Task:** Transcribe the provided audio. **Instructions:** - Your response \"\n", " \"must contain only the verbatim transcription of the audio. - Do not include \"\n", " \"any introductory text, summaries, or conversational filler in your response. \"\n", " \"The output should begin directly with the first word of the audio.\"\n", ")\n", "\n", "# Convert the audio series to the runtime representation required by the model.\n", "# This involves fetching metadata and getting a signed access URL.\n", "audio_metadata = bbq.obj.fetch_metadata(audio_series)\n", "audio_runtime = bbq.obj.get_access_url(audio_metadata, mode=\"R\")\n", "\n", "transcribed_results = bbq.ai.generate(\n", " prompt=(prompt_text, audio_runtime),\n", " endpoint=\"gemini-2.5-flash\",\n", " model_params={\"generationConfig\": {\"temperature\": 0.0}},\n", ")\n", "\n", "transcribed_series = transcribed_results.struct.field(\"result\").rename(\"transcribed_content\")\n", "transcribed_series" ] }, { "cell_type": "code", "execution_count": null, "id": "7209a62a", "metadata": {}, "outputs": [], "source": [ "# To get verbose results (including status), we can extract both fields from the result struct.\n", "transcribed_content_series = transcribed_results.struct.field(\"result\")\n", "transcribed_status_series = transcribed_results.struct.field(\"status\")\n", "\n", "transcribed_series_verbose = bpd.DataFrame(\n", " {\n", " \"status\": transcribed_status_series,\n", " \"content\": transcribed_content_series,\n", " }\n", ")\n", "# Package as a struct for consistent display\n", "transcribed_series_verbose = bbq.struct(transcribed_series_verbose).rename(\"transcription_results\")\n", "transcribed_series_verbose" ] }, { "cell_type": "markdown", "id": "c8351cc3", "metadata": {}, "source": [ "### 7. Extract EXIF metadata from images" ] }, { "cell_type": "markdown", "id": "e59670b9", "metadata": {}, "source": [ "This section demonstrates how to extract EXIF metadata from images using a custom BigQuery Python UDF and the `Pillow` library." ] }, { "cell_type": "code", "execution_count": null, "id": "fda362f4", "metadata": {}, "outputs": [], "source": [ "# Construct the canonical connection ID\n", "FULL_CONNECTION_ID = f\"{PROJECT}.{LOCATION}.bigframes-default-connection\"\n", "\n", "@bpd.udf(\n", " input_types=[str],\n", " output_type=str,\n", " dataset=DATASET_ID,\n", " name=\"extract_exif\",\n", " bigquery_connection=FULL_CONNECTION_ID,\n", " packages=[\"pillow\", \"requests\"],\n", " max_batching_rows=8192,\n", " container_cpu=0.33,\n", " container_memory=\"512Mi\"\n", ")\n", "def extract_exif(src_obj_ref_rt: str) -> str:\n", " import io\n", " import json\n", " from PIL import ExifTags, Image\n", " import requests\n", " src_obj_ref_rt_json = json.loads(src_obj_ref_rt)\n", " src_url = src_obj_ref_rt_json[\"access_urls\"][\"read_url\"]\n", " response = requests.get(src_url, timeout=30)\n", " bts = response.content\n", " image = Image.open(io.BytesIO(bts))\n", " exif_data = image.getexif()\n", " exif_dict = {}\n", " if exif_data:\n", " for tag, value in exif_data.items():\n", " tag_name = ExifTags.TAGS.get(tag, tag)\n", " exif_dict[tag_name] = value\n", " return json.dumps(exif_dict)" ] }, { "cell_type": "code", "execution_count": null, "id": "40bb6bc9", "metadata": {}, "outputs": [], "source": [ "import gcsfs\n", "import bigframes.bigquery as bbq\n", "\n", "# Create a Multimodal DataFrame from the sample image URIs\n", "fs = gcsfs.GCSFileSystem()\n", "uris = fs.glob(\"gs://bigframes_blob_test/images_exif/*\")\n", "\n", "# Ensure URIs have gs:// prefix\n", "uris = [u if u.startswith(\"gs://\") else f\"gs://{u}\" for u in uris]\n", "\n", "if not uris:\n", " uris = [\"gs://bigframes_blob_test/images_exif/dummy.jpg\"]\n", "\n", "exif_image_df = bpd.read_gbq(f\"SELECT uri FROM UNNEST({uris[:5]}) as uri\")\n", "exif_image_df['blob_col'] = bbq.obj.make_ref(exif_image_df['uri'], authorizer=FULL_CONNECTION_ID)\n", "exif_image_df = exif_image_df[['blob_col']]\n", "\n", "# Generate a JSON string containing the runtime information (including signed read URLs)\n", "# This allows the UDF to download the images from Google Cloud Storage\n", "access_urls = get_runtime_json_str(exif_image_df[\"blob_col\"], mode=\"R\")\n", "\n", "# Apply the BigQuery Python UDF to the runtime JSON strings\n", "# We cast to string to ensure the input matches the UDF's signature\n", "exif_json = access_urls.astype(str).apply(extract_exif)\n", "\n", "# Parse the resulting JSON strings back into a structured JSON type for easier access\n", "exif_data = bbq.parse_json(exif_json)\n", "\n", "exif_data" ] } ], "metadata": { "colab": { "provenance": [] }, "kernelspec": { "display_name": "venv (3.13.0)", "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 }