Cara Looker menghasilkan SQL

Jika Anda berasal dari latar belakang SQL, Anda mungkin ingin tahu cara Looker menghasilkan SQL. Pada dasarnya, Looker adalah alat yang membuat kueri SQL dan mengirimkannya terhadap 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 Anda 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-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

Dalam Look tersimpan atau 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 Buka di SQL Runner dan Jelaskan di SQL Runner di bagian bawah tab SQL untuk melihat kueri 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 format 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 mendasar ini yang diterapkan ke project LookML, seperti yang terlihat dalam contoh kueri sebelumnya.

Looker menggunakan parameter berikut untuk membuat kueri SQL lengkap:

• model: nama model LookML yang akan ditargetkan, yang menentukan target database
• explore: nama Eksplorasi yang akan dikueri, yang mengisi klausa SQL FROM
• Kolom: parameter dimension dan measure yang akan disertakan dalam kueri, yang mengisi klausa SQL SELECT
• filter: Ekspresi filter Looker yang akan diterapkan ke nol atau lebih 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 Jelajah Looker. Elemen yang sama ini muncul di semua mode eksekusi kueri dengan Looker, seperti di SQL yang dihasilkan, di URL yang merepresentasikan kueri, dan di Looker API.

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

Contoh kueri dan SQL yang dihasilkan

Mari buat kueri di Looker untuk menunjukkan cara kueri dibuat 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 temukan jumlah pesanan (ORDERS Count) yang dikelompokkan menurut negara bagian (USERS State) dan difilter menurut tanggal pembuatan pesanan (ORDERS Created Date) dalam Eksplorasi Looker.

Untuk melihat kueri SQL yang dibuat 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 kemiripannya dengan formula kueri kanonis. SQL Looker menunjukkan beberapa ciri kode yang dihasilkan mesin (misalnya, COALESCE(users.state,'') AS "_g1"), tetapi selalu sesuai dengan formulanya.

Bereksperimenlah dengan lebih banyak kueri di Looker untuk membuktikan kepada diri Anda sendiri 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 inginkan 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 dijalankan 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.

Catatan: Jika setelan koneksi Anda menentukan zona waktu database, Looker akan mengeluarkan SET TIMEZONE saat koneksi, yang dapat memengaruhi hasil.

Memeriksa komponen kueri di URL yang diperluas

Setelah menjalankan kueri di Looker, Anda dapat memeriksa URL yang diperluas untuk melihat komponen dasar kueri Looker. Mulailah dengan memilih Bagikan dari menu roda gigi Jelajah untuk membuka menu Bagikan URL.

Catatan: Jika Anda memulai dari Look, klik link Jelajahi dari Sini Look untuk membuka kueri di Eksplorasi.

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 yang akan 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 kueri contoh 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 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 pokok dan menghitung nilai turunan. Di LookML, blok SQL adalah cuplikan kode SQL yang disediakan oleh pemodel 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 pokok atau untuk melakukan 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 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: ${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 pokok (seperti fungsi MySQL CONCAT dan DATEDIFF dalam kasus 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 menurunkan 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 merujuk 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});;
}