Cómo usar date_start y date_end con filtros de fecha

Puedes usar los filtros con plantillas para hacer referencia a las fechas. Para ello, selecciona las fechas de inicio y finalización en un filtro de fecha ({% date_start date_filter %} y {% date_end date_filter %}, respectivamente). En esta página, se explican algunos ejemplos de casos de uso y los pasos para lograrlos.

Notas de sintaxis

La siguiente sintaxis funciona con la mayoría de los dialectos, pero algunos tienen casos de uso específicos. Ejemplo:

  • BigQuery permite un control detallado cuando se trabaja con funciones comodín de tablas, como TABLE_DATE_RANGE y TABLE_QUERY, por lo que usar {% table_date_range prefix date_filter %} no es suficiente para especificar filtros de fecha.
  • Hadoop permite trabajar con columnas particionadas por fecha, sin importar el tipo (string, date) o el formato (YYYY-MM-DD) de la columna.

Notas de uso

  • Si no se especifica un valor para date_filter, {% date_start date_filter %} y {% date_end date_filter %} se evaluarán como NULL.
  • En el caso de una date_filter abierta (como before 2016-01-01 o after 2016-01-01), uno de los filtros {% date_start date_filter %} o {% date_end date_filter %} será NULL.

    • Para asegurarte de que ninguno de estos dos casos genere un SQL no válido, puedes usar IFNULL o COALESCE en LookML.

Ejemplos de casos prácticos

Columnas particionadas mensuales (en BigQuery)

En algunos conjuntos de datos de BigQuery, las tablas se organizan por mes y el ID de la tabla tiene la combinación de año y mes como sufijo. Un ejemplo de esto es el conjunto de datos públicos [fh-bigquery:wikipedia] de Wikipedia de BigQuery, que tiene tablas llamadas pagecounts_201601, pagecounts_201602, pagecounts_201603, etcétera.

Ejemplo 1: LookML que depende de always_filter

En la siguiente tabla derivada, se usa TABLE_QUERY([dataset], [expr]) a fin de obtener el conjunto correcto de tablas para consultar: 

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

Algunas notas sobre el código en la expresión:

  • table_id se refiere al nombre de la tabla en el conjunto de datos.
  • length(table_id) = 17 se asegura de ignorar las otras tablas con nombres como pagecounts_201407_en_top64k.
  • STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') mostrará solo la parte YYYYmm de la fecha de inicio.

Se sustituirá NULL por las partes date_filter. Para evitar esto, debes tener un always_filter en Explorar: 

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

Ten en cuenta que esto seguirá fallando para los filtros de fechas anteriores a la fecha más antigua del conjunto de datos porque {% date_start date_filter %} se evaluará como NULL.

Ejemplo 2: LookML que no depende de always_filter

También es posible usar COALESCE o IFNULL para codificar un conjunto predeterminado de tablas para realizar consultas. En el siguiente ejemplo, se usan los últimos dos meses:

  • El límite inferior: COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
  • El límite superior: 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
  }
}

Los archivos de registro están en UTC cuando se realizan consultas en zonas horarias de Estados Unidos (en BigQuery).

A veces, tus archivos de registro de Looker se almacenan en UTC, aunque realices consultas en zonas horarias del este o del Pacífico. Esto puede causar problemas en los que los archivos de registro ya se hayan lanzado a la fecha de mañana en la zona horaria local de la consulta, lo que puede causar que se pierdan algunos datos.

La solución es agregar un día adicional a la fecha de finalización del filtro de fecha para asegurarse de que, si es más allá de la medianoche UTC, se captan esas entradas de registro.

En los siguientes ejemplos, se usa el conjunto de datos públicos [githubarchive:day], que tiene una partición diaria de información de GitHub.

Ejemplo 1: LookML que depende 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
  }
}

Debido a que este SQL fallará si se sustituyen NULL por las fechas, es necesario agregar un always_filter a Explorar: 

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

Ejemplo 2: LookML que no depende de always_filter

En este ejemplo, el período predeterminado está codificado en LookML. Como COALESCE mostraba un tipo unknown, al final tuve que usar IFNULL para que SQL funcionara.

  • El límite inferior: IFNULL({% date_start date_filter %},CURRENT_DATE())
  • Límite superior: IFNULL({% date_end date_filter %},CURRENT_DATE()) + 1 día 
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
  }
}

Funciones analíticas de N días finales (en BigQuery)

Cuando se realizan ciertos análisis, se espera que los cálculos sean de alguna forma agregada durante un período histórico. Para realizar esta operación en SQL, por lo general, se implementa una función analítica que recupera n cantidad de filas en una tabla única por fecha. Sin embargo, cuando se usa una tabla particionada por fecha, existe un catch-22: primero se debe dictar el conjunto de tablas con el que se ejecutará la consulta, incluso si esta necesita tablas históricas adicionales para el procesamiento.

Cómo solucionarlo: Permite que la fecha de inicio sea anterior a las fechas indicadas en el filtro de fechas. Este es un ejemplo de un período anterior de una semana: 

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

La sentencia SELECT adicional es necesaria porque proporciona una restricción WHERE para volver a reducir el conjunto de resultados al período que el usuario especificó originalmente en la consulta.

Tabla particionada por fecha a través de una cadena con formato “AAAA-MM-DD” (en Presto)

Es un patrón común en las tablas de Hadoop usar columnas particionadas para acelerar los tiempos de búsqueda de las columnas en las que se buscan con frecuencia, especialmente las fechas. El formato de las columnas de fecha puede ser arbitrario, aunque YYYY-MM-DD y YYYYMMDD son los más comunes. El tipo de la columna de fecha puede ser una cadena, una fecha o un número.

En este ejemplo, una tabla de Hive table_part_by_yyyy_mm_dd tiene una columna particionada dt, una string con formato YYYY-MM-DD, que Presto busca.

Cuando se ejecuta el generador por primera vez, LookML se ve de la siguiente manera: 

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

Algunas notas sobre el código en las expresiones de ambos ejemplos:

  • El resultado de date_start y date_end es type: timestamp.
  • date_format( <expr>, '%Y-%m-%d') se usa para convertir la marca de tiempo en una string y en el formato correcto.
  • coalesce sirve para manejar las mayúsculas y minúsculas de los valores NULL si alguien escribe un filtro como before 2010-01-01 o after 2012-12-31.
  • Este es el código de dialecto Presto, por lo que Hive tendrá algunas diferencias en la cadena de formato (yyyy-MM-dd) y date_format no puede tomar un valor NULL, por lo que coalesce tendría que moverse allí con algún tipo de valor predeterminado.

Ejemplo 1: LookML que usa una expresión de tabla común para filtrarla

En este ejemplo, se usa una tabla derivada para filtrar la tabla.

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

Por lo general, las tablas particionadas tardan demasiado en analizar la tabla completa (y consumen demasiados recursos de clúster), por lo que también es una buena idea colocar un filtro predeterminado en Explorar para esta vista: 

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

Ejemplo 2: LookML que filtra directamente el predicado

En este ejemplo, se filtra el predicado directamente en la tabla, sin una subconsulta o una expresión de tabla común. 

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

Podemos validar que las particiones de la tabla realmente se estén usando verificando el resultado de EXPLAIN en el Ejecutor de SQL para una consulta generada por este LookML (puedes acceder a ella haciendo clic en la sección SQL en la pestaña Datos de la página Explorar). Verás algo como esto: 

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

El partitionKey=true y el rango de claves de partición que se indican indican que solo se analizan esas columnas particionadas.