Aws-data-wrangler: Error when trying to Insert Pandas Dataframe into Redshift with 'INTEGER' type

[GroovyDan]

awswrangler version 0.3.2

We noticed that when trying to insert a pandas Dataframe into Redshift, we get an error when specifying a cast column of type 'INTEGER'.

Redshift Schema

create table test.test_cast
(
    col_int_null int
);

Dataframe Creation and function call

df = pd.DataFrame({
    'col_int_null': [None, 1],
})

wr.pandas.to_redshift(
    dataframe=df,
    path='s3_path',
    table='test_cast',
    schema='test',
    connection='test_connection',
    iam_role='iam_role',
    mode="append",
    preserve_index=False,
    cast_columns={
        'col_int_null': 'INTEGER',
    },
)

Stack Trace and Debug Messages

cast_columns: {'col_int_null': 'int'}
partition_cols: []
procs_cpu_bound: 1
procs_io_bound: 24
Casting column col_int_null Int64 to float64
Casting column col_int_null (0) to bigint (int64)
Traceback (most recent call last):
  File "script.py", line 341, in <module>
    run()
  File "script.py", line 337, in run
    'col_int_null': 'INTEGER',
  File "/Users/username/.virtualenvs/test_cast-qMgZg6ug/lib/python3.6/site-packages/awswrangler/pandas.py", line 1157, in to_redshift
    raise ex
  File "/Users/username/.virtualenvs/test_cast-qMgZg6ug/lib/python3.6/site-packages/awswrangler/pandas.py", line 1150, in to_redshift
    varchar_lengths=varchar_lengths)
  File "/Users/username/.virtualenvs/test_cast-qMgZg6ug/lib/python3.6/site-packages/awswrangler/redshift.py", line 278, in load_table
    cursor.execute(sql)
  File "/Users/username/.virtualenvs/test_cast-qMgZg6ug/lib/python3.6/site-packages/pg8000/core.py", line 861, in execute
    self._c.execute(self, operation, args)
  File "/Users/username/.virtualenvs/test_cast-qMgZg6ug/lib/python3.6/site-packages/pg8000/core.py", line 1909, in execute
    self.handle_messages(cursor)
  File "/Users/username/.virtualenvs/test_cast-qMgZg6ug/lib/python3.6/site-packages/pg8000/core.py", line 1976, in handle_messages
    raise self.error
pg8000.core.ProgrammingError: {'S': 'ERROR', 'C': 'XX000', 'M': 'S3 Query Exception (Fetch)', 'D': "\n  -----------------------------------------------\n  error:  S3 Query Exception (Fetch)\n  code:      15001\n  context:   Task failed due to an internal error. File 'https://s3.amazonaws.com/test_bucket/tmp/2c4c6c9dba344264a5965a07a4116d6b.snappy.parquet  has an incompatible Parquet schema for column 's3://test_bucket/\n  query:     1548333\n  location:  dory_util.cpp:1138\n  process:   fetchtask_thread [pid=25245]\n  -----------------------------------------------\n", 'F': '/home/ec2-user/padb/src/sys/xen_execute.cpp', 'L': '8653', 'R': 'pg_throw'}

This appear to be because of the athena2pandas mapping all Athena integer types to Int64 dtypes.

https://github.com/awslabs/aws-data-wrangler/blob/master/awswrangler/data_types.py#L19-L20

When switch athena2pandas to map integers more specifically, (i.e. Athena int to dtype Int32) we no longer get this error:

    if dtype in ("bigint",):
        return "Int64"
    elif dtype in ("int", "integer", "smallint", "tinyint"):
        return "Int32"

Just to note, smallint and tinyint may need more specific mappings. We were just testing with integer.

[GroovyDan]

Also just a note, if we were to change the athena2pandas to map more specifically, the below code would no longer apply to anything either than a 'bigint'. It is now currently applying to every integer type in Athena as every Athena int type is mapped to 'Int64' dtype:

https://github.com/awslabs/aws-data-wrangler/blob/master/awswrangler/pandas.py#L1020-L1024

I'm not sure what the purpose of this code snippet is though.

[igorborgest]

Hi @GroovyDan, thank you for the awesome troubleshooting!

It makes a lot of sense, and we will address this integers specification in the new 1.0.0 version.

We will also try to remove the code snippet at https://github.com/awslabs/aws-data-wrangler/blob/master/awswrangler/pandas.py#L1020-L1024.
It was necessary in the past because at least at that time PyArrow didn't accept the new Int64 (with capital "I") pandas data type, so the purpose was workaround this limitation casting the pandas column to float64 during the conversion to then convert it back to integer in the Arrow Table.

So, version 1.0.0 is coming with tons of others improvements and simplifications. If you want I can let you know when this functionality will be ready to be tested.

[igorborgest]

Hi! We will release AWS Data Wrangler 1.0.0 on April 10 and we are collecting as much feedback as possible.

This issue should be resolved with this release. So, please test it if possible, and any feedback about the docs, the library or this specific functionality will be very welcome.

• Install: pip install git+https://github.com/awslabs/[email protected]
• Docs: https://aws-data-wrangler.readthedocs.io/en/dev-1.0.0/
• Code: https://github.com/awslabs/aws-data-wrangler/tree/dev-1.0.0

[igorborgest]

Thank you for the contribution. Please, reopen the issue if it persists even after the version 1.0.0.