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 quando você está verificando 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 tabela do BigQuery a seguir, 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) sejam escritos em um bloco de código no exemplo a seguir, é uma prática recomendada colocar visualizações em arquivos de visualização individuais e colocar as explorações 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
. você pode transformar isso em uma matriz de elementos STRUCT
colocando-o 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 não aninhada 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
.