Membuat kueri pada kolom bertingkat dan berulang di legacy SQL

Dokumen ini menjelaskan cara membuat kueri data bertingkat dan berulang dalam sintaksis kueri legacy SQL. Sintaksis kueri yang lebih disukai untuk BigQuery adalah GoogleSQL. Untuk informasi tentang penanganan data bertingkat dan berulang di GoogleSQL, lihat panduan migrasi GoogleSQL.

BigQuery mendukung pemuatan dan exporting data bertingkat dan berulang dalam bentuk file JSON dan Avro. Untuk banyak kueri legacy SQL, BigQuery dapat meratakan data secara otomatis. Misalnya, banyak pernyataan SELECT dapat mengambil kolom bertingkat atau berulang sekaligus mempertahankan struktur data, dan klausa WHERE dapat memfilter data sekaligus mempertahankan strukturnya. Sebaliknya, klausa ORDER BY dan GROUP BY secara implisit meratakan data yang dikueri. Untuk situasi ketika data tidak diratakan secara implisit, seperti membuat kueri beberapa kolom berulang dalam legacy SQL, Anda dapat membuat kueri data menggunakan fungsi SQL FLATTEN dan WITHIN.

FLATTEN

Saat Anda membuat kueri data bertingkat, BigQuery akan meratakan data tabel untuk Anda secara otomatis. Misalnya, mari kita lihat skema sampel untuk data orang:

   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)

Perhatikan bahwa ada beberapa kolom berulang dan bertingkat. Jika Anda menjalankan kueri legacy SQL seperti berikut terhadap tabel orang:

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

BigQuery akan menampilkan data Anda dengan output yang diratakan:

+---------------+-----+--------+-------------------+------------------------+
|     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 |
+---------------+-----+--------+-------------------+------------------------+

Dalam contoh ini, citiesLived.place sekarang menjadi citiesLived_place dan citiesLived.yearsLived sekarang menjadi citiesLived_yearsLived.

Meskipun BigQuery dapat meratakan kolom bertingkat secara otomatis, Anda mungkin perlu memanggil FLATTEN secara eksplisit saat menangani lebih dari satu kolom berulang. Misalnya, jika Anda mencoba menjalankan kueri legacy SQL seperti berikut:

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

BigQuery akan menampilkan error yang mirip dengan:

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

Untuk membuat kueri di lebih dari satu kolom berulang, Anda perlu meratakan salah satu kolom:

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

Yang akan menghasilkan:

+------------+-----+--------+-------------------+
|  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            |
+------------+-----+--------+-------------------+

DALAM Klausa

Kata kunci WITHIN secara khusus berfungsi dengan fungsi agregat untuk digabungkan di seluruh turunan dan kolom berulang dalam data dan kolom bertingkat. Saat menentukan kata kunci WITHIN, Anda harus menentukan cakupan yang ingin digabungkan:

  • WITHIN RECORD: Menggabungkan data pada nilai berulang dalam data.
  • WITHIN node_name: Menggabungkan data dalam nilai berulang dalam node yang ditentukan, dengan node sebagai node induk dari kolom dalam fungsi agregasi.

Misalkan Anda ingin menemukan jumlah anak yang dimiliki setiap orang pada contoh sebelumnya. Untuk melakukannya, Anda dapat menghitung jumlah children.name yang dimiliki setiap data:

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

Anda mendapatkan hasil berikut:

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

Untuk membandingkan, coba cantumkan semua nama anak:

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          |
+---------------+---------------+

Ini cocok dengan hasil kueri WITHIN RECORD kita. John Doe memiliki dua anak bernama Jane dan John, Jane Austen memiliki dua anak bernama Josh dan Jim, Mike Jones memiliki tiga anak bernama Earl, Sam, dan Kit, dan Anna Karenina tidak memiliki anak.

Sekarang, misalkan Anda ingin menemukan berapa kali seseorang tinggal di tempat yang berbeda. Anda dapat menggunakan klausa WITHIN untuk menggabungkan satu node tertentu:

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 |
+---------------+---------------------+-------------------+-------------------------+

Kueri ini melakukan hal berikut:

  • Melakukan WITHIN RECORD pada citiesLived.place dan menghitung jumlah tempat tinggal setiap orang
  • Melakukan WITHIN pada citiesLived.yearsLived dan menghitung berapa kali setiap orang tinggal di setiap kota (hanya dihitung di citiesLived).

Menggunakan penggabungan terbatas pada kolom bertingkat dan berulang merupakan salah satu fitur BigQuery yang paling canggih, yang sering kali dapat menghilangkan penggabungan yang mahal dalam kueri.