Transformação Unnest

Descompacta dados aninhados de uma coluna de objeto ou matriz para criar novas linhas ou colunas com base nas chaves dos dados de origem. Essa transformação funciona de maneira diferente em colunas do tipo matriz ou objeto.

A transformação unnest precisa incluir chaves que você especifica como parte da etapa de transformação. Para desaninhar uma coluna de dados de matriz que não contenha chaves, use a transformação flatten. Consulte Transformação Flatten.

Esta transformação pode ser aplicada automaticamente como uma das primeiras etapas de sua receita. Consulte Etapas de análise inicial.

Uso básico

unnest col: myObj keys:'sourceA','sourceB' pluck:true markLineage:true

Resultado:

  • Extrai da coluna myObj os valores correspondentes para as chaves sourceA e sourceB em duas novas colunas.
  • Como markLineage é true, esses novos nomes de coluna são adicionados antes do nome de origem: sourceA_column1 e sourceB_column2.
  • Todos os valores não ausentes das colunas de origem são adicionados às novas colunas correspondentes e removidos da coluna de origem, uma vez que pluck é true.

Parâmetros

unnest col:column_ref keys:'key1','key2' [pluck:true|false] [markLineage:true|false]

ParâmetroObrigatório?Transform BuilderTipo de dadosDescrição
colSColunastringNome da coluna de origem
keysSCaminhosstringLista separada por vírgulas de nomes de chaves entre aspas. Consulte os exemplos abaixo.
pluckNRemover originalbooleanoSe for true, quaisquer valores desaninhados da fonte também serão removidos. O padrão é false.
markLineageNPreceder o nome da coluna originalbooleanoSe for true, os nomes das novas colunas serão adicionados antes do nome da coluna de origem.

Para mais informações sobre os padrões de sintaxe, consulte Notas de sintaxe da documentação da linguagem.

col

Identifica a coluna na qual aplicar a transformação. Você pode especificar apenas uma coluna.

Observações sobre o uso:

Obrigatório?Tipo de dados
SimString (nome da coluna)

keys

Lista de chaves separadas por vírgulas a usar para extrair dados da coluna de origem especificada.

  • É necessário colocar os valores da chave entre aspas (por exemplo, 'key1','key2'). Qualquer valor entre aspas é considerado o caminho para uma única chave.
  • Os valores da chave diferenciam maiúsculas de minúsculas.
  • É necessário listar cada chave. Não é possível especificar um intervalo de chaves.

OBSERVAÇÃO: teclas que contêm valores não alfanuméricos, como espaços, precisam estar entre colchetes e aspas. Os valores com sublinhados não requerem aspas duplas.

A lista de chaves separadas por vírgulas determina as colunas a serem geradas dos dados de origem. Se você especificar três valores para as chaves, as três novas colunas conterão os valores correspondentes da coluna de origem.

Esse parâmetro tem sintaxe diferente para o uso de dados aninhados de nível único e de vários níveis. Há também variações na sintaxe entre o tipo de dados de Objeto e de Matriz.

Observações sobre o uso:

Obrigatório?Tipo de dados
Sim

Valores de string separados por vírgulas.

Os exemplos de sintaxe são fornecidos abaixo.

Chaves para dados de Objeto - nível único

OBSERVAÇÃO: os nomes das chaves diferenciam maiúsculas e minúsculas.

Para uma única chave de nível superior em um campo de Objeto, você pode especificar a chave como uma string entre aspas simples:

unnest col:myCol keys: 'myObjKey'

O exemplo acima procura a chave myObjKey entre as chaves de nível superior no objeto e retorna o valor correspondente para a nova coluna. Você também pode colocar esta chave entre colchetes:

unnest col:myCol keys: '[myObjKey]'

Para especificar várias chaves de primeiro nível, use o seguinte comando

unnest col:myCol keys:'myObjKey','my2ndObjKey'

O exemplo acima gera duas novas colunas (myObjKey e my2ndObjKey) que contêm os valores correspondentes para as chaves.

Chaves para dados de Objeto - vários níveis

Você também pode fazer referência a chaves que estão abaixo do primeiro nível no Objeto.

Dados de exemplo

{ "Key1" :
  { "Key1A" :

  }
}
{ "Key2" :
  { "Key2A" :

  }
}
{ "Key3" :
  { "Key3A" :

  }
}

