Cara Looker menghasilkan SQL

Jika Anda menggunakan Looker dengan latar belakang SQL, Anda mungkin penasaran tentang cara Looker menghasilkan SQL. Pada dasarnya, Looker adalah alat yang menghasilkan kueri SQL dan mengirimkannya ke koneksi database. Looker merumuskan kueri SQL berdasarkan project LookML yang menjelaskan hubungan antara tabel dan kolom dalam database. Dengan memahami cara Looker menghasilkan kueri, Anda akan lebih memahami cara kode LookML diterjemahkan ke dalam kueri SQL yang efisien.

Setiap parameter LookML mengontrol beberapa aspek cara Looker menghasilkan SQL, dengan mengubah struktur, konten, atau perilaku kueri. Halaman ini menjelaskan prinsip-prinsip cara Looker menghasilkan SQL, tetapi tidak membahas semua elemen LookML secara mendetail. Halaman dokumentasi referensi cepat LookML adalah tempat yang tepat untuk memulai informasi tentang parameter LookML.

Melihat kueri

Di Look tersimpan atau di Eksplorasi, Anda dapat menggunakan tab SQL di panel Data untuk melihat apa yang dikirim Looker ke database untuk mendapatkan data. Anda juga dapat menggunakan link Open in SQL Runner dan Explain in SQL Runner di bagian bawah tab SQL untuk melihat kueri Anda di SQL Runner atau melihat rencana penjelasan database untuk kueri.

Untuk mengetahui informasi selengkapnya tentang SQL Runner, lihat halaman dokumentasi dasar-dasar SQL Runner. Untuk mengetahui informasi selengkapnya tentang cara mengoptimalkan kueri menggunakan SQL Runner, lihat postingan Komunitas Cara mengoptimalkan SQL dengan EXPLAIN.

Bentuk kanonis kueri Looker

Kueri SQL Looker selalu menggunakan bentuk berikut.

SELECT
   <dimension>, <dimension>, ...
   <measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>

Project LookML menentukan semua dimensi, ukuran, Eksplorasi, dan tampilan yang dirujuk dalam kueri SQL. Ekspresi filter ditentukan di Looker oleh pengguna untuk membentuk kueri ad hoc. Ekspresi filter juga dapat dideklarasikan langsung di LookML untuk diterapkan ke semua kueri.

Komponen dasar kueri Looker

Semua kueri Looker direpresentasikan oleh parameter dasar ini yang diterapkan ke project LookML, seperti yang terlihat dalam contoh kueri sebelumnya.

Looker menggunakan parameter berikut untuk menghasilkan kueri SQL lengkap:

  • model: nama model LookML yang akan ditargetkan, yang menentukan database target
  • explore: nama Eksplorasi yang akan dikueri, yang mengisi klausa SQL FROM
  • Fields: parameter dimension dan measure yang akan disertakan dalam kueri, yang mengisi klausa SQL SELECT
  • filter: ekspresi filter Looker yang akan diterapkan ke nol atau beberapa kolom, yang mengisi klausa SQL WHERE dan HAVING
  • Urutan pengurutan: kolom yang akan diurutkan, dan urutan pengurutan, yang mengisi klausa SQL ORDER BY

Parameter ini adalah elemen yang ditentukan pengguna saat membuat kueri di halaman Explore Looker. Elemen yang sama ini muncul di semua mode eksekusi kueri dengan Looker, seperti di SQL yang dihasilkan, di URL yang mewakili kueri, dan di Looker API.

Bagaimana dengan tampilan yang ditentukan oleh klausa LEFT JOIN? Klausa JOIN diisi berdasarkan struktur model LookML, yang menentukan cara tampilan bergabung ke Eksplorasi. Saat membuat kueri SQL, Looker hanya menyertakan klausa JOIN jika diperlukan. Saat membuat kueri di Looker, pengguna tidak perlu menentukan cara tabel bergabung, karena informasi ini dienkode dalam model -- salah satu manfaat Looker yang paling efektif bagi pengguna bisnis.

Contoh kueri dan SQL yang dihasilkan

Mari kita buat kueri di Looker untuk menunjukkan cara kueri dihasilkan sesuai dengan pola sebelumnya. Pertimbangkan toko e-commerce yang memiliki database dengan dua tabel, orders dan users, untuk melacak pengguna dan pesanan.

orders
id INT
created_at DATETIME
users_id INT
status VARCHAR(255)
traffic_source VARCHAR(15)
users
id INT
email VARCHAR(255)
first_name VARCHAR(255)
last_name VARCHAR(255)
created_at DATETIME
zip INT
country VARCHAR(255)
state VARCHAR(255)
city VARCHAR(255)
age INT
traffic_source VARCHAR(15)

Mari kita temukan jumlah pesanan (ORDERS Count) yang dikelompokkan menurut negara bagian (USERS State) dan difilter menurut tanggal pembuatan pesanan (ORDERS Created Date) di Eksplorasi Looker.

