Generar datos estructurados con la función AI.GENERATE_TABLE

En este documento se explica cómo generar datos estructurados con un modelo de Gemini y, a continuación, dar formato a la respuesta del modelo con un esquema SQL.

Para ello, debes completar las siguientes tareas:

Roles obligatorios

Para crear un modelo remoto y usar la función AI.GENERATE_TABLE, necesitas los siguientes roles de Gestión de Identidades y Accesos (IAM):

  • Crear y usar conjuntos de datos, tablas y modelos de BigQuery: Editor de datos de BigQuery (roles/bigquery.dataEditor) en tu proyecto.

  • Crear, delegar y usar conexiones de BigQuery: Administrador de conexiones de BigQuery (roles/bigquery.connectionsAdmin) en tu proyecto.

    Si no tiene configurada una conexión predeterminada, puede crear una y definirla al ejecutar la instrucción CREATE MODEL. Para ello, debes tener el rol Administrador de BigQuery (roles/bigquery.admin) en tu proyecto. Para obtener más información, consulta Configurar la conexión predeterminada.

  • Concede permisos a la cuenta de servicio de la conexión: administrador de gestión de identidades y accesos del proyecto (roles/resourcemanager.projectIamAdmin) en el proyecto que contiene el endpoint de Vertex AI. Este es el proyecto actual de los modelos remotos que creas especificando el nombre del modelo como endpoint. Es el proyecto identificado en la URL de los modelos remotos que creas especificando una URL como endpoint.

  • Crear tareas de BigQuery: Usuario de tareas de BigQuery (roles/bigquery.jobUser) en tu proyecto.

Estos roles predefinidos contienen los permisos necesarios para realizar las tareas descritas en este documento. Para ver los permisos exactos que se necesitan, despliega la sección Permisos necesarios:

Permisos obligatorios

  • Crea un conjunto de datos: bigquery.datasets.create
  • Crear, delegar y usar una conexión: bigquery.connections.*
  • Define los permisos de la cuenta de servicio: resourcemanager.projects.getIamPolicy y resourcemanager.projects.setIamPolicy
  • Crea un modelo y ejecuta la inferencia:
    • bigquery.jobs.create
    • bigquery.models.create
    • bigquery.models.getData
    • bigquery.models.updateData
    • bigquery.models.updateMetadata
  • Consultar datos de tabla: bigquery.tables.getData

También puedes obtener estos permisos con roles personalizados u otros roles predefinidos.

Antes de empezar

  1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  2. Verify that billing is enabled for your Google Cloud project.

  3. Enable the BigQuery, BigQuery Connection, and Vertex AI APIs.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the APIs

Crear conjunto de datos

Crea un conjunto de datos de BigQuery para que contenga tus recursos:

Consola

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a la página de BigQuery

  2. En el panel Explorador, haz clic en el nombre de tu proyecto.

  3. Haz clic en Ver acciones > Crear conjunto de datos.

  4. En la página Crear conjunto de datos, haz lo siguiente:

    • En ID del conjunto de datos, escribe un nombre para el conjunto de datos.

    • En Tipo de ubicación, seleccione una ubicación para el conjunto de datos.

    • Haz clic en Crear conjunto de datos.

bq

  1. Para crear un conjunto de datos, usa el comando bq mk con la marca --location:

    bq --location=LOCATION mk -d DATASET_ID

    Haz los cambios siguientes:

    • LOCATION: la ubicación del conjunto de datos.
    • DATASET_ID es el ID del conjunto de datos que vas a crear.

  2. Confirma que se ha creado el conjunto de datos:

    bq ls

Crear una conexión

Puedes saltarte este paso si tienes una conexión predeterminada configurada o si tienes el rol Administrador de BigQuery.

Crea una conexión de recursos de Cloud para que la use el modelo remoto y obtén la cuenta de servicio de la conexión. Crea la conexión en la misma ubicación que el conjunto de datos que has creado en el paso anterior.

Selecciona una de las opciones siguientes:

Consola

  1. Ve a la página BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, haga clic en Añadir datos:

    El elemento de la interfaz de usuario para añadir datos.

    Se abrirá el cuadro de diálogo Añadir datos.

  3. En el panel Filtrar por, en la sección Tipo de fuente de datos, selecciona Aplicaciones empresariales.

    También puede introducir Vertex AI en el campo Buscar fuentes de datos.

  4. En la sección Fuentes de datos destacadas, haga clic en Vertex AI.

  5. Haz clic en la tarjeta de solución Modelos de Vertex AI: federación de BigQuery.

  6. En la lista Tipo de conexión, selecciona Modelos remotos, funciones remotas y BigLake (recurso de Cloud) de Vertex AI.

  7. En el campo ID de conexión, introduce un nombre para la conexión.

  8. Haga clic en Crear conexión.

  9. Haz clic en Ir a la conexión.

  10. En el panel Información de conexión, copia el ID de la cuenta de servicio para usarlo en un paso posterior.

