Cara Looker menghasilkan SQL

Jika menggunakan Looker dari latar belakang SQL, Anda mungkin ingin tahu 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 bagaimana Looker menghasilkan kueri, Anda akan lebih memahami bagaimana kode LookML diterjemahkan ke kueri SQL yang efisien.

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

Melihat kueri

Di Tampilan tersimpan atau di Jelajah, 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 di SQL Runner atau melihat rencana penjelasan database untuk kueri tersebut.

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 MENJELASKAN.

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 mendefinisikan semua dimensi, ukuran, Eksplorasi, dan tampilan yang dirujuk dalam formula di atas. Ekspresi filter ditentukan di Looker oleh pengguna untuk membentuk kueri ad hoc. Ekspresi filter juga dapat dideklarasikan langsung dalam LookML untuk diterapkan ke semua kueri.

Komponen dasar kueri Looker

Semua kueri Looker diwakili oleh parameter dasar ini yang diterapkan pada project LookML, seperti yang terlihat pada formula di atas.

Looker menggunakan parameter berikut untuk membuat kueri SQL yang lengkap:

  • model: nama model LookML yang akan ditargetkan, yang menentukan database target
  • explore: nama Explore untuk dikueri, yang mengisi klausa FROM SQL
  • Kolom: parameter dimension dan measure yang akan disertakan dalam kueri, yang mengisi klausa SELECT SQL
  • filter: Ekspresi filter Looker untuk diterapkan ke nol atau beberapa kolom, yang mengisi klausa WHERE dan HAVING SQL
  • Sort order: kolom yang akan diurutkan, dan tata urutan, yang mengisi klausa ORDER BY SQL

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

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 menyertakan klausa JOIN hanya jika diperlukan. Saat membuat kueri di Looker, mereka tidak perlu menentukan cara tabel digabungkan, karena informasi ini dienkode dalam model-- salah satu manfaat Looker yang paling penting bagi pengguna bisnis.

Contoh kueri dan hasil SQL

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 cari jumlah pesanan (Jumlah PESANAN) yang dikelompokkan berdasarkan status (Status PENGGUNA) dan difilter menurut tanggal pembuatan pesanan (Tanggal Dibuat PESANAN) dalam Jelajah Looker.

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

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

Lakukan eksperimen dengan lebih banyak kueri di Looker untuk membuktikan kepada diri Anda bahwa struktur kueri selalu sama.

Menjalankan SQL mentah di SQL Runner Looker

Looker menyertakan fitur bernama SQL Runner tempat Anda dapat menjalankan SQL apa pun yang diinginkan pada koneksi database yang telah disiapkan di Looker.

Karena setiap kueri yang dibuat oleh Looker menghasilkan perintah SQL yang lengkap dan fungsional, Anda dapat menggunakan SQL Runner untuk menyelidiki atau mencoba menggunakan kueri tersebut.

Kueri SQL mentah yang dijalankan di SQL Runner memberikan kumpulan hasil yang sama. Jika SQL berisi kesalahan, SQL Runner akan menyoroti lokasi kesalahan pertama dalam perintah SQL dan akan menyertakan posisi kesalahan tersebut dalam pesan kesalahan.

Memeriksa komponen kueri dalam URL panjang

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

URL panjang memberikan informasi yang memadai untuk membuat ulang kueri. Misalnya, contoh URL panjang 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
eksplorasi events
kolom untuk dikueri dan ditampilkan fields=users.state,users.count
kolom pengurutan dan urutan sorts=users.count+desc
filter kolom dan nilai f[users.created_year]=2020

Cara Looker menyusun JOIN

Dalam kueri SQL di atas, perhatikan bahwa Explore orders muncul di klausa FROM utama dan tampilan gabungan muncul di klausa LEFT JOIN. Penggabungan Looker dapat ditulis dengan berbagai cara, yang dijelaskan secara mendetail di halaman Menggunakan gabungan di LookML.

Blok SQL menentukan klausa SQL kustom

Tidak semua elemen kueri Looker dihasilkan 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 pengukuran. 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

Di bawah ini adalah beberapa contoh blok SQL untuk dimensi dan ukuran. Operator substitusi LookML ($) membuat deklarasi sql ini tampak menipu tidak seperti SQL. Namun, setelah substitusi 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 dalam dua dimensi terakhir di atas, blok SQL dapat menggunakan fungsi yang didukung oleh database yang mendasarinya (seperti fungsi MySQL CONCAT dan DATEDIFF dalam kasus ini). Kode yang Anda gunakan dalam blok SQL harus sesuai dengan dialek SQL yang digunakan {i>database<i}.

Contoh blok SQL untuk tabel turunan

Tabel turunan juga menggunakan blok SQL untuk menentukan kueri yang menghasilkan tabel. Contohnya ada di bawah ini:

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 Explore

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