Description
Describe the bug
Based on the documentation (https://aws-sdk-pandas.readthedocs.io/en/3.11.0/stubs/awswrangler.redshift.copy.html) method wr.redshift.copy is capable to add new columns automatically if in destination table column from dataframe is not existing. This is working fine with "simple" column names, but for example if we have data frame with column name "example_column.name" method is failing with error:
identifier must contain only alphanumeric characters, spaces, underscores, or hyphens...
If user execute direct SQL command in Redshift:
alter table example_schema.table_name add "example_column.name" integer;
command is finished successfully and column is added to Redshift table. Based on the code I would say that issue is how SQL command in awswrangler module is generated.
How to Reproduce
import awswrangler as wr
import redshift_connector
d = {'first_column': [1, 2], 'example_column.name': [3, 4]}
df = pd.DataFrame(data=d)
conn = redshift_connector.connect(
host='example_redshift_host,
database='dev',
port=5439,
user='user',
password='password'
)
wr.redshift.copy(df,
con=conn,
path=f"s3://example_bucket/table/",
schema="example_schema",
table="table_name",
mode="append",
index=False,
use_column_names=True,
add_new_columns=True)
Expected behavior
Command for adding new column should be executed successfully since adding columns with dot inside column names is supported in Redshift if we execute direct SQL command in QueryEditor.
Your project
No response
Screenshots
No response
OS
Mac/Linux
Python version
3.10.13
AWS SDK for pandas version
3.11.0
Additional context
No response