En esta página se describe la estructura recomendada para escribir peticiones eficaces para tus agentes de datos de la API Conversational Analytics que se conectan a datos de BigQuery. Estas peticiones son contextos creados que se definen como cadenas mediante el parámetro system_instruction.
Ejemplos de componentes clave de las instrucciones del sistema
En las siguientes secciones se incluyen ejemplos de componentes clave de las instrucciones del sistema en BigQuery. Estas claves incluyen las siguientes:
tablesfieldsmeasuresgolden_queriesgolden_action_plansrelationshipsglossariesadditional_descriptions
Para ver las descripciones de estos componentes clave, consulta la página de documentación Guía el comportamiento del agente con contexto creado.
Describir los datos con tables
El siguiente bloque de código YAML muestra la estructura básica de la clave tables de la tabla bigquery-public-data.thelook_ecommerce.orders:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- description: Data for customer orders in The Look fictitious e-commerce store.
- synonyms:
- sales
- orders_data
- tags:
- ecommerce
- transaction
Describir los campos que se usan con frecuencia con fields
El siguiente código YAML de ejemplo describe los campos clave, como order_id, status, created_at, num_of_items y earnings, de la tabla orders:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- fields:
- field:
- name: order_id
- description: The unique identifier for each customer order.
- field:
- name: user_id
- description: The unique identifier for each customer.
- field:
- name: status
- description: The current status of the order.
- sample_values:
- complete
- shipped
- returned
- field:
- name: created_at
- description: The timestamp when the order was created.
- field:
- name: num_of_items
- description: The total number of items in the order.
- aggregations:
- sum
- avg
- field:
- name: earnings
- description: The sales amount for the order.
- aggregations:
- sum
- avg
Definir métricas de negocio con measures
Por ejemplo, puedes definir una medida profit como el resultado de restar los ingresos a los costes, de la siguiente manera:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- measures:
- measure:
- name: profit
- description: Raw profit (earnings minus cost).
- exp: earnings - cost
- synonyms: gains
Mejorar la precisión con golden_queries
Por ejemplo, puede definir consultas de referencia para análisis habituales de los datos de la tabla orders de la siguiente manera:
- tables:
- table:
- golden_queries:
- golden_query:
- natural_language_query: How many orders are there?
- sql_query: SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
- golden_query:
- natural_language_query: How many orders were shipped?
- sql_query: >-
SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
WHERE status = 'shipped'
Describir tareas de varios pasos con golden_action_plans
Por ejemplo, puedes definir un plan de acción para mostrar los desgloses de pedidos por grupo de edad e incluir detalles sobre la consulta SQL y los pasos relacionados con la visualización:
- tables:
- table:
- golden_action_plans:
- golden_action_plan:
- natural_language_query: Show me the number of orders broken down by age group.
- action_plan:
- step: >-
Run a SQL query that joins the table
sqlgen-testing.thelook_ecommerce.orders and
sqlgen-testing.thelook_ecommerce.users to get a
breakdown of order count by age group.
- step: >-
Create a vertical bar plot using the retrieved data,
with one bar per age group.
Definir combinaciones de tablas con relationships
Por ejemplo, puede definir una relación orders_to_user entre las tablas bigquery-public-data.thelook_ecommerce.orders y bigquery-public-data.thelook_ecommerce.users de la siguiente manera:
- relationships:
- relationship:
- name: orders_to_user
- description: >-
Connects customer order data to user information with the user_id and id fields to allow an aggregated view of sales by customer demographics.
- relationship_type: many-to-one
- join_type: left
- left_table: bigquery-public-data.thelook_ecommerce.orders
- right_table: bigquery-public-data.thelook_ecommerce.users
- relationship_columns:
- left_column: user_id
- right_column: id
Explicar términos empresariales con glossaries
Por ejemplo, puedes definir términos como los estados habituales de las empresas y "OMPF" según el contexto específico de tu empresa de la siguiente manera:
- glossaries:
- glossary:
- term: complete
- description: Represents an order status where the order has been completed.
- synonyms: 'finish, done, fulfilled'
- glossary:
- term: shipped
- description: Represents an order status where the order has been shipped to the customer.
- glossary:
- term: returned
- description: Represents an order status where the customer has returned the order.
- glossary:
- term: OMPF
- description: Order Management and Product Fulfillment
Incluye más instrucciones con additional_descriptions
Por ejemplo, puedes usar la tecla additional_descriptions para proporcionar información sobre tu organización de la siguiente manera:
- additional_descriptions:
- text: All the sales data pertains to The Look, a fictitious ecommerce store.
- text: 'Orders can be of three categories: food, clothes, and electronics.'
Ejemplo: instrucciones del sistema en BigQuery
En el siguiente ejemplo se muestran instrucciones del sistema de muestra para un agente analista de ventas ficticio:
- system_instruction: >-
You are an expert sales analyst for a fictitious ecommerce store. You will answer questions about sales, orders, and customer data. Your responses should be concise and data-driven.
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- description: Data for orders in The Look, a fictitious ecommerce store.
- synonyms: sales
- tags: 'sale, order, sales_order'
- fields:
- field:
- name: order_id
- description: The unique identifier for each customer order.
- field:
- name: user_id
- description: The unique identifier for each customer.
- field:
- name: status
- description: The current status of the order.
- sample_values:
- complete
- shipped
- returned
- field:
- name: created_at
- description: >-
The date and time at which the order was created in timestamp
format.
- field:
- name: returned_at
- description: >-
The date and time at which the order was returned in timestamp
format.
- field:
- name: num_of_items
- description: The total number of items in the order.
- aggregations: 'sum, avg'
- field:
- name: earnings
- description: The sales revenue for the order.
- aggregations: 'sum, avg'
- field:
- name: cost
- description: The cost for the items in the order.
- aggregations: 'sum, avg'
- measures:
- measure:
- name: profit
- description: Raw profit (earnings minus cost).
- exp: earnings - cost
- synonyms: gains
- golden_queries:
- golden_query:
- natural_language_query: How many orders are there?
- sql_query: SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
- golden_query:
- natural_language_query: How many orders were shipped?
- sql_query: >-
SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
WHERE status = 'shipped'
- golden_action_plans:
- golden_action_plan:
- natural_language_query: Show me the number of orders broken down by age group.
- action_plan:
- step: >-
Run a SQL query that joins the table
sqlgen-testing.thelook_ecommerce.orders and
sqlgen-testing.thelook_ecommerce.users to get a
breakdown of order count by age group.
- step: >-
Create a vertical bar plot using the retrieved data,
with one bar per age group.
- table:
- name: bigquery-public-data.thelook_ecommerce.users
- description: Data for users in The Look, a fictitious ecommerce store.
- synonyms: customers
- tags: 'user, customer, buyer'
- fields:
- field:
- name: id
- description: The unique identifier for each user.
- field:
- name: first_name
- description: The first name of the user.
- tag: person
- sample_values: 'alex, izumi, nur'
- field:
- name: last_name
- description: The first name of the user.
- tag: person
- sample_values: 'warmer, stilles, smith'
- field:
- name: age_group
- description: The age demographic group of the user.
- sample_values:
- 18-24
- 25-34
- 35-49
- 50+
- field:
- name: email
- description: The email address of the user.
- tag: contact
- sample_values: '222larabrown@gmail.com, cloudysanfrancisco@gmail.com'
- golden_queries:
- golden_query:
- natural_language_query: How many unique customers are there?
- sql_query: >-
SELECT COUNT(DISTINCT id) FROM
bigquery-public-data.thelook_ecommerce.users
- golden_query:
- natural_language_query: How many users in the 25-34 age group have a cymbalgroup email address?
- sql_query: >-
SELECT COUNT(DISTINCT id) FROM
bigquery-public-data.thelook_ecommerce.users WHERE users.age_group =
'25-34' AND users.email LIKE '%@cymbalgroup.com';
- relationships:
- relationship:
- name: orders_to_user
- description: >-
Connects customer order data to user information with the user_id and id fields to allow an aggregated view of sales by customer demographics.
- relationship_type: many-to-one
- join_type: left
- left_table: bigquery-public-data.thelook_ecommerce.orders
- right_table: bigquery-public-data.thelook_ecommerce.users
- relationship_columns:
- left_column: user_id
- right_column: id
- glossaries:
- glossary:
- term: complete
- description: Represents an order status where the order has been completed.
- synonyms: 'finish, done, fulfilled'
- glossary:
- term: shipped
- description: Represents an order status where the order has been shipped to the customer.
- glossary:
- term: returned
- description: Represents an order status where the customer has returned the order.
- glossary:
- term: OMPF
- description: Order Management and Product Fulfillment
- additional_descriptions:
- text: All the sales data pertains to The Look, a fictitious ecommerce store.
- text: 'Orders can be of three categories: food, clothes, and electronics.'