Menggabungkan SQL dan merujuk ke objek LookML

Untuk menulis LookML yang efektif, Anda harus dapat mereferensikan dimensi, ukuran, tampilan, atau tabel turunan yang ada meskipun tidak berada dalam cakupan saat ini. Anda juga perlu mereferensikan kolom dalam tabel pokok dan menggunakan panggilan fungsi dialek database untuk memanipulasi nilai tersebut.

Operator penggantian ($)

Operator penggantian, $, membuat kode LookML lebih fleksibel dan modular, sehingga Anda dapat mereferensikan tampilan dan tabel turunan, kolom dalam tabel SQL, atau dimensi dan ukuran LookML lainnya. Hal ini baik karena dua alasan. Pertama, Anda mungkin telah mengerjakan dimensi atau ukuran yang sangat rumit, dan Anda tidak perlu menuliskan semua kompleksitasnya lagi. Kedua, jika Anda mengubah sesuatu pada dimensi atau ukuran, perubahan tersebut dapat diterapkan ke semua hal lain yang bergantung padanya.

Ada beberapa cara untuk menggunakan operator penggantian:

${TABLE}.column_name mereferensikan kolom dalam tabel yang terhubung ke tampilan yang sedang Anda kerjakan. Contoh:

dimension: customer_id {
  type: number
  sql: ${TABLE}.customer_id ;;
}

${field_name} mereferensikan dimensi atau ukuran dalam tampilan yang sedang Anda kerjakan. Contoh:

measure: total_population {
  type: sum
  sql: ${population} ;;
}

${view_name.field_name} mereferensikan dimensi atau ukuran dari tampilan lain. Contoh:

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders} ;;
}

${view_name.SQL_TABLE_NAME} mereferensikan tampilan atau tabel turunan lain. Perhatikan bahwa SQL_TABLE_NAME dalam referensi ini adalah string literal; Anda tidak perlu menggantinya dengan apa pun. Contoh:

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}

${view_name.SQL_TABLE_NAME} tidak berfungsi dengan parameter sql_trigger yang digunakan dengan datagroups.

Penentuan cakupan dan penamaan

Anda dapat memberi nama Eksplorasi, tampilan, kolom, dan set. ID Looker ini ditulis tanpa tanda petik.

Kolom dan set LookML memiliki nama lengkap dan nama pendek:

  • Nama lengkap memiliki format <view>.<field-name | set-name>. Sisi kiri menunjukkan cakupan, yaitu tampilan yang berisi kolom atau set. Sisi kanan menentukan nama kolom atau kumpulan tertentu.
  • Nama pendek hanya berbentuk <field-name | set-name>, tanpa titik pemisah. Looker memperluas nama singkat menjadi nama lengkap menggunakan cakupan tempat nama tersebut digunakan.

Berikut adalah contoh yang menunjukkan banyak bentuk nama dan cakupan. Ini adalah grup kolom yang tidak realistis, tetapi ditampilkan untuk menunjukkan berbagai kemungkinan ekspresi cakupan.

