BigQueryでデータロード時に欠損値の読み込みルールまとめ

最近、bigqueryの評価を行っている。本番向けのデータではよくあることだが、本来データが入る場所にnullが入っていたり、要素が無かったり、逆に要素が多かったりする。

bigqueryはTreasureDataの様にスキーマレスではなくきちんとスキーマを定義しなければならない。

bigqueryでは、スキーマの定義にjsonを使い、データのロードにもjsonを使うため、要素にミスマッチが発生する場合が考えられる。

スキーマとjsonで要素のミスマッチがあった場合は、データにnullが入っていた場合のbigqueryの挙動に関してまとめた。

最初に結果だけ書き、後半に実際にデータロードで試したサンプルを乗せる。

データロード時のBigQueryスキーマと、jsonの対応

	送信jsonにスキーマがある。	送信jsonにスキーマが無い
bigqueryにスキーマがある	◯	◯ (nullが入る)
bigqueryにスキーマが無い	◯(余分な項目は無視)	×

となる。

上の表の説明をすると、bigquery上のスキーマと、送信json上の要素のミスマッチを問題にしている。

送信json上にスキーマがあり、bigquery上のスキーマが無い時データはロードされるが、送信json上のデータは無視される。bigquery側のスキーマが優先される。

bigquery上のスキーマでは、指定があり、送信json上でその項目がない場合、nullが入る。

配列を含むデータのロード

bigqueryは配列を含むデータをロード出来る。しかし、配列の途中にnullが含まれる場合などはどのような挙動をするだろうか？
調査した。

欠落値パターン	結果
A:配列カラムがnull	×
B:配列カラムが空配列	◯。空配列はnullになる
C:配列にはnullしか入っていない	×
D:配列には整数値が入っているが途中で、文字列が含まれる	×

配列は制約が厳しく、配列中にnullを含むとデータを読まない。何もデータがない場合には、空配列を入れる必要がある。整数値が入ることを期待された配列に文字列を入れようとすると入らない。事前の型チェックが必要だろう。

辞書を含むデータのロード

bigqueryはネストした辞書を含むデータをロードできる。しかし、ネストした辞書にnullが含まれる場合はどのような挙動を示すだろうか？調査した。

欠落値シナリオ	結果
A:hashに値にnullが入っている	×
B:hashのカラム自体が無い	◯ カラムにnullが入る(BQ側ではスキーマがあるので)
C:hashの中身にnullが含まれる	◯ nullの含まれる要素はnullになる
D:hashの中身に項目が無い	◯ ない要素にはnullが入る

ネストした辞書の場合には、null以外の場合にはデータが入るようだ。

配列の中に辞書を含むデータのロード

最後の例は複雑だ。配列の中に辞書を含む場合どの様にデータを持てるだろうか。
WeightやSexと言うのは後のサンプルで出てくる。

欠落値シナリオ	結果
A:配列カラムがnull	データ入らない
B:配列カラムの要素がない	データは入る(nullが入る)
C:配列中の要素にnullが入っている	データ入る,子要素にはnullが入る。
D:配列中の要素に、子要素が無い(Weightがない)	データ入る。子要素にはnullが入る
E:配列の要素自体がnull	データ入らない
F:配列カラムが空配列	データ入る,nullが入る
G:規定外の要素が子要素に含まれる(Sexは指定していない)	データは入る。Sexは無視される

ベスト・プラクティス

• 配列や、辞書自体にnullが入ると上手くデータが入らない。その場合にはvalueがnullの場合には、key自体を削除することを考える。
• 配列にデータを入れる際に、異なる型、整数値や文字列が一緒に入らないように注意。
• 配列中にnullが入らないように注意する。IntgerやStringにnullが混ざるとデータが入らない。

実際にBigQueryのロードで使ったサンプル

送信JsonとBigQueryで要素にミスマッチがある。

