Aws-data-wrangler: Session Athena Query Results are not convertable to Redshift saving

[vfrank66]

I am reading from athena using a .Session because I am using cross account access to Athena in a different AWS Account and returning the results to a dataframe which I am then trying to create a redshift table from. All in aws wrangler. I am currently getting a pyarrow error on the redshift call.

pyarrow.lib.ArrowInvalid: (‘Could not convert with type NAType: did not recognize Python value type when inferring an Arrow data type’, ‘Conversion failed for column hosp_adm_id with type object’)

Traceback (most recent call last):
<...>
File "/tmp/test-app/awswrangler/pandas.py", line 858, in _data_to_s3_dataset_writer_remote
isolated_dataframe=True))
File "/tmp/test-app/awswrangler/pandas.py", line 803, in _data_to_s3_dataset_writer
isolated_dataframe=isolated_dataframe)
File "/tmp/test-app/awswrangler/pandas.py", line 899, in _data_to_s3_object_writer
isolated_dataframe=isolated_dataframe)
File "/tmp/test-app/awswrangler/pandas.py", line 968, in write_parquet_dataframe
table = pa.Table.from_pandas(df=dataframe, preserve_index=preserve_index, safe=False)
File "pyarrow/table.pxi", line 1177, in pyarrow.lib.Table.from_pandas
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 575, in dataframe_to_arrays
for c, f in zip(columns_to_convert, convert_fields)]
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 575, in
for c, f in zip(columns_to_convert, convert_fields)]
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 566, in convert_column
raise e
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 560, in convert_column
result = pa.array(col, type=type_, from_pandas=True, safe=safe)
File "pyarrow/array.pxi", line 265, in pyarrow.lib.array
File "pyarrow/array.pxi", line 80, in pyarrow.lib._ndarray_to_array
File "pyarrow/error.pxi", line 84, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: ('Could not convert with type NAType: did not recognize Python value type when inferring an Arrow data type', 'Conversion failed for column hosp_adm_id with type object')
Process Process-30:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/multiprocessing/process.py", line 297, in _bootstrap
self.run()
File "/usr/local/lib/python3.7/multiprocessing/process.py", line 99, in run
self._target(self._args, *self._kwargs)
File "/tmp/test-app/awswrangler/pandas.py", line 858, in _data_to_s3_dataset_writer_remote
isolated_dataframe=True))
File "/tmp/test-app/awswrangler/pandas.py", line 803, in _data_to_s3_dataset_writer
isolated_dataframe=isolated_dataframe)
File "/tmp/test-app/awswrangler/pandas.py", line 899, in _data_to_s3_object_writer
isolated_dataframe=isolated_dataframe)
File "/tmp/test-app/awswrangler/pandas.py", line 968, in write_parquet_dataframe
table = pa.Table.from_pandas(df=dataframe, preserve_index=preserve_index, safe=False)
File "pyarrow/table.pxi", line 1177, in pyarrow.lib.Table.from_pandas
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 575, in dataframe_to_arrays
for c, f in zip(columns_to_convert, convert_fields)]
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 575, in
for c, f in zip(columns_to_convert, convert_fields)]
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 566, in convert_column
raise e
File "/usr/local/lib/python3.7/site-packages/pyarrow/pandas_compat.py", line 560, in convert_column
result = pa.array(col, type=type_, from_pandas=True, safe=safe)
File "pyarrow/array.pxi", line 265, in pyarrow.lib.array
File "pyarrow/array.pxi", line 80, in pyarrow.lib._ndarray_to_array
File "pyarrow/error.pxi", line 84, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: ('Could not convert with type NAType: did not recognize Python value type when inferring an Arrow data type', 'Conversion failed for column hosp_adm_id with type object')
<...>

My column that is failing hosp_adm_id is defined in a Athena ORC table as hosp_adm_id int,.
In addition there are only two records that have null values.

The python code

class DataWrangler():
    def __init__(self, region, db, test_env, second_env, color, aws_access_key_id, aws_secret_access_key, host, port, project_name):
        self.host = host
        self.port = port
        self.environment = test_env
        self.project_name = project_name
        self.s3_staging_dir = f"s3:/test-bucket"
        self.schema = f"{second_env}-{db}"
        self.aws_access_key_id = aws_access_key_id
        self.aws_secret_access_key = aws_secret_access_key

wrangler_settings  = DataWrangler(...)

sess = wr.Session(aws_access_key_id=wrangler_settings.aws_access_key_id,
                      aws_secret_access_key=wrangler_settings.aws_secret_access_key,
                      region_name='us-east-1',
                      athena_ctas_approach=True)
df = sess.pandas.read_sql_athena(sql=sql,
                                     database=wrangler_settings.schema,
                                     s3_output=wrangler_settings.s3_staging_dir,
                                     ctas_approach=True)
con = wr.redshift.generate_connection(
        database=wrangler_settings.environment, 
        host=wrangler_settings.host, 
        port=wrangler_settings.port, 
        user='test_user', 
        password='test_password'
    )

### FAILING
wr.pandas.to_redshift(
        dataframe=df,
        path=f"{s3_path}",
        schema="test_schema",
        table=key,
        connection=con,
        iam_role="arn:aws:iam::11111111111:role/TestRedshift",
        mode="overwrite",
        preserve_index=False,
    )

### ALSO FAILING
    # wr.pandas.to_redshift(
    #     dataframe=df,
    #     path=f"{s3_path}",
    #     schema="test_schema",
    #     table=key,
    #     connection=con,
    #     iam_role="arn:aws:iam::11111111111:role/TestRedshift",
    #     mode="overwrite",
    #     preserve_index=False,
    #     cast_columns={
    #         'hosp_adm_id': 'INT8'
    #     }
    # )

The reason I am posting here is because I think that when aws wrangler returns athena results it uses pd.NA which is then not compatible with aws wrangler's redshift conversion, personal assumption. I also didn't see any recommendations on how to address this in how to correctly write the Athena query, if that is where this change is supposed to occur.

[igorborgest]

Thanks @vfrank66! We will try to find out what is happening here.

[vfrank66]

@igorborgest This might be a result of upgrading to pandas 1.0 https://pandas.pydata.org/pandas-docs/stable/whatsnew/v1.0.0.html#experimental-new-features

A new pd.NA value (singleton) is introduced to represent scalar missing values. Up to now, pandas used several values to represent missing data: np.nan is used for this for float data, np.nan or None for object-dtype data and pd.NaT for datetime-like data.

[igorborgest]

Hi @vfrank66, thank you for the excellent troubleshooting.
We were able to improve the both ends:

• Pandas.read_sql_athena(ctas_approach=True) will be able to return Int64 for integers with nulls.
• Pandas.to_redshift() will be able to cast object columns with integers and pd.NA mixed.

I expect now that your scenario will works with or without casting.

Will be released in the version 0.3.2 soon.