Dados aninhados no BigQuery (registros repetidos)

O BigQuery oferece suporte a registros aninhados em tabelas. Os registros aninhados podem ser um único registro ou conter valores repetidos. Esta página oferece 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 mesclas. 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 a uma mesclagem colocalizada.
• As estruturas aninhadas evitam a repetição de dados que teriam que ser repetidos em uma tabela ampla e 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 tabela persons_living do BigQuery a seguir mostra um esquema típico que armazena dados de exemplo do usuário, incluindo fullName, age, phoneNumber e citiesLived, além do tipo de dados e do modo 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 das 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 parece idêntica a uma Análise gerada com tabelas não aninhadas.

Observação: embora todos os componentes (visualizações e Análises) sejam escritos em um bloco de código no exemplo abaixo, é recomendável 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 seções a seguir:

• visualizações
• Declarações de registro
• Mesclagens
• 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 medições. As medidas e drill_fields são definidas normalmente, como se estivessem na própria tabela. 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]
  }

}

Declarações de registro

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

Os 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. Além dessa diferença, desaninhar uma matriz de elementos STRUCT é exatamente como mesclar uma tabela.

No caso de registros não repetidos, basta usar STRUCT. você pode transformar isso em uma matriz de elementos STRUCT colocando-o entre colchetes. Embora isso pareça estranho, não há penalidade de desempenho. Isso mantém as coisas limpas e simples.

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
  }

}

Junções para 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, você pode encontrar uma situação em que algumas matrizes não têm 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 relativo (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 simples repetidos

Os dados aninhados no BigQuery também podem ser valores simples, como números inteiros ou strings. Para desagrupar 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 direta à tabela de valores, que é desaninhada no explore.