Menggunakan date_start dan date_end dengan filter tanggal

Anda dapat menggunakan filter template untuk merujuk ke tanggal dengan memilih tanggal mulai dan akhir dalam filter tanggal — {% date_start date_filter %} dan {% date_end date_filter %}. Halaman ini akan memandu Anda melalui beberapa contoh kasus penggunaan dan langkah-langkah untuk menyelesaikannya.

Catatan sintaksis

Sintaksis berikut berfungsi dengan sebagian besar dialek, tetapi dialek tertentu memiliki kasus penggunaan tertentu. Contoh:

  • BigQuery memungkinkan kontrol yang lebih mendetail saat menggunakan fungsi karakter pengganti tabel seperti TABLE_DATE_RANGE dan TABLE_QUERY, sehingga penggunaan {% table_date_range prefix date_filter %} tidak cukup untuk menentukan filter tanggal.
  • Hadoop memungkinkan Anda bekerja dengan kolom yang dipartisi menurut tanggal, apa pun jenis (string, date) atau format (YYYY-MM-DD) kolom tersebut.

Catatan penggunaan

  • Jika tidak ada nilai yang ditentukan untuk date_filter, {% date_start date_filter %} dan {% date_end date_filter %} akan bernilai NULL.
  • Dalam kasus date_filter uraian (seperti before 2016-01-01 atau after 2016-01-01), salah satu dari filter {% date_start date_filter %} atau {% date_end date_filter %} adalah NULL.

    • Untuk memastikan tak satu pun dari dua kasus ini menghasilkan SQL yang tidak valid, Anda dapat menggunakan IFNULL atau COALESCE di LookML.

Contoh kasus penggunaan

Kolom yang dipartisi bulanan (di BigQuery)

Di beberapa set data BigQuery, tabel disusun berdasarkan bulan, dan ID tabel memiliki kombinasi tahun/bulan sebagai akhiran. Contohnya ada di set data Wikipedia [fh-bigquery:wikipedia] BigQuery publik, yang memiliki tabel bernama pagecounts_201601, pagecounts_201602, pagecounts_201603, dan sebagainya.

Contoh 1: LookML yang bergantung pada always_filter

Tabel turunan di bawah menggunakan TABLE_QUERY([dataset], [expr]) untuk mendapatkan kumpulan tabel yang tepat untuk dikueri: 

