Utilizzo di date_start e date_end con i filtri data

Puoi utilizzare i filtri basati su modelli per fare riferimento alle date selezionando le date di inizio e di fine in un filtro data, rispettivamente {% date_start date_filter %} e {% date_end date_filter %}. Questa pagina illustra alcuni esempi di casi d'uso e i passaggi per realizzarli.

Note sulla sintassi

La sintassi seguente funziona con la maggior parte dei dialetti, ma alcuni dialetti hanno casi d'uso specifici. Esempio:

• BigQuery consente un maggiore controllo quando si utilizzano funzioni con caratteri jolly della tabella come TABLE_DATE_RANGE e TABLE_QUERY, pertanto l'utilizzo di {% table_date_range prefix date_filter %} non è sufficiente per specificare i filtri data.
• Hadoop consente di lavorare con colonne con partizione per data, indipendentemente dal tipo (string, date) o dal formato (YYYY-MM-DD) della colonna.

Note sull'utilizzo

• Quando non è specificato alcun valore per date_filter, sia {% date_start date_filter %} che {% date_end date_filter %} avranno come valore NULL.
• Nel caso di un date_filter aperto (ad es. before 2016-01-01 o after 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 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 di anno e mese come suffisso. Un esempio è il seguente set di dati, che contiene molte tabelle con nomi come pagecounts_201601, pagecounts_201602, pagecounts_201603.

Esempio 1: codice LookML che dipende da always_filter

La seguente tabella derivata utilizza TABLE_QUERY([dataset], [expr]) per ottenere l'insieme corretto di tabelle su cui eseguire 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 fa riferimento al nome della tabella nel set di dati.
• length(table_id) = 17 assicura che le altre tabelle con nomi come pagecounts_201407_en_top64k vengano ignorate.
• STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') restituirà solo la parte YYYYmm della data di inizio.

Le parti date_filter verranno sostituite con NULL. Per risolvere il problema, è necessario un always_filter nell'esplorazione:

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

Tieni presente che l'operazione non andrà a buon fine per i filtri relativi alle date precedenti alla data più antica del set di dati perché {% date_start date_filter %} avrà il valore NULL.

Esempio 2: codice LookML che non dipende da always_filter

È anche possibile utilizzare COALESCE o IFNULL per codificare un insieme predefinito di tabelle su cui eseguire query. Nell'esempio seguente vengono utilizzati gli ultimi due mesi:

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

I file di log sono in UTC quando esegui query nei fusi orari americani (in BigQuery)

A volte i file di log di Looker vengono archiviati in UTC, anche se esegui query nei fusi orari orientale o del Pacifico. Questo problema può causare un problema in cui i file di log sono già stati sottoposti a roll per la data di domani nel fuso orario locale della query, con conseguente perdita di alcuni dati.

La soluzione è aggiungere un giorno in più alla data di fine del filtro della data, per assicurarti che se è passata la mezzanotte UTC, queste voci di log vengano rilevate.

Gli esempi seguenti utilizzano il set di dati pubblico [githubarchive:day], che contiene una partizione giornaliera delle informazioni di GitHub.

Esempio 1: codice 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é questo SQL non andrà a buon fine se NULL viene sostituito per le date, è necessario aggiungere un always_filter all'esplorazione:

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

Esempio 2: codice 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.

• Il 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 finestra con N giorni finali (in BigQuery)

Quando esegui determinate analisi, i calcoli sono previsti in una forma aggregata in un periodo di tempo storico. Per eseguire questa operazione in SQL, in genere viene implementata una funzione finestra che recupera il numero n di righe per una tabella univoca per data. Tuttavia, esiste un dilemma quando si utilizza una tabella con partizioni per data: innanzitutto, devi specificare l'insieme di tabelle su cui verrà eseguita la query, anche se la query ha bisogno di tabelle storiche aggiuntive per il calcolo.

La soluzione:consenti che la data di inizio sia precedente alle date fornite nel filtro delle date. Ecco un esempio che risale a un'altra settimana:

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 limitare il set di risultati all'intervallo di date specificato inizialmente dall'utente nella query.

Tabella partizionata per data tramite stringa con formato "AAAA-MM-GG" (in Presto)

Nelle tabelle Hadoop è un pattern comune utilizzare colonne partizionate per velocizzare i tempi di ricerca per le colonne su cui vengono eseguite ricerche comuni, in particolare le date. Il formato delle colonne di date può essere arbitrario, anche se YYYY-MM-DD e YYYYMMDD sono i più comuni. Il tipo di colonna della data può essere stringa, data o numero.

In questo esempio, una tabella Hive table_part_by_yyyy_mm_dd ha una colonna partizionata dt, una stringa formattata YYYY-MM-DD, in cui viene eseguita la ricerca da parte di Presto.

Al primo avvio del generatore, il codice LookML ha il seguente aspetto:

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 gli esempi seguenti:

• L'output di date_start e date_end è type: timestamp.
• date_format( <expr>, '%Y-%m-%d') viene utilizzato per convertire il timestamp in una stringa e nel formato corretto.
• coalesce serve a gestire i casi di valori NULL se un utente digita un filtro come before 2010-01-01 o after 2012-12-31.
• Questo è il codice del dialetto Presto, quindi Hive avrà alcune differenze nella stringa di formato (yyyy-MM-dd) e date_format non può accettare un valore NULL, quindi coalesce dovrebbe essere inserito con una sorta di valore predefinito.

Esempio 1: codice 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 scansioni complete delle tabelle (e consumano troppe risorse del cluster), quindi è buona norma applicare un filtro predefinito anche all'esplorazione per questa vista:

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

Esempio 2: LookML che filtra direttamente nel predicato

Questo esempio esegue il filtro del predicato 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 a quanto segue:

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 elencate indicano che vengono sottoposte a scansione solo le colonne partizionate.