# Linguagem de Manipulação de Dados no BigQuery: Como Realizar Consultas SQL Avançadas 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`: ```sql 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`: ```sql 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 ```sql INSERT dataset.Inventory (product, quantity) VALUES ('top load washer', 10), ('front load washer', 20), ('dryer', 30), ('refrigerator', 10), ('microwave', 20), ('dishwasher', 30), ('oven', 5) ``` ```sql 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` ```sql 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` ```sql INSERT dataset.Warehouse (warehouse, state) WITH w AS ( SELECT ARRAY> [('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 ```sql INSERT dataset.DetailedInventory (product, quantity, supply_constrained) SELECT product, quantity, false FROM dataset.Inventory ``` #### `INSERT VALUES` utilizando uma subconsulta ```sql INSERT dataset.DetailedInventory (product, quantity) VALUES ('countertop microwave', (SELECT quantity FROM dataset.DetailedInventory WHERE product = 'microwave')) ``` #### `INSERT` sem utilizar os nomes das colunas ```sql INSERT dataset.Warehouse VALUES('warehouse #4', 'WA'), ('warehouse #5', 'NY') ``` #### Utilização do `INSERT` com tipos `STRUCT` ```sql 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: | product | quantity | supply\_constrained | comments | specifications | | | --- | --- | --- | --- | --- | --- | | front load washer | 20 | false | `[{"created":"2021-02-09","comment":"comment1"}]` | `{"color":"beige","warranty":"1 year","dimensions":{"depth":"35.0","height":"45.0","width":"30.0"}}` | | | top load washer | 10 | false | `[{"created":"2021-02-09","comment":"comment1"}]` | `{"color":"white","warranty":"1 year","dimensions":{"depth":"30.0","height":"40.0","width":"28.0"}}` | | ### `INSERT` com tipos `ARRAY` ```sql CREATE TABLE IF NOT EXISTS dataset.table1 (names ARRAY); 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` ```sql UPDATE dataset.Inventory SET quantity = quantity - 10 WHERE product like '%washer%' ``` #### `UPDATE` usando mesclagens ```sql 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: ```sql 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 ```sql UPDATE dataset.DetailedInventory SET specifications.color = 'white',specifications.warranty = '1 year' WHERE product like '%washer%' ``` Como alternativa, atualize todo o registro: ```sql UPDATE dataset.DetailedInventory SET specifications = STRUCT>('white', '1 year', NULL) WHERE product like '%washer%' ``` #### `UPDATE` de registros repetidos ```sql 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: ```sql DELETE FROM dataset.DetailedInventory WHERE true ``` Segue mais alguns exemplos: #### `DELETE` com cláusula `WHERE` ```sql DELETE dataset.Inventory WHERE quantity = 0 ``` #### `DELETE` com uma subconsulta ```sql DELETE dataset.Inventory i WHERE i.product NOT IN (SELECT product from dataset.NewArrivals) ``` #### `DELETE` com subconsulta, utilizando a cláusula `EXISTS` ```sql 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: ```sql 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". ```sql 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. ```sql 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.