如何在bigquery中处理动态模式 [英] How to handle dynamic schema in bigquery

问题描述

我的数据如下所示：
第1行 - {id：1，object：user，metadata：{name：1234 }
rows 2 - {id：1，object：user，metadata：{name：我创建了表格1
code>元数据RECORD NULLABLE
metadata.tenant STRING NULLABLE
对象STRING NULLABLE
ID STRING NULLABLE

但是我的插入将在第2行上失败。我的模式应该如何处理，以便它可以处理元数据字段中的更改？

解决方案

在你的问题中显示的例子 - 我会在下面的模式中使用


$ b

  [
 {
name：id，
type：INTEGER，
mode：NULLABLE$ b $ 
name：object，
type：STRING，
mode：NULLABLE
}，
 {
name：元数据，
键入：STRING，
mode：NULLABLE
} 
] 
  

以下是我如何处理它的一个例子
$ b

 ＃ standardSQL 
 WITH yourProject.yourDataset.yourTable AS（
 SELECT 1 AS id，'user'AS object，'{name：BI Architect，email：abc @ abc。 com}'AS'元数据UNION ALL 
 SELECT 2，'expert'，'{name：Elliott Brossard}'
）
 SELECT 
 id，
对象，
 JSON_EXTRACT_SCALAR（元数据，'$ .name'）AS名称，
 JSON_EXTRACT_SCALAR（元数据，'$ .email'）AS电子邮件
从`yourProject.yourDataset.yourTable` 
 ORDER BY id 
  

导致以下输出

id对象名称电子邮件
1位用户BI架构师[e-mail]
2位专家Elliott Brossard null


My data looks like this: row 1 - {"id": "1", "object": "user","metadata": {"name": "1234"}} rows 2 - {"id": "1", "object": "user","metadata": {"name": "1234","email": "[e-mail]"}}

I created the table using row 1 metadata RECORD NULLABLE metadata.tenant STRING NULLABLE object STRING NULLABLE id STRING NULLABLE

But my insert will fail on row 2. what should my schema look like so that it can handle changes in metadata field?

解决方案

For the example shown in your question - I would go with below schema

[
  {
    "name": "id",
    "type": "INTEGER",
    "mode": "NULLABLE"
  },
  {
    "name": "object",
    "type": "STRING",
    "mode": "NULLABLE"
  },
  {
    "name": "metadata",
    "type": "STRING",
    "mode": "NULLABLE"
  }
]   

And below is an example of how I would process it

#standardSQL
WITH `yourProject.yourDataset.yourTable` AS (
  SELECT 1 AS id, 'user' AS object, '{"name": "BI Architect", "email": "[e-mail]"}' AS metadata UNION ALL
  SELECT 2, 'expert', '{"name": "Elliott Brossard"}'
)
SELECT 
  id, 
  object, 
  JSON_EXTRACT_SCALAR(metadata, '$.name') AS name,
  JSON_EXTRACT_SCALAR(metadata, '$.email') AS email 
FROM `yourProject.yourDataset.yourTable` 
ORDER BY id

resulted in below output

id  object  name                email    
1   user    BI Architect        [e-mail]  
2   expert  Elliott Brossard    null     

这篇关于如何在bigquery中处理动态模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！