O BigQuery é um serviço de armazenamento e análise de dados em nuvem, que suporta diversas linguagens de manipulação de dados (DML) para realizar consultas SQL avançadas. Com isso, é possível executar consultas complexas em grandes volumes de dados com rapidez e eficiência. Neste artigo, vamos discutir as principais linguagens de manipulação de dados suportadas pela ferramenta e como utilizá-las em suas consultas SQL.

Cláusula SELECT

Ele é usada para selecionar dados de uma tabela. É possível selecionar colunas específicas, filtrar dados com base em condições e aplicar funções de agregação a valores numéricos.

Exemplo de consulta SQL utilizando a cláusula SELECT:

SELECT customer_id, SUM(price) as total_price
FROM orders
WHERE order_date >= '2022-01-01'
GROUP BY customer_id;

Nessa consulta, a cláusula SELECT seleciona o ID do cliente e o total de gastos em pedidos de cada cliente desde o início de 2022. A cláusula GROUP BY é usada para agrupar os dados por ID do cliente, permitindo calcular o total de gastos de cada cliente.

Cláusula INSERT

A cláusula INSERT é usada para inserir novas linhas em uma tabela. É possível inserir dados em uma única linha ou em várias linhas em uma única consulta.

Exemplo simples de uma consulta SQL utilizando a cláusula INSERT:

INSERT INTO customers (id, name, email)
VALUES (123, 'João Silva', 'joao.silva@email.com');

Além disso, podemos utilizar o INSERT com outras cláusulas.

Usando valores explícitos

INSERT dataset.Inventory (product, quantity)
VALUES  ('top load washer', 10),
                ('front load washer', 20),
                ('dryer', 30),
                ('refrigerator', 10),
                ('microwave', 20),
                ('dishwasher', 30),
                ('oven', 5)

INSERT dataset.NewArrivals (product, quantity, warehouse)
VALUES ('top load washer', 100, 'warehouse #1'),
             ('dryer', 200, 'warehouse #2'),
             ('oven', 300, 'warehouse #3')

Utilizando instrução INSERT SELECT

INSERT dataset.Warehouse (warehouse, state)
SELECT *
FROM UNNEST([('warehouse #1', 'WA'),
                         ('warehouse #2', 'CA'),
                         ('warehouse #3', 'WA')])

Utilizando instrução WITH como alternativa ao INSERT SELECT

INSERT dataset.Warehouse (warehouse, state)
WITH w AS (
    SELECT ARRAY<STRUCT<warehouse string, state string>>
    [('warehouse #1', 'WA'),
     ('warehouse #2', 'CA'),
     ('warehouse #3', 'WA')] col
)
SELECT warehouse, state FROM w, UNNEST(w.col)

Copiando conteúdo de uma tabela para outra

INSERT dataset.DetailedInventory (product, quantity, supply_constrained)
SELECT product, quantity, false
FROM dataset.Inventory

INSERT VALUES utilizando uma subconsulta

INSERT dataset.DetailedInventory (product, quantity)
VALUES ('countertop microwave',
        (SELECT quantity FROM dataset.DetailedInventory WHERE product = 'microwave'))

INSERT sem utilizar os nomes das colunas

INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY')

Utilização do INSERT com tipos STRUCT

INSERT dataset.DetailedInventory
VALUES
    ('top load washer', 10, FALSE, [(CURRENT_DATE, "comment1")],("white","1 year",(30,40,28))),
    ('front load washer', 20, FALSE, [(CURRENT_DATE, "comment1")], ("beige","1 year",(35,45,30)))

Dado que a tabela tenha as colunas comments e specifications como um tipo STRUCT, com os valores seguindo a ordem dos campos, ela será incluída. Veja uma saída de uma consulta de exemplo:

INSERT com tipos ARRAY

CREATE TABLE IF NOT EXISTS dataset.table1 (names ARRAY<STRING>);

INSERT INTO dataset.table1 (names) VALUES (["name1","name2"])

Cláusula UPDATE

A cláusula UPDATE é usada para atualizar os valores existentes em uma ou mais colunas em uma tabela. Ao criar consultas com ele, pode vir com outras cláusulas:

• WHERE: as instruções UPDATE precisam incluir a cláusula WHERE com uma condição pelo menos. Caso precise atualizar todas as linhas de uma tabela, use WHERE true.

• FROM: o UPDATE pode utilizar opcionalmente a cláusula FROM, quando precisa especificar quais as linhas serão atualizadas na tabela de destino.

Sobre o FROM, algumas advertências:

1. A cláusula SET ela pode referir a colunas de uma tabela de destino e colunas de qualquer item FROM. Se tiver conflitos de nomes, as referências não qualificadas são tratadas como ambíguas.

2. Se a tabela de destino na cláusula FROM estiver presente, ela vai precisar ter um alias se quiser executar mesclagem automaticamente

3. Se uma linha de tabela estiver mesclada com zero linhas da cláusula FROM, ela não seria atualizada

4. Se uma linha de tabela estiver mesclada exatamente com uma linha da cláusula FROM, ela será atualizada

5. Se uma linha de tabela estiver mesclada com mais de uma linha da cláusula FROM, a consulta gerará o seguinte erro: UPDATE/MERGE must match at most one source row for each target row.

Segue alguns exemplos de consultas SQL utilizando a cláusula UPDATE:

UPDATE com a cláusula WHERE

UPDATE dataset.Inventory SET quantity = quantity - 10 WHERE product like '%washer%'

UPDATE usando mesclagens

UPDATE dataset.Inventory
SET quantity = quantity + (SELECT quantity FROM dataset.NewArrivals 
                     WHERE Inventory.product = NewArrivals.product),
    supply_constrained = false
