Vous pouvez utiliser des filtres modélisés pour faire référence aux dates en sélectionnant les dates de début et de fin dans un filtre de date ({% date_start date_filter %}
et {% date_end date_filter %}
, respectivement). Cette page présente quelques exemples d'utilisation et les étapes à suivre.
Remarques sur la syntaxe
La syntaxe suivante fonctionne avec la plupart des dialectes, mais certains ont des cas d'utilisation spécifiques. Exemple :-
BigQuery permet un contrôle précis lors de l'utilisation de fonctions génériques de table telles que
TABLE_DATE_RANGE
etTABLE_QUERY
. Par conséquent, l'utilisation de{% table_date_range prefix date_filter %}
ne suffit pas pour spécifier des filtres de date. -
Hadoop permet d'utiliser des colonnes partitionnées par date, quel que soit leur type (
string
,date
) ou leur format (YYYY-MM-DD
).
Remarques sur l'utilisation
-
Si aucune valeur n'est spécifiée pour
date_filter
,{% date_start date_filter %}
et{% date_end date_filter %}
donnent tous deux la valeurNULL
. -
Dans le cas d'une
date_filter
ouverte (before 2016-01-01
ouafter 2016-01-01
, par exemple), l'un des filtres{% date_start date_filter %}
ou{% date_end date_filter %}
seraNULL
.
Pour vous assurer qu'aucun de ces deux cas n'entraîne un code SQL non valide, vous pouvez utiliser IFNULL
ou COALESCE
dans le code LookML.
Exemples de cas d'utilisation
Colonnes partitionnées par mois (dans BigQuery)
Dans certains ensembles de données BigQuery, les tables sont organisées par mois, et l'ID de la table comporte la combinaison année/mois en guise de suffixe. L'ensemble de données public BigQuery Wikipédia [fh-bigquery:wikipedia]
en est un bon exemple. Il contient des tables nommées pagecounts_201601
, pagecounts_201602
, pagecounts_201603
, etc.
Exemple 1: LookML qui dépend de always_filter
La table dérivée ci-dessous utilise TABLE_QUERY([dataset], [expr])
pour obtenir l'ensemble de tables approprié à interroger:
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 } }
Remarques sur le code de l'expression:
-
table_id
fait référence au nom de la table dans l'ensemble de données. -
length(table_id) = 17
permet d'ignorer les autres tables portant des noms tels quepagecounts_201407_en_top64k
. -
STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m')
affiche uniquement la partieYYYYmm
de la date de début.
Remplacement de NULL
par les éléments date_filter
. Pour contourner ce problème, vous devez disposer d'un always_filter
dans l'exploration:
explore: pagecounts { always_filter: { filters: [date_filter: "2 months ago"] } }
Notez que cette opération échouera toujours pour les filtres portant sur des dates antérieures à la date la plus proche dans l'ensemble de données, car {% date_start date_filter %}
renvoie la valeur NULL
.
Exemple 2: code LookML qui ne dépend pas de always_filter
Il est également possible d'utiliser COALESCE
ou IFNULL
pour encoder un ensemble de tables par défaut à interroger. Dans l'exemple ci-dessous, les deux derniers mois sont utilisés:
-
Limite inférieure:
COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
-
Limite supérieure:
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 } }
Les fichiers journaux sont exprimés en UTC lorsqu'ils effectuent des requêtes dans les fuseaux horaires américains (dans BigQuery)
Parfois, vos fichiers journaux Looker sont stockés en UTC, même si vous effectuez des requêtes dans le fuseau horaire de l'Est ou du Pacifique. Ce problème peut entraîner le déploiement des fichiers journaux à la date de demain
dans le fuseau horaire local de la requête, ce qui donne lieu à des données manquantes.
La solution consiste à ajouter un jour supplémentaire à la date de fin du filtre de date, afin que les entrées de journal soient récupérées lorsque l'heure est passée à minuit (UTC).
Les exemples ci-dessous utilisent l'ensemble de données public [githubarchive:day]
, qui possède une partition quotidienne des informations GitHub.
Exemple 1: LookML qui dépend de 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 } }
Comme ce code SQL échouera si NULL
est remplacé par les dates, il est nécessaire d'ajouter un always_filter
à l'exploration:
explore: githubarchive { always_filter: { filters: [date_filter: "2 days ago"] } }
Exemple 2: code LookML qui ne dépend pas de always_filter
Dans cet exemple, la plage de dates par défaut est encodée en code LookML. Comme COALESCE
renvoyait un type unknown
, j'ai finalement dû utiliser IFNULL
pour que SQL fonctionne.
-
Limite inférieure:
IFNULL({% date_start date_filter %},CURRENT_DATE())
-
Limite supérieure:
IFNULL({% date_end date_filter %},CURRENT_DATE())
+ 1 jour
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 } }
Fonctions de fenêtrage sur N jours de fin (dans BigQuery)
Lors de l’exécution de certaines analyses, les calculs sont attendus sous une forme agrégée sur une période historique. Pour effectuer cette opération en SQL, il convient généralement d'implémenter un fenêtrage qui récupère un nombre de lignes de n
pour une table unique par date. Cependant, il existe un piège 22 lorsque vous utilisez une table partitionnée par date : vous devez d'abord dicter l'ensemble de tables sur lequel la requête sera exécutée, même si la requête a réellement besoin de tables historiques supplémentaires pour les calculs.
Solution:indiquez que la date de début est antérieure aux dates fournies dans le filtre de date. Voici un exemple de renvoi d'une semaine supplémentaire:
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 } }
L'instruction SELECT
supplémentaire est nécessaire, car elle fournit une contrainte WHERE
pour réduire l'ensemble de résultats à la plage de dates initialement spécifiée par l'utilisateur dans la requête.
Table partitionnée par date via une chaîne au format "AAAA-MM-JJ" (dans Presto)
Dans les tables Hadoop, il est courant d'utiliser des colonnes partitionnées afin d'accélérer les temps de recherche dans les colonnes fréquemment recherchées, en particulier les dates. Le format des colonnes de dates peut être arbitraire, bien que YYYY-MM-DD
et YYYYMMDD
soient les plus courants. Le type de la colonne de date peut être une chaîne, une date ou un nombre.
Dans cet exemple, une table Hive table_part_by_yyyy_mm_dd
comporte une colonne partitionnée dt
, une chaîne au format YYYY-MM-DD
, qui est recherchée par Presto.
Lorsque le générateur est exécuté pour la première fois, le code LookML se présente comme suit:
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 ;; } }
Remarques sur le code dans les expressions des deux exemples suivants:
-
La sortie de
date_start
etdate_end
esttype: timestamp
. -
date_format( <expr>, '%Y-%m-%d')
permet de convertir l'horodatage en chaîne et au format approprié. -
coalesce
permet de gérer le cas des valeurs NULL si un utilisateur saisit un filtre tel quebefore 2010-01-01
ouafter 2012-12-31
. -
Il s'agit du code du dialecte Presto. Hive aura donc des différences dans la chaîne de format (
yyyy-MM-dd
) etdate_format
ne peut pas accepter une valeur NULL, donccoalesce
devrait se déplacer à cet endroit avec une sorte de valeur par défaut.
Exemple 1: LookML qui utilise une expression de table commune pour filtrer la table
Cet exemple utilise une table dérivée pour filtrer la table.
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 ;; } }
En général, l'analyse complète des tables partitionnées prend trop de temps (et cela consomme trop de ressources de cluster). Nous vous recommandons donc de placer un filtre par défaut dans l'exploration pour cette vue également:
explore: table_part_by_yyyy_mm_dd { always_filter: { filters: [date_filter: "2013-01"] } }
Exemple 2: code LookML qui filtre directement dans le prédicat
Cet exemple effectue le filtrage de prédicat directement sur la table, sans sous-requête ni expression de table commune.
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 ;; } }
Nous pouvons vérifier que les partitions de table sont effectivement utilisées en vérifiant la sortie de EXPLAIN
dans l'exécuteur SQL pour une requête générée par ce code LookML (vous pouvez y accéder en cliquant sur la section SQL dans l'onglet "Données" de la page d'exploration). Vous verrez quelque chose comme ceci:
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]]
Le champ partitionKey=true
, ainsi que la plage de clés de partition listées, indiquent que seules les colonnes partitionnées sont analysées.