bq

  1. En un entorno de línea de comandos, crea una conexión:

    bq mk --connection --location=REGION --project_id=PROJECT_ID \
    --connection_type=CLOUD_RESOURCE CONNECTION_ID

    El parámetro --project_id anula el proyecto predeterminado.

    Haz los cambios siguientes:

    • REGION: tu región de conexión
    • PROJECT_ID: tu ID de proyecto Google Cloud
    • CONNECTION_ID: un ID para tu conexión

    Cuando creas un recurso de conexión, BigQuery crea una cuenta de servicio del sistema única y la asocia a la conexión.

    Solución de problemas: si aparece el siguiente error de conexión, actualiza el SDK de Google Cloud:

    Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...

  2. Obtén y copia el ID de la cuenta de servicio para usarlo en un paso posterior:

    bq show --connection PROJECT_ID.REGION.CONNECTION_ID

    El resultado debería ser similar al siguiente:

    name                          properties
1234.REGION.CONNECTION_ID     {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}

Terraform

Usa el recurso google_bigquery_connection.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta Configurar la autenticación para bibliotecas de cliente.

En el siguiente ejemplo se crea una conexión de recursos de Cloud llamada my_cloud_resource_connection en la región US:


# This queries the provider for project information.
data "google_project" "default" {}

# This creates a cloud resource connection in the US region named my_cloud_resource_connection.
# Note: The cloud resource nested object has only one output field - serviceAccountId.
resource "google_bigquery_connection" "default" {
  connection_id = "my_cloud_resource_connection"
  project       = data.google_project.default.project_id
  location      = "US"
  cloud_resource {}
}

Para aplicar la configuración de Terraform en un proyecto, sigue los pasos que se indican en las siguientes secciones. Google Cloud

Preparar Cloud Shell

  1. Abre Cloud Shell.

  2. Define el Google Cloud proyecto Google Cloud predeterminado en el que quieras aplicar tus configuraciones de Terraform.

    Solo tiene que ejecutar este comando una vez por proyecto y puede hacerlo en cualquier directorio.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Las variables de entorno se anulan si defines valores explícitos en el archivo de configuración de Terraform.

Preparar el directorio

Cada archivo de configuración de Terraform debe tener su propio directorio (también llamado módulo raíz).

  1. En Cloud Shell, crea un directorio y un archivo nuevo en ese directorio. El nombre del archivo debe tener la extensión .tf. Por ejemplo, main.tf. En este tutorial, nos referiremos al archivo como main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Si estás siguiendo un tutorial, puedes copiar el código de ejemplo de cada sección o paso.

    Copia el código de ejemplo en el archivo main.tf que acabas de crear.

    También puedes copiar el código de GitHub. Se recomienda cuando el fragmento de Terraform forma parte de una solución integral.

  3. Revisa y modifica los parámetros de ejemplo para aplicarlos a tu entorno.
  4. Guarda los cambios.
  5. Inicializa Terraform. Solo tienes que hacerlo una vez por directorio. 
    terraform init

    Si quieres usar la versión más reciente del proveedor de Google, incluye la opción -upgrade: 

    terraform init -upgrade

Aplica los cambios

  1. Revisa la configuración y comprueba que los recursos que va a crear o actualizar Terraform se ajustan a tus expectativas: 
    terraform plan

    Haga las correcciones necesarias en la configuración.

  2. Aplica la configuración de Terraform ejecutando el siguiente comando e introduciendo yes en la petición: 
    terraform apply

    Espera hasta que Terraform muestre el mensaje "Apply complete!".

  3. Abre tu Google Cloud proyecto para ver los resultados. En la Google Cloud consola, ve a tus recursos en la interfaz de usuario para asegurarte de que Terraform los ha creado o actualizado.

Dar acceso a la cuenta de servicio

Asigna el rol Usuario de Vertex AI a la cuenta de servicio de la conexión.

Si tiene previsto especificar el endpoint como una URL al crear el modelo remoto (por ejemplo, endpoint = 'https://us-central1-aiplatform.googleapis.com/v1/projects/myproject/locations/us-central1/publishers/google/models/gemini-2.5-flash'), otorgue este rol en el mismo proyecto que especifique en la URL.

