Langchain对接BigQuery含RECORD字段表时出现报错问题

问题 #

我正尝试使用Langchain、BigQuery和Vertex LLM构建一个简单的Text to Query流水线。初始化Langchain的SQLDatabase对象过程正常：

from sqlalchemy import *
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import *
from langchain import SQLDatabase, SQLDatabaseChain

dataset_id = 'ecomm'
table_uri = f"bigquery://{PROJECT_ID}/{dataset_id}"
engine = create_engine(f"bigquery://{PROJECT_ID}/{dataset_id}")

db = SQLDatabase(engine=engine,metadata=MetaData(bind=engine),include_tables=['retail_products'],sample_rows_in_table_info=3)

但调用print(db.table_info)时出现如下错误：

BadRequest: 400 Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRUCT<text ARRAY<STRING>, numbers ARRAY<FLOAT64>, searchable BOOL, ...>>> at [1:406]
[SQL: SELECT `retail_products`.`name`, `retail_products`.`id`, `retail_products`.`type`, `retail_products`.`primaryProductId`, `retail_products`.`collectionMemberIds`, `retail_products`.`gtin`, `retail_products`.`categories`, `retail_products`.`title`, `retail_products`.`brands`, `retail_products`.`description`, `retail_products`.`languageCode`, `retail_products`.`attributes`, `retail_products`.`attributes`.`key`, `retail_products`.`attributes`.`value`, `retail_products`.`attributes`.`value`.`text`, `retail_products`.`attributes`.`value`.`numbers`, `retail_products`.`attributes`.`value`.`searchable`, `retail_products`.`attributes`.`value`.`indexable`, `retail_products`.`tags`, `retail_products`.`priceInfo`, `retail_products`.`priceInfo`.`currencyCode`, `retail_products`.`priceInfo`.`price`, `retail_products`.`priceInfo`.`originalPrice`, `retail_products`.`priceInfo`.`cost`, `retail_products`.`priceInfo`.`priceEffectiveTime`, `retail_products`.`priceInfo`.`priceExpireTime`, `retail_products`.`rating`, `retail_products`.`rating`.`ratingCount`, `retail_products`.`rating`.`averageRating`, `retail_products`.`rating`.`ratingHistogram`, `retail_products`.`expireTime`, `retail_products`.`ttl`, `retail_products`.`ttl`.`seconds`, `retail_products`.`ttl`.`nanos`, `retail_products`.`availableTime`, `retail_products`.`availability`, `retail_products`.`availableQuantity`, `retail_products`.`fulfillmentInfo`, `retail_products`.`fulfillmentInfo`.`type`, `retail_products`.`fulfillmentInfo`... FROM `retail_products` LIMIT %

涉及的retail_products表结构如下：

问题出在表中的RECORD类型字段，无嵌套字段的简单表可正常运行，求解决办法。

解决方案 #

方法1：排除嵌套字段，仅保留顶级字段 #

Langchain自动生成表信息时，会尝试展开嵌套RECORD字段，但对于attributes这类REPEATED类型的RECORD，直接访问子字段会触发SQL错误。可以通过exclude_columns参数排除所有嵌套字段，只保留顶级非嵌套字段：

db = SQLDatabase(
    engine=engine,
    metadata=MetaData(bind=engine),
    include_tables=['retail_products'],
    sample_rows_in_table_info=3,
    exclude_columns=['attributes', 'priceInfo', 'rating', 'ttl', 'fulfillmentInfo', 'images', 'audience', 'colorInfo', 'promotions']
)

这样生成表信息时只会包含顶级字段，避免访问REPEATED RECORD子字段的错误。

方法2：自定义表信息描述 #

如果需要保留嵌套字段的信息供LLM理解，但不想触发自动生成SQL的错误，可以手动编写表结构描述，替换Langchain自动生成的table_info：

custom_table_info = """
表名：retail_products
字段说明：
- name: STRING，可为空
- id: STRING，必填
- type: STRING，可为空
- primaryProductId: STRING，可为空
- collectionMemberIds: STRING数组，可重复
- gtin: STRING，可为空
- categories: STRING数组，可重复
- title: STRING，必填
- brands: STRING数组，可重复
- description: STRING，可为空
- languageCode: STRING，可为空
- attributes: 可重复的STRUCT类型，包含key(STRING)、value(STRUCT，含text数组、numbers数组、searchable布尔值等)
- tags: STRING数组，可重复
- priceInfo: STRUCT类型，包含currencyCode、price、originalPrice、cost等字段
- rating: STRUCT类型，包含ratingCount、averageRating、ratingHistogram等字段
- ttl: STRUCT类型，包含seconds、nanos字段
- fulfillmentInfo: 可重复的STRUCT类型，包含type等字段
- images: 可重复的STRUCT类型
- audience: STRUCT类型
- colorInfo: STRUCT类型
- sizes: STRING数组，可重复
- materials: STRING数组，可重复
- patterns: STRING数组，可重复
- conditions: STRING数组，可重复
- retrievableFields: STRING，可为空
- publishTime: STRING，可为空
- promotions: 可重复的STRUCT类型
"""

# 初始化SQLDatabase后替换自动生成的table_info
db = SQLDatabase(
    engine=engine,
    metadata=MetaData(bind=engine),
    include_tables=['retail_products'],
    sample_rows_in_table_info=3
)
db._table_info = custom_table_info

这种方式既保留了嵌套字段的上下文信息，又避免了自动生成错误SQL的问题。

方法3：预展平嵌套字段（适合复杂查询场景） #

如果需要基于嵌套字段做精确查询，可以在BigQuery中创建展平后的视图，将REPEATED RECORD字段拆分成多行或扁平化字段，再让Langchain连接这个视图：

比如创建展平attributes字段的视图：

CREATE OR REPLACE VIEW ecomm.retail_products_flattened AS
SELECT
  name, id, type, primaryProductId, collectionMemberIds, gtin, categories, title, brands,
  description, languageCode, tags, expireTime, availableTime, availability, availableQuantity,
  uri, sizes, materials, patterns, conditions, retrievableFields, publishTime,
  -- 展平attributes字段
  attr.key AS attr_key,
  attr.value.text AS attr_text,
  attr.value.numbers AS attr_numbers,
  attr.value.searchable AS attr_searchable,
  -- 展平priceInfo字段
  priceInfo.currencyCode AS price_currency,
  priceInfo.price AS current_price,
  priceInfo.originalPrice AS original_price,
  -- 展平其他需要的嵌套字段
  rating.averageRating AS avg_rating,
  rating.ratingCount AS rating_count
FROM ecomm.retail_products
-- 针对REPEATED类型的字段用UNNEST展开
LEFT JOIN UNNEST(attributes) AS attr

之后在Langchain中连接这个视图：

db = SQLDatabase(
    engine=create_engine(f"bigquery://{PROJECT_ID}/{dataset_id}"),
    include_tables=['retail_products_flattened'],
    sample_rows_in_table_info=3
)

这种方法适合需要基于嵌套字段进行查询的场景，但需要根据实际需求设计视图的展平逻辑。

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