SQLite FDW で PostgreSQL から SQLite3 データベースを使ってみよう!

はじめに

SQLite FDW を使って、PostgreSQL から SQLite データベース にアクセスしてみました。今回、PostgreSQL, SQLite をソースコードからインストールしていますが、既に PostgreSQL Version 9.6, 10, 11 または 12、SQLite3 がインストールされている場合、PostgreSQL, SQLite のインストールを飛ばして、SQLite FDW のインストールから始めて下さい。

FDW (Foreign Data Wrapper) とは

PostgreSQLには、SQL2003 SQL/MED 規格に沿った他データソースへのアクセス機能である、FDW(Foreign Data Wrapper) 機能が存在します。この機能により、様々なデータソースへのアクセスが可能となっています。現在、Oracle や SQL Server などの商業 RDBMS、SQLite や MySQL などのOSS RDBMS、Cassandra や GridDB といった NoSQL データベースといったデータソースへアクセスするためのFDWがユーザによって作成・公開されています。

PostgreSQLをソースコードからインストール

PostgreSQLをソースコードからインストールします。PostgreSQLドキュメントを参考しています。

CentOS7 で postgresユーザを作成します。

$ sudo groupadd postgresql
$ sudo useradd -d /home/postgres -g postgres -s /bin/bash postgres
$ sudo passwd postgres
$ su - postgres

PostgreSQLコミュニティから、PostgreSQLを入手します。

$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
$ tar -jxvf postgresql-12.2.tar.bz2

PostgreSQLをインストールします。

$ cd postgresql-12.2
$ ./configure 
$ make
$ make install
$ cd

SQLiteをソースコードからインストール

SQLite をソースコードからインストールします。SQLite - Installation の Install SQLite on Linux を参考しています。
SQLiteコミュニティから SQLite を入手します。

$ wget https://www.sqlite.org/2020/sqlite-autoconf-3310100.tar.gz
$ tar xvfz sqlite-autoconf-3310100.tar.gz
$ mv sqlite-autoconf-3310100 sqlite3.31.1

SQLiteをインストールします。

$ cd sqlite3.31.1
$ ./configure
$ make
$ sudo make install

環境設定

環境設定 LD_LIBRARY_PATH を設定します。

$ LD_LIBRARY_PATH=/usr/local/pgsql/lib:/usr/local/lib
$ export LD_LIBRARY_PATH

環境設定 PATH を設定します。

$ PATH=/usr/local/pgsql/bin:$PATH
$ export PATH

SQLite FDW のインストール

SQLite FDW をインストールします。GitHubにある readme.md を参考しています。
PostgreSQLコミュニティによると、SQLite FDW が2つ存在しますが、更新やプッシュダウンが可能なこちらの SQLite FDW を使用します。以下の方法は、SQLite FDW ソースコードをPostgreSQLソースツリーに含めてインストールする方法です。

$ cd ~/postgresql-12.2/contrib
$ git clone https://github.com/pgspider/sqlite_fdw.git
$ cd sqlite_fdw
$ make
$ sudo make install

もし、pg_configコマンドにパスが通っている場合には、下記のコマンドでインストールできます。

$ make USE_PGXS=1
$ sudo make install USE_PGXS=1

PostgreSQL を起動します。

$ initdb -D /usr/local/pgsql/data
$ postgres -D /usr/local/pgsql/data &

サンプルデータベースを入手

SQLite tutorialからサンプルデータベースを入手します。

$ cd /usr/local/pgsql/data/
$ wget https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip
$ unzip chinook.zip

Command Line Shell (sqlite3) でサンプルデータベース(chinnook.db) を確認してみましょう。11のテーブルが存在します。

$ sqlite3 chinook.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tab
albums          employees       invoices        playlists     
artists         genres          media_types     tracks        
customers       invoice_items   playlist_track

SQLite FDW の初期設定

CREATE EXTENSION で SQLite FDW をデータベースに取り込みます。

$ psql
psql (12.2)
Type "help" for help.
postgres=# CREATE EXTENSION sqlite_fdw;
CREATE EXTENSION

CREATE SERVER で新しい外部サーバを定義します。 今回は sqlite_server を外部サーバ名とします。OPTIONSで、SQLiteのデータベースファイル名を指定します。
次に外部テーブル一覧で何もないことを確認します。

postgres=# CREATE SERVER sqlite_server FOREIGN DATA WRAPPER sqlite_fdw 
postgres=# OPTIONS (database '/usr/local/pgsql/data/chinook.db');
CREATE SERVER
postgres=# \dE
Did not find any relations.

外部テーブルの作成して動作確認

SQLite で、media_types テーブルのカラム情報を表示します。

sqlite> PRAGMA table_info(media_types);
0|MediaTypeId|INTEGER|1||1
1|Name|NVARCHAR(120)|0||0

