JSON键包含连字符时，在bigquery中创建表失败 [英] Create table in bigquery fails when the JSON key contains hyphen

问题描述

我有来自Firebase Backup的 JSON 数据. 生成的数据使得每个键前面都带有连字符.

I have a JSON data from Firebase Backup. Data generated is such that every key is preceded by a hyphen.

样本数据如下:

"-GuGCJDEprMKczAMDUj8":{"deviceId":"399a649c6cee6209","dow":"Thursday","downloadFlag":"N","event":"streamStart","halfHourFull":"18h1","liveFlag":"Y","localDate":"2009-01-01","localHalfHour":1,"minutesSinceMidnight":1080,"quarterHourFull":"18q1","stationName":"hit 105","streamListenMethod":"Headphones","timestampLocal":"2009-01-01T18:00:33.679+10:00","timestampUTC":"2009-01-01T08:00:33.679Z"}

当我们尝试将该数据加载到Bigquery中时，会遇到以下提到的错误:

When we are trying to load that data into Bigquery then we are encountered with the below mentioned error:

字段必须仅包含字母，数字和下划线，以 一个字母或下划线，并且最长为128个字符.

Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

这是bigquery的限制吗? 如果是，那么这里建议的解决方案是什么.

Is this a bigquery limitation? If yes, then what's the proposed solution here.

非常感谢您的帮助/建议.

Any help/suggestion is much appreciated.

推荐答案

这是bigquery的限制吗?如果是，那么这里建议的解决方案是什么.

Is this a bigquery limitation? If yes, then what's the proposed solution here.

您需要改用其他字段名称.一种选择是将数据加载到单个STRING列中，例如通过将'CSV'用于格式为'|'的字段定界符(或数据中未出现的任何其他字符).然后，您可以使用JSON_EXTRACT_SCALAR函数从JSON中提取字段，例如:

You need to use different field names instead. One option is to load the data into a single STRING column, e.g. by using 'CSV' for the format with a field delimiter of '|' (or any other character that doesn't appear in your data). Then you can use the JSON_EXTRACT_SCALAR function to extract fields from the JSON, e.g.:

CREATE TABLE dataset.table AS
SELECT
  JSON_EXTRACT_SCALAR(json_string, '$.-GuGCJDEprMKczAMDUj8.deviceId') AS deviceId,
  JSON_EXTRACT_SCALAR(json_string, '$.-GuGCJDEprMKczAMDUj8.dow') AS dow,
  JSON_EXTRACT_SCALAR(json_string, '$.-GuGCJDEprMKczAMDUj8.downloadFlag') AS downloadFlag,
...
FROM dataset.single_column_table

这篇关于JSON键包含连字符时，在bigquery中创建表失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！