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.
Nota
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. As tabelas externas são tabelas relacionais no metastore que fazem referência a arquivos em um local de data lake que você especificar. Você pode acessar esses dados consultando a tabela ou lendo os arquivos diretamente do data lake.
Nota
As tabelas externas são "frouxamente vinculadas" aos arquivos subjacentes e excluir a tabela não exclui os arquivos. Isso permite que você use o Spark para fazer o trabalho pesado da transformação e, em seguida, persista os dados no lago. Depois disso, você pode soltar a tabela e os processos downstream podem acessar essas estruturas otimizadas. Você também pode definir tabelas gerenciadas , para as quais os arquivos de dados subjacentes são armazenados em um local de armazenamento gerenciado 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 Year=*NNNN* / Month=*N*, com cada pasta contendo um arquivo parquet para as ordens 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
Mesas suspensas
Ao trabalhar com tabelas externas, você pode usar o DROP comando 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;