Google Big Query: consultas avançadas

Criando procedures - Apresentação

Olá! Meu nome é Victorino Vila e tenho o prazer de ser seu instrutor neste curso de Big Query: Consultas Avançadas.

Audiodescrição: Victorino é um homem de pele clara, cabelos brancos, cavanhaque grisalho e olhos castanhos. Está de camiseta preta. À sua frente, um microfone apoiado por um braço articulado. Ao fundo, uma parede lisa. O ambiente está iluminado por uma luz azulada.

O que aprenderemos?

Neste curso, vamos mergulhar no uso avançado do Big Query, explorando como Procedures e Funções podem transformar a maneira com que você interage com essa ferramenta.

Vamos aprender a encapsular sequências de operações de SQL em Procedures, permitindo funções de várias ações com maior simplicidade. Também vamos aprender a criar as funções UDF, para expandir a capacidade de manipulação de dados do Big Query, além das funções SQL padrão que ele fornece.

Vamos começar este curso com uma visão geral sobre o que são Procedures e UDFs, e por que são essenciais para qualquer profissional de banco de dados.

Para isso, vamos seguir explorando o estudo de caso da empresa Belleza Verde. Neste curso, enfrentaremos um desafio de incluir novas vendas na base de dados, demonstrando a aplicação prática dos conceitos que serão aprendidos durante as aulas.

À medida que avançarmos, vamos criar nossa primeira Procedure para automatizar a criação de registros de novas vendas na base de dados da Belleza Verde.

Vamos também explorar técnicas avançadas, como a geração de identificadores (IDs) únicos para as novas vendas, e a validação da existência de entidades Produto e Cliente antes de inserir a venda na base de dados.

Continuaremos evoluindo essa Procedure principal, desenvolvendo-a ao longo das aulas do curso, fazendo scripts complexos para calcular o custo de produção dos produtos que estão sendo vendidos, e acrescentando a esse custo uma margem de lucro desejada.

Além disso, vamos aprender a simular vendas usando dados aleatórios, onde escolheremos produtos e clientes para essas vendas aleatórias, além de usar essas mesmas funções para criar quantidades aleatórias a serem criadas dentro de um conjunto de datas.

Finalizaremos nosso curso transformando o script principal em uma única Procedure, e que será agendada para ser executada periodicamente.

Tudo pronto para iniciar essa jornada?! Esperamos que gostem deste curso. Um forte abraço e até o próximo vídeo!

Criando procedures - O que é uma procedure e função no BigQuery?

No BigQuery, frequentemente enfrentamos desafios que incluem a execução de sequências de operações SQL complexas e a necessidade de executá-las repetidamente.

Às vezes, precisamos realizar cálculos ou lógicas específicas que vão além da capacidade nativa dos comandos SQL padrão. Também precisamos usar comandos de looping ou de decisão para implementar uma lógica complexa de um cálculo. O SQL padrão não é uma linguagem estruturada que possui, de forma nativa, esses comandos de repetição e decisão.

Então, surge a pergunta: como gerenciar essas operações complexas de modo eficiente? A resposta é: por meio de Funções e Procedures!

O que é uma Procedure?

No BigQuery, uma Procedure é essencialmente um conjunto de instruções SQL que você pode salvar e executar posteriormente, repetidamente. Se você tem uma lógica complexa ou uma sequência de operações que precisa executar várias vezes, uma Procedure pode, com certeza, simplificar bastante esse processo.

As vantagens de usar Procedures incluem:

• Reusabilidade do código;
• Manutenção simplificada desse código (pois fica salvo em algum repositório do BigQuery);
• Capacidade de executar múltiplas operações SQL em uma única chamada;
• Possibilidade de implementar lógicas condicionais e loopings.

Um exemplo de uso desse recurso é criar uma Procedure para atualizar os saldos de inventário no final de cada dia, ou mesmo para processar transações.

A diferença-chave entre Procedures e UDFs é que Procedures não retornam valor de forma direta, e dentro da Procedure podemos executar comandos de modificação do banco de dados. Já as UDFs são projetadas para retornar um valor com base em entradas específicas.

O que é uma função UDF?

UDF é uma sigla do inglês para User Defined Functions (Funções Definidas pelo Usuário).

Em outros cursos da formação, já aprendemos a definição de função. O BigQuery possui nativamente uma série de funções de texto, número, lógica, data e muito mais. As funções UDF são funções personalizadas, não nativas do BigQuery, que você pode criar usando o SQL ou mesmo linguagens externas como o JavaScript para serem usadas depois, dentro das consultas SQL.

Essas funções são incrivelmente úteis quando você precisa realizar operações que não são diretamente suportadas pelas funções SQL padrão do BigQuery.

Os benefícios das UDFs incluem:

• Flexibilidade para a implementação de lógicas complexas;
• Capacidade de processamento de dados e de reutilização dessas lógicas em diferentes consultas.

Um exemplo de uso desse tipo de função é criar uma UDF para calcular o imposto sobre venda de produtos com base em uma determinada regra de negócio específica da sua empresa. Ou pode criar, também, uma função que converte temperaturas Celsius em Fahrenheit.

Diferente das Procedures, as UDFs são projetadas para processar e retornar valores com base nos parâmetros de entrada. E nas UDFs, não conseguimos executar operações que modifiquem o banco de dados.

Portanto, ao integrar Procedures e UDFs em nossas análises dentro do BigQuery, não só simplificamos processos complexos, mas também abrimos novas possibilidades para fazer análises mais profundas e personalizadas nos nossos conjuntos de dados. Essa abordagem nos prepara para enfrentar uma ampla gama de cenários de dados com confiança.

