Como usar date_start e date_end com filtros de data

É possível usar filtros com modelo para consultar datas selecionando as datas de início e término em um filtro: {% date_start date_filter %} e {% date_end date_filter %}, respectivamente. Esta página guiará você por alguns exemplos de casos de uso e as etapas para realizá-los.

Notas de sintaxe

A sintaxe a seguir funciona com a maioria dos dialetos, mas alguns têm casos de uso específicos. Exemplo:

  • O BigQuery permite um controle detalhado ao trabalhar com funções de caractere curinga de tabela, como TABLE_DATE_RANGE e TABLE_QUERY. Portanto, usar {% table_date_range prefix date_filter %} é insuficiente para especificar filtros de data.
  • O Hadoop permite trabalhar com colunas particionadas por data, não importa o tipo (string, date) ou o formato (YYYY-MM-DD) da coluna.

Observações sobre uso

  • Quando não há um valor especificado para date_filter, {% date_start date_filter %} e {% date_end date_filter %} são avaliados como NULL.
  • No caso de uma date_filter aberta (como before 2016-01-01 ou after 2016-01-01), um dos filtros {% date_start date_filter %} ou {% date_end date_filter %} será NULL.

    • Para garantir que nenhum desses dois casos resulte em SQL inválido, use IFNULL ou COALESCE no LookML.

Exemplos de casos de uso

Colunas particionadas por mês (no BigQuery)

Em alguns conjuntos de dados do BigQuery, as tabelas são organizadas por mês, e o ID da tabela tem a combinação ano/mês como sufixo. Um exemplo disso está no conjunto de dados público [fh-bigquery:wikipedia] da Wikipédia do BigQuery, que tem tabelas com os nomes pagecounts_201601, pagecounts_201602, pagecounts_201603 e assim por diante.

Exemplo 1: LookML que depende de always_filter

A tabela derivada a seguir usa TABLE_QUERY([dataset], [expr]) para receber o conjunto certo de tabelas a serem consultadas: 

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

Algumas observações sobre o código na expressão:

  • table_id refere-se ao nome da tabela no conjunto de dados.
  • length(table_id) = 17 garante que as outras tabelas com nomes como pagecounts_201407_en_top64k sejam ignoradas.
  • STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') gerará apenas a parte YYYYmm da data de início.

NULL será substituído pelas partes date_filter. Para contornar esse problema, é necessário usar uma always_filter na seção "Explorar": 

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

Observe que isso ainda falhará para filtros de datas anteriores à data mais antiga no conjunto de dados porque {% date_start date_filter %} será avaliado como NULL.

Exemplo 2: LookML que não depende de always_filter

Também é possível usar COALESCE ou IFNULL para codificar um conjunto padrão de tabelas para consulta. No exemplo a seguir, os últimos dois meses são usados:

  • O limite inferior: COALESCE({% date_start date_filter %},DATE_ADD(CURRENT_TIMESTAMP(),-2,'MONTH'))
  • O limite 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
  }
}

Os arquivos de registros estão em UTC ao fazer consultas em fusos horários dos Estados Unidos (no BigQuery)

Às vezes, os arquivos de registro do Looker são armazenados em UTC, mesmo que você esteja fazendo consultas nos fusos horários Leste ou Pacífico. Isso pode causar um problema em que os arquivos de registro já foram transferidos para a data amanhã no fuso horário local da consulta, resultando em alguns dados perdidos.

A solução é adicionar um dia extra à data de término do filtro de data para garantir que, caso passe da meia-noite UTC, essas entradas de registro sejam coletadas.

Os exemplos a seguir usam o conjunto de dados público [githubarchive:day], que tem uma partição diária de informações do GitHub.

Exemplo 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 esse SQL vai falhar se NULL for substituído pelas datas, é necessário adicionar um always_filter à Análise detalhada: 

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

Exemplo 2: LookML que não depende de always_filter

Neste exemplo, o período padrão é codificado no LookML. Como COALESCE estava retornando um tipo unknown, tive que usar IFNULL para fazer o SQL funcionar.

  • O limite inferior: IFNULL({% date_start date_filter %},CURRENT_DATE())
  • Limite superior: IFNULL({% date_end date_filter %},CURRENT_DATE()) + 1 dia 
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
  }
}

Funções da janela de N dias finais (no BigQuery)

Ao realizar certas análises, os cálculos são esperados de alguma forma agregada ao longo de um período de tempo histórico. Para realizar essa operação no SQL, normalmente é implementada uma função de janela que alcança o número n de linhas de uma tabela exclusiva por data. No entanto, há um problema ao usar uma tabela particionada por data: primeiro é preciso definir o conjunto de tabelas em que a consulta será executada, mesmo que a consulta realmente precise de tabelas históricas extras para computação.

A solução: permitir que a data de início seja anterior às datas fornecidas no filtro de data. Confira um exemplo de retorno de mais uma 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
  }
}

A instrução SELECT extra é necessária porque fornece uma restrição WHERE para reduzir o conjunto de resultados ao período que o usuário especificou originalmente na consulta.

Tabela particionada por data usando string com o formato 'AAAA-MM-DD' (no Presto)

É um padrão comum nas tabelas do Hadoop usar colunas particionadas para acelerar os tempos de pesquisa em colunas que são pesquisadas com frequência, principalmente datas. O formato das colunas de data pode ser arbitrário, embora YYYY-MM-DD e YYYYMMDD sejam os mais comuns. O tipo da coluna de data pode ser string, data ou número.

Neste exemplo, uma tabela do Hive table_part_by_yyyy_mm_dd tem uma coluna particionada dt, uma string formatada YYYY-MM-DD, que está sendo pesquisada pelo Presto.

Quando o gerador é executado pela primeira vez, o LookML fica assim: 

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

Algumas observações sobre o código nas expressões em ambos os exemplos a seguir:

  • A saída de date_start e date_end é type: timestamp.
  • date_format( <expr>, '%Y-%m-%d') é usado para converter o carimbo de data/hora em uma string e no formato correto.
  • O coalesce processa o caso de NULLs se alguém digitar um filtro como before 2010-01-01 ou after 2012-12-31.
  • Esse é um código de dialeto Presto, então o Hive terá algumas diferenças na string de formato (yyyy-MM-dd), e date_format não pode receber um valor NULL. Portanto, o coalesce precisa ser movido para lá com algum tipo de valor padrão.

Exemplo 1: LookML que usa uma expressão de tabela comum para filtrá-la

Neste exemplo, usamos uma tabela derivada para filtrá-la.

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, as tabelas particionadas demoram muito para fazer varreduras de tabela completas (e consomem muitos recursos do cluster). Por isso, é recomendável colocar um filtro padrão no recurso Explorar para essa visualização: 

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

Exemplo 2: LookML que filtra diretamente no predicado

Este exemplo faz a filtragem de predicado diretamente na tabela, sem uma subconsulta ou expressão de tabela comum. 

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

É possível validar se as partições de tabela estão sendo usadas, verificando a saída de EXPLAIN no SQL Runner para uma consulta gerada por esse LookML. Para acessar, clique na seção SQL na guia "Dados" da página "Análise". Você vai encontrar algo como isto: 

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

A partitionKey=true e o intervalo de chaves de partição listadas indicam que ele está verificando apenas essas colunas particionadas.