昨日の続きです。昨日は簡単なINSERT文でテストしましたが、今日は実践的なデータエクスポート。PostgreSQLにあるテーブルから、必要な列だけSQLiteINSERTできました。ただ何故かTEXT型の列をconcat関数でラップする必要があり、でないとPostgreSQLサーバが落ちます。。。便利な反面、ちょっと注意がいると判明。
前回:実行環境 ~OGR_FDWインストール ~ テスト
Contents


SQLiteにエクスポートするテーブル

↓ 旧ブログ(Seesaa)の記事リスト。ここから日付・タイトルなど4列をSQLiteにエクスポートして記事検索に使いたい。現在もすでにSQLiteで稼動してますが、それはpg_dump経由でエクスポートしたもの。手順が少々面倒でした(詳細は831日の記事)。今日は、同じDBOGR_FDWで作ってみます。
# \d kenpg_seesaa_list
Table "public.kenpg_seesaa_list"
+-----------+------+-----------+
|  Column   | Type | Modifiers |
+-----------+------+-----------+
| ymd       | date | not null  | <=
| url       | text | not null  |
| title     | text | not null  | <=
| tags      | text | not null  | <=
| html_file | text | not null  |
| html      | text | not null  |
| body      | text |           | <=
+-----------+------+-----------+
Indexes:
    "kenpg_seesaa_list_pkey" PRIMARY KEY, btree (ymd) 
    "kenpg_seesaa_list_url_idx" UNIQUE, btree (url)


手順まとめ

OGR_FDWのセットアップまでは昨日書いたので省き、今日は(1)エクスポート先DBとテーブルをSQLiteで作成、(2)SQLiteテーブルをPostgreSQLの外部テーブル化、(3)既存テーブルからINSERT、の三つ。

まとめると ↓ うち12は昨日も簡単なテストで行いました。新しく、また普通と違うのはINSERTするSELECTクエリで、TEXT型の列をわざわざconcat関数でラップしてます。
でないとINSERTに失敗しサーバが落ちるため。
-- 1) create sqlite database
$ sqlite3 r:/seesaa_ogrfdw.db
sqlite> CREATE TABLE articles (ymd, title, tags, body2); 

-- 2) create a foreign table on psql
# create server seesaa_ogrfdw
    foreign data wrapper ogr_fdw
    options (
        datasource 'r:/seesaa_ogrfdw.db',
        format 'SQLite');

# import foreign schema ogr_all
    from server seesaa_ogrfdw
    into public;

-- 3) export data to the foreign table
# insert into public.articles (ymd, title, tags, body2) 
    select ymd,
        concat(title), 
        concat(tags),
        concat(body2)
    from kenpg_seesaa_list;


上の(1)SQLiteでテーブルを作る際、列の型指定を省いてます。
昨日テストした範囲では、指定しても結局テキストになるっぽいので。以下、psqlに移って23を実際やってみた様子。


psqlでの実際

まず外部テーブルを入れるサーバの作成。SQLiteのファイル一つを関連付けます。ファイルがないとクエリがエラー。フォーマット名はSQLite3が付きませんが、3です。
# create server seesaa_ogrfdw
    foreign data wrapper ogr_fdw
    options (
        datasource 'r:/seesaa_ogrfdw.db',
        format 'SQLite');

# \des+
                                                                  List of foreign servers
+---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+ 
|     Name      |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                     FDW Options                     | Description |
+---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+
| seesaa_ogrfdw | postgres | ogr_fdw              |                   |      |         | (datasource 'r:/seesaa_ogrfdw.db', format 'SQLite') |             |
+---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+
(1 row)


続いてIMPORTA FOREIGN SCHEMA文で外部テーブル作成。普通のCREATE FOREIGN TABLEでも可能ですが、
昨日書いたとおりこの方が便利なので。
# import foreign schema ogr_all
    from server seesaa_ogrfdw
    into public;

# \det+ 
                         List of foreign tables
+--------+----------+---------------+--------------------+-------------+ 
| Schema |  Table   |    Server     |    FDW Options     | Description |
+--------+----------+---------------+--------------------+-------------+
| public | articles | seesaa_ogrfdw | (layer 'articles') |             |
+--------+----------+---------------+--------------------+-------------+
(1 row)


