将BigQuery数据导入Vertex AI Retail时的时间戳格式问题

我正尝试将产品数据导入Vertex AI的产品目录，已遵循官方文档指定的Schema，文档要求publishTime需采用Timestamp格式（例如"2014-10-02T15:01:23Z"或"2014-10-02T15:01:23.045123456Z"）。

我的Schema #

[
  {
    "name": "id",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "type",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "categories",
    "type": "STRING",
    "mode": "REPEATED"
  },
  {
    "name": "title",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "brands",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "priceInfo",
    "type": "RECORD",
    "mode": "REQUIRED",
    "fields": [
      {
        "name": "currencyCode",
        "type": "STRING",
        "mode": "REQUIRED"
      },
      {
        "name": "price",
        "type": "FLOAT",
        "mode": "REQUIRED"
      }
    ]
  },
  {
    "name": "rating",
    "type": "INTEGER",
    "mode": "NULLABLE"
  },
  {
    "name": "publishTime",
    "type": "TIMESTAMP",
    "mode": "NULLABLE"
  },
  {
    "name": "promotions",
    "type": "BOOLEAN",
    "mode": "NULLABLE"
  }
]

插入查询语句 #

INSERT INTO `project.products.test` (
    id,
    type,
    categories,
    title,
    brands,
    priceInfo,
    rating,
    publishTime,
    promotions
) VALUES (
    'IXbgQctuTx9',
    'PRIMARY',
    ARRAY["Shopping", "Fashion", "Women"],
    'GSM W',
    'onitsukatiger',
    STRUCT("GBP" AS currencyCode, 90 AS price),  -- Price info with currency and price
    NULL,  -- Rating
    TIMESTAMP('2023-10-09T00:00:00Z'),  -- Publish time with proper format
    FALSE  -- Promotions
);

数据已成功插入BigQuery表中：

但尝试将该表导入Vertex AI时，出现以下错误：

{
  "code": 3,
  "message": "Invalid value at 'publish_time' (type.googleapis.com/google.protobuf.Timestamp): Field 'publishTime', Illegal timestamp format; timestamps must end with 'Z' or have a valid timezone offset.",
  "details": [
    {
      "@type": "type.googleapis.com/google.rpc.ResourceInfo",
      "resourceName": "gs://530498506955_eu_import_product/staging11436364033615815385/retail_products_000000000004.json:1"
    }
  ]
}

解决方法 #

方法1：修改BigQuery导出时的Timestamp格式 #

BigQuery默认导出TIMESTAMP类型字段时，可能会生成不带Z后缀的格式（比如2023-10-09 00:00:00 UTC），导致Vertex AI识别失败。可以在导出前用格式化函数将字段转为符合要求的字符串：

SELECT
  id,
  type,
  categories,
  title,
  brands,
  priceInfo,
  rating,
  FORMAT_TIMESTAMP("%Y-%m-%dT%H:%M:%EZ", publishTime) AS publishTime,
  promotions
FROM `project.products.test`

将查询结果导出到GCS，再用这个导出文件导入Vertex AI即可。

方法2：将publishTime存储为STRING类型 #

直接修改Schema中publishTime的类型为STRING，插入数据时直接传入符合格式的字符串：
修改后的Schema片段：

{
  "name": "publishTime",
  "type": "STRING",
  "mode": "NULLABLE"
}

修改后的插入语句：

INSERT INTO `project.products.test` (
    ...
    '2023-10-09T00:00:00Z',  -- 直接传入字符串格式的时间
    FALSE
);

这样导出时字段就是标准格式，不会出现转换问题。

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