如何可靠地使用拼花文件中的日期时间值来填充(雪花)表

Question

TLDR

如何确保正确地将拼花文件中的日期时间值复制到雪花表中？

描述

我正在使用一个拼花文件将数据插入到雪花中的某个阶段。然后，该文件用于复制到雪花表。

除日期时间值外，所有操作都很好:取决于我是使用fastparquet还是pyarrow在本地保存拼花文件，日期时间值是否正确(数据类型为雪花中的TIMESTAMP_NTZ(9) )：

# With fastparquet:
ID    SOME_TIMESTAMP
----------- 
1     2020-02-02 00:35:28.000
2     2020-02-02 00:35:31.857
3     2020-02-02 00:35:21.000

# With pyarrow:
ID    SOME_TIMESTAMP
----------- 
1     52057-05-03 07:06:40.000
2     52057-05-03 08:10:57.000
3     52057-05-03 05:10:00.000

然而，在熊猫中，数据格式与两者的dtype相同：datetime64[ns]。

1 2020-02-02 00:35:28.000
2 2020-02-02 00:35:31.857
3 2020-02-02 00:35:21.000

因此，我认为问题的根源在于快速拼花和比索处理日期时间值的方式，以及雪花如何读取拼花文件。

解决思路

在这一点上，我可以想到三种方法来处理这个问题：

1. 确保使用fastparquet保存了拼花文件。尽管我不能马上控制这一点，但我总是可以用pd.read_parquet()打开每个拼花文件，然后用pd.to_parquet(fname, engine='fastparquet')保存它。尽管如此，我还是想避免使用这个选项，因为文件可能会变得非常大。
2. 使用元信息，尝试找出文件是如何保存的。如果使用的引擎不是fastparquet，则打开并使用fastparquet保存文件。这将大大减少我前面指出的问题，因为我只打开&保存我知道会造成麻烦的文件。
3. 确保雪花正确处理时间戳值。

要使用选项2，我需要以某种方式区分(在Python中)如何保存拼板。

我去的其他小巷

雪花允许我指定使用的压缩，如COMPRESSION = AUTO (默认)或COMPRESSION = SNAPPY，如描述的这里。但是，pyarrow和fastparquet都使用这种压缩。此外，根据文档，AUTO选项应该捕捉到这一点。

Answer 1

虽然我不能百分之百确定熊猫或蟒蛇在做什么。我以前见过，对我们来说，这是由于划时代的时间试图转换成时间戳。当这些数据被传入时，它似乎被转换为一个时代，然后是CAST (::TIMESTAMP_NTZ)到TIMESTAMP_NTZ，由于某种原因，它的数值是0而不是9。

我建议使用一个函数来CONVERT (TRY_TO_TIMSTAMP(< value >，scale/format) )，将值转换为时间戳。这将使您可以使用控件来告诉函数正在传递什么类型的数据，以及它应该如何解释该数据。有了演员，你就让雪花来决定。

下面是一些示例代码，希望这些代码有助于可视化情况。我使用了时间戳示例，这些示例最初是作为字符串构建的。然后，我将(并转换为完整性)转换为时间戳，然后转换为毫秒，然后使用毫秒返回时间戳。

SELECT $1 AS C1
    ,$2 AS C2
    // strings cast or converted to timestamps both result in the same output, so we can use either to calculate the MS from EPOCH start
    ,$2::TIMESTAMP_NTZ AS STRING_CAST_TO_DTTM
    ,TO_TIMESTAMP_NTZ($2) AS STRING_CONVERT_TO_DTTM
    ,DATEDIFF('MS','0',STRING_CAST_TO_DTTM) AS MS
//    ,DATEDIFF('MS','0',STRING_CONVERT_TO_DTTM) AS MS
    // the lines below are integers being CONVERTED to timestamp and the scale is an explicit parameter of the function; note that everything converts correctly BASED ON SCALE
    ,TO_TIMESTAMP_NTZ(MS,0) AS CONVERT_NTZ0
    ,TO_TIMESTAMP_NTZ(MS,3) AS CONVERT_NTZ3
    ,TO_TIMESTAMP_NTZ(MS,6) AS CONVERT_NTZ6
    ,TO_TIMESTAMP_NTZ(MS,9) AS CONVERT_NTZ9
    // the lines below are integers being CAST to timestamp; note that the scale does not matter everything is returned as TIMESTAMP_NTZ(0)
    ,MS::TIMESTAMP_NTZ(0) AS CAST_NTZ0
    ,MS::TIMESTAMP_NTZ(3) AS CAST_NTZ3
    ,MS::TIMESTAMP_NTZ(6) AS CAST_NTZ6
    ,MS::TIMESTAMP_NTZ(9) AS CAST_NTZ9
FROM VALUES 
(1,'2020-02-02 00:35:28.000')
,(2,'2020-02-02 00:35:31.857')
,(3,'2020-02-02 00:35:21.000')
;

// use this to see the resulting data types for each column
DESC RESULT LAST_QUERY_ID();

如果您对上述代码有任何疑问，请发表评论。