When you add a BigQuery connector to your Gemini Enterprise Agent Platform Vision app all the connected app model outputs will be ingested to the target table.
You can either create your own BigQuery table and specify that table when you add a BigQuery connector to the app, or let the Gemini Enterprise Agent Platform Vision app platform automatically create the table.
Automatic table creation
If you let Gemini Enterprise Agent Platform Vision app platform automatically create the table, you can specify this option when you add the BigQuery connector node.
The following dataset and table conditions apply if you want to use automatic table creation:
- Dataset: The automatically created dataset name is
visionai_dataset. - Table: The automatically created table name is
visionai_dataset.APPLICATION_ID. Error handling:
- If the table with the same name under the same dataset exists, no automatic creation happens.
Console
Open the Applications tab of the Gemini Enterprise Agent Platform Vision dashboard.
Select View app next to the name of your application from the list.
On the application builder page select BigQuery from the Connectors section.
Leave the BigQuery path field empty.
Change any other settings.
REST & CMD LINE
To let the app platform infer a table schema, use the
createDefaultTableIfNotExists field of the BigQueryConfig
when you create or update an app.
Manually create and specify a table
If you want to manage your output table manually, the table must have the required schema as a subset of the table schema.
If the existing table has incompatible schemas, the deployment is rejected.
Use the default schema
If you use the default schema for model output tables, make sure your table only contains the following required columns in the table. You can directly copy the following schema text when you create the BigQuery table. For more detailed information about creating a BigQuery table, see Create and use tables. For more information about schema specification when you create a table, see Specifying a schema.
Use the following text to describe the schema when you create a table. For
information on using the JSON column type
("type": "JSON"), see Working with JSON data in Standard SQL.
The JSON column type is recommended for annotation query. You can also use
"type" : "STRING".
[
{
"name": "ingestion_time",
"type": "TIMESTAMP",
"mode": "REQUIRED"
},
{
"name": "application",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "instance",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "node",
"type": "STRING",
"mode": "REQUIRED"
},
{
"name": "annotation",
"type": "JSON",
"mode": "REQUIRED"
}
]
Google Cloud console
In the Google Cloud console, go to the BigQuery page.
Select your project.
Select more options .
Click Create table.
In the "Schema" section, enable Edit as text.
gcloud
The following example first creates the request JSON file, then uses the
gcloud alpha bq tables create command.
First create the request JSON file:
echo "{ \"schema\": [ { \"name\": \"ingestion_time\", \"type\": \"TIMESTAMP\", \"mode\": \"REQUIRED\" }, { \"name\": \"application\", \"type\": \"STRING\", \"mode\": \"REQUIRED\" }, { \"name\": \"instance\", \"type\": \"STRING\", \"mode\": \"REQUIRED\" }, { \"name\": \"node\", \"type\": \"STRING\", \"mode\": \"REQUIRED\" }, { \"name\": \"annotation\", \"type\": \"JSON\", \"mode\": \"REQUIRED\" } ] } " >> bigquery_schema.jsonSend the
gcloudcommand. Make the following replacements:TABLE_NAME: The ID of the table or fully qualified identifier for the table.
DATASET: The id of the BigQuery dataset.
gcloud alpha bq tables create TABLE_NAME \ --dataset=DATASET \ --schema-file=./bigquery_schema.json
Sample BigQuery rows generated by a Gemini Enterprise Agent Platform Vision app:
| ingestion_time | application | instance | node | annotation |
|---|---|---|---|---|
| 2022-05-11 23:3211.911378 UTC | my_application | 5 | just-one-node | {"bytesFields": ["Ig1qdXN0LW9uZS1ub2RIGgE1Eg5teV9hcHBsaWNhdGlvbgjS+YnOzdj3Ag=="],"displayNames":["hello","world"],"ids":["12345","34567"]} |
| 2022-05-11 23:3211.911338 UTC | my_application | 1 | just-one-node | {"bytesFields": ["Ig1qdXN0LW9uZS1ub2RIGgExEg5teV9hcHBsaWNhdGlvbgiq+YnOzdj3Ag=="],"displayNames":["hello","world"],"ids":["12345","34567"]} |
| 2022-05-11 23:3211.911313 UTC | my_application | 4 | just-one-node | {"bytesFields": ["Ig1qdXN0LW9uZS1ub2RIGgE0Eg5teV9hcHBsaWNhdGlvbgiR+YnOzdj3Ag=="],"displayNames":["hello","world"],"ids":["12345","34567"]} |
| 2022-05-11 23:3212.235327 UTC | my_application | 4 | just-one-node | {"bytesFields": ["Ig1qdXN0LW9uZS1ub2RIGgE0Eg5teV9hcHBsaWNhdGlvbgi/3J3Ozdj3Ag=="],"displayNames":["hello","world"],"ids":["12345","34567"]} |
Use a customized schema
If the default schema doesn't work for your use case, you can use Cloud Run functions to generate BigQuery rows with a user-defined schema. If you use a custom schema there is no prerequisite for the BigQuery table schema.
App graph with BigQuery node selected
The BigQuery connector can be connected to any model that outputs video or proto-based annotation:
- For video input, the BigQuery connector only extracts the metadata data stored in the stream header and ingests this data to BigQuery as other model annotation outputs. The video itself is not stored.
- If your stream contains no metadata, nothing will be stored to BigQuery.
Query table data
With the default BigQuery table schema, you can perform powerful analysis after the table is populated with data.
Sample queries
You can use the following sample queries in BigQuery to gain insight from Gemini Enterprise Agent Platform Vision models.
For example, you can use BigQuery to draw a time-based curve for maximum number of detected people per minute using data from the Person / vehicle detector model with the following query:
WITH nested3 AS( WITH nested2 AS ( WITH nested