レガシー SQL でのネストされたフィールドと繰り返しフィールドのクエリ

このドキュメントでは、ネストされたデータと繰り返しデータをレガシー SQL クエリ構文でクエリする方法を詳しく説明します。BigQuery で推奨されるクエリ構文は GoogleSQL です。GoogleSQL でのネストされたデータと繰り返しデータの処理については、GoogleSQL 移行ガイドをご覧ください。

BigQuery では、JSON または Avro ファイル形式でネストされたデータと繰り返しデータの読み込みエクスポートをサポートしています。レガシー SQL クエリの多くに対して、BigQuery は自動的にデータをフラット化します。たとえば、多くの SELECT ステートメントでは、データの構造を維持しながらネストされたフィールドまたは繰り返しフィールドを取得できます。また、WHERE 句を使用すると、構造を維持しながらデータをフィルタリングできます。逆に、ORDER BY 句と GROUP BY 句は、クエリ対象のデータを暗黙的にフラット化します。データが暗黙的にフラット化されない場合(レガシー SQL で複数の繰り返しフィールドのクエリを実行する場合など)、SQL 関数 FLATTEN および WITHIN を使用してデータのクエリを実行できます。

FLATTEN

ネストされたデータを照会する場合、BigQuery は自動的にテーブルデータをフラット化します。例として、個人データのサンプル スキーマを見てみましょう。

   Last modified                 Schema                 Total Rows   Total Bytes   Expiration
 ----------------- ----------------------------------- ------------ ------------- ------------
  27 Sep 10:01:06   |- kind: string                     4            794
                    |- fullName: string (required)
                    |- age: integer
                    |- gender: string
                    +- phoneNumber: record
                    |  |- areaCode: integer
                    |  |- number: integer
                    +- children: record (repeated)
                    |  |- name: string
                    |  |- gender: string
                    |  |- age: integer
                    +- citiesLived: record (repeated)
                    |  |- place: string
                    |  +- yearsLived: integer (repeated)

いくつかの繰り返しフィールドとネストされたフィールドがあるのでご注意ください。次のようなレガシー SQL クエリを person テーブルに対して実行すると、

SELECT
  fullName AS name,
  age,
  gender,
  citiesLived.place,
  citiesLived.yearsLived
FROM [dataset.tableId]

BigQuery は、フラット化された出力でデータを返します。

+---------------+-----+--------+-------------------+------------------------+
|     name      | age | gender | citiesLived_place | citiesLived_yearsLived |
+---------------+-----+--------+-------------------+------------------------+
| John Doe      |  22 | Male   | Seattle           |                   1995 |
| John Doe      |  22 | Male   | Stockholm         |                   2005 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1989 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1993 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   1998 |
| Mike Jones    |  35 | Male   | Los Angeles       |                   2002 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1990 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1993 |
| Mike Jones    |  35 | Male   | Washington DC     |                   1998 |
| Mike Jones    |  35 | Male   | Washington DC     |                   2008 |
| Mike Jones    |  35 | Male   | Portland          |                   1993 |
| Mike Jones    |  35 | Male   | Portland          |                   1998 |
| Mike Jones    |  35 | Male   | Portland          |                   2003 |
| Mike Jones    |  35 | Male   | Portland          |                   2005 |
| Mike Jones    |  35 | Male   | Austin            |                   1973 |
| Mike Jones    |  35 | Male   | Austin            |                   1998 |
| Mike Jones    |  35 | Male   | Austin            |                   2001 |
| Mike Jones    |  35 | Male   | Austin            |                   2005 |
| Anna Karenina |  45 | Female | Stockholm         |                   1992 |
| Anna Karenina |  45 | Female | Stockholm         |                   1998 |
| Anna Karenina |  45 | Female | Stockholm         |                   2000 |
| Anna Karenina |  45 | Female | Stockholm         |                   2010 |
| Anna Karenina |  45 | Female | Moscow            |                   1998 |
| Anna Karenina |  45 | Female | Moscow            |                   2001 |
| Anna Karenina |  45 | Female | Moscow            |                   2005 |
| Anna Karenina |  45 | Female | Austin            |                   1995 |
| Anna Karenina |  45 | Female | Austin            |                   1999 |
+---------------+-----+--------+-------------------+------------------------+

この例では、citiesLived.placecitiesLived_place に、citiesLived.yearsLivedcitiesLived_yearsLived になりました。

BigQuery はネストされたフィールドを自動的にフラット化できますが、複数の繰り返しフィールドを処理するときは、明示的に FLATTEN を呼び出す必要があります。たとえば、次のようなレガシー SQL クエリを実行しようとするとします。

SELECT fullName, age
FROM [dataset.tableId]
WHERE
  (citiesLived.yearsLived > 1995 ) AND
  (children.age > 3)

