Menggabungkan SQL dan merujuk ke objek LookML

Untuk menulis LookML yang efektif, Anda harus dapat mereferensikan dimensi, ukuran, tabel virtual, 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 substitusi ($)

Operator penggantian, $, membuat kode LookML lebih dapat digunakan kembali dan modular, sehingga Anda dapat mereferensikan tampilan dan tabel turunan lain, kolom dalam tabel SQL, atau dimensi dan ukuran LookML. Hal ini baik karena dua alasan. Pertama, Anda mungkin telah menghitung dimensi atau ukuran yang sangat rumit, dan Anda tidak perlu menuliskan kembali semua kerumitannya. Kedua, jika Anda mengubah sesuatu tentang dimensi atau ukuran, perubahan tersebut dapat diterapkan ke semua hal lain yang mengandalkannya.

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} merujuk 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 dengan datagroups.

Pencakupan 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 set tertentu.
  • Nama pendek berbentuk <field-name | set-name>, tanpa titik pemisah. Looker memperluas nama pendek menjadi nama lengkap dengan menggunakan cakupan tempat nama tersebut digunakan.

Berikut adalah contoh yang menunjukkan berbagai bentuk nama dan cakupan. Ini adalah grup kolom yang tidak realistis, tetapi ditampilkan untuk mendemonstrasikan 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 melampirkan (orders). Hal ini dapat berguna saat Anda perlu membandingkan kolom antara dua tampilan yang berbeda.

Saat 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. Tabel B harus digabungkan ke tabel A dalam satu atau beberapa Eksplorasi. Lihat halaman Bekerja dengan gabungan di LookML kami untuk mempelajari gabungan.

Dialek SQL

Looker mendukung banyak jenis database, seperti MySQL, Postgres, Redshift, dan BigQuery. Setiap database mendukung serangkaian 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 memilih satu dialek daripada dialek lainnya. 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, jadi Anda harus menggunakan dialek SQL yang cocok dengan database Anda. Misalnya, jika Anda menggunakan fungsi SQL, fungsi tersebut harus merupakan fungsi yang 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 berupa satu nama kolom atau serumit subpilihan yang berkorelasi. Kontennya bisa sangat rumit, mengakomodasi hampir semua kebutuhan Anda 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 berbeda dari SQL. Namun, setelah penggantian terjadi, string yang dihasilkan adalah SQL murni, yang disisipkan 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 pokok (seperti fungsi MySQL CONCAT dan DATEDIFF dalam contoh ini).

Contoh blok SQL dengan subpilihan yang berkorelasi

Anda dapat menempatkan pernyataan SQL apa pun di blok SQL kolom, termasuk subpilihan 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 menurunkan 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 direferensikan sebagai jenis data tertentu dengan menggunakan titik ganda (::) yang diikuti dengan jenis yang dipilih. 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 ditransmisikan sebagai 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.
  • Di kolom referensi ke lokasi, Anda dapat menggunakan ::latitude dan ::longitude.

Menggunakan referensi jenis kolom LookML dengan kolom tanggal

Sebagai contoh, misalkan 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 berapa bulan atau tahun yang berlalu antara pendaftaran dan kelulusan siswa dengan membuat grup dimensi type: duration. Namun, karena beberapa kolom tanggal dikonversi 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 ini yang dikonversi 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 dimensi individual Bulan Terdaftar dan Tahun Terdaftar.

Alternatif yang lebih sederhana untuk menggunakan jangka waktu raw dalam grup dimensi type: duration adalah dengan menentukan jenis rujukan ::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 mentransformasikannya sebagai string dengan SQL.

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

Sintaksis ini tidak tersedia dengan ukuran type: list, yang tidak dapat dirujuk 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 Anda yang menerima string, 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" sebagai berikut:

constant: city {
  value: "Okayama"
}

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


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

Kemudian, Looker akan menampilkan Pengguna Okayama di menu Eksplorasi dan di judul Eksplorasi, bukan Pengguna default.

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