store/filter local mqtt broker payloads to h2 db instance from kura wire ?

[aminesig]

Is there some way to filter and persist mqtt broker incoming messages to the local sql database ? possibly from kura wire.

[MMaiero]

Hi, could you describe better your context? Are you using the internal MQTT broker available in Kura or referring to something else?

Kura has already available a subscriber block that subscribes to an mqtt broker (internal or external) and other blocks for db storing and db filtering that may fit your needs.

Best regards, Matteo

[aminesig]

I am using the embedded broker, just to explain better my use case

Embedded broker --> payload (wire subscriber ) --> extract data from the payload(filter or conditional wire component) --> save it to the db dynamically(db store wire component ) !
Don't know how to do it using wire graph! don't know how to store dynamic data with dynamic values in a preconfigured table

BR, Amine

[MMaiero]

For the first element you need to create a cloud connection pointing to the internal broker. Then, in wire composer, create a wire subscriber that uses the just created cloud connection and that subscribes to a certain topic.

For the db component, it will create the table depending on your data. You'll have just simply to wire it to the graph to have the messages automatically stored.

Give it a try, I believe you will figure out pretty easily how to configure the graph and the different components. Maybe use also the logger in the different stages to understand if the data flows as expected.

[aminesig]

I have managed to subscribe to the local borker, publish messages and recieve them, I can see the messages on the console kura.log file " INFO o.e.k.i.c.e.m.c.CloudConnectionManagerImpl - Message arrived on topic: c/g/b/req/temp ", but nothing get stored in the dedicated table for the mqtt payloads storage. The only data I can see stored are the login and disconnection from the broker in this table "org.eclipse.kura.cloudconnection.eclipseiot.mqtt.DataService" created by kura when I added the new datasource connection.

[MMaiero]

but nothing get stored in the dedicated table for the mqtt payloads storage

Where are you looking at to get those results? What is your wire graph? Could you post a picture?

[aminesig]

this is the H2db web console

and this is the graph

[MMaiero]

ok, the graph seems correct. Could you also post the configuration of dbstore? While running, is the graph blinking (meaning that there is data flow)? Could you also post the values of kura.log and kura-console.log?

[aminesig]

these are the files as requested : kura.log kura-console.log

[MMaiero]

I believe one issue is related to the database configuration: compared to the default configuration explained here where db.connector.url is set to jdbc:h2:mem:kuradb, in your case is set to jdbc:h2:~/mqtt

[aminesig]

jdbc:h2:~/mqtt is just another db (a persistent one ) that I have created with it's own service and own PID, the kura graph creates the table automatically inside the in memory default database service (named "buf" which I didn't create manually ). Anyways I tried to delete the mqtt db service and nothing changed.

PS: I don't see the kura wire graph blinking.

[MMaiero]

Got it. From the log I don't see received messages. From what I remember something should be logged about received messages. I would check that. And the fact that you don't see the graph blinking seems to confirm that hypothesis. Maybe connect the logger to the subscriber to log incoming messages received by the subscriber.

[aminesig]

I have connected the sub component to the logger, this is what I have got from the log file and this is the payload : are there any problems with the mqtt payload structure ?

[MMaiero]

The message seems to be received by Kura's cloud stack but not propagated to the subscriber. And that is the reason why you see no message logged by the logger component. I would check the cloud subscriber configuration to check that is actually bound to a proper subscriber component in the "Cloud Services" section.

[aminesig]

I don't understand this bit, I tried to check the configuration and the cloud connection I have set doesn't have any subscriber binding ? where do I find it ? the only thing I can add is a cloud publisher in that window(which I already did with the same topic I am sending the messages to).

[MMaiero]

Sorry, my bad. I forgot to ask which version of Kura you are running. I assumed you were running Kura 4. For this last version of Kura you need to:

1. Configure the cloud connection

2. Define a subscriber that refers to the cloud connection and where you specify the subscription topic (as depicted in the following image)

   

3. Create the wire graph with the wire subscriber component and configure this last to bind to the subscriber defined in point 2.

[aminesig]

I am running KURA_4.0.0, and that option is not available in my cloud settings to add, I don't have any subscribers as you can see in the pic below

[MMaiero]

That is because you are using the Eclipse IoT integration connection. The protocol defined does not support custom subscriptions so that is the reason why you don't see it available in the list.

The option showed is, indeed, available in the default CloudService connection which support custom subscriptions.

[aminesig]

Ok it's working now, every time I publish the wire graph blinks, the new time stamp is inserted to the table created by the graph to store the messages, the only thing missing is the content of the message, this is the log generated.

2018-12-27T12:03:40,741 [MQTT Call: rpi] INFO o.e.k.i.w.l.Logger - Received WireEnvelope from org.eclipse.kura.wire.H2DbWireRecordStore-1545911919247-12 2018-12-27T12:03:40,741 [MQTT Call: rpi] INFO o.e.k.i.w.l.Logger - Record List content: 2018-12-27T12:03:40,742 [MQTT Call: rpi] INFO o.e.k.i.w.l.Logger - Record content: 2018-12-27T12:03:40,742 [MQTT Call: rpi] INFO o.e.k.i.w.l.Logger -

so my question is, do I have to structure the payload somehow to be able to see it on the log and on the db table ?

[MMaiero]

As described here, the default cloud connection supports both KuraProtobuf and simple JSON. The choice between the two is done in CloudService configuration.

[aminesig]

Never mind @MMaiero , I have already read the docs about the json file formatting and structure. Thanks a lot! for everything. You can close this issue.

[MMaiero]

Great! Thank you for the update