search

orchestracities / ngsi-timeseries-api

QuantumLeap: a FIWARE Generic Enabler to support the usage of NGSIv2 (and NGSI-LD experimentally) data in time-series databases
https://quantumleap.rtfd.io/
MIT License
37 stars 49 forks source link

Timescale Error `"mtopeniot.etfillinglevelsensor" does not exist'` #669

Closed jason-fox closed 1 year ago

jason-fox commented 2 years ago

Describe the bug When listening to an NGSI-LD subscription from Orion-LD and recording data associated to an NGSILD-Tenant the data is not persisted to timescale but an error occurs Timescale Error"mtopeniot.etfillinglevelsensor" does not exist'`

To Reproduce

Full docker-compose can be found on the following feature/timescale branch: https://github.com/FIWARE/tutorials.Time-Series-Data/tree/feature/timescale

Steps to reproduce the behavior:

  1. Send this subscription to ORION-LD:
curl -L -X POST 'http://localhost:1026/ngsi-ld/v1/subscriptions/' \
-H 'Content-Type: application/json' \
-H 'NGSILD-Tenant: openiot' \
-H 'fiware-servicepath: /' \
-H 'Link: <http://context/ngsi-context.jsonld>; rel="http://www.w3.org/ns/json-ld#context"; type="application/ld+json"' \
--data-raw '{
  "description": "Notify me of all feedstock changes",
  "type": "Subscription",
  "entities": [{"type": "FillingLevelSensor"}],
  "watchedAttributes": ["filling"],
  "notification": {
    "attributes": ["filling", "location"],
    "format": "normalized",
    "endpoint": {
      "uri": "http://quantumleap:8668/v2/notify",
      "accept": "application/json",
      "receiverInfo": [
          {"key": "fiware-service", "value": "openiot"}
      ]
    }
  }
}'
  1. Remove hay from the barn. This sends the following payload with NGSILD-Tenant openiot 
    [
{
"id": "urn:ngsi-ld:Device:filling001",
"type": "FillingLevelSensor",
"location": {
  "type": "GeoProperty",
  "value": {
    "type": "Point",
    "coordinates": [
      13.3505,
      52.5144
    ]
  },
  "observedAt": "2022-07-25T14:47:20.000Z"
},
"filling": {
  "type": "Property",
  "value": 0.96,
  "unitCode": "C62",
  "observedAt": "2022-07-25T14:47:20.000Z"
}
}
]
  2. See error in logs

Expected behavior

Using Crate-DB, the data is properly persisted onto mt<TENANT>.et<ENTITY> without errors. Using Timescale, the database is not updated and an error occurs - see log appended below.

Environment:

Additional context

Full debug log can be seen below

