Simple Log Service:Sintaksis dan fungsi SQL

Simple Log Service memungkinkan Anda menganalisis data dalam hasil pencarian menggunakan pernyataan SQL. Topik ini menjelaskan sintaksis dasar pernyataan analitik SQL.

Sintaksis dasar

Pernyataan pencarian|Pernyataan analitik

* | SELECT status, count(*) AS PV GROUP BY status

Fungsi SQL dan klausa SQL

Dalam kebanyakan kasus, fungsi SQL digunakan untuk menghitung, mengonversi, dan memformat data. Untuk informasi lebih lanjut, lihat Fungsi SQL. Misalnya, Anda dapat menggunakan fungsi SQL untuk menghitung jumlah dan rata-rata nilai, melakukan operasi pada string, serta memproses tanggal. Umumnya, fungsi SQL disematkan dalam klausa SQL.

Klausa SQL digunakan untuk membuat pernyataan pencarian SQL lengkap atau pernyataan pemrosesan data guna mengidentifikasi sumber, kondisi, kelompok, dan urutan data. Untuk informasi lebih lanjut, lihat Klausa SQL.

Contoh 1. Kueri log hari kemarin

Fungsi current_date mengembalikan tanggal saat ini. Fungsi date_add mengurangi interval tertentu dari tanggal saat ini. Hasilnya ditampilkan dalam bentuk tabel sehingga memungkinkan Anda melihat data secara intuitif. (Demo)

• Pernyataan kueri

  * |
  SELECT
    *
  FROM  log
  WHERE
    __time__ < to_unixtime(current_date)
    AND __time__ > to_unixtime(date_add('day', -1, current_date))

• Hasil

Contoh 2. Kueri distribusi alamat IP sumber untuk log

Kueri ini menggunakan fungsi ip_to_province untuk mendapatkan provinsi setiap alamat IP. Kemudian menggunakan group by untuk mengagregasi hasil berdasarkan alamat dan fungsi count untuk menghitung jumlah kemunculan setiap alamat. Hasilnya ditampilkan dalam grafik pie. (Coba Demo)

• Pernyataan kueri

  * |
  select  
    count(1) as c,
    ip_to_province(remote_addr) as address
  group by
    address
  limit
    100

Contoh 3. Kueri traffic inbound dan outbound NGINX

Kueri ini menggunakan fungsi date_trunc untuk menyelaraskan nilai __time__ ke satuan jam. Bidang __time__ merupakan bidang sistem yang merepresentasikan waktu pemasukan data log, yang secara default berupa Unix timestamp. Kueri kemudian menggunakan fungsi date_format untuk memformat timestamp yang telah diselaraskan, klausa group by untuk mengelompokkan hasil berdasarkan waktu yang telah diformat, serta fungsi sum untuk menghitung total traffic per jam. Hasilnya ditampilkan sebagai grafik garis dengan time pada sumbu-X, dan net_out serta net_in pada sumbu-Y kiri. (Coba Demo)

• Pernyataan kueri

  * |
  select
    sum(body_bytes_sent) as net_out,
    sum(request_length) as net_in,
    date_format(date_trunc('hour', __time__), '%m-%d %H:%i') as time
  group by
    date_format(date_trunc('hour', __time__), '%m-%d %H:%i')
  order by
    time
  limit
    10000

• Hasil

  

Contoh 4. Kueri 10 URL paling sering diakses di NGINX

Gunakan fungsi split_part untuk memisahkan nilai request_uri dengan menggunakan ? sebagai pembatas. Elemen pertama dari array yang dihasilkan adalah path permintaan. Agregasikan data berdasarkan path ini menggunakan GROUP BY. Gunakan fungsi count untuk menghitung jumlah kunjungan untuk setiap path dan ORDER BY untuk mengurutkan jumlah tersebut. Kata kunci DESC menentukan bahwa hasil diurutkan secara menurun dari yang terbesar ke terkecil. Hasilnya ditampilkan dalam grafik kolom. (Coba Demo)

• Pernyataan kueri

  * |
  select
    count(1) as pv,
    split_part(request_uri, '?', 1) as path
  group by
    path
  order by
    pv desc
  limit
    10

• Hasil

  

Contoh 5. Kueri tren kategori dan PV metode permintaan

