Konsep SQL untuk tabel turunan

Meskipun tampilan LookML biasanya didasarkan pada tabel yang ada dalam database, Anda juga dapat membuat tampilan yang didasarkan pada pernyataan SQL SELECT. Di Looker, jenis tampilan ini disebut tabel turunan. Tabel turunan adalah kueri yang hasilnya digunakan seolah-olah hasil tersebut adalah tabel sebenarnya dalam database.

Dalam panduan ini, Anda akan mempelajari topik berikut:

Cara Looker menghasilkan SQL untuk tabel turunan

Saat Anda membuat kueri tabel turunan di LookML, Looker menerjemahkan kueri Anda menjadi pernyataan SQL sebagai Common Table Expression (CTE) atau tampilan inline, bergantung pada dialeknya. Dengan kata lain, kueri SQL yang dihasilkan dapat menyerupai salah satu contoh berikut.

Contoh berikut menunjukkan cara Looker membuat kueri SQL yang menggunakan CTE untuk mengkueri tabel turunan:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

Contoh berikut menunjukkan cara Looker dapat menghasilkan kueri SQL yang menggunakan tampilan inline untuk membuat kueri tabel turunan:

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

Tabel turunan berbasis SQL

Untuk membuat tabel turunan berbasis SQL, Anda menentukan kueri SQL secara langsung dalam LookML dengan menggunakan parameter sql dalam parameter derived_table tampilan. Dengan demikian, Anda dapat menentukan kolom tabel turunan menggunakan SQL.

Misalnya, LookML contoh berikut menentukan tabel turunan bernama top_100_users yang mengidentifikasi 100 pengguna teratas dengan total pembelanjaan tertinggi di semua pesanan mereka:

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

Saat tampilan top_100_users dirujuk dalam kueri Jelajah, Looker menggunakan pernyataan SQL SELECT ini dalam SQL yang dihasilkan sebagai CTE atau tampilan inline, bergantung pada dialek Anda.

Penggunaan SQL untuk menentukan tabel turunan dapat memiliki beberapa batasan. Misalnya, dalam contoh dari bagian tabel turunan berbasis SQL, pertimbangan berikut berlaku:

  • Hubungan antara tabel orders dan order_items mungkin sudah ditentukan dalam model LookML. Jika nama tabel pokok dalam database Anda berubah, nama tabel harus diperbarui di beberapa tempat, termasuk dalam definisi tabel turunan berbasis SQL.
  • SQL dalam definisi tabel turunan Anda harus menggunakan dialek yang benar untuk database pokok. Jika data dipindahkan ke database lain, definisi tabel turunan berbasis SQL Anda harus diubah.

Oleh karena itu, tabel turunan native sering kali menjadi pilihan yang lebih baik.

Tabel turunan native

Di Looker, tabel turunan native ditentukan dengan LookML. Berbeda dengan penggunaan tabel turunan berbasis SQL, saat menentukan tabel turunan native, Anda memanfaatkan model LookML dengan cara berikut:

  • Jika nama tabel berubah di database, Anda hanya perlu memperbaruinya satu kali di model LookML. Karena tabel turunan berbasis native mengarah ke objek LookML yang ada tempat tabel database ditentukan, tabel turunan berbasis native akan otomatis merujuk ke tabel yang sesuai.
  • Demikian pula, jika Anda beralih ke dialek yang berbeda, LookML untuk tabel turunan native akan tetap valid karena Looker menghasilkan SQL yang sesuai dengan koneksi database Anda.

Anda dapat menentukan LookML untuk tabel turunan bawaan secara manual. Namun, cara termudah untuk membuat tabel turunan native adalah dengan meminta Looker membuat tabel turunan dari kueri Jelajah. Di Eksplorasi, pilih kolom yang ingin Anda gunakan dalam tabel turunan, lalu gunakan opsi Dapatkan LookML > Tabel Turunan dari menu roda gigi Eksplorasi untuk mendapatkan LookML. Looker membuat LookML yang Anda perlukan untuk membuat tabel turunan dari kueri Jelajah Anda, termasuk definisi kolom yang relevan dari model LookML Anda yang diperlukan untuk membuat kolom untuk tabel turunan Anda. Anda dapat menyalin LookML ke dalam file tampilan di project untuk membuat tabel turunan.

Contoh berikut menunjukkan tabel turunan native yang mengidentifikasi 100 pengguna teratas dengan total pembelanjaan tertinggi di semua pesanan mereka:

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Saat Anda membuat kueri tabel turunan native di Eksplorasi, semua detail kueri SQL untuk tabel turunan native akan dibuat secara otomatis. Definisi kolom tampilan LookML dengan tabel turunan native disimpulkan dari definisi Eksplorasi pokok, sehingga tidak perlu mengulangi definisi apa pun.

Tabel turunan persisten (PDT)

Di Looker, Anda dapat membuat tabel turunan sementara dan tabel turunan persisten. Setelah membuat tabel turunan berbasis SQL atau tabel turunan native, Anda dapat menambahkan persistensi sehingga Looker menulis tabel ke dalam skema sementara di database Anda dan meregenerasinya sesuai jadwal yang Anda tentukan. Untuk mengetahui informasi selengkapnya, lihat Tabel turunan di Looker.