PostgreSQL用のGridDB外部データラッパーを使ってみよう

よくある問題

PostgreSQL用のGridDB外部データラッパーがSQLクエリで動作するために、すべての .o と実行可能なファイルは、システム上の/usr/local/pgsql/share/extension/ディレクトリに置かれます。 通常、 make install コマンドはこれを自動的に行いますが、次のようなエラーが発生した場合は、

ERROR: could not open extension control file: 

コンパイルされた .o ファイルと 実行可能なファイルをshare/extensionディレクトリにコピーするとよいでしょう。

$ cd /path/to/postgresql/source/installation/contrib/griddb_fdw
$ make clean
$ make
$ cp * /usr/local/pgsql/share/extension

PostgreSQLとGridDBの接続

ここまでで PostgreSQL用のGridDB外部データラッパーがインストールされたので、PostgreSQLデータベースを介して SQL クエリを使用してGridDBデータにアクセスできるようになりました。

まず、 CREATE EXTENSION コマンドを使用してGridDBのPostgreSQLに拡張機能を追加します。 GridDBの拡張の名前は 'griddb_fdw' です。外部データにアクセスするには、PostgreSQLに外部の SERVER と USER MAPPING が必要で、GridDBに接続して認証します。 GridDBクラスタに接続するために必要な設定は、これらのコマンドの OPTIONS フラグで指定します。

既存のコンテナ用の外部テーブルをGridDBサーバに自動的に作成し、それらを選択したPostgreSQLにインポートすることができます。これは IMPORT FOREIGN SCHEMA コマンドで行うことができます。 GridDBのリモートスキーマの名前は 'griddb_schema' です。次の例では、PostgreSQLの 'public' データベースからGridDBデータベースにアクセスします。

-- GridDBのビルド拡張の名前は 'griddb_fdw'です
CREATE EXTENSION griddb_fdw;
-- PostgreSQLを使用してGridDBサーバにアクセスするには、ホスト、ポート、クラスタ名、ユーザー名、およびパスワードが必要です
--これらは 'OPTIONS'フィールドで指定できます
CREATE SERVER griddb_svr FOREIGN DATA WRAPPER griddb_fdw OPTIONS(host '239.0.0.1', port '31999', clustername 'defaultCluster');
CREATE USER MAPPING FOR public SERVER griddb_svr OPTIONS(username 'admin', password 'admin');

-- GridDBコンテナから外部テーブルを作成してロードするために必要なリモートスキーマの名前は 'griddb_schema'です。
IMPORT FOREIGN SCHEMA griddb_schema FROM SERVER griddb_svr INTO public;

これが完了すると、外部 SERVER は、PostgreSQLのGridDB外部データラッパー（ griddb_fdw 拡張）がデータソースとして使用する接続情報を、GridDBにカプセル化します。

GridDBコンテナへのアクセス

PostgreSQL用のGridDB外部データラッパーが設定され、PostgreSQLデータベースがGridDBサーバに接続されると、 SQL クエリでGridDBコンテナとそのデータにアクセスできるようになりました。

閲覧可能なコンテナの表示

GridDBからインポートされた利用可能な外部テーブルはすべて、 information_schema._pg_foreign_tables テーブルを照会して閲覧することができます。

クエリ例：

SELECT foreign_table_name FROM information_schema._pg_foreign_tables;

Example Output

foreign_table_name
-------------------------
employee
department
jp_prefecture
jp_prefecture_readings
water_quality_ts_3

外部テーブルの作成

griddb_schema からインポートされたテーブルがGridDBデータベースのすべてのコンテナを本当に反映しておらず、PostgreSQLでそのコンテナのマッピングを手動で再作成する必要がある場合があります。 このような場合には、PostgreSQLに CREATE FOREIGN TABLE コマンドを発行することで対処することができます。

たとえば、領域内の多くのセンサーからのタイムスタンプ付き温度の読み取り値を記録する jp_prefecture_readings という名前のTimeSeriesコンテナがあり、これをPostgreSQLの FOREIGN TABLE としてモデル化したいとします。

GridDB Java API では以下の行スキーマを設定してください。

public class InstrumentLog {
             @RowKey
             public Date timestamp;  //Timestamp Column (Primary Key for Timeseries containers)

             public String weatherstationid; //String column for the sensor of recording

             public double temperature; //Temperature reading (Double Column)

             public Blob liveimage; //Image recording (Binary Stream Data or Blob Column)
}

上記のGridDBコンテナスキーマは、PostgreSQLの FOREIGN TABLE の下に変換され、 SQL で操作できます。 外部テーブルを作成するときは、PostgreSQLが外部コンテナを見つけるために使用する外部サーバを指定する必要があります。

CREATE FOREIGN TABLE jp_prefecture_readings (
                  timestamp timestamp, 
                  weatherstationid text,
                  temperature float,
                  liveimage bytea
) SERVER griddb_svr;

FOREIGN TABLEを作成する一般的な形式は次のとおりです。

CREATE FOREIGN TABLE name_of_container_in_foreign_database (
                 [ column_name column_type ]   
) SERVER foreign_server_name;

PostgreSQLの外部テーブルとそれに対応するGridDBコンテナの両方のカラム名が、column not foundエラーを防ぐために等価であることを確認してください。 また、SQLクエリの type mismatch エラーを防ぐために、列タイプが同等であることを確認してください。

制限事項

PostgreSQLを使用してGridDBの行を作成して破棄することはできますが、格納する実際のコンテナは作成できないことに注意してください。 つまり、PostgreSQLのクエリを使用してGridDBでコンテナを作成または削除することはできません。

CREATE FOREIGN TABLE ステートメントは、PostgreSQLがGridDBの既存のコンテナに基づいて使用するテーブルスキーママッピングを作成します。 DROP FOREIGN TABLE ステートメントは、PostgreSQLの information_schema._pg_foreign_tables テーブルからGridDBコンテナを削除しますが、コンテナとそのデータはそのままGridDBデータベースに残します。 つまり、もう一度 FOREIGN TABLE を作成するまでPostgreSQLからそのテーブルにアクセスすることはできなくなりますが、GridDBのAPIまたは TQL queriesを使ってコンテナにアクセスすることはできます。

列タイプリファレンス

GridDBのほとんどのカラムタイプは、対応する外部テーブルの作成に使用できるPostgreSQLの同等のタイプを持っています。 以下は、PostgreSQLのカラムタイプのうち、対応がそれほど明白ではないものです。

• 注意： PostgreSQL用のGridDB外部データラッパーを使用して、非常に大きな値を持つ LONG 型の列を格納する際に問題が発生することがあります。これは、外部PostgreSQLテーブルに変換されたときにGridDBコンテナの long 型が integer 型に切り捨てられている可能性があります。 これにより、 2147483647以上の数値データを挿入する際に問題が発生する可能性があります。

SQLクエリの発行

GridDBコンテナをPostgreSQLのテーブルにラップすることができたので、TQLで利用できなかったGridDBデータに多くのSQL機能を組み込むことができます。

まず、コンテナから個々の列を選択するか、 SELECT DISTINCT 列の値を選択することができます。

データの選択

-- SELECTクエリから 'liveimage'（BLOB / BYTEA型）列を除外する
SELECT weatherstationid FROM jp_prefecture_readings ORDER BY weatherstationid ASC;

-- タイムスタンプ測定値からすべてのユニークな温度値を見つける
SELECT DISTINCT temperature FROM jp_prefecture_readings ORDER BY temperature ASC;

Sample Output:

        name         | latitude | longitude 
---------------------+----------+-----------
 Hokkaido-Sapporo    | 43.06417 | 141.34694
 Aomori-Aomori       |    48.83 |    140.75
 Iwate-Morioka       | 39.70361 |  141.1525

-- (snip)

 temperature 
-------------
          50
        55.6
          70
          80
(4 rows)

データの変更

また、 INSERT 、 UPDATE 、 DELETE ステートメントを含むSQLクエリでデータを変更することもできます。

-- Insert Data into GridDB Containers
INSERT INTO jp_prefecture (id,name,latitude,longitude,hascamera) 
  VALUES ('weather_station_48','Tsukishima',35.662,139.776,false);

INSERT INTO jp_prefecture_readings (timestamp, weatherstationid, temperature, liveimage) 
  VALUES('2018-05-20 12:35:55.789','weather_station_0',55.6,bytea('\x533435363741'));

-- (snip)

-- Update Queries
UPDATE water_quality_ts_3 SET water_temperature = 18.5 WHERE beach_name LIKE '%met Beach';

UPDATE jp_prefecture SET latitude = 48.83, longitude = 140.75 WHERE id = 'weather_station_2' AND name='Aomori-Aomori';

-- (snip)
-- Delete Data from GridDB Containers
DELETE FROM jp_prefecture WHERE name='Tsukishima';
DELETE FROM jp_prefecture_readings 
  WHERE weatherstationid LIKE '%0' AND temperature > 50.0 AND temperature <= 60.0;

-- Delete all rows from a GridDB Container
DELETE FROM jp_prefecture;

データの集約とグループ化

GROUP BY 句を使用して、GridDBをより深いレベルに集約して分類することもできます。

-- 集約操作とグループ化操作
-- 固有の気象ステーションのIDごとに平均温度を決定して表示する
-- 'weatherstationid'は、個々の天気または気候センサーを表す
SELECT weatherstationid, AVG(temperature) AS average_temp FROM jp_prefecture_readings 
  GROUP BY weatherstationid ORDER BY average_temp ASC;

Sample Output:

  weatherstationid  |   average_temp   
--------------------+------------------
 weather_station_0  |             55.6
 weather_station_44 |               60
 weather_station_12 | 63.3333333333333
 weather_station_32 |               64
 weather_station_26 |               64
 weather_station_38 |               64
 weather_station_20 |               64
 weather_station_22 | 66.6666666666667
 weather_station_36 | 66.6666666666667
 weather_station_6  | 66.6666666666667
 weather_station_24 | 67.1428571428571
 weather_station_4  | 67.1428571428571
 weather_station_28 | 67.1428571428571
 weather_station_16 | 67.1428571428571
 weather_station_43 |             67.5
 weather_station_23 |             67.5
 weather_station_15 |             67.5
 weather_station_27 |             67.5
 weather_station_17 |             67.5

テーブルのマージ

複数の外部テーブルまたは UNION クエリを使用して複数のコンテナのテーブルをマージすることもできます。 関連する列の値を共有するコンテナのデータを JOIN 操作でマージすることもできます。

-- Selecting from multiple tables
SELECT jp.id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature 
  FROM jp_prefecture jp, jp_prefecture_readings jpr WHERE jp.id = 'weather_station_18'
  ORDER BY jpr.timestamp DESC;

-- Join Operations
SELECT jp.id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp INNER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid 
  ORDER BY jp.id ASC;

SELECT jp.id AS left_id, jpr.weatherstationid AS right_id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp LEFT OUTER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;

SELECT jp.id AS left_id, jpr.weatherstationid AS right_id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp RIGHT OUTER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;

SELECT jp.id AS left_id, jpr.weatherstationid AS right_id, jp.latitude, jp.longitude, jpr.timestamp, jpr.temperature
  FROM jp_prefecture jp FULL OUTER JOIN jp_prefecture_readings jpr ON jp.id = jpr.weatherstationid
  ORDER BY jp.id ASC;

-- Union operations
SELECT id FROM jp_prefecture 
  UNION 
SELECT weatherstationid FROM jp_prefecture_readings;

SELECT id FROM jp_prefecture 
  UNION ALL
SELECT weatherstationid FROM jp_prefecture_readings;

結論

PostgreSQL用のGridDBの外部データラッパーを使うことで、アプリケーションがPostgreSQLのすべてのGridDBデータに簡単かつ迅速にアクセスすることができるようになるということがお分かりいただけたでしょうか。GridDBデータベース全体をPostgreSQLに移行する手間と時間をかけることなく、GridDBコンテナをGridDBクラスタからPostgreSQLテーブルにシームレスで自動的に変換することができるようになります。

また、このGridDBの外部データラッパーにより、SQLインターフェースを使用しているレガシーアプリケーションにGridDBデータを組み込むことがはるかに簡単にできるようになりました。これにより、SQLシステムとクエリによるデータ管理ツールと、GridDBの高速でスケーラブルなパフォーマンスを同時に得ることができます。

参照

• このブログではGridDB Community Edition 3.0.1 をGridDBデータベースとサーバーとして使用しました。
• このブログで使用したGridDB Cクライアントは open-source version for Community Editionで、コードはここからダウンロードできます。ドキュメントはGridDB API リファレンスの中の こちらのセクション にあります。
• GridDB CクライアントとGridDB外部データラッパーはいずれも、gcc version 4.8.5を使用して構築しました。
• このブログのすべてのクエリとサンプルコードは、 CentOS 7.3オペレーティングシステム上で実行しました。
• このブログで使用した
  PostgreSQL version 9.6.9 は、source codeからインストールしました。
• PostgreSQL用の外部データラッパーの詳細については、こちらPostgreSQL wiki pageをご覧ください。
• リレーショナルデータベースやSQLデータベースをGridDBに移行する方法をより詳しく知りたい方は、MySQL to GridDBやPostgreSQL to GridDBをご参照ください。データベースの移行またはドキュメントについては、import and export functionsやGridDB Standard Editionをご参照ください。
• GridDB はまた、 SQLクエリやデータベースとのインターフェースにも使えるJDBC とODBC コネクタも提供しています。
• PostgreSQLの外部サーバについては、thoughtbot blog postにも詳しく載っています。

ソースコード

このブログで使用したすべてのソースコードは、下のリンクからダウンロードできます。

• [Download not found]