Menggabungkan SQL dan merujuk ke objek LookML

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

Operator substitusi ($)

Operator substitusi, $, membuat kode LookML lebih dapat digunakan kembali dan modular, sehingga Anda dapat mereferensikan tampilan lain dan tabel turunan, kolom dalam tabel SQL, atau dimensi dan ukuran LookML. Ini bagus karena dua alasan. Pertama, Anda mungkin sudah memperhitungkan dimensi atau ukuran yang sangat rumit, dan Anda tidak perlu menuliskan semua kerumitan itu lagi. Kedua, jika Anda mengubah sesuatu terkait dimensi atau ukuran, perubahan tersebut dapat menyebar ke hal lain yang bergantung pada dimensi atau ukuran tersebut.

Ada beberapa cara untuk menggunakan operator substitusi:

${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 lain atau tabel turunan. 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 bersama datagroups.

Cakupan dan penamaan

Anda dapat memberi nama Jelajah, tampilan, kolom, dan kumpulan. ID Looker ini ditulis tanpa tanda kutip.

Kolom dan set LookML memiliki nama lengkap dan nama pendek:

  • Nama lengkap adalah format <view>.<field-name | set-name>. Sisi kiri menunjukkan cakupan, yaitu tampilan yang berisi kolom atau yang ditetapkan. Sisi kanan menentukan kolom atau nama kumpulan tertentu.
  • Nama pendek cukup berbentuk <field-name | set-name>, tanpa periode pemisahan. Looker memperluas nama pendek menjadi nama lengkap menggunakan cakupan yang mencakup nama tersebut.

Di bawah ini adalah contoh yang menampilkan berbagai 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
    ]
  }
}

Pada deklarasi dimension: customer_address di atas, perhatikan bahwa tampilan dasar untuk blok SQL (customer) berbeda dengan cakupan tampilan yang mencakup (orders). Hal ini berguna saat Anda perlu membandingkan kolom antara dua tampilan yang berbeda.

Jika tampilan (kami akan menyebutnya "tampilan A") merujuk pada kolom yang ditentukan dalam tampilan yang berbeda (kami akan menyebutnya "tampilan B"), ada beberapa hal yang perlu diperhatikan:

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

Dialek SQL

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

LookML dirancang untuk bekerja dengan semua dialek SQL, dan LookML tidak lebih menyukai 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, sehingga Anda harus menggunakan dialek SQL yang cocok dengan database. Misalnya, jika Anda menggunakan fungsi SQL, fungsi tersebut harus merupakan fungsi yang didukung database Anda.

Blok SQL

Beberapa parameter LookML mengharuskan Anda menyediakan ekspresi SQL mentah sehingga 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 pengukuran untuk menentukan ekspresi SQL yang menentukan dimensi atau ukuran.

Kode yang Anda tentukan dalam blok SQL bisa sesederhana satu nama kolom atau serumit sub-pilihan yang berkorelasi. Kontennya bisa sangat kompleks, mengakomodasi hampir semua kebutuhan Anda mungkin harus 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

Di bawah ini adalah contoh blok SQL untuk dimensi dan ukuran. Operator substitusi LookML ($) dapat membuat deklarasi sql ini tampak menipu tidak seperti SQL. Namun, setelah substitusi 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 di atas, blok SQL dapat menggunakan fungsi yang didukung oleh database yang mendasarinya (seperti fungsi MySQL CONCAT dan DATEDIFF dalam contoh ini).

Contoh blok SQL dengan sub-pilihan yang berkorelasi

Anda bisa menempatkan pernyataan SQL apa pun di blok SQL {i>field<i}, termasuk sub-pilihan yang berkorelasi. Contohnya ada di bawah ini:

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. Contohnya ada di bawah ini:

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 direferensikan sebagai jenis data tertentu dengan menggunakan titik dua (::) diikuti dengan jenis yang diinginkan. Misalnya, jika 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 di SQL yang dihasilkan Looker, bukan diubah sebagai string.

Jenis data yang dapat Anda gunakan dalam referensi bergantung pada jenis data kolom asli yang Anda rujuk. 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 akan menampilkan data dalam zona waktu kueri, sedangkan referensi yang menggunakan ::datetime akan 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

Sebagai contoh, misalnya Anda memiliki dimensi enrollment_month dan dimensi graduation_month, 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 dimensi enrollment_month dan graduation_month, Anda dapat menghitung berapa bulan atau tahun yang berlalu antara pendaftaran dan kelulusan siswa dengan membuat grup dimensi type: duration. Namun, karena beberapa kolom tanggal diubah sebagai 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 yang ditransmisikan sebagai string, salah satu opsi adalah membuat grup dimensi type: duration, dengan mereferensikan jangka waktu raw dari grup dimensi enrollment dan graduation di 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 menghasilkan grup dimensi yang disebut Durasi Terdaftar, dengan dimensi masing-masing 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 menggunakan 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 pada ukuran type: list, yang tidak dapat direferensikan pada Looker 6.8.

Konstanta LookML

Parameter constant memungkinkan Anda menentukan konstanta yang dapat digunakan di seluruh project LookML. Dengan konstanta LookML, Anda bisa mendefinisikan nilai sekali dan mereferensikannya di setiap bagian project Anda di mana string diterima, sehingga mengurangi pengulangan dalam kode LookML Anda.

Konstanta harus dideklarasikan dalam file manifes project, dan nilai untuk konstanta harus berupa string. Misalnya, Anda dapat menentukan konstanta city dengan nilai "Okayama" seperti 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 Eksplorasi users:


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

Looker kemudian menampilkan Okayama Users di menu Explore dan di judul Explore, bukan di menu Users default.

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