Como usar date_start e date_end com filtros de data

É possível usar filtros com modelo para se referir a datas selecionando as datas de início e término em um filtro de data: {% 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 necessárias para realizá-los.

Observações de sintaxe

A sintaxe a seguir funciona com a maioria dos dialetos, mas alguns dialetos 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 %} não é suficiente 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 houver um valor especificado para date_filter, {% date_start date_filter %} e {% date_end date_filter %} serã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 mensais (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 um sufixo. Um exemplo disso é o conjunto de dados público [fh-bigquery:wikipedia] da Wikipédia do BigQuery, que tem tabelas chamadas pagecounts_201601, pagecounts_201602, pagecounts_201603 e assim por diante.

Exemplo 1: LookML que depende de always_filter

A tabela derivada abaixo usa TABLE_QUERY([dataset], [expr]) para conseguir o conjunto certo de tabelas para consulta:

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 ignora as outras tabelas com nomes como pagecounts_201407_en_top64k.
• STRFTIME_UTC_USEC({% date_start date_filter %},'%Y%m') produzirá apenas a parte YYYYmm da data de início.

NULL será substituído pelas partes de date_filter. Para contornar esse problema, é preciso ter um always_filter na Análise:

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 abaixo, 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 consultar em fusos horários dos EUA (no BigQuery)

Às vezes, os arquivos de registros do Looker são armazenados em UTC, mesmo que você esteja consultando em fusos horários do Leste ou do Pacífico. Isso pode causar um problema em que os arquivos de registro já foram lançados 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, se passar da meia-noite UTC, essas entradas de registro serão feitas.

Os exemplos abaixo 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 falhará se NULL for substituído pelas datas, é necessário adicionar um always_filter à Análise:

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, eu precisei usar IFNULL para fazer o SQL funcionar.

• O limite inferior: IFNULL({% date_start date_filter %},CURRENT_DATE())
• O 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 em alguma forma agregada ao longo de um período histórico. Para executar essa operação no SQL, normalmente é feita uma função de janela que retorna o número n de linhas de uma tabela exclusiva por data. No entanto, há um catch-22 ao usar uma tabela particionada por data. É preciso primeiro ditar 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. Aqui está 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 está fornecendo uma restrição WHERE para reduzir o conjunto de resultados de volta ao período que o usuário especificou originalmente na consulta.

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

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

Neste exemplo, uma tabela Hive table_part_by_yyyy_mm_dd tem uma coluna particionada dt, uma string formatada como 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 nos dois exemplos abaixo:

• 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 é para processar o caso de NULLs se alguém digitar um filtro como before 2010-01-01 ou after 2012-12-31.
• Esse é o código de dialeto Presto, de modo que o Hive terá algumas diferenças na string de formato (yyyy-MM-dd) e date_format não pode assumir um valor NULL. Portanto, o coalesce precisaria ser movido para lá com algum tipo de valor padrão.

Exemplo 1: LookML que usa uma expressão comum para filtrar a tabela

Este exemplo usa uma tabela derivada para filtrar a tabela.

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 levam muito tempo para fazer verificações completas de tabelas e consomem muitos recursos de cluster. Por isso, é recomendável também colocar um filtro padrão em "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 do 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 ;;
  }
}

Para validar se as partições da tabela estão sendo usadas, verifique a saída de EXPLAIN no SQL Runner para uma consulta gerada por este LookML. Para acessá-la, clique na seção SQL na guia "Dados" da página "Explorar". Você verá algo assim:

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

O partitionKey=true com o intervalo de chaves de partição listado indicam que ele está verificando apenas essas colunas particionadas.