PostgreSQLからGridDBへの移行

はじめに

以前のブログで、Javaを使用してリレーショナルデータベースMySQLを移行する方法についてご紹介しました。 このブログでは、PostgreSQL databaseをPythonを使ってGridDBへ移行する方法をご紹介します。

Postgresql は、ビジネスインテリジェンス、Web開発、商取引、ヘルスケア、生物薬学で広く使用されているリレーショナルデータベースです。 PostgreSQLには利点もある一方で、スケーラビリティと柔軟性に欠けるといった、他のリレーショナルデータベースによくありがちな問題点があります。 PostgreSQLはまた、習得するのに時間がかかり、特定のデータセットを扱う際にパフォーマンスが悪くなるという欠点もあります。

一方、GridDBは、水平方向のスケーラビリティに優れ、NoSQLデータベースの柔軟性も備えています。また、信頼性 と アベイラビリティに加えて ACIDにも準拠しています。GridDBはまた、インメモリアーキテクチャで優れた性能を発揮するため、IoTアプリケーションなどに最適であるといえます。

事例

分かりやすく、前回のブログでご紹介したのと同じ事例でご説明します。 このシナリオは、太陽光発電所(Photovoltaic site)（通称PV）におけるものです。このデータベースのデータの大部分は、センサーとそのセンサーがある施設から送信された測定値や情報などが含まれます。

GridDBデータモデル

PostgreSQLはOracle DatabaseとMySQLに非常によく似たリレーショナルデータベースなであるため、今回も前回のブログで使ったものと同じサンプルデータベースを使用します。 したがって、PostgreSQLデータベースのスキーマは前回と同じです。 私たちのGridDBデータ・モデルも前回と同じですが、データベースは Python API を使用して作成してデータを作成します。

PostgreSQLデータベースには 4つのテーブルがあり、GridDBでは 4つのコンテナスキーマに変換されます。 詳しくは以下の通りです。

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

CREATE TABLE IF NOT EXISTS facilities (
	facilityId VARCHAR(20) PRIMARY KEY,
	name VARCHAR(50) NOT NULL,
	specifications BYTEA
);

facilities = gridstore.put_container("facilities",
                        [("facilityId",griddb.GS_TYPE_STRING),
			("name",griddb.GS_TYPE_STRING),
                        ("specifications",griddb.GS_TYPE_BLOB)],
			griddb.GS_CONTAINER_COLLECTION)

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

CREATE TABLE IF NOT EXISTS sensors (
	sensorId VARCHAR(40) PRIMARY KEY,
	facilityId VARCHAR(20) REFERENCES facilities(facilityId) 
           ON DELETE CASCADE ON UPDATE CASCADE,
	name VARCHAR(50) NOT NULL,
	type VARCHAR(50) NOT NULL
);

facility = gridstore.put_container("facility_1",
                [("sensorId",griddb.GS_TYPE_STRING),
		("name",griddb.GS_TYPE_STRING),
                ("type",griddb.GS_TYPE_STRING)],
                 griddb.GS_CONTAINER_COLLECTION)
           

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

CREATE TABLE IF NOT EXISTS readings (
	recordId SERIAL PRIMARY KEY,
	sensorId VARCHAR(40) REFERENCES sensors(sensorId) 
            ON DELETE CASCADE ON UPDATE CASCADE,
	ts TIMESTAMP DEFAULT NOW() NOT NULL,
	value REAL NOT NULL,
	status VARCHAR(255) NOT NULL
);

timeseries = gridstore.put_container("sensor_1",
                [("timestamp",griddb.GS_TYPE_TIMESTAMP),
		("value",griddb.GS_TYPE_DOUBLE),
                ("status",griddb.GS_TYPE_STRING)],
		griddb.GS_CONTAINER_TIME_SERIES)

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

CREATE TABLE IF NOT EXISTS alerts (
	alertId SERIAL PRIMARY KEY,
	ts TIMESTAMP DEFAULT NOW() NOT NULL,
	facilityId VARCHAR(20) REFERENCES facilities(facilityId) 
            ON DELETE CASCADE ON UPDATE CASCADE,
	sensorId VARCHAR(40) REFERENCES sensors(sensorId) 
            ON DELETE CASCADE ON UPDATE CASCADE,
	level INT NOT NULL,
	detail VARCHAR(255) NOT NULL
);

