Description
Describe the bug
I'm looking to use AWS Data Wrangler to read data from s3 files, process it, and insert into RDS MySQL. The source of the s3 files is another RDS database (via DBMS). I am not involved in the schema of the source, and on some of the tables reserved words have been used as column names - in this example it is 'set', but others such as 'group' are used in other tables.
I'm having an issue when inserting these records into the target RDS (at this point as a copy of the source data, without columns added by processing in the Lambda), caused by the use of a reserved word.
I'd like to keep the columns the same as the source where possible - is there a way for me to achieve this?
wr.mysql.to_sql(df, con_mysql, schema=db, table=tab, mode="upsert_duplicate_key", index=False, chunksize=200, use_column_names=True)
[DEBUG] 2021-09-21T09:26:53.098Z eca30ba2-d2ff-4a1b-90dd-3f418901d090 sql: INSERT INTO db.table (timestamp, id, set, parentid, name, value, file_record_number, dlk_rnk, dlk_updatedat, rnk) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE timestamp=VALUES(timestamp), id=VALUES(id), set=VALUES(set), parentid=VALUES(parentid), name=VALUES(name), value=VALUES(value), file_record_number=VALUES(file_record_number), dlk_rnk=VALUES(dlk_rnk), dlk_updatedat=VALUES(dlk_updatedat), rnk=VALUES(rnk)
[ERROR] 2021-09-21T09:26:53.103Z eca30ba2-d2ff-4a1b-90dd-3f418901d090 (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set, parentid, name, value, file_record_number, dlk_rnk, dlk_updatedat, rnk) VAL' at line 1")
With backticks added to column names of the dataframe:
df = df.rename(columns=lambda x: '' + x + '')
[DEBUG] 2021-09-21T10:06:38.475Z ed54a68e-b76a-4fb9-84df-fa27c6eeb2d3 sql: INSERT INTO db.table (timestamp, id, set, parentid, name, value, file_record_number, dlk_rnk, dlk_updatedat, rnk) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE timestamp=VALUES(timestamp), id=VALUES(id), set=VALUES(set), parentid=VALUES(parentid), name=VALUES(name), value=VALUES(value), file_record_number=VALUES(file_record_number), dlk_rnk=VALUES(dlk_rnk), dlk_updatedat=VALUES(dlk_updatedat), rnk=VALUES(rnk)
[ERROR] 2021-09-21T10:06:38.484Z ed54a68e-b76a-4fb9-84df-fa27c6eeb2d3 (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'timestamp=VALUES(timestamp), id=VALUES(id), set=VALUES(set``' at line 1")
With quotation marks added to column names:
df = df.rename(columns=lambda x: '"' + x + '"')
[DEBUG] 2021-09-21T10:08:18.733Z 4f75c982-07ec-496d-8231-00abc5760375 sql: INSERT INTO db.table ("timestamp", "id", "set", "parentid", "name", "value", "file_record_number", "dlk_rnk", "dlk_updatedat", "rnk") VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE "timestamp"=VALUES("timestamp"), "id"=VALUES("id"), "set"=VALUES("set"), "parentid"=VALUES("parentid"), "name"=VALUES("name"), "value"=VALUES("value"), "file_record_number"=VALUES("file_record_number"), "dlk_rnk"=VALUES("dlk_rnk"), "dlk_updatedat"=VALUES("dlk_updatedat"), "rnk"=VALUES("rnk")
[ERROR] 2021-09-21T10:08:18.745Z 4f75c982-07ec-496d-8231-00abc5760375 (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"timestamp", "id", "set", "parentid", "name", "value", "file_record_number", "dl' at line 1')
Environment
v 2.11.0 of AWS data wrangler, AWS Lambda Python 3.8 runtime. Target is Aurora RDS MySQL 5.7.