Para adquirir os dados para a chave Key1A, use o seguinte:

unnest col: myCol keys: 'Key1[Key1A]'

Na nova coluna, o valor exibido é este:

Para desaninhar um valor de terceira camada, use uma transformação semelhante à que está abaixo:

unnest col: myCol keys: 'Key2[Key2A][Key2A1]'

Na nova coluna, essa transformação gera um valor de Value2.

Chaves para dados de Matriz - nível único

Você pode fazer referência a elementos de matriz usando nomes de chaves ou índices baseados em zero.

OBSERVAÇÃO: todas as referências às chaves de Matriz precisam estar entre colchetes. Só é possível fazer referência às chaves de Matriz pelo número de índice.

Dados de matriz de exemplo:

["red","orange","yellow","green","blue","indigo","violet"]

unnest col: myCol keys:'[1]'

A transformação acima recupera o valor orange da matriz.

unnest col: myCol keys:'[1]','[3]'

Valores devolvidos: orange e green.

Chaves para dados da matriz - vários níveis

O exemplo seguinte de dados de Matriz aninhados corresponde à estrutura dos dados de Objeto no exemplo anterior:

[ [ "Item1", ["Item1A", ["Item1A1","Value1"] ] ], [ "Item2", ["Item2A",  ["Item2A1","Value2"] ] ], [ "Item3", ["Item3A",["Item3A1","Value3"] ] ] ] 

Para desaninhar o valor para Items2A:

unnest col:myCol keys:'[1][0]'

O valor inserido na nova coluna é o seguinte:

["Item2A1","Value2"]

Para desaninhar do terceiro nível:

unnest col:myCol keys:'[2][0][0]'

O valor inserido é Item3A.

pluck

Indica se os valores adicionados das colunas de origem para as de saída serão removidos da origem.

  • Defina como true para remover valores da origem depois de eles terem sido adicionados às colunas de saída.
  • (Padrão) Defina como false para deixar as colunas de origem intactas.

Observações sobre o uso:

Obrigatório?Tipo de dado
NãoBooleano

markLineage

Quando definido como true, os nomes de novas colunas são adicionados antes do nome da coluna de origem. Exemplo:

Coluna de origemColuna de saída
mySourceColumnmySourceColumn_column1

As referências de chave aninhadas são anexadas ao nome da coluna:

Coluna de origemValor da chaveColuna de saída
mySourceColumnkeys: '[Key1][Key2]'mySourceColumn_Key1_Key2

OBSERVAÇÃO: se a transformação unnest não alterar o número de linhas, você ainda poderá acessar as informações do número da linha na grade de dados, presumindo que ainda estavam disponíveis quando a transformação foi executada.

Observações sobre o uso:

Obrigatório?Tipo de dado
NãoBooleano

Exemplos

Exemplo: desaninhar um objeto

Você tem o conjunto de dados a seguir. A coluna Sizes contém dados do objeto nos tamanhos disponíveis.

Fonte:

ProdIdProdNameSizes
1001Chapéu{'Small':'N','Medium':'Y','Large':'Y','Extra-Large':'Y'}
1002Camisa{'Small':'N','Medium':'Y','Large':'Y','Extra-Large':'N'}
1003Calça{'Small':'Y','Medium':'Y','Large':'Y','Extra-Large':'N'}

Transformação:

OBSERVAÇÃO: dependendo do formato dos dados de origem, talvez seja necessário efetuar algumas substituições na coluna Sizes, para eles sejam inferidos como valores apropriados do tipo Objeto. O formato final deverá ficar como o mostrado acima.

Se ainda não estiver inferido, defina o tipo da coluna Sizes como Objeto:

settype col: Sizes type: 'Object'

Desaninhe os dados em colunas separadas. O seguinte adiciona Sizes_ ao início do nome da coluna recém-gerada.

unnest col:Sizes keys:'Small','Medium','Large','Extra-Large' markLineage:true

Pode ser útil adicionar pluck:true à transformação acima. Quando adicionados, os valores que não são aninhados são removidos da fonte, deixando apenas os valores que não foram processados:

unnest col:Sizes keys:'Small','Medium','Large','Extra-Large' markLineage:true pluck:true

