Transforme dados com SQL
A biblioteca SparkSQL, que fornece a estrutura de dataframe, também permite que você use SQL como uma maneira de trabalhar com dados. Com essa abordagem, você pode consultar e transformar dados em dataframes usando consultas SQL e persistir os resultados como tabelas.
Observação
As tabelas são abstrações de metadados sobre arquivos. Os dados não são armazenados em uma tabela relacional, mas a tabela fornece uma camada relacional sobre arquivos no data lake.
Definir tabelas e vistas
As definições de tabela no Spark são armazenadas no metastore, uma camada de metadados que encapsula abstrações relacionais sobre arquivos. Tabelas externas são tabelas relacionais no metastore que fazem referência a ficheiros numa localização de data lake que você especificar. Você pode acessar esses dados consultando a tabela ou lendo os arquivos diretamente do data lake.
Observação
As tabelas externas estão "ligadas de forma solta" aos arquivos subjacentes e apagar a tabela não apaga os arquivos. Isso permite usar o Spark para realizar o trabalho pesado da transformação e, em seguida, persistir os dados no data lake. Depois disso, pode-se eliminar a tabela, e os processos subsequentes podem aceder a estas estruturas otimizadas. Você também pode definir tabelas geridas, para as quais os arquivos de dados subjacentes são armazenados num local de armazenamento gerido internamente associado ao metastore. As tabelas gerenciadas são "fortemente vinculadas" aos arquivos, e soltar uma tabela gerenciada exclui os arquivos associados.
O exemplo de código a seguir salva um dataframe (carregado de arquivos CSV) como um nome de tabela externa sales_orders. Os arquivos são armazenados na pasta /sales_orders_table no data lake.
order_details.write.saveAsTable('sales_orders', format='parquet', mode='overwrite', path='/sales_orders_table')
Usar SQL para consultar e transformar os dados
Depois de definir uma tabela, você pode usar o SQL para consultar e transformar seus dados. O código a seguir cria duas novas colunas derivadas chamadas Ano e Mês e, em seguida, cria uma nova tabela transformed_orders com as novas colunas derivadas adicionadas.
# Create derived columns
sql_transform = spark.sql("SELECT *, YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month FROM sales_orders")
# Save the results
sql_transform.write.partitionBy("Year","Month").saveAsTable('transformed_orders', format='parquet', mode='overwrite', path='/transformed_orders_table')
Os arquivos de dados para a nova tabela são armazenados em uma hierarquia de pastas com o formato de Ano=*NNNN* / Mês=*N*, com cada pasta contendo um arquivo parquet para as encomendas correspondentes por ano e mês.
Consultar o metastore
Como essa nova tabela foi criada no metastore, você pode usar SQL para consultá-la diretamente com a chave mágica %%sql na primeira linha para indicar que a sintaxe SQL será usada conforme mostrado no script a seguir:
%%sql
SELECT * FROM transformed_orders
WHERE Year = 2021
AND Month = 1
Eliminar tabelas
Ao trabalhar com tabelas externas, você pode usar o comando DROP para excluir as definições de tabela do metastore sem afetar os arquivos no data lake. Essa abordagem permite limpar o metastore depois de usar SQL para transformar os dados, ao mesmo tempo em que disponibiliza os arquivos de dados transformados para análise de dados downstream e processos de ingestão.
%%sql
DROP TABLE transformed_orders;
DROP TABLE sales_orders;