Sintaxe SQL do Google BigQuery: Um guia abrangente

O que é o Google BigQuery? O que ele pode fazer por sua organização? O BigQuery SQL é uma habilidade valiosa? Onde você pode aprender e onde pode praticar?

Atualmente, poucas organizações não são informatizadas. Muitos processos agora fazem uso da IoT (Internet of Things, Internet das Coisas), em que todos os tipos de dispositivos são conectados em rede e alimentam continuamente os sistemas de computador com dados em tempo real. O resultado é uma grande quantidade de dados disponíveis para a tomada de decisões. Isso geralmente é chamado de lago de dados.

Os principais desafios desse fluxo de dados são como e onde armazená-los e qual a melhor forma de disponibilizá-los para os tomadores de decisão. O BigQuery do Google é, sem dúvida, uma das melhores respostas a esses desafios.

As habilidades em SQL do BigQuery são um acréscimo importante ao seu portfólio. Se você quiser aprender BigQuery, por onde deve começar? O BigQuery é compatível com os padrões ANSI SQL, portanto, o primeiro passo é adquirir habilidades básicas em SQL.

Se você ainda não conhece SQL, o curso LearnSQL.com.br 's SQL para Iniciantes o ajudará a ganhar velocidade rapidamente. Você aprenderá conceitos importantes de banco de dados e, ao final do curso, será capaz de extrair, agregar e analisar dados de uma ou mais tabelas para obter informações significativas. O curso leva cerca de 10 horas para ser concluído e inclui 129 exercícios interativos que ensinam você a resolver problemas do mundo real com SQL.

O que é o Google BigQuery?

O Google BigQuery é uma solução de armazenamento de dados sem servidor e altamente escalável. Ele pode analisar terabytes de dados em segundos e petabytes de dados em minutos. Mas o que isso significa de fato?

Armazém de dados

Os dados geralmente são armazenados para uma de duas finalidades:

• Para dar suporte às operações cotidianas de uma empresa
• Apoiar a tomada de decisões.

O primeiro deles é conhecido como dados operacionais e, geralmente, é armazenado por vários sistemas de computador diferentes, pois eles executam várias tarefas necessárias à organização. O segundo é conhecido como dados analíticos. Idealmente, esses dados devem ser armazenados em um local central onde possam ser acessados por qualquer pessoa que precise de informações.

Esse local central é conhecido como data warehouse e deve ser otimizado para pesquisar e analisar rapidamente grandes volumes de dados. Você usaria um processo conhecido como ETL (Extrair, Transformar, Carregar) para transferir dados operacionais para o data warehouse.

Como um data warehouse, o BigQuery tem várias vantagens:

• Possui excelentes ferramentas de ETL.
• Usa o sistema de arquivos Colossus, que foi projetado para ser "grande"; mais espaço pode ser facilmente adicionado quando necessário.
• Usa SQL como linguagem de consulta, portanto é compatível com as principais ferramentas de business intelligence (BI), como
• O Google Data Studio permite que os dados sejam apresentados de várias maneiras diferentes.

Pesquisas rápidas

O BigQuery usa o mecanismo de busca Dremel para processar rapidamente grandes volumes de dados. Como mencionei anteriormente, o BigQuery pode analisar petabytes (um petabyte equivale aproximadamente a um milhão de gigabytes) em minutos. Como ele faz isso?

Cada vez que você executa uma pesquisa no BigQuery, milhares de threads de trabalho dividem a tarefa entre si; isso permite que enormes quantidades de dados sejam examinadas simultaneamente. Os resultados são então reunidos e retornados ao usuário.

Arquitetura sem servidor

Se você usar o BigQuery, não contratará um servidor. Isso significa que você não precisa gerenciar a infraestrutura, preocupar-se com backups e segurança ou descobrir se precisa de mais discos rígidos. Seus dados se tornam parte do grande lago de dados - ou oceano!

Esse oceano de dados digitais é dividido em inúmeros servidores em todo o mundo. Se for necessário mais espaço, o Google o aloca e gerencia. Você só paga pelo que usar.