以上でエクスポート準備終わり。INSERTする前の確認で、エクスポート元(普通のテーブル)と先(外部テーブル)の列構成を表示します。↓
# \d kenpg_seesaa_list
Table "public.kenpg_seesaa_list"
+-----------+------+-----------+
|  Column   | Type | Modifiers |
+-----------+------+-----------+
| ymd       | date | not null  | 
| url       | text | not null  |
| title     | text | not null  |
| tags      | text | not null  |
| html_file | text | not null  |
| html      | text | not null  |
| body      | text |           |
+-----------+------+-----------+
 

# \d articles
            Foreign table "public.articles"
+--------+-------------------+-----------+-------------+
| Column |       Type        | Modifiers | FDW Options |
+--------+-------------------+-----------+-------------+
| fid    | bigint            |           |             |
| ymd    | character varying |           |             |
| title  | character varying |           |             |
| tags   | character varying |           |             |
| body2  | character varying |           |             |
+--------+-------------------+-----------+-------------+
Server: seesaa_ogrfdw
FDW Options: (layer 'articles')


↓ 外部テーブルが空の状態から、INSERT文を実行。元テーブルの693行が外部テーブル = SQLiteのデータベースに書き込まれました。
# select * from articles;
+-----+-----+-------+------+-------+
| fid | ymd | title | tags | body2 |
+-----+-----+-------+------+-------+
+-----+-----+-------+------+-------+
(0 rows)

# insert into articles (ymd, title, tags, body2) 
    select ymd,
        concat(title), 
        concat(tags),
        concat(body2)
    from kenpg_seesaa_list;
INSERT 0 693

# select count(*) from articles;


結果確認:pg_dump経由でエクスポートしたものと比較

↓ 書き込んだテーブルの行数をSQLiteクライアントで出すと確かに693行。なおPostgreSQLでの外部テーブル化に関わらず、SQLiteクライアントからは普通にアクセスできます。またpg_dump経由でエクスポートしたものと同一サイズ。テーブル定義が若干違うためか(今回は型を省略)中身は少し違うようです。
$ sqlite3 r:/seesaa_ogrfdw.db
sqlite> select count(*) from articles;
693

$ ls -l r:/*.db
-rwxrwx---+ 1 Administrators None 3993600 Aug 30 08:46 seesaa.db
-rwxrwx---+ 1 Administrator  None 3993600 Sep  4 20:04 seesaa_ogrfdw.db <= 

$ diff --binary seesaa.db seesaa_ogrfdw.db
Binary files seesaa.db and seesaa_ogrfdw.db differ


テーブルの中身も、日本語やローマ数字を含め問題なく前と同様にエクスポートされてました。で、既に
旧ブログ記事の検索で使ってます。前の「INSERT文でpg_dump → エディタで開いて編集 → SQLiteクライアントでインポート」より確実に便利。


既存テーブルのTEXT列をそのままINSERTするとサーバが落ちる

これは残念な、かつ不可解な点。既存テーブルにあるTEXT型の列をそのままINSERTSQLiteの外部テーブルに入れようとしたら ↓ サーバが異常終了します。下の赤い部分はpsqlで接続が切れた際のメッセージ。
# insert into articles (ymd, title, tags, body2)
    select ymd,
        title,
        tags,
        body2
    from kenpg_seesaa_list;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded. 


↓ こちらがサーバのログ。詳しくは今後調べます。
LOG:  server process (PID 4848) was terminated by exception 0xC0000005
DETAIL:  Failed process was running: insert into articles (ymd, title, tags, body2)
            select ymd,
                title,
                tags,
                body2
            from kenpg_seesaa_list;
HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
...


TEXT型でない列、今回では日付型のymdはそのままINSERTできてます。なぜconcat関数にくるむとOKなのか謎。型キャストでTEXTVARCHARにしても駄目でした。

とは言え、SQLite専用でなく多様なGISデータが主眼のOGR_FDWで、外部テーブルに書き込み可能になった最初のバージョンで一応ここまで使えたのは予想以上。今後いろいろ試したいと思います。