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 SELECT SQL. Di Looker, jenis tampilan ini disebut tabel turunan. Tabel turunan adalah kueri yang hasilnya digunakan seolah-olah hasilnya adalah tabel sebenarnya dalam database.

Dalam panduan ini, Anda akan mempelajari topik berikut:

Cara Looker membuat SQL untuk tabel turunan

Saat Anda membuat kueri tabel turunan di LookML, Looker menerjemahkan kueri Anda menjadi pernyataan SQL sebagai Ekspresi Tabel Umum (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 membuat kueri 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 membuat 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 langsung dalam LookML menggunakan parameter sql dalam parameter derived_table tampilan. Hal ini memungkinkan Anda menentukan kolom tabel turunan menggunakan SQL.

Misalnya, contoh LookML 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 Jelajahi, Looker menggunakan pernyataan SELECT SQL 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 di database Anda berubah, nama tabel harus diperbarui di beberapa tempat, termasuk dalam definisi tabel turunan berbasis SQL.
  • SQL dalam definisi tabel turunan Anda harus dalam 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 sekali di model LookML. Karena tabel turunan native Anda mengarah ke objek LookML yang ada tempat tabel database ditentukan, tabel turunan native Anda 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 native secara manual. Namun, cara termudah untuk membuat tabel turunan native adalah dengan meminta Looker membuat tabel turunan dari kueri Jelajah. Di Jelajahi, pilih kolom yang ingin Anda gunakan di tabel turunan, lalu gunakan opsi Dapatkan LookML > Tabel Turunan dari menu roda gigi Jelajahi untuk mendapatkan LookML. Looker menghasilkan LookML yang Anda perlukan untuk membuat tabel turunan dari kueri Jelajah, termasuk definisi kolom yang relevan dari model LookML yang diperlukan untuk membuat kolom untuk tabel turunan. 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 Jelajahi, semua detail kueri SQL untuk tabel turunan native akan dibuat secara otomatis. Definisi kolom tampilan LookML dengan tabel turunan native disimpulkan dari definisi Jelajah yang mendasarinya, sehingga Anda 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 awal di database dan membuatnya ulang sesuai jadwal yang Anda tentukan. Untuk informasi selengkapnya, lihat Tabel turunan di Looker.