Como usar "date_start" e "date_end" com filtros de data

Use filtros com modelos 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. Nesta página, você vai encontrar alguns exemplos de casos de uso e as etapas para realizá-los.

Observações de sintaxe

• O BigQuery permite maior controle ao trabalhar com funções de caractere curinga de tabela, como TABLE_DATE_RANGE e TABLE_QUERY. Portanto, o uso de {% 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 há um valor especificado para date_filter, {% date_start date_filter %} e {% date_end date_filter %} são avaliados como NULL.
• No caso de um date_filter aberto (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

Exemplo 1: LookML que depende de always_filter

A tabela derivada a seguir usa TABLE_QUERY([dataset], [expr]) para receber o conjunto correto 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 se refere 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') vai 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"]
  }
}

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 a serem consultadas. No exemplo abaixo, os dois últimos 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
  }
}

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

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

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 notas sobre o código nas expressões nos dois 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 é usado para processar o caso de valores NULL 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 filtrar a tabela

Neste exemplo, usamos 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 muito para fazer varreduras de tabela completas (e consomem muitos recursos do cluster). Por isso, é recomendável colocar um filtro padrão no recurso Análise 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]]

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