„date_start“ und „date_end“ mit Datumsfiltern verwenden

Sie können Filtervorlagen verwenden, um auf Datumsangaben zu verweisen. Wählen Sie dazu in einem Datumsfilter das Start- und Enddatum aus – {% date_start date_filter %} bzw. {% date_end date_filter %}. Auf dieser Seite werden einige Anwendungsfälle und die Schritte zu deren Umsetzung erläutert.

Syntaxhinweise

Die folgende Syntax funktioniert mit den meisten Dialekten, aber bestimmte Dialekte haben spezielle Anwendungsfälle. Beispiel:

• BigQuery ermöglicht eine präzise Steuerung bei der Arbeit mit Tabellenplatzhalterfunktionen wie TABLE_DATE_RANGE und TABLE_QUERY, sodass die Verwendung von {% table_date_range prefix date_filter %} zum Angeben von Datumsfiltern nicht ausreicht.
• Hadoop ermöglicht die Arbeit mit nach Datum partitionierten Spalten unabhängig vom Typ (string, date) oder Format (YYYY-MM-DD) der Spalte.

Verwendungshinweise

• Wenn für date_filter kein Wert angegeben ist, werden sowohl {% date_start date_filter %} als auch {% date_end date_filter %} als NULL ausgewertet.
• Im Fall eines offenen date_filter-Filters (z. B. before 2016-01-01 oder after 2016-01-01) ist einer der Filter {% date_start date_filter %} oder {% date_end date_filter %} NULL.

Um sicherzustellen, dass keiner dieser beiden Fälle zu einem ungültigen SQL-Code führt, können Sie in LookML IFNULL oder COALESCE verwenden.

Anwendungsbeispiele

Monatlich partitionierte Spalten (in BigQuery)

In einigen BigQuery-Datasets sind die Tabellen nach Monat organisiert und die Tabellen-ID hat die Kombination aus Jahr und Monat als Suffix. Ein Beispiel dafür finden Sie im öffentlichen BigQuery-Dataset [fh-bigquery:wikipedia] in Wikipedia mit Tabellen namens pagecounts_201601, pagecounts_201602, pagecounts_201603 usw.

Beispiel 1: LookML, der von always_filter abhängt

In der folgenden abgeleiteten Tabelle wird TABLE_QUERY([dataset], [expr]) verwendet, um den richtigen Satz von Tabellen für die Abfrage abzurufen:

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

Hinweise zum Code im Ausdruck:

• table_id bezieht sich auf den Namen der Tabelle im Dataset.
• length(table_id) = 17 sorgt dafür, dass die anderen Tabellen mit Namen wie pagecounts_201407_en_top64k ignoriert werden.
• STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') gibt nur den YYYYmm-Teil des Startdatums aus.

Die date_filter-Teile werden durch NULL ersetzt. Um dies zu umgehen, ist ein always_filter im Explore erforderlich:

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

Beachten Sie, dass dies weiterhin bei Filtern für Datumsangaben vor dem frühesten Datum im Dataset fehlschlägt, da {% date_start date_filter %} als NULL ausgewertet wird.

Beispiel 2: LookML ohne Abhängigkeit von always_filter

Es ist auch möglich, mit COALESCE oder IFNULL einen Standardsatz von Tabellen zu codieren, die abgefragt werden sollen. Im folgenden Beispiel werden die letzten zwei Monate verwendet:

• Untergrenze: COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
• Obergrenze: 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
  }
}

Protokolldateien werden bei Abfragen in amerikanischen Zeitzonen (in BigQuery) in UTC angegeben

Manchmal werden Ihre Looker-Protokolldateien in UTC gespeichert, obwohl die Abfragen in östlichen oder pazifischen Zeitzonen erfolgen. Dies kann dazu führen, dass die Protokolldateien bereits zum morgen Datum in der lokalen Zeitzone der Abfrage übertragen wurden und einige Daten nicht erfasst wurden.

Die Lösung besteht darin, dem Enddatum des Datumsfilters einen zusätzlichen Tag hinzuzufügen, um sicherzustellen, dass diese Logeinträge übernommen werden, wenn es nach Mitternacht UTC liegt.

In den folgenden Beispielen wird das öffentliche Dataset [githubarchive:day] verwendet, das eine tägliche Partition von GitHub-Informationen enthält.

Beispiel 1: LookML, der von always_filter abhängt

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

Da diese SQL fehlschlägt, wenn NULL durch die Datumsangaben ersetzt wird, muss dem Explore ein always_filter hinzugefügt werden:

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