E quanto à capacidade de processamento necessária para realizar os cálculos? O Google aloca a capacidade de processamento como e quando ela é necessária. O software de rede de alta velocidade do Google, Jupiter, foi especialmente projetado para a comunicação rápida entre threads; você só paga pelos slots de processamento que usar.

E quanto à segurança? Você pode criar exibições para seus dados e especificar exatamente quem pode ver o quê. Como os dados são criptografados, tanto quando armazenados quanto quando em trânsito, eles estão protegidos contra intrusos.

Praticando o BigQuery

Se você já conhece um pouco de SQL, pode experimentar o BigQuery agora mesmo. Se não souber, ainda poderá experimentar alguns dos exemplos da próxima seção para ter uma boa noção do que ele pode fazer. Mas para obter todos os benefícios do BigQuery, vale a pena aprimorar suas habilidades em SQL inscrevendo-se em um curso abrangente de SQL. LearnSQL.com.br A trilha de aprendizado do SQL de A a Z inclui 7 cursos que o levam do iniciante ao especialista.

Onde e como você pode praticar essas habilidades no BigQuery? Qualquer pessoa pode criar um projeto no BigQuery gratuitamente. Você só será cobrado quando quiser carregar e processar grandes quantidades de dados. O sandbox do BigQuery permite que você insira seus próprios dados de teste. Ou você pode acessar uma grande variedade de bancos de dados públicos para brincar.

Para começar, visite a página do BigQuery no Google Cloud Console. Se ainda não estiver conectado a uma conta do Google, poderá ser solicitado a fazê-lo. Na primeira vez que acessar esse console, você verá um aviso como este:

Clique em CREATE PROJECT e dê um nome ao seu projeto ou aceite a sugestão do Google.

Agora você precisará de alguns dados para praticar. Felizmente, o BigQuery tem muitos dados públicos disponíveis. Para acessá-los, você precisará adicionar os conjuntos de dados públicos ao seu SQL Workspace. No canto superior esquerdo da página, você verá o seguinte:

Clique em ADD. Será exibida uma tela pop-up com várias opções. Clique nesta opção:

Você será solicitado a informar o nome do projeto. Digite bigquery-public-data e clique em 'STAR'. Você deverá ver esse projeto listado no explorador de projetos à esquerda da tela. E agora você está pronto para começar a explorar. Se você clicar na seta ao lado de bigquery-public-data, verá uma lista de todos os bancos de dados desse projeto. E se você clicar em um banco de dados, verá uma lista de suas tabelas. Os exemplos na próxima seção deste artigo foram extraídos do banco de dados census_bureau_international.

Se você já tem experiência com SQL, isso é tudo de que precisa para começar. No entanto, se for um novato em SQL, talvez queira praticar um pouco em um ambiente em que a ajuda esteja disponível e você seja orientado a encontrar soluções para os problemas. Nesse caso, eu recomendaria o curso prático do LearnSQL.com.br, no qual você terá a chance de resolver mais de 80 desafios do mundo real.

Sintaxe do BigQuery

Agora que você já sabe como se preparar para o BigQuery, vamos dar uma olhada na sintaxe do BigQuery. Mas, primeiro, como você já deve saber, o SQL tem muitos dialetos diferentes, exclusivos para diferentes produtos de banco de dados. Você pode saber mais sobre os diferentes dialetos SQL aqui.

O ANSI (American National Standards Institute) define os padrões recomendados para SQL; cada dialeto pode ou não implementar todos esses padrões. Muitos deles têm suas próprias adições à linguagem para permitir mais funcionalidade.

Originalmente, o BigQuery tinha uma versão muito fora do padrão do SQL que era exclusiva para suas necessidades. Essa versão agora é chamada de "Legacy SQL" e você só precisaria aprendê-la se quisesse trabalhar em uma empresa que ainda a utilizasse. O BigQuery agora usa o Google SQL, que se aproxima muito do padrão ANSI. Se você já estiver familiarizado com outro dialeto (como o MS SQL Server ou o MySQL), encontrará muito poucas diferenças ao usar o BigQuery.

A principal diferença é que o BigQuery tem alguns recursos estatísticos extras e oferece suporte a estruturas de dados complexas, como JSON e matrizes.

