Bekerja dengan gabungan di LookML

Join memungkinkan Anda menghubungkan berbagai tampilan sehingga Anda dapat menjelajahi data dari lebih dari satu tampilan secara bersamaan dan melihat bagaimana berbagai bagian data Anda saling berkaitan.

Misalnya, database Anda mungkin menyertakan tabel order_items, orders, dan users. Anda dapat menggunakan join untuk menjelajahi data dari semua tabel secara bersamaan. Halaman ini menjelaskan penggabungan di LookML, termasuk parameter penggabungan dan pola penggabungan tertentu.

Gabungan dimulai dengan Jelajahi

Join ditentukan dalam file model untuk membuat hubungan antara Jelajah dan tampilan. Gabungan menghubungkan satu atau beberapa tampilan ke satu Jelajah, baik secara langsung maupun melalui tampilan gabungan lainnya.

Pertimbangkan dua tabel database: order_items dan orders. Setelah Anda membuat tampilan untuk kedua tabel, deklarasikan satu atau beberapa tampilan tersebut di bagian parameter explore dalam file model:

explore: order_items { ... }

Saat Anda menjalankan kueri dari Jelajahi order_items, order_items akan muncul dalam klausa FROM dari SQL yang dihasilkan:

SELECT ...
FROM order_items

Anda dapat menggabungkan informasi tambahan ke Eksplorasi order_items. Misalnya, Anda dapat menggunakan contoh LookML berikut untuk menggabungkan tampilan orders ke Jelajah order_items:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

LookML yang ditampilkan sebelumnya akan melakukan dua hal. Pertama, Anda dapat melihat kolom dari orders dan order_items di Pemilih kolom eksplorasi:

Kedua, LookML menjelaskan cara menggabungkan orders dan order_items. LookML tersebut akan diterjemahkan ke SQL berikut:

SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id

Parameter LookML ini dijelaskan secara lebih mendetail di bagian berikut.

Tips Tim Chat: Pengguna paling sering bertanya tentang error validasi, "Bidang tidak diketahui atau tidak dapat diakses", yang dapat disebabkan oleh join yang tidak ada. Lihat Error: Kolom tidak diketahui atau tidak dapat diakses untuk mengetahui informasi selengkapnya.

Parameter join

Empat parameter utama digunakan untuk bergabung: join, type, relationship, dan sql_on.

Langkah 1: Memulai Jelajah

Pertama, buat order_items Jelajahi:

explore: order_items { ... }

Langkah 2: join

Untuk menggabungkan tabel, Anda harus mendeklarasikan tabel dalam tampilan terlebih dahulu. Dalam contoh ini, asumsikan bahwa orders adalah tampilan yang ada dalam model Anda.

Kemudian, gunakan parameter join untuk mendeklarasikan bahwa Anda ingin menggabungkan tampilan orders ke Jelajahi order_items:

explore: order_items {
  join: orders { ... }
}

Langkah 3: type

Pertimbangkan jenis join yang akan dilakukan. Looker mendukung LEFT JOIN, INNER JOIN, FULL OUTER JOIN, dan CROSS JOIN. Nilai ini sesuai dengan parameter value type dari left_outer, inner, full_outer, dan cross.

explore: order_items {
  join: orders {
    type: left_outer
  }
}

Nilai default type adalah left_outer.

Langkah 4: relationship

Tentukan hubungan join antara Jelajah order_items dan tampilan orders. Mendeklarasikan hubungan join dengan benar sangat penting bagi Looker untuk menghitung ukuran yang akurat. Hubungan ditentukan dari order_items Jelajah ke tampilan orders. Opsi yang mungkin adalah one_to_one, many_to_one, one_to_many, dan many_to_many.

Dalam contoh ini, satu pesanan dapat memiliki banyak item pesanan. Hubungan dari Jelajah order_items ke tampilan orders adalah many_to_one:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
  }
}

Jika Anda tidak menyertakan parameter relationship dalam join, Looker akan menetapkan default ke many_to_one.

Untuk tips tambahan tentang cara menentukan parameter relationship dengan benar untuk join, lihat Mendapatkan parameter relationship dengan benar.

