DynamoDB CDC: Improve type mappings

[amotl]

Problem

Validation failed for data: Cannot cast expression `'[]'` of type `text` to `text_array`

Details

@wierdvanderhaar reported that when the CDC transformer processes an UPDATE event, generating an SQL statement like that

UPDATE "demo" SET data['items'] = '[]'

an exception is raised.

sqlalchemy.exc.ProgrammingError: (crate.client.exceptions.ProgrammingError) ColumnValidationException[Validation failed for data: Cannot cast expression `'[]'` of type `text` to `text_array`]

[amotl]

Hi. I guess this change might be the root cause of the issue?

• DynamoDB: Wrap all values in UPDATE statements in quotes #19

It pretends to leave it to CrateDB to auto-cast values. Maybe that does not work for empty arrays with CrateDB?

[amotl]

With a demo table in CrateDB like that...

create table foo (data OBJECT(DYNAMIC));
insert into foo (data) values ('{"items": ["foo", "bar"]}');

This statement works,

UPDATE "foo" SET data['items'] = [];

while this statement doesn't. It raises the same error message like presented in the OP.

UPDATE "foo" SET data['items'] = '[]';
ColumnValidationException[Validation failed for data: Cannot cast expression `'[]'` of type `text` to `text_array`]

[amotl]

@wierdvanderhaar: Can I humbly ask you check if downgrading to commons-codec 0.0.6 could be an easy option for you?

Version 0.0.7 includes a change "DynamoDB: Fixed a syntax issue with text data type in UPDATE statements", which is probably responsible for this hiccup.

[amotl]

This patch includes a proposal to apply better type mapping for UPDATE statements.

• DynamoDB: Apply rough type evaluation and dispatching #24

[wierdvanderhaar]

@amotl Thank you for your effort and input. The downgrade to [https://commons-codec.readthedocs.io/changes.html#v0-0-6](common-codec 0.0.6) is not really an option here because that version 0.0.7 fixed an issue raised in this https://github.com/daq-tools/commons-codec/pull/19.

In general, we will need to do more to apply correct type mapping.

[amotl]

... carrying over two more reports by @hlcianfagna from #24 (review). Thanks!

Top-level arrays

We need a test case for the situation of a first level field being an array of objects, for instance

"testarrayobj": [{"field1":1},{"field2":"abc"}]

This cast is successful in CrateDB:

'[{"field1":1},{"field2":"abc"}]'::ARRAY(OBJECT)

String Set

Another situation is the one of a DynamoDB String Set for which CrateDB may have already got an ARRAY(TEXT) defined, but the actual payload may have a single string, for the INSERT or UPDATE to succed we need to wrap this in [ ].

[amotl]

Hi again. We have been able to add a few follow-up fixes to improve type mappings. Thanks a stack for your support, @surister, @wierdvanderhaar, @hlcianfagna, and @hammerhead.

• DynamoDB: Apply rough type evaluation and dispatching #24
• DynamoDB: Fix String Set and Number Set representation to CrateDB. #29
• DynamoDB: Fix serializing empty strings #30
• DynamoDB: Fix object (Map) representation to CrateDB #31
• DynamoDB: Fix serializing OBJECT and ARRAY representations to CrateDB #34

[amotl]

We need a test case for the situation of a first level field being an array of objects [...]

GH-34 may improve this situation already, including test cases. Do you agree?

Another situation is the one of a DynamoDB String Set for which CrateDB may have already got an ARRAY(TEXT) defined, but the actual payload may have a single string, for the INSERT or UPDATE to succed we need to wrap this in [ ].

That would be a serious anomaly to fix up, mostly because a stateless transformation routine will not know about the fact that CrateDB already may have something stored as a certain type.

If we do an inquiry upfront inserting, the situation may change, but a) this adds additional complexity and b) might produce edge case situations when NO data has been inserted beforehand, and may create new kinds of ambiguities. That doesn't mean we will not implement it as advised in order to gain DWIM, but I still wanted to mention it that it's not straight-forward.

If you have any idea how to solve it more elegantly, please let me know.

[amotl]

I think we are in a reasonable better position now, after improving the DynamoDB translator implementation on behalf of the latest releases. In this spirit, we may close this issue, and open different / more specific ones when applicable.

@wierdvanderhaar / @hlcianfagna: Please 👍 if you agree.

[amotl]

Just using the chance to convey a big learning for me, which you may also like, if you didn't know yet. It is about how to use parameterized queries together with SQL CASTs, which might not be possible using the more ad hoc value::TYPE syntax.

commons-codec/src/commons_codec/transform/dynamodb.py

Lines 179 to 183 in 25eb728

	if isinstance(value, dict):
	rval = f"CAST(:{column} AS OBJECT)"
	elif isinstance(value, list) and value and isinstance(value[0], dict):
	rval = f"CAST(:{column} AS OBJECT[])"

I picked it up from some MSSQL or Oracle page I guess and discussed with @seut that it would be worth to also bring it more highlighted to our documentation in one way or another. It is just a small yet important detail, I believe.

/cc @matkuliak

[amotl]

Closing this now. Please re-open when applicable.