Identificando colunas com mais de um tipo de dado em um Data Warehouse
teste
Motivação
Em um data warehouse como o BigQuery existe uma variedade de tabelas, distribuídas em diferentes datasets e que podem pertencer a diferentes projetos. Por se tratar de uma quantidade muito grande de dados, é esperado a ocorrência de eventuais inconsistências no tipo dos mesmos.
Por isso, temos como objetivo aqui identificar as colunas que representam uma mesma variável, mas aparecem com formatos distintos em diferentes tabelas de um projeto.
Nos bancos de dados em SQL, as colunas possuem um tipo de dado conveniente com a função que a sua variável desempenha. Um exemplo simples é imaginar uma tabela que contenha as vendas realizadas em uma loja durante um determinado período.
Essa tabela possui as colunas código_do_produto, quantidade, e preço, como mostrado a seguir
Dado que a variável código_do_produto corresponde a um rótulo único que identifica cada produto, ela pode ser representada com o tipo STRING, já que não há necessidade de aplicar nenhuma operação matemática sobre esses valores.
Já as variáveis quantidade e preço são numéricas e, nesse caso, é conveniente representá-las, por exemplo, pelos tipos INTEGER e FLOAT, respectivamente.
Como em Análise de Dados estamos frequentemente realizando operações entre tabelas, como concatenação e fusão (merge), é necessário que as colunas que representam uma mesma variável tenham do mesmo tipo de dado, do contrário essas operações não funcionarão.
Por exemplo, considerando duas tabelas, VENDAS_1 e VENDAS_2, em que a variável código_do_produto em VENDAS_2 é do tipo INTEGER em vez de STRING.
Caso necessite-se concatenar ambas as tabelas, essa operação não será possível por meio de um comando em SQL, pois o tipo da coluna deve ser o mesmo para colunas com o mesmo rótulo.
No contexto de Big Data, onde temos centenas ou milhares de tabelas em diferentes “diretórios”, a ocorrência desse tipo de inconsistência é quase inevitável.
Por isso, a proposta aqui é identificar essas inconsistências, isto é, identificar todas as variáveis que apresentam mais de um formato dentro de um data warehouse.
Abordagem
A tarefa descrita aqui é realizada por meio de um script em Python para acessar os dados de tabelas armazenadas no BigQuery e portanto a biblioteca de cliente da API do BigQuery foi usada.
Em um primeiro momento, o script mapeia todas as colunas presentes em um projeto específico, que inclui diferentes datasets, onde estão localizadas diferentes tabelas.
Assim, identifica-se os rótulos e tipos de dados das colunas e seleciona-se apenas aquelas que apresentam mais de um tipo de dado dentro do ambiente, armazenando esses registros em uma nova tabela que contém as informações mais relevantes acerca de cada coluna.
Descrição do código
O script percorre cada tabela do BigQuery, lista cada coluna e o seu tipo e armazenar essas observações em um dataframe. Para acessar as informações das tabelas, utilizou-se a biblioteca google-cloud-bigquery e para processá-las de maneira eficiente, a biblioteca pandas.
Para isso, foi criada a função map_fields, que recebe como parâmetro de entrada um objeto da classe BigQuery Client, que por sua vez, tem como entrada o ID do projeto onde estão os datasets investigados.
Essa função lista todos os datasets do projeto por meio do método list_datasets e todas as tabelas de cada dataset por meio do método list_tables, criando o dicionário dataset_dict, cuja chave é o nome do dataset e o valor é a respectiva lista de tabelas, conforme mostra o trecho de código abaixo:
Em seguida (no trecho de código logo abaixo), a função acessa o esquema de cada tabela e obtém o nome de todas as colunas presentes, bem como seus respectivos tipos de dados.
Essas informações são, então, armazenadas em um dataframe, cujas colunas são project, dataset, table, field_name, field_type.
Assim, uma amostra do dataframe resultante terá o seguinte aspecto:
Para processar as informações do dataframe gerado, foram definidas algumas funções e as principais são:
- get_occurrences: Conta o número de ocorrências de cada coluna, agrupados pelo tipo de dado, retornando um dataframe cujas colunas são field_name, field_type e occurrences.
- get_distinct_types: Recebe o dataframe com as ocorrências de cada coluna por tipo de dado e retorna um array somente com as colunas que possuem tipos distintos.
Assim, o array gerado pela função get_distinct_types é usado para filtrarmos o dataframe originado pelo mapeamento de colunas, resultando então em um novo dataframe que contém apenas as colunas de interesse, isto é, as colunas com inconsistências no tipo. Esse será o dataframe de análise.
Verificando as inconsistências
O dataframe de análise contém um pouco mais que 5900 observações sobre as colunas inconsistentes. Agrupando-o pela coluna field_names, temos uma nova tabela que indica a quantidade de ocorrências de cada coluna. Ao todo, temos 307 observações, ou seja, temos 307 variáveis que apresentam tipos diferentes, em que as dez mais recorrentes são mostradas na tabela abaixo.
A coluna TEC_USER_UPT é a que mais aparece dentro do grupo analisado, estando presente em 752 tabelas. Assumindo a importância que essas tabelas têm dentro do projeto, seria útil realizar uma análise minuciosa sobre essas colunas, que inclui verificar a quantidade de vezes que cada tipo aparece, observando qual é o mais recorrente, pois este provavelmente será o padrão para aquela variável. Então, por exemplo, tomando a coluna TEC_USER_UPT e contando o número de ocorrências de cada tipo, temos:
A maior parte das colunas são do tipo STRING, somente uma minoria tem outro tipo de formado, dessa forma, podemos esperar que STRING seja o tipo padrão. Fazendo o mesmo com a variável SCTN_CD (tabela abaixo), temos que a maior parte das colunas é do tipo INTERGER, logo espera-se que esse seja o padrão para a variável.
Quando temos a distribuição dos tipos de dados de forma concentrada em um só valor é mais fácil presumirmos o tipo padrão. No entanto, quando a distribuição é mais uniforme ou quando temos poucas tabelas com a coluna analisada, é mais difícil identificar o tipo correto, sendo necessário consultar o conteúdo do arquivo. Nesse caso, podemos verificar esse conteúdo executando uma consulta SQL por meio do método query da classe BigQuery client, seguido do método to_dataframe, que resulta em um dataframe da tabela
# Exemplo
query = “SELECT SCTN_CD FROM `brlm-web-data.ods.FT_SLS_ONLN_SCTN_3W_AUX` LIMIT 10”df = cliente.query(query).to_dataframe()
Conclusões
Como foi visto, o script fornece uma tabela com os registros das colunas que possuem mais de um tipo de dado. Tendo em vista que a visualização desses dados usando Python requer um conhecimento razoável da linguagem e das suas biblioteca, uma forma mais fácil e intuitiva de investigar esses registros é utilizar ferramentas de visualização. O Qlik Sense, por exemplo, é uma boa alternativa, pois possibilita a geração de tabelas secundárias com filtros de seleção de campo.
Uma vez que conseguimos detectar quais desses tipos estão incorretos, nós podemos reduzir a tabela para uma que contenha apenas os registros das colunas que precisam ser consertadas.
Por fim, cabe destacar que script foi desenvolvido para ser executado em ambiente de máquina virtual da corporação e seu código está disponível no repositório do github, disponível em https://github.com/adeo/lmbr-python-field-occurrences
