Dados aninhados no BigQuery (registros repetidos)

O BigQuery aceita registros aninhados em tabelas. Os registros aninhados podem ser um único registro ou conter valores repetidos. Nesta página, você encontra uma visão geral do trabalho com dados aninhados do BigQuery no Looker.

As vantagens dos registros aninhados

Há algumas vantagens em usar registros aninhados ao verificar um conjunto de dados distribuído:

• Registros aninhados não exigem junções. Isso significa que os cálculos podem ser mais rápidos e verificar muito menos dados do que se você tivesse que juntar os dados extras novamente a cada consulta.
• Estruturas aninhadas são essencialmente tabelas pré-mescladas. Não há despesas adicionais para a consulta se você não fizer referência à coluna aninhada, porque os dados do BigQuery são armazenados em colunas. Se você fizer referência à coluna aninhada, a lógica será idêntica à junção colocalizada.
• Estruturas aninhadas evitam a repetição de dados que teriam de ser repetidos em uma ampla tabela desnormalizada. Em outras palavras, para uma pessoa que morou em cinco cidades, uma ampla tabela desnormalizada conteria todas as suas informações em cinco linhas (uma para cada cidade em que ela morou). Em uma estrutura aninhada, as informações repetidas ficam em apenas uma linha, já que a matriz de cinco cidades pode estar contida em uma única linha e desaninhada quando necessário.

Como trabalhar com registros aninhados no LookML

A seguinte tabela do BigQuery, persons_living, exibe um esquema típico que armazena dados de usuários de exemplo, incluindo fullName, age, phoneNumber e citiesLived com o tipo de dados e o mode de cada coluna. O esquema mostra que os valores na coluna citiesLived são repetidos, indicando que alguns usuários podem ter morado em várias cidades:

O exemplo a seguir é o LookML para as Análises e visualizações que você pode criar usando o esquema anterior mostrado. Há três visualizações: persons, persons_cities_lived e persons_phone_number. A Análise é idêntica a uma Análise criada com tabelas não aninhadas.

Observação:embora todos os componentes (visualizações e Análise) estejam escritos em um bloco de código no exemplo a seguir, a prática recomendada é colocar as visualizações em arquivos de visualização individuais e colocar as Análises e a especificação connection: no arquivo de modelo.

-- model file

connection: "bigquery_publicdata_standard_sql"

explore: persons {

  # Repeated nested object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
    relationship: one_to_many
  }

  # Non repeated nested object
  join: persons_phone_number {
    view_label: "Persons: Phone:"
    sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
    relationship: one_to_one
  }

}

-- view files

view: persons {

 sql_table_name: bigquery-samples.nested.persons_living ;;

  dimension: id {
    primary_key: yes
    sql: ${TABLE}.fullName ;;
  }

  dimension: fullName {label: "Full Name"}

  dimension: kind {}

  dimension: age {type:number}

  dimension: citiesLived {hidden:yes}

  dimension: phoneNumber {hidden:yes}

  measure: average_age {
    type: average
    sql: ${age} ;;
    drill_fields: [fullName,age]
  }

  measure: count {
    type: count
    drill_fields: [fullName, cities_lived.place_count, age]
  }
}

view: persons_phone_number {

  dimension: areaCode {label: "Area Code"}

  dimension: number {}
}

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
  }

  dimension: place {}

  dimension: numberOfYears {
    label: "Number Of Years"
    type: number
  }

  measure: place_count {
    type: count
    drill_fields: [place, persons.count]
  }

  measure: total_years {
    type: sum
    sql: ${numberOfYears} ;;
    drill_fields: [persons.fullName, persons.age, place, numberOfYears]
  }

}

Cada componente para trabalhar com dados aninhados no LookML é discutido em mais detalhes nas seguintes seções:

• visualizações
• Registrar declarações
• Mesclagens (link em inglês)
• Valores repetidos simples

Visualizações

Cada registro aninhado é gravado como uma visualização. Por exemplo, a visualização phoneNumber simplesmente declara as dimensões que aparecem no registro:

view: persons_phone_number {

  dimension: areaCode {label: "Area Code"}

  dimension: number {}

}

