Update docs for awswrangler.s3.to_csv

[maiconrocha]

Hi there,

I believe the documentation for awswrangler.s3.to_csv needs to be updated since schema_evolution is not supported for CSV:
https://github.com/awslabs/aws-data-wrangler/blob/ca73c197e6e85775ae9b1e262686c1cd59a5ade1/awswrangler/catalog/_create.py#L322

and the doc suggests we can enable schema_evolution: True.

Thanks

[jaidisido]

I believe the schema_evolution parameter is available in the latest release (2.10.0) as it was added in this PR.

[maiconrocha]

Hi @jaidisido. Thanks. Let me clarify:

1. I'm using latest version of AWS Data Wrangler: 2.10.0
2. Schema has changed on source RDS MySQL Database.
3. When create_csv_table on Glue Catalog I got the following error:

  File "/usr/local/airflow/.local/lib/python3.7/site-packages/awswrangler/catalog/_create.py", line 322, in _create_csv_table
    f"Schema change detected - New column {c}. Schema evolution is not supported for CSV tables."

Snippet code:

columns_types, partitions_types = wr.catalog.extract_athena_types(
                df=dataset,
                index=False,
                partition_cols=["XXXXXX", ],
                file_format="csv"
            )

            wr.catalog.create_csv_table(
                database=glue_database,
                table=table + '_csv',
                path='s3://' + s3_bucket + '/csv/' + database + '/' + table + '/',
                columns_types=columns_types,
                partitions_types=partitions_types,
                compression='gzip',
                boto3_session=session
            )

interestingly the schema was updated on Glue Catalog for the Parquet Table.
So, my questions are:

1. From my understanding, schema evolution is not supported for CSV tables on Glue Catalogs, is that correct?
   https://github.com/awslabs/aws-data-wrangler/blob/main/awswrangler/catalog/_create.py#L322

2. Is there a way to allow schema evolution on Glue Catalog CSV Tables since we allow schema evolution to s3.to_csv as you pointed out?

[jaidisido]

Thanks for this @maiconrocha, I see what you mean now. In the PR, schema_evolution was enabled for the to_csv method, but because it's still not accepted in the _create_csv method, schema evolution won't succeed for CSV files.

Stepping back, I believe the fundamental issue here is in how schema updates are handled for text formats in Athena. As you can see from the docs, it's not possible to add columns at the beginning or middle of CSV file formats. It's permitted at the end of the files though. This is because Athena relies on column ordering for text formats and changing that order leads to schema mismatch errors when the schema of partitions changes.

So I see one of two options:

1. Revert to not allowing schema evolution for CSV files
2. Allow schema evolution for CSV, but clarify in the docs that it's up to the user to make sure they do not change the order of the columns. That is they can add columns at the end, but they cannot add them at the beginning/middle

I am in favor of #2 but will ask the community first and revert back here asap

[kukushking]

I agree on #2. It's up to the user to make sure they use text formats in Athena as intended.

[kukushking]

Fixed schema evolution in wr.s3.to_csv and wr.catalog. create_csv_table with above PR.

The fix is merged to main and can be tested via:

pip uninstall awswrangler -y
pip install git+https://github.com/awslabs/aws-data-wrangler.git@main

Until it's available in the next release. Feel free to reopen the issue if something else pops up.