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 memodelkan 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 Eksplorasi 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 Eksplorasi untuk mulai menentukan tabel turunan native

Dimulai dengan Eksplorasi, Looker dapat membuat LookML untuk semua atau sebagian besar tabel turunan Anda. Cukup buat Eksplorasi dan pilih semua kolom yang ingin Anda sertakan 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 Eksplorasi.

   

3. Salin LookML.

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

1. Dalam Mode Pengembangan, buka file project Anda.

2. Klik + di bagian atas daftar file project di Looker IDE, lalu pilih Buat Tampilan. 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 dalam Eksplorasi, 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 Lengkap Kolom di bagian Deret dalam setelan visualisasi atau dengan menggunakan parameter view_label dengan versi jamak dari nama tampilan Anda.

Mendefinisikan tabel turunan native di LookML

Baik Anda menggunakan tabel turunan yang dideklarasikan di SQL atau LookML native, output kueri derived_table's adalah tabel dengan sekumpulan kolom. Saat tabel turunan dinyatakan dalam SQL, nama kolom output tersirat dalam 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, mencakup 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 dengan menggunakan Eksplorasi order_items yang ditentukan dan beberapa kolom Eksplorasi 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 memungkinkan kolom referensi

Dalam file tampilan tabel turunan native, Anda menggunakan parameter explore_source untuk mengarah ke Eksplorasi 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 Eksplorasi. Jika Anda tidak memiliki pernyataan include, Looker IDE tidak akan autosuggest nama kolom atau memverifikasi referensi kolom saat Anda membuat tabel turunan native. Sebagai gantinya, Anda dapat menggunakan Validator LookML untuk memverifikasi kolom yang Anda rujuk dalam tabel turunan native.

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

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

Catatan: Saat Anda membuat file Jelajah, file tersebut harus memiliki ekstensi .explore.lkml di akhir nama filenya. Jika Anda mengupload file, Anda harus menggunakan ekstensi .explore.lkml lengkap. Jika Anda menggunakan IDE Looker 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 Jelajah terpisah dan menggunakan parameter include untuk mengarah ke file Jelajah dalam file tampilan tabel turunan native, pastikan file LookML Anda memenuhi persyaratan berikut:

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

Catatan: File Explore akan memantau koneksi model yang disertakan dalam file Explore. 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 disertakan 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 Eksplorasi asli.

Sering kali, Anda akan menginginkan nama kolom yang berbeda dalam tabel output dengan nama kolom di Eksplorasi asli. Contoh sebelumnya menghasilkan penghitungan nilai umur per pengguna menggunakan Eksplorasi order_items. Dalam tabel output, total_revenue sebenarnya adalah 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 tidak disertakan dalam deklarasi kolom, parameter tersebut diasumsikan sebagai <explore_name>.<field_name>. Misalnya, jika Anda telah menentukan explore_source: order_items, maka

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 dalam Eksplorasi parameter explore_source. Setiap parameter derived_column memiliki parameter sql yang menentukan cara membuat nilai.

Penghitungan sql Anda 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 menambahkan kolom average_customer_order terhitung, yang dihitung dari kolom lifetime_customer_value dan lifetime_number_of_orders dalam tabel turunan bawaan.

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 urut, kunci utama, total berjalan dan kumulatif, serta perhitungan multi-baris berguna lainnya. Setelah kueri utama dieksekusi, semua deklarasi derived_column akan dieksekusi dalam proses terpisah.

Jika dialek database Anda mendukung fungsi jendela, Anda dapat menggunakannya dalam 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(), kolom ini menghitung kolom yang berisi nomor urut 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

Misalkan Anda ingin membuat tabel turunan nilai pelanggan selama 90 hari terakhir. Anda menginginkan perhitungan yang sama seperti yang Anda lakukan pada contoh sebelumnya, tetapi Anda 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 mencakup nama kolom dengan nama tampilan. Anda harus menyertakan nama tampilan untuk kolom, meskipun kolom tersebut berada di tampilan dasar Eksplorasi.

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 diterapkan Looker hanya ke versi pengembangan tabel turunan, yang berarti Anda dapat membuat versi tabel yang lebih kecil dan difilter untuk melakukan iterasi dan pengujian tanpa menunggu tabel lengkap dibuat setelah setiap perubahan.

Parameter dev_filters bekerja 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 ber-template:

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

Pada dasarnya, hal ini 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 pokok.

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

Dalam 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 Eksplorasi ke subkueri tabel turunan native. Lihat halaman dokumentasi parameter explore_source untuk mengetahui 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, Eksplorasi 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 asli 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 asli, tabel turunan asli tidak akan melakukan konversi zona waktu pada data berbasis waktu, dan sebagai gantinya, data berbasis waktu akan menggunakan zona waktu database secara default.

Jika tabel turunan bawaan tidak persisten, Anda dapat menyetel nilai zona waktu ke "query_timezone" untuk otomatis menggunakan zona waktu kueri yang sedang berjalan.