英文:
Snowflake Failed to cast variant value to DATE
问题
我有一个名为df的pandas数据帧，我想将它添加到Snowflake中的一个表中。然而，当我运行下面的代码时，出现了一个编程错误，说它无法将一个值转换为日期格式。
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas
success, nchunks, nrows, _ = write_pandas(cnn, df, "TABLE_NAME", quote_identifiers=False)
>>> ProgrammingError: 100071 (22000): Failed to cast variant value 1466467200000000 to DATE
我尝试过的一些事情：
- 我尝试定位在我的数据帧中引发错误的值，但下面的命令没有返回任何内容，这真的很奇怪！我还查看了原始的CSV文件，没有发现任何格式错误。
df[df.eq("1466467200000000").any(1)]
df[df.eq(1466467200000000).any(1)]
-
日期列已经被解析，当我打印
df.dtypes时，日期列的类型为datetime64[ns]。 -
我尝试运行下面的代码再次解析日期列，强制处理任何错误，但这没有任何不同。
df['date_col']=pd.to_datetime(df['date_col'],errors='coerce')
有什么可能导致这个错误的想法吗？任何帮助或建议将不胜感激。
英文:
I have a pandas dataframe df that I want to add to a table in Snowflake. However when I run the code below I get a programming error saying that it failed to cast a value to date format.
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas
success, nchunks, nrows, _ = write_pandas(cnn, df, "TABLE_NAME", quote_identifiers=False)
>>> ProgrammingError: 100071 (22000): Failed to cast variant value 1466467200000000 to DATE
A few things I tried:
- I tried to pinpoint where this value is in my dataframe that's causing the erorr, but the commands below returned nothing, which is really strange! Also took a look at the original csv file, and didn't spot any format error.
df[df.eq("1466467200000000").any(1)]
df[df.eq(1466467200000000).any(1)]
-
Date columns are already parsed, and when I print
df.dtypes, the date columns are in the correctdatetime64[ns]type. -
I tried running the code below to parse the date columns once again, coercing any errors - but this did not make any difference.
df['date_col']=pd.to_datetime(df['date_col'],errors='coerce')
Any ideas what might be causing the error? Any help or advice will be very much appreciated.
答案1
得分: 4
我认为错误出现是因为你的目标表期望得到DATE类型，其中只包括YYYY-MM-DD部分，而datetime64[ns]包含了整个时间戳，包括小数秒。如果你想在Snowflake的目标表中只保存日期，可以在写入Snowflake之前从时间戳中提取日期部分：
df['date_col']=pd.to_datetime(df['date_col'],errors='coerce').dt.date
如果你想保存整个时间戳值，那么请使用类型为TIMESTAMP的目标列，然后你的代码应该可以正常工作。
英文:
I think the error appears because your target table expect to get the DATE type, which includes only YYYY-MM-DD part, while datetime64[ns] has the entire timestamp up to decimal seconds. If you want to have only the date in your target table in Snowflake, try extracting the date part from your timestamp before writing it to Snowflake:
df['date_col']=pd.to_datetime(df['date_col'],errors='coerce').dt.date
If you want to save the entire timestamp value, then use the target columns with the type, e.g. TIMESTAMP, then your code should work.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论