Rethink how to store original data for Crate backend

[c0c0n3]

The problem

Turning on saving of original data when using the Crate backend results in an insert failure if the data contains nested arrays. Both the initial and the recovery insert fail because the type of the original data column is object and Crate does not support nested arrays in JSON docs---see #343 about it. Notice this only affects the Crate backend, not Timescale.

What to do about it

See if there's any easy workaround that will let us keep the object type for the original data column. If not consider switching back to the text type as we did initially, see conversation over here. Another option that springs to mind is to use a text column in all cases (= safe) but add a full text index to the column so we can query the content. Not as nice as querying the JSON but maybe a viable option?

[c0c0n3]

Here's how to reproduce the issue. Start QL with the KEEP_RAW_ENTITY environment variable set to true. Then use the below script to make QL bomb out.

from conftest import QL_URL
import json
import requests

notify_url = "{}/notify".format(QL_URL)

HEADERS = {
    'Content-Type': 'application/json',
    'fiware-servicepath': '/ParkingManagement/Cleverciti',
}

p1 = {
    'id': 'urn:ngsi-ld:ParkingSpot:G09',
    'type': 'ParkingSpot',
    'address': {
        'type': 'PostalAddress',
        'value': {
            'addressCountry': 'Switzerland',
            'addressLocality': 'Dietikon',
            'streetAddress': 'Überlandstrasse 2'
        },
        'metadata': {}
    },
    'dataProvider': {
        'type': 'Text',
        'value': 'https://www.cleverciti.com',
        'metadata': {}
    },
    'dateObserved': {
        'type': 'DateTime',
        'value': '2020-07-29T12:57:07.00Z',
        'metadata': {}
    },
    'location': {
        'type': 'geo:json',
        'value': {
            'type': 'Polygon',
            'coordinates': [[
                [8.409021754, 47.410719586], [8.409034494, 47.410700074],
                [8.409096856, 47.410719133], [8.409082104, 47.410739553],
                [8.409021754, 47.410719586]
            ]]
        }
    },
    'name': {
        'type': 'Text',
        'value': 'G09',
        'metadata': {}
    },
    'refDevice': {
        'type': 'Text',
        'value': 'urn:ngsi-ld:Device:C:DIE.FS.006',
        'metadata': {}
    },
    'status': {
        'type': 'Text',
        'value': 'free',
        'metadata': {}
    },
}

problemPayload = {
    "data": [p1]
}


def test_valid_notification(clean_mongo, clean_crate):
    r = requests.post('{}'.format(notify_url),
                      data=json.dumps(problemPayload),
                      headers=HEADERS)

    assert r.status_code == 200
    assert r.json() == 'Notification successfully processed'

[c0c0n3]

Here are another couple of things to keep in mind:

• Because of Crate won't honour NGSI type when adding new column #375, when upgrading from QL 0.7.5 to 0.7.6, the __original_ngsi_entity__ might be added with a text type instead of object---haven't tested this scenario, but we should.
• There may be cases where you don't want to save the original data at all, even on an insert error. For example, Notification not processed, Column __original_ngsi_entity__ unknown #381 would be less of a pain to @pinkerltm if he could turn off this feature since alot of his inserts may fail b/c of duplicated PKs.

[chicco785]

maybe a good idea is to make value as string in crate

[chicco785]

@c0c0n3 value is now a string in crate (if I am not wrong)

[c0c0n3]

@chicco785 yep. that's correct, closing this issue...