Visão geral

O BigQuery é um banco de dados de análise NoOps desenvolvido pelo Google, totalmente gerenciado e de baixo custo. Com ele, você pode consultar muitos terabytes de dados sem ter que gerenciar uma infraestrutura ou precisar de um administrador de banco de dados. O BigQuery usa SQL e está disponível no modelo de pagamento por uso. Assim, você pode dar foco na análise dos dados para encontrar insights significativos.

Este laboratório mostra em detalhes como trabalhar com dados semiestruturados (fazendo a ingestão de dados do tipo matriz e JSON) no BigQuery. A desnormalização do seu esquema em uma única tabela com campos aninhados e repetidos pode gerar melhorias de desempenho, mas a sintaxe SQL para trabalhar com dados do tipo matriz pode ser complexa. Com atividades práticas, você aprenderá a carregar, consultar, solucionar problemas e desaninhar vários conjuntos de dados semiestruturados.

Objetivos

Neste laboratório, você aprenderá a:

• Carregar dados JSON semiestruturados no BigQuery
• Criar e consultar matrizes
• Criar e consultar structs
• Consultar campos aninhados e repetidos

Configuração e requisitos

Para cada laboratório, você recebe um novo projeto do Google Cloud e um conjunto de recursos por um determinado período e sem custos financeiros.

1. Faça login no Qwiklabs em uma janela anônima.

2. Confira o tempo de acesso do laboratório (por exemplo, 1:15:00) e finalize todas as atividades nesse prazo.
   Não é possível pausar o laboratório. Você pode reiniciar o desafio, mas vai precisar refazer todas as etapas.

3. Quando tudo estiver pronto, clique em Começar o laboratório.

4. Anote as credenciais (Nome de usuário e Senha). É com elas que você vai fazer login no Console do Google Cloud.

5. Clique em Abrir Console do Google.

6. Clique em Usar outra conta, depois copie e cole as credenciais deste laboratório nos locais indicados.
   Se você usar outras credenciais, vai receber mensagens de erro ou cobranças.

7. Aceite os termos e pule a página de recursos de recuperação.

Abra o BigQuery no Console do Cloud

1. No Console do Google Cloud, selecione o menu de navegação > BigQuery:

Você vai ver a caixa de mensagem Olá! Este é o BigQuery no Console do Cloud. Ela tem um link para o guia de início rápido e lista as atualizações da interface.

2. Clique em Concluído.

Tarefa 1: criar um conjunto de dados para armazenar nossas tabelas

1. Para criar um conjunto de dados, clique no ícone Ver ações ao lado do seu ID do projeto e selecione Criar conjunto de dados.

2. Nomeie o novo conjunto de dados como fruit_store. Não altere as outras opções (local dos dados e expiração da tabela padrão).

3. Clique em Criar conjunto de dados.

Tarefa 2: praticar o uso de matrizes no SQL

No SQL, normalmente você terá um único valor para cada linha, como nesta lista de frutas abaixo:

Row	Fruit
1	raspberry
2	blackberry
3	strawberry
4	cherry

E se você quisesse uma lista de frutas para cada pessoa na loja? Ela poderia ficar assim:

Row	Fruit	Person
1	raspberry	sally
2	blackberry	sally
3	strawberry	sally
4	cherry	sally
5	orange	frederick
6	apple	frederick

Em um banco de dados SQL relacional tradicional, você examinaria a repetição de nomes e logo pensaria em dividir a tabela acima em duas tabelas separadas, uma com as frutas e outra com as pessoas. Esse processo é chamado de normalização (o ato de transformar uma tabela em várias). Trata-se de uma abordagem comum para bancos de dados transacionais como o mySQL.

Para o armazenamento em data warehouse, os analistas de dados costumam fazer o contrário (a desnormalização) e transformam muitas tabelas separadas em uma grande tabela de relatórios.

Agora você vai aprender uma abordagem diferente que armazena dados em diversos níveis de granularidade em uma só tabela usando campos repetidos:

Row	Fruit (array)	Person
1	raspberry	sally
	blackberry
	strawberry
	cherry
2	orange	frederick
	apple

O que há de estranho na tabela anterior?

• Ela tem apenas duas linhas.
• Há vários valores de campo (frutas) em uma única linha.
• As pessoas estão associadas a todos os valores de campo.

Qual o insight principal? O tipo de dados array.

Uma maneira mais fácil de interpretar a matriz de frutas:

Row	Fruit (array)	Person
1	[raspberry, blackberry, strawberry, cherry]	sally
2	[orange, apple]	frederick

