Description
Release Notes
None
duplicates
relates to
Activity
Show:
Vanathi Ganeshraj August 30, 2022 at 5:56 AM
I created a GCP project and experimented with creating a Cloud SQL MySQL instance (as a sink) and connecting to it from a CDF pipeline. An SQL syntax error message popped up when one of the CDAP output schema fields was named "insert" and given as input to the MySQL DB. I'd like some further context on this issue as I don't understand the solution mentioned.
Avinash Achar August 2, 2022 at 6:23 AMEdited
Can be worked on by a newbie
Context
Currently CDAP supports writing to relational databases using JDBC API with https://github.com/data-integrations/database-plugins.
While database and table names are fully configurable, field names are taken directly from CDAP schema and passed unqualified to the RDBMS. This leads to the next limitations:
If any field name is a reserved keyword in RDBMS the insert query would fail.
If table fields use symbols that are not permitted as part of CDAP schema, it’s impossible to write to such fields
Solution
The solution is to provide an additional property “Field Mapping” that would allow to map some of the CDAP schema fields to a different name in the destination table. Any such mapped field mapped will be passed as a quoted identifier (surrounded by double quotes). This will address both limitations as it would allow to pass keywords (with quotation RDBMs can clearly identify it as field name) and special characters.
It’s important to leave unmapped field names unquoted as quotation automatically enabled case sensitivity for a lot of RDBMS. So if unquoted name aaa would successfully match table column AAA, quoted “aaa” would not. Only mapped field names must be quoted.