alerts_col = gridstore.put_container("alerts",
                [("id",griddb.GS_TYPE_INTEGER),
		("timestamp",griddb.GS_TYPE_TIMESTAMP),
                ("facilityId",griddb.GS_TYPE_STRING),
		("sensorId",griddb.GS_TYPE_STRING),
                ("level",griddb.GS_TYPE_INTEGER),
                ("detail",griddb.GS_TYPE_STRING)],
		griddb.GS_CONTAINER_COLLECTION)

移行の実行

ここまででPostgreSQLとGridDBの両方にスキーマとデータモデルを設定し、データ移行の準備ができました。PostgreSQLの各テーブルからすべてのrowsをSELECTし、再フォーマットしてGridDB Rowに再配置します。 次に、その行を対応する Collection または Timeseries (時系列) コンテナに挿入します。

参考までに、移行されるPostgreSQLデータベースの名前は 'pv' です。

Python APIとコネクタのインストール

まず、PostgreSQLでPythonを使用するには、psycopg2 packageが必要です。 PythonからGridDBにアクセスするには、griddb_python_client packageも必要です。 これらのパッケージは両方とも、pipパッケージインストーラを使用してインストールできます。

$ python -m pip install psycopg2
$ python -m pip install griddb_python_client

インストールが完了したら、pscycopg2を使って PostgreSQLのpvデータベースに接続できるようになります。GridDBクラスタにはPythonを使って接続します。

施設テーブルの移行

施設を格納するためにGridDBでCollectionコンテナを作成したら、PostgreSQLの施設テーブルからすべての行にアクセスして移行できます。まず、PostgreSQLのfacilitiesテーブルから row cursor を取得します。 row cursor は、行の 列の値の 配列を返します。

すべての列の値は、Pythonで適切な型に変換されます。つまり、値の解析や型の変換は必要ありません。各行に対して返された列の数に対して正しい量の変数を作成する限り、行の個々の値をすべて取得することができます。

すべての行の列の値を取得したら、GridDBの行オブジェクトを作成し、それに応じてすべての row fields を設定できます。データベース間の型の変換は、文字列や数値などの型に対してこのように処理されます。GridDBコレクションに挿入する前にPostgreSQLの BLOB 値を bytearray に変換することを忘れずに行ってください。

query = "SELECT * FROM facilites"

cursor = connection.cursor() #Obtain row cursor from PostgreSQL connection
cursor.execute(query)

row = cursor.fetchone()
while row is not None:
        ## Get all id, name, and specification column values from row
        facility_id, name, blob = row

        facility_row = facilities.create_row()

        facility_row.set_field_by_string(0,facility_id)
        facility_row.set_field_by_string(1,name)
        facility_row.set_field_by_blob(2,bytearray(blob)) ## Convert Blob data to bytearray

        ret = facilities.put_row(facility_row)

センサーデータの移行

施設テーブルの移行中に、データベース内のすべての施設の facility_id を取得することができます。 このFacility IDを使用してその施設の sensor_collection を作成し、センサーに情報を格納します。

まず、PostgreSQLの中で facility id を持つ sensors テーブルのすべての行を取得します。 GridDBのセンサーコンテナの行には facility_id 列がないため、この列をPostgreSQLから取得する必要はありません。

施設のセンサーのために Collectionコンテナを作成した後は、先ほどと同様にPostgreSQLの行を解析し、それらをGridDB内の施設 Collection に挿入するという同じプロセスを行います。

facility = "facility_1"
query = "SELECT sensorId,name,type FROM sensors WHERE facilityId='%s'" % (facility)

cursor = connection.cursor()
cursor.execute(query)
row = cursor.fetchone()

while row is not None:
        sensor_id, name, sensor_type = row

        sensor = sensor_collection.create_row()

        sensor.set_field_by_string(0,sensor_id)
        sensor.set_field_by_string(1,name)
        sensor.set_field_by_string(2,sensor_type)

        ret = sensor_collection.put_row(sensor)

