Utiliser date_start et date_end avec des filtres de date

Vous pouvez utiliser des filtres modélisés pour faire référence à des 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 de cas d'utilisation et les étapes à suivre pour les mettre en œuvre.

Remarques sur la syntaxe

La syntaxe suivante fonctionne avec la plupart des dialectes, mais certains dialectes ont des cas d'utilisation spécifiques. Exemple :

• BigQuery permet un contrôle précis lorsque vous utilisez des fonctions de caractères génériques de table telles que TABLE_DATE_RANGE et TABLE_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 de travailler avec des colonnes partitionnées par date, quels que soient 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 %} sont tous deux évalués sur NULL.
• Dans le cas d'une date_filter ouverte (comme before 2016-01-01 ou after 2016-01-01), l'un des filtres {% date_start date_filter %} ou {% date_end date_filter %} sera NULL.

Pour vous assurer qu'aucun de ces deux cas ne génère 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 table a la combinaison année/mois en tant que suffixe. L'ensemble de données [fh-bigquery:wikipedia] public BigQuery Wikipédia en est un 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 suivante utilise TABLE_QUERY([dataset], [expr]) pour obtenir l'ensemble de tables à 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 dans l'expression:

• table_id fait référence au nom de la table dans l'ensemble de données.
• length(table_id) = 17 s'assure qu'il ignore les autres tables portant des noms tels que pagecounts_201407_en_top64k.
• STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') ne affichera que la partie YYYYmm de la date de début.

NULL sera remplacé par 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 NULL.

Exemple 2: LookML qui ne dépend pas de always_filter

Il est également possible d'utiliser COALESCE ou IFNULL pour encoder un ensemble par défaut de tables à interroger. Dans l'exemple suivant, les deux derniers mois sont utilisés:

• La limite inférieure: COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
• La 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 temps UTC pour les requêtes effectuées 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 un report des fichiers journaux jusqu'à la date de demain dans le fuseau horaire local de la requête, entraînant ainsi des données manquantes.

La solution consiste à ajouter un jour supplémentaire à la date de fin du filtre de date, pour que les entrées de journal soient récupérées après minuit UTC.

Les exemples suivants utilisent l'ensemble de données public [githubarchive:day], qui comporte une partition quotidienne d'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 échoue si les dates sont remplacées par NULL, il est nécessaire d'ajouter un always_filter à l'exploration:

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

Exemple 2: LookML qui ne dépend pas de always_filter

Dans cet exemple, la plage de dates par défaut est encodée dans le code LookML. Comme COALESCE renvoyait un type unknown, j'ai finalement dû utiliser IFNULL pour que le SQL fonctionne.

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

Fenêtrages de N jours consécutifs (dans BigQuery)

Lorsque vous effectuez certaines analyses, les calculs sont attendus sous une certaine forme agrégée sur une période historique. Pour effectuer cette opération en SQL, il faut généralement implémenter un fenêtrage qui récupère un nombre de n lignes pour une table unique par date. Cependant, il existe un "catch-22" lors de l'utilisation d'une table partitionnée par date : il faut d'abord dicter l'ensemble de tables sur lequel la requête sera exécutée, même si elle nécessite réellement des tables historiques supplémentaires pour le calcul.

Solution:faites en sorte que la date de début soit antérieure à celles indiquées dans le filtre de date. Voici un exemple de retour 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 pour accélérer les recherches dans les colonnes qui font l'objet de recherches fréquentes, en particulier les dates. Le format des colonnes de date peut être arbitraire, bien que YYYY-MM-DD et YYYYMMDD soient les plus courants. Le type de la colonne de date peut être chaîne, date ou nombre.

Dans cet exemple, une table Hive table_part_by_yyyy_mm_dd comporte une colonne partitionnée dt, une chaîne formatée YYYY-MM-DD, qui est recherchée par Presto.

Lorsque vous exécutez le générateur 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 ;;
  }
}

Quelques remarques sur le code des expressions figurant dans les deux exemples suivants:

• Le résultat de date_start et date_end est type: timestamp.
• date_format( <expr>, '%Y-%m-%d') permet de convertir l'horodatage en chaîne au format approprié.
• coalesce permet de gérer le cas des valeurs NULL si quelqu'un saisit un filtre tel que before 2010-01-01 ou after 2012-12-31.
• Il s'agit du code du dialecte Presto. Hive possède donc des différences au niveau de la chaîne de format (yyyy-MM-dd) et date_format ne peut pas accepter une valeur NULL. coalesce devrait donc s'y déplacer avec une sorte de valeur par défaut.

Exemple 1: LookML utilisant 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, les tables partitionnées prennent trop de temps pour effectuer une analyse complète des tables (et consomment trop de ressources de cluster). Il est donc recommandé de définir également un filtre par défaut sur l'exploration pour cette vue:

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

Exemple 2: LookML qui filtre directement le prédicat

Cet exemple effectue le filtrage du 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 la table sont bien utilisées en vérifiant la sortie de EXPLAIN dans SQL Runner pour une requête générée par ce LookML (vous pouvez y accéder en cliquant sur la section SQL dans l'onglet Données de la page Exploration). Vous verrez alors ce qui suit:

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 partitionKey=true et la plage de clés de partitionnement répertoriées indiquent qu'il n'analyse que ces colonnes partitionnées.