[C++][Dataset] Writing partitions with timestamp type give mis-formatted (integers) directory names

[asfimport]

In [1]: df = pd.DataFrame({"dates": list(pd.date_range("2012-01-01", periods=2, freq="D")) * 5, "col": range(10)})

In [2]: table = pa.table(df)

In [8]: part = ds.partitioning(table.select(["dates"]).schema, flavor="hive")

In [9]: ds.write_dataset(table, "test_partition_timestamp", format="parquet", partitioning=part)

In [10]: !ls test_partition_timestamp/
'dates=1325376000000000000'  'dates=1325462400000000000'

Same happens for non-ns timestamp types.

(didn't directly find an existing issue about it)

Reporter: Joris Van den Bossche / @jorisvandenbossche

Related issues:

• [C++][Dataset] Support reading date/time-partitioned datasets accounting for URL encoding (Spark) (is related to)
• [C++][Compute] Add strftime kernel (depends upon)

_{Note: This issue was originally created as ARROW-11378. Please see the migration documentation for further details.}

[asfimport]

Joris Van den Bossche / @jorisvandenbossche:
cc @westonpace

[asfimport]

Weston Pace / @westonpace:
What would the correct behavior be here?  When I search for other tools to see how they handle this scenario it appears that timestamp columns are not allowed to be partition columns [1].  This would force the user to convert the column to a more reasonable date32.

I agree with the reasoning too, if these are truly timestamps (and not just dates encoded as timestamps) then they won't make a suitable partition column (too many values, no chance of any rows sharing the same value).

It seems are options are to either forbid this to save the user from themselves (disallow date64 and timestamp columns as partition columns) or allow it and use integers as the values.

I suppose we could use an ISO-8601 string as the value but that would be somewhat verbose.

I'll check date32 and see how we are handling that column type.

 [1] https://community.cloudera.com/t5/Support-Questions/Hive-partitions-based-on-date-from-timestamp/td-p/61423

 

 

[asfimport]

David Li / @lidavidm:
Tangentially related, but in ARROW-12644 it seems Spark 1) allows partitioning on a timestamp (though it separates it into day and time parts?) and 2) encodes it as a HH:MM:SS string with URL-encoding, which presents some additional fun for us.

[asfimport]

Weston Pace / @westonpace:
So it seems that : is indeed an invalid character in Windows filesystems (NTFS, FAT).  Rather than introduce a bunch of %3A my preference would be to use strings like 20210630T204219Z which are valid ISO-8601 strings.

I wonder if I need to introduce an option for Spark compatibility.

Also, just checking my logic:

• If the timestamp column is timezone=None then assume it is a local date time and just convert to UTC ISO-8601

• If the timestamp column is timezone=UTC then same as above.

• If the timestamp column is timezone=Other then it is a bit trickier.  My preference would be to just return invalid in this case.  Otherwise I don't think we can retain the timezone string through a roundtrip without breaking ISO-8601 compatibility.

   

[asfimport]

Apache Arrow JIRA Bot:
This issue was last updated over 90 days ago, which may be an indication it is no longer being actively worked. To better reflect the current state, the issue is being unassigned per project policy. Please feel free to re-take assignment of the issue if it is being actively worked, or if you plan to start that work soon.