As duas últimas tabelas são iguais, e aprendemos duas coisas importantes com elas:

• Uma matriz é simplesmente uma lista de itens entre colchetes [ ]
• O BigQuery mostra visualmente as matrizes niveladas. Isso significa que os valores da matriz são listados na vertical (observe que todos eles ainda pertencem a uma única linha)

Teste por conta própria.

1. Digite o seguinte no Editor de consultas do BigQuery:

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry'] AS fruit_array

2. Clique em Executar.

3. Agora tente executar esta consulta:

#standardSQL SELECT ['raspberry', 'blackberry', 'strawberry', 'cherry', 1234567] AS fruit_array

Será exibido um erro parecido com este:

Error: Array elements of types {INT64, STRING} do not have a common supertype at [3:1]

As matrizes podem ter só um tipo de dados (somente strings ou números, por exemplo).

4. Esta é a tabela final de consulta:

#standardSQL SELECT person, fruit_array, total_cost FROM `data-to-insights.advanced.fruit_store`;

5. Clique em Executar.

6. Depois que você receber os resultados, clique na guia JSON para ver a estrutura aninhada.

Carregar dados JSON semiestruturados no BigQuery

E se você precisasse fazer a ingestão de um arquivo JSON no BigQuery?

1. Crie uma tabela no conjunto de dados fruit_store.
2. Para criar uma tabela, clique no ícone Ver ações ao lado do conjunto de dados fruit_store e selecione Criar tabela.

3. Adicione os seguintes detalhes à tabela:

• Origem: escolha Google Cloud Storage no menu suspenso Criar tabela de.
• Selecione o arquivo do bucket do GCS (digite ou cole as seguintes informações): cloud-training/data-insights-course/labs/optimizing-for-performance/shopping_cart.json
• Formato do arquivo: JSONL (JSON delimitado por nova linha) {Esses dados serão preenchidos automaticamente}
• Esquema: verifique a detecção automática (esquema e parâmetros de entrada).

4. Defina o nome da nova tabela como fruit_details.

5. Clique em Criar tabela.

No esquema, observe que fruit_array está marcada como REPEATED. Isso significa que ela é uma matriz.

Resumo

• O BigQuery tem suporte nativo a matrizes
• Os valores da matriz precisam ser dados do mesmo tipo
• No BigQuery, as matrizes são identificadas como campos REPEATED

Clique em Verificar meu progresso para conferir o objetivo. Criar um conjunto de dados e carregar dados JSON na tabela

Tarefa 3: criar suas próprias matrizes com ARRAY_AGG()

Você ainda não tem matrizes nas suas tabelas? Vamos criar!

1. Copie e cole a consulta abaixo para analisar esse conjunto de dados público:

SELECT fullVisitorId, date, v2ProductName, pageTitle FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 ORDER BY date

2. Clique em Executar para ver os resultados.

3. Agora usaremos a função ARRAY_AGG() para agregar os valores de string em uma matriz. Copie e cole a consulta abaixo para analisar este conjunto de dados público:

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

4. Clique em Executar para ver os resultados.

5. Em seguida, vamos usar a função ARRAY_LENGTH() para contar o número de páginas e produtos visualizados:

SELECT fullVisitorId, date, ARRAY_AGG(v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(v2ProductName)) AS num_products_viewed, ARRAY_AGG(pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(pageTitle)) AS num_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

6. Em seguida, vamos eliminar as páginas e os produtos duplicados para ver quantos produtos exclusivos foram visualizados. Basta adicionar DISTINCT a ARRAY_AGG():

SELECT fullVisitorId, date, ARRAY_AGG(DISTINCT v2ProductName) AS products_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT v2ProductName)) AS distinct_products_viewed, ARRAY_AGG(DISTINCT pageTitle) AS pages_viewed, ARRAY_LENGTH(ARRAY_AGG(DISTINCT pageTitle)) AS distinct_pages_viewed FROM `data-to-insights.ecommerce.all_sessions` WHERE visitId = 1501570398 GROUP BY fullVisitorId, date ORDER BY date

Resumo

É possível realizar ações muito úteis com as matrizes, por exemplo:

• Encontrar a quantidade de elementos com ARRAY_LENGTH(<array>)
• Eliminar a duplicação de elementos com ARRAY_AGG(DISTINCT <field>)
• Ordenar elementos com ARRAY_AGG(<field> ORDER BY <field>)
• Limitar ARRAY_AGG(<field> LIMIT 5)

Clique em Verificar meu progresso para conferir o objetivo. Criar matrizes com ARRAY_AGG()

