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

• 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

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

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

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 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 ou 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, indica que está apenas a analisar essas colunas particionadas.