Generative AI for data practitioners

Key Concepts

• Generative AI in BigQuery
• Gemini models
• Vertex AI integration
• SQL syntax
• Structured and unstructured data
• Object tables and ObjectRefs
• Multimodal data analysis
• AI.GENERATE_TABLE function
• Structured data output
• Model choice (Gemini, Claude, Llama, Mistral, open models)
• Semantic search and vector embeddings
• BigQuery search function

Generative AI with BigQuery Overview

Jeff Nelson introduces the use of generative AI within Google Cloud's BigQuery, highlighting its integration with Vertex AI. This integration allows users to leverage powerful foundation models like the Gemini family directly from their BigQuery environment using familiar SQL syntax. The core idea is to enhance data value by applying AI to both structured BigQuery tables and unstructured multimodal data (PDFs, images, audio, video).

Accessing Models in BigQuery

To access these models, users call ML or AI functions within BigQuery, providing the model, a prompt, and the data as input arguments. The Gemini model processes the prompt and data for each row, returning structured results to BigQuery. These results can then be used alongside existing BigQuery tables or for ad hoc analysis.

Model Choice and Flexibility

Besides Gemini models, BigQuery offers model choice, including partner models like Claude, Llama, and Mistral, accessible as a service. Users can also access open models hosted on Vertex AI endpoints, including those from Hugging Face or fine-tuned models specific to their business needs.

Structured Data Output

A key announcement is the support for structured data output in BigQuery. This feature allows users to define the output schema in the query, ensuring that the large language model returns specific fields and data types that match the defined schema.

Multimodal Data Support

BigQuery supports multimodal data, allowing users to reference unstructured data in Google Cloud Storage (images, audio, documents) alongside structured data in tables. This enables the mixing of structured and unstructured data in a single inference query.

Inference Flexibility

Users have the flexibility to apply generative AI to all rows within a table or only a subset of records, optimizing performance and cost.

Demo Scenario: Cymbal Pets

The demo scenario focuses on a retailer called Cymbal Pets, which lacks valuable metadata for its products, making it difficult for customers to search and navigate the website. The goal is to use BigQuery and Gemini models to populate the website with product metadata, FAQs, brand descriptions, and enhanced search capabilities.

Object Tables and ObjectRefs

The demo begins by creating object tables, which provide a way for BigQuery to access unstructured data in Cloud Storage. The URI (path) to the objects in Cloud Storage is passed to BigQuery. Object tables provide a SQL interface to these images without moving the data out of Cloud Storage. When querying an object table, fields like URI, object metadata, and an "object ref" column are returned. The object ref is a new data type that forms the foundation for accessing unstructured objects for AI purposes.

Querying Unstructured Data with AI.GENERATE_TABLE

The AI.GENERATE_TABLE function is used to query unstructured data. It takes the Gemini model, the object table (pointing to product images), and a prompt asking to describe each product image in a single sentence. Optional arguments like temperature and max output tokens can also be specified. The function returns single-sentence descriptions for each product image.

Example: The function generates a description for an image of a glass aquarium on a dark brown wooden base.

Generating Structured Outputs

The demo extends to generating more complex structured outputs, such as animal type, search keywords, and product subcategory for each image. The structure of the outputs is defined in the query, specifying the fields and their data types.

Example: For the aquarium image, the function assigns the animal type as "fish," provides a list of five search keywords, and sets the subcategory as "aquariums."

Extracting FAQs from Product Manuals

The demo shows how to extract frequently asked questions from product manuals. An object table is created over a bucket containing product manuals. The Gemini model is then queried to generate question-answer pairs derived from the manual.

Example: The model extracts question-answer pairs from a product manual for an automatic pet feeder.

Generating ObjectRefs on Standard BigQuery Tables

The demo illustrates how to generate object ref columns on standard BigQuery tables using the MAKE_REF and FETCH_METADATA functions. This allows for multimodal analysis over any table.

Example: An image_ref column is added to the products table, pointing to product images in Cloud Storage.

Generating Brand Descriptions

The demo shows how to generate succinct brand descriptions by passing all images and text descriptions from the multimodal table into the Gemini model.

Example: The Aquaclear brand receives a description generated from an array of 33 images and 33 product descriptions.

Filtering Tables with AI Operations

AI operations can be used to filter tables. The AI.GENERATE_BOOL scalar function is used to filter rows based on AI-powered conditions.

Example: Filtering a table of dog treats to find treats that are highly rated, cost less than $10, and come in a blue bag.

Using Open Models from Vertex AI

The demo showcases the use of open models hosted on Vertex AI endpoints. A pointer is created to a Quen QWQ 32 billion parameter model.

Example: The Quen model is used to extract two to three key product themes from thousands of customer reviews.

Semantic Search with Vector Embeddings

The demo introduces semantic search using vector embeddings. Instead of keyword search, semantic search captures the meaning and intent behind the query.

Process:

1. A new BigQuery model is created, pointing to a text embedding model hosted in Vertex AI.
2. An embedding is generated for the search query (e.g., "puppy toy").
3. The vector search function is used against the products table, which has an embedding column.

Result: The search returns relevant products even if the description does not contain the exact keywords.

Visualizing Search Results with BigQuery Colab

The demo shows how to visualize search results using a BigQuery Colab notebook. A Python function is used to render the images returned from a vector search.

Multimodal Search (Text to Image)

The demo extends to multimodal search, specifically text to image. A remote model is defined in BigQuery that references the multimodal embedding model endpoint. The search query is turned into a multimodal embedding, and the vector search function is used to search the table with images and embeddings.

Example: Searching for "fish tank" returns relevant images of fish tanks.

Conclusion

The demo recaps the use of unstructured data sources (images, PDFs, text customer reviews) to generate valuable data for business and discoverability. It highlights the use of BigQuery, Gemini models, and open models to enhance product information, generate FAQs, create brand descriptions, and improve search capabilities. The presentation concludes with resources for getting started with BigQuery generative AI functions, deploying open models, and using ObjectRefs for analyzing structured and unstructured data.