使用 SQL 转换数据

  • 5 分钟

SparkSQL 库提供了数据帧结构，还可以使用 SQL 作为处理数据的方式。 使用此方法，可以使用 SQL 查询在数据帧中查询和转换数据，并将结果保留为表。

注释

表是基于文件的元数据抽象。 数据并不是存储在关系型表中，但该表在数据湖中的文件上提供了一个关系层。

定义表和视图

Spark 中的表定义存储在 元存储中，这是一个元数据层，用于封装文件的关系抽象。 外部表是元存储中的关系表，用于引用指定的 Data Lake 位置中的文件。 可以通过查询表或通过直接从 Data Lake 读取文件来访问此数据。

注释

外部表与基础文件“松散绑定”，删除表 不会 删除文件。 这样，便可以使用 Spark 执行转换的繁重作，然后将数据保留在湖中。 完成此作后，可以删除表，下游进程可以访问这些优化的结构。 还可以定义 托管 表，其中基础数据文件存储在与元存储关联的内部托管存储位置中。 托管表与文件“紧密绑定”，删除托管表会删除关联的文件。

下面的代码示例将数据帧（从 CSV 文件加载）保存为外部表名 sales_orders。 这些文件存储在 data lake 中的 /sales_orders_table 文件夹中。

order_details.write.saveAsTable('sales_orders', format='parquet', mode='overwrite', path='/sales_orders_table')

使用 SQL 查询和转换数据

定义表后，可以使用 SQL 查询和转换其数据。 以下代码创建两个名为 year 和 Month 的新派生列，然后使用添加的新派生列创建新表 transformed_orders。

# 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')

新表的数据文件存储在文件夹层次结构中，其格式为 Year=*NNNN* / Month=*N*，其中每个文件夹都包含按年份和月份排列相应订单的 parquet 文件。

查询元存储

由于此新表是在元存储中创建的，因此可以使用 SQL 通过第一行中的 %%sql magic 键直接查询它，以指示将按以下脚本所示使用 SQL 语法：

%%sql

SELECT * FROM transformed_orders
WHERE Year = 2021
    AND Month = 1

删除表

使用外部表时，可以使用 DROP 命令从元存储中删除表定义，而不会影响 Data Lake 中的文件。 使用此方法可以在使用 SQL 转换数据后清理元存储，同时使转换后的数据文件可用于下游数据分析和引入过程。

%%sql

DROP TABLE transformed_orders;
DROP TABLE sales_orders;