Informações gerais

O BigQuery é um banco de dados de análise NoOps, totalmente gerenciado e de baixo custo desenvolvido pelo Google. 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 utilização. Assim, você fica livre para se concentrar na análise dos dados e encontrar informações relevantes.

Este laboratório mostra etapas detalhadas do trabalho 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ê vai aprender a:

• Ingerir conjuntos de dados JSON.
• Criar MATRIZES e STRUCTS.
• Desaninhar dados semiestruturados em busca de insights.

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.

Abrir 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 IU.

2. Clique em Concluído.

Tarefa 1: criar um conjunto de dados

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 fruit_store. Não mexa nas outras opções (local dos dados, expiração da tabela padrão).

3. Clique em CRIAR CONJUNTO DE DADOS.

Tarefa 2: pratique 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 consideraria imediatamente dividir a tabela acima em duas tabelas separadas, uma com as frutas e outra com as pessoas.

No BigQuery, você adotará uma abordagem mais rápida, que usa dados do tipo matriz para chegar ao seguinte resultado:

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

Confira as diferenças dessa tabela em comparação com outras tabelas que você já conhece.

• Ela só tem duas linhas.
• Há vários valores de campo (frutas) em uma única linha.
• As pessoas estão associadas a mais de um valor de campo.

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, É importante entendermos duas coisas:

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

1. Teste por conta própria. 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', 33] AS fruit_array

Um erro parecido com este será exibido:

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 (apenas strings ou números, por exemplo). Você deve estar se perguntando neste momento: é possível ter uma matriz de matrizes? Sim, é perfeitamente possível. Falaremos disso ainda neste laboratório.

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 conferir a estrutura aninhada.

Faça upload de arquivos JSON

E se você precisasse ingerir um arquivo JSON no BigQuery? Vamos testar isso agora.

Crie uma tabela no conjunto de dados fruit_store.

1. Para criar uma tabela, clique no ícone Ver ações ao lado do conjunto de dados fruit_store e depois em Abrir.

2. Em seguida, clique em Criar tabela no painel à direita.

Observação: talvez você tenha que ampliar a janela do navegador para acessar a opção "Criar tabela".

3. Adicione os seguintes detalhes à tabela:

• Origem: clique em 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/shopping_cart.json.
• Formato do arquivo: JSONL (JSON delimitado por nova linha).

4. Defina fruit_details como o novo nome da tabela.

5. Em Esquema, clique na caixa de seleção Detectar automaticamente.

6. Clique em Criar tabela.

7. Clique na tabela fruit_details.

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

Tarefa 3: armazenar diferentes tipos de dados sobre uma entidade

Sabemos que as matrizes só podem ter dados do mesmo tipo. No entanto, um desses tipos dá suporte a vários nomes e tipos de campos: o STRUCT.

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

1. Para essa consulta, use a sintaxe de um STRUCT e confira 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ê pode observar sobre os nomes de campo? Como existem campos aninhados dentro do struct (name e split são um subconjunto do corredor), você acaba com uma notação de ponto.

E se o corredor tiver vários splits em um só registro? Como você faria para colocar mais de um tempo de split em um registro? Dica: os splits são dados do mesmo tipo numérico.

Resposta: com uma matriz, é claro!

2. 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.split
1	Rudisha	23,4 26,3 26,4 26,1

Recapitulando:

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

Carregue os resultados dos outros corredores em uma nova tabela

1. Crie um novo conjunto de dados chamado racing.

2. Crie uma nova tabela chamada race_results.

3. Faça a ingestão deste arquivo JSON do Google Cloud Storage: cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json.

• Origem: selecione Google Cloud Storage na lista suspensa Criar tabela de.
• Selecione o arquivo do bucket do GCS: cloud-training/data-insights-course/labs/optimizing-for-performance/race_results.json.
• Formato do arquivo: JSONL (JSON delimitado por nova linha) com Nome da tabela definido como race_results.

4. Mova o controle deslizante Editar como texto e adicione o seguinte:

[ { "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" } ] } ]

5. Clique em Criar tabela.

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

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

Resposta: o campo participantes é um STRUCT porque é do tipo RECORD.

