Usar date_start e date_end com filtros de data

Pode usar filtros baseados em modelos para fazer referência a datas selecionando as datas de início e fim num filtro de datas, respetivamente {% date_start date_filter %} e {% date_end date_filter %}. Esta página explica alguns exemplos de utilização e os passos para os realizar.

Notas de sintaxe

A sintaxe seguinte funciona com a maioria dos dialetos, mas determinados dialetos têm exemplos de utilização específicos. Exemplo:

  • O BigQuery permite um maior controlo quando trabalha com funções de carateres universais de tabelas, como TABLE_DATE_RANGE e TABLE_QUERY, pelo que a utilização de {% table_date_range prefix date_filter %} é insuficiente para especificar filtros de datas.
  • O Hadoop permite trabalhar com colunas particionadas por data, independentemente do tipo (string, date) ou do formato (YYYY-MM-DD) da coluna.

Notas de utilização

  • Quando não é especificado nenhum valor para date_filter, {% date_start date_filter %} e {% date_end date_filter %} são avaliados como NULL.
  • No caso de um intervalo aberto date_filter (como before 2016-01-01 ou after 2016-01-01), um dos filtros {% date_start date_filter %} ou {% date_end date_filter %} é NULL.

    • Para se certificar de que nenhum destes dois casos resulta em SQL inválido, pode usar IFNULL ou COALESCE no LookML.

Exemplos de utilização

Colunas particionadas mensalmente (no BigQuery)

Em alguns conjuntos de dados do BigQuery, as tabelas estão organizadas por mês e o ID da tabela tem a combinação do ano e do mês como sufixo. Um exemplo disto encontra-se no seguinte conjunto de dados, que tem muitas tabelas com nomes como pagecounts_201601, pagecounts_201602 e pagecounts_201603.

Exemplo 1: LookML que depende de always_filter

A seguinte tabela derivada usa TABLE_QUERY([dataset], [expr]) para obter o conjunto correto de tabelas 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
  }
}

Algumas notas 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') vai gerar apenas a parte YYYYmm da data de início.

NULL vai ser substituído pelas peças date_filter. Para contornar esta situação, é necessário um always_filter no Explore: 

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

Tenha em atenção que esta ação continua a falhar para filtros de datas anteriores à data mais antiga no conjunto de dados, porque {% date_start date_filter %} é 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 predefinido de tabelas para consultar. No exemplo seguinte, são usados os dois meses anteriores:

  • 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 ficheiros de registo estão em UTC quando são consultados em fusos horários americanos (no BigQuery)

Por vezes, os ficheiros de registo do Looker são armazenados em UTC, mesmo que esteja a consultar fusos horários do leste ou do Pacífico. Este problema pode fazer com que os ficheiros de registo já tenham sido transferidos para a data de amanhã no fuso horário local da consulta, o que resulta na perda de alguns dados.

A solução consiste em adicionar um dia extra à data de fim do filtro de data para garantir que, se for depois da meia-noite UTC, essas entradas do registo são recolhidas.

Os exemplos seguintes 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
  }
}

Uma vez que este SQL falha se NULL for substituído pelas datas, é necessário adicionar um always_filter à exploração: 

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

Exemplo 2: LookML que não depende de always_filter

Neste exemplo, o intervalo de datas predefinido está codificado no LookML. Como COALESCE estava a devolver um tipo unknown, tive de usar IFNULL para que o SQL funcionasse.

  • 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 de intervalo de N dias finais (no BigQuery)

Quando realiza determinadas análises, são esperados cálculos de alguma forma agregada num período histórico. Para realizar esta operação em SQL, normalmente, implementa-se uma função de janela que retrocede n linhas para uma tabela única por data. No entanto, existe um dilema quando usa uma tabela particionada por data: primeiro, tem de indicar o conjunto de tabelas em que a consulta vai ser executada, mesmo que a consulta precise realmente de tabelas do histórico adicionais para o cálculo.

A solução: permitir que a data de início seja anterior às datas fornecidas no filtro de datas. Segue-se um exemplo que retrocede 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 declaração SELECT adicional é necessária porque está a fornecer uma restrição WHERE para reduzir o conjunto de resultados ao intervalo de datas que o utilizador especificou originalmente na consulta.

Tabela particionada por data através de uma 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 de colunas que são pesquisadas com frequência, especialmente 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á a ser pesquisada pelo Presto.

Quando o gerador é executado pela primeira vez, o LookML tem o seguinte aspeto: 

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 notas sobre o código nas expressões em ambos os exemplos seguintes:

  • O resultado de date_start e date_end é type: timestamp.
  • date_format( <expr>, '%Y-%m-%d') é usado para converter a data/hora numa string e no formato correto.
  • O objetivo do coalesce é processar os casos de valores NULL se alguém escrever um filtro como before 2010-01-01 ou after 2012-12-31.
  • Este é o código do dialeto Presto, pelo que o Hive terá algumas diferenças na string de formato (yyyy-MM-dd) e date_format não pode ter um valor NULL, pelo que coalesce teria de ser movido para aí com algum tipo de valor predefinido.

Exemplo 1: LookML que usa uma expressão de tabela 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 demoram demasiado tempo para fazer análises completas das tabelas (e consomem demasiados recursos do cluster). Por isso, é uma boa ideia colocar também um filtro predefinido na opção Explorar para esta vista: 

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 predicados diretamente na tabela, sem uma subconsulta nem uma 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 ;;
  }
}

Podemos validar se as partições de tabelas estão realmente a ser usadas verificando o resultado de EXPLAIN no SQL Runner para uma consulta gerada por este LookML (pode aceder ao mesmo clicando na secção SQL no separador Dados da página Explorar). É apresentado algo semelhante ao seguinte: 

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, juntamente com o intervalo de chaves de partição indicadas, mostra que está apenas a analisar essas colunas particionadas.