A way to handle datetime column error "AttributeError: Can only use .dt accessor with datetimelike values" in athena.read_sql_query

[helios1014]

So sometimes, when querying datetime columns from Athena, one might have an issue but not all of them. To look into that, I arrived at the following solution whereby I create a custom function for the Athena CSV to avoid the conversion issue with datetime objects and mark which columns are throwing the initial error:

import boto3
import awswrangler as wr
import pandas as pd

original_fix = wr.athena._read._fix_csv_types #save the original csv fucnction for  athena.read_sql_query incase you need it.
from typing import Any, Dict, Iterator, List, Match, NamedTuple, Optional, Union

def my_fix_csv_types(df: pd.DataFrame, parse_dates: List[str], binaries: List[str]) -> pd.DataFrame:
    """Apply data types cast to a Pandas DataFrames."""
    if len(df.index) > 0:
        for col in parse_dates:
            try:
                df[col] = df[col].dt.date.replace(to_replace={pd.NaT: None})
            except:
                df.rename(columns={f'{col}': f'{col}'+'_threw_error'}, inplace=True) #insead of converting to datetime, this simply retains the column as an object and flags it in the name
        for col in binaries:
            df[col] = df[col].str.encode(encoding="utf-8")
    return df

wr.athena._read._fix_csv_types = my_fix_csv_types #replaces original function in your code without permanent changes to source. 

Then you simply run wr.athena.read_sql_query as you normally would and instead of nothing, you get a dataframe you can examine for issues in your data and repair accordingly.