Uploaded image for project: 'Help-Desk'
  1. Help-Desk
  2. HELP-17431

FIWARE.Question.Tech.fiware quantumleap insert into cratedb not working (schema missing).

    Details

      Description

      Created question in FIWARE Q/A platform on 24-02-2021 at 09:02
      Please, ANSWER this question AT https://stackoverflow.com/questions/66348247/fiware-quantumleap-insert-into-cratedb-not-working-schema-missing

      Question:
      fiware quantumleap insert into cratedb not working (schema missing)

      Description:
      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

      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}
      ]

      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

      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

      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]

      Tables in cratedb after running qunatumleap for a while:

      screenshot of cratedb tables

      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"
      1. - "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
                                    1. 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

        Activity

        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open In Progress In Progress
        5m 22s 1 Fernando Lopez 01/Mar/21 10:46 AM
        In Progress In Progress Answered Answered
        2s 1 Fernando Lopez 01/Mar/21 10:46 AM
        Answered Answered Closed Closed
        3s 1 Fernando Lopez 01/Mar/21 10:46 AM

          People

          • Assignee:
            jason.fox Jason Fox
            Reporter:
            backlogmanager Backlog Manager
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: