Utilizzare date_start e date_end con i filtri della data

Puoi utilizzare i filtri basati su modelli per fare riferimento alle date selezionando le date di inizio e di fine in un filtro di 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 di sintassi

La seguente sintassi funziona con la maggior parte dei dialetti, ma alcuni dialetti 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 e TABLE_QUERY. Di conseguenza, l'utilizzo di {% 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 %} sia {% date_end date_filter %} avranno come valore NULL.
• Nel caso di un date_filter a risposta aperta (come 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 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 è nel set di dati pubblico BigQuery di Wikipedia [fh-bigquery:wikipedia], che contiene tabelle denominate pagecounts_201601, pagecounts_201602, pagecounts_201603 e così via.

Esempio 1: LookML che dipende da always_filter

La seguente tabella derivata utilizza TABLE_QUERY([dataset], [expr]) per ottenere il set 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 si riferisce al nome della tabella nel set di dati.
• length(table_id) = 17 si assicura di ignorare le altre tabelle con nomi come pagecounts_201407_en_top64k.
• 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 aggirare questa limitazione è necessario un always_filter in Esplora:

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

Tieni presente che questa operazione non riuscirà comunque per i filtri per le date antecedenti alla prima data 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 query. Nel seguente esempio, 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 vengono eseguite query nei fusi orari americani (in BigQuery)

A volte i file di log di Looker sono archiviati nel fuso orario UTC, anche se le query vengono eseguite nei fusi orari dell'Europa orientale o del Pacifico. Questo problema può causare un problema in cui i file di log sono già stati spostati alla data di domani nel fuso orario locale della query, causando la perdita di alcuni dati.

Per risolvere il problema, aggiungi un altro giorno alla data di fine del filtro della data per assicurarti che le voci di log vengano rilevate se è trascorsa la mezzanotte (fuso orario UTC).

Negli esempi riportati di seguito viene utilizzato il set di dati pubblico [githubarchive:day], che ha una partizione giornaliera di informazioni su 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é questo SQL non riuscirà se le date vengono sostituite con NULL, è necessario aggiungere 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 stava restituendo un tipo unknown, alla fine ho dovuto usare IFNULL per far funzionare l'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 di N giorni finali (in BigQuery)

Quando si eseguono determinate analisi, i calcoli sono previsti in forma aggregata in un arco di tempo storico. Per eseguire questa operazione in SQL, in genere si implementa una funzione finestra che raggiunge n numero di righe per una tabella univoca per 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 storiche aggiuntive per il calcolo.

Soluzione: fai in modo che la data di inizio sia precedente alle date fornite nel filtro della data. Ecco un esempio di come tornare per 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
  }
}

È necessaria un'istruzione SELECT aggiuntiva 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 l'utilizzo di colonne partizionate per accelerare i tempi di ricerca per le colonne in cui la ricerca viene comunemente eseguita, in particolare in relazione alle date. Il formato delle colonne delle date può essere arbitrario, anche se i più comuni sono YYYY-MM-DD e YYYYMMDD. 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 da YYYY-MM-DD, che viene cercata da Presto.

Alla prima esecuzione del generatore, il 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 i seguenti esempi:

• 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 per gestire il caso dei valori NULL se qualcuno 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ò assumere un valore NULL, quindi coalesce dovrebbe spostarsi lì con un qualche tipo 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 è una buona idea applicare anche un filtro predefinito 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

In questo esempio viene eseguito un filtro dei predicati 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 siano 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 Data della pagina Esplora). Vedrai qualcosa di simile a questo:

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

Il parametro partitionKey=true e l'intervallo di chiavi di partizione elencate indicano che sta eseguendo la scansione solo delle colonne partizionate.