Membuat tabel turunan native

Tabel turunan adalah kueri yang hasilnya digunakan seolah-olah tabel turunan adalah tabel fisik dalam database. Tabel turunan native didasarkan pada kueri yang Anda tentukan menggunakan istilah LookML. Hal ini berbeda dengan tabel turunan berbasis SQL, yang didasarkan pada kueri yang Anda tentukan dengan istilah SQL. Dibandingkan dengan tabel turunan berbasis SQL, tabel turunan native jauh lebih mudah dibaca dan dipahami saat Anda membuat model data. Lihat bagian Tabel turunan native dan tabel turunan berbasis SQL di halaman dokumentasi Tabel turunan di Looker untuk mengetahui informasi selengkapnya.

Tabel turunan native dan berbasis SQL ditentukan di LookML menggunakan parameter derived_table di tingkat tampilan. Namun, dengan tabel turunan native, Anda tidak perlu membuat kueri SQL. Sebagai gantinya, Anda menggunakan parameter explore_source untuk menentukan Jelajah yang akan menjadi dasar tabel turunan, kolom yang diinginkan, dan karakteristik lain yang diinginkan.

Anda juga dapat meminta Looker membuat LookML tabel turunan dari kueri SQL Runner, seperti yang dijelaskan di halaman dokumentasi Menggunakan SQL Runner untuk membuat tabel turunan.

Menggunakan Jelajahi untuk mulai menentukan tabel turunan native

Mulai dari Jelajahi, Looker dapat membuat LookML untuk semua atau sebagian besar tabel turunan Anda. Cukup buat Eksplorasi dan pilih semua kolom yang ingin disertakan dalam tabel turunan. Kemudian, untuk membuat LookML tabel turunan native, ikuti langkah-langkah berikut:

1. Pilih menu roda gigi Jelajahi Tindakan, lalu pilih Dapatkan LookML.

   

2. Klik tab Tabel Turunan untuk melihat LookML guna membuat tabel turunan native untuk Jelajah.

   

3. Salin LookML.

Setelah Anda menyalin LookML yang dihasilkan, tempelkan ke dalam file tampilan:

1. Dalam Mode Pengembangan, buka file project.

2. Klik + di bagian atas daftar file project di IDE Looker, lalu pilih Create View. Atau, Anda dapat mengklik menu folder dan memilih Buat Tampilan dari menu untuk membuat file di dalam folder.

3. Tetapkan nama tampilan ke sesuatu yang bermakna.

4. Secara opsional, ubah nama kolom, tentukan kolom turunan, dan tambahkan filter.

Saat Anda menggunakan pengukuran type: count di Jelajah, visualisasi akan memberi label pada nilai yang dihasilkan dengan nama tampilan, bukan kata Jumlah. Untuk menghindari kebingungan, buat nama tampilan Anda dalam bentuk jamak. Anda dapat mengubah nama tampilan dengan memilih Tampilkan Nama Kolom Lengkap di bagian Deret di setelan visualisasi atau dengan menggunakan parameter view_label dengan versi jamak dari nama tampilan Anda.

Menentukan tabel turunan native di LookML

Baik Anda menggunakan tabel turunan yang dideklarasikan dalam SQL atau LookML native, output kueri derived_table adalah tabel dengan kumpulan kolom. Saat tabel turunan dinyatakan dalam SQL, nama kolom output tersirat oleh kueri SQL. Misalnya, kueri SQL berikut akan memiliki kolom output user_id, lifetime_number_of_orders, dan lifetime_customer_value:

SELECT
  user_id
  , COUNT(DISTINCT order_id) as lifetime_number_of_orders
  , SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1

Di Looker, kueri didasarkan pada Eksplorasi, menyertakan kolom ukuran dan dimensi, menambahkan filter yang berlaku, dan juga dapat menentukan urutan pengurutan. Tabel turunan native berisi semua elemen ini beserta nama output untuk kolom.