view: orders {                   # "orders" becomes the containing scope
  measure: count {               # short name, equivalent to orders.count
    type: count
  }
  dimension: customer_id {       # short name, equivalent to orders.customer_id
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  dimension: customer_address {  # short name, equivalent to orders.customer_address
    sql: ${customer.address} ;;  # full name, references a field defined in the "customer" view
  }
  set: drill_fields {            # short name, equivalent to orders.drill_fields
    fields: [
      count,                     # short name, equivalent to orders.count
      customer.id                # full name, references a field defined in the "customer" view
    ]
  }
}

Dalam deklarasi dimension: customer_address, perhatikan bahwa tampilan pokok untuk blok SQL (customer) berbeda dengan cakupan tampilan yang melingkupi (orders). Hal ini dapat berguna saat Anda perlu membandingkan kolom antara dua tampilan yang berbeda.

Jika tampilan (kita akan menyebutnya "tampilan A") merujuk ke kolom yang ditentukan dalam tampilan lain (kita akan menyebutnya "tampilan B"), ada beberapa hal yang perlu diingat:

  1. File tampilan B harus disertakan dalam model yang sama dengan tampilan A, menggunakan parameter include.
  2. Tampilan B harus digabungkan ke tampilan A di satu atau beberapa Jelajah. Lihat halaman Bekerja dengan gabungan di LookML untuk mempelajari gabungan.

Dialek SQL

Looker mendukung banyak jenis database, seperti MySQL, Postgres, Redshift, BigQuery, dan sebagainya. Setiap database mendukung kumpulan fitur yang sedikit berbeda dengan nama fungsi yang berbeda, yang disebut sebagai dialek SQL.

LookML dirancang untuk berfungsi dengan semua dialek SQL, dan LookML tidak lebih memilih satu dialek daripada yang lain. Namun, Anda harus menyertakan ekspresi kode SQL (dikenal sebagai blok SQL) dalam parameter LookML tertentu. Dengan parameter ini, Looker meneruskan ekspresi SQL langsung ke database Anda, sehingga Anda harus menggunakan dialek SQL yang cocok dengan database Anda. Misalnya, jika Anda menggunakan fungsi SQL, fungsi tersebut harus didukung oleh database Anda.

Blok SQL

Beberapa parameter LookML mengharuskan Anda memberikan ekspresi SQL mentah agar Looker dapat memahami cara mengambil data dari database Anda.

Parameter LookML yang dimulai dengan sql_ mengharapkan ekspresi SQL dalam beberapa bentuk. Contohnya adalah: sql_always_where, sql_on, dan sql_table_name. Parameter LookML yang paling umum untuk blok SQL adalah sql, yang digunakan dalam definisi kolom dimensi dan ukuran untuk menentukan ekspresi SQL yang menentukan dimensi atau ukuran.

Kode yang Anda tentukan dalam blok SQL dapat sesederhana satu nama kolom atau serumit subselect yang berkorelasi. Kontennya bisa sangat kompleks, yang mengakomodasi hampir semua kebutuhan yang mungkin Anda miliki untuk mengekspresikan logika kueri kustom dalam SQL mentah. Perhatikan bahwa kode yang Anda gunakan dalam blok SQL harus cocok dengan dialek SQL yang digunakan oleh database.

Contoh blok SQL untuk dimensi dan ukuran

Berikut adalah contoh blok SQL untuk dimensi dan ukuran. Operator penggantian LookML ($) dapat membuat deklarasi sql ini tampak tidak seperti SQL. Namun, setelah penggantian terjadi, string yang dihasilkan adalah SQL murni, yang dimasukkan Looker ke dalam klausa SELECT kueri.

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;   # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${order_items.cost} ;;   # Specify the field that you want to average
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: int
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

Seperti yang ditunjukkan dalam dua dimensi terakhir, blok SQL dapat menggunakan fungsi yang didukung oleh database yang mendasarinya (seperti fungsi MySQL CONCAT dan DATEDIFF dalam contoh ini).

Contoh blok SQL dengan subselect yang berkorelasi

Anda dapat menempatkan pernyataan SQL apa pun di blok SQL kolom, termasuk sub-pilihan yang berkorelasi. Berikut adalah contohnya:

view: customers {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
  dimension: first_order_id {
    sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;;
         # correlated subselect to derive the value for "first_order_id"
  }
}

Contoh blok SQL untuk tabel turunan

Tabel turunan menggunakan blok SQL untuk menentukan kueri yang menghasilkan tabel. Berikut adalah contohnya:

view: user_order_facts {
  derived_table: {
    sql:            # Get the number of orders for each user
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }
  # later, dimension declarations reference the derived column(s)

  dimension: lifetime_orders {
    type: number
  }
}

Referensi jenis kolom LookML

Saat mereferensikan kolom LookML yang ada dalam kolom lain, Anda dapat menginstruksikan Looker untuk memperlakukan kolom yang dirujuk sebagai jenis data tertentu dengan menggunakan titik dua ganda (::) diikuti dengan jenis yang diinginkan. Misalnya, jika Anda mereferensikan dimensi orders.created_date dalam kolom lain, Anda dapat menggunakan sintaksis ${orders.created_date::date} untuk memastikan bahwa kolom created_date akan diperlakukan sebagai kolom tanggal dalam SQL yang dihasilkan Looker, bukan diubah menjadi string.

Jenis data yang dapat Anda gunakan dalam referensi bergantung pada jenis data kolom asli yang Anda referensikan. Misalnya, jika Anda mereferensikan kolom string, satu-satunya jenis data yang dapat Anda tentukan adalah ::string. Berikut adalah daftar lengkap referensi jenis kolom yang diizinkan yang dapat Anda gunakan untuk setiap jenis kolom:

  • Dalam referensi ke kolom string, Anda dapat menggunakan ::string.
  • Dalam referensi ke kolom angka, Anda dapat menggunakan ::string dan ::number.
  • Dalam referensi ke kolom tanggal atau waktu, Anda dapat menggunakan ::string, ::date, dan ::datetime.

    Referensi yang menggunakan ::string dan ::date menampilkan data dalam zona waktu kueri, sedangkan referensi yang menggunakan ::datetime menampilkan data dalam zona waktu database.
  • Dalam referensi ke kolom yesno, Anda dapat menggunakan ::string, ::number, dan ::boolean.

    Referensi kolom yang menggunakan jenis ::boolean tidak tersedia untuk dialek database yang tidak mendukung jenis data Boolean.
  • Dalam referensi ke kolom lokasi, Anda dapat menggunakan ::latitude dan ::longitude.

Menggunakan referensi jenis kolom LookML dengan kolom tanggal

Misalnya, Anda memiliki dimensi enrollment_month dan dimensi graduation_month, yang keduanya dibuat dalam grup dimensi type: time. Dalam contoh ini, dimensi enrollment_month dihasilkan oleh grup dimensi type: time berikut:


dimension_group: enrollment {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.enrollment_date ;;
}

Demikian pula, dimensi graduation_month dibuat oleh grup dimensi type: time berikut:


dimension_group: graduation {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.graduation_date ;;
}

Dengan menggunakan dimensi enrollment_month dan graduation_month, Anda dapat menghitung jumlah bulan atau tahun yang berlalu antara pendaftaran dan kelulusan siswa dengan membuat grup dimensi type: duration. Namun, karena beberapa kolom tanggal diubah menjadi string dalam SQL yang dihasilkan Looker, menetapkan dimensi enrollment_month dan graduation_month sebagai nilai untuk sql_start dan sql_end dapat menyebabkan error.

Untuk menghindari error yang dihasilkan dari kolom waktu ini yang ditransmisikan sebagai string, salah satu opsi adalah membuat grup dimensi type: duration, yang mereferensikan jangka waktu raw dari grup dimensi enrollment dan graduation dalam parameter sql_start dan sql_end:


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_raw} ;;
  sql_end: ${graduation_raw} ;;
}