Se todos os valores tiverem sido processados, a coluna Sizes agora conterá um conjunto de mapas com dados ausentes. Você pode usar o seguinte para determinar se o comprimento dos dados restantes é maior do que dois caracteres. Esta transformação é recomendada para a visualização:

derive value:(LEN(Sizes) > 2) as:'len_Sizes'

Você pode descartar a coluna de origem:

drop col:Sizes

Resultados:

Quando você terminar, o conjunto de dados deverá ser o seguinte:

ProdIdProdNameSizes_SmallSizes_MediumSizes_LargeSizes_Extra-Large
1001ChapéuNSSS
1002CamisaNSSN
1003CalçaSSSN

Exemplo - Desaninhar uma matriz

No exemplo a seguir demonstramos as diferenças entre a transformação unnest e o flatten, incluindo a forma como você usa unnest para nivelar os dados de matriz com base em chaves especificadas.

Fonte:

Você tem os seguintes dados sobre as pontuações dos testes dos alunos. As pontuações individuais são armazenadas na matriz Scores, e você precisa acompanhar cada teste em uma linha exclusivamente identificável. Este exemplo tem dois objetivos:

  1. Uma linha para cada teste de estudante.
  2. Um identificador exclusivo para cada combinação estudante-pontuação.
LastNameFirstNameScores
AdamsAllen[81,87,83,79]
BurnsBonnie[98,94,92,85]
CannonCharles[88,81,85,78]

Transformação:

Quando os dados forem importados do formato CSV, adicione uma transformação header e remova as aspas da coluna Scores:

header

replace col:Scores with:'' on:`"` global:true

Validar data do teste: para começar, é recomendável verificar se você tem o número adequado de pontuação de teste para cada estudante. Você pode usar a seguinte transformação para calcular a diferença entre o número esperado de elementos na matriz Scores (4) e o número real:

derive value: (4 - ARRAYLEN(Scores)) as: 'numMissingTests'

Quando a transformação é visualizada, você pode ver no conjunto de dados de amostra que todos os testes estão incluídos. Você decide se quer ou não incluir essa coluna no conjunto de dados final, já que pode identificar testes ausentes quando a receita é executada em escala.

Identificador exclusivo de linha: é necessário dividir a matriz Scores em linhas individuais para cada teste. No entanto, não há um identificador exclusivo para a linha rastrear testes individuais. Em teoria, você poderia usar a combinação dos valores LastName-FirstName-Scores para fazê-lo, mas, se um aluno gravou o mesmo resultado duas vezes, o conjunto de dados fica com linhas duplicadas. Na transformação a seguir, você cria uma matriz paralela chamada Tests, que contém uma matriz de índice para o número de valores na coluna Scores. Os valores do índice começam em 0:

derive value:RANGE(0,ARRAYLEN(Scores)) as:'Tests'

Além disso, queremos criar um identificador para a linha de origem usando a função SOURCEROWNUMBER:

derive value:SOURCEROWNUMBER() as:'orderIndex'

Uma linha para cada teste de estudante: os dados serão semelhantes aos seguintes:

LastNameFirstNameScoresTestsorderIndex
AdamsAllen[81,87,83,79][0,1,2,3]2
BurnsBonnie[98,94,92,85][0,1,2,3]3
CannonCharles[88,81,85,78][0,1,2,3]4

Agora, reúna as matrizes Tests e Scores em uma única matriz aninhada usando a função ARRAYZIP:

derive value:ARRAYZIP([Tests,Scores])

O conjunto de dados foi alterado:

LastNameFirstNameScoresTestsorderIndexcolumn1
AdamsAllen[81,87,83,79][0,1,2,3]2[[0,81],[1,87],[2,83],[3,79]]
AdamsBonnie[98,94,92,85][0,1,2,3]3[[0,98],[1,94],[2,92],[3,85]]
CannonCharles[88,81,85,78][0,1,2,3]4[[0,88],[1,81],[2,85],[3,78]]

Com a transformação flatten, você pode descompactar a matriz aninhada:

flatten col: column1

Cada combinação teste-pontuação agora é dividida em uma linha separada. As combinações aninhadas teste-pontuação precisam ser divididas em colunas separadas usando unnest:

unnest col:column1 keys:'[0]','[1]'

Depois de soltar column1, que não é mais necessária, renomeie as duas colunas geradas:

rename col:column_0 to:'TestNum'

