Tabel turunan membuka berbagai kemungkinan analisis tingkat lanjut, tetapi tabel tersebut mungkin sulit untuk didekati, diterapkan, dan dipecahkan masalahnya. Cookbook ini berisi kasus penggunaan tabel turunan paling populer di Looker.
Halaman ini berisi contoh berikut:
- Membuat meja pada pukul 3 pagi setiap hari
- Menambahkan data baru ke tabel besar
- Menggunakan fungsi jendela SQL
- Membuat kolom turunan untuk nilai yang dihitung
- Strategi pengoptimalan
- Menggunakan PDT untuk menguji pengoptimalan
UNIONdua tabel- Menjumlahkan penjumlahan (yang melakukan dimensi ukuran)
- Tabel gabungan dengan awareness gabungan
Resource tabel turunan
Buku resep ini mengasumsikan bahwa Anda memiliki pemahaman pengantar tentang LookML dan tabel turunan. Anda harus merasa nyaman saat membuat tampilan dan mengedit file model. Jika Anda menginginkan penyegaran tentang salah satu topik ini, lihat referensi berikut:
- Tabel turunan
- Istilah dan konsep LookML
- Membuat tabel turunan native
- Referensi parameter
derived_table - Menyimpan kueri dalam cache dan mem-build ulang PDT dengan grup data
Membuat meja pada pukul 3 pagi setiap hari
Data dalam contoh ini masuk pada pukul 2 dini hari setiap hari. Hasil kueri pada data ini akan sama, baik dijalankan pada pukul 3 pagi atau pukul 9 malam. Oleh karena itu, masuk akal untuk membuat tabel sekali sehari dan membiarkan pengguna menarik hasil dari cache.
Dengan menyertakan grup data dalam file model, Anda dapat menggunakannya kembali dengan beberapa tabel dan Jelajah. Grup data ini berisi parameter sql_trigger_value yang memberi tahu grup data kapan harus memicu dan membuat ulang tabel turunan.
Untuk contoh ekspresi pemicu lainnya, lihat dokumentasi sql_trigger_value.
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
Tambahkan parameter datagroup_trigger ke definisi derived_table di file tampilan, dan tentukan nama grup data yang ingin Anda gunakan. Dalam contoh ini, grup data adalah standard_data_load.
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
Menambahkan data baru ke tabel besar
PDT inkremental adalah tabel turunan persisten yang dibuat Looker dengan menambahkan data baru ke tabel, bukan mem-build ulang tabel secara keseluruhan.
Contoh berikutnya dibuat berdasarkan contoh tabel orders untuk menunjukkan bagaimana tabel dibuat secara inkremental. Data pesanan baru masuk setiap hari dan dapat ditambahkan ke tabel yang ada saat Anda menambahkan parameter increment_key dan parameter increment_offset.
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
Nilai increment_key disetel ke created_at, yang merupakan penambahan waktu saat data baru harus dikueri dan ditambahkan ke PDT dalam contoh ini.
Nilai increment_offset disetel ke 3 untuk menentukan jumlah jangka waktu sebelumnya (pada tingkat perincian kunci penambahan) yang dibuat ulang untuk memperhitungkan data yang terlambat tiba.
Menggunakan fungsi jendela SQL
Beberapa dialek database mendukung fungsi jendela, terutama untuk membuat nomor urut, {i>primary key<i}, total berjalan dan kumulatif, serta penghitungan multi-baris yang berguna lainnya. Setelah kueri utama dieksekusi, semua deklarasi derived_column dieksekusi dalam penerusan 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 Anda. Saat merujuk ke nilai, Anda harus menggunakan nama kolom seperti yang didefinisikan dalam tabel turunan bawaan.
Contoh berikut menunjukkan cara membuat tabel turunan native yang menyertakan kolom user_id, order_id, dan created_time. Anda kemudian akan menggunakan kolom turunan dengan fungsi jendela SQL ROW_NUMBER() untuk 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
}
}
Membuat kolom turunan untuk nilai yang dihitung
Anda dapat menambahkan parameter derived_column untuk menentukan kolom yang tidak ada di 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 dengan menggunakan parameter column. Kolom turunan tidak dapat menyertakan fungsi agregat, tetapi dapat menyertakan kalkulasi yang dapat dilakukan pada satu baris tabel.
Contoh ini membuat kolom average_customer_order, 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: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
Strategi pengoptimalan
Karena PDT disimpan dalam database, Anda harus mengoptimalkan PDT dengan menggunakan strategi berikut, seperti yang didukung oleh dialek Anda:
Misalnya, untuk menambahkan persistensi, Anda dapat menetapkan PDT untuk di-build ulang saat orders_datagroup datagroup terpicu, lalu Anda dapat menambahkan indeks di customer_id dan first_order, seperti yang ditunjukkan berikutnya:
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
Jika Anda tidak menambahkan indeks (atau yang setara dengan dialek Anda), Looker akan memperingatkan bahwa Anda harus melakukannya untuk meningkatkan performa kueri.
Menggunakan PDT untuk menguji pengoptimalan
Anda dapat menggunakan PDT untuk menguji berbagai opsi pengindeksan, distribusi, dan pengoptimalan lainnya tanpa memerlukan banyak dukungan dari developer DBA atau ETL.
Pertimbangkan kasus di mana Anda memiliki tabel tetapi ingin menguji indeks yang berbeda. LookML awal Anda untuk tampilan mungkin terlihat seperti berikut:
view: customer {
sql_table_name: warehouse.customer ;;
}
Untuk menguji strategi pengoptimalan, Anda dapat menggunakan parameter indexes untuk menambahkan indeks ke LookML, seperti yang ditunjukkan berikutnya:
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
Mengkueri tampilan sekali untuk menghasilkan PDT. Kemudian, jalankan kueri pengujian dan bandingkan hasilnya. Jika hasilnya positif, Anda dapat meminta tim DBA atau ETL Anda untuk menambahkan indeks ke tabel asli.
UNION dua tabel
Anda dapat menjalankan operator UNION atau UNION ALL SQL di kedua tabel turunan jika dialek SQL Anda mendukungnya. Operator UNION dan UNION ALL menggabungkan set hasil dua kueri.
Contoh ini menunjukkan tampilan tabel turunan berbasis SQL dengan UNION:
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
Pernyataan UNION dalam parameter sql menghasilkan tabel turunan yang menggabungkan hasil dari kedua kueri tersebut.
Perbedaan antara UNION dan UNION ALL adalah UNION ALL tidak menghapus baris duplikat. Ada pertimbangan performa yang perlu diingat saat menggunakan UNION versus UNION ALL, karena server database harus melakukan pekerjaan tambahan untuk menghapus baris duplikat.
Menjumlahkan penjumlahan (yang melakukan dimensi ukuran)
Sebagai aturan umum dalam SQL — dan, dengan demikian, Looker — Anda tidak dapat mengelompokkan kueri berdasarkan hasil fungsi agregat (direpresentasikan di Looker sebagai measures). Anda hanya dapat mengelompokkan menurut kolom yang tidak diagregasi (direpresentasikan di Looker sebagai dimensi).
Untuk mengelompokkan berdasarkan agregat (misalnya untuk menjumlahkan), Anda perlu "melakukan dimensi" ukuran. Salah satu cara untuk melakukan ini adalah dengan menggunakan tabel turunan, yang secara efektif membuat sub kueri dari agregat.
Dimulai dengan Jelajah, Looker dapat membuat LookML untuk semua atau sebagian besar tabel turunan Anda. Cukup buat Explore dan pilih semua kolom yang ingin Anda sertakan dalam tabel turunan. Kemudian, untuk menghasilkan tabel turunan native (atau berbasis SQL) LookML, ikuti langkah-langkah berikut:
Klik menu roda gigi Explore dan pilih Get LookML.
Untuk melihat LookML guna membuat tabel turunan native untuk Explore, klik tab Derived Table.
Menyalin LookML.
Setelah menyalin LookML yang dihasilkan, tempel ke file view dengan mengikuti langkah-langkah berikut:
Di Mode Pengembangan, buka file project Anda.
Klik + di bagian atas daftar file project di Looker IDE, lalu pilih Create View. Atau, untuk membuat file di dalam folder, klik menu folder dan pilih Buat Tampilan.
Tetapkan nama tampilan ke sesuatu yang bermakna.
Jika perlu, ubah nama kolom, tentukan kolom turunan, dan tambahkan filter.
Tabel gabungan dengan awareness gabungan
Di Looker, Anda mungkin sering menemukan set data atau tabel yang sangat besar yang, agar berperforma tinggi, memerlukan tabel agregasi atau penggabungan.
Dengan awareness gabungan Looker, Anda dapat membuat tabel gabungan terlebih dahulu ke berbagai tingkat perincian, dimensi, dan agregasi; serta dapat memberi tahu Looker cara menggunakannya dalam Jelajah yang ada. Kueri kemudian akan menggunakan tabel gabungan ini jika Looker dianggap sesuai, tanpa input pengguna apa pun. Hal ini akan mengurangi ukuran kueri, mengurangi waktu tunggu, dan meningkatkan pengalaman pengguna.
Berikut adalah implementasi yang sangat sederhana dalam model Looker untuk menunjukkan betapa ringannya awareness gabungan. Dengan tabel hipotesis penerbangan dalam database dengan baris untuk setiap penerbangan yang dicatat melalui FAA, Anda dapat membuat model tabel ini di Looker dengan tampilan dan Jelajahnya sendiri. Berikut ini adalah LookML untuk tabel gabungan yang dapat Anda tentukan untuk Explore:
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
Dengan tabel gabungan ini, pengguna dapat membuat kueri Jelajah flights, dan Looker akan otomatis menggunakan tabel gabungan untuk menjawab kueri. Untuk panduan yang lebih mendetail tentang awareness gabungan, kunjungi Tutorial awareness gabungan.