Menggunakan date_start dan date_end dengan filter tanggal

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

Catatan sintaksis

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

Catatan penggunaan

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

Untuk memastikan kedua kasus ini tidak menghasilkan SQL yang tidak valid, Anda dapat menggunakan IFNULL atau COALESCE di LookML.

Contoh kasus penggunaan

Contoh 1: LookML yang bergantung pada always_filter

Tabel turunan berikut 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 memastikan tabel lain dengan nama seperti pagecounts_201407_en_top64k diabaikan.
• 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 mengatasi hal ini, Anda memerlukan always_filter di bagian Jelajahi:

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

Perhatikan bahwa hal ini masih akan gagal untuk filter tanggal sebelum tanggal paling awal dalam set data karena {% date_start date_filter %} akan bernilai NULL.

Contoh 2: LookML yang tidak bergantung pada always_filter

Anda juga dapat menggunakan COALESCE atau IFNULL untuk mengenkode kumpulan tabel default yang akan dikueri. Dalam contoh berikut, 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
  }
}

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 harus menambahkan always_filter ke Jelajahi:

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 agar 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
  }
}

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
  }
}

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

Pola umum dalam tabel Hadoop adalah menggunakan kolom berpartisi untuk mempercepat waktu penelusuran kolom yang biasa ditelusuri, terutama tanggal. Format kolom tanggal dapat bersifat arbitrer, meskipun YYYY-MM-DD dan YYYYMMDD paling umum. Jenis kolom tanggal dapat berupa string, tanggal, atau angka.

Dalam contoh ini, tabel Hive table_part_by_yyyy_mm_dd memiliki kolom dt yang dipartisi, string yang diformat YYYY-MM-DD, yang sedang 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 digunakan untuk menangani kasus NULL jika seseorang mengetik filter seperti before 2010-01-01 atau after 2012-12-31.
• Ini adalah kode dialek Presto, sehingga Hive akan memiliki beberapa perbedaan dalam string format (yyyy-MM-dd) dan date_format tidak dapat menggunakan nilai NULL, sehingga coalesce harus dipindahkan 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, pemindaian tabel lengkap memerlukan waktu terlalu lama untuk tabel yang dipartisi (dan menggunakan terlalu banyak resource cluster), jadi sebaiknya masukkan filter default di Jelajahi untuk tampilan ini juga:

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

Contoh 2: LookML yang memfilter langsung dalam 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 dihasilkan oleh LookML ini (Anda dapat mengaksesnya dengan mengklik bagian SQL di tab Data di halaman Jelajahi), Anda akan melihat sesuatu 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 partitionKey=true hanya memindai kolom yang dipartisi tersebut.