[Tutorial] How to use DB service

InnoWorks競賽
1

[Tutorial] How to use DB service

  • Before reading this tutorial, suggest completing the WISE-PaaS Core Level 2 certification course.
  • Each team will receive a DB secret for accessing the cloud databases to query data or create new collections(mongodb) or tabels(PostgreSQL)
    • MongoDB:
      • Store historical data that is uploaded to each Datahub’s node.
      • Using accessing tools like Robo3T or Compass to access MongoDB
    • PostgreSQL:
      • Store Datahub’s and Dashboard’s configurations.
      • Using tools like pgadmin or dbeaver to access.
    • RabbitMQ:
      • Including DataHub MQTT broker connection credentials, for transfer and monitoring data between cloud and edge device.
      • Using MQTT client(e.g MQTTbox) to connect.

MongoDB

  • Using editor like VScode to beautify the JSON, and view the MongoDB’s part.

    1376×498 48.9 KB

  • For Robo3T, the connection settings:

    • In connection tab, fill the extermalHost from Json into host, port number is 27017 as default.
    • In Authentication, check the perform authentication option.
      • Fill in the Database with the database list in JSON.
      • Fill in the username and password
      • Check the “Manually specify visable database” options
        • Fill in the Database with the database list in JSON.
      • Do not check SSH and TLS options
    • Click Test to ensure the credentials are right to connect.

  • After connection, view the collections in the database

    • The name of the collection that starts with “datahub”, was generated by Datahub.
      • The collection named datahub_HistRawData_xxx is the historical data that upload to datahub, the id that after the collection’s name is Datahub’s node ID
        1362×652 94 KB
    • Create a new collection and use mongoimport to import data into MongoDB.
      • For AIFS or other APP to use.

PostgreSQL

  • Using an editor like VScode to view the Postgres credentials in the JSON file

  • Using DB connection tools like DBeaver, create a new Postgres connection

    • Fill in host with the extermalHost in JSON, port number as 5432
    • Fill in database, username, and password
    • Click “Test Connection” to test if the connection is established.

  • View the schemas, the datahub schema are the datahub’s configurations, please don’t edit it, or will cause an error to Datahub.

    1352×458 35 KB

  • To create new schema and tables in Postgres

    • Select the Database and Owner as the one in JSON
    • Create tabels under the schemas
    • After creating the tables, press save at the right bottom corner to save the change
      1395×704 54.6 KB

RabbitMQ

  • RabbitMQ as the WISE-PaaS’s Message-oriented middleware(MOM), is the place to exchange the message but not to process them. It provides message exchange and load balancing functions.

  • By Publish/Subscribe patterns the message from edge devices uses a different “topic” to publish to the message broker, and the subscriber subscribes specific topic to receive a message from specific devices.

  • To connect Rabbitmq’s message broker:

    • View p-rabbitmq in the JSON file
    • rabbitmq provide multiple trasfer protocols such as MQTT
    • Using MQTT, use MQTTBox as mqtt client:
      • Retrieve the credentials.externalHosts in JSON
      • Find mqtt under the protocols section in JSON
      • Retrieve username and password
    • Fill the credentials into mqttbox
    • To use “Topic” to monitoring datahub data transfer, use the follwing topic to monitor spcific node on datahub.
      • /wisepaas/scada//#
    • Using wild card like “#” or “*” to subscribe or publish to multiple topics.
      863×479 24.7 KB