Puoi utilizzare i filtri basati su modelli per fare riferimento alle date selezionando la data di inizio e la data di fine in un filtro data, rispettivamente {% date_start date_filter %}
e {% date_end date_filter %}
. In questa pagina vengono illustrati alcuni esempi di casi d'uso e i passaggi per realizzarli.
Note sulla sintassi
La seguente sintassi funziona con la maggior parte dei dialetti, ma alcuni hanno casi d'uso specifici. Esempio:-
BigQuery consente un controllo granulare quando si lavora con funzioni con caratteri jolly nelle tabelle, come
TABLE_DATE_RANGE
eTABLE_QUERY
, quindi usare{% table_date_range prefix date_filter %}
non è sufficiente per specificare i filtri della data. -
Hadoop consente di lavorare con colonne partizionate in base alle date, indipendentemente dal tipo (
string
,date
) o dal formato (YYYY-MM-DD
) della colonna.
Note sull'utilizzo
-
Se non viene specificato alcun valore per
date_filter
, sia{% date_start date_filter %}
che{% date_end date_filter %}
restituiranno comeNULL
. -
Nel caso di un
date_filter
a risposta aperta (comebefore 2016-01-01
oafter 2016-01-01
), uno dei filtri{% date_start date_filter %}
o{% date_end date_filter %}
saràNULL
.
Per assicurarti che nessuno di questi due casi generi un codice SQL non valido, puoi utilizzare IFNULL
o COALESCE
in LookML.
Esempi di casi d'uso
Colonne partizionate mensili (in BigQuery)
In alcuni set di dati BigQuery, le tabelle sono organizzate per mese e l'ID tabella ha la combinazione anno/mese come suffisso. Un esempio è il set di dati pubblico di Wikipedia [fh-bigquery:wikipedia]
di BigQuery, che contiene tabelle denominate pagecounts_201601
, pagecounts_201602
, pagecounts_201603
e così via.
Esempio 1: LookML che dipende da always_filter
La tabella derivata seguente utilizza TABLE_QUERY([dataset], [expr])
per ottenere l'insieme di tabelle corretto su cui eseguire una query:
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 } }
Alcune note sul codice nell'espressione:
-
table_id
si riferisce al nome della tabella nel set di dati. -
length(table_id) = 17
si assicura di ignorare le altre tabelle con nomi comepagecounts_201407_en_top64k
. -
STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m')
restituirà solo la parteYYYYmm
della data di inizio.
NULL
verrà sostituito con date_filter
. Per aggirare il problema è necessario un always_filter
nell'esplorazione:
explore: pagecounts { always_filter: { filters: [date_filter: "2 months ago"] } }
Tieni presente che questo metodo non andrà comunque a buon fine per i filtri per date precedenti alla data precedente nel set di dati perché {% date_start date_filter %}
restituirà NULL
.
Esempio 2: LookML che non dipende da always_filter
È anche possibile utilizzare COALESCE
o IFNULL
per codificare un insieme predefinito di tabelle su cui eseguire una query. Nell'esempio seguente, vengono utilizzati gli ultimi due mesi:
-
Limite inferiore:
COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
-
Il limite superiore:
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 } }
Quando esegui query nei fusi orari americani (in BigQuery), i file di log sono nel fuso orario UTC
A volte i file di log di Looker vengono archiviati nel fuso orario UTC, anche se le query vengono effettuate nei fusi orari dell'Est o del Pacifico. Questo problema può causare un problema per cui i file di log sono già stati riportati alla data di domani
nel fuso orario locale della query, causando la perdita di alcuni dati.
La soluzione consiste nell'aggiungere un giorno in più alla data di fine del filtro della data per assicurarti che, dopo la mezzanotte (UTC), le voci di log vengano prelevate.
Gli esempi riportati di seguito utilizzano il set di dati pubblico [githubarchive:day]
, che ha una partizione giornaliera delle informazioni GitHub.
Esempio 1: LookML che dipende da 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 } }
Poiché l'SQL non andrà a buon fine se NULL
viene sostituito alle date, è necessario aggiungere un always_filter
all'esplorazione:
explore: githubarchive { always_filter: { filters: [date_filter: "2 days ago"] } }
Esempio 2: LookML che non dipende da always_filter
In questo esempio, l'intervallo di date predefinito è codificato in LookML. Poiché COALESCE
restituiva un tipo unknown
, alla fine ho dovuto utilizzare IFNULL
per far funzionare SQL.
-
Limite inferiore:
IFNULL({% date_start date_filter %},CURRENT_DATE())
-
Il limite superiore:
IFNULL({% date_end date_filter %},CURRENT_DATE())
+ 1 giorno
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 } }
Funzioni della finestra N giorni finali (in BigQuery)
Quando si eseguono determinate analisi, i calcoli sono previsti in forma aggregata per un periodo di tempo storico. Per eseguire questa operazione in SQL, in genere si implementa una funzione finestra che raggiunge il valore di n
di righe per una tabella univoca in base alla data. Tuttavia, c'è un catch-22 quando si utilizza una tabella partizionata in base alle date: è necessario prima dettare l'insieme di tabelle su cui verrà eseguita la query, anche se la query ha davvero bisogno di tabelle cronologiche aggiuntive per il calcolo.
Soluzione: inserisci una data di inizio precedente a quella specificata nel filtro della data. Ecco un esempio di un intervallo di una settimana aggiuntiva:
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 } }
L'istruzione SELECT
aggiuntiva è necessaria perché fornisce un vincolo WHERE
per ridurre il set di risultati all'intervallo di date originariamente specificato dall'utente nella query.
Tabella partizionata per data tramite stringa con formato "AAAA-MM-GG" (in Presto)
È un modello comune nelle tabelle Hadoop utilizzare colonne partizionate per accelerare i tempi di ricerca per le colonne in cui viene comunemente eseguita la ricerca, in particolare le date. Il formato delle colonne delle date può essere arbitrario, sebbene YYYY-MM-DD
e YYYYMMDD
sono i più comuni. Il tipo di colonna della data può essere una stringa, una data o un numero.
In questo esempio, una tabella Hive table_part_by_yyyy_mm_dd
ha una colonna partizionata dt
e una stringa in formato YYYY-MM-DD
nella quale Presto esegue la ricerca.
Alla prima esecuzione del generatore, il codice LookML sarà simile al seguente:
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 ;; } }
Alcune note sul codice nelle espressioni in entrambi i seguenti esempi:
-
L'output di
date_start
edate_end
ètype: timestamp
. -
date_format( <expr>, '%Y-%m-%d')
viene utilizzato per convertire il timestamp in una stringa e nel formato corretto. -
coalesce
consente di gestire il caso dei valori NULL se qualcuno digita un filtro comebefore 2010-01-01
oafter 2012-12-31
. -
Questo è il codice dialetto Presto, quindi Hive avrà alcune differenze nella stringa di formato (
yyyy-MM-dd
) edate_format
non può accettare un valore NULL, quindicoalesce
dovrebbe essere spostato con una sorta di valore predefinito.
Esempio 1: LookML che utilizza un'espressione di tabella comune per filtrare la tabella
Questo esempio utilizza una tabella derivata per filtrare la tabella.
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 ;; } }
In genere, le tabelle partizionate richiedono troppo tempo per le analisi complete delle tabelle (e consumano troppe risorse del cluster), quindi è consigliabile applicare un filtro predefinito anche nell'esplorazione per questa visualizzazione:
explore: table_part_by_yyyy_mm_dd { always_filter: { filters: [date_filter: "2013-01"] } }
Esempio 2: LookML che filtra direttamente nel predicato
In questo esempio, l'applicazione di filtri ai predicati avviene direttamente nella tabella, senza una sottoquery o un'espressione di tabella comune.
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 ;; } }
Possiamo verificare che le partizioni della tabella vengano effettivamente utilizzate controllando l'output di EXPLAIN
in SQL Runner per una query generata da questo LookML (puoi accedervi facendo clic sulla sezione SQL nella scheda Dati della pagina Esplora). Vedrai qualcosa di simile al seguente:
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
e l'intervallo di chiavi di partizione elencati indicano che viene eseguita la scansione solo delle colonne partizionate.