Tarefa 4: consultar conjuntos de dados que já têm matrizes

O conjunto de dados público do BigQuery para Google Analytics bigquery-public-data.google_analytics_sample tem muito mais campos e linhas do que o conjunto de dados data-to-insights.ecommerce.all_sessions do nosso curso. Mais importante do que isso, ele já armazena valores de campos, como produtos, páginas e transações, de forma nativa como matrizes.

1. Copie e cole a consulta abaixo para analisar os dados disponíveis e tentar encontrar campos com valores repetidos (matrizes):

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

2. Execute a consulta

3. Role para a direita nos resultados até encontrar o campo hits.product.v2ProductName (falaremos sobre aliases de vários campos em breve).

4. A quantidade de campos disponíveis no esquema do Google Analytics pode sobrecarregar nossa análise. Vamos tentar consultar somente os campos referentes às visitas e aos nomes de página, como anteriormente:

SELECT visitId, hits.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398

Você vai receber um erro: Cannot access field page on a value with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>> at [3:8]

Para consultar os campos REPEATED (as matrizes) normalmente, você precisa organizar as matrizes em linhas.

Por exemplo, a matriz de hits.page.pageTitle está armazenada como uma linha única da seguinte forma:

['homepage','product page','checkout']

E precisamos que ela fique assim:

['homepage', 'product page', 'checkout']

Como fazer isso com o SQL?

5. Resposta: use a função UNNEST() no seu campo de matriz:

SELECT DISTINCT visitId, h.page.pageTitle FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, UNNEST(hits) AS h WHERE visitId = 1501570398 LIMIT 10

Vamos abordar o UNNEST() com mais detalhes posteriormente. Por enquanto, basta saber que:

• você precisa desaninhar as matrizes por meio do UNNEST() para trazer os elementos da matriz de volta às linhas;
• o UNNEST() sempre segue o nome da tabela na sua cláusula FROM. Pense nele conceitualmente como uma tabela pré-mesclada.

Clique em Verificar meu progresso para conferir o objetivo. Consultar conjuntos de dados que já têm matrizes

Tarefa 5: introdução aos STRUCTs

Talvez você queira saber por que o alias de campo hit.page.pageTitle parece ser três campos em um, separados por pontos. Da mesma forma que os valores matriz oferecem a flexibilidade de aprofundamento na granularidade dos seus campos, outros tipos de dados permitem maior amplitude no seu esquema, com o agrupamento de campos relacionados. Esse tipo de dado SQL é o STRUCT.

A maneira mais fácil de entender conceitualmente um STRUCT é pensar que ele é uma tabela separada e pré-mesclada na sua tabela principal.

Um STRUCT pode ter:

• um ou mais campos;
• tipos de dados iguais ou diferentes para cada campo;
• um alias próprio.

Até parece uma tabela, não é mesmo?

Agora vamos analisar um conjunto de dados com STRUCTs

1. Mude para o painel Explorador e clique em + Adicionar dados > Marcar um projeto com estrela por nome.

2. Digite bigquery-public-data e clique em Marcar com estrela.

3. Selecione bigquery-public-data na lista de projetos fixados para abri-lo.

4. Clique em Conjuntos de dados, encontre e abra google_analytics_sample.

5. Clique na tabela ga_sessions.

Observação: selecione qualquer tabela ga_sessions na lista.

6. Comece a rolar pelo esquema e responda à pergunta a seguir usando o recurso de procura do seu navegador (por exemplo, CTRL + F). Dica: abra todas as colunas antes de começar a contagem.

7. Como você pode imaginar, em um site moderno de e-commerce há uma quantidade enorme de dados de sessão armazenados. A principal vantagem de ter 32 STRUCTs em uma única tabela é que isso permite a execução de consultas como esta, sem o uso de mesclagens:

SELECT visitId, totals.*, device.* FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` WHERE visitId = 1501570398 LIMIT 10 Observação: a sintaxe .* diz ao BigQuery para retornar todos os campos desse STRUCT (como se totals.* fosse uma tabela separada que mesclamos).

O armazenamento de grandes tabelas de relatórios como STRUCTs ("tabelas" pré-mescladas) e MATRIZES (granularidade profunda) permite que você:

• ganhe vantagens significativas de desempenho ao evitar a mesclagem de 32 tabelas;
• receba dados granulares de MATRIZES quando precisar, mas sem nenhuma punição se não for o caso (o BigQuery armazena cada coluna individualmente no disco);
• tenha todo o contexto comercial em uma tabela, sem se preocupar com chaves de junção e com quais tabelas vão ter os dados necessários.

Clique em Verificar meu progresso para conferir o objetivo. Explorar um conjunto de dados com STRUCTS

Tarefa 6: praticar com ESTRUTURAS e MATRIZES

O próximo conjunto de dados contém os tempos das voltas dos corredores em uma pista. Cada volta será chamada de "split".

1. Para essa consulta, use a sintaxe de um STRUCT e observe os diferentes tipos de campo dentro do contêiner do struct:

#standardSQL SELECT STRUCT("Rudisha" as name, 23.4 as split) as runner

Row	runner.name	runner.split
1	Rudisha	23.4

O que você observa sobre os aliases de campos? Como existem campos aninhados dentro do struct (name e split são um subconjunto de runner), você acaba com uma notação de ponto.

E se o corredor tiver vários tempos de volta em uma única corrida (como tempo por volta)?

2. Usaremos uma matriz, é claro! Execute a consulta abaixo para confirmar:

#standardSQL SELECT STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner

Row	runner.name	runner.splits
1	Rudisha	23.4
		26.3
		26.4
		26.1

Recapitulando:

• Structs são contêineres que podem ter vários nomes de campos e tipos de dados aninhados.
• Matrizes podem ser os tipos de campo de um struct (como o campo splits no código acima).

Pratique a ingestão de dados JSON

1. Mude para o painel Análises clássicas e crie um conjunto de dados chamado racing.

2. Crie uma nova tabela chamada race_results.

3. Faça a ingestão desse arquivo JSON do Google Cloud Storage:

cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json

• Origem: selecione Google Cloud Storage no menu suspenso Criar tabela de.
• Selecione o arquivo no bucket do GCS: cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json
• Formato do arquivo: JSONL (JSON delimitado por nova linha)
• Em Esquema, mova o controle deslizante Editar como texto e adicione as seguintes informações:

[ { "name": "race", "type": "STRING", "mode": "NULLABLE" }, { "name": "participants", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "name", "type": "STRING", "mode": "NULLABLE" }, { "name": "splits", "type": "FLOAT", "mode": "REPEATED" } ] } ]

4. Clique em Criar tabela.

5. Depois do job de carregamento, veja o esquema da tabela recém-criada:

Qual campo é o STRUCT? Como você sabe disso?

O campo participants é um STRUCT porque é do tipo RECORD.

Qual campo é uma MATRIZ?

O campo participants.splits é uma matriz de pontos flutuantes dentro do struct participants. Ele tem o modo REPEATED que indica uma matriz. Os valores dessa matriz são chamados de valores aninhados porque estão dentro de um único campo.

Praticar consultas em campos repetidos e aninhados

1. Agora vamos conferir todos os corredores da prova de 800 metros:

#standardSQL SELECT * FROM racing.race_results

Quantas linhas foram retornadas?

Resposta: 1

E se você quisesse listar o nome de cada corredor e o tipo de corrida?

2. Execute o esquema abaixo e descubra o que acontece:

#standardSQL SELECT race, participants.name FROM racing.race_results

Error: Cannot access field name on a value with type ARRAY<STRUCT<name STRING, splits ARRAY<FLOAT64>>> at [2:27]

Isso também acontece quando esquecemos de adicionar GROUP BY com funções de agregação. Temos dois níveis diferentes de granularidade: uma linha para a corrida e três linhas para os nomes dos participantes. Como você mudaria isto:

Row	race	participants.name
1	800 m	Rudisha
2	???	Makhloufi
3	???	Murphy

... para ter isto:

Row	race	participants.name
1	800 m	Rudisha
2	800 m	Makhloufi
3	800 m	Murphy

No SQL relacional tradicional, se você tivesse uma tabela de corridas e uma de participantes, o que você faria para extrair informações das duas? Você usaria JOIN para combiná-las. Aqui, o STRUCT com os participantes (que, conceitualmente, é muito semelhante a uma tabela) já faz parte da tabela de corridas, mas ainda não está associado corretamente ao campo não STRUCT "race".

Você consegue pensar em um comando SQL com duas palavras que você usaria para associar a corrida de 800 m a cada um dos corredores na primeira tabela?

Resposta: CROSS JOIN

Ótimo.

3. Agora execute esta consulta:

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN participants # this is the STRUCT (it's like a table within a table)

Error: Table "participants" must be qualified with a dataset (e.g. dataset.table).

Ainda que o STRUCT com os participantes seja como uma tabela, tecnicamente ele é um campo na tabela racing.race_results.

4. Adicione o nome do conjunto de dados à consulta:

#standardSQL SELECT race, participants.name FROM racing.race_results CROSS JOIN race_results.participants # full STRUCT name

5. Clique em Executar.

Uau! Você listou todos os corredores de cada corrida.

Row	race	name
1	800 m	Rudisha
2	800 m	Makhloufi
3	800 m	Murphy
4	800 m	Bosse
5	800 m	Rotich
6	800 m	Lewandowski
7	800 m	Kipketer
8	800 m	Berian

Para simplificar a última consulta, faça o seguinte:

• Adicione um alias para a tabela original.
• Substitua as palavras "CROSS JOIN" por uma vírgula (implicitamente, uma vírgula realiza essa operação).

Isso retornará o mesmo resultado desta consulta:

#standardSQL SELECT race, participants.name FROM racing.race_results AS r, r.participants

Se você tivesse mais de um tipo de corrida (800 m, 100 m, 200 m), um CROSS JOIN poderia associar cada nome de corredor a todas as corridas possíveis como um produto cartesiano?

Resposta: não. Essa é uma função correlacionada que só descompacta os elementos associados a uma única linha. Para mais detalhes, veja como trabalhar com ARRAYs e STRUCTs.

Clique em Verificar meu progresso para conferir o objetivo. Praticar com structs e matrizes

Recapitulando o que são STRUCTs:

• Um STRUCT SQL nada mais é do que um contêiner com campos de dados que podem ser de diferentes tipos. O termo "struct", que vem da palavra inglesa "structure", significa estrutura de dados. Relembrando o exemplo anterior:
• STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits) AS runner
• Os STRUCTs recebem um alias (como "runner" acima) e podemos entendê-los conceitualmente como uma tabela dentro da sua tabela principal.
• Você precisa descompactar os STRUCTs (e as matrizes) para operar os elementos deles. Coloque um UNNEST() em torno do nome do struct ou do campo do struct que é uma matriz para descompactá-lo e achatá-lo.

Tarefa 7: pergunta do laboratório: STRUCT()

Responda às perguntas abaixo usando a tabela racing.race_results que você criou.

Tarefa: escreva uma consulta para usar COUNT e descobrir quantos corredores participaram da corrida.

• Para começar, use a consulta parcialmente escrita abaixo:

#standardSQL SELECT COUNT(participants.name) AS racer_count FROM racing.race_results Observação: você vai precisar correlacionar o nome do struct a uma outra fonte de dados após FROM.

Possível solução:

#standardSQL SELECT COUNT(p.name) AS racer_count FROM racing.race_results AS r, UNNEST(r.participants) AS p

Row	racer_count.
1	8

Resposta: 8 corredores participaram da corrida.

Tarefa 8: pergunta do laboratório: como descompactar matrizes com UNNEST( )

Escreva uma consulta que listará o tempo total dos corredores com nomes que começam com R. Ordene os resultados começando pelo menor tempo total. Use o operador UNNEST() e comece com a consulta parcialmente escrita abaixo.

• Complete a consulta:

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_times WHERE GROUP BY ORDER BY ; Observação:

Você vai precisar descompactar o struct e a matriz dentro do struct como fontes de dados após a cláusula FROM.

Use aliases quando apropriado.

Possível solução:

#standardSQL SELECT p.name, SUM(split_times) as total_race_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_times WHERE p.name LIKE 'R%' GROUP BY p.name ORDER BY total_race_time ASC;

Row	name	total_race_time.
1	Rudisha	102.19999999999999
2	Rotich	103.6

Tarefa 9: pergunta do laboratório: como fazer filtragens em valores de matriz

Você viu que o tempo de volta mais rápido registrado na corrida de 800 m foi de 23,2 segundos, mas não sabe qual corredor conseguiu essa marca. Crie uma consulta que retorne esse resultado.

• Tarefa – complete esta consulta parcial:

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , r.participants AS p , p.splits AS split_time WHERE split_time = ;

Possível solução:

#standardSQL SELECT p.name, split_time FROM racing.race_results AS r , UNNEST(r.participants) AS p , UNNEST(p.splits) AS split_time WHERE split_time = 23.2;

Row	name	split_time.
1	Kipketer	23.2

Parabéns!

Você ingeriu conjuntos de dados JSON, criou matrizes e structs e usou dados semiestruturados desaninhados para extrair insights.

Próximas etapas / Saiba mais

Como leitura complementar, consulte Como trabalhar com matrizes.

Copyright 2026 Google LLC. Todos os direitos reservados. Google e o logotipo do Google são marcas registradas da Google LLC. Todos os outros nomes de empresas e produtos podem ser marcas registradas das empresas a que estão associados.