BigQuery:存储半结构化JSON数据

Question

我有可以拥有不同json键的数据，我希望将所有这些数据存储在bigquery中，然后在以后探索可用的字段。

我的结构会是这样的：

[
{id: 1111, data: {a:27, b:62, c: 'string'} },
{id: 2222, data: {a:27, c: 'string'} },
{id: 3333, data: {a:27} },
{id: 4444, data: {a:27, b:62, c:'string'} },
]

我想使用STRUCT类型，但似乎所有字段都需要声明？

然后，我希望能够查询和查看每个键出现的频率，基本上可以像在自己的列中一样使用a键对所有记录运行查询。

附带注意:这些数据来自URL查询字符串，也许有人认为最好按下完整的url并使用函数来运行分析？

Answer 1

有两种存储半结构化数据的主要方法，如您在示例中所使用的：

选项1:存储JSON字符串

您可以将data字段存储为JSON，然后使用JSON_EXTRACT函数提取它可以找到的值，它将返回任何它找不到的值的NULL。

既然您提到需要对字段进行数学分析，那么让我们为a和b的值做一个简单的b

# Creating an example table using the WITH statement, this would not be needed
# for a real table.
WITH records AS (
  SELECT 1111 AS id, "{\"a\":27, \"b\":62, \"c\": \"string\"}" as data
  UNION ALL
  SELECT 2222 AS id, "{\"a\":27, \"c\": \"string\"}" as data
  UNION ALL
  SELECT 3333 AS id, "{\"a\":27}" as data
  UNION ALL
  SELECT 4444 AS id, "{\"a\":27, \"b\":62, \"c\": \"string\"}" as data
)

# Example Query
SELECT SUM(aValue) AS aSum, SUM(bValue) AS bSum FROM (
  SELECT id, 
    CAST(JSON_EXTRACT(data, "$.a") AS INT64) AS aValue, # Extract & cast as an INT
    CAST(JSON_EXTRACT(data, "$.b") AS INT64) AS bValue  # Extract & cast as an INT
  FROM records
)

# results
# Row | aSum | bSum
# 1   | 108  | 124

这种方法有一些优点和缺点：

Pros

• 语法相当直白。
• 较少出错

Cons

• 存储成本会稍微高一些，因为您必须存储所有字符才能序列化为JSON。
• 查询将比使用纯原生SQL运行得慢。

选项2:重复字段

BigQuery有对重复字段的支持，允许您使用您的结构并在本机中用SQL表示它。

使用同样的例子，我们将如何做到这一点：

## Using a with to create a sample table
WITH records AS (SELECT * FROM UNNEST(ARRAY<STRUCT<id INT64, data ARRAY<STRUCT<key STRING, value STRING>>>>[
  (1111, [("a","27"),("b","62"),("c","string")]),
  (2222, [("a","27"),("c","string")]),
  (3333, [("a","27")]),
  (4444, [("a","27"),("b","62"),("c","string")])
])),
## Using another WITH table to take records and unnest them to be joined later
recordsUnnested AS (
  SELECT id, key, value
  FROM records, UNNEST(records.data) AS keyVals
)

SELECT SUM(aValue) AS aSum, SUM(bValue) AS bSum
FROM (
  SELECT R.id, CAST(RA.value AS INT64) AS aValue, CAST(RB.value AS INT64) AS bValue
  FROM records R
    LEFT JOIN recordsUnnested RA ON R.id = RA.id AND RA.key = "a"
    LEFT JOIN recordsUnnested RB ON R.id = RB.id AND RB.key = "b"
)

# results
# Row | aSum | bSum
# 1   | 108  | 124

正如您所看到的，要执行类似的操作，它仍然相当复杂。您还必须在必要时将字符串之类的项存储到其他值中，因为不能在重复字段中混合类型。

Pros

• 商店大小将小于JSON
• 查询通常会执行得更快。

Cons

• 语法更复杂，不像前面那么简单。

希望这能帮上忙祝你好运。