Contoh sederhana berikut menghasilkan tabel turunan dengan tiga kolom: user_id, lifetime_customer_value, dan lifetime_number_of_orders. Anda tidak perlu menulis kueri secara manual di SQL. Sebagai gantinya, Looker akan membuat kueri untuk Anda menggunakan order_items Jelajah yang ditentukan dan beberapa kolom Jelajah tersebut (order_items.user_id, order_items.total_revenue, dan order_items.order_count).

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: lifetime_number_of_orders {
        field: order_items.order_count
      }
      column: lifetime_customer_value {
        field: order_items.total_revenue
      }
    }
  }
  # Define the view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
}

Menggunakan pernyataan include untuk mengaktifkan kolom referensi

Dalam file tampilan tabel turunan native, Anda menggunakan parameter explore_source untuk mengarah ke Jelajahi dan menentukan kolom serta karakteristik lainnya untuk tabel turunan native.

Dalam file tampilan tabel turunan native, Anda tidak perlu menggunakan parameter include untuk mengarah ke file yang berisi definisi Jelajahi. Jika Anda tidak memiliki pernyataan include, IDE Looker tidak akan menawarkan nama kolom secara otomatis atau memverifikasi referensi kolom saat Anda mem-build tabel turunan native. Sebagai gantinya, Anda dapat menggunakan Validator LookML untuk memverifikasi kolom yang Anda referensikan dalam tabel turunan native.

Namun, jika Anda ingin mengaktifkan saran otomatis dan verifikasi kolom langsung di IDE Looker, atau jika Anda memiliki project LookML kompleks yang memiliki beberapa Jelajah dengan nama yang sama atau berpotensi untuk referensi melingkar, Anda dapat menggunakan parameter include untuk mengarah ke lokasi definisi Jelajah.

Eksplorasi sering kali ditentukan dalam file model, tetapi, dalam kasus tabel turunan native, sebaiknya buat file terpisah untuk Eksplorasi. File Jelajah LookML memiliki ekstensi file .explore.lkml, seperti yang dijelaskan dalam dokumentasi untuk Membuat File Jelajah. Dengan begitu, dalam file tampilan tabel turunan native, Anda dapat menyertakan satu file Jelajah, bukan seluruh file model.

Catatan: Saat Anda membuat file Jelajah, file tersebut harus memiliki ekstensi .explore.lkml di akhir nama filenya. Jika mengupload file, Anda harus menggunakan ekstensi .explore.lkml lengkap. Jika Anda menggunakan Looker IDE untuk membuat file, gunakan opsi File LookML generik. Looker akan menambahkan .lkml ke akhir nama file jika ekstensi tidak ada, tetapi Anda tetap harus menambahkan bagian .explore dari ekstensi ke nama file secara manual.

Jika Anda ingin membuat file Eksplorasi terpisah dan menggunakan parameter include untuk mengarah ke file Eksplorasi dalam file tampilan tabel turunan native, pastikan file LookML Anda memenuhi persyaratan berikut:

• File tampilan tabel turunan native harus menyertakan file Jelajahi. Contoh:
  • include: "/explores/order_items.explore.lkml"
• File Jelajah harus menyertakan file tampilan yang diperlukan. Contoh:
  • include: "/views/order_items.view.lkml"
  • include: "/views/users.view.lkml"
• Model harus menyertakan file Jelajah. Contoh:
  • include: "/explores/order_items.explore.lkml"

Catatan: File Jelajah akan memproses koneksi model yang menyertakan file Jelajah. Pertimbangkan fakta ini saat Anda menyertakan file Jelajah dalam model yang dikonfigurasi dengan koneksi yang berbeda dari model induk file Jelajah. Jika skema untuk koneksi model yang menyertakan berbeda dengan skema untuk koneksi model induk, hal ini dapat menyebabkan error kueri.

Menentukan kolom tabel turunan native

Seperti yang ditunjukkan dalam contoh sebelumnya, Anda menggunakan column untuk menentukan kolom output tabel turunan.

Menentukan nama kolom

