Cara Looker menghasilkan SQL

Jika Anda menggunakan Looker dari latar belakang SQL, Anda mungkin ingin tahu cara Looker menghasilkan SQL. Pada dasarnya, Looker adalah alat yang membuat 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 membuat kueri, Anda akan lebih memahami cara kode LookML diterjemahkan menjadi 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 cara Looker menghasilkan SQL, tetapi tidak membahas semua elemen LookML secara mendetail. Halaman dokumentasi referensi cepat LookML adalah tempat yang tepat untuk memulai mendapatkan informasi tentang parameter LookML.

Melihat kueri

Di Tampilan tersimpan atau di Jelajahi, Anda dapat menggunakan tab SQL di panel Data untuk melihat apa yang dikirim Looker ke database untuk mendapatkan data. Anda juga dapat menggunakan link Buka di SQL Runner dan Jelaskan di SQL Runner di bagian bawah tab SQL untuk melihat kueri di SQL Runner atau untuk melihat rencana penjelasan database untuk kueri.

Untuk mengetahui informasi selengkapnya tentang SQL Runner, lihat halaman dokumentasi dasar-dasar SQL Runner. Untuk 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, Jelajah, dan tampilan yang direferensikan 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 diwakili 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 Jelajahi untuk membuat kueri, yang mengisi klausa FROM SQL
  • Kolom: parameter dimension dan measure yang akan disertakan dalam kueri, yang mengisi klausa SELECT SQL
  • filter: Ekspresi filter Looker yang akan diterapkan ke nol atau beberapa kolom, yang mengisi klausa WHERE dan HAVING SQL
  • Urutan pengurutan: kolom yang akan diurutkan, dan urutan pengurutan, yang mengisi klausa ORDER BY SQL

Parameter ini adalah elemen yang ditentukan pengguna saat membuat kueri di halaman Jelajahi Looker. Elemen yang sama ini muncul di semua mode eksekusi kueri dengan Looker, seperti dalam 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 tabel virtual bergabung ke Jelajah. Saat membuat kueri SQL, Looker menyertakan klausa JOIN hanya jika diperlukan. Saat membuat kueri di Looker, pengguna tidak perlu menentukan cara tabel digabungkan, 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 dibuat sesuai dengan pola sebelumnya. Pertimbangkan toko e-commerce yang memiliki database dengan dua tabel, pesanan dan pengguna, 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 status (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 dijalankan 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 kesamaannya dengan formula kueri kanonis. SQL Looker menunjukkan beberapa karakteristik kode yang dibuat mesin (misalnya, COALESCE(users.state,'') AS "_g1"), tetapi selalu sesuai dengan formula.

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

Menjalankan SQL mentah di SQL Runner Looker

Looker menyertakan fitur bernama SQL Runner tempat Anda dapat menjalankan SQL yang diinginkan 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 mencoba kueri.

Kueri SQL mentah yang dijalankan di SQL Runner menghasilkan set 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 Bagikan dari menu roda gigi Jelajahi untuk membuka menu Bagikan URL.

URL yang diperluas memberikan informasi yang memadai 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
penggunaan e_thelook
jelajah events
kolom untuk membuat kueri dan menampilkan 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 dalam klausa FROM utama dan tampilan gabungan muncul dalam klausa LEFT JOIN. Gabungan Looker dapat ditulis dengan berbagai cara, yang dijelaskan secara lebih mendetail di halaman Bekerja dengan gabungan di LookML.

Blok SQL menentukan klausa SQL kustom

Tidak semua elemen kueri Looker dibuat oleh mesin. Pada titik tertentu, model data perlu memberikan detail spesifik agar Looker dapat mengakses tabel yang mendasarinya dan menghitung nilai turunan. Di LookML, blok SQL adalah cuplikan kode SQL yang disediakan oleh pembuat model data, yang digunakan Looker untuk menyintesis 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 yang mendasarinya atau untuk menjalankan fungsi agregat. Secara umum, semua parameter LookML yang dimulai dengan sql_ mengharapkan ekspresi SQL dalam bentuk tertentu. 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 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: ${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 pada dua dimensi terakhir dalam contoh ini, blok SQL dapat menggunakan fungsi yang didukung oleh database yang mendasarinya (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 menghasilkan 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 Jelajah

Parameter LookML sql_always_where dan sql_always_having memungkinkan Anda membatasi data yang tersedia untuk kueri dengan memasukkan blok SQL ke klausa WHERE atau HAVING SQL. 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});;
}