MQTT->MQTT broker->TQL->MSSQL

[EPIC3D]

Hello.

I'm looking for help to understand the workings of machbase-neo better. I do not see any discussions anywhere yet. Is this because machbase-neo is a new project? I see it started around 2023 February.

I found machbase-neo because I need a compact system that can handle MQTT JSON data, have a MQTT broker, and can transfer this to a database (ms sql or machbase own db?) for uses with Grafana. If I'm not wrong I can do all that too with machbase-neo. Even drawing graphs and statistics. (I do not wish to use AWS)

AXIS MQTT (JSON) -> MQTT-Broker -> bridge(mqtt) -> ????(TQL?))))) -> bridge (ms sql) -> grafana
Device sending data to broker, I created the bridge to the machbase MQTT broker, and created a bridge to the MS SQL database where I have the tables ready.

Now I need subscribe and save the data to MSSQL. If I understand right this can be done with a TQL script. I think I'm stuck with the TQL script writing part right now. I tried to follow the tutorials, but it is not enough or it just not clicks for me how it supposed to work.

Here is an example output of an AXIS payload:

{
"topic": "axis:CameraApplicationPlatform/ObjectAnalytics/Device2Scenario1",
"timestamp": 1732653071807,
"message": {
"source": {},
"key": {},
"data": {
"totalCar": "3683",
"reason": "car",
"totalBike": "0",
"totalBus": "5",
"totalTruck": "268",
"total": "3956",
"resetTime": "2024-11-25T23:00:00Z",
"scenario": "Scenario 1",
"totalOtherVehicle": "0"
}
}
}

[OutOfBedlam]

welcome to machbase-neo, I'll upload some example or tutorial of your case. It could be take one or couple of days.

[EPIC3D]

Thank you very much. I appreciate it.

So if I understand it right, machbase-neo have all the needed components. So I might not need MS SQL or Grafana.

MQTT PUB -> MQTT Broker (Machbase) -> MQTT bride (is it needed if I use the machbase broker?) -> TQL -> timer for TQL -> machbasedb -> monitoring on dashboard (Realtime and history)

[OutOfBedlam]

I have update a new tutorial for your case. --here https://docs.machbase.com/neo/tutorials/mqtt-bridge-sqlite/

The tutorial that demonstrates:

MQTT Client --> MQTT Broker (mosquitto) --> machbase-neo --> External DB (sqlite)

In this setup, machbase-neo functions as an MQTT Client (subscriber), a DB Client, and an Application (TQL script parsing JSON document).

While this scenario showcases the capabilities of machbase-neo, it is not practical due to the complexity and numerous components involved for a simple task.

The simplest approach is:

MQTT Client --> machbase-neo

If your goal is to store data from an MQTT client and visualize it, you don't need external MQTT brokers or other databases.

• Create a table in machbase (see https://docs.machbase.com/dbms/).
• Set your MQTT client's destination address to the machbase-neo MQTT port, which is 5653. This port is fully compatible with MQTT standard v3.1, v3.1.1 and v5.
• The MQTT clients should publish to the TOPIC db/write/{TABLE_NAME} with a data format that machbase-neo understands (refer to https://docs.machbase.com/neo/api-mqtt/write/).
• As long as the data format is supported by machbase-neo, no additional steps are required to store the data.
• If your data format is not "standard", you will need a TQL script to handle your custom data format. Then the topic should be db/tql/{tql_file}.

For visualizing the stored data, you have several options:

• Use the dashboard feature of machbase-neo.
• Create a TQL script that generates charts and embed the TQL result into your application using the RESTful API.
• Develop your application to query the database via the RESTful API and generate your own visualizations.

[EPIC3D]

Thank you for the detailed tutorial.

I will try out both solutions, the simpler the better.

[EPIC3D]

MQTT Client --> MQTT Broker (mosquitto) --> machbase-neo --> External DB (sqlite) tutorial worked. Thank you.

I also tried to do the "db/write/{TABLE_NAME}" method but it looks like the JSON format is not standard, so it did not write in the DB.
So I tried db/tql/{tql_file} method.

From your example I created a modified TQL script. Which after some tinkering works.

Now I need to make some nice graphs and place it on the dashboard.