Search code examples
fiwarefiware-orioncratedb

fiware quantumleap insert into cratedb not working (schema missing)

goal

Use qunatumleap to move data into a crate_db to display later using Grafana.

what I did

  • follow tutorial to setup Docker images
  • setup opc-agent to provide data to the orion broker
  • setup quantumleap to move data from broker to crate_db on change
  • checked that a subscription is present in the contextBroker

Expected behavior

on subscription of a new item quantumleap will create a entry in a table in the crate_db to store the provided values

what actually happens

Instead of creating a entry in the Crate_db quantumleap throws a "schema not existing" fault. The provided tutorials do not talk about setting those schema up myself, therefore I assume that quantumleap normally sets them up. Right now I do not know why this is failing, most likely it is a configuration mistake on my side

additional information

  1. subscription present in contextBroker: 
         curl -X GET \
       'http://localhost:1026/v2/subscriptions/' \
       -H 'fiware-service: openiot' \
       -H 'fiware-servicepath: /'

 [
{"id":"60360eae34f0ca493f0fc148",
"description":"plc_id",
"status":"active",
"subject":{"entities":[{"idPattern":"PLC1"}],
"condition":{"attrs":["main"]}},
"notification":{"timesSent":1748,
"lastNotification":"2021-02-24T08:59:45.000Z",
"attrs":["main"],
"onlyChangedAttrs":false,
"attrsFormat":"normalized",
"http":{"url":"http://quantumleap:8668/v2/notify"},
"metadata":["dateCreated","dateModified"],
"lastSuccess":"2021-02-24T08:59:45.000Z",
"lastSuccessCode":500},
"throttling":1}
]
  1. Orion log:
orion_1        | INFO@09:07:55  logTracing.cpp[130]: Request received: POST /v1/updateContext, request payload (327 bytes): {"contextElements":[{"type":"plc","isPattern":"false","id":"PLC1","attributes":[{"name":"main","type":"Number","value":"12285","metadatas":[{"name":"SourceTimestamp","type":"ISO8601","value":"2021-02-24T09:07:55.033Z"},{"name":"ServerTimestamp","type":"ISO8601","value":"2021-02-24T09:07:55.033Z"}]}]}],"updateAction":"UPDATE"}, response code: 200
  1. Quantum Leap log:
quantumleap_1  | time=2021-02-24 09:07:55.125 | level=ERROR | corr=c7df320c-767f-11eb-bbb3-0242ac1b0005; cbnotif=1 | from=172.27.0.5 | srv=openiot | subserv=/ | op=_insert_entity_rows | comp=translators.crate | msg=Failed to insert entities because of below error; translator will still try saving original JSON in "mtopeniot"."etplc".__original_ngsi_entity__ | payload=[{'id': 'PLC1', 'type': 'plc', 'main': {'type': 'Number', 'value': '12285', 'metadata': {'dateCreated': {'type': 'DateTime', 'value': '2021-02-24T08:28:59.917Z'}, 'dateModified': {'type': 'DateTime', 'value': '2021-02-24T09:07:55.115Z'}}}, 'time_index': '2021-02-24T09:07:55.115000+00:00'}] | thread=140262103055136  | process=67
  1. Traceback from Qunatumleap
