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 ermöglicht eine detaillierte Steuerung bei der Arbeit mit Tabellenplatzhalterfunktionen wie
TABLE_DATE_RANGEundTABLE_QUERY. Daher reicht die Verwendung von{% table_date_range prefix date_filter %}nicht aus, um Datumsfilter festzulegen. -
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_filterkein Wert angegeben ist, werden sowohl{% date_start date_filter %}als auch{% date_end date_filter %}alsNULLausgewertet. -
Bei einem offenen
date_filter(z. B.before 2016-01-01oderafter 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 führt, können Sie IFNULL oder COALESCE in der LookML verwenden.
Anwendungsbeispiele
Monatlich partitionierte Spalten (in BigQuery)
In einigen BigQuery-Datasets sind Tabellen nach Monat organisiert, wobei die Tabellen-ID die Kombination aus Jahr und Monat als Suffix hat. Ein Beispiel hierfür ist das öffentliche BigQuery-Dataset [fh-bigquery:wikipedia] in Wikipedia, das Tabellen mit den Namen pagecounts_201601, pagecounts_201602, pagecounts_201603 usw. enthält.
Beispiel 1: Von always_filter abhängiger LookML-Code
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_idbezieht sich auf den Namen der Tabelle im Dataset. -
length(table_id) = 17sorgt dafür, dass die anderen Tabellen mit Namen wiepagecounts_201407_en_top64kignoriert werden. -
STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m')gibt nur denYYYYmm-Teil des Startdatums aus.
NULL wird durch date_filter ersetzt. Um dies zu umgehen, ist ein always_filter auf dem 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 für die Abfrage zu codieren. Im folgenden Beispiel werden die letzten zwei Monate verwendet:
-
Die Untergrenze:
COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH')) -
Die 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
}
}
Bei Abfragen in amerikanischen Zeitzonen (in BigQuery) sind Protokolldateien in UTC angegeben.
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 abgerufen 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 die Datumsangaben durch NULL ersetzt werden, muss dem Explore 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 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()) -
Die 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 der Durchführung bestimmter Analysen werden Berechnungen in aggregierter Form über einen vergangenen Zeitraum erwartet. Um diesen Vorgang in SQL durchzuführen, wird in der Regel eine Fensterfunktion implementiert, die die Anzahl von n Zeilen einer Tabelle nach Datum zurückbewegt. Es gibt jedoch einen Catch-22 bei Verwendung einer nach Datum partitionierten Tabelle. Zuerst muss der Satz von Tabellen bestimmt werden, für den die Abfrage ausgeführt wird, auch wenn die Abfrage tatsächlich zusätzliche historische Tabellen für die Berechnung benötigt.
Lösung: Legen Sie fest, dass das Startdatum vor den im Datumsfilter angegebenen Daten liegt. Hier ist ein Beispiel für eine weitere Woche:
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 bereitstellt, um das Ergebnisdatensatz wieder auf den Zeitraum zu verkürzen, den der Nutzer ursprünglich in der Abfrage angegeben hat.
Tabelle nach Datum über den String „JJJJ-MM-TT“ partitioniert (in Presto)
In Hadoop-Tabellen ist es ein gängiges Muster, 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 am häufigsten YYYY-MM-DD und YYYYMMDD verwendet werden. Der Typ der Datumsspalte kann String, Datum oder Zahl sein.
In diesem Beispiel hat die Hive-Tabelle table_part_by_yyyy_mm_dd eine partitionierte Spalte dt mit einem String im Format YYYY-MM-DD, die von Presto durchsucht 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 Hinweise zum Code in den Ausdrücken in den beiden folgenden Beispielen:
-
Die Ausgabe von
date_startunddate_endisttype: timestamp. -
Mit
date_format( <expr>, '%Y-%m-%d')wird der Zeitstempel in einen String und in das richtige Format konvertiert. -
Die
coalescedient dazu, den Fall von NULL-Werten zu verarbeiten, wenn jemand einen Filter wiebefore 2010-01-01oderafter 2012-12-31eingibt. -
Da es sich um einen Presto-Dialekt handelt, weist Hive einige Unterschiede im Formatstring (
yyyy-MM-dd) auf unddate_formatkann keinen NULL-Wert annehmen, sodasscoalescemit einem Standardwert eingefügt werden muss.
Beispiel 1: LookML, die einen allgemeinen Tabellenausdruck zum Filtern der Tabelle verwendet
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 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.