CODEWITHSUNDEEP
pythonpandashiveamazon-athenafastparquet
prasannads
prasannads

Reputation: 659

Pandas dataframe type datetime64[ns] is not working in Hive/Athena

I am working on a python application which just converts csv file to hive/athena compatible parquet format and I am using fastparquet and pandas libraries to perform this. There are timestamp values in csv file like 2018-12-21 23:45:00 which needs to be written as timestamp type in parquet file . Below is my code that am running , 

columnNames = ["contentid","processed_time","access_time"]

dtypes = {'contentid': 'str'}

dateCols = ['access_time', 'processed_time']

s3 = boto3.client('s3')

obj = s3.get_object(Bucket=bucketname, Key=keyname)

df = pd.read_csv(io.BytesIO(obj['Body'].read()), compression='gzip', header=0, sep=',', quotechar='"', names = columnNames, error_bad_lines=False, dtype=dtypes, parse_dates=dateCols)

s3filesys = s3fs.S3FileSystem()

myopen = s3filesys.open

write('outfile.snappy.parquet', df, compression='SNAPPY', open_with=myopen,file_scheme='hive',partition_on=PARTITION_KEYS)

the code ran successfully , below is the dataframe created by pandas

contentid                 object
processed_time            datetime64[ns]
access_time               datetime64[ns]

And finally , when i queried the parquet file in Hive and athena , the timestamp value is +50942-11-30 14:00:00.000 instead of 2018-12-21 23:45:00

Any help is highly appreciated

Upvotes: 13

Views: 9408

Answers (6)

Gusty2000
Gusty2000

Reputation: 13

I also got this problem for multiple times. My error code is I set the index to datetime format by:

df.set_index(pd.DatetimeIndex(df.index), inplace=True)

When I then read the parquet file by fastparquet it may notice me that

OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 219968-03-28 05:07:11

However, it can be easily solved by using pd.read_parquet(path_file) rather than fastparquet.ParquetFile(path_file).to_pandas()

PLEASE USE pd.read_parquet(path_file) TO FIX THIS PROBLEM

That's my solution and it works well, hope it may help you then you don't need to worry about how to write parquet in which way.

Upvotes: 0

Nguyễn Văn Thưởng
Nguyễn Văn Thưởng

Reputation: 83

You could try:

dataframe.to_parquet(file_path, compression=None, engine='pyarrow', allow_truncated_timestamps=True, use_deprecated_int96_timestamps=True)

Upvotes: 4

Hely Andrés Palencia
Hely Andrés Palencia

Reputation: 61

I solved the problem by this way.

tranforms the df series with to_datetime method

next with a .dt accesor pick the date part of the datetime64[ns]

Example:

df.field = pd.to_datetime(df.field)
df.field = df.field.dt.date

After that, athena will recognize the data

Upvotes: 1

Ditlev Stjerne
Ditlev Stjerne

Reputation: 81

I know this question is old but it is still relevant.

As mentioned before Athena only supports int96 as timestamps. Using fastparquet it is possible to generate a parquet file with the correct format for Athena. The important part is the times='int96' as this tells fastparquet to convert pandas datetime to int96 timestamp.

from fastparquet import write
import pandas as pd

def write_parquet():
  df = pd.read_csv('some.csv')
  write('/tmp/outfile.parquet', df, compression='GZIP', times='int96')

Upvotes: 8

Severun
Severun

Reputation: 2916

The problem seems to be with Athena, it only seems to support int96 and when you create a timestamp in pandas it is an int64

my dataframe column that contains a string date is "sdate" I first convert to timestamp

# add a new column w/ timestamp
df["ndate"] = pandas.to_datetime["sdate"]
# convert the timestamp to microseconds
df["ndate"] = pandas.to_datetime(["ndate"], unit='us')

# Then I convert my dataframe to pyarrow
table = pyarrow.Table.from_pandas(df, preserve_index=False)

# After that when writing to parquet add the coerce_timestamps and 
# use_deprecated_int96_timstamps. (Also writing to S3 directly)
OUTBUCKET="my_s3_bucket"

pyarrow.parquet.write_to_dataset(table, root_path='s3://{0}/logs'.format(OUTBUCKET), partition_cols=['date'], filesystem=s3, coerce_timestamps='us', use_deprecated_int96_timestamps=True)

Upvotes: 0

Amandeep Singh
Amandeep Singh

Reputation: 521

I was facing the same problem, after a lot of research, it is solved now.

when you do 

write('outfile.snappy.parquet', df, compression='SNAPPY', open_with=myopen,file_scheme='hive',partition_on=PARTITION_KEYS)

it uses fastparquet behind the scene, which uses a different encoding for DateTime than what Athena is compatible with.

the solution is: uninstall fastparquet and install pyarrow

  • pip uninstall fastparquet
  • pip install pyarrow

run your code again. It should work this time. :)

Upvotes: -1

Related Questions