Di UI Eksplorasi, tindakan ini akan menghasilkan grup dimensi yang disebut Durasi Terdaftar, dengan masing-masing dimensi Bulan Terdaftar dan Tahun Terdaftar.

Alternatif yang lebih sederhana untuk menggunakan jangka waktu raw dalam grup dimensi type: duration adalah dengan menentukan jenis referensi ::date atau ::datetime untuk kolom yang dirujuk dalam parameter sql_start dan sql_end.


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_month::date} ;;
  sql_end: ${graduation_month::date} ;;
}

LookML dalam contoh ini juga membuat grup dimensi Durasi Terdaftar, tetapi penggunaan referensi ::date memungkinkan dimensi enrollment_month dan graduation_month digunakan tanpa menggunakan jangka waktu raw atau mentransmisikannya sebagai string dengan SQL.

Untuk contoh tambahan tentang cara referensi jenis kolom LookML dapat digunakan untuk membuat grup dimensi kustom type: duration, lihat halaman dokumentasi parameter dimension_group.

Sintaksis ini tidak tersedia dengan ukuran type: list, yang tidak dapat direferensikan mulai Looker 6.8.

Konstanta LookML

Parameter constant memungkinkan Anda menentukan konstanta yang dapat digunakan di seluruh project LookML. Dengan konstanta LookML, Anda dapat menentukan nilai satu kali dan mereferensikannya di bagian mana pun dalam project tempat string diterima, sehingga mengurangi pengulangan dalam kode LookML.

Konstanta harus dideklarasikan dalam file manifes project, dan nilai untuk konstanta harus berupa string. Misalnya, Anda dapat menentukan konstanta city dengan nilai "Okayama" sebagai berikut:

constant: city {
  value: "Okayama"
}

Konstanta city kemudian dapat direferensikan di seluruh project Anda menggunakan sintaksis @{city}. Misalnya, Anda dapat menggunakan konstanta city dengan parameter label di users Jelajahi:


explore: users {
  label: "@{city} Users"
}

Looker kemudian menampilkan Pengguna Okayama di menu Jelajahi dan di judul Jelajahi, bukan Pengguna default.

Untuk informasi selengkapnya dan contoh cara menggunakan konstanta LookML untuk menulis kode yang dapat digunakan kembali, lihat halaman dokumentasi parameter constant.