Vamos dar uma olhada em alguns exemplos de execução de consultas no Console do Cloud. Se você nunca usou SQL antes e está achando os exemplos difíceis de seguir, talvez ache útil este artigo sobre os comandos SQL mais importantes.

Você pode encontrar a sintaxe completa de todos os comandos do Google SQL aqui.

Execução de comandos no console do BigQuery Cloud

À direita do explorador de projetos no Console do BigQuery Cloud, você verá uma janela na qual poderá executar comandos do Google SQL. Ela tem a seguinte aparência:

Você digita sua consulta na janela de consulta e, em seguida, clica em 'RUN' na barra de ações na parte superior. Se tiver digitado um comando SQL válido, você verá os dados solicitados na janela de resultados. Caso contrário, você verá uma mensagem de erro.

Comandos SQL simples

A extração e a análise de dados usam apenas um comando SQL: a instrução SELECT. Em sua forma mais simples, o comando é SELECT * FROM tablename, em que tablename é o nome de uma das tabelas em seu banco de dados. O asterisco (*) indica que ele deve retornar todas as colunas da tabela.

No Google SQL, a menos que os dados sejam provenientes do seu próprio projeto, você prefixa o nome da tabela com o nome do projeto e do banco de dados. Nos exemplos, vou acessar os dados do banco de dados census_bureau_international no projeto bigquery-public-data. Uma das tabelas que usarei se chama midyear_population. Precisarei me referir a essa tabela como:

bigquery-public-data.census_bureau_international.midyear_population

Por conveniência, você pode atribuir um alias a esse nome bastante longo para não ter que digitá-lo constantemente. No exemplo abaixo, estou dando a ela o alias midyear.

bigquery-public-data.census_bureau_international.midyear_population AS midyear

Quando estiver testando tabelas que podem ser muito grandes, é uma boa ideia colocar um limite no número de linhas que serão retornadas. Caso contrário, você poderá exceder os limites da versão gratuita do BigQuery.

Para ver tudo nas primeiras 10 linhas dessa tabela, você digitaria este comando na janela de consulta e, em seguida, clicaria em RUN:

SELECT * FROM 
bigquery-public-data.census_bureau_international.midyear_population 
AS midyear
LIMIT 10

Experimente você mesmo. A menos que tenha cometido um erro de digitação, sua tela deverá ter a seguinte aparência:

Você pode usar a barra de rolagem para percorrer os resultados.

Agora vamos dar uma olhada em alguns dos recursos opcionais da declaração SELECT.

• A cláusula WHERE permite que você escolha somente as linhas que atendem a um determinado critério.
• Em vez de mostrar todas as colunas (ou seja, usar o asterisco), você pode listar os nomes das colunas que deseja ver. Na imagem acima, os nomes das colunas (country_code, country_name, year e midyear_population) são mostrados na parte superior de cada coluna na janela de resultados.
• Você pode especificar a ordem das linhas no conjunto de resultados usando a opção ORDER BY

Tente executar esta consulta para ver somente os nomes dos países e as populações de 1975, classificados pelo nome do país.

SELECT
midyear.country_name, midyear_population
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Se tiver feito tudo corretamente, você verá o seguinte:

O SQL tem muito mais recursos. Dois dos mais úteis são:

• Usar funções de agregação, como SUM() para encontrar totais, AVG() para encontrar médias e MIN() e MAX() para encontrar os menores/maiores valores. Os agregados geralmente são usados em conjunto com a cláusula GROUP BY, que define os grupos aos quais as funções agregadas se aplicam. Sem agrupar os dados, você verá os agregados do conjunto de dados como um todo.
• Recuperação de dados de duas ou mais tabelas, desde que elas tenham uma ou mais colunas em comum que possam ser usadas para uni-las.

Vamos experimentar algumas consultas de exemplo. Primeiro:

SELECT
midyear.country_name, AVG(midyear.midyear_population)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
GROUP BY country_name
ORDER BY country_name

Essa consulta mostra a população média do meio do ano para cada país durante todo o período.

A próxima consulta obtém dados de duas tabelas: a tabela midyear_population e a tabela birth_death_growth_rates. Assim, você pode ver a taxa de crescimento junto com a população.

Você precisará unir esses dados em duas colunas correspondentes: o país e o ano. Aqui está a consulta, seguida de seus resultados:

