#AIGenerateTable

[govindhtech]

AI Generate Table: Extracts Structured Data From Images

Generate Table AI

Due to social media, cellphones, and other digital sources, a lot of unstructured data has been created, including documents, movies, and photos. BigQuery works with Google Cloud's powerful AI platform, Vertex AI, to analyse this data. This lets you use advanced AI models like Gemini 2.5 Pro/Flash to find meaning in unstructured data.

Google's AI systems can analyse text, images, audio, and video. They can extract names, dates, and keywords from raw data to provide organised insights that work with your products. These models can also deliver structured JSON data with innovative constrained decoding methods to ensure workflow compliance.

To speed up this process, Google Cloud added AI.GENERATE_TABLE() to BigQuery, expanding on ML.GENERATE_TEXT(). This program automatically converts unstructured data insights into a structured BigQuery table using the prompt and table schema. With this simplified way, you can analyse the collected data with your current data analysis tools.

Extracting picture data structure

We'll use a three-image sample to explore this new feature. The first is a Seattle skyline and Space Needle shot. A New York City perspective follows. Finally, there is a non-cityscape photo of flowers and cookies.

You must give BigQuery these photographs to leverage its generative AI features. Create a table called “image_dataset” that links to the Google Cloud Storage bucket with the photos.

Now that your image data is ready, connect to the powerful Gemini 2.5 Flash model. Through a BigQuery “remote model” to this advanced AI, this is achieved.

Let's use AI.GENERATE_TABLE() to inspect the images. The function requires the remote model you made (connected to Gemini 2.5 Flash) and the photo table.

The model must “Identify the city from the image and provide its name, state of residence, brief history and tourist attractions.” Please output nothing if the photo is not a city. It will create a structured output format with the following fields to provide organised and user-friendly results:

String city_name

String state

History_brief = string

String array attractions

This style ensures output consistency and compatibility with other BigQuery tools. This schema's syntax matches BigQuery's CREATE TABLE command.

When run, AI.GENERATE_TABLE() builds a five-column table. The fifth column has the input table photo URI, while the other four columns—city_name, state, brief_history, and attractions—match your schema.

The model successfully identified the first two photos' cities, including their names and states. It listed attractions and brief histories for each city using its own data. This shows how large language models can directly extract insights from pictures.

Structured medical transcription data extraction

Let's use AI.GENERATE_TABLE again to obtain unstructured data from a BQ controlled table. The Kaggle Medical Transcriptions dataset will be used to sample medical transcriptions from various specialities.

Transcriptions are lengthy and include a patient's age, weight, blood pressure, illnesses, and more. Sorting and organising them manually is tough and time-consuming. It may now use AI.GENERATE_TABLE and LLM.

Say you need these details:

Int64 age

struct (high, low int64) blood_pressure

Weight (float64)

Conditional string array

A diagnosis (string array)

Drug strings

AI.GENERATE_TABLE() converts data into a BigQuery table for easy analysis and workflow integration.