センサー値の移行

センサー のPostgreSQLテーブルを移行するときに各センサーの sensor_id を取得するので、そのセンサーの測定値を保存するための Timeseries コンテナを作成します。 sensor-id で読み出しテーブルのすべての行を SELECT します。timeseriesはrow-keysとしてタイムスタンプを持っているため、PostgreSQLから取得した行を TIMESTAMP 列で並べ替えます。

すべての過去テーブルのすべての行に対して、同じ手順ですべての列の値を取得し、GridDB行の行フィールドとして設定します。

PostgreSQLの TIMESTAMP 、 DATE 、TIMEの列の値は、Pythonでは datetime の値として返ってくることに注意してください。 GridDBのタイムスタンプの列または行フィールドは、数値形式でのみ設定できるため、 datetime オブジェクトをタイムスタンプ番号に変換する必要があります。変換した後に、すべての行フィールドを設定し、GridDBのそのセンサーの Timeseries コンテナに行を挿入します。

griddb = griddb_python_client

def datetime_to_timestamp(dt):
        ## GridDB Timestamp Format: (All fields must be numerics)
        ## $YEAR-$MONTH-$DAYT$HOUR:$MINUTE:$SECONDZ
        time_string = dt.strftime("%Y-%m-%dT%XZ") # '%X' means get timestamp value from 'datetime'
        timestamp = griddb.Timestamp_parse(time_string)
        return timestamp

## (snip)
## Migrate data from 'readings' table from PostgreSQL into Timeseries containers in GridDB

sensor_id = "sensor_1" ## Example of a sensor id in a PV Site's facility
query = "SELECT ts,value,status FROM sensors WHERE sensor_id = '%s' ORDER BY ts ASC" % (sensor_id)

cursor = connection.cursor()
cursor.execute(query)
row = cursor.fetchone()

## Obtain column values from each reading row (sensor measurement) from 'readings table
while row is not None:
        date, value, status = row
        
        reading = timeseries.create_row()
        timestamp = datetime_to_timestamp(date)
 
        reading.set_field_by_timestamp(0,timestamp)
        reading.set_field_by_double(1,value)
        reading.set_field_by_string(2,status)

        ret = timeseries.put_row(reading)

アラートテーブルの移行

PostgreSQLテーブルのアラートテーブルは、GridDBの 1コレクションコンテナにのみマップされます。 そのため、移行の手順は施設テーブルの移行の手順に似ています。

query = "SELECT * FROM alerts"
cursor = connection.cursor()

cursor.execute(query)
row = cursor.fetchone()

while row is not None:
        alert_id, date, facility_id, sensor_id, level, detail = row
        
        alert_notification = alerts_col.create_row()
        timestamp = datetime_to_timestamp(date) 

        alert_notification.set_field_by_integer(0,alert_id)
        alert_notification.set_field_by_timestamp(1,timestamp)
        alert_notification.set_field_by_string(2,facility_id)
        alert_notification.set_field_by_string(3,sensor_id)
        alert_notification.set_field_integer(4,level)
        alert_notification.set_field_by_string(5,detail)

        ret = alerts_col.put_row(alert_notification)

まとめ

これで 4つのPostgreSQLテーブルがすべて移行され、GridDBへの移行は完了しました。 この後はすべてのPostgreSQLデータを削除し、GridDBの優れた設計と性能のメリットを実感していただけるでしょう。 GridDBコンテナでは、 ACID準拠のようなSQLの特性が得られると同時に、柔軟性や 高いスケーラビリティといったNoSQLの利点も得ることができます。リレーショナルデータベースからGridDBへの移行をシームレスに行うためには、適切な設計と適切なデータモデリングを行うことが重要です。

参照

• PostgreSQL データベースはPostgreSQL Version 9.2.23 を使用しました。
• GridDBデータベースはGridDB Community Edition 3.0.1を使用しました。
• CentOS 7.3 Linux Operating System上での移行のためのプログラミング言語としてPython version 2.7.13 を使用しました。
• Pythonを使ったGridDBについて詳しく知りたい方は、API postをご参照ください。

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