Untuk kolom user_id, nama kolom cocok dengan nama kolom yang ditentukan di Jelajah asli.

Sering kali, Anda ingin nama kolom yang berbeda di tabel output dari nama kolom di Jelajahi asli. Contoh sebelumnya menghasilkan penghitungan nilai umur menurut pengguna menggunakan order_items Jelajahi. Dalam tabel output, total_revenue benar-benar merupakan lifetime_customer_value pelanggan.

Deklarasi column mendukung deklarasi nama output yang berbeda dari kolom input. Misalnya, kode berikut menginstruksikan Looker untuk "membuat kolom output bernama lifetime_value dari kolom order_items.total_revenue":

column: lifetime_value {
  field: order_items.total_revenue
}

Nama kolom tersirat

Jika parameter field dihilangkan dari deklarasi kolom, parameter tersebut diasumsikan sebagai <explore_name>.<field_name>. Misalnya, jika Anda telah menentukan explore_source: order_items,

column: user_id {
  field: order_items.user_id
}

setara dengan

column: user_id {}

Membuat kolom turunan untuk nilai yang dihitung

Anda dapat menambahkan parameter derived_column untuk menentukan kolom yang tidak ada di Jelajahi parameter explore_source. Setiap parameter derived_column memiliki parameter sql yang menentukan cara membuat nilai.

Penghitungan sql dapat menggunakan kolom apa pun yang telah Anda tentukan menggunakan parameter column. Kolom turunan tidak dapat menyertakan fungsi agregat, tetapi dapat menyertakan penghitungan yang dapat dilakukan pada satu baris tabel.

Contoh berikut menghasilkan tabel turunan yang sama dengan contoh sebelumnya, kecuali bahwa contoh ini menambahkan kolom average_customer_order yang dihitung, yang dihitung dari kolom lifetime_customer_value dan lifetime_number_of_orders dalam tabel turunan native.

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: lifetime_number_of_orders {
        field: order_items.order_count
      }
      column: lifetime_customer_value {
        field: order_items.total_revenue
      }
      derived_column: average_customer_order {
        sql:  lifetime_customer_value / lifetime_number_of_orders ;;
      }
    }
  }
  # Define the view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
  dimension: average_customer_order {
    type: number
  }
}

Menggunakan fungsi jendela SQL

Beberapa dialek database mendukung fungsi jendela, terutama untuk membuat nomor urutan, kunci utama, total berjalan dan kumulatif, serta penghitungan multi-baris lainnya yang berguna. Setelah kueri utama dieksekusi, deklarasi derived_column apa pun akan dieksekusi dalam penerusan terpisah.

Jika dialek database Anda mendukung fungsi jendela, Anda dapat menggunakannya di tabel turunan native. Buat parameter derived_column dengan parameter sql yang berisi fungsi jendela yang diinginkan. Saat merujuk ke nilai, Anda harus menggunakan nama kolom seperti yang ditentukan dalam tabel turunan native.

Contoh berikut membuat tabel turunan native yang menyertakan kolom user_id, order_id, dan created_time. Kemudian, menggunakan kolom turunan dengan fungsi jendela SQL ROW_NUMBER(), fungsi ini menghitung kolom yang berisi nomor urutan pesanan pelanggan.

view: user_order_sequences {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: order_id {
        field: order_items.order_id
      }
      column: created_time {
        field: order_items.created_time
      }
      derived_column: user_sequence {
        sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
      }
    }
  }
  dimension: order_id {
    hidden: yes
  }
  dimension: user_sequence {
    type: number
  }
}

Menambahkan filter ke tabel turunan native

Misalnya, Anda ingin membuat tabel turunan nilai pelanggan selama 90 hari terakhir. Anda menginginkan penghitungan yang sama seperti yang dilakukan dalam contoh sebelumnya, tetapi hanya ingin menyertakan pembelian dari 90 hari terakhir.

