Análise e Exploratória e Machine Learning com BigQuery [Parte I]

Published in

gb.tech

9 min readMay 26, 2023

Recentemente li numa newsletter do CEO e fundador do Grupo Alura, Paulo Silveira, que SQL é a maior e mais demandada linguagem de programação do mundo. Mesmo que não seja a linguagem principal nos anúncios de vagas, todos que “pessoas que trabalham com dashboards, KPIs, ciência de dados, também precisam dela [SQL]”

Pensando nisso, decidi escrever esse post com o intuito de disseminar ainda mais essa linguagem que me ajuda tanto no dia a dia como cientista de dados, explorando novas funcionalidades desenvolvidas graças ao aperfeiçoamento e incremento de frameworks de banco de dados. Escolhi o Google BigQuery pois é uma ferramenta robusta e que conta com várias funcionalidades pouco exploradas na comunidade.

Nesse artigo, vou demonstrar como é possível fazer uma breve análise exploratória de dados (EDA) com BigQuery. Além disso, vamos utilizar os insights obtidos com a EDA para criar um modelo de machine learning usando apenas BigQuery, no próximo artigo.

EDA

O processo de EDA é fundamental no dia a dia dos cientistas de dados, pois é onde se realiza primordialmente a limpeza e tratamento dos dados, verifica relação entre variáveis, visualiza a distribuição dos dados e garante o entendimento da base. É um processo considerado essencial e que geralmente demanda tempo, principalmente quando o volume de dados utilizado é muito grande. A seguir mostro como construir uma EDA com BigQuery considerando:

Informações gerais sobre a tabela
Resumo estatístico dos dados.
Outliers
Identificação e tratamento de valores nulos
Matriz de correlação
Boxplots e Histogramas

Os dados

A primeira coisa de que precisamos são os dados. Neste artigo, vou usar um dataset bastante conhecido na comunidade e que pode ser acessado diretamente do diretório de dados público do BigQuery, que contém informações sobre espécies de pinguins:

bigquery-public-data.ml_datasets.penguins

Primeiro, vamos ver as primeiras linhas da tabela

SELECT * FROM `bigquery-public-data.ml_datasets.penguins` LIMIT 5

Print da tabela com as 5 primeiras observações, mostrando todas as colunas e seus respectivos valores.

Informações gerais sobre a tabela

Agora, utilizando os metadados da tabela contidos no INFORMATION_SCHEMA da tabela vamos contar a quantidade de colunas e de linhas da tabela:

-- QTD COLUNAS E REGISTROS
SELECT 
  count(distinct column_name) as qtd_colunas,
 (select  count(*) from  `bigquery-public-data.ml_datasets.census_adult_income`) as record_count
FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'penguins';

Print da quantidade de colunas e registros da tabela

Com o mesmo método, podemos observar outras informações da tabela, como nome das colunas, tipo do campo, etc.

-- VISUALIZANDO ALGUMAS INFORMAÇÕES SOBRE A TABELA
SELECT
*
EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'penguins'

Print das informações sobre as colunas da tabela utilizada

Uma prática comum é analisar a quantidade de valores distintos por variável, principalmente para variáveis categóricas. Abaixo temos uma maneira de calcular essa métrica:

-- QTD DE VALORES DISTINTOS POR COLUNAS
DECLARE columns ARRAY<STRING>;
DECLARE query STRING;
SET columns = (
 WITH all_columns AS (
   SELECT column_name
   FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
   WHERE table_name = 'penguins'
 )
 SELECT ARRAY_AGG((column_name) ) AS columns
 FROM all_columns
);

SET query = (select STRING_AGG('(select count(distinct '||x||')  from `bigquery-public-data.ml_datasets.penguins`) '||x ) AS string_agg from unnest(columns) x );
EXECUTE IMMEDIATE
"SELECT  "|| query
;

Print da quantidade de valores distintos por variável

Resumo estatístico dos dados

A análise de distribuição dos dados normalmente é feita inicialmente observando-se um resumo estatístico dos dados, como a média, mediana, mínimo, máximo, etc. Abaixo temos o cálculo resumido para as variáveis numéricas:

-- DESCRIBE DOS DADOS
DECLARE columns ARRAY<STRING>;
DECLARE query1, query2, query3, query4, query5, query6, query7, query8 STRING;
SET columns = (
 WITH all_columns AS (
   SELECT column_name
   FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
   WHERE table_name = 'penguins'
       and  data_type IN ('INT64','FLOAT64')
 )
 SELECT ARRAY_AGG((column_name) ) AS columns
 FROM all_columns
);

SET query1 = (select STRING_AGG('(select round(stddev( '||x||'),2)  from `bigquery-public-data.ml_datasets.penguins`) '||x ) AS string_agg from unnest(columns) x );
SET query2 = (select STRING_AGG('(select round(avg( '||x||'),2)  from `bigquery-public-data.ml_datasets.penguins`) '||x ) AS string_agg from unnest(columns) x );
SET query3 = (select STRING_AGG('(select round(PERCENTILE_CONT( '||x||', 0.5) over(),2)  from `bigquery-public-data.ml_datasets.penguins` limit 1) '||x ) AS string_agg from unnest(columns) x );
SET query4 = (select STRING_AGG('(select round(PERCENTILE_CONT( '||x||', 0.25) over(),2)  from `bigquery-public-data.ml_datasets.penguins` limit 1) '||x ) AS string_agg from unnest(columns) x );
SET query5 = (select STRING_AGG('(select round(PERCENTILE_CONT( '||x||', 0.75) over(),2)  from `bigquery-public-data.ml_datasets.penguins` limit 1) '||x ) AS string_agg from unnest(columns) x );
SET query6 = (select STRING_AGG('(select max( '||x||')  from `bigquery-public-data.ml_datasets.penguins`) '||x ) AS string_agg from unnest(columns) x );
SET query7 = (select STRING_AGG('(select min( '||x||')  from `bigquery-public-data.ml_datasets.penguins`) '||x ) AS string_agg from unnest(columns) x );
SET query8 = (select STRING_AGG('(select countif( '||x||' is null)  from `bigquery-public-data.ml_datasets.penguins`) '||x ) AS string_agg from unnest(columns) x );

EXECUTE IMMEDIATE (
"SELECT 'stddev' ,"|| query1 || " UNION ALL " ||
"SELECT 'mean'   ,"|| query2 || " UNION ALL " ||
"SELECT 'median' ,"|| query3 || " UNION ALL " ||
"SELECT '0.25' ,"|| query4 || " UNION ALL " ||
"SELECT '0.75' ,"|| query5 || " UNION ALL " ||
"SELECT 'max' ,"|| query6 || " UNION ALL " ||
"SELECT 'min' ,"|| query7 || " UNION ALL " ||
"SELECT 'null_values' ,"|| query8
);

Print do resumo estatístico dos dados, com a média, mediana, quartis e desvio padrão dos dados.

Identificação e remoção de Outliers e nulos

A identificação de outliers é outro ponto importante de ser observado pois geralmente são registros da base que podem enviesar análises de comportamentos gerais dos dados, como cálculos de média, por exemplo, e até trazer ruídos indesejados no desenvolvimento de modelos de machine learning. Uma das formas de se identificar outliers é pela amplitude interquartil. Abaixo demonstro como identificar outliers por esse método:

-- OUTLIERS
-- SÓ PRA VISUALIZAR APENAS UMA VARIÁVEL
DECLARE lower, upper, Q1, Q3, INTERVALO FLOAT64;
SET Q1 = (SELECT MAX(var)
         FROM (SELECT culmen_length_mm AS var, NTILE(4) OVER(ORDER BY culmen_length_mm ASC) AS Q from `bigquery-public-data.ml_datasets.penguins`)
         WHERE Q = 1
         );
SET Q3 = (SELECT MAX(var)
         FROM (SELECT culmen_length_mm AS var, NTILE(4) OVER(ORDER BY culmen_length_mm ASC) AS Q from `bigquery-public-data.ml_datasets.penguins`)
         WHERE Q = 3
         );
SET lower = (SELECT DISTINCT (Q1 - ((Q3 - Q1) * 1.5 )) FROM UNNEST([NULL]));
SET upper = (SELECT DISTINCT (Q3 + ((Q3 - Q1) * 1.5 )) FROM UNNEST([NULL]));
EXECUTE IMMEDIATE (
   "SELECT * from `bigquery-public-data.ml_datasets.penguins` WHERE culmen_length_mm >"|| upper ||" OR culmen_length_mm < " || lower
);

