Description

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.

Release Notes

None

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 AM
Edited

Can be worked on by a newbie

Pinned fields
Click on the next to a field label to start pinning.
Details

Assignee

Vanathi Ganeshraj

Reporter

Vitalii Tymchyshyn

Labels

Priority

Created December 18, 2021 at 1:52 AM
Updated September 3, 2022 at 5:41 AM