quantumleap_1  | Traceback (most recent call last): quantumleap_1  |   File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 365, in _insert_entity_rows 
quantumleap_1  |     self.cursor.executemany(stmt, rows) quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/cursor.py", line 67, in executemany quantumleap_1  |     self.execute(sql, bulk_parameters=seq_of_parameters) 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/cursor.py", line 53, in execute quantumleap_1  |     self._result = self.connection.client.sql(sql, parameters, 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 331, in sql quantumleap_1  |     content = self._json_request('POST', self.path, data=data) 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 458, in _json_request quantumleap_1  |     _raise_for_status(response) 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 187, in _raise_for_status 
quantumleap_1  |     raise ProgrammingError(error.get('message', ''), 
quantumleap_1  | crate.client.exceptions.ProgrammingError: SQLActionException[SchemaUnknownException: Schema 'mtopeniot' unknown] quantumleap_1  |  quantumleap_1  | During handling of the above exception, another exception occurred: quantumleap_1  |  
quantumleap_1 | Traceback (most recent call last): 
quantumleap_1  |   File "/src/ngsi-timeseries-api/src/reporter/reporter.py", line 195, in notify quantumleap_1  |     trans.insert(payload, fiware_s, fiware_sp) 
quantumleap_1  |   File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 221, in insert 
quantumleap_1  |     res = self._insert_entities_of_type(et, 
quantumleap_1  |   File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 354, in _insert_entities_of_type 
quantumleap_1  |     self._insert_entity_rows(table_name, col_names, entries, entities) quantumleap_1  |   File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 381, in _insert_entity_rows 
quantumleap_1  |     self._insert_original_entities_in_failed_batch( 
quantumleap_1  |   File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 437, in _insert_original_entities_in_failed_batch 
quantumleap_1  |     self.cursor.executemany(stmt, rows) 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/cursor.py", line 67, in executemany 
quantumleap_1  |     self.execute(sql, bulk_parameters=seq_of_parameters) 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/cursor.py", line 53, in execute 
quantumleap_1  |     self._result = self.connection.client.sql(sql, parameters, 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 331, in sql 
quantumleap_1  |     content = self._json_request('POST', self.path, data=data) 
quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 458, in _json_request 
quantumleap_1  |     _raise_for_status(response) quantumleap_1  |   File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 187, in _raise_for_status 
quantumleap_1  |     raise ProgrammingError(error.get('message', ''), 
quantumleap_1  | crate.client.exceptions.ProgrammingError: SQLActionException[SchemaUnknownException: Schema 'mtopeniot' unknown]
  1. Tables in cratedb after running qunatumleap for a while:

screenshot of cratedb tables

  1. docker-compose file
version: "3"

services:
  iotage:
    hostname: iotage
    image: iotagent4fiware/iotagent-opcua
    networks:
      - hostnet
      - iotnet
    ports:
      - "4001:4001"
      - "4081:8080"
    extra_hosts:
      - "iotcarsrv:192.168.2.16"
 #     - "PLC1:192.168.2.57"
    depends_on:
      - iotmongo
      - orion
    volumes:
      - ./certificates:/opt/iotagent-opcua/certificates
      - ./AGECONF:/opt/iotagent-opcua/conf
    command: /usr/bin/tail -f /var/log/lastlog

  iotmongo:
    hostname: iotmongo
    image: mongo:3.4
    volumes:
      - iotmongo_data:/data/db
      - iotmongo_conf:/data/configdb
       
  crate-db:
    image: crate
    hostname: crate-db
    ports:
        - "4200:4200"
        - "4300:4300"
    command:
        crate -Clicense.enterprise=false -Cauth.host_based.enabled=false  -Ccluster.name=democluster
        -Chttp.cors.enabled=true -Chttp.cors.allow-origin="*"
    networks:
      - hostnet
        
  quantumleap:
    hostname: quantumleap
    image: smartsdk/quantumleap
    ports:
        - "8668:8668"
    depends_on:
        - crate-db
    environment:
        - CRATE_HOST=crate-db
    networks:
      - hostnet
        
  grafana:
    image: grafana/grafana
    depends_on:
        - crate-db
    ports:
        - "3003:3000"
    networks:
      - hostnet

  ################ OCB ################

  orion:
    hostname: orion
    image: fiware/orion:latest
    networks:
      - hostnet
      - ocbnet
    ports:
      - "1026:1026"
    depends_on:
      - orion_mongo
    #command: -dbhost mongo
    entrypoint: /usr/bin/contextBroker -fg -multiservice -ngsiv1Autocast -statCounters -dbhost mongo -logForHumans -logLevel DEBUG -t 255

  orion_mongo:
    hostname: orion_mongo
    image: mongo:3.4
    networks:
      ocbnet:
        aliases:
          - mongo
    volumes:
      - orion_mongo_data:/data/db
      - orion_mongo_conf:/data/configdb
    command: --nojournal

volumes:
  iotmongo_data:
  iotmongo_conf:
  orion_mongo_data:
  orion_mongo_conf:

networks:
  hostnet:
  iotnet:
  ocbnet:

edits

  • added docker compose file
Solution

  • after changing the database to a more recent version (for example crate-db:3.1.2) the data arrives at the database nicely