Dialogflow交互日志BigQuery数据建模与查询分析方案咨询

一、扁平化单表VS拆分多表的选择 #

• 若分析需求以会话全链路视角为主（如统计会话时长、意图触发占比、用户提问趋势），优先选择单表扁平化，避免跨表关联的复杂度，提升查询效率。
• 若JSON字段存在多层嵌套且需高频独立分析（如单独拆解request中的用户参数、response中的多轮回复类型），可考虑将核心JSON字段拆为独立明细表，但建议先通过视图实现，而非直接创建物理表，避免数据冗余。

二、数据建模思路 #

1. 核心会话层（单表扁平化） #

保留原始表的基础标识字段（project_id、conversation_name、request_time等），将JSON字段中常用的层级字段提取为顶级列，构建面向会话分析的核心模型：

• 从request提取用户提问内容、匹配意图名称
• 从response提取机器人回复内容、回复类型
• 从derived_data提取意图置信度、用户上下文信息

2. 明细扩展层（可选拆分） #

针对需深度分析的嵌套JSON对象，创建独立明细表，通过conversation_name和turn_position与核心表关联：

• 会话信号明细表：存储conversation_signals中的用户行为事件（如停留时长、操作动作）
• 多轮回复明细表：存储partial_responses中的每一条分步回复内容

三、扁平化具体实现方法 #

1. 临时查询扁平化（即时分析） #

直接在查询中提取JSON字段，适合临时分析需求：

SELECT
  project_id,
  agent_id,
  conversation_name,
  turn_position,
  request_time,
  language_code,
  -- 提取request核心字段
  request.query_text AS user_query,
  request.intent.display_name AS matched_intent,
  -- 提取response回复内容
  ARRAY(SELECT text FROM UNNEST(response.fulfillment_messages)) AS bot_responses,
  -- 提取derived_data置信度
  derived_data.intent_confidence AS intent_confidence_score,
  -- 保留原始JSON字段用于特殊分析
  request,
  response,
  derived_data
FROM `<your_dataset_name>.dialogflow_bigquery_export_data`

2. 创建扁平化视图（复用性高） #

若需频繁使用扁平化结构，创建视图替代物理表，避免数据重复存储：

CREATE OR REPLACE VIEW `<your_dataset_name>.dialogflow_flattened_view` AS
SELECT
  project_id,
  agent_id,
  conversation_name,
  turn_position,
  request_time,
  language_code,
  request.query_text AS user_query,
  request.intent.display_name AS matched_intent,
  request.parameters AS user_parameters,
  (SELECT text FROM UNNEST(response.fulfillment_messages) LIMIT 1) AS primary_bot_response,
  derived_data.intent_confidence AS intent_confidence_score,
  conversation_signals.user_engagement.duration AS user_engagement_duration
FROM `<your_dataset_name>.dialogflow_bigquery_export_data`

3. 物理扁平化表（大规模高频分析） #

针对数据量较大、查询性能要求高的场景，创建分区+聚类的物理扁平化表，可通过BigQuery调度任务定时更新：

CREATE OR REPLACE TABLE `<your_dataset_name>.dialogflow_flattened_table`
PARTITION BY DATE(request_time)
CLUSTER BY conversation_name, matched_intent
AS
SELECT
  project_id,
  agent_id,
  conversation_name,
  turn_position,
  request_time,
  language_code,
  request.query_text AS user_query,
  request.intent.display_name AS matched_intent,
  request.parameters AS user_parameters,
  ARRAY(SELECT text FROM UNNEST(response.fulfillment_messages)) AS bot_responses,
  derived_data.intent_confidence AS intent_confidence_score,
  conversation_signals.user_engagement.duration AS user_engagement_duration
FROM `<your_dataset_name>.dialogflow_bigquery_export_data`

内容的提问来源于stack exchange，提问作者Awssylearn