Tabel data Jelajah menampilkan jumlah pesanan yang dikelompokkan menurut status pengguna untuk pesanan yang dilakukan dalam 30 hari terakhir.

Untuk melihat kueri SQL yang dihasilkan dan dieksekusi oleh Looker, klik tab SQL di panel Data.

SELECT COALESCE(users.state, ' ') AS "_g1",
   users.state AS 'users.state',
   COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id

WHERE
  orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500

Perhatikan kemiripannya dengan rumus kueri kanonis. SQL Looker menunjukkan beberapa ciri kode yang dihasilkan mesin (misalnya, COALESCE(users.state,'') AS "_g1"), tetapi selalu sesuai dengan rumus.

Bereksperimenlah dengan lebih banyak kueri di Looker untuk membuktikan bahwa struktur kueri selalu sama.

Menjalankan SQL mentah di SQL Runner Looker

Looker menyertakan fitur yang disebut SQL Runner tempat Anda dapat menjalankan SQL apa pun yang Anda suka terhadap koneksi database yang telah Anda siapkan di Looker.

Karena setiap kueri yang dihasilkan oleh Looker menghasilkan perintah SQL yang lengkap dan berfungsi, Anda dapat menggunakan SQL Runner untuk menyelidiki atau bereksperimen dengan kueri.

Kueri SQL mentah yang dieksekusi di SQL Runner menghasilkan kumpulan hasil yang sama. Jika SQL berisi error, SQL Runner akan menandai lokasi error pertama dalam perintah SQL dan akan menyertakan posisi error dalam pesan error.

Memeriksa komponen kueri di URL yang diperluas

Setelah menjalankan kueri di Looker, Anda dapat memeriksa URL yang diperluas untuk melihat komponen dasar kueri Looker. Mulai dengan memilih Share dari menu roda gigi Eksplorasi untuk membuka menu Share URLs.

URL yang diperluas memberikan informasi yang cukup untuk membuat ulang kueri. Misalnya, contoh URL yang diperluas ini memberikan informasi berikut:

https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500
model e_thelook
jelajah events
kolom untuk dikueri dan ditampilkan fields=users.state,users.count
kolom dan urutan pengurutan sorts=users.count+desc
kolom dan nilai filter f[users.created_year]=2020

Cara Looker menyusun JOIN

Dalam contoh kueri sebelumnya, perhatikan bahwa Eksplorasi orders muncul di klausa FROM utama dan tampilan yang digabungkan muncul di klausa LEFT JOIN. Gabungan Looker dapat ditulis dengan berbagai cara, yang dijelaskan lebih mendetail di halaman Bekerja dengan gabungan di LookML.

Blok SQL menentukan klausa SQL kustom

Tidak semua elemen kueri Looker dihasilkan oleh mesin. Pada suatu titik, model data harus memberikan detail spesifik agar Looker dapat mengakses tabel dasar dan menghitung nilai turunan. Di LookML, blok SQL adalah cuplikan kode SQL yang disediakan oleh pembuat model data, yang digunakan Looker untuk mensintesis ekspresi SQL lengkap.

Parameter blok SQL yang paling umum adalah sql, yang digunakan dalam definisi dimensi dan ukuran. Parameter sql menentukan klausa SQL untuk mereferensikan kolom dasar atau untuk menjalankan fungsi agregat. Secara umum, semua parameter LookML yang dimulai dengan sql_ mengharapkan ekspresi SQL dalam beberapa bentuk. Misalnya: sql_always_where, sql_on, dan sql_table_name. Lihat Referensi LookML untuk mengetahui informasi selengkapnya tentang setiap parameter.

Contoh blok SQL untuk dimensi dan ukuran

Contoh kode berikut memberikan beberapa contoh blok SQL untuk dimensi dan ukuran. Operator penggantian LookML ($) membuat deklarasi sql ini tampak berbeda dengan 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: ${cost} ;;      # Specify the field that you want to average
                       # The field 'cost' is declared elsewhere
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: number
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

Seperti yang ditunjukkan dalam dua dimensi terakhir dalam contoh ini, blok SQL dapat menggunakan fungsi yang didukung oleh database dasar (seperti fungsi MySQL CONCAT dan DATEDIFF dalam hal ini). Kode yang Anda gunakan dalam blok SQL harus cocok dengan dialek SQL yang digunakan oleh database.

Contoh blok SQL untuk tabel turunan

Tabel turunan juga menggunakan blok SQL untuk menentukan kueri yang berasal dari tabel. Berikut adalah contoh tabel turunan berbasis SQL:

view: user_order_facts {
  derived_table: {
    sql:
      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
  }
}

Contoh blok SQL untuk memfilter Eksplorasi

Parameter LookML sql_always_where dan sql_always_having memungkinkan Anda membatasi data yang tersedia untuk kueri dengan menyisipkan blok SQL ke klausa SQL WHERE atau HAVING. Dalam contoh ini, operator penggantian LookML ${view_name.SQL_TABLE_NAME} digunakan untuk mereferensikan tabel turunan:

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});;
}