BigQuery mendukung data bertingkat dalam tabel. Data bertingkat dapat berupa data tunggal atau berisi nilai berulang. Halaman ini memberikan ringkasan cara menggunakan data bertingkat BigQuery di Looker.
Keuntungan dari catatan bersarang
Ada beberapa keuntungan menggunakan catatan bertingkat saat Anda memindai {i>dataset<i} terdistribusi:
- Data bertingkat tidak memerlukan gabungan. Ini berarti komputasi bisa lebih cepat dan memindai data yang jauh lebih sedikit dibandingkan jika Anda harus menggabungkan kembali data tambahan setiap kali Anda membuat kuerinya.
- Struktur bertingkat pada dasarnya adalah tabel yang telah digabungkan sebelumnya. Tidak ada biaya tambahan untuk kueri jika Anda tidak mereferensikan kolom bertingkat, karena data BigQuery disimpan dalam kolom. Jika Anda mereferensikan kolom bertingkat, logikanya identik dengan gabungan yang ditempatkan di lokasi.
- Struktur bertingkat menghindari pengulangan data yang harus diulang dalam tabel yang didenormalisasi secara lebar. Dengan kata lain, untuk orang yang telah tinggal di lima kota, tabel lebar yang didenormalisasi akan berisi semua informasi mereka dalam lima baris (satu untuk setiap kota tempat mereka tinggal). Dalam struktur bertingkat, informasi berulang hanya membutuhkan satu baris karena array lima kota dapat dimuat dalam satu baris dan tidak bertingkat saat diperlukan.
Bekerja dengan catatan bertingkat di LookML
Tabel BigQuery berikut, persons_living, menampilkan skema umum yang menyimpan contoh data pengguna, termasuk fullName, age, phoneNumber, dan cityLived bersama dengan jenis data dan mode setiap kolom. Skema ini menunjukkan bahwa nilai di kolom citiesLived diulang, menunjukkan bahwa beberapa pengguna mungkin pernah tinggal di beberapa kota:
Contoh berikut adalah LookML untuk Jelajah dan tampilan yang dapat Anda buat dari skema sebelumnya yang ditampilkan. Ada tiga tampilan: persons
, persons_cities_lived
, dan persons_phone_number
. Jelajah tampak identik dengan Jelajah yang ditulis dengan tabel yang tidak bertingkat.
Catatan: Meskipun semua komponen (tampilan dan Jelajah) ditulis dalam satu blok kode pada contoh berikut, praktik terbaiknya adalah menempatkan tampilan dalam file tampilan individual serta menempatkan Jelajah dan spesifikasi connection:
di file model.
-- model file connection: "bigquery_publicdata_standard_sql" explore: persons { # Repeated nested object join: persons_cities_lived { view_label: "Persons: Cities Lived:" sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;; relationship: one_to_many } # Non repeated nested object join: persons_phone_number { view_label: "Persons: Phone:" sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;; relationship: one_to_one } } -- view files view: persons { sql_table_name: bigquery-samples.nested.persons_living ;; dimension: id { primary_key: yes sql: ${TABLE}.fullName ;; } dimension: fullName {label: "Full Name"} dimension: kind {} dimension: age {type:number} dimension: citiesLived {hidden:yes} dimension: phoneNumber {hidden:yes} measure: average_age { type: average sql: ${age} ;; drill_fields: [fullName,age] } measure: count { type: count drill_fields: [fullName, cities_lived.place_count, age] } } view: persons_phone_number { dimension: areaCode {label: "Area Code"} dimension: number {} } view: persons_cities_lived { dimension: id { primary_key: yes sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;; } dimension: place {} dimension: numberOfYears { label: "Number Of Years" type: number } measure: place_count { type: count drill_fields: [place, persons.count] } measure: total_years { type: sum sql: ${numberOfYears} ;; drill_fields: [persons.fullName, persons.age, place, numberOfYears] } }
Setiap komponen untuk bekerja dengan data bertingkat di LookML dibahas secara lebih mendetail di bagian berikut:
Tabel Virtual
Setiap data bertingkat ditulis sebagai tampilan. Misalnya, tampilan phoneNumber
hanya mendeklarasikan dimensi yang muncul dalam data:
view: persons_phone_number { dimension: areaCode {label: "Area Code"} dimension: number {} }
Tampilan persons_cities_lived
lebih kompleks. Seperti yang ditunjukkan di atas, Anda menentukan dimensi yang muncul dalam data (numberOfYears
dan place
), tetapi Anda juga dapat menentukan beberapa ukuran. Ukuran dan drill_fields
ditetapkan seperti biasa, seolah-olah data ini berada di tabelnya sendiri. Satu-satunya perbedaan nyata adalah Anda mendeklarasikan id
sebagai primary_key
sehingga agregat dihitung dengan benar.
view: persons_cities_lived { dimension: id { primary_key: yes sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;; } dimension: place {} dimension: numberOfYears { label: "Number Of Years" type: number } measure: place_count { type: count drill_fields: [place, persons.count] } measure: total_years { type: sum sql: ${numberOfYears} ;; drill_fields: [persons.fullName, persons.age, place, numberOfYears] } }
Deklarasi kumpulan data
Dalam tampilan yang berisi subdata (dalam hal ini persons
), Anda harus mendeklarasikan data tersebut. Keduanya akan digunakan saat Anda membuat join. Anda dapat menyembunyikan kolom LookML ini dengan parameter hidden
karena Anda tidak akan membutuhkannya saat menjelajahi data.
view: persons { ... dimension: citiesLived { hidden:yes } dimension: phoneNumber { hidden:yes } ... }
Gabungan
Data bertingkat di BigQuery adalah array elemen STRUCT
. Alih-alih menggabungkan dengan parameter sql_on
, hubungan join dibangun ke dalam tabel. Dalam hal ini, Anda dapat menggunakan parameter join sql:
agar dapat menggunakan operator UNNEST
. Selain perbedaan tersebut, membatalkan tingkatan array elemen STRUCT
sama persis seperti menggabungkan tabel.
Untuk catatan yang tidak berulang, Anda cukup menggunakan STRUCT
; Anda dapat mengubahnya menjadi array elemen STRUCT
dengan menempatkannya dalam tanda kurung siku. Meskipun mungkin terdengar aneh, tampaknya tidak ada penalti performa — dan hal ini menjaga semuanya tetap bersih dan sederhana.
explore: persons { # Repeated nested object join: persons_cities_lived { view_label: "Persons: Cities Lived:" sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;; relationship: one_to_many } # Non repeated nested object join: persons_phone_number { view_label: "Persons: Phone:" sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;; relationship: one_to_one } }
Gabungan untuk array tanpa kunci unik untuk setiap baris
Meskipun cara terbaik adalah memiliki kunci alami yang dapat diidentifikasi dalam data, atau kunci pengganti yang dibuat dalam proses ETL, hal ini tidak selalu memungkinkan. Misalnya, Anda dapat mengalami situasi di mana beberapa array tidak memiliki kunci unik relatif untuk baris tersebut. Di sinilah WITH OFFSET
dapat berguna dalam sintaksis join.
Misalnya, kolom yang mewakili seseorang mungkin dimuat beberapa kali jika orang tersebut tinggal di beberapa kota — Chicago, Denver, San Francisco, dll. Membuat kunci utama di baris yang tidak bertingkat bisa jadi sulit jika tanggal atau kunci alami yang dapat diidentifikasi lainnya tidak diberikan untuk membedakan masa jabatan orang tersebut di setiap kota. Di sini, WITH OFFSET
dapat memberikan nomor baris relatif (0,1,2,3) untuk setiap baris yang tidak bertingkat. Pendekatan ini menjamin kunci unik pada baris yang tidak bertingkat:
explore: persons { # Repeated nested Object join: persons_cities_lived { view_label: "Persons: Cities Lived:" sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived WITH OFFSET as person_cities_lived_offset;; relationship: one_to_many } } view: persons_cities_lived { dimension: id { primary_key: yes sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${offset} AS STRING)) ;; } dimension: offset { type: number sql: person_cities_lived_offset;; } }
Nilai berulang sederhana
Data bertingkat di BigQuery juga dapat berupa nilai sederhana, seperti bilangan bulat atau string. Untuk membatalkan bertingkat array dari nilai berulang sederhana, Anda dapat menggunakan pendekatan serupa seperti yang ditunjukkan sebelumnya, menggunakan operator UNNEST
dalam join.
Contoh berikut membatalkan bertingkat array bilangan bulat tertentu, `unresolved_skus`:
explore: impressions { join: impressions_unresolved_sku { sql: LEFT JOIN UNNEST(unresolved_skus) AS impressions_unresolved_sku ;; relationship: one_to_many } } view: impressions_unresolved_sku { dimension: sku { type: string sql: ${TABLE} ;; } }
Parameter sql
untuk array bilangan bulat, unresolved_skus
, direpresentasikan sebagai ${TABLE}
. Tindakan ini secara langsung mereferensikan tabel nilai itu sendiri, yang kemudian tidak bertingkat di explore
.