[Glue Crawler, Parquet] - Crawler infers timestamp[ns] as bigint

Question

I'm using Pandas to create a DataFrame with a single column of dates, which I then save as a Parquet file to an S3 bucket. Here's the code snippet I'm using:

```python
import pandas as pd

df = pd.DataFrame({'date': pd.date_range('2022-10-01', periods=10)})
df.to_parquet('s3:///data/athena/test/test.parquet', version='2.6')
```

After uploading the Parquet file to S3, I use a crawler to populate the schema in a data catalog. However, I've noticed that the data type of the date column is inferred as `bigint` rather than a `timestamp` type.

Interestingly, when I explicitly convert the column to `timestamp[us]` before saving the DataFrame, the crawler correctly identifies the column as a `timestamp` type, and everything works as expected.

However, I'm curious why this adjustment is necessary. Is there a known limitation with `timestamp[ns]` data types in Parquet files or with how crawler interpret them? How can I ensure that my `timestamp[ns]` columns are correctly identified as `timestamp` types without needing to convert them to `timestamp[us]`?

Thank you for your help!

Answer

There is a known limitation where Athena may not correctly infer timestamp columns from Parquet files generated in certain ways.

When writing the DataFrame to Parquet, Pandas uses nanosecond resolution timestamps which Parquet supports as INT96. However, some data catalogs and query engines may expect microsecond resolution instead.
Explicitly converting the timestamp column to microsecond resolution before writing to Parquet ensures the data type will be correctly identified.

df['date'] = df['date'].astype('datetime64[us]')

Another option is to set the proper metadata in the Parquet file itself to specify nanosecond resolution timestamps. Tools like Spark handle this automatically, but you may need to configure other systems like DMS to do the same.

[Glue Crawler, Parquet] - Crawler infers timestamp[ns] as bigint

Relevant content