Carregamento de arquivos CSV complexos para o BigQuery com o Planilhas Google



Criação de um pipeline de ELT com o Planilhas Google como intermediário

O BigQuery oferece a capacidade de importar rapidamente um arquivo CSV por meio da interface de usuário da Web e da linha de comando:

 01 bq load --source_format CSV --autodetect \ 
 02 mydataset.mytable ./myfile.csv0  

Limitações da detecção automática e da importação

Isso funciona para os arquivos CSV básicos, mas pode não funcionar nos arquivos CSV complexos. Como exemplo de um arquivo onde isso não funcionaria, vamos analisar um conjunto de dados de aluguéis do Airbnb em Nova York da Kaggle. Esse conjunto de dados tem 16 colunas, mas uma delas consiste basicamente em texto de formato livre. Isso significa que ela pode conter emojis, caracteres de quebra de linha…

Na verdade, se você tentar abrir esse arquivo com o BigQuery:

 01 bq load --source_format CSV --skip_leading_rows 1 \ 
 02    advdata.airbnb_nyc_2019 AB_NYC_2019.csv \ 
 03 id:string,name:string,host_id:string,host_name:string,neighbourhood_group:string,neighbourhood:string,latitude:float,longitude:float,room_type:string,price:float,minimum_nights:integer,number_of_reviews:integer,last_review:string,reviews_per_month:float,calculated_host_listings_count:integer,availability_365:integer  

receberá erros como:

 01 Error while reading data, error message: Error detected while 
 02 parsing row starting at position: 104942. Error: Missing close 
 03 double quote (") character.  

Isso ocorre porque uma linha do arquivo é dividida em várias linhas de texto. Portanto, as aspas de abertura em uma linha não têm um fechamento. Esse não é um problema fácil de resolver. Muitas ferramentas têm dificuldades com arquivos CSV que contêm quebras de linha dentro de células.

Planilhas ao resgate

O Planilhas Google, por outro lado, tem um mecanismo de importação de CSV muito melhor. Você abre uma planilha Google, importa o arquivo CSV e voilà…

O bacana é que, com uma planilha Google, você pode fazer a preparação interativa dos dados na planilha antes de carregá-la no BigQuery.

Comece excluindo a primeira linha (o cabeçalho) da planilha. Não queremos que ela faça parte dos dados.

ELT de uma planilha Google

Uma vez no Planilhas Google, podemos usar um truquezinho útil: o BigQuery consegue consultar diretamente as Planilhas Google! Para isso, definimos a planilha Google como uma tabela no BigQuery:

Etapas na IU do BigQuery

• Selecione um conjunto de dados e clique em Create Table.
• Selecione Drive como a origem e especifique a planilha Google no URL da unidade.
• Defina Google Sheet como o formato de arquivo.
• Dê um nome para a tabela. Eu usei o nome airbnb_raw_googlesheet.
• Especifique o esquema:

 01 id:string,name:string,host_id:string,host_name:string,neighbourhood_group:string,neighbourhood:string,latitude:float,longitude:float,room_type:string,price:float,minimum_nights:integer,number_of_reviews:integer,last_review:string,reviews_per_month:float,calculated_host_listings_count:integer,availability_365:integer  

Essa tabela não copia os dados da planilha. Ela consulta a planilha ao vivo.

Então, vamos copiar os dados da forma como estão para o BigQuery (é claro que podemos fazer também algumas transformações):

 01 CREATE OR REPLACE TABLE advdata.airbnb_nyc_2019 AS 
 02 SELECT * FROM advdata.airbnb_raw_googlesheet  

Como automatizar

Você pode automatizar essas etapas:

1. Este artigo mostra como ler um arquivo CSV no Planilhas usando o Python.
2. A partir daí, use o dataform.co ou os scripts do BigQuery para definir a tabela do BigQuery e fazer o ELT.

Para importar arquivos CSV complexos para o BigQuery, crie um pipeline de ELT usando o Planilhas Google como intermediário. Com isso, você pode lidar com arquivos CSV com quebras de linha e outros caracteres especiais nas colunas. Divirta-se!

Artigo relacionado

[New blog series] BigQuery explained: An overview

Nossa nova série de blogs fornece uma visão geral do que é possível com o BigQuery.

Leia o artigo