SELECT
midyear.country_name, midyear.midyear_population, 
growth.growth_rate
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
JOIN
bigquery-public-data.census_bureau_international.birth_death_growth_rates
AS growth
ON growth.country_name = midyear.country_name
AND growth.year = midyear.year
WHERE midyear.year = 1975
ORDER BY country_name

Para todas essas consultas, o BigQuery usa a sintaxe padrão ANSI; as consultas funcionariam igualmente bem em qualquer dialeto SQL.

Alguns comandos SQL Avançado

Aprofundar a sintaxe de instruções SQL complexas está além do escopo deste artigo, mas gostaria de incluir algumas consultas avançadas que têm a mesma sintaxe no Google SQL e em outros dialetos (como o MS SQL Server).

As funções de janela são usadas para visualizar linhas individuais em relação a agregados de todo o conjunto de dados. Este exemplo classifica os países por população - o país com a maior população é classificado como 1.

SELECT
midyear.country_name, midyear.midyear_population, RANK() OVER (ORDER BY midyear_population DESC)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Seus resultados devem ter a seguinte aparência:

Outros recursos avançados incluem extensões GROUP BY, que permitem mostrar agregados em mais de um nível. Novamente, a sintaxe do BigQuery segue os padrões ANSI; se você aprendeu esses recursos no MS SQL, poderá usá-los no BigQuery.

Aqui está um exemplo. Isso mostrará os totais nos níveis de idade e país.

SELECT
m.country_name, m.age, SUM(m.population)
FROM
bigquery-public-data.census_bureau_international.midyear_population_agespecific
AS m
WHERE m.year = 1975
GROUP BY ROLLUP (m.country_name, m.age)
ORDER BY m.country_name, m.age

Os resultados têm a seguinte aparência. Os nulos indicam que essa linha de saída é um total.

Recursos adicionais do BigQuery

O BigQuery tem muitos recursos que não são encontrados na maioria dos dialetos. Isso o torna particularmente útil para lidar com big data provenientes de muitas fontes diferentes.

Ele tem vários tipos de dados adicionais:

• Geography - Armazena coordenadas geográficas e pode ser usado para calcular a distância entre dois locais.
• Interval - Armazena um intervalo de tempo.
• JSON - Um formato usado por muitos aplicativos.
• Struct - Um contêiner para armazenar campos ordenados.

O BigQuery também permite que você trabalhe com matrizes.

Ao trabalhar com conjuntos de dados muito grandes, é particularmente útil poder extrair amostras aleatórias de dados. Aqui está um exemplo de extração de uma amostra aleatória da tabela midyear_population.

SELECT
AVG (m.life_expectancy)
FROM
bigquery-public-data.census_bureau_international.mortality_life_expectancy
AS m
TABLESAMPLE SYSTEM (10 PERCENT)

Em vez de percorrer toda a tabela, que é grande, esse procedimento coletará uma amostra estatística de 10% da tabela. Como a amostra é aleatória, você poderá obter resultados ligeiramente diferentes a cada vez que a executar. O resultado pode ser semelhante a este:

Você pode saber mais sobre os recursos adicionais do BigQuery SQL na documentação do produto.

Trabalhando com o Google BigQuery: O próximo passo

O oceano digital cresce a cada ano, e cada vez mais empregadores estão procurando por habilidades em BigQuery. Vale a pena se tornar um especialista nessa área. Mas como?

Aprimore suas habilidades em SQL fazendo alguns cursos. O site LearnSQL.com.br tem uma grande variedade de cursos que abrangem desde tópicos iniciantes até avançados. Você pode procurar por cursos específicos ou simplesmente navegar pelo que temos a oferecer.

LearnSQL.com.brA trilha de prática do SQL da Microsoft ensina você a ser ágil na solução de todos os tipos de problemas com SQL. Você também deve passar muito tempo fazendo experiências com os conjuntos de dados públicos no Google Cloud Console. Se você tem sua própria empresa e gostaria de criar um programa de treinamento em SQL para sua equipe, também podemos ajudá-lo a fazer isso.

O BigQuery veio para ficar, portanto, comece a se tornar um especialista agora mesmo!