rename col:column_0 to:'TestScore'

Identificador exclusivo de linha: você pode realizar mais uma etapa para criar identificadores exclusivos de teste, que identificam o teste específico para cada estudante. A seguir, é usado o identificador de linha original OrderIndex como um identificador para o aluno e o valor TestNumber para criar o valor da coluna TestId:

derive value: (orderIndex * 10) + TestNum as: 'TestId'

Os valores acima são inteiros. Para melhorar a aparência dos identificadores, você pode adicionar o seguinte comando:

merge col:'TestId00','TestId'


Extensão:
pode ser útil gerar algumas informações estatísticas resumidas neste conjunto de dados. Por exemplo, você pode estar interessado em calcular o resultado médio do teste de cada estudante. Nesta etapa, é necessário descobrir como agrupar adequadamente os valores de teste. Nesse caso, não é possível agrupar pelo valor LastName e, quando executado em escala, poderá haver colisões entre os nomes quando essa receita for executada neste modo. Portanto, talvez seja necessário criar um tipo de chave primária usando o seguinte comando:

merge col:'LastName','FirstName' with:'-' as:'studentId'

Agora você pode usar isto como um parâmetro de agrupamento para o cálculo:

derive value:AVERAGE(TestScore) group:studentId as:'avg_TestScore'

Resultados:

Depois de descartar as colunas desnecessárias e mover as outras, o conjunto de dados será semelhante ao seguinte:

TestIdLastNameFirstNameTestNumTestScorestudentIdavg_TestScore
TestId0021AdamsAllen081Adams-Allen82,5
TestId0022AdamsAllen187Adams-Allen82,5
TestId0023AdamsAllen283Adams-Allen82,5
TestId0024AdamsAllen379Adams-Allen82,5
TestId0031AdamsBonnie098Adams-Bonnie92,25
TestId0032AdamsBonnie194Adams-Bonnie92,25
TestId0033AdamsBonnie292Adams-Bonnie92,25
TestId0034AdamsBonnie385Adams-Bonnie92,25
TestId0041CannonChris088Cannon-Chris83
TestId0042CannonChris181Cannon-Chris83
TestId0043CannonChris285Cannon-Chris83
TestId0044CannonChris378Cannon-Chris83

Exemplo: como extrair valores de chave dos dados de carro e depois desaninhá-los em colunas separadas

Neste exemplo, mostramos como você pode descompactar dados aninhados em um Objeto em colunas separadas usando as seguintes transformações:

Fonte:

Você tem a seguinte informação sobre carros usados. A coluna VIN contém identificadores de veículo, e a coluna Properties contém pares chave-valor que descrevem as características de cada veículo. Você quer desempacotar esses dados em colunas separadas.

VINProperties
XX3 JT4522year=2004,make=Subaru,model=Impreza,color=green,mileage=125422,cost=3199
HT4 UJ9122year=2006,make=VW,model=Passat,color=silver,mileage=102941,cost=4599
KC2 WZ9231year=2009,make=GMC,model=Yukon,color=black,mileage=68213,cost=12899
LL8 UH4921year=2011,make=BMW,model=328i,color=brown,mileage=57212,cost=16999

Transformação:

Adicione a seguinte transformação, que identifica todos os valores-chave na coluna como se começassem com caracteres alfabéticos.

  • A string valueafter identifica onde o valor correspondente começa após a chave.
  • A string delimiter indica o fim de cada par de chave-valor.

extractkv col:Properties key:`{alpha}+` valueafter:`=` delimiter:`,`

Agora que o objeto de valores foi criado, você pode usar a transformação unnest para descompactar esses dados mapeados. Na instrução a seguir, cada chave é especificada, o que resulta em colunas separadas encabeçadas pela chave nomeada:

unnest col:extractkv_Properties keys:'year','make','model','color','mileage','cost'

Resultados:

Quando você descartar as colunas Properties desnecessárias, o conjunto de dados ficará da seguinte maneira:

VINyearmakemodelcolormileagecost
XX3 JT45222004SubaruImprezagreen1254223199
HT4 UJ91222006VWPassatsilver1029414599
KC2 WZ92312009GMCYukonblack6821312899
LL8 UH49212011BMW328ibrown5721216999

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Documentação do Google Cloud Dataprep
Precisa de ajuda? Acesse nossa página de suporte.