Langkah 5: sql_on

Deklarasikan cara menggabungkan tabel order_items dan tabel orders bersama dengan parameter sql_on atau parameter foreign_key.

Tips: Sebaiknya gunakan parameter sql_on karena dapat melakukan semua hal yang dapat dilakukan parameter foreign_key, tetapi parameter sql_on biasanya lebih mudah dipahami.

Parameter sql_on setara dengan klausa ON dalam SQL yang dihasilkan untuk kueri. Dengan parameter ini, Anda dapat mendeklarasikan kolom mana yang harus dicocokkan untuk melakukan join:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

Anda juga dapat menulis join yang lebih kompleks. Misalnya, Anda mungkin ingin hanya menggabungkan pesanan dengan id lebih besar dari 1.000:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
  }
}

Lihat dokumentasi operator penggantian untuk mempelajari sintaksis ${ ... } lebih lanjut dalam contoh ini.

Langkah 6: Pengujian

Uji apakah penggabungan ini berfungsi seperti yang diharapkan dengan membuka Jelajahi Item Pesanan. Anda akan melihat kolom dari order_items dan orders.

Lihat Menguji kolom di Eksplorasi untuk mempelajari lebih lanjut cara menguji perubahan LookML di Eksplorasi.

Bergabung melalui tampilan lain

Anda dapat menggabungkan tampilan ke Jelajahi melalui tampilan lain. Dalam contoh parameter join, Anda menggabungkan orders ke order_items melalui kolom order_id. Kita mungkin juga ingin menggabungkan data dari tampilan yang disebut users ke order_items Jelajahi, meskipun keduanya tidak memiliki kolom yang sama. Hal ini dapat dilakukan dengan bergabung melalui tampilan orders.

Gunakan parameter sql_on atau parameter foreign_key untuk menggabungkan tampilan users ke tampilan orders, bukan ke order_items Explore. Lakukan hal ini dengan menentukan cakupan kolom dari orders sebagai orders.user_id dengan benar.

Berikut adalah contoh yang menggunakan parameter sql_on:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

Bergabung ke tampilan lebih dari sekali

Tampilan users berisi data untuk pembeli dan penjual. Untuk menggabungkan data dari tampilan ini ke dalam order_items, tetapi melakukannya secara terpisah untuk pembeli dan penjual, Anda dapat menggabungkan users dua kali, dengan nama yang berbeda, menggunakan parameter from.

Parameter from memungkinkan Anda menentukan tampilan yang akan digunakan dalam join, sekaligus memberi join nama unik. Contoh:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: buyers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.buyer_id} = ${buyers.id} ;;
  }
  join: sellers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.seller_id} = ${sellers.id} ;;
  }
}

Dalam hal ini, hanya data pembeli yang digabungkan sebagai buyers, sedangkan hanya data penjual yang digabungkan sebagai sellers.

Catatan: Tampilan users kini harus dirujuk dengan nama aliasnya, buyers dan sellers, dalam join.

Membatasi kolom dari join

Parameter fields memungkinkan Anda menentukan kolom yang diambil dari join ke dalam Jelajah. Secara default, semua kolom dari tampilan akan disertakan saat digabungkan. Namun, Anda mungkin hanya ingin menampilkan sebagian kolom.

Misalnya, saat orders digabungkan ke order_items, sebaiknya Anda hanya menyertakan kolom shipping dan tax melalui penggabungan:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [shipping, tax]
  }
}

Anda juga dapat mereferensikan kumpulan kolom, seperti [set_a*]. Setiap set ditentukan dalam tampilan menggunakan parameter set. Misalkan Anda memiliki kumpulan berikut yang ditentukan dalam tampilan orders:

set: orders_set {
  fields: [created_date, shipping, tax]
}

Anda dapat memilih untuk hanya menyertakan tiga kolom ini saat menggabungkan orders ke order_items:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [orders_set*]
  }
}

Agregat simetris

