GCP – Introducing AI.GENERATE_TABLE: creating structured data from gen AI models in BigQuery

The explosion of digital content from social media, smartphones, and other sources has created a massive amount of unstructured data like images, videos, and documents. To help you analyze this data, BigQuery is connected with Vertex AI, Google Cloud’s powerful AI platform, so you can use advanced AI models, like Gemini 2.5 Pro/Flash, to understand the meaning hidden within your unstructured data.

Google’s advanced AI models can analyze a wide range of data formats, from text and images to audio and video. They can extract key information like names, dates, and keywords, transforming raw data into structured insights that integrate with your existing tools. Plus, with new techniques like constrained decoding, these models can even generate structured data in JSON format, helping to ensure compatibility with your workflows.

To further streamline this process, we recently added a new BigQuery feature called AI.GENERATE_TABLE(), which builds upon the capabilities of ML.GENERATE_TEXT(). This function allows you to automatically convert the insights from your unstructured data into a structured table within BigQuery, based on the provided prompt and table schema. This streamlined process allows you to easily analyze the extracted information using your existing data analysis tools.

Extracting structured data from images

Let’s dive deeper into how this new feature works with an example that uses three images. First, you have a picture of the Seattle skyline featuring the iconic Space Needle. Next, you have a city view of New York City. Finally, you have an image of cookies and flowers, which is unrelated to cityscapes.

To use these images with BigQuery’s generative AI functions, you first need to make them accessible to BigQuery. You can do this by creating a table namely “image_dataset” that connects to the Google Cloud Storage bucket where the images are stored.

Now that you’ve prepared your image data, let’s connect to the powerful Gemini 2.5 Flash model. You do this by creating a “remote model” within BigQuery, which acts as a bridge to this advanced AI.

Now, let’s use the AI.GENERATE_TABLE() function to analyze the images. You’ll need to provide the function with two things: the remote model you created (connected to Gemini 2.5 Flash) and the table containing your images.

You’ll ask the model to “Recognize the city from the picture and output its name, belonging state, brief history, and tourist attractions. Please output nothing if the image is not a city.” To ensure the results are organized and easy to use, we’ll specify a structured output format with the following fields:

• city_name (string)

• state (string)

• brief_history (string)

• attractions (array of strings)

This format, known as a schema, ensures the output is consistent and compatible with other BigQuery tools. You’ll notice that the syntax for defining this schema is the same as the CREATE TABLE command in BigQuery.

When you run the AI.GENERATE_TABLE() function, it produces a table with five columns. Four of these columns match the schema you defined (city_name, state, brief_history, and attractions), while the fifth column contains the image URI from the input table.

As you can see, the model successfully identified the cities in the first two images, providing their names and the states in which they are found. It even generated a brief history and a list of attractions for each city based on its internal knowledge. This demonstrates the power of large language models to extract information and insights directly from images.

Extracting structured data from medical transcriptions

Now let’s see another example where you can use AI.GENERATE_TABLE to extract information from unstructured data stored in a BQ managed table. We are going to use the Kaggle Medical Transcriptions dataset which contains sample medical transcriptions from various specialities. 

Transcriptions are long and verbose and have all kinds of information, e.g. a patient’s age, weight, blood pressure, conditions, etc. It is challenging and time-consuming for people to process them manually and make it well organized. But now, we can let the LLM and AI.GENERATE_TABLE help us.

Suppose you need the following information:

• age (int64)

• blood_pressure (struct<high int64, low int64)

• weight (float64)

• conditions (array of strings)

• diagnosis (array of strings)

• medications (array of strings)

We can come up with this SQL query:

You can see that the model successfully extracted the information from the medical transcriptions and the results are organized as the schema specified with the help of AI.GENERATE_TABLE.

The AI.GENERATE_TABLE() function can help you transform your data and create a BigQuery table for easy analysis and integration with your existing workflows. To learn more about the full syntax, refer to the documentation. Have feedback on these new features or have additional feature requests? Let us know at bqml-feedback@google.com.