Konsep SQL untuk tampilan

Setiap file tampilan dalam project LookML Anda menentukan satu tampilan dalam Looker, yang menentukan tabel untuk dikueri dan kolom mana — dimensi dan ukuran — dari tabel tersebut yang akan ditampilkan di UI Looker. Tampilan sesuai dengan satu tabel di database Anda atau satu tabel turunan.

Dalam panduan ini, Anda akan mempelajari topik berikut:

Untuk mempelajari lebih lanjut cara menggunakan SQL untuk menentukan dan menyesuaikan tabel turunan di LookML menggunakan SQL, lihat Konsep SQL untuk tabel turunan.

Tampilan

Berikut adalah contoh file tampilan yang disebut users.view, yang menyertakan definisi untuk tabel database yang akan dikueri beserta beberapa dimensi dan ukuran:

view: users {
  sql_table_name: thelook.users ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
   measure: average_age {
    type: average
    sql: ${age} ;;  }

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

  measure: count {
    type: count
  }
}

Elemen pertama definisi tampilan adalah parameter sql_table_name, yang menentukan tabel di database Anda yang akan dikueri oleh tampilan. Nilai ini adalah satu-satunya tempat di seluruh model tempat nama tabel ditentukan, karena semua referensi lain ke tampilan akan menggunakan alias tabel ${TABLE}. Jika Anda ingin mengubah nama tabel database, nama tersebut hanya perlu diubah di parameter sql_table_name. Ada beberapa hal yang perlu dipertimbangkan saat Anda mereferensikan tabel database.

Looker menggunakan nilai sql_table_name untuk menulis klausa FROM SQL-nya, diikuti dengan nama tampilan, yang menjadi alias tabel. SQL yang setara akan terlihat seperti ini:

FROM `thelook`.`users` AS `users`

Looker menggunakan dimensi dan ukuran yang ditentukan tampilan untuk membuat klausa SELECT SQL-nya. Setiap dimensi menentukan jenis dimensi — seperti string, angka, atau Boolean — dan parameter LookML sql yang mereferensikan dimensi dalam tampilan, menggunakan alias tabel. Untuk dimensi yang disebut age, lihat contoh berikut:

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

Saat membuat SQL untuk dikirim ke database, Looker akan mengganti alias untuk tampilan ke dalam ${TABLE}. Untuk dimensi age dari contoh sebelumnya, Looker akan menghasilkan klausa SELECT seperti berikut:

SELECT `users`.`age` AS `users.age`

Pengukuran sering kali merupakan agregasi yang dilakukan pada dimensi. Anda menentukan alias dimensi dalam ekspresi sql ukuran. Misalnya, ukuran yang menghitung rata-rata dimensi age dapat memiliki ekspresi sql dengan alias ${age} di dalamnya, seperti dalam contoh berikut:

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

  measure: average_age {
    type: average
    sql: ${age} ;;
  }

Jika Anda mengganti nama dimensi age, alias barunya akan diterapkan ke semua referensi alias dimensinya.

Menyesuaikan file tampilan

Anda dapat menyesuaikan ekspresi SQL file tampilan atau menggunakan logika LookML bawaan Looker untuk meniru logika ekspresi SQL.

Menggunakan ekspresi SQL

Misalnya, Anda ingin membagi data usia menjadi empat kelompok, dengan pengguna yang berusia di bawah 18 tahun didefinisikan sebagai "Remaja", pengguna berusia 18-35 tahun sebagai "Dewasa Muda", pengguna berusia 36-65 tahun sebagai "Dewasa Tua", dan pengguna berusia 65 tahun ke atas sebagai "Lansia". Untuk melakukan pembagian ini, Anda harus menentukan dimensi baru — misalnya, dimension: age_cohort — dengan ekspresi sql yang menangkap kohor ini. Definisi dimensi LookML berikut menggunakan pernyataan CASE yang sesuai untuk koneksi database MySQL:

dimension: age_cohort {
  type: string
  sql:
    CASE
      WHEN ${age} < 18 THEN 'Youth'
      WHEN ${age} < 35 THEN 'Young Adult'
      WHEN ${age} < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END ;;
}

Setelah menentukan kelompok usia sebagai dimensi, Anda dapat menggunakan kembali logika CASE dengan menyertakan dimensi kelompok usia dalam kueri Jelajahi.

Saat membuat kueri Jelajah dengan dimensi kelompok usia, Anda dapat menggunakan tab SQL di Jelajah untuk melihat SQL yang dihasilkan Looker. Dengan dimensi kelompok usia, SQL akan terlihat seperti ini:

SELECT
CASE
  WHEN users.age < 18 THEN 'Youth'
  WHEN users.age < 35 THEN 'Young Adult'
  WHEN users.age < 65 THEN 'Older Adult'
  ELSE 'Senior'
END  AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 500

Menggunakan logika kasus bawaan Looker

Anda dapat mencapai efek yang sama seperti pernyataan CASE SQL dengan ekspresi yang tidak bergantung pada database. Parameter case LookML memungkinkan Anda menentukan bucket kelompok yang terdiri dari pernyataan when yang menggunakan ekspresi sql untuk menangkap kondisi dan string tertentu untuk memberi label pada hasil.

Berikut adalah contoh dimensi age_cohort baru yang sama yang ditulis dengan parameter LookML case:

  dimension: age_cohort {
    case: {
      when: {
        sql: ${age} < 18 ;;
        label: "Youth"
      }
      when: {
        sql: ${age} < 35 ;;
        label: "Young Adult"
      }
      when: {
        sql: ${age} < 65 ;;
        label: "Middle-aged Adult"
      }
      else: "Older Adult"
    }
  }

Saat runtime, Looker membuat sintaksis SQL CASE yang benar untuk database Anda. Selain itu, Looker membuat ekspresi lain untuk menangani pengurutan grup, sehingga label yang dihasilkan tidak hanya diurutkan secara alfanumerik (kecuali jika Anda menentukan urutan pengurutan sebagai alfanumerik). Looker membuat kueri SQL yang dihasilkan yang menyerupai berikut:

SELECT
CASE
  WHEN users.age < 18  THEN '0'
  WHEN users.age < 35  THEN '1'
  WHEN users.age < 65  THEN '2'
  ELSE '3'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age < 18  THEN 'Youth'
  WHEN users.age < 35  THEN 'Young Adult'
  WHEN users.age < 65  THEN 'Older Adult'
  ELSE 'Senior'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500

Menggunakan logika bin atau tingkat bawaan Looker

Metode lain untuk menentukan cara pengelompokan nilai numerik menggunakan jenis parameter bin atau tier bawaan Looker. type:bin digunakan bersama dengan parameter bins — demikian pula, type: tier digunakan bersama dengan parameter tiers — untuk memisahkan dimensi numerik menjadi serangkaian rentang angka. Namun, Anda tidak dapat menentukan label untuk setiap bin.

Contoh LookML berikut menggunakan parameter bins dalam dimensi untuk menentukan nilai minimum di setiap set:

  dimension: age_cohort {
    type: bin
    bins: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

Anda dapat menggunakan parameter tiers dalam dimensi dengan cara yang sama persis. Contoh:

  dimension: age_cohort {
    type: tier
    tiers: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

Looker kemudian akan menghasilkan sesuatu seperti pernyataan SQL berikut:

SELECT
CASE
  WHEN users.age  < 18 THEN '0'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
  WHEN users.age  >= 65 THEN '3'
  ELSE '4'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age  < 18 THEN 'Below 18'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
  WHEN users.age  >= 65 THEN '65 or Above'
  ELSE 'Undefined'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500