DynamoDB: Support list with items of different types.

[surister]

Example:

[
  {'a': 1}, 2, "Three"
]

[surister]

That's actually ilegal in CrateDB, but ok in DynamoDB, we should probably detect it and handle it.

[amotl]

Yeah, CrateDB needs to specify the inner type, like ::ARRAY(TEXT). So, it is probably not possible to store arrays/lists of varied types.

[amotl]

we should probably detect it and handle it

Good idea. While storing it 1:1 is probably not possible, because CrateDB can't handle it, we need a trick/workaround. Do you have any suggestions? Maybe one of you already applied any tricks in similar situations, or have another idea?

/cc @hlcianfagna, @wierdvanderhaar, @hammerhead, @proddata, @matriv

[surister]

How about we detect it, map it to a OBJECT DYNAMIC and insert it to the object with keys being the types?

l = [
  {'a': 1}, 2, "Three"
]

r = {}

for el in l:
    if (_type := type(el).__name__) not in r:
        r[_type] = []
    r[_type].append(el)

print(r)

{
    'dict': [{'a': 1}],
    'int': [2],
    'str': ['Three']
}

[proddata]

You can indirectly store it

cr> CREATE TABLE t01 (o OBJECT(IGNORED));
cr> INSERT INTO t01 (o) VALUES ($${"oo":[{"a": 1}, 2, "Three"]}$$);

cr> SELECT * FROM t01;
+--------------------------------+
| o                              |
+--------------------------------+
| {"oo": [{"a": 1}, 2, "Three"]} |
+--------------------------------+

cr> SELECT o['oo'][1] FROM t01;
+------------+
| o['oo'][1] |
+------------+
| {"a": 1}   |
+------------+

cr> SELECT o['oo'][2] FROM t01;
+------------+
| o['oo'][2] |
+------------+
|          2 |
+------------+

[matriv]

I think the OBJECT(IGNORED) is the only way, as @proddata suggested, but then nothing is indexed, so maybe this functionality can be enabled/disabled with a flag, and it should be clearly documented how such rows are indexed, and that searching on them is not possible.

[proddata]

I think the option is to store what is possible in a typed field and what is not in a separate column.
Ideally CrateDB could handle this e.g. like DuckDB and map types where possible and otherwise use generic json type

SELECT json_structure('[{"x":1,"y":1},{"x":2,"y":[1,2,3]}]') a;
┌──────────────────────────────┐
│              a               │
│             json             │
├──────────────────────────────┤
│ [{"x":"UBIGINT","y":"JSON"}] │
└──────────────────────────────┘

[amotl]

Thank you for your excellent suggestions 💯. I followed @proddata's advise:

I think the option is to store what is possible in a typed field and what is not in a separate column.

• DynamoDB: Add special decoding for varied lists #39

Please add your voice about general idea, naming things, or anything else which comes to mind. Thanks!

[amotl]

The patch referenced above, GH-39, will be included into the upcoming release, in order to improve the situation. The code is now also validated on behalf of integration tests with CrateDB, after GH-42 has been added.

To recap and summarize, the gist of the implementation is to use two distinct columns to store typed vs. untyped data. Currently, column names are data vs. aux. Please advise about better naming things, or if those names should even be made configurable on a subsequent iteration.

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

Lines 75 to 81 in 3a31a06

	# Define name of the column where typed DynamoDB fields will get materialized into.
	# This column uses the `OBJECT(DYNAMIC)` data type.
	TYPED_COLUMN = "data"
	# Define name of the column where untyped DynamoDB fields will get materialized into.
	# This column uses the `OBJECT(IGNORED)` data type.
	UNTYPED_COLUMN = "aux"

[amotl]

The improvement has been included into release v0.0.14.