A visualização persons_cities_lived é mais complexa. Como mostrado no exemplo do LookML, você define as dimensões que aparecem no registro (numberOfYears e place), mas também pode definir algumas medidas. As medidas e drill_fields são definidas normalmente, como se os dados estivessem em uma tabela própria. A única diferença real é que você declara id como um primary_key para que os agregados sejam calculados corretamente.

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
  }

  dimension: place {}

  dimension: numberOfYears {
    label: "Number Of Years"
    type: number
  }

  measure: place_count {
    type: count
    drill_fields: [place, persons.count]
  }

  measure: total_years {
    type: sum
    sql: ${numberOfYears} ;;
    drill_fields: [persons.fullName, persons.age, place, numberOfYears]
  }

}

Registrar declarações

Na visualização que contém os subregistros (neste caso, persons), você precisa declarar os registros. Elas serão usadas ao criar mesclagens. É possível ocultar esses campos do LookML com o parâmetro hidden porque eles não serão necessários ao analisar os dados.

view: persons {

  ...
  dimension: citiesLived {
    hidden:yes
    }

  dimension: phoneNumber {
    hidden:yes
    }
  ...

}

Mesclagens

Registros aninhados no BigQuery são matrizes de elementos STRUCT. Em vez de usar um parâmetro sql_on, a relação de mesclagem é integrada à tabela. Nesse caso, você pode usar o parâmetro de mesclagem sql: para usar o operador UNNEST. Fora essa diferença, desaninhar uma matriz de elementos STRUCT é exatamente como unir uma tabela.

No caso de registros não repetidos, basta usar STRUCT. Para transformar isso em uma matriz de elementos STRUCT, coloque-a entre colchetes. Embora isso possa parecer estranho, parece que não há penalidades de desempenho e, por isso, deixa tudo mais simples e organizado.

explore: persons {

  # Repeated nested object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
    relationship: one_to_many
  }

  # Non repeated nested object
  join: persons_phone_number {
    view_label: "Persons: Phone:"
    sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
    relationship: one_to_one
  }

}

Mesclagens de matrizes sem chaves exclusivas para cada linha

Embora seja melhor ter chaves naturais identificáveis nos dados ou chaves alternativas criadas no processo ETL, isso nem sempre é possível. Por exemplo, é possível encontrar uma situação em que algumas matrizes não tenham uma chave exclusiva relativa para a linha. É aqui que WITH OFFSET pode ser útil na sintaxe de mesclagem.

Por exemplo, uma coluna que representa uma pessoa pode ser carregada várias vezes se a pessoa mora em várias cidades, como Chicago, Denver, São Francisco etc. Pode ser difícil criar uma chave primária na linha desaninhada se uma data ou outra chave natural identificável não for fornecida para distinguir o tempo de permanência da pessoa em cada cidade. É aqui que WITH OFFSET pode fornecer um número de linha relativa (0,1,2,3) para cada linha não aninhada. Essa abordagem garante uma chave exclusiva na linha não aninhada:

explore: persons {

  # Repeated nested Object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived WITH OFFSET as person_cities_lived_offset;;
    relationship: one_to_many
  }

}

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${offset} AS STRING)) ;;
  }

  dimension: offset {
    type: number
    sql: person_cities_lived_offset;;
  }

}

Valores repetidos simples

Os dados aninhados no BigQuery também podem ser valores simples, como números inteiros ou strings. Para desaninhar matrizes de valores repetidos simples, use uma abordagem semelhante à mostrada anteriormente, usando o operador UNNEST em uma mesclagem.

O exemplo a seguir desaninha uma determinada matriz de números inteiros, "unresolved_skus".

explore: impressions {
  join: impressions_unresolved_sku {
    sql: LEFT JOIN UNNEST(unresolved_skus) AS impressions_unresolved_sku ;;
    relationship: one_to_many
  }

}

view: impressions_unresolved_sku {

  dimension: sku {
    type: string
    sql: ${TABLE} ;;
  }

}

O parâmetro sql para a matriz de números inteiros, unresolved_skus, é representado como ${TABLE}. Isso faz referência diretamente à própria tabela de valores, que é desaninhada no explore.