Usar date_start y date_end con filtros de fecha

Puedes usar filtros basados en plantillas para hacer referencia a fechas seleccionando 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 describen algunos ejemplos de casos prácticos y los pasos para llevarlos a cabo.

Notas sobre la sintaxis

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

  • BigQuery permite un mayor control al trabajar con funciones comodín de tabla, como TABLE_DATE_RANGE y TABLE_QUERY, por lo que no basta con usar {% table_date_range prefix date_filter %} para especificar filtros de fecha.
  • Hadoop permite trabajar con columnas particionadas por fecha, independientemente del tipo (string o date) o del formato (YYYY-MM-DD) de la columna.

Notas de uso

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

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

Ejemplos de uso

Columnas con particiones mensuales (en BigQuery)

En algunos conjuntos de datos de BigQuery, las tablas se organizan por meses y el ID de tabla tiene la combinación de año y mes como sufijo. Un ejemplo de esto se puede ver en el siguiente conjunto de datos, que tiene muchas tablas con nombres como pagecounts_201601, pagecounts_201602 y pagecounts_201603.

Ejemplo 1: LookML que depende de always_filter

La siguiente tabla derivada usa TABLE_QUERY([dataset], [expr]) para obtener el conjunto de tablas correcto que se va a 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 de la expresión:

  • table_id hace referencia al nombre de la tabla del conjunto de datos.
  • length(table_id) = 17 se asegura de que se ignoren las demás 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.

NULL se sustituirá por las piezas date_filter. Para evitarlo, debes hacer lo siguiente en Explorar:always_filter 

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

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

Ejemplo 2: LookML que no depende de always_filter

También se pueden usar COALESCE o IFNULL para codificar un conjunto predeterminado de tablas que consultar. En el siguiente ejemplo, se usan los dos últimos 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 consultan en zonas horarias americanas (en BigQuery)

A veces, los archivos de registro de Looker se almacenan en UTC, aunque las consultas se hagan en las zonas horarias del este o del Pacífico. Este problema puede provocar que los archivos de registro ya se hayan cambiado a la fecha de mañana en la zona horaria local de la consulta, lo que provoca que se pierdan algunos datos.

La solución es añadir un día adicional a la fecha de finalización del filtro de fecha para asegurarse de que, si es después de medianoche UTC, se recojan esas entradas de registro.

En los ejemplos que se muestran a continuación, se usa el conjunto de datos público [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
  }
}

Como este SQL fallará si se sustituyen las fechas por NULL, es necesario añadir 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 periodo predeterminado está codificado en LookML. Como COALESCE devolvía un tipo unknown, al final tuve que usar IFNULL para que funcionara la consulta SQL.

  • 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 de ventana de los últimos N días (en BigQuery)

Al realizar determinados análisis, se espera que los cálculos se hagan de forma agregada en un periodo determinado. Para llevar a cabo esta operación en SQL, normalmente se implementa una función de ventana que retrocede n filas en una tabla única por fecha. Sin embargo, hay un dilema cuando se usa una tabla particionada por fecha: primero se debe indicar el conjunto de tablas en las que se ejecutará la consulta, aunque la consulta realmente necesite tablas históricas adicionales para realizar los cálculos.

Solución: Permite que la fecha de inicio sea anterior a las fechas proporcionadas en el filtro de fechas. Aquí tienes un ejemplo que se remonta una semana más: 

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 instrucción SELECT adicional es necesaria porque proporciona una restricción WHERE para reducir el conjunto de resultados al intervalo de fechas que el usuario especificó originalmente en la consulta.

Tabla particionada por fecha mediante una cadena con el formato "AAAA-MM-DD" (en Presto)

Es habitual en las tablas de Hadoop usar columnas particionadas para acelerar los tiempos de búsqueda de las columnas 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 habituales. El tipo de la columna de fecha puede ser cadena, fecha o número.

En este ejemplo, una tabla de Hive table_part_by_yyyy_mm_dd tiene una columna con particiones dt, una cadena con formato YYYY-MM-DD, que Presto está buscando.

Cuando se ejecuta el generador por primera vez, el LookML tiene este aspecto: 

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 de las expresiones de los dos ejemplos siguientes:

  • 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 cadena y en el formato correcto.
  • El coalesce se usa para gestionar los valores NULL si alguien escribe un filtro como before 2010-01-01 o after 2012-12-31.
  • Se trata de código del dialecto de 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 ahí con algún tipo de valor predeterminado.

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

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

Normalmente, las tablas particionadas tardan demasiado en realizar análisis completos de la tabla (y consumen demasiados recursos del clúster), por lo que es recomendable aplicar un filtro predeterminado a la exploración de esta vista: 

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

Ejemplo 2: LookML que filtra directamente en el predicado

En este ejemplo, el filtrado de predicados se realiza directamente en la tabla, sin una subconsulta ni 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 ;;
  }
}

Para comprobar que las particiones de la tabla se están usando, consulta el resultado de EXPLAIN en SQL Runner para una consulta generada por este LookML (puedes acceder a él haciendo clic en la sección SQL de la pestaña Datos de la página Explorar). Verás algo parecido a lo siguiente: 

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 junto con el intervalo de claves de partición que se muestra indica que solo se están analizando esas columnas particionadas.