A partir daqui, vamos entender como funcionam Procedures e funções UDFs usando um exemplo prático, que conheceremos no próximo vídeo, usando ainda a base de dados da Belleza Verde.

Criando procedures - Explicando o problema da Belleza Verde

Vamos retomar o estudo de caso que estamos utilizando nesta formação: a empresa Belleza Verde, focada na fabricação e venda de produtos de beleza.

Conjunto de dados da Belleza Verde

Para continuarmos com os exercícios práticos deste curso, é necessário ter o conjunto de dados da Belleza Verde (belleza_verde_vendas) criado dentro da sua instância de BigQuery, com suas tabelas com dados (clientes, fornecedores, materiasprimas, metas, produtos, vendas, vendedores, etc.).

Para ter esse banco de dados, basta ter seguido com sucesso o primeiro curso desta formação de BigQuery. É importante que esses dados estejam iguais aos do resultado final do primeiro curso da formação.

Problema atual

O grande problema da Belleza Verde, que vamos tentar resolver neste curso, consiste em criar novas vendas.

A questão é que não queremos mais incluir as vendas uma a uma usando o comando INSERT, e sim criar um processo para gerar, de forma aleatória, dezenas de novas vendas.

Claro que o objetivo não é criar vendas falsas, mas sim popular essa base de dados com mais períodos para termos uma base de dados mais robusta para praticar e estudar os recursos de análise no BigQuery.

Na realidade, o BigQuery é uma base de dados gerencial e não foi feito para controlar processos. Normalmente, os dados dessas novas vendas viriam de arquivos externos que seriam gravados no BigQuery por meio de processos de ETL.

Mas, neste curso, vamos usar esse problema da criação de novas vendas como uma ilustração para que você entenda como funcionam as funções UDF e procedures no BigQuery. Entendendo como funcionam esses dois componentes, você poderá atender a flexibilidade do BigQuery mesmo fora do seu propósito principal.

O exemplo hipotético da Belleza Verde vai servir como um caso valioso para você entender e aplicar o que vamos aprender neste curso em contextos reais, permitindo abrir novas possibilidades para análise de dados no BigQuery!

Retomando: nosso objetivo é criar um processo para incluir novas vendas na tabela de vendas para termos mais informações nessa base de dados.

Como já mencionado, se executarmos o comando INSERT, inserimos novos dados. Vamos criar uma nova área de script no BigQuery e digitar o comando INSERT INTO com o ID da tabela de vendas (curso-big-query-68805.belleza_verde_vendas.vendas, que podemos consultar na aba de Detalhes dessa tabela) para inserir novos dados nela.

Para inserir uma venda nova, vamos adicionar entre parênteses o nome dos campos: id_venda, id_produto, id_cliente, data, quantidade e preco.

Abaixo, adicionamos o VALUES para inserir os valores desejados nos campos.

INSERT INTO curso-big-query-688865.belleza_verde_vendas.vendas 
(id_venda, id_produto, id_cliente, data, quantidade, preco)
VALUES 
()

Aqui começam os problemas. Por exemplo, o campo id_venda recebe um identificador único para cada venda, e não podemos ter várias vendas com o mesmo identificador.

Se não prestarmos atenção e colocarmos um identificador que já existe, como ele não tem chave primária nem chave estrangeira, o identificador será aceito, mas o dado não estará íntegro.

Então, antes de inserir uma nova venda, precisamos consultar o max() de id_venda (o identificador máximo de venda), na tabela de vendas:

SELECT max(id_venda) FROM curso-big-query-688865.belleza_verde_vendas.vendas

Executando o código acima, conferimos que o maior identificador é o da venda 10.950. Então, sabemos que temos que o identificador da nossa próxima venda adicionada deve ser 10.951.

Mas precisamos também escolher um produto e um cliente, e não podemos escolher pelo nome do produto ou pelo nome do cliente, mas por código. Então, vamos escolher o produto 1 e o cliente 1, por exemplo.

Precisamos também criar uma data. Para isso, vamos também consultar a data máxima:

SELECT max(data) FROM curso-big-query-688865.belleza_verde_vendas.vendas

Executando o comando acima, conferiremos que a última data da base da Belleza Verde é 31 de dezembro de 2023. Então vamos começar a incluir datas a partir do dia primeiro de janeiro de 2024 para novas vendas (2024-01-01).

Também precisamos especificar uma quantidade e um valor, que podem ser 10 e 5, respectivamente.

Então, nosso INSERT ficará assim:

INSERT INTO curso-big-query-688865.belleza_verde_vendas.vendas 
(id_venda, id_produto, id_cliente, data, quantidade, preco)
VALUES 
(10951, 1, 1, '2024-01-01', 10, 5)

Executando o código acima, inserimos uma nova venda na nossa base de dados.

No entanto, note que para criar um simples INSERT, precisamos conferir várias informações na base de dados. Imagine se precisarmos fazer esse INSERT 100 ou 200 vezes!

Será que podemos fazer isso de forma automática, sem precisar de todas essas investigações toda vez que queremos incluir uma nova venda?

É claro que sim! Afinal, o objetivo deste curso é fazer essa automação, e as faremos por meio de Procedures.

Vamos começar no próximo vídeo!

Sobre o curso Google Big Query: consultas avançadas

O curso Google Big Query: consultas avançadas possui 188 minutos de vídeos, em um total de 50 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Criando procedures
• Trabalhando com entidades
• Complementando a manipulação de entidades
• Trabalhando com custos da venda
• Preparando a procedure final