Migrating from MySQL to GridDB

Database migrations can be quite difficult and time-consuming to perform. This is why many businesses simply choose to forego them. While there might be large performance and cost benefits of using a new database, there are still many considerations to be made doing a migration. Common concerns developers might have when doing a database migration generally include having to remodel the existing database’s schema to fit into a NoSQL model. Another concern might be having to find a way to fetch and rearrange the relational data into the new database. To address these concerns, this post will demonstrate a brief example of how to perform a migration from a relational MySQL database to a new NoSQL GridDB database.

Industry Example

All of the data for our migration will relate to the maintenance of a Photovoltaic site, or PV site. A PV site is an industrial site equipped with hundreds-to-thousands of solar panels that collect power and transmit it to a power grid. A PV site can also be known as a solar farm. There can be up to thousands of internet-capable sensors involved in this process. Data reports from these sensors can include measurements such as voltage readings and alerts. An industrial site like this can scale to many different sizes and involve various types of devices. GridDB with its high performance, scalability, and flexibility make it the best database to migrate to for this PV site.

IoT time series database use case

Translating MySQL Schema to GridDB Schema

The MySQL database for this site will have 4 Tables. These 4 tables will translate into 4 container schemas in GridDB. These schemas will be implemented as 4 Java classes since we will be performing the migration in Java.

The tables and their GridDB schemas are listed below:

1. Facilities: Contains information and specifications for the facilities of the PV site.

1. Sensor: Stores general information like sensor-type and name on the PV site’s sensors

1. Readings: Stores the timestamps and values of the measurements recorded by the PV site’s sensors

1. Alerts: Stores information related to alerts and notification sent by the sensors

The schema diagrams for both our MySQL 'pv' database and our migrated GridDB database can be seen in the schema diagram below.

Data Modeling in GridDB

MySQL is a relational database and hence uses a relational model for designing databases. The power in relational database design is in identifying and creating relations among data records. In the case of the PV Site, our MySQL database consists of several one-to-many relations imposed with FOREIGN KEYS. Two examples would be the relation between sensors and facilities and the relation between readings and sensors.

GridDB, as a NoSQL database that provides much more flexibility by using a KEY-CONTAINER model of relating data. In the key-container model, rows stored in one container can have fields that can be used as keys to other containers in GridDB. For the PV site, every Sensor row would be stored in a Collection container whose key matches to a field in the Facility class. To go further, every Reading row would be stored in a TimeSeries container whose key matches to a field in the Sensor class. This type of model can be extended to any depth and can include many containers.

Performing the Migration

Now that we have our data model and schemas for GridDB created, we can perform the full data migration. The approach we will use is to SELECT all the rows from each table in MySQL and create a new row in GridDB and insert it into a GridDB container.

Begin by connecting to the MySQL 'pv' database using JDBC driver. We will follow by connecting to our GridDB cluster using the GridDB Java API.

Migrating the Facilities Data

The first table to migrate from MySQL will be the facilities table into a GridDB Collection of the same name. It will have a similar schema to the MySQL table.

To begin the data migration, we will SELECT all the rows from the table in MySQL. The next step would be to reformat each row into the Facility class schema in GridDB.

Additionally, as a way to improve scalability, we will create a Collection (named with the facility’s facility id) with a schema similar to the sensors table from MySQL. Each facility container will be responsible storing the sensors for that facility.

The conversion of Blob types from MySQL to GridDB is not so intuitive on the other hand. Luckily we will detail the process of handling MySQL Blobs in the section below.

an example is detailed in the code snippet below where the specifications column is translated to the specifications attribute in the Facility class.

Handling MySQL Blob Data

We begin by creating a ByteArrayOutputStream to write our Blob bytes from MySQL to. Next we obtain a BufferedInputStream for storing the bytes of the MySQL Blob. To get the MySQL Blob byte data or binary data from the MySQL row, we call the .getBinaryStream method on the Blob column (specifications). From there we write arrays of byte data from the binary stream (the BufferedInputStream) to our ByteArrayOutputStream.

Once our ByteArrayOutputStream is fully written to, we can create a GridDB Blob. To create a GridDB blob, we need all our data as a byte array. We obtain the byte array by calling the .toByteArray() method on our ByteArrrayOutputStream. The last thing to do is set the Blob column of our GridDB row by setting the specifications column or attribute to the SerialBlob of our output stream’s byte array.

Now that the Blob type is handled, the new row can be inserted into the GridDB collection.

Collection<String,Facility> collection = gridstore.putCollection("facilities",Facility.class);

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM pv.facilities");