A base de dados que estamos utilizando não possui outliers identificados pelo método selecionado, por isso a query acima não retorna nenhum valor.

Dependendo do caso, é desejável a exclusão dos outliers. Para fazer isso com BigQuery, temos a query abaixo, onde é necessário apenas alterar o nome da coluna desejada ao final da query:

-- DELATAR OUTLIERS
DECLARE lower, upper, Q1, Q3, INTERVALO FLOAT64;
SET Q1 = (SELECT MAX(var)
         FROM (SELECT culmen_length_mm AS var, NTILE(4) OVER(ORDER BY culmen_length_mm ASC) AS Q from `bigquery-public-data.ml_datasets.penguins`)
         WHERE Q = 1
         );
SET Q3 = (SELECT MAX(var)
         FROM (SELECT culmen_length_mm AS var, NTILE(4) OVER(ORDER BY culmen_length_mm ASC) AS Q from `bigquery-public-data.ml_datasets.penguins`)
         WHERE Q = 3
         );
SET lower = (SELECT DISTINCT (Q1 - ((Q3 - Q1) * 1.5 )) FROM UNNEST([NULL]));
SET upper = (SELECT DISTINCT (Q3 + ((Q3 - Q1) * 1.5 )) FROM UNNEST([NULL]));
EXECUTE IMMEDIATE (
   "DELETE FROM `bigquery-public-data.ml_datasets.penguins` WHERE culmen_length_mm >"|| upper ||" OR culmen_length_mm < " || lower
);

Identificar e tratar valores nulos é igualmente relevante, pois a grande maioria dos algoritmos de machine learning não lidam com dados nulos. Existem vários tipos de tratamentos deste tipo de problema. Para citar alguns exemplos comuns, temos: imputação de valores médios ou medianos para variáveis numéricas; imputação da classe mais frequente para variáveis categóricas; ou até mesmo a exclusão do registro (linha) caso alguma coluna possua valor nulo. As queries abaixo demonstram a aplicação desta última solução, que primeiramente identifica os registros nulos e posteriormente os deleta da base.

Identificação de nulos

-- IDENTIFICANDO NULOS
DECLARE columns ARRAY<STRING>;
DECLARE query STRING DEFAULT '';
DECLARE i INT64 DEFAULT 0;

SET columns = (
 WITH all_columns AS (
   SELECT column_name
   FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
   WHERE table_name = 'penguins'
       -- and  data_type IN ('INT64','FLOAT64')
 )
 SELECT ARRAY_AGG((column_name) ) AS columns
 FROM all_columns
);

 LOOP
     SET i = i + 1;

     IF i > ARRAY_LENGTH(columns) THEN
         LEAVE;
     END IF;

     IF i > 1 THEN
         SET query = query || ' OR ';
     END IF;
      SET query = query || ' ' || columns[ORDINAL(i)] || ' is null '  ;
    
 END LOOP;

EXECUTE IMMEDIATE (
       ' SELECT * FROM  `bigquery-public-data.ml_datasets.penguins` WHERE ' || query
   );

Print do resultado da consulta que indentifica registros que tenham pelo menos alguma coluna com um valor nulo

Deleção de nulos

Para deletar os registros que possuem algum valor nulo, temos a query abaixo que faz isso automaticamente, verificando cada coluna e deletando o respectivo registro por completo:

-- DELETANDO NULOS
DECLARE columns ARRAY<STRING>;
DECLARE query STRING DEFAULT '';
DECLARE i INT64 DEFAULT 0;

SET columns = (
 WITH all_columns AS (
   SELECT column_name
   FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
   WHERE table_name = 'penguins'
       -- and  data_type IN ('INT64','FLOAT64')
 )
 SELECT ARRAY_AGG((column_name) ) AS columns
 FROM all_columns
);

 LOOP
     SET i = i + 1;

     IF i > ARRAY_LENGTH(columns) THEN
         LEAVE;
     END IF;

     IF i > 1 THEN
         SET query = query || ' OR ';
     END IF;
      SET query = query || ' ' || columns[ORDINAL(i)] || ' is null '  ;
    
 END LOOP;

EXECUTE IMMEDIATE (
       'DELETE FROM `bigquery-public-data.ml_datasets.penguins` WHERE ' || query
   );

Matriz de correlação

