Utiliser les variables date_start et date_end avec des filtres de date

Vous pouvez utiliser des filtres basés sur des modèles 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 vous présente quelques exemples de cas d'utilisation et les étapes à suivre pour les réaliser.

Remarques sur la syntaxe

• BigQuery permet un contrôle plus précis lorsque vous utilisez des fonctions 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, quel que soit leur type (string, date) ou leur format (YYYY-MM-DD).

Remarques sur l'utilisation

• Lorsqu'aucune valeur n'est spécifiée pour date_filter, {% date_start date_filter %} et {% date_end date_filter %} sont évalués à NULL.
• Dans le cas d'un date_filter ouvert (tel que 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 n'entraîne un code SQL non valide, vous pouvez utiliser IFNULL ou COALESCE dans LookML.

Exemples de cas d'utilisation

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 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 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 d'ignorer les autres tables portant des noms tels que pagecounts_201407_en_top64k.
• STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') n'affichera que la partie YYYYmm de la date de début.

NULL sera remplacé par les pièces date_filter. Pour contourner ce problème, vous devez ajouter un always_filter à l'exploration :

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

Notez que cela échouera toujours pour les filtres de dates antérieures à la date la plus ancienne de l'ensemble de données, car {% date_start date_filter %} sera évalué à 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 de tables par défaut à interroger. Dans l'exemple suivant, 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
  }
}

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

Étant donné que 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 : LookML qui ne dépend pas de always_filter

Dans cet exemple, la plage de dates par défaut est encodée dans le LookML. Comme COALESCE renvoyait un type unknown, j'ai finalement dû utiliser IFNULL pour que le code 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
  }
}

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

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 temps de recherche des colonnes fréquemment recherchées, en particulier les dates. Le format des colonnes de date peut être arbitraire, mais YYYY-MM-DD et YYYYMMDD sont les plus courants. Le type de la colonne de date peut être "string", "date" ou "number".

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 ;;
  }
}

Voici quelques remarques sur le code des expressions dans les deux exemples suivants :

• Le résultat de date_start et date_end est type: timestamp.
• date_format( <expr>, '%Y-%m-%d') est utilisé pour convertir le code temporel en chaîne et au bon format.
• coalesce permet de gérer les valeurs NULL si un utilisateur saisit un filtre tel que before 2010-01-01 ou after 2012-12-31.
• Il s'agit d'un code de dialecte Presto. Par conséquent, Hive présentera quelques différences dans la chaîne de format (yyyy-MM-dd). De plus, date_format ne peut pas prendre de valeur NULL. Par conséquent, coalesce devra y être déplacé 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 règle générale, les analyses complètes de tables partitionnées prennent trop de temps (et consomment trop de ressources de cluster). Il est donc judicieux de placer également un filtre par défaut sur l'onglet "Explorer" de cette vue :

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

Exemple 2 : LookML qui filtre directement dans 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 ;;
  }
}

Pour vérifier que les partitions de table sont réellement utilisées, consultez le résultat 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 de l'onglet "Données" de la page "Explorer"). 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 partitionKey=true et la plage de clés de partition listée indiquent que seules ces colonnes partitionnées sont analysées.