Is there any way to use a federated query against MySQL against a table whose name requires MySQL backtick escape characters or that has a column name that require escaping?
I have a column in a MySQL database called "condition". When I query in MySQL, I can escape reserved words and columns with spaces in the name using backticks.
If I don't escape it at all in Redshift, I get this error from MySQL:
SELECT id, condition FROM ext_schema.mytable
Yields: "ERROR: 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 'condition FROM ...'"
If I try to use MySQL backtick escapes in Redshift, I get a Redshift error:
SELECT id, (backtick)condition(backtick) FROM ext_schema.mytable
Yields: "ERROR: operator does not exist: ` character varying Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts."
If I try to use Redshift/PostgreSQL doublequote escapes in Redshift, I get a MySQL error:
SELECT id, "condition" FROM ext_schema.mytable
Yields: "ERROR: 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 'condition FROM...'"
AWS OFFICIALUpdated 2 years ago
Thank you for replying but this is not my problem. I need to somehow pass MySQL backtick escaping through to the Federated Query in Redshift. If I use ", MySQL complains. If I use backtick, then Redshift complains. If I try to put backticks in quotes, then MySQL says it can't find column "
condition". There doesn't appear to be a way to pass backtick escape characters from Redshift through to the Federated Query in MySQL