Si tienes previsto especificar el endpoint usando el nombre del modelo al crear el modelo remoto (por ejemplo, endpoint = 'gemini-2.5-flash'), otorga este rol en el mismo proyecto en el que vayas a crear el modelo remoto.

Si se asigna el rol en otro proyecto, se produce el error bqcx-1234567890-wxyz@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource.

Para conceder el rol, sigue estos pasos:

Consola

  1. Ve a la página IAM y administración.

    Ir a IAM y administración

  2. Haz clic en Añadir.

    Se abrirá el cuadro de diálogo Añadir principales.

  3. En el campo Nuevos principales, introduce el ID de la cuenta de servicio que has copiado anteriormente.

  4. En el campo Selecciona un rol, elige Vertex AI y, a continuación, Usuario de Vertex AI.

  5. Haz clic en Guardar.

gcloud

Usa el comando gcloud projects add-iam-policy-binding.

gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/aiplatform.user' --condition=None

Haz los cambios siguientes:

  • PROJECT_NUMBER: tu número de proyecto
  • MEMBER: el ID de la cuenta de servicio que has copiado antes

Crear un modelo remoto de BigQuery ML

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el editor de SQL, crea un modelo remoto:

    CREATE OR REPLACE MODEL
`PROJECT_ID.DATASET_ID.MODEL_NAME`
REMOTE WITH CONNECTION {DEFAULT | `PROJECT_ID.REGION.CONNECTION_ID`}
OPTIONS (ENDPOINT = 'ENDPOINT');

    Haz los cambios siguientes:

    • PROJECT_ID: tu ID de proyecto
    • DATASET_ID: ID del conjunto de datos que contendrá el modelo. Este conjunto de datos debe estar en la misma ubicación que la conexión que estés usando.
    • MODEL_NAME: el nombre del modelo
    • REGION: la región que usa la conexión
    • CONNECTION_ID: el ID de tu conexión de BigQuery

      Cuando consultas los detalles de la conexión en la consola de Google Cloud , este es el valor de la última sección del ID de conexión completo que se muestra en ID de conexión, por ejemplo: projects/myproject/locations/connection_location/connections/myconnection

    • ENDPOINT: el nombre del modelo de Gemini que se va a usar. En los modelos de Gemini compatibles, puedes especificar el endpoint global para mejorar la disponibilidad. Para obtener más información, consulta ENDPOINT.

Generar datos estructurados

Genera datos estructurados usando la función AI.GENERATE_TABLE con un modelo remoto y usando datos de peticiones de una columna de una tabla:

SELECT *
FROM AI.GENERATE_TABLE(
  MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`,
  [TABLE `PROJECT_ID.DATASET_ID.TABLE_NAME` / (PROMPT_QUERY)],
  STRUCT(TOKENS AS max_output_tokens, TEMPERATURE AS temperature,
  TOP_P AS top_p, STOP_SEQUENCES AS stop_sequences,
  SAFETY_SETTINGS AS safety_settings,
  OUTPUT_SCHEMA AS output_schema)
);

Haz los cambios siguientes:

  • PROJECT_ID: tu ID de proyecto.
  • DATASET_ID: el ID del conjunto de datos que contiene el modelo.
  • MODEL_NAME: el nombre del modelo.
  • TABLE_NAME: el nombre de la tabla que contiene la petición. Esta tabla debe tener una columna llamada prompt o puedes usar un alias para usar una columna con otro nombre.
  • PROMPT_QUERY: la consulta de GoogleSQL que genera los datos de la petición. El valor de la petición se puede extraer de una columna o se puede especificar como un valor struct con un número arbitrario de subcampos de nombre de columna y cadena. Por ejemplo, SELECT ('Analyze the sentiment in ', feedback_column, 'using the following categories: positive, negative, neutral') AS prompt.
  • TOKENS: un valor INT64 que define el número máximo de tokens que se pueden generar en la respuesta. Este valor debe estar en el intervalo [1,8192]. Especifica un valor inferior para las respuestas más cortas y un valor superior para las respuestas más largas. El valor predeterminado es 128.
  • TEMPERATURE: un valor FLOAT64 en el intervalo [0.0,2.0] que controla el grado de aleatoriedad en la selección de tokens. El valor predeterminado es 1.0.

    Los valores más bajos de temperature son adecuados para las peticiones que requieren una respuesta más determinista y menos abierta o creativa, mientras que los valores más altos de temperature pueden dar lugar a resultados más diversos o creativos. El valor 0 de temperature es determinista, lo que significa que siempre se selecciona la respuesta con la probabilidad más alta.

  • TOP_P: un valor FLOAT64 en el intervalo [0.0,1.0] ayuda a determinar la probabilidad de los tokens seleccionados. Especifica un valor más bajo para obtener respuestas menos aleatorias y un valor más alto para obtener respuestas más aleatorias. El valor predeterminado es 0.95.
  • STOP_SEQUENCES: un valor ARRAY<STRING> que elimina las cadenas especificadas si se incluyen en las respuestas del modelo. Las cadenas deben coincidir exactamente, incluidas las mayúsculas y las minúsculas. El valor predeterminado es un array vacío.
  • SAFETY_SETTINGS: un valor ARRAY<STRUCT<STRING AS category, STRING AS threshold>> que configura los umbrales de seguridad del contenido para filtrar las respuestas. El primer elemento de la struct especifica una categoría de daño y el segundo, un umbral de bloqueo correspondiente. El modelo filtra el contenido que infringe estos ajustes. Solo puedes especificar cada categoría una vez. Por ejemplo, no puedes especificar STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_MEDIUM_AND_ABOVE' AS threshold) y STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_ONLY_HIGH' AS threshold). Si no hay ningún ajuste de seguridad para una categoría determinada, se usará el ajuste de seguridad BLOCK_MEDIUM_AND_ABOVE.

    Las categorías admitidas son las siguientes:

    • HARM_CATEGORY_HATE_SPEECH
    • HARM_CATEGORY_DANGEROUS_CONTENT
    • HARM_CATEGORY_HARASSMENT
    • HARM_CATEGORY_SEXUALLY_EXPLICIT

    Los umbrales admitidos son los siguientes:

    • BLOCK_NONE (Restringido)
    • BLOCK_LOW_AND_ABOVE
    • BLOCK_MEDIUM_AND_ABOVE (predeterminado)
    • BLOCK_ONLY_HIGH
    • HARM_BLOCK_THRESHOLD_UNSPECIFIED

    Para obtener más información, consulta las categorías de daño y el artículo Cómo configurar filtros de contenido.

  • OUTPUT_SCHEMA: un valor STRING que especifica el formato de la respuesta del modelo. El valor de output_schema debe ser una definición de esquema SQL, similar a la que se usa en la instrucción CREATE TABLE. Se admiten los siguientes tipos de datos:
    • INT64
    • FLOAT64
    • BOOL
    • STRING
    • ARRAY
    • STRUCT

    En el caso de los modelos de Gemini 1.5, solo debes especificar el tipo de datos FLOAT64 si tienes la certeza de que el valor devuelto no será un número entero. En ocasiones, estos modelos pueden devolver valores INT64 en lugar de valores FLOAT64 para números redondos (por ejemplo, 2 en lugar de 2.0), lo que puede provocar un error de análisis en la consulta.

    Cuando se usa el argumento output_schema para generar datos estructurados basándose en las peticiones de una tabla, es importante entender los datos de la petición para especificar un esquema adecuado.

    Por ejemplo, supongamos que está analizando el contenido de reseñas de películas de una tabla que tiene los siguientes campos:

    • movie_id
    • reseña
    • petición

    A continuación, puedes crear un texto de petición ejecutando una consulta similar a la siguiente: 

    UPDATE mydataset.movie_review
SET prompt = CONCAT('Extract the key words and key sentiment from the text below: ', review)
WHERE review IS NOT NULL;

    También puede especificar un valor de output_schema similar a "keywords ARRAY<STRING>, sentiment STRING" AS output_schema.

Ejemplos

En el siguiente ejemplo se muestra una solicitud que toma datos de una tabla y proporciona un esquema SQL para dar formato a la respuesta del modelo:

SELECT
*
FROM
AI.GENERATE_TABLE( MODEL `mydataset.gemini_model`,
  TABLE `mydataset.mytable`,
  STRUCT("keywords ARRAY<STRING>, sentiment STRING" AS output_schema));

En el siguiente ejemplo se muestra una solicitud que toma datos de una consulta y proporciona un esquema SQL para dar formato a la respuesta del modelo:

SELECT *
FROM
  AI.GENERATE_TABLE(
    MODEL `mydataset.gemini_model`,
    (
      SELECT
        'John Smith is a 20-year old single man living at 1234 NW 45th St, Kirkland WA, 98033. He has two phone numbers 123-123-1234, and 234-234-2345. He is 200.5 pounds.'
          AS prompt
    ),
    STRUCT("address STRUCT<street_address STRING, city STRING, state STRING, zip_code STRING>, age INT64, is_married BOOL, name STRING, phone_number ARRAY<STRING>, weight_in_pounds FLOAT64"
        AS output_schema, 8192 AS max_output_tokens));