view: pagecounts {
  derived_table: {
    sql: SELECT * FROM
    TABLE_QUERY([fh-bigquery:wikipedia],
    "length(table_id) = 17 AND
    table_id >= CONCAT( 'pagecounts_' , STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') ) AND
    table_id <= CONCAT('pagecounts_' , STRFTIME_UTC_USEC({% date_end date_filter %},'%Y%m') )";
    )
    ;;
  }
  filter: date_filter {
    type: date
  }
}

Beberapa catatan tentang kode dalam ekspresi:

  • table_id mengacu pada nama tabel dalam set data.
  • length(table_id) = 17 memastikannya mengabaikan tabel lain dengan nama seperti pagecounts_201407_en_top64k.
  • STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') hanya akan menampilkan bagian YYYYmm dari tanggal mulai.

NULL akan diganti dengan bagian date_filter. Untuk menggunakan fitur ini, Anda memerlukan always_filter di Explore: 

explore: pagecounts {
  always_filter: {
    filters: [date_filter: "2 months ago"]
  }
}

Perhatikan bahwa tindakan ini akan tetap gagal untuk filter tanggal dari sebelum tanggal paling awal dalam set data karena {% date_start date_filter %} akan dievaluasi ke NULL.

Contoh 2: LookML yang tidak bergantung pada always_filter

Anda juga dapat menggunakan COALESCE atau IFNULL untuk mengenkode kumpulan tabel default untuk membuat kueri. Dalam contoh di bawah ini, data dua bulan terakhir digunakan:

  • Batas bawah: COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
  • Batas atas: COALESCE({% date_end date_filter %},CURRENT_TIMESTAMP()) 
view: pagecounts {
  derived_table: {
    sql: SELECT * FROM
    TABLE_QUERY([fh-bigquery:wikipedia],
    "length(table_id) = 17 AND
    table_id >= CONCAT( 'pagecounts_'; , STRFTIME_UTC_USEC(COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH')),'%Y%m') ) AND
    table_id <= CONCAT( 'pagecounts_' , STRFTIME_UTC_USEC(COALESCE({% date_end date_filter %},CURRENT_TIMESTAMP()),'%Y%m') )"
    )
    ;;
  }
  filter: date_filter {
    type: date
  }
}

File log menggunakan UTC saat membuat kueri di zona waktu Amerika (di BigQuery)

Terkadang file log Looker Anda disimpan dalam UTC, meskipun Anda membuat kueri dalam zona waktu Timur atau Pasifik. Masalah ini dapat menyebabkan masalah saat file log sudah diluncurkan ke tanggal besok dalam zona waktu lokal kueri, sehingga menyebabkan beberapa data terlewat.

Solusinya adalah menambahkan hari tambahan ke tanggal akhir filter tanggal, untuk memastikan bahwa jika sudah melewati tengah malam UTC, entri log tersebut diambil.

Contoh di bawah ini menggunakan set data [githubarchive:day] publik, yang memiliki partisi harian dari informasi GitHub.

Contoh 1: LookML yang bergantung pada always_filter

view: githubarchive {
  derived_table: {
    sql: SELECT * FROM
    TABLE_DATE_RANGE([githubarchive:day.],
    {% date_start date_filter %},
    DATE_ADD({% date_end date_filter %},1,"DAY")
    )
    ;;
  }

  filter: date_filter {
    type: date
  }
}

Karena SQL ini akan gagal jika NULL diganti dengan tanggal, Anda perlu menambahkan always_filter ke Eksplorasi: 

explore: githubarchive {
  always_filter: {
    filters: [date_filter: "2 days ago"]
  }
}

Contoh 2: LookML yang tidak bergantung pada always_filter

Dalam contoh ini, rentang tanggal default dienkode dalam LookML. Karena COALESCE menampilkan jenis unknown, saya akhirnya harus menggunakan IFNULL untuk membuat SQL berfungsi.

  • Batas bawah: IFNULL({% date_start date_filter %},CURRENT_DATE())
  • Batas atas: IFNULL({% date_end date_filter %},CURRENT_DATE()) + 1 hari 
view: githubarchive {
  derived_table: {
    sql: SELECT * FROM
    TABLE_DATE_RANGE([githubarchive:day.],
    IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()),
    DATE_ADD(IFNULL({% date_end date_filter %},CURRENT_TIMESTAMP()),1,"DAY")
    )
    ;;
  }
  filter: date_filter {
    type: date
  }
}

Fungsi periode N hari di akhir (di BigQuery)

Saat melakukan analisis tertentu, penghitungan diharapkan dalam beberapa bentuk agregat selama jangka waktu historis. Untuk menjalankan operasi ini di SQL, biasanya seseorang akan mengimplementasikan fungsi jendela yang menjangkau kembali n jumlah baris untuk tabel yang unik berdasarkan tanggal. Namun, ada catch-22 saat menggunakan tabel yang dipartisi menurut tanggal — kita harus terlebih dahulu menentukan kumpulan tabel yang akan dijalankan oleh kueri, bahkan karena kueri benar-benar membutuhkan tabel historis tambahan untuk komputasi.

Solusi: Biarkan tanggal mulai lebih awal dari tanggal yang diberikan di filter tanggal. Berikut adalah contoh yang menghubungi kembali satu minggu lagi: 

view: githubarchive {
  derived_table: {
    sql:  SELECT y._date,
                y.foo,
                y.bar
            FROM (
              SELECT _date,
                    SUM(foo) OVER (ORDER BY _date RANGE BETWEEN x PRECEDING AND CURRENT ROW),
                    COUNT(DISTINCT(bar)) OVER (ORDER BY _date RANGE BETWEEN x PRECEDING AND CURRENT ROW)
                FROM (
                    SELECT _date,
                          foo,
                          bar
                      FROM TABLE_DATE_RANGE([something:something_else.], DATE_ADD(IFNULL({% date_start date_filter %},CURRENT_TIMESTAMP()), -7, "DAY"), IFNULL({% date_end date_filter %},CURRENT_TIMESTAMP()))
                      ) x
            ) y
          WHERE {% condition date_filter %} y._date {% endcondition %};;
  }
  filter: date_filter {
    type: date
  }
}

Pernyataan SELECT tambahan diperlukan karena memberikan batasan WHERE untuk memangkas hasil rangkaian kembali ke rentang tanggal yang awalnya ditentukan pengguna dalam kueri.

Tabel dipartisi menurut tanggal melalui string dengan format 'YYYY-MM-DD' (di Presto)

Menggunakan kolom yang dipartisi merupakan pola umum dalam tabel Hadoop untuk mempercepat waktu penelusuran untuk kolom yang sering ditelusuri, terutama tanggal. Format kolom tanggal dapat bebas, meskipun YYYY-MM-DD dan YYYYMMDD adalah yang paling umum. Jenis kolom tanggal dapat berupa string, tanggal, atau angka.

Dalam contoh ini, tabel Hive table_part_by_yyyy_mm_dd memiliki kolom yang dipartisi dt, string berformat YYYY-MM-DD, yang ditelusuri oleh Presto.

Saat generator pertama kali dijalankan, LookML akan terlihat seperti ini: 

view: table_part_by_yyyy_mm_dd {
  sql_table_name: hive.taxi. table_part_by_yyyy_mm_dd ;;
  suggestions: no
  dimension: dt {
    type: string
    sql: ${TABLE}.dt ;;
  }
}

Beberapa catatan tentang kode dalam ekspresi di kedua contoh berikut:

  • Output date_start dan date_end adalah type: timestamp.
  • date_format( <expr>, '%Y-%m-%d') digunakan untuk mengonversi stempel waktu menjadi string dan ke format yang tepat.
  • coalesce ditujukan untuk menangani kasus NULLs jika seseorang mengetik filter seperti before 2010-01-01 atau after 2012-12-31.
  • Ini adalah kode dialek Presto, jadi Hive akan memiliki beberapa perbedaan dalam string format (yyyy-MM-dd) dan date_format tidak dapat mengambil nilai NULL, sehingga coalesce harus pindah ke sana dengan semacam nilai default.

Contoh 1: LookML yang menggunakan ekspresi tabel umum untuk memfilter tabel

Contoh ini menggunakan tabel turunan untuk memfilter tabel.

view: table_part_by_yyyy_mm_dd {
  # sql_table_name: hive.taxi. table_part_by_yyyy_mm_dd
  suggestions: no
  derived_table: {
    sql: SELECT * FROM hive.taxi. table_part_by_yyyy_mm_dd
      WHERE ( coalesce( dt >= date_format({% date_start date_filter %}, '%Y-%m-%d'), TRUE) )
      AND ( coalesce( dt <= date_format({% date_end date_filter %}, '%Y-%m-%d'), TRUE) )
      ;;
  }
  filter: date_filter {
    type: date
  }
  dimension: dt {
    type: string
    sql: ${TABLE}.dt ;;
  }
}

Biasanya, tabel yang dipartisi memerlukan waktu terlalu lama untuk pemindaian tabel penuh (dan menggunakan terlalu banyak resource cluster), jadi sebaiknya Anda juga menempatkan filter default di Explore untuk tampilan ini: 

explore: table_part_by_yyyy_mm_dd {
  always_filter: {
    filters: [date_filter: "2013-01"]
  }
}

Contoh 2: LookML yang memfilter langsung di predikat

Contoh ini melakukan pemfilteran predikat langsung pada tabel, tanpa subkueri atau ekspresi tabel umum. 

view: table_part_by_yyyy_mm_dd {
  sql_table_name: hive.taxi.table_part_by_yyyy_mm_dd ;;
  filter: date_filter {
    type: date
    sql: ( coalesce( ${dt} >= date_format({% date_start date_filter %}, '%Y-%m-%d'), TRUE) )
    AND ( coalesce( ${dt} <= date_format({% date_end date_filter %}, '%Y-%m-%'), TRUE) );;
  }
  dimension: dt {
    type: string
    sql: ${TABLE}.dt ;;
  }
}

Kita dapat memvalidasi bahwa partisi tabel benar-benar digunakan dengan memeriksa output EXPLAIN di SQL Runner untuk kueri yang dibuat oleh LookML ini (Anda dapat mengaksesnya dengan mengklik bagian SQL di tab Data pada halaman Explore), Anda akan melihat hasil seperti ini: 

output[table_part_by_yyyy_mm_dd.count] => [count:bigint]
table_part_by_yyyy_mm_dd.count := count
  TopN[500 by (count DESC_NULLS_LAST)] => [count:bigint]
  Aggregate(FINAL) => [count:bigint]
  count := "count"("count_4")
  RemoteExchange[GATHER] => count_4:bigint
  Aggregate(PARTIAL) => [count_4:bigint]
  count_4 := "count"(*)
  Filter[(COALESCE(("dt" >= CAST('2013-04-01' AS VARCHAR)), true) AND COALESCE(("dt" <= CAST('2016-08-01' AS VARCHAR)), true))] => [dt:varchar]
  TableScan[hive:hive:taxi: table_part_by_yyyy_mm_dd, originalConstraint = (COALESCE(("dt" >= CAST('2013-04-01' AS VARCHAR)), true) AND COALESCE(("dt" <= CAST('2016-08-01' AS VARCHAR)), true))] => [dt:varchar]
  LAYOUT: hive dt := HiveColumnHandle{clientId=hive, name=dt, hiveType=string, hiveColumnIndex=-1, partitionKey=true}
  :: [[2013-04-01, 2013-12-31]]

partitionKey=true beserta rentang kunci partisi yang tercantum menunjukkan bahwa metode tersebut hanya memindai kolom yang dipartisi tersebut.