BigQuery上传含Byte类型数据的JSON文件时出现Schema不匹配报错

问题原因 #

你在建表时将rightData、leftData两个字段定义为BYTES二进制类型，但JSON格式本身没有原生二进制类型，你上传的JSON里这两个字段都是普通的十六进制字符串，BigQuery执行导入时默认会将引号包裹的这类内容识别为STRING字符串类型，和表定义的BYTES类型冲突，因此抛出类型不匹配的报错。此外你JSON里的iterationID字段值也用引号包裹为字符串格式，和表定义的integer整型也存在类型不匹配的隐患，后续即使解决了二进制字段的问题也可能触发同类报错。

解决方案 #

方案1：调整JSON数据格式适配表结构 #

BigQuery导入JSON数据时，BYTES类型的字段需要传入base64编码后的内容，或在十六进制字符串前加base64:前缀标识；同时要去掉iterationID值的引号适配整型要求，修改后的JSON示例如下：

{
    "rightData": "base64:F16060D6D7E260C607E40B1106B02000057C",
    "leftData": "base64:C1D8D9D9F0F360F8F1F8606060D6D7E260C6F3F5F4F0F5C1C8C1D8F8F8606060F9F1F9",
    "iterationID": 10
}

该方案适合JSON数据量小、修改成本低的场景。

方案2：导入时做显式类型转换 #

如果已有大量现成JSON文件不想批量修改，可以先把所有字段按STRING类型导入临时表，再通过SQL做类型转换写入目标正式表：

INSERT INTO test_table (rightData, leftData, iterationID)
SELECT 
  FROM_HEX(rightData) AS rightData,
  FROM_HEX(leftData) AS leftData,
  CAST(iterationID AS INT64) AS iterationID
FROM 临时表名称

方案3：调整表结构适配JSON数据类型 #

如果业务层面只需要存储十六进制文本，不需要直接存储二进制格式，可以直接修改表字段类型匹配JSON数据格式：

ALTER TABLE test_table 
ALTER COLUMN rightData SET DATA TYPE STRING,
ALTER COLUMN leftData SET DATA TYPE STRING,
ALTER COLUMN iterationID SET DATA TYPE STRING;

后续需要做二进制计算时，再调用FROM_HEX函数实时转换即可。

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