代码之家 › 专栏 › 技术社区 › dendog

BigQuery:存储半结构化JSON数据

google-bigquery sql

dendog · 技术社区 · 7 年前

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并使用函数来运行分析?

0 回复 | 直到 7 年前

Justin Carmony 7 年前

存储半结构化数据有两种主要方法,如示例中所示:

你可以储存 data 字段,然后使用 JSON_EXTRACT 函数来提取它能找到的值,它将返回 NULL

既然你提到需要对场进行数学分析,那么让我们做一个简单的 SUM 对于 a 和 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

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

赞成的意见

语法相当直接
不易出错

欺骗

存储成本会稍微高一些,因为您必须存储所有要序列化为JSON的字符。

选项2:重复字段

BigQuery有 support for repeated fields ,允许您采用您的结构并在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

如您所见,执行类似的操作仍然相当复杂。您还必须存储字符串和 CAST 由于不能在重复字段中混合类型,因此在必要时可以将它们转换为其他值。

赞成的意见

存储大小将小于JSON
查询通常执行得更快。

欺骗