Kueri ini menggunakan fungsi date_trunc untuk menyelaraskan timestamp ke satuan menit. Kemudian mengelompokkan hasil berdasarkan waktu yang telah diselaraskan dan request_method untuk menghitung PV, lalu mengurutkan hasil berdasarkan waktu. Data ditampilkan sebagai grafik area bertumpuk, dengan waktu pada sumbu-X, PV pada sumbu-Y, dan request_method sebagai kolom seri. (Coba Demo)

• Pernyataan kueri

  * |
  select
    date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as t,
    request_method,
    count(*) as pv
  group by
    t,
    request_method
  order by
    t asc
  limit
    10000

• Hasil

  

Contoh 6. Bandingkan PV hari ini dan kemarin

Fungsi count menghitung jumlah PV untuk hari ini. Fungsi compare mengembalikan perbandingan day-over-day dalam PV antara hari ini dan hari sebelumnya. (Demo)

• Pernyataan kueri

  * |
  select
    diff [1] as today,
    round((diff [3] -1.0) * 100, 2) as growth
  FROM
    (
      SELECT
        compare(pv, 86400) as diff
      FROM
        (
          SELECT
            COUNT(1) as pv
          FROM
            log
        )
    )

• Hasil

Contoh 7. Prediksi jumlah PV berdasarkan log akses NGINX

Ekspresi time - time % 60 menyelaraskan timestamp ke satuan menit, menghasilkan nilai stamp. Kueri kemudian mengelompokkan hasil berdasarkan stamp menggunakan klausa group by dan menggunakan fungsi count untuk menghitung jumlah event per menit. Hasil ini digunakan sebagai subkueri untuk fungsi ts_predicate_simple guna memprediksi enam titik data berikutnya. Hasil kueri ditampilkan sebagai grafik deret waktu. (Coba Demo)

• Pernyataan kueri

  * |
  select
    ts_predicate_simple(stamp, value, 6)
  from
    (
      select
        __time__ - __time__ % 60 as stamp,
        COUNT(1) as value
      from
        log
      GROUP BY
        stamp
      order by
        stamp
    )
  LIMIT
    1000

• Hasil

  

Contoh 8. Kumpulkan data berdasarkan bidang http_user_agent, dan urutkan serta tampilkan data berdasarkan jumlah PV

Kueri ini mengagregasi data berdasarkan http_user_agent, mengkueri permintaan dari setiap agen dan jumlah traffic respons. Karena satuannya adalah byte, kueri menggunakan fungsi round untuk mengonversi nilai ke MB dan membulatkannya ke dua tempat desimal. Ekspresi case when kemudian mengklasifikasikan kode status ke dalam kategori seperti 2xx, 3xx, 4xx, dan 5xx, serta menghitung persentase masing-masing. Hasilnya ditampilkan dalam bentuk tabel untuk gambaran yang jelas. (Coba Demo)

• Pernyataan kueri

  * |
  select
    http_user_agent as "User agent",
    count(*) as pv,
    round(sum(request_length) / 1024.0 / 1024, 2) as "Traffic permintaan (MB)",
    round(sum(body_bytes_sent) / 1024.0 / 1024, 2) as "Traffic respons (MB)",
    round(
      sum(
        case
          when status >= 200
          and status < 300 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Persentase kode status 2xx (%)",
    round(
      sum(
        case
          when status >= 300
          and status < 400 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Persentase kode status 3xx (%)",
    round(
      sum(
        case
          when status >= 400
          and status < 500 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Persentase kode status 4xx (%)",
    round(
      sum(
        case
          when status >= 500
          and status < 600 then 1
          else 0
        end
      ) * 100.0 / count(1),
      6
    ) as "Persentase kode status 5xx (%)"
  group by
    "User agent"
  order by
    pv desc
  limit
    100

Contoh 9. Kueri persentase jumlah permintaan error dalam log NGINX

Jumlah permintaan error yang memiliki kode status melebihi 400 dan jumlah total permintaan diperoleh berdasarkan pernyataan SQL. Kemudian, persentase jumlah permintaan error dihitung. Hasilnya ditampilkan dalam grafik statistik. (Demo)

• Pernyataan kueri

  * |
  select
    round((errorCount * 100.0 / totalCount), 2) as errorRatio
  from
    (
      select
        sum(
          case
            when status >= 400 then 1
            else 0
          end
        ) as errorCount,
        count(1) as totalCount
      from
        log
    )

• Hasil