Aws-data-wrangler: to_parquet() - dtype parameter

[jarretg]

Hello,

I have a process where I am reading AWS DMS created parquet files sourced from SQL Server. In one of my source tables, I have a date column with a value of 3015-06-29 in it. When I read the metadata from the parquet file, it does show my date column as a 'date' type ('startdate': 'date'). Also, when I display the data from the dataframe, I do see the 3015-06-29 value in the 'startdate' column. Without any transformations, I attempt to write the file out with supplying the dtype parameter in the to_parquet() call, but I get an error (OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 3015-06-29 00:00:00). However, if I remove the dtype parameter and write to S3, it completes successfully.

key = 's3://bucket/file.parquet'
column_metadata = wr.s3.read_parquet_metadata(key)[0]
df = wr.s3.read_parquet(key)

output_key = 's3://bucket/output/file.parquet'

#Write to S3 with dtype parameter.
wr.s3.to_parquet(df, path=output_key, dtype=column_metadata, compression='gzip') #This errors with OutOfBoundsDatetime error)

#Write to S3 without dtype parameter.
wr.s3.to_parquet(new_df, path=output_key, compression='gzip') #This completes successfully.

wr.s3.read_parquet_metadata(output_key)[0] #Shows 'startdate': 'date'

wr.s3.read_parquet(output_key) #This shows the date 3015-06-29 as from source.

I hope this is all understandable! I need the ability to supply the column types on the write because the inferred schema is causing other issues elsewhere. Thanks for your time.

Edited to add: I am using aws wrangler version 1.8.0.

Jarret

[jarretg]

I was able to write a script to reproduce the behavior. The only difference from what I'm seeing in my system is that my datatype having issues is showing as 'date', whereas this sample shows 'timestamp', but it has the same error outcome.

import pandas as pd
import awswrangler as wr  # version 1.8.1
from datetime import datetime

# Create dataframe
dataframe = pd.DataFrame([
    [datetime(3015, 6, 29), 1, 'test']
], columns = ['col1', 'col2', 'col3'])

key = 's3://bucket/path/testdata.parquet'

# Create the input file to mimic situation
wr.s3.to_parquet(dataframe, path=key)

# Read column metadata
cols = wr.s3.read_parquet_metadata(key)[0]
# Output: {'col1': 'timestamp', 'col2': 'bigint', 'col3': 'string'}

output_key = 's3://bucket/path/testdata-out.parquet'

# Use to_parquet() with and without dtype parameter
wr.s3.to_parquet(dataframe, path=output_key, dtype=cols) #Fails
wr.s3.to_parquet(dataframe, path=output_key) #Succeeds

# Read column metadata when successfully written (without dtype parameter)
wr.s3.read_parquet_metadata(output_key)[0]
#Output: {'col1': 'timestamp', 'col2': 'bigint', 'col3': 'string'}

The error is: OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 3015-06-29 00:00:00

[igorborgest]

Hi @jarretg, I will investigate it.

P.S. To create the date sample you can do something like:

df = pd.DataFrame([
    [datetime(3015, 6, 29).date(), 1, 'test']
], columns = ['col1', 'col2', 'col3'])

[igorborgest]

@jarretg ,

I've just extended the cast to date to accept this range in the commit above.
Do you mind to test it from our development branch and check if it solves your issue?
The ideia is to publish this enhancement in the version 1.9.0 later this week.

pip install git+https://github.com/awslabs/aws-data-wrangler.git@dev

[jarretg]

Hi Igor! I tested the dev branch and it does successfully write the file with the dtype parameter passed in. You have my vote to push into v1.9.0.

I originally tried the new version with my script above (as written, which uses the timestamp datatype), and it still fails with the OutOfBounds error. Once I casted the value as date, it did work as expected. Is there a plan to correct the timestamp type as well?

Thanks for your help!!!

[igorborgest]

Thanks for testing @jarretg.

Unfortunately the timestamp bound is a limitation inherited from Pandas itself. So my suggestion is to replace this out of bound values for NaT.

[jarretg]

Got it, thanks for the explanation!

[igorborgest]

Released on version 1.9.0