bigqueyrスキーマ

    [
      {
        "name": "Name",
        "type": "STRING"
      },
      {
        "name": "Age",
        "type": "INTEGER"
      },
      {
        "name": "Weight",
        "type": "FLOAT"
      },
      {
        "name": "IsMagic",
        "type": "BOOLEAN"
      }
    ]

送信json

{"Name":"hoge","Age":20,"Weight":3.1,"IsMagic":false}　<-全て入る。
{"Age":21,"Weight":4.1,"IsMagic":false} <- 入る。Nameがないので、Nameにはnullが入る。
{"Name":"fuga","Age":22,"Weight":5.1,"IsMagic":false,"SEX":"M"} <- 入る。SEXが余分。無視される。

配列データを読み込む

bigqueryスキーマ

[
  {
    "name": "Name",
    "type": "STRING"
  },
  {"name": "status",
    "type": "INTEGER",
    "mode": "repeated",
  }
]

送信json

{"Name":"hoge1","status":[1,2,3,4,5,6]} <-入る。正常
{"Name":"hoge2","status":null} <-入らない。配列カラムがnull
{"Name":"hoge3","status":[]} <-入る。配列カラムが空配列
{"Name":"hoge3","status":[null,null,null]} <-入らない。配列カラムにnullが入っている。
{"Name":"hoge4","status":[1,"hoge",2]} <-入らない。配列には整数が入っていたが、途中で文字が含まれる。

辞書データを読み込む

bigqueryスキーマ

[
  {
    "name": "Name",
    "type": "STRING"
  },
  {"name": "status",
    "type": "RECORD",
    "mode": "NULLABLE",
    "fields":
    [
      {
        "name": "Age",
        "type": "INTEGER"
      },
      {
        "name": "Weight",
        "type": "FLOAT"
      },
      {
        "name": "IsMagic",
        "type": "BOOLEAN"
      }
    ]
  }
]

送信json

{"Name":"hoge1","status":{"Age":21,"Weight":3.1,"IsMagic":false}} <- 入る。正常
{"Name":"hoge2","status":null} <- 入らない。項目にnullが入っている。
{"Name":"hoge3"} <- 入る。項目自体がない。
{"Name":"hoge4","status":{"Age":22,"Weight":null,"IsMagic":false}}　<- 入る。要素の一つにnullが入っている。
{"Name":"hoge5","status":{"Age":23,"IsMagic":false}} 入る。要素の一つの項目がない。

配列に辞書を含むデータを読み込む

bigqueryスキーマ

[
  {
    "name": "Name",
    "type": "STRING"
  },
  {"name": "status",
    "type": "RECORD",
    "mode": "REPEATED",
    "fields": [
      {
        "name": "Age",
        "type": "INTEGER"
      },
      {
        "name": "Weight",
        "type": "FLOAT"
      },
      {
        "name": "IsMagic",
        "type": "BOOLEAN"
      }
    ]
  }
]

送信json

{"Name":"hoge10","status":[{"Age":21,"Weight":3.1,"IsMagic":false},{"Age":22,"Weight":3.3,"IsMagic":true}]} <-入る。正常
{"Name":"hoge20","status":null} <- 入らない。配列カラムがnull
{"Name":"hoge30"} <-入る。配列カラムの要素が無い。
{"Name":"hoge40","status":[{"Age":23,"Weight":null,"IsMagic":false},{"Age":24,"Weight":null,"IsMagic":false}]} 入る。配列中の要素にnullが入っている。
{"Name":"hoge50","status":[{"Age":23,"IsMagic":false},{"Age":23,"IsMagic":true}]} <- 入る。配列中の要素に子要素が無い
{"Name":"hoge60","status":[{"Age":23,"IsMagic":false},null]} <- 入らない。配列中の値がnull
{"Name":"hoge70","status":[]} <-入る。配列カラムが空配列
{"Name":"hoge80","status":[{"Sex":"Male","Age":23,"IsMagic":false},{"Sex":"Female","Age":23,"IsMagic":true}]} <- 入る。子要素に、Sexという規定外の要素が入っている。