„date_start“ und „date_end“ mit Datumsfiltern verwenden

Sie können Filtervorlagen verwenden, um auf Datumsangaben zu verweisen, indem Sie das Start- und Enddatum in einem Datumsfilter auswählen ({% date_start date_filter %} bzw. {% date_end date_filter %}). Auf dieser Seite finden Sie einige Beispiele für Anwendungsfälle und die Schritte, mit denen Sie sie erreichen können.

Syntaxhinweise

Die folgende Syntax funktioniert mit den meisten Dialekten. Bestimmte Dialekte haben jedoch spezielle Anwendungsfälle. Beispiel:

• BigQuery bietet eine detaillierte Steuerung bei der Arbeit mit Tabellen-Wildcard-Funktionen wie TABLE_DATE_RANGE und TABLE_QUERY. Die Verwendung von {% table_date_range prefix date_filter %} reicht daher nicht aus, um Datumsfilter anzugeben.
• 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, wird sowohl für {% date_start date_filter %} als auch für {% date_end date_filter %} NULL zurückgegeben.
• Bei einem offenen date_filter (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.

Damit in keinem dieser beiden Fälle ungültiges SQL generiert wird, können Sie in der LookML IFNULL oder COALESCE verwenden.

Anwendungsbeispiele

Nach Monaten partitionierte Spalten (in BigQuery)

In einigen BigQuery-Datasets sind Tabellen nach Monaten organisiert und die Tabellen-ID hat die Kombination aus Jahr und Monat als Suffix. Ein Beispiel hierfür ist das öffentliche BigQuery-Wikipedia-Dataset [fh-bigquery:wikipedia] mit Tabellen namens pagecounts_201601, pagecounts_201602, pagecounts_201603 usw.

Beispiel 1: LookML, die von always_filter abhängt

Die folgende abgeleitete Tabelle verwendet TABLE_QUERY([dataset], [expr]), 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
  }
}

Einige 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.

NULL wird durch date_filter ersetzt. Um dieses Problem zu beheben, ist ein always_filter im Explore erforderlich:

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

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

Beispiel 2: LookML, die nicht von always_filter abhängt

Sie können auch COALESCE oder IFNULL verwenden, um einen Standardsatz von Tabellen zu codieren, die abgefragt werden sollen. Im folgenden Beispiel werden die letzten zwei Monate verwendet:

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

Logdateien sind in UTC, wenn in BigQuery in amerikanischen Zeitzonen abgefragt wird

Manchmal werden Ihre Looker-Protokolldateien in UTC gespeichert, obwohl die Abfragen in der östlichen oder pazifischen Zeitzone erfolgen. Dieses Problem kann zu einem Problem führen, bei dem die Protokolldateien bereits auf das Datum morgen in der lokalen Zeitzone der Abfrage übertragen wurden, wodurch Daten nicht erfasst werden.

Die Lösung besteht darin, dem Enddatum des Datumsfilters einen zusätzlichen Tag hinzuzufügen, damit diese Logeinträge auch nach Mitternacht (UTC) erfasst werden.

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

Beispiel 1: Von always_filter abhängiger LookML-Code

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-Abfrage fehlschlägt, wenn NULL anstelle der Datumsangaben verwendet wird, muss der explorativen Datenanalyse eine always_filter hinzugefügt werden:

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

Beispiel 2: LookML, die nicht von always_filter abhängt

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

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

Funktionen für nachfolgende n-Tage-Fenster (in BigQuery)

Bei bestimmten Analysen werden Berechnungen in aggregierter Form über einen bestimmten Zeitraum erwartet. Um diesen Vorgang in SQL auszuführen, wird in der Regel eine Fensterfunktion implementiert, die n Zeilen zurückreicht, für eine Tabelle, die nach Datum eindeutig ist. Bei der Verwendung einer datumspartitionierten Tabelle gibt es jedoch eine Zwickmühle: Sie müssen zuerst die Tabellen angeben, auf die die Abfrage angewendet werden soll, auch wenn für die Berechnung zusätzliche Verlaufstabellen erforderlich sind.

Lösung: Legen Sie fest, dass das Startdatum vor den im Datumsfilter angegebenen Daten liegt. Hier ein Beispiel, das eine zusätzliche Woche zurückreicht:

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 eine WHERE-Einschränkung liefert, um das Ergebnis auf den ursprünglichen Zeitraum des Nutzers in der Abfrage zu begrenzen.

Tabelle nach Datum über den String „JJJJ-MM-TT“ partitioniert (in Presto)

In Hadoop-Tabellen werden häufig partitionierte Spalten verwendet, um die Suchzeiten für Spalten zu verkürzen, nach denen häufig gesucht wird, insbesondere nach Datumsangaben. Das Format der Datumsspalten kann beliebig sein, wobei am häufigsten YYYY-MM-DD und YYYYMMDD verwendet werden. Der Typ der Datumsspalte kann String, Datum oder Zahl sein.

In diesem Beispiel hat eine Hive-Tabelle table_part_by_yyyy_mm_dd eine partitionierte Spalte dt, einen String im Format YYYY-MM-DD, in dem nach Presto gesucht wird.

Bei der ersten Ausführung des Generators 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 ;;
  }
}

Einige Anmerkungen zum Code in den Ausdrücken in den beiden folgenden Beispielen:

• Die Ausgabe von date_start und date_end ist type: timestamp.
• Mit date_format( <expr>, '%Y-%m-%d') wird der Zeitstempel in einen String und in das richtige Format konvertiert.
• Mit coalesce werden NULL-Werte verarbeitet, wenn ein Nutzer einen Filter wie before 2010-01-01 oder after 2012-12-31 eingibt.
• Da es sich um einen Presto-Dialekt handelt, weist Hive einige Unterschiede im Formatstring (yyyy-MM-dd) auf und date_format kann keinen NULL-Wert annehmen, sodass coalesce mit einem Standardwert eingefügt werden muss.

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

In diesem Beispiel wird eine abgeleitete Tabelle verwendet, um die Tabelle zu filtern.

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 dauern partitionierte Tabellen zu lange für vollständige Tabellenscans (und verbrauchen zu viele Clusterressourcen). Daher ist es ratsam, auch für diese Ansicht einen Standardfilter auf das Explore anzuwenden:

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

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

In diesem Beispiel wird die Prädikatfilterung direkt auf die Tabelle angewendet, ohne eine Unterabfrage oder einen 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 im SQL Runner auf eine von diesem LookML generierte Abfrage. Sie können darauf zugreifen, indem Sie auf der Explore-Seite auf der Registerkarte „Data“ (Daten) auf den SQL-Abschnitt klicken. Die Ausgabe sieht ungefähr so aus:

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

Die partitionKey=true und der Bereich der aufgeführten Partitionsschlüssel geben an, dass nur diese partitionierten Spalten gescannt werden.