Cookbook Looker: Mendapatkan hasil maksimal dari tabel turunan di Looker

Tabel turunan membuka berbagai kemungkinan analisis lanjutan, tetapi dapat menjadi hal yang menakutkan untuk didekati, diterapkan, dan dipecahkan masalahnya. Cookbook ini berisi kasus penggunaan tabel turunan yang paling populer di Looker.

Halaman ini berisi contoh berikut:

Resource tabel turunan

Cookbook ini mengasumsikan bahwa Anda memiliki pemahaman pengantar tentang LookML dan tabel turunan. Anda harus sudah terbiasa membuat tampilan dan mengedit file model. Jika Anda ingin mengulang materi tentang salah satu topik ini, lihat referensi berikut:

Membuat tabel pada pukul 03.00 setiap hari

Data dalam contoh ini masuk pada pukul 02.00 setiap hari. Hasil kueri pada data ini akan sama, baik dijalankan pada pukul 03.00 atau pukul 21.00. Oleh karena itu, sebaiknya buat tabel sekali sehari dan izinkan pengguna mengambil hasil dari cache.

Menyertakan grup data dalam file model memungkinkan Anda menggunakannya kembali dengan beberapa tabel dan Penjelajahan. Grup data ini berisi parameter sql_trigger_value yang memberi tahu grup data kapan harus memicu dan mem-build 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 dalam file tampilan, dan tentukan nama grup data yang ingin Anda gunakan. Dalam contoh ini, datagroup-nya 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 membuat ulang tabel secara keseluruhan.

Contoh berikutnya dibuat berdasarkan contoh tabel orders untuk menunjukkan cara tabel dibuat secara bertahap. 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 ditetapkan ke created_at, yang merupakan penambahan waktu untuk membuat kueri data baru dan menambahkannya ke PDT dalam contoh ini.

Nilai increment_offset ditetapkan ke 3 untuk menentukan jumlah jangka waktu sebelumnya (pada tingkat perincian kunci inkremental) yang dibuat ulang untuk memperhitungkan data yang terlambat.

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 Anda. Saat merujuk ke nilai, Anda harus menggunakan nama kolom seperti yang ditentukan dalam tabel turunan native.

Contoh berikut menunjukkan cara membuat tabel turunan native yang menyertakan kolom user_id, order_id, dan created_time. Kemudian, Anda 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 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 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 di database, Anda harus mengoptimalkan PDT menggunakan strategi berikut, seperti yang didukung oleh dialek Anda:

Misalnya, untuk menambahkan persistensi, Anda dapat menetapkan PDT untuk membuat ulang saat grup data orders_datagroup dipicu, lalu Anda dapat menambahkan indeks di customer_id dan first_order, seperti yang ditunjukkan di bawah:

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 untuk dialek Anda), Looker akan memperingatkan Anda bahwa Anda harus melakukannya untuk meningkatkan performa kueri.

Menggunakan PDT untuk menguji pengoptimalan

Anda dapat menggunakan PDT untuk menguji berbagai pengindeksan, distribusi, dan opsi pengoptimalan lainnya tanpa memerlukan banyak dukungan dari developer DBA atau ETL.

Pertimbangkan kasus saat 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 di bawah:

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]
  }
}

Buat kueri tampilan sekali untuk membuat PDT. Kemudian, jalankan kueri pengujian dan bandingkan hasilnya. Jika hasilnya positif, Anda dapat meminta tim DBA atau ETL 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 kumpulan hasil dari 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.

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.

Mengambil jumlah dari jumlah (membuat dimensi pengukuran)

Sebagai aturan umum di SQL — dan, secara luas, Looker — Anda tidak dapat mengelompokkan kueri berdasarkan hasil fungsi agregat (diwakili di Looker sebagai ukuran). Anda hanya dapat mengelompokkan berdasarkan kolom yang tidak digabungkan (diwakili di Looker sebagai dimensi).

Untuk mengelompokkan menurut agregat (misalnya, untuk mengambil jumlah dari jumlah), Anda perlu "mendimensialisasikan" pengukuran. Salah satu cara untuk melakukannya adalah dengan menggunakan tabel turunan, yang secara efektif membuat subkueri agregat.

Mulai dari Jelajahi, 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 (atau berbasis SQL), ikuti langkah-langkah berikut:

  1. Klik menu roda gigi Jelajah, lalu pilih Dapatkan LookML.

  2. Untuk melihat LookML guna membuat tabel turunan native untuk Jelajah, klik tab Tabel Turunan.

  3. Salin LookML.

Setelah menyalin LookML yang dihasilkan, tempelkan ke file tampilan dengan mengikuti langkah-langkah berikut:

  1. Dalam Mode Pengembangan, buka file project.

  2. Klik + di bagian atas daftar file project di IDE Looker, lalu pilih Create View. Atau, untuk membuat file di dalam folder, klik menu folder, lalu pilih Create View.

  3. Tetapkan nama tampilan ke sesuatu yang bermakna.

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

Tabel gabungan dengan kesadaran agregat

Di Looker, Anda mungkin sering menemukan set data atau tabel yang sangat besar yang, agar berperforma baik, memerlukan tabel agregasi atau gabungan.

Dengan kesadaran agregat Looker, Anda dapat membuat tabel agregat secara otomatis ke berbagai tingkat perincian, dimensi, dan agregasi; serta Anda dapat memberi tahu Looker cara menggunakannya dalam Jelajah yang ada. Kueri kemudian akan menggunakan tabel gabungan ini jika Looker menganggapnya sesuai, tanpa input pengguna. Hal ini akan mengurangi ukuran kueri, mengurangi waktu tunggu, dan meningkatkan pengalaman pengguna.

Berikut ini menunjukkan implementasi yang sangat sederhana dalam model Looker untuk menunjukkan betapa ringan kesadaran gabungan. Dengan tabel penerbangan hipotetis di database yang memiliki baris untuk setiap penerbangan yang dicatat melalui FAA, Anda dapat membuat model tabel ini di Looker dengan tampilan dan Jelajahnya sendiri. Berikut adalah LookML untuk tabel gabungan yang dapat Anda tentukan untuk Jelajahi:

  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 flights Jelajahi, dan Looker akan otomatis menggunakan tabel gabungan untuk menjawab kueri. Untuk panduan kesadaran agregat yang lebih mendetail, buka Tutorial kesadaran agregat.