BigQuery は次のようなエラーを返します。

Cannot query the cross product of repeated fields children.age and citiesLived.yearsLived

複数の繰り返しフィールドに対してクエリを実行するには、いずれかのフィールドをフラット化する必要があります。

SELECT
  fullName,
  age,
  gender,
  citiesLived.place
FROM (FLATTEN([dataset.tableId], children))
WHERE
  (citiesLived.yearsLived > 1995) AND
  (children.age > 3)
GROUP BY fullName, age, gender, citiesLived.place

この場合、次の結果が返されます。

+------------+-----+--------+-------------------+
|  fullName  | age | gender | citiesLived_place |
+------------+-----+--------+-------------------+
| John Doe   |  22 | Male   | Stockholm         |
| Mike Jones |  35 | Male   | Los Angeles       |
| Mike Jones |  35 | Male   | Washington DC     |
| Mike Jones |  35 | Male   | Portland          |
| Mike Jones |  35 | Male   | Austin            |
+------------+-----+--------+-------------------+

WITHIN 句

WITHIN キーワードは特に集計関数と組み合わせてレコード内またはネストされたフィールド内の子や繰り返しフィールドを集計する場合に使用します。WITHIN キーワードを指定する場合、次のいずれかを使用して集計する範囲を指定する必要があります。

  • WITHIN RECORD: レコードに含まれる繰り返し値のデータを集計します。
  • WITHIN node_name: 指定したノード内の繰り返し値のデータを集計します。ここで指定するノードは、集計関数内のフィールドの親ノードです。

上記の例で、それぞれの人の子供の数を調べるとします。それには、各レコードの children.name の数をカウントします。

SELECT
  fullName,
  COUNT(children.name) WITHIN RECORD AS numberOfChildren
FROM [dataset.tableId];

次の結果が得られます。

+---------------+------------------+
|   fullName    | numberOfChildren |
+---------------+------------------+
| John Doe      |                2 |
| Jane Austen   |                2 |
| Mike Jones    |                3 |
| Anna Karenina |                0 |
+---------------+------------------+

比較のために、子供の名前をすべてリストしてみます。

SELECT fullName, children.name
FROM [dataset.tableId]
+---------------+---------------+
|   fullName    | children_name |
+---------------+---------------+
| John Doe      | Jane          |
| John Doe      | John          |
| Jane Austen   | Josh          |
| Jane Austen   | Jim           |
| Mike Jones    | Earl          |
| Mike Jones    | Sam           |
| Mike Jones    | Kit           |
| Anna Karenina | None          |
+---------------+---------------+

これは WITHIN RECORD クエリの結果と一致します。John Doe には、Jane と John という名前の 2 人の子どもがいます。Jane Austen には、Josh と Jim という名前の 2 人の子供がいます。Mike Jones には、Earl、Sam、Kit という 3 人の子どもがいます。Anna Karenina には子どもがいません。

ここで、人々が住んだことがある場所の数を知りたいとします。この場合は、WITHIN 句を使用して 1 つの特定のノード全体で集計を実行します。

SELECT
  fullName,
  COUNT(citiesLived.place) WITHIN RECORD AS numberOfPlacesLived,
  citiesLived.place,
  COUNT(citiesLived.yearsLived) WITHIN citiesLived AS numberOfTimesInEachCity,
FROM [dataset.tableId];
+---------------+---------------------+-------------------+-------------------------+
|   fullName    | numberOfPlacesLived | citiesLived_place | numberOfTimesInEachCity |
+---------------+---------------------+-------------------+-------------------------+
| John Doe      |                   2 | Seattle           |                       1 |
| John Doe      |                   2 | Stockholm         |                       1 |
| Mike Jones    |                   4 | Los Angeles       |                       4 |
| Mike Jones    |                   4 | Washington DC     |                       4 |
| Mike Jones    |                   4 | Portland          |                       4 |
| Mike Jones    |                   4 | Austin            |                       4 |
| Anna Karenina |                   3 | Stockholm         |                       4 |
| Anna Karenina |                   3 | Moscow            |                       3 |
| Anna Karenina |                   3 | Austin            |                       2 |
+---------------+---------------------+-------------------+-------------------------+

このクエリは、次のことを行います。

  • citiesLived.placeWITHIN RECORD を実行し、それぞれの人が住んだことのある場所の数をカウントします。
  • citiesLived.yearsLivedWITHIN を実行し、それぞれの人が各都市に住んだことのある回数をカウントします(citiesLived 全体でカウント)。

範囲を設定してネストされたフィールドや繰り返しフィールドを集計する機能は BigQuery の長所の 1 つであり、多くの場合、コストのかかる結合をクエリで使用する必要がなくなります。