MySQLからGridDBへの移行

はじめに

データベースの移行作業は、ときに非常に難しく手間や時間がかかることがあります。そのため、多くの企業が移行に関心がありながらも先送りしてしまいがちです。新しいデータベースへ移行することによりパフォーマンス向上とコストの削減が期待できますが、移行作業を行うにあたっては検討すべき事項がいくつかあります。通常、データベースの移行を行う際に開発担当者が懸念するのは、既存のデータベースのスキーマをNoSQLモデルに合わせて正しく変更することができるか、ということです。もう1つは、リレーショナルデータを取り出して新しいデータベースに再配置する方法を見つけなければならないことです。これらの問題に対処する開発担当者の方々のために、このブログでは、代表的なリレーショナルデータベースであるMySQLからNoSQLデータベースであるGridDBへの移行を実行する方法をご紹介します。

太陽光発電サイトにおける事例

これからご紹介する移行に関するデータは、太陽光発電(Photovoltaic) サイト、通称 PVサイトの保守に関連するものです。 PVサイトは、数百から数千のソーラーパネルを備えた産業用サイトで、電力を集めて電力網に送ります。 PVサイトはソーラーファームとしても知られ、このプロセスには数千ものインターネット対応センサーが関与しています。これらのセンサから、電圧測定値やアラートなどの測定値がデータレポートとして送られてきます。このような工業用サイトは、さまざまなサイズに拡張でき、さまざまな種類のデバイスに対応できます。高性能、スケーラビリティ、柔軟性を備えたGridDBは、このPVサイトに移行するための最良のデータベースであるといえます。

MySQLスキーマをGridDBスキーマに変換する方法

このサイトのMySQLデータベースには、 4つのテーブルがあります。これらの4つのテーブルは、4つのコンテナスキーマをGridDBに追加します。これらのスキーマは、Javaで移行を実行するため、4つのJavaクラスとして実装されます。

表とそのGridDBスキーマを以下に示します。

1. Facilities: Contains information and specifications for the facilities of the PV site.
MySQL Facilities Table
CREATE TABLE IF NOT EXISTS facilities (
   facilityId VARCHAR(40) NOT NULL,
   name VARCHAR(60) NOT NULL,
   specifications BLOB,
   PRIMARY KEY (facilityId)
);        
GridDB Facility Container Schema
public class Facility{
    @RowKey
    public String facilityId;
    public String name;
    public Blob specifications;
}
 
2. Sensor: Stores general information like sensor-type and name on the PV site’s sensors
MySQL Sensor Table
CREATE TABLE IF NOT EXISTS sensors (
   sensorId VARCHAR(40) PRIMARY KEY (sensorId),
   facilityId VARCHAR(40) NOT NULL,
   name VARCHAR(60) NOT NULL,
   type VARCHAR(30) NOT NULL,
   FOREIGN KEY(facilityId) REFERENCES facilities(facilityId) 
     ON DELETE CASCADE ON UPDATE CASCADE
);
GridDB Sensor Container Schema
//Row schema class similar to the 'Sensors' table
public class Sensor {
    @RowKey
    public String sensorId;

    public String name;
    public String type;
}
 
3. Readings: Stores the timestamps and values of the measurements recorded by the PV site’s sensors
MySQL Reading Table
CREATE TABLE IF NOT EXISTS readings (
   record_id INT AUTO_INCREMENT PRIMARY KEY(record_id),
   sensorId VARCHAR(40) NOT NULL,
   tstamp TIMESTAMP NOT NULL,
   value DECIMAL(19,4) NOT NULL,
   status VARCHAR(255) NOT NULL,
   FOREIGN KEY (sensorId) REFERENCES sensors(sensorId) 
     ON DELETE CASCADE ON UPDATE CASCADE
);
GridDB Reading Container Schema
//Row schema class similar to the 'Readings' table
public class Reading {
    @RowKey
    public Date tstamp;

    public double value;

    public String status;
}
 
4. Alerts: Stores information related to alerts and notification sent by the sensors
MySQL Alerts Table
CREATE TABLE IF NOT EXISTS alerts (
  id INT AUTO_INCREMENT PRIMARY KEY (id),
  tstamp TIMESTAMP NOT NULL,
  facilityId VARCHAR(40) NOT NULL,
  sensorId VARCHAR(40) NOT NULL,
  level INT NOT NULL,
  detail VARCHAR(255) NOT NULL,
  FOREIGN KEY (facilityId) REFERENCES facilities(facilityId) 
     ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (sensorId) REFERENCES sensors(sensorId) 
     ON DELETE CASCADE ON UPDATE CASCADE
);
GridDB Alerts Container Schema
//Row schema class similar to 'alerts' table
public class Alert {
    @RowKey
    public int alertId;
    
    public String facilityId;
    public String sensorId;
  
    public Date tstamp;
    public int level;
    public String detail;
}
 

MySQLの 'pv' データベースと移行後のGridDBデータベースのスキーマについては、下記の図の通りです。

GridDBにおけるデータモデリング

MySQLはリレーショナルデータベースであるため、データベースの設計にはリレーショナルモデルを使用します。 リレーショナルデータベース設計の強みは、データレコード間で リレーション を識別して作成することにあります。 PVサイトの場合、MySQLデータベースは FOREIGN KEYS で設定された複数の一対多の関係で構成されています。 2つの例は、センサーと施設との関係と測定値とセンサーの関係です。

GridDBは、関連するデータの キーコンテナモデルを使用することにより、より高い柔軟性を持つNoSQLデータベースです。キーコンテナ モデルにおいて、1つのコンテナに格納された行は、GridDB内の他のコンテナに対してkeys として使用できるフィールドを持つことができます。 PVサイトでは、すべてのSensorの行は、 Facility クラスのフィールドに一致するキーを持つCollection に格納されます。さらにすべてのReadingの行は、キーが Sensor クラスのフィールドに一致するTimeSeries コンテナに格納されます。このタイプのモデルは、任意の深さに拡張することができ、複数のコンテナを含むことができます。

移行の実行

ここまででGridDBのデータモデルとスキーマを作成し、データ移行の準備ができました。 移行にあたっては、MySQLの各テーブルのすべての行を SELECT し、GridDBで新しい行を作成し、GridDBコンテナに挿入する方法を採用します。

まずJDBC ドライバを使い、MySQL 'pv' データベースに接続します。次にGridDB Java APIを使い、GridDB クラスタへ接続します。

施設データの移行

MySQLから移行する最初のテーブルは facilities テーブルで、同じ名前のGridDB Collection にします。 これは、MySQLテーブルと同様のスキーマを持ちます。

データの移行を開始するには、MySQLのテーブルからすべての行を SELECT します。 次に、各行をGridDBの Facility classスキーマに再フォーマットします。

さらに、スケーラビリティを向上させる方法として、 MySQLのsensorsテーブルのようなスキーマを持つ Collection (facility idで名称を付ける)を作成します。それぞれのfacilityコンテナは、その施設のセンサーの格納を担当します。

一方、MySQLからGridDBへの Blob 型の変換は、それほど直感的に分かりやすくはありません。 以下のセクションでMySQL Blobを処理するプロセスについて詳しく説明します。
以下のコードスニペットで、Facility classのspecification columnを specifications attributeに変換する例を詳しく説明しています。

MySQL Blobデータの処理

まず、Blob bytesをMySQLから書き出すための ByteArrayOutputStream を作成します。 次に、MySQL Blobのバイトを格納する BufferedInputStream を取得します。 MySQLの行からMySQL Blobバイトデータまたは binary data を取得するには、Blob列（specification）で .getBinaryStream メソッドを呼び出します。 そこからバイナリストリーム（ BufferedInputStream ）のバイトデータの配列を ByteArrayOutputStream に書き出します。

ByteArrayOutputStream への書き込みが終わったら、 GridDB Blob を作成できるようになります。 GridDB Blobを作成するには、すべてのデータをbyte arrayとして取得する必要があります。 ByteArrrayOutputStream の .toByteArray（）メソッドを呼び出して、バイト配列を取得します。 最後に、出力ストリームのbyte arrayの SerialBlob にspecificationsの列または属性を設定することで、GridDB行のBlob列を設定します。
Blob
これでBlob型が処理されたので、新しい行をGridDBコレクションに挿入することができるようになりました。

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);
}

センサーデータの移行

2番目に移行するテーブルは、MySQLの sensors テーブルです。 テーブルからすべての行を選択し、GridDBコレクションに挿入する行を並べ替える、先ほどと同じプロセスを実行します。

この表の各行には facilityId 列と FOREIGN KEY があります。 この列は、センサーがどの施設に属するかを表します。 この行をGridDBに移行すると、その列の値が行から取得され、その機能に対応する適切な Collection コンテナが取得されます。

次に、MySQL行の残りの列値から新しい Sensor 行を作成し、GridDBのコレクションコンテナに挿入します。

各センサーは、センサーの測定値を格納する独自の TimeSeries コンテナも取得します。 （センサーのsensor idは、Timeseriesの名前/キーになります）。 コンテナには Reading classスキーマがあります。

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);
}

センサ値の移行

3番目に移行するテーブルは、readings テーブルで、時系列(Timeseries) データを含めることに留意してください。

まずはjava.sql.Timestampオブジェクトとして格納されているMySQL のTIMESTAMPを、java.util.Dateオブジェクトとして格納されるGridDB Timestampに変換する必要があります。

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

readings MySQLテーブルから取得したすべての行を繰り返し処理するとき、MySQLの行の sensorId 列の値を使用して、 TimeSeries 新しいGridDB Readingがに挿入されます。 ほかのすべてのテーブルと同様に、GridDBの新しい Reading 行の他のすべてのカラム値を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);
}

アラートテーブルの移行

最後に移行するテーブルはalertsです。 この移行方法は、 facilities テーブルの移行方法に似ています。

まず、アラート通知を格納するための Collection コンテナをGridDBに作成します。 このコンテナが Alert スキーマを保持します。

そこから、alertsテーブルのすべての行を繰り返して、各行を Alert オブジェクトに変換して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);
}

まとめ

これで移行作業が完了し、MySQLデータベース内のすべてのデータがGridDBに移行されました。 この後、MySQLデータベースを削除して、より高速でよりスケーラブルな GridDBデータベースをお使いください。

MySQLは、ビジネスインテリジェンス、Web開発、マーケットプレイス、在庫管理によく使われているリレーショナルデータベースです。 このチュートリアルでは、 CentOS 7 の MySQL Version 5.7 を使用して初期リレーショナルデータベースを設定しました。 GridDBデータベースには、GridDB Community Edition Build 3.0.1を使用しました。

今回はデータベース移行の事例として、太陽光発電サイトの事例をご紹介しました。その他の IoT業界でのGridDBの使用事例については、このブログをご参照ください。

ブログの内容について疑問や質問がある場合は Q&A サイトである Stack Overflow に質問を投稿しましょう。 GridDB 開発者やエンジニアから速やかな回答が得られるようにするためにも "griddb" タグをつけることをお忘れなく。 https://stackoverflow.com/questions/ask?tags=griddb