„date_start“ und „date_end“ mit Datumsfiltern verwenden

Mit Vorlagenfiltern können Sie auf Datumsangaben 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 Anwendungsbeispiele und die entsprechenden Schritte.

Syntaxhinweise

Die folgende Syntax funktioniert mit den meisten Dialekten, aber für bestimmte Dialekte gibt es spezielle Anwendungsfälle. Beispiel:

• BigQuery bietet mehr Kontrolle bei der Verwendung von Tabellen-Wildcard-Funktionen wie TABLE_DATE_RANGE und TABLE_QUERY. Daher reicht die Verwendung von {% table_date_range prefix date_filter %} nicht aus, um Datumsfilter anzugeben.
• Hadoop ermöglicht die Arbeit mit datumsbasierten Partitionierungsspalten, unabhängig vom Typ (string, date) oder Format (YYYY-MM-DD) der Spalte.

Verwendungshinweise

• Wenn kein Wert für date_filter angegeben ist, ergeben sowohl {% date_start date_filter %} als auch {% date_end date_filter %} den Wert NULL.
• 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 keiner dieser beiden Fälle zu ungültigem SQL führt, können Sie IFNULL oder COALESCE in LookML verwenden.

Anwendungsbeispiele

Monatlich partitionierte Spalten (in BigQuery)

In einigen BigQuery-Datasets sind Tabellen nach Monat organisiert und die Tabellen-ID hat die Kombination aus Jahr und Monat als Suffix. Ein Beispiel dafür ist das folgende Dataset, das viele Tabellen mit Namen wie pagecounts_201601, pagecounts_201602 und pagecounts_201603 enthält.

Beispiel 1: LookML, das von always_filter abhängt

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

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.

NULL wird durch die date_filter-Teile ersetzt. Um dieses Problem zu umgehen, ist eine always_filter im Explore erforderlich:

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

Das funktioniert jedoch weiterhin nicht für Filter für Datumsangaben vor dem frühesten Datum im Dataset, da {% date_start date_filter %} als NULL ausgewertet wird.

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

Es ist auch möglich, COALESCE oder IFNULL zu verwenden, um einen Standardsatz von abzufragenden Tabellen zu codieren. Im folgenden Beispiel werden die letzten beiden 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
  }
}

Logdateien sind bei Abfragen in amerikanischen Zeitzonen (in BigQuery) in UTC.

Manchmal werden Ihre Looker-Logdateien in UTC gespeichert, obwohl Sie Abfragen in der Eastern Time Zone oder der Pacific Time Zone ausführen. Dieses Problem kann dazu führen, dass die Logdateien bereits auf das Datum von morgen in der lokalen Zeitzone der Anfrage umgestellt wurden, was zu fehlenden Daten führt.

Die Lösung besteht darin, dem Enddatum des Datumsfilters einen zusätzlichen Tag hinzuzufügen, damit diese Log-Einträge erfasst werden, wenn es nach Mitternacht UTC ist.

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

Beispiel 1: LookML, das 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 dieser SQL-Code 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, die nicht von always_filter abhängt

In diesem Beispiel ist der Standardzeitraum im LookML-Code codiert. Da COALESCE den Typ unknown zurückgegeben hat, musste ich letztendlich IFNULL verwenden, damit der SQL-Code 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
  }
}

Funktionen für das N-Tage-Fenster (in BigQuery)

Bei bestimmten Analysen kann es sinnvoll sein, Daten über einen historischen Zeitraum hinweg zu aggregieren. Um diese Operation in SQL auszuführen, wird in der Regel eine Fensterfunktion implementiert, die für eine nach Datum eindeutige Tabelle n Zeilen zurückreicht. Bei der Verwendung einer datumspartitionierten Tabelle gibt es jedoch ein Dilemma: Zuerst muss die Gruppe von Tabellen angegeben werden, für die die Abfrage ausgeführt wird, obwohl für die Berechnung zusätzliche Verlaufsdaten erforderlich sind.

Lösung:Das Startdatum darf vor den im Datumsfilter angegebenen Daten liegen. Hier ein Beispiel, das eine zusätzliche Woche umfasst:

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 enthält, um das Ergebnisset auf den Zeitraum zu beschränken, den der Nutzer ursprünglich in der Abfrage angegeben hat.

Tabelle, die nach Datum partitioniert ist (String im Format „JJJJ-MM-TT“ in Presto)

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 sein, wobei YYYY-MM-DD und YYYYMMDD am häufigsten 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 als String formatierten 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 ;;
  }
}

Einige Hinweise 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 wird der Fall von NULL-Werten behandelt, wenn jemand einen Filter wie before 2010-01-01 oder after 2012-12-31 eingibt.
• Dies ist Presto-Dialektcode. Daher gibt es in Hive einige Unterschiede im Formatstring (yyyy-MM-dd). Außerdem kann date_format keinen NULL-Wert annehmen. Daher müsste coalesce mit einem Standardwert dorthin verschoben werden.

Beispiel 1: LookML, in der ein CTE zum Filtern der Tabelle verwendet wird

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 vollständige Tabellenscans bei partitionierten Tabellen zu lange und verbrauchen zu viele Clusterressourcen. Daher ist es ratsam, auch für diese Ansicht einen Standardfilter für den Explore festzulegen:

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

Beispiel 2: LookML, die direkt im Prädikat gefiltert wird

In diesem Beispiel wird die Prädikatfilterung direkt in der Tabelle ohne Unterabfrage oder Common Table Expression durchgeführt.

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, indem wir die Ausgabe von EXPLAIN in SQL Runner für eine Abfrage prüfen, die von diesem LookML-Code generiert wurde. Sie können darauf zugreifen, indem Sie auf dem Tab „Daten“ der Seite „Erkunden“ auf den SQL-Bereich klicken. Dort sehen Sie etwa Folgendes:

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 zusammen mit dem Bereich der aufgeführten Partitions-Schlüssel weisen darauf hin, dass nur diese partitionierten Spalten gescannt werden.