// Iterate every row in the 'facilites' table in MySQL
while(resultSet.next()){
    Facility facility = new Facility();
    
    facility.facilityId = resultSet.getString("faciltiyId");
    facility.name = resultSet.getString("name");
    
    // Create a Sensor Collection for storing sensors in a facility
    gridstore.putCollection(facility.facilityId,Sensor.class);

    //Translate MySQL Blob to GridDB
    ByteArrayOutputStream byteStream = new ByteArrayOutputStream();
    InputStream inputStream = new BufferedInputStream(resultSet.getBinaryStream("specifications"));

    byte buffer[] = new byte[1024];    
    while(inputStream.read(buffer) != -1)
        byteStream.write(buffer);
    inputStream.close();

    // Set the GridDB Blob column to the binary data obtained from MySQL
    facility.specifications = new SerialBlob(byteStream.toByteArray());
        
    collection.put(facility.facilityId,facility);
}

Migrating Sensor Data

The second table to migrate is the sensors table from MySQL. We will go through the same process of selecting all the rows from the table and rearranging each row to be inserted into a GridDB collection.

Each row in this table has a FOREIGN KEY column, facilityId. This column details which facility a sensor belongs to. When we migrate this row into GridDB, we will get that column value from the row and retrieve the appropriate Collection container that corresponds to that facility.

We then create a new Sensor row from the rest of the column values in the MySQL row and insert it into the collection container in GridDB.

Each sensor will also get its own TimeSeries container for storing the sensor’s measurements. (The sensor’s sensor id will be the Timeseries’s name/key). The container will have the Reading class schema.

ResultSet resultSet = statement.executeQuery("SELECT * FROM pv.sensors");

// Iterate all sensors in the MySQL 'sensor' table
while(resultSet.next()){
    String facilityId = resultSet.get("facilityId");
    Collection<String,Sensor> collection = gridstore.getCollection(facilityId,Sensor.class);
    
    Sensor sensor = new Sensor();
    
    sensor.sensorId = resultSet.getString("sensorId");
    gridstore.putTimeSeries(sensor.sensorId,Reading.class);

    sensor.name = resultSet.getString("name");
    sensor.type = resultSet.getString("type");

    collection.put(sensor.sensorId,sensor);
}

Migrating Sensor Readings

The third table we migrate is the readings table which should be noted for containing timeseries data.

To begin, we need a way to translate a MySQL TIMESTAMP which is stored as a java.sql.Timestamp object into a GridDB Timestamp which is stored as a java.util.Date object.

Date parseTimestamp(Timestamp timestamp){
    Date date = new Date(timestamp.getTime());
    return date;
}

When we iterate through every retrieved row from the readings MySQL table, we will use the sensorId column value from MySQL row to determine which TimeSeries container the new GridDB Reading will be inserted into. Just like all the other tables we fetch all the other column values for our new Reading row in GridDB from the column fields of the row in MySQL.

ResultSet resultSet = statement.executeQuery("SELECT * FROM pv.readings ORDER BY tstamp");

// Iterate through every row retrieved from 'readings' table
while(resultSet.next()){
    String sensorId = resultSet.getString("sensorId");
    TimeSeries<Reading> timeseries = gridstore.getTimeSeries(sensorId,Reading.class);

    Timestamp timestamp = resultSet.getTimestamp("tstamp");
    Date rowKey = parseTimestamp(timestamp);

    Reading reading = new Reading();
    reading.tstamp = rowKey;
    reading.value = resultSet.getDouble("value");
    reading.status = resultSet.getString("status");

    timeseries.put(reading.tstamp,reading);
}

Migrate Alerts Table

The last table we will migrate is the alerts. This migration is rather simple and fairly akin to the migration for the facilities table.

The first step is to create an Collection container in GridDB for storing the alert notifications. It will have the Alert schema.

From there simply iterate through every row in the alerts table, and translate each row into an Alert object to be inserted as a row in GridDB.

ResultSet resultSet = statement.executeQuery("SELECT * FROM pv.alerts");
Collection<Integer,Alert> collection = gridstore.putCollection("alerts",Alert.class);

// Iterate through every row in the 'alerts' table
while(resultSet.next()){
    Alert alert = new Alert();

    Timestamp timestamp = resultSet.getTimestamp("tstamp");
    alert.tstamp = parseTimestamp(timestamp);

    alert.alertId = resultSet.getInt("id");
    alert.facilityId = resultSet.getString("facilityId");
    alert.sensorId = resultSet.getString("sensorId");
    alert.level = resultSet.getInt("level");
    alert.detail = resultSet.getString("detail");

    collection.put(alert.alertId,alert);
}

Conclusion

Our migration is now complete: all data from our MySQL database is now in GridDB. Now you can drop your old MySQL database and continue with a new faster, more scalable GridDB database.

MySQL is a relational database used heavily in business intelligence, web development, and marketplace and inventory management. For the purpose of this tutorial we set up an initial relational database using MySQL Version 5.7 on CentOS 7. We used GridDB Community Edition Build 3.0.1 as our GridDB database.

An photovoltaic site was used as the use-case for our database migration. More use-cases of GridDB in the IoT industry can be found in this post.

Originally published at griddb.net.