Anda hanya perlu menambahkan filter ke derived_table yang memfilter transaksi dalam 90 hari terakhir. Parameter filters untuk tabel turunan menggunakan sintaksis yang sama dengan yang Anda gunakan untuk membuat ukuran yang difilter.

Catatan: Dalam parameter filters, gunakan format view_name.field_name untuk menentukan cakupan nama kolom dengan nama tampilan. Anda harus menyertakan nama tampilan untuk kolom, meskipun kolom tersebut berada di tampilan dasar Jelajahi.

view: user_90_day_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: number_of_orders_90_day {
        field: order_items.order_count
      }
      column: customer_value_90_day {
        field: order_items.total_revenue
      }
      filters: [order_items.created_date: "90 days"]
    }
  }
  # Add define view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: number_of_orders_90_day {
    type: number
  }
  dimension: customer_value_90_day {
    type: number
  }
}

Filter akan ditambahkan ke klausa WHERE saat Looker menulis SQL untuk tabel turunan.

Selain itu, Anda dapat menggunakan subparameter dev_filters dari explore_source dengan tabel turunan native. Parameter dev_filters memungkinkan Anda menentukan filter yang hanya diterapkan Looker ke versi pengembangan tabel turunan, yang berarti Anda dapat mem-build versi tabel yang lebih kecil dan difilter untuk melakukan iterasi dan pengujian tanpa menunggu tabel lengkap di-build setelah setiap perubahan.

Parameter dev_filters berfungsi bersama dengan parameter filters sehingga semua filter diterapkan ke versi pengembangan tabel. Jika dev_filters dan filters menentukan filter untuk kolom yang sama, dev_filters akan diprioritaskan untuk versi pengembangan tabel.

Lihat Bekerja lebih cepat dalam Mode Pengembangan untuk mengetahui informasi selengkapnya.

Menggunakan filter dengan template

Anda dapat menggunakan bind_filters untuk menyertakan filter dengan template:

bind_filters: {
  to_field: users.created_date
  from_field: filtered_lookml_dt.filter_date
}

Hal ini pada dasarnya sama dengan menggunakan kode berikut dalam blok sql:

{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}

to_field adalah kolom tempat filter diterapkan. to_field harus berupa kolom dari explore_source yang mendasarinya.

from_field menentukan kolom tempat filter akan diambil, jika ada filter saat runtime.

Pada contoh bind_filters sebelumnya, Looker akan mengambil filter apa pun yang diterapkan ke kolom filtered_lookml_dt.filter_date dan menerapkan filter tersebut ke kolom users.created_date.

Anda juga dapat menggunakan subparameter bind_all_filters dari explore_source untuk meneruskan semua filter runtime dari Jelajah ke subkueri tabel turunan native. Lihat halaman dokumentasi parameter explore_source untuk informasi selengkapnya.

Mengurutkan dan membatasi tabel turunan native

Anda juga dapat mengurutkan dan membatasi tabel turunan, jika diinginkan:

sorts: [order_items.count: desc]
limit: 10

Ingat, Jelajah dapat menampilkan baris dalam urutan yang berbeda dari pengurutan yang mendasarinya.

Mengonversi tabel turunan native ke zona waktu yang berbeda

Anda dapat menentukan zona waktu untuk tabel turunan native menggunakan subparameter timezone:

timezone: "America/Los_Angeles"

Saat Anda menggunakan subparameter timezone, semua data berbasis waktu dalam tabel turunan native akan dikonversi ke zona waktu yang Anda tentukan. Lihat halaman dokumentasi nilai timezone untuk mengetahui daftar zona waktu yang didukung.

Jika Anda tidak menentukan zona waktu dalam definisi tabel turunan native, tabel turunan native tidak akan melakukan konversi zona waktu pada data berbasis waktu, dan sebagai gantinya, data berbasis waktu akan ditetapkan secara default ke zona waktu database Anda.

Jika tabel turunan native tidak permanen, Anda dapat menetapkan nilai zona waktu ke "query_timezone" untuk otomatis menggunakan zona waktu kueri yang sedang berjalan.