Anda dapat menggunakan filter ber-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 khusus. Contoh:-
BigQuery memungkinkan kontrol yang lebih besar saat menggunakan fungsi kartu tabel seperti
TABLE_DATE_RANGE
danTABLE_QUERY
, sehingga penggunaan{% table_date_range prefix date_filter %}
tidak cukup untuk menentukan filter tanggal. -
Hadoop memungkinkan penggunaan kolom yang dipartisi menurut 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 bernilaiNULL
. -
Jika
date_filter
tidak ditentukan (sepertibefore 2016-01-01
atauafter 2016-01-01
), maka salah satu filter{% date_start date_filter %}
atau{% date_end date_filter %}
akan menjadiNULL
.
Untuk memastikan kedua kasus ini tidak 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 dan bulan sebagai akhiran. Contohnya ada di set data berikut, yang memiliki banyak tabel dengan nama seperti pagecounts_201601
, pagecounts_201602
, pagecounts_201603
.
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 bahwa tabel lain dengan nama sepertipagecounts_201407_en_top64k
diabaikan. -
STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m')
hanya akan menampilkan bagianYYYYmm
dari tanggal mulai.
NULL
akan menggantikan bagian date_filter
. Untuk mengatasi hal ini, Anda memerlukan always_filter
di Jelajahi:
explore: pagecounts { always_filter: { filters: [date_filter: "2 months ago"] } }
Perhatikan bahwa hal ini akan tetap gagal untuk filter tanggal dari sebelum tanggal paling awal dalam set data karena {% date_start date_filter %}
akan dievaluasi menjadi 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 } }
File log dalam UTC saat membuat kueri di zona waktu Amerika (di BigQuery)
Terkadang file log Looker Anda disimpan dalam UTC, meskipun Anda membuat kueri di zona waktu Eastern atau Pacific. Masalah ini dapat menyebabkan masalah saat file log telah di-roll ke tanggal besok
di zona waktu lokal kueri, sehingga beberapa data terlewat.
Solusinya adalah menambahkan satu hari ke tanggal akhir filter tanggal, untuk memastikan bahwa jika sudah melewati tengah malam UTC, entri log tersebut akan diambil.
Contoh berikut menggunakan set data [githubarchive:day]
publik, yang memiliki partisi harian 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
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 } }
Fungsi jendela N hari terakhir (di BigQuery)
Saat melakukan analisis tertentu, penghitungan diharapkan dalam beberapa bentuk gabungan selama jangka waktu historis. Untuk melakukan operasi ini di SQL, biasanya Anda akan menerapkan fungsi jendela yang menjangkau kembali n
jumlah baris untuk tabel yang unik menurut tanggal. Namun, ada dilema saat menggunakan tabel yang dipartisi menurut tanggal — seseorang harus terlebih dahulu menentukan kumpulan tabel yang akan dijalankan kuerinya, meskipun kueri tersebut benar-benar memerlukan tabel historis tambahan untuk komputasi.
Solusinya: Izinkan tanggal mulai lebih awal dari tanggal yang diberikan di filter tanggal. Berikut adalah contoh yang menjangkau satu minggu tambahan:
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 kembali set hasil ke rentang tanggal yang awalnya ditentukan pengguna dalam kueri.
Tabel yang dipartisi menurut 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 sering ditelusuri, terutama tanggal. Format kolom tanggal dapat berupa apa saja, 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 berpartisi dt
, YYYY-MM-DD
berformat string, yang sedang dicari 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
dandate_end
adalahtype: timestamp
. -
date_format( <expr>, '%Y-%m-%d')
digunakan untuk mengonversi stempel waktu menjadi string dan ke format yang benar. -
coalesce
digunakan untuk menangani kasus NULL jika seseorang mengetik filter sepertibefore 2010-01-01
atauafter 2012-12-31
. -
Ini adalah kode dialek Presto, jadi Hive akan memiliki beberapa perbedaan dalam string format (
yyyy-MM-dd
) dandate_format
tidak dapat mengambil nilai NULL, sehinggacoalesce
harus dipindahkan ke sana dengan beberapa jenis 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 berpartisi memerlukan waktu terlalu lama untuk pemindaian tabel penuh (dan menggunakan terlalu banyak resource cluster), jadi sebaiknya terapkan filter default pada Eksplorasi 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 pada halaman Jelajah), 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 kueri hanya memindai kolom yang dipartisi tersebut.