Snowflake中Python存储过程拆分xlsx为csv的datetime报错排查

我有一个存储在已集成到Snowflake Stage环境的Blob存储容器中的.xlsx文件，尝试通过Snowflake中的Python存储过程将其拆分为同Stage下的多个.csv文件。编写代码后出现报错，提示需要datetime.datetime类型但获取到datetime.date，即使将.xlsx中的所有数据转为字符串也无法解决，忽略指定工作表后报错依然存在。

错误日志 #

Python Interpreter Error:
Traceback (most recent call last):
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/descriptors/base.py", line 59, in _convert
    value = expected_type(value)
TypeError: an integer is required (got type datetime.date)
 During handling of the above exception, another exception occurred:

 Traceback (most recent call last):
  File "_udf_code.py", line 10, in main
    workbook = load_workbook(f, data_only=True, read_only=True)
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/reader/excel.py", line 348, in load_workbook
    reader.read()
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/reader/excel.py", line 295, in read
    self.read_properties()
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/reader/excel.py", line 176, in read_properties
    self.wb.properties = DocumentProperties.from_tree(src)
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/descriptors/serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/packaging/core.py", line 107, in __init__
    self.modified = modified or now
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/descriptors/base.py", line 272, in __set__
    super().__set__(instance, value)
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/descriptors/nested.py", line 33, in __set__
    super().__set__(instance, value)
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/descriptors/base.py", line 71, in __set__
    value = _convert(self.expected_type, value)
  File "/usr/lib/python_udf/07da35646d4f3b655d96bf0222d31c191a6797e450b0ac0eef955ee969f584c8/lib/python3.8/site-packages/openpyxl/descriptors/base.py", line 61, in _convert
    raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'datetime.datetime'>
 in function SPLIT_XLSX_TO_CSV_PROC with handler main

原代码 #

CREATE OR REPLACE PROCEDURE split_xlsx_to_csv_proc(file_path string, sheet_to_process string, sheet_to_ignore string, target_stage string)

RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python', 'pandas', 'openpyxl')
HANDLER = 'main'
EXECUTE AS CALLER
AS
$$
from openpyxl import load_workbook
import os, sys, csv
import pandas as pd

def main(session, file_path, sheet_to_process, sheet_to_ignore, target_stage):
session.file.get(file_path, "/tmp/")
file_name = os.path.basename(file_path)
with open(os.path.join("/tmp", file_name), "rb") as f: 
    workbook = load_workbook(f, data_only=True, read_only=True)
    
    # Check if the sheet to process is the one to ignore
    if sheet_to_process == sheet_to_ignore:
        return f"Sheet '{sheet_to_ignore}' is set to be ignored."
    
    # Choose the desired worksheet
    worksheet = workbook[sheet_to_process]
    
    # Open the CSV file in write mode
    with open("/tmp/exported.csv", 'w', newline='') as csv_file:
        csv_writer = csv.writer(csv_file)
        # Iterate through rows in the worksheet and write to the CSV file
        for row in worksheet.iter_rows(values_only=True):
            csv_writer.writerow([str(cell) if cell is not None else '' for cell in row])
    
    # Close the workbook
    workbook.close()
    session.file.put("file:///tmp/exported.csv", target_stage)

return os.path.join(target_stage, "exported.csv")
$$;

问题根源 #

这个错误并非来自工作表内的数据，而是openpyxl在读取Excel文件的**文档属性（如文件修改时间）**时出现的类型不匹配。Snowflake的Python运行环境中，文件元数据的日期被解析为datetime.date类型，但openpyxl的文档属性解析逻辑要求必须是datetime.datetime类型，从而触发报错。

修复方案 #

替换openpyxl为pandas直接读取Excel文件，pandas对Snowflake环境的兼容性更好，且能自动处理日期类型转换，绕开文档属性的解析问题。

修改后的代码 #

CREATE OR REPLACE PROCEDURE split_xlsx_to_csv_proc(file_path string, sheet_to_process string, sheet_to_ignore string, target_stage string)

RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
PACKAGES = ('snowflake-snowpark-python', 'pandas', 'openpyxl') -- openpyxl仍需保留，作为pandas读取xlsx的依赖
HANDLER = 'main'
EXECUTE AS CALLER
AS
$$
import os
import pandas as pd

def main(session, file_path, sheet_to_process, sheet_to_ignore, target_stage):
    # 从Stage下载文件到临时目录
    session.file.get(file_path, "/tmp/")
    file_name = os.path.basename(file_path)
    local_file_path = os.path.join("/tmp", file_name)
    
    # 检查是否要处理的工作表是忽略的表
    if sheet_to_process == sheet_to_ignore:
        return f"Sheet '{sheet_to_ignore}' is set to be ignored."
    
    # 用pandas读取指定工作表，自动处理类型转换
    df = pd.read_excel(local_file_path, sheet_name=sheet_to_process, dtype=str)
    
    # 将DataFrame写入CSV文件
    csv_output_path = "/tmp/exported.csv"
    df.to_csv(csv_output_path, index=False, na_rep='')
    
    # 将CSV上传回Stage
    session.file.put(f"file://{csv_output_path}", target_stage, auto_compress=False)
    
    return os.path.join(target_stage, "exported.csv")
$$;

修改说明 #

1. 移除openpyxl的直接调用，改用pd.read_excel读取指定工作表，避免文档属性解析错误
2. 通过dtype=str将所有数据转为字符串，保持原需求的格式统一
3. 用pandas的to_csv方法简化CSV写入流程，自动处理空值为''
4. 保留原有的工作表判断逻辑，确保业务逻辑不变

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