Outro passo importante durante o processo de exploração dos dados é a análise de correlação entre as variáveis. Um método comum de se encontrar relação entre variáveis é a matriz de correlação. Abaixo demonstramos como calcular a correlação de Pearson entre variáveis numéricas da base.

-- MATRIZ DE CORRELAÇÃO

DECLARE columns ARRAY<STRING>;
DECLARE query STRING DEFAULT '';
DECLARE i,j INT64 DEFAULT 0;

SET columns = (
 WITH all_columns AS (
   SELECT column_name
   FROM `bigquery-public-data.ml_datasets.INFORMATION_SCHEMA.COLUMNS`
   WHERE table_name = 'penguins'
       --and column_name not in ()
       and  data_type IN ('INT64','FLOAT64')
 )
 SELECT ARRAY_AGG((column_name) ) AS columns
 FROM all_columns
);

LOOP
   SET i = i + 1;

   IF i > ARRAY_LENGTH(columns) THEN
       LEAVE;
   END IF;

   IF i > 1 THEN
       SET query = query || ' UNION ALL ';
   END IF;
       SET query = query || ' SELECT '|| i || ","||"'"||columns[ORDINAL(i)]|| "'" ;
      
       LOOP
           SET j = j + 1;

           IF j > ARRAY_LENGTH(columns) THEN
               LEAVE;
           END IF;
           SET query = query || ' , round(corr('|| columns[ORDINAL(i)] ||','|| columns[ORDINAL(j)] ||'),4)  as ' ||" "||columns[ORDINAL(j)]|| " "  ;
       END LOOP;
       SET query = query || ' FROM  `bigquery-public-data.ml_datasets.penguins` ' ;
       SET j = 0;
  
END LOOP;

EXECUTE IMMEDIATE (
       query || " order by 1;"
   );

Print da matriz de correlação de Pearson entre as variáveis numéricas.

Boxplots e Histogramas

Para construção do boxplot e histogramas de forma visual, bem como análises bivariadas (que cruzam cada variável com a variável alvo) recomendo a utilização da ferramenta Looker Studio (antigo Data Studio), que é integrado a plataforma GCP e é muito fácil de usar. Com uma interface interativa no code, é possível criar dashboards exploratórios com grandes volumes de dados.

No entanto, se o seu objetivo pode ser alcançado apenas observando valores, sem a ajuda visual dos gráficos, é possível construir boxplots com SQL calculando os seus componentes: quartis e mediana.

-- BOXPLOTS
select
species
,stat
,avg(min) min
,avg(q25) q25
,avg(median) median
,avg(q75) q75
,avg(max) max
from (
   SELECT species, 'culmen_length_mm' as stat ,
 PERCENTILE_CONT(culmen_length_mm, 0) OVER(partition by species)     AS min,
 PERCENTILE_CONT(culmen_length_mm, 0.25) OVER(partition by species)  AS q25,
 PERCENTILE_CONT(culmen_length_mm, 0.5) OVER(partition by species)   AS median,
 PERCENTILE_CONT(culmen_length_mm, 0.75) OVER(partition by species)  AS q75,
 PERCENTILE_CONT(culmen_length_mm, 1) OVER(partition by species)     AS max
 FROM `bigquery-public-data.ml_datasets.penguins`
)
 group by 1,2;

Print do calculo dos intervalos interquartis e da mediana das colunas numéricas para construção do boxplot.

Da mesma forma, para observar distribuição de variáveis contínuas por meio de histogramas, basta fazer agregações nas colunas desejadas, como por exemplo:

-- HISTOGRAMA
SELECT count(*) frequency, bucket
FROM (
   SELECT species, round(culmen_length_mm / 10)* 10 as bucket FROM `bigquery-public-data.ml_datasets.penguins`)
GROUP BY bucket
order by 2;

Print do resultado do calculo de frequência de uma variável por buckets para plotar histogramas.

Conclusão

Neste artigo, vimos como criar uma breve análise exploratória de dados utilizando apenas SQL, utilizando seus recursos avançados e pouco explorados durante o processo de EDA, e que pode ser muito útil quando se está lidando com bases de dados muito volumosas, que consumiram muito recurso computacional em um Jupyter Notebook, por exemplo.

No próximo artigo, vamos criar um modelo de machine learning com a base de dados que tratamos neste post, utilizando apenas SQL no Google Big Query.