WHERE product IN (SELECT product FROM dataset.NewArrivals)

Uma alternativa é associar as tabelas:

UPDATE dataset.Inventory i
SET quantity = i.quantity + n.quantity, supply_constrained = false
FROM dataset.NewArrivals n
WHERE i.product = n.product

UPDATE de campos aninhados

UPDATE dataset.DetailedInventory
SET specifications.color = 'white',specifications.warranty = '1 year'
WHERE product like '%washer%'

Como alternativa, atualize todo o registro:

UPDATE dataset.DetailedInventory
SET specifications = STRUCT<color STRING, warranty STRING, dimensions STRUCT<depth FLOAT64, height FLOAT64, width FLOAT64>>('white', '1 year', NULL)
WHERE product like '%washer%'

UPDATE de registros repetidos

UPDATE dataset.DetailedInventory
SET comments = ARRAY(
    SELECT comment FROM UNNEST(comments) AS comment  
    UNION ALL  
    SELECT (CAST('2016-01-01' AS DATE), 'comment1')
)
WHERE product like '%washer%'

Dica
Se a coluna for do tipo STRUCT, o column_name pode referenciar um campo usando a notação de ponto, como por exemplo: struct1.field1.

Cláusula DELETE

A cláusula DELETE é usada para remover linhas específicas de uma tabela. É importante explicar que a cláusula WHERE é obrigatória para esse tipo de instrução. Caso precise excluir todas as linhas de uma tabela, defina o WHERE como true, como no exemplo abaixo:

DELETE FROM dataset.DetailedInventory WHERE true

Segue mais alguns exemplos:

DELETE com cláusula WHERE

DELETE dataset.Inventory WHERE quantity = 0

DELETE com uma subconsulta

DELETE dataset.Inventory i
WHERE i.product NOT IN (SELECT product from dataset.NewArrivals)

DELETE com subconsulta, utilizando a cláusula EXISTS

DELETE dataset.Inventory
WHERE NOT EXISTS  
    (SELECT * from dataset.NewArrivals WHERE Inventory.product = NewArrivals.product)

Cláusula TRUNCATE TABLE

A cláusula TRUNCATE TABLE é uma operação que permite remover todos os dados de uma tabela de uma só vez. Ao contrário da cláusula DELETE, que remove linhas específicas de uma tabela, a cláusula TRUNCATE TABLE remove todos os dados da tabela, sem levar em consideração nenhuma condição específica. Portanto, é importante ter cuidado ao utilizar essa cláusula para evitar a perda acidental de dados importantes.

A seguir, um exemplo de como usar a cláusula TRUNCATE TABLE no BigQuery:

TRUNCATE TABLE dataset.Inventory

Cláusula MERGE

A cláusula MERGE é uma operação suportada pelo BigQuery que combina as operações de INSERT, UPDATE e DELETE em uma única consulta SQL. Essa operação é usada para combinar dados de várias tabelas em uma única tabela, ou para atualizar ou inserir novas linhas em uma tabela existente com base em uma condição de correspondência.

Ela é útil quando se trabalha com grandes volumes de dados em diferentes fontes, pois permite combinar dados de forma mais eficiente e precisa. O BigQuery permite que você execute consultas MERGE usando instruções SQL padrão, o que torna a integração de dados mais fácil e eficiente.

Suponha que você tenha duas tabelas, "customers_old" e "customers_new", que contêm informações sobre clientes. A tabela "customers_old" contém informações desatualizadas, enquanto a tabela "customers_new" contém informações atualizadas. Você pode usar a cláusula MERGE para combinar as duas tabelas e atualizar as informações desatualizadas na tabela "customers_old".

MERGE customers_old AS target
USING customers_new AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET target.name = source.name, target.email = source.email
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email)
  VALUES (source.customer_id, source.name, source.email);

Nessa consulta, a cláusula MERGE combina as tabelas "customers_old" e "customers_new" com base no campo "customer_id". A cláusula WHEN MATCHED é usada para atualizar as informações desatualizadas na tabela "customers_old" com as informações atualizadas da tabela "customers_new". A cláusula WHEN NOT MATCHED é usada para inserir novas linhas na tabela "customers_old" com informações da tabela "customers_new" que não existiam anteriormente.

Vamos a um outro exemplo. Suponha que você tenha uma tabela chamada "orders", que contém informações sobre pedidos de clientes, incluindo o ID do cliente, a data do pedido e o valor total. Você pode usar a cláusula MERGE para inserir novos pedidos na tabela "orders" ou atualizar os valores dos pedidos existentes com base no ID do cliente e na data do pedido.

MERGE orders AS target
USING (
  SELECT customer_id, order_date, SUM(price) as total_price
  FROM new_orders
  GROUP BY customer_id, order_date
) AS source
ON target.customer_id = source.customer_id AND target.order_date = source.order_date
WHEN MATCHED THEN
  UPDATE SET target.total_price = source.total_price
WHEN NOT MATCHED THEN
  INSERT (customer_id, order_date, total_price)
  VALUES (source.customer_id, source.order_date, source.total_price);

Nessa consulta, a cláusula MERGE combina a tabela "orders" com uma subconsulta "new_orders", que contém informações sobre novos pedidos de clientes. A cláusula ON é usada para comparar o ID do cliente e a data do pedido em ambas as tabelas. A cláusula WHEN MATCHED é usada para atualizar os valores de pedidos existentes na tabela "orders" com os valores da subconsulta "new_orders". A cláusula WHEN NOT MATCHED é usada para inserir novos pedidos na tabela "orders" que não existiam anteriormente.

Para quem já tem familiaridade com bancos relacionais e SQL, vai se identificar com a maioria dos comandos aqui, e isso é bom, porque a curva de aprendizado é bem menor.