Qual campo é a MATRIZ?

Resposta: o campo participants.splits é uma matriz de pontos flutuantes no struct pai 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.

Pratique consultas em campos repetidos e aninhados

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

#standardSQL SELECT * FROM racing.race_results

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 name "participants" missing dataset while no default dataset is set in the request.

Ainda que o STRUCT com os participantes seja como uma tabela, ele é tecnicamente 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. E 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

Faça o seguinte para simplificar a última consulta:

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

6. Isso vai 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. Esta é uma função CROSS JOIN correlacionada que só descompacta os elementos associados a uma única linha. Para uma discussão, consulte a documentação de referência como trabalhar com matrizes e structs.

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. Use 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 o nome do struct ou do campo do struct que é uma matriz em UNNEST () para descompactá-lo e nivelá-lo.

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

Dica: não se esqueça de associar o nome do seu 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 4: descompactar matrizes com UNNEST( )

Agora que você já conhece os structs, é hora de colocar em prática esse mesmo conhecimento de descompactação com algumas matrizes tradicionais.

Não esqueça: o operador UNNEST toma uma matriz e retorna uma tabela, com uma linha para cada elemento na tabela.

Isso vai permitir que você realize operações SQL normais, como:

• Agregar valores em uma matriz.
• Filtrar matrizes para encontrar valores específicos.
• Ordenar e classificar matrizes.

Lembrete: matrizes são listas ordenadas de elementos com dados do mesmo tipo.

Confira abaixo uma matriz de strings com os nomes dos 8 corredores.

['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian']

É possível criar matrizes no BigQuery adicionando colchetes [ ] e valores separados por vírgula.

1. Execute a consulta abaixo e confira quantas linhas são geradas. Será que teremos 8 linhas?

#standardSQL SELECT ['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian'] AS normal_array

Resposta: o resultado é apenas uma linha com os 8 elementos da matriz.

Row	normal_array
1	Rudisha
	Makhloufi
	Murphy
	Bosse
	Rotich
	Lewandowski
	Kipketer
	Berian

Dica: se você já tem um campo sem o formato de matriz, é possível juntar valores para formar uma matriz usando ARRAY_AGG().

Para encontrar os corredores com nomes que começam com a letra M, é necessário descompactar a matriz acima em linhas individuais para usar uma cláusula WHERE.

Descompacte a matriz unindo os elementos ou o nome dela com UNNEST (), como mostrado abaixo.

2. Execute a consulta abaixo e confira quantas linhas são retornadas:

#standardSQL SELECT * FROM UNNEST(['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian']) AS unnested_array_of_names

O seguinte será exibido:

Row	unnested_array_of_names
1	Rudisha
2	Makhloufi
3	Murphy
4	Bosse
5	Rotich
6	Lewandowski
7	Kipketer
8	Berian

Você desaninhou a matriz. Essa operação também é chamada de nivelamento da matriz.

3. Agora, adicione uma cláusula WHERE normal para filtrar essas linhas e execute a consulta:

#standardSQL SELECT * FROM UNNEST(['Rudisha','Makhloufi','Murphy','Bosse','Rotich','Lewandowski','Kipketer','Berian']) AS unnested_array_of_names WHERE unnested_array_of_names LIKE 'M%'

Row	unnested_array_of_names
1	Makhloufi
2	Murphy

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 ;

Dica:

• Você 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 5: filtre os valores da matriz

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

• Complete a consulta parcialmente escrita:

#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.

Finalize o laboratório

Clique em Terminar o laboratório após a conclusão. O Google Cloud Ensina remove os recursos usados e limpa a conta por você.

Você vai poder avaliar sua experiência no laboratório. Basta selecionar o número de estrelas, digitar um comentário e clicar em Enviar.

O número de estrelas indica o seguinte:

• 1 estrela = muito insatisfeito
• 2 estrelas = insatisfeito
• 3 estrelas = neutro
• 4 estrelas = satisfeito
• 5 estrelas = muito satisfeito

Feche a caixa de diálogo se não quiser enviar feedback.

Para enviar seu feedback, fazer sugestões ou correções, use a guia Suporte.

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