Puedes utilizar los filtros con plantillas para hacer referencia a las fechas. Para ello, selecciona las fechas de inicio y finalización en un filtro de fechas: {% date_start date_filter %}
y {% date_end date_filter %}
, respectivamente. En esta página, se mostrarán 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
yTABLE_QUERY
, por lo que usar{% table_date_range prefix date_filter %}
no es suficiente para especificar filtros de fecha. -
Hadoop permite trabajar con columnas con particiones por fecha, sin importar el tipo (
string
,date
) o el formato (YYYY-MM-DD
) de la columna.
Notas de uso
-
Cuando no hay un valor especificado para
date_filter
, tanto{% date_start date_filter %}
como{% date_end date_filter %}
se evaluarán comoNULL
. -
En el caso de
date_filter
abierto (comobefore 2016-01-01
oafter 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 de uso
Columnas con particiones por mes (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 año/mes como sufijo. Un ejemplo de esto es en el conjunto de datos público [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
La siguiente tabla derivada usa TABLE_QUERY([dataset], [expr])
para 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
hace referencia al nombre de la tabla en el conjunto de datos. -
length(table_id) = 17
se asegura de que ignore las otras tablas con nombres comopagecounts_201407_en_top64k
. -
STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m')
mostrará solo la parteYYYYmm
de la fecha de inicio.
Se reemplazará NULL
por las partes date_filter
. Para solucionar este problema, debes tener un always_filter
en Explorar:
explore: pagecounts { always_filter: { filters: [date_filter: "2 months ago"] } }
Ten en cuenta que este proceso fallará para 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 es posible usar COALESCE
o IFNULL
a fin de codificar un conjunto predeterminado de tablas para consultar. En el siguiente ejemplo, se utilizan los últimos dos meses:
-
Límite inferior:
COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
-
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 estadounidenses (en BigQuery).
A veces, los archivos de registro de Looker se almacenan en UTC, aunque realices consultas en las zonas horarias del este o del Pacífico. lo que puede causar un problema en el que los archivos de registro ya se lanzaron hasta la fecha de mañana
en la zona horaria local de la consulta, lo que genera que se pierdan algunos datos.
La solución es agregar un día adicional a la fecha de finalización del filtro de fechas para garantizar que, si es pasada la medianoche UTC, se recopilen esas entradas de registro.
En los siguientes ejemplos, se usa el conjunto de datos público [githubarchive:day]
, que tiene una partición diaria de la 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 sustituye NULL
por las fechas, es necesario agregar una always_filter
a la función 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. Debido a que COALESCE
mostraba un tipo unknown
, en última instancia tuve que usar IFNULL
para que SQL funcionara.
-
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 N días finales (en BigQuery)
Cuando se realizan ciertos análisis, se espera que los cálculos sean de forma agregada durante un período histórico. Para realizar esta operación en SQL, por lo general, se implementará una función analítica que alcanza la cantidad de filas de n
en una tabla única por fecha. Sin embargo, existe un catch-22 cuando se usa una tabla particionada por fecha: primero se debe establecer el conjunto de tablas en las que se ejecutará la consulta, incluso si esta realmente necesita tablas históricas adicionales para el procesamiento.
La solución: Permite que la fecha de inicio sea anterior a las fechas proporcionadas en el filtro de fechas. Este es un ejemplo de cómo volver a comunicarse 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 } }
Se necesita la sentencia SELECT
adicional porque proporciona una restricción WHERE
para recortar el conjunto de resultados hasta el período que el usuario especificó originalmente en la consulta.
Tabla particionada por fecha mediante una string con el formato “AAAA-MM-DD” (en Presto)
Es un patrón común en las tablas de Hadoop usar columnas de partición 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 comunes. El tipo de 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 particionada dt
, una string con formato YYYY-MM-DD
, que Presto está buscando.
Cuando se ejecuta el generador por primera vez, LookML tiene el siguiente 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 en las expresiones de ambos ejemplos a continuación:
-
El resultado de
date_start
ydate_end
estype: timestamp
. -
date_format( <expr>, '%Y-%m-%d')
se usa para convertir la marca de tiempo en una string y al formato correcto. -
coalesce
controla el caso de NULL si alguien escribe un filtro comobefore 2010-01-01
oafter 2012-12-31
. -
Este es un código de dialecto de Presto, por lo que Hive tendrá algunas diferencias en la string de formato (
yyyy-MM-dd
) ydate_format
no puede tomar un valor NULL, por lo quecoalesce
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 filtrarla.
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 de partición tardan demasiado en realizar análisis completos de tablas (y consumen demasiados recursos de clúster), por lo que también se recomienda 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 en el predicado
En este ejemplo, el predicado filtra 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 ;; } }
Podemos validar que las particiones de la tabla se están usando realmente verificando el resultado de EXPLAIN
en el Ejecutor de SQL en busca de 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 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 rango de claves de partición indicadas indican que solo se analizan esas columnas con particiones.