Migrating Historical Data to the NoSQL Database

June 18th, 2020

You can migrate the historical point values from a Derby, H2 or MySQL database. This even works from an older Mango M2M database which is the only way to easily upgrade from Mango M2M to Mango Automation.

The NoSQL migration can take several hours to complete but it will happen in the background. Even during migration Mango will continue to collect and store current point values into the NoSQL database so no data is lost in the process.

You will find the NoSQL Migration tools under Administration > Admin home > Utilities > Mango NoSQL

Screenshot 2019-08-17 18.21.31.png

This page is used to import data from an external SQL database while Mango is running.

The Driver class name specifies the Java class that implements the JDBC Driver interface within your JDBC driver. This value can be found in the documentation for your driver. The following is a list of driver class names for the given database, and is provided as convenience only. Note that connectivity typically requires the installation of driver software on the Mango server. This is achieved by placing the Driver's .jar file into the SQL datasource's “lib” directory, again referring to the documentation to locate the JDBC Driver interface.

  • MySQL: com.mysql.jdbc.Driver

  • Oracle thin driver: oracle.jdbc.driver.OracleDriver

  • Derby: org.apache.derby.jdbc.EmbeddedDriver

  • H2: org.h2.Driver

The Connection string depends both upon the database you are using and the specifics of its installation. Your JDBC driver documentation will explain how to derive the value for your database. The Username andPassword fields provide the necessary credentials for your database to accept connections from this application.

  • H2 Connection String example: jdbc:h2:./databases/mah2

  • MySQL Connection String example: jdbc:mysql://localhost:3306/<your mysql schema name>

  • Username and Password are the database user's login credentials. You can find this in your env.properties file.

Advanced Options

These values should be modified to assist in performance and memory management during large imports of data (millions or billions of point values). There is a tradeoff between speed/memory use and keeping mango collecting data effectively during this process.

The Number of threads defines how many threads the points will be broken into to concurrently import the data. For example if importing 10 points Mango can use up to 10 threads to import the data. The more threads the faster the import will run, but the more resources it will consume and potentially affect the data collection of Mango.

Thread priority can be set to any of the following:

  • High priority - will run all threads almost immediately in parallel in a pool of 1000's of threads (same priority as data collection)

  • Medium priority - will run in parallel without slowing down the high priority thread pool and will run in a pool of up to 30 threads.

  • Low priority - will run one thread at a time giving priority to all other threads

The Maximum point value queue size is used to ensure that Mango has enough memory to perform other tasks. Each thread will use a block of memory large enough to store this many point values while streaming them from the SQL database into MangoNoSQL. If the SQL database is able to provide Mango with data faster than it can store it, this block of memory will fill up. Once full Mango will slow down the rate at which it is receiving data from the SQL database to ensure that extra memory is not required.

Stopping a running migration is possible by pressing the Cancel button. This does not happen immediately as Mango must contact the database and request the query to be terminated. This is not supported on all database types but has been tested and works (slowly) on MySQL. Note that data will stop streaming to mango immediately as the results are discarded but this does not mean the query has stopped running on the Database end of the connection.

Copyright © 2024 Radix IoT, LLC.