Beispiel 2: LookML ohne Abhängigkeit von always_filter

In diesem Beispiel ist der Standardzeitraum in LookML codiert. Da COALESCE einen unknown-Typ zurückgibt, musste ich letztlich IFNULL verwenden, damit SQL funktioniert.

• Untergrenze: IFNULL({% date_start date_filter %},CURRENT_DATE())
• Obergrenze: IFNULL({% date_end date_filter %},CURRENT_DATE()) + 1 Tag

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

N-Tage-Fensterfunktionen (in BigQuery)

Bei der Durchführung bestimmter Analysen werden Berechnungen in aggregierter Form über einen historischen Zeitraum erwartet. Um diesen Vorgang in SQL auszuführen, wird in der Regel eine Fensterfunktion implementiert, die die Anzahl von n Zeilen für eine Tabelle zurückgreift, die nach Datum eindeutig ist. Es gibt jedoch einen Catch-22-Fehler, wenn eine nach Datum partitionierte Tabelle verwendet wird: Zuerst muss der Satz von Tabellen vorgegeben werden, für die die Abfrage ausgeführt wird, auch wenn die Abfrage wirklich zusätzliche historische Tabellen für die Berechnung benötigt.

Lösung:Legen Sie fest, dass das Startdatum vor dem im Datumsfilter angegebenen Datum liegt. Hier ist ein Beispiel für eine weitere Woche zurück:

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

Die zusätzliche SELECT-Anweisung ist erforderlich, da sie die Einschränkung WHERE bereitstellt, mit der die Ergebnisse auf den Zeitraum reduziert werden, den der Nutzer ursprünglich in der Abfrage angegeben hat.

Tabelle, nach Datum über einen String im Format "JJJJ-MM-TT" (in Presto) partitioniert

Es ist ein gängiges Muster in Hadoop-Tabellen, partitionierte Spalten zu verwenden, um die Suchzeiten für Spalten zu verkürzen, nach denen häufig gesucht wird, insbesondere nach Datumsangaben. Das Format der Datumsspalten kann beliebig gewählt werden. Am häufigsten sind jedoch YYYY-MM-DD und YYYYMMDD. Der Typ der Datumsspalte kann ein String, ein Datum oder eine Zahl sein.

In diesem Beispiel hat eine Hive-Tabelle table_part_by_yyyy_mm_dd eine partitionierte Spalte dt, einen String mit dem Format YYYY-MM-DD, der von Presto durchsucht wird.

Wenn der Generator zum ersten Mal ausgeführt wird, sieht der LookML-Code so aus:

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

Hinweise zum Code in den Ausdrücken in den beiden folgenden Beispielen:

• Die Ausgabe von date_start und date_end ist type: timestamp.
• date_format( <expr>, '%Y-%m-%d') wird verwendet, um den Zeitstempel in einen String und in das richtige Format zu konvertieren.
• coalesce ist für den Fall von NULL-Werten geeignet, wenn jemand einen Filter wie before 2010-01-01 oder after 2012-12-31 eingibt.
• Dies ist ein Presto-Dialektcode. Hive hat also einige Unterschiede im Formatstring (yyyy-MM-dd) und date_format kann keinen NULL-Wert annehmen, sodass coalesce mit einem Standardwert dorthin verschoben werden muss.

Beispiel 1: LookML mit einem allgemeinen Tabellenausdruck zum Filtern der Tabelle

In diesem Beispiel wird eine abgeleitete Tabelle zum Filtern der Tabelle verwendet.

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

Normalerweise benötigen partitionierte Tabellen zu lange für vollständige Tabellenscans (und verbrauchen zu viele Clusterressourcen). Daher empfiehlt es sich, auch für diese Ansicht einen Standardfilter für das Explore festzulegen:

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

Beispiel 2: LookML, der direkt im Prädikat filtert

In diesem Beispiel wird über das Prädikat direkt anhand der Tabelle gefiltert, und zwar ohne Unterabfrage oder allgemeinen Tabellenausdruck.

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

Wir können überprüfen, ob die Tabellenpartitionen tatsächlich verwendet werden. Dazu prüfen wir die Ausgabe von EXPLAIN in SQL Runner auf eine Abfrage, die von dieser LookML generiert wurde. Sie können darauf zugreifen, indem Sie auf dem Tab „Daten“ der Seite „Explore“ auf den SQL-Abschnitt klicken.

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

Der partitionKey=true zusammen mit dem aufgeführten Bereich der Partitionsschlüssel zeigt an, dass nur diese partitionierten Spalten gescannt werden.