Looker menggunakan fitur yang disebut "agregat simetris" untuk menghitung agregasi (seperti jumlah dan rata-rata) dengan benar, meskipun jika join menghasilkan fanout. Agregat simetris dijelaskan lebih mendetail di Memahami agregat simetris. Masalah fanout yang dipecahkan agregat simetrid dijelaskan dalam Postingan komunitas Masalah fanout SQL.

Kunci utama diperlukan

Agar ukuran (agregasi) muncul melalui join, Anda harus menentukan kunci utama di semua tampilan yang terlibat dalam join.

Lakukan ini dengan menambahkan parameter primary_key ke definisi kolom kunci utama di setiap tampilan:

dimension: id {
  type: number
  primary_key: yes
}

Catatan: Untuk menangani ukuran gabungan dengan benar, Looker mengandalkan Anda untuk menentukan kunci utama dengan nilai yang benar-benar unik dan bukan nilai NULL. Jika data Anda tidak berisi kunci utama, pertimbangkan apakah penyambungan beberapa kolom akan menghasilkan kunci utama dari nilai non-NULL yang sepenuhnya unik. Jika kunci utama Anda tidak unik atau berisi nilai NULL dan kueri Anda menyertakan data yang mengungkapkan masalah tersebut, Looker akan menampilkan error seperti yang dijelaskan di halaman Praktik Terbaik Error: Nilai/kunci utama (atau sql_distinct_key) tidak unik, kelebihan atau ketidakcocokan nilai saat menghitung jumlah.

Dialek SQL yang didukung

Agar Looker mendukung agregat simetris dalam project Looker, dialek database Anda juga harus mendukungnya. Tabel berikut menunjukkan dialek yang mendukung agregat simetris dalam rilis Looker terbaru:

Dialek	Didukung?
Actian Avalanche	Ya
Amazon Athena	Ya
Amazon Aurora MySQL	Ya
Amazon Redshift	Ya
Apache Druid	Tidak
Apache Druid 0.13+	Tidak
Apache Druid 0.18+	Tidak
Apache Hive 2.3+	Tidak
Apache Hive 3.1.2+	Tidak
Apache Spark 3+	Ya
ClickHouse	Tidak
Cloudera Impala 3.1+	Ya
Cloudera Impala 3.1+ dengan Driver Native	Ya
Cloudera Impala dengan Driver Native	Tidak
DataVirtuality	Ya
Databricks	Ya
Denodo 7	Ya
Denodo 8	Ya
Dremio	Tidak
Dremio 11+	Ya
Exasol	Ya
Firebolt	Ya
Legacy SQL Google BigQuery	Ya
SQL Standar Google BigQuery	Ya
Google Cloud PostgreSQL	Ya
Google Cloud SQL	Ya
Google Spanner	Ya
Greenplum	Ya
HyperSQL	Tidak
IBM Netezza	Ya
MariaDB	Ya
Microsoft Azure PostgreSQL	Ya
Microsoft Azure SQL Database	Ya
Microsoft Azure Synapse Analytics	Ya
Microsoft SQL Server 2008+	Ya
Microsoft SQL Server 2012+	Ya
Microsoft SQL Server 2016	Ya
Microsoft SQL Server 2017+	Ya
MongoBI	Tidak
MySQL	Ya
MySQL 8.0.12+	Ya
Oracle	Ya
Oracle ADWC	Ya
PostgreSQL 9.5+	Ya
PostgreSQL versi pra-9.5	Ya
PrestoDB	Ya
PrestoSQL	Ya
SAP HANA 2+	Ya
SingleStore	Ya
SingleStore 7+	Ya
Snowflake	Ya
Teradata	Ya
Trino	Ya
Vektor	Ya
Vertica	Ya

Jika dialek Anda tidak mendukung agregat simetris, berhati-hatilah saat menjalankan join di Looker, karena beberapa jenis join dapat menyebabkan agregasi yang tidak akurat (seperti jumlah dan rata-rata). Masalah ini dan solusinya dijelaskan secara mendetail dalam postingan Komunitas Masalah fanout SQL.

Pelajari lebih lanjut penggabungan

Untuk mempelajari parameter join di LookML lebih lanjut, lihat dokumentasi Referensi join.