Log ```text titime=2022-07-25 02:47:20.374 | level=ERROR | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=with_connection_guard | comp=root | msg={'S': 'ERROR', 'V': 'ERROR', 'C': '42704', 'M': 'type "geometry" does not exist', 'P': '241', 'F': 'parse_type.c', 'L': '271', 'R': 'typenameType'} | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 Traceback (most recent call last): File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 249, in execute self._context = self._c.execute_simple(operation) File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 620, in execute_simple self.handle_messages(context) File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 767, in handle_messages raise self.error pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42704', 'M': 'type "geometry" does not exist', 'P': '241', 'F': 'parse_type.c', 'L': '271', 'R': 'typenameType'} 2022-07-25T14:47:20.384241001Z During handling of the above exception, another exception occurred: 2022-07-25T14:47:20.384244626Z Traceback (most recent call last): File "/src/ngsi-timeseries-api/src/translators/timescale.py", line 129, in with_connection_guard db_action() File "/src/ngsi-timeseries-api/src/translators/timescale.py", line 174, in do_create self.cursor.execute(stmt) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 279, in execute raise cls(msg) pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42704', 'M': 'type "geometry" does not exist', 'P': '241', 'F': 'parse_type.c', 'L': '271', 'R': 'typenameType'} time=2022-07-25 02:47:20.388 | level=DEBUG | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=read | comp=translators.config | msg=Env variable KEEP_RAW_ENTITY not set, using default value of: False | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 time=2022-07-25 02:47:20.390 | level=DEBUG | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=read | comp=translators.insert_splitter | msg=Env variable INSERT_MAX_SIZE not set, using default value of: None | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 time=2022-07-25 02:47:20.393 | level=ERROR | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=_insert_entity_rows | comp=translators.timescale | msg=Failed to insert entities because of below error; translator will still try saving original JSON in "mtopeniot"."etfillinglevelsensor".__original_ngsi_entity__ | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 Traceback (most recent call last): File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 252, in execute self._context = self._c.execute_unnamed( File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 630, in execute_unnamed self.handle_messages(context) File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 767, in handle_messages raise self.error pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} 2022-07-25T14:47:20.396573459Z During handling of the above exception, another exception occurred: 2022-07-25T14:47:20.396578542Z Traceback (most recent call last): File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 370, in _insert_entity_rows res = self.cursor.executemany(stmt, batch) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 304, in executemany self.execute(operation, parameters) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 279, in execute raise cls(msg) pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} time=2022-07-25 02:47:20.399 | level=DEBUG | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=dispose | comp=translators.timescale | msg=Translation completed | time=184.892 msec | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 time=2022-07-25 02:47:20.399 | level=DEBUG | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=read | comp=utils.cfgreader | msg=Env variable WQ_OFFLOAD_WORK not set, using default value of: False | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 time=2022-07-25 02:47:20.400 | level=ERROR | corr=None | from=172.18.1.7 | srv=openiot | subserv=/ | op=notify | comp=reporter.reporter | msg=Notification not processed or not updated: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} | payload=[{'id': 'urn:ngsi-ld:Device:filling001', 'type': 'FillingLevelSensor', 'location': {'type': 'geo:json', 'value': {'type': 'Point', 'coordinates': [13.3505, 52.5144]}}, 'filling': {'type': 'Property', 'value': 0.96, 'unitCode': 'C62', 'observedAt': '2022-07-25T14:47:20.000Z'}, 'time_index': '2022-07-25T14:47:20+00:00', 'location_centroid': {'type': 'geo:point', 'value': '52.5144, 13.3505'}}] | thread=274992937760 | process=12 Traceback (most recent call last): File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 252, in execute self._context = self._c.execute_unnamed( File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 630, in execute_unnamed self.handle_messages(context) File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 767, in handle_messages raise self.error pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} 2022-07-25T14:47:20.404963459Z During handling of the above exception, another exception occurred: 2022-07-25T14:47:20.404967209Z Traceback (most recent call last): File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 370, in _insert_entity_rows res = self.cursor.executemany(stmt, batch) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 304, in executemany self.execute(operation, parameters) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 279, in execute raise cls(msg) pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} 2022-07-25T14:47:20.404994709Z During handling of the above exception, another exception occurred: 2022-07-25T14:47:20.404998376Z Traceback (most recent call last): File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 252, in execute self._context = self._c.execute_unnamed( File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 630, in execute_unnamed self.handle_messages(context) File "/usr/local/lib/python3.8/site-packages/pg8000/core.py", line 767, in handle_messages raise self.error pg8000.exceptions.DatabaseError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} 2022-07-25T14:47:20.405185084Z During handling of the above exception, another exception occurred: 2022-07-25T14:47:20.405188959Z Traceback (most recent call last): File "/src/ngsi-timeseries-api/src/reporter/reporter.py", line 192, in notify InsertAction(fiware_s(), fiware_sp(), fiware_correlator(), payload) \ File "/src/ngsi-timeseries-api/src/wq/core/task.py", line 253, in enqueue run_action(self) File "/src/ngsi-timeseries-api/src/wq/core/task.py", line 305, in run_action target.run() File "/src/ngsi-timeseries-api/src/wq/ql/notify.py", line 87, in run self._handle_exception(svc, e) File "/src/ngsi-timeseries-api/src/wq/ql/notify.py", line 92, in _handle_exception raise e File "/src/ngsi-timeseries-api/src/wq/ql/notify.py", line 85, in run trans.insert(data.payload, svc, svc_path) File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 224, in insert res = self._insert_entities_of_type(et, File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 357, in _insert_entities_of_type self._insert_entity_rows(table_name, col_names, entries, entities) File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 397, in _insert_entity_rows self._insert_original_entities_in_failed_batch( File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 453, in _insert_original_entities_in_failed_batch self.cursor.executemany(stmt, rows) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 304, in executemany self.execute(operation, parameters) File "/usr/local/lib/python3.8/site-packages/pg8000/legacy.py", line 279, in execute raise cls(msg) pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P01', 'M': 'relation "mtopeniot.etfillinglevelsensor" does not exist', 'P': '13', 'F': 'parse_relation.c', 'L': '1170', 'R': 'parserOpenTable'} ```
c0c0n3 commented 2 years ago

hi @jason-fox, thanks so much for this detailed report, it really helps heaps to have all that info in one place and being able to reproduce!

I think the reason why QL isn't able to insert the notification coming from Orion is that it can't create the Timescale table where to store the NGSI entity. In turn, the reason for not being able to create the table is that the geometry type is missing from the Postgres catalogue. In fact, QL needs to create a table to store the FillingLevelSensor entity and that table has to have a geometry column to store the location property. So where does the geometry type come from and how to I add it to the Postgres catalogue? Well, the QL Postgres DB needs a bit of setup before QL can actually use it. As part of that setup, the Postgis extension should get installed. Fortunately we've got a QL init script that takes care of the whole init procedure for you. We packed the script in a Docker image you can use as an init container: orchestracities/quantumleap-pg-init.

Bottomline: to use QL with Timescale as a backend, you need to run the QL init container. Adding this stanza to the Docker Compose file you linked should do the trick:

quantumleap-db-setup:
    image: orchestracities/quantumleap-pg-init
    depends_on:
      - timescale
    environment:
      - QL_DB_PASS=ql
      - QL_DB_INIT_DIR=/ql-db-init
      - PG_HOST=timescale
      - PG_PASS=ql

Let me know if that works...

c0c0n3 commented 2 years ago

oh, one more thing. I see the tenant gets specified through the new NGSILD-Tenant header. We're not using that header at the moment---see #664 about it. So if Orion sends a notification with that header (i.e. NGSILD-Tenant: openiot) but no fiware-service header, then QL will store the notified entity in the default tenant schema, not in the mtopeniot.et fillinglevelsensor table as you might expect...

SBlechmann commented 1 year ago

Hey there,

it's really nice that you guys work on handling LD data! I saw that there has been a merge into the master branch lately regarding the ngsi-tenant header. Do you still need any test people for this? And do you know when there will be a new release? :)

Thanks!

c0c0n3 commented 1 year ago

Hi @SBlechmann :-)

Do you still need any test people for this?

You're welcome to give it a try and report back if there's any issues :-) The change set got merged into master (#721) and is available in the latest Docker build on Docker Hub.

do you know when there will be a new release?

We're planning to release 0.9 by the 3rd of April.

c0c0n3 commented 1 year ago

closing this issue since as of #721, QuantumLeap supports the NGSI tenant header.

c0c0n3 commented 1 year ago

For the record, kudos to @rohit-vrrr who implemented the feature!

SBlechmann commented 1 year ago

Just a quick feedback: For my setup everything worked just fine :) QL works with both v2 and LD at the same time.

c0c0n3 commented 1 year ago

@SBlechmann that's great, thank you sooo much for testing, much appreciated!!