media_types テーブルのカラム情報から、PostgreSQLで、media_types テーブルの外部テーブルを作成します。

postgres=# CREATE FOREIGN TABLE media_types("MediaTypeId" bigint, "Name" text) SERVER sqlite_server;
CREATE FOREIGN TABLE

media_types テーブルを SELECT します。5レコード表示されます。

postgres=# SELECT * FROM media_types;
 MediaTypeId |            Name             
-------------+-----------------------------
           1 | MPEG audio file
           2 | Protected AAC audio file
           3 | Protected MPEG-4 video file
           4 | Purchased AAC audio file
           5 | AAC audio file
(5 rows)

media_types テーブルに レコードを 1件、追加します。SELECTで確認すると、レコードが追加されています。

postgres=# INSERT INTO media_types VALUES(6, 'TEXT file');
INSERT 0 1
postgres=# select * from media_types;
 MediaTypeId |            Name             
-------------+-----------------------------
           1 | MPEG audio file
           2 | Protected AAC audio file
           3 | Protected MPEG-4 video file
           4 | Purchased AAC audio file
           5 | AAC audio file
           6 | TEXT file
(6 rows)

SQLiteでも、media_types テーブルを SELECT してみます。追加したレコードを確認できます。

sqlite> SELECT * FROM media_types;
1|MPEG audio file
2|Protected AAC audio file
3|Protected MPEG-4 video file
4|Purchased AAC audio file
5|AAC audio file
6|TEXT file

次の動作確認のために、一度、media_types テーブルを DROP します。

postgres=# DROP FOREIGN TABLE media_types;

IMPORT FOREIGN SCHEMA で外部テーブルを一括して作成

1つ、2つのテーブルであれば、外部テーブルを作成しても気になりませんが、テーブルが11もあると、外部テーブル作成がイヤになりますよね。そこで、IMPORT FOREIGN SCHEMA で外部テーブルを一括して作成してみましょう。IMPORT FOREIGN SCHEMA は外部サーバ上に存在するテーブルの外部テーブルを作成します。

postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_server INTO public;
IMPORT FOREIGN SCHEMA

外部テーブル一覧で確認すると、11の外部テーブルが作成されています。

postgres=# \dE
                 List of relations
 Schema |      Name      |     Type      |  Owner   
--------+----------------+---------------+----------
 public | albums         | foreign table | postgres
 public | artists        | foreign table | postgres
 public | customers      | foreign table | postgres
 public | employees      | foreign table | postgres
 public | genres         | foreign table | postgres
 public | invoice_items  | foreign table | postgres
 public | invoices       | foreign table | postgres
 public | media_types    | foreign table | postgres
 public | playlist_track | foreign table | postgres
 public | playlists      | foreign table | postgres
 public | tracks         | foreign table | postgres
(11 rows)

外部テーブルでジョインを使った検索をやってみよう!

外部テーブルを使ったジョインを使った検索も可能です。
tracks テーブル、albums テーブル、artists テーブルでインナージョインで検索します。次のSQLは、artistid が 10 のレコードの Track 名、album 名、artist名を表示する SQL です。

postgres=# SELECT "TrackId", Tracks."Name" AS Track, Albums."Title" AS Album, Artists."Name" AS Artist
postgres-#   FROM Tracks 
postgres-#  INNER JOIN Albums ON Albums."AlbumId" = Tracks."AlbumId"
postgres-#  INNER JOIN Artists ON Artists."ArtistId" = Albums."ArtistId"
postgres-#  WHERE Artists."ArtistId" = 10;
 TrackId |                    track                     |          album           |    artist    
---------+----------------------------------------------+--------------------------+--------------
     123 | Quadrant                                     | The Best Of Billy Cobham | Billy Cobham
     124 | Snoopy's search-Red baron                    | The Best Of Billy Cobham | Billy Cobham
     125 | Spanish moss-"A sound portrait"-Spanish moss | The Best Of Billy Cobham | Billy Cobham
     126 | Moon germs                                   | The Best Of Billy Cobham | Billy Cobham
     127 | Stratus                                      | The Best Of Billy Cobham | Billy Cobham
     128 | The pleasant pheasant                        | The Best Of Billy Cobham | Billy Cobham
     129 | Solo-Panhandler                              | The Best Of Billy Cobham | Billy Cobham
     130 | Do what cha wanna                            | The Best Of Billy Cobham | Billy Cobham
(8 rows)

外部テーブルを使っても問題なくインナージョインを使った検索が実行できました。

まとめ

SQLite FDW を使用して、PosgreSQL から SQLite データベース にアクセスしてみました。これまで、SQLite からデータを取り出して、PostgreSQLにロードして、使う必要がありましたが、SQLite FDW を使うと簡単にPostgreSQLからアクセスできました。手順も簡単なので気軽に使ってみて下さい。

もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。