Spark SQL加载DD/MM/YYYY格式日期文件时DATE列出现空值问题

问题详情 #

使用Spark SQL将Blob存储中的CSV文件加载到外部表时，格式为DD/MM/YYYY的LastEnDate列被解析为Timestamp类型后全部显示为空值，其余列解析正常。

示例数据集 #

UserId,LastEnDate,AppLoginMessageStartTimest,IsActive,SFlag,Daycount
A23415,14/08/2023,2023-08-14 13:20:16.148+00,Passed,0,3
A23459,15/12/2023,2023-12-15 14:21:11.686+00,Passed,1,4
A23458,14/09/2023,2023-09-14 06:50:15.375+00,Passed,0,5

加载表的Spark SQL代码 #

CREATE TABLE IF NOT EXISTS tempdb.SampleTable
(
UserfId STRING,
LastEnDate TimeStamp,
AppLoginMessageStartTimest TimeStamp,
IsActive STRING,
SFlag BOOLEAN,
Daycount INTEGER
)
USING csv
OPTIONS ( header 'true', firstRowAsHeader 'true', delimiter ',')
LOCATION 'abfss://01raw@nzadlsndhomerawdev01.dfs.core.windows.net/samplefiles/Samplefile.csv';

输出结果 #

LastEnDate列全部显示为空值，其余列数据正常加载。

解决方案 #

Spark默认仅支持解析yyyy-MM-dd或带时分秒的标准Timestamp格式，无法识别DD/MM/YYYY格式，需指定自定义解析规则：

方法1：创建表时直接指定多格式解析 #

在OPTIONS中添加timestampFormats参数，同时适配两种Timestamp列的格式：

CREATE TABLE IF NOT EXISTS tempdb.SampleTable
(
UserfId STRING,
LastEnDate TimeStamp,
AppLoginMessageStartTimest TimeStamp,
IsActive STRING,
SFlag BOOLEAN,
Daycount INTEGER
)
USING csv
OPTIONS ( 
    header 'true', 
    firstRowAsHeader 'true', 
    delimiter ',',
    timestampFormats 'dd/MM/yyyy,yyyy-MM-dd HH:mm:ss.SSSXXX'
)
LOCATION 'abfss://01raw@nzadlsndhomerawdev01.dfs.core.windows.net/samplefiles/Samplefile.csv';

• dd/MM/yyyy对应LastEnDate的日/月/年格式
• yyyy-MM-dd HH:mm:ss.SSSXXX对应AppLoginMessageStartTimest的ISO标准格式

方法2：先加载为字符串再转换格式 #

先将LastEnDate以STRING类型加载，再通过函数转换为Timestamp：

-- 加载原始数据到临时表
CREATE TABLE IF NOT EXISTS tempdb.SampleRawTable
(
UserfId STRING,
LastEnDate STRING,
AppLoginMessageStartTimest TimeStamp,
IsActive STRING,
SFlag BOOLEAN,
Daycount INTEGER
)
USING csv
OPTIONS ( header 'true', firstRowAsHeader 'true', delimiter ',')
LOCATION 'abfss://01raw@nzadlsndhomerawdev01.dfs.core.windows.net/samplefiles/Samplefile.csv';

-- 转换日期格式并创建目标表
CREATE TABLE IF NOT EXISTS tempdb.SampleTable AS
SELECT
    UserfId,
    to_timestamp(LastEnDate, 'dd/MM/yyyy') AS LastEnDate,
    AppLoginMessageStartTimest,
    IsActive,
    SFlag,
    Daycount
FROM tempdb.SampleRawTable;

关键说明 #

• Spark的timestampFormat（单格式）/timestampFormats（多格式）参数遵循Java日期格式语法，用于指定CSV中Timestamp列的解析规则
• 若仅需适配一种特殊格式，使用timestampFormat 'dd/MM/yyyy'即可

内容的提问来源于stack exchange，提问作者Umer