PostgreSQL CSVインポート

PostgreSQLではCSVのインポートが簡単に行えます

CSVを用意した後、対応するテーブルをcreateしてしまえば、後はpsqlからcopyコマンドを実行するだけでインポートが完了します。Oracleの場合では、コントロールファイル(*1)を作成しないといけない事に対し、PostgreSQLは取り込むテーブルさえ適切に作成しておけばインポートできます。しかもかなりの高速だと思います。（約37M、３３万レコードのインポートに1.5秒です。当方の環境はノートPCにVirtualBOX入れその中での検証です）

(*1) コントロールファイルを使うとインポートする際いろんなデータ加工もできるので便利な面も当然あります。

今回は以下国土交通省のサイトからダウンロードできる住所CSVデータをインポートしてみました。

位置参照情報ダウンロードサービス

丁目レベルのデータ取り込み

丁目レベルインポート

\copy kokudo_address_chome from '01000-15.0b\01_2021.csv'  delimiter ',' csv header
;

上記はCSVの項目数と、テーブルカラム数が同じなのでテーブル名のみの記述で事足りているのですが、テーブル側にCSV以上のカラムを保持している場合対応するカラムを()内にカンマ区切りで記載する必要があります。ただし多くのカラムが存在する場合、copyコマンドの仕様として複数行に分けての記載ができないので「ながーい」コマンドになってしまうところがちょっと不便です。

オプション解説

csv header 1行目をヘッダーとして認識します

丁目レベルインポート実行例

PS D:\ldrtest> ls 01000-15.0b\01_2021.csv

    ディレクトリ: D:\ldrtest\01000-15.0b

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/03/03     13:46        2538925 01_2021.csv

PS D:\ldrtest>
PS D:\ldrtest> psql -U sooni -d myposdb -h vm022
psql (13.9、サーバ 13.6)
"help"でヘルプを表示します。

myposdb=# \timing
タイミングは on です。
myposdb=# \copy kokudo_address_chome from '01000-15.0b\01_2021.csv'  delimiter ',' csv header;
COPY 25980
時間: 272.514 ミリ秒
myposdb=#

一点補足します。上記サイトからダウンロードするCSVファイルはShift_JIS＋CRLFです。今回はWindows環境からpsqlを実行しているので、このCSVをそのまま取込できていますがLinux環境のpsqlを利用する際は、UTF-8+LFに変換する必要がありますのでご注意ください。

丁目レベル住所テーブル用DDL

create table kokudo_address_chome (
    prefectures_code            varchar(2),
    prefectures_name            varchar(32),
    city_code                   varchar(5),
    city_name                   varchar(64),
    oaza_town_chome_code        varchar(12),
    oaza_town_chome_name        varchar(32),
    latitude                    numeric(11,8),
    longitude                   numeric(11,8),
    document_code               integer,
    chome_kubun_code            integer
);
create unique index uni_idx_kokudo_address_chome
on sooni.kokudo_address_chome (oaza_town_chome_code);

街区レベルのデータ取り込み

街区レベルインポート

\copy kokudo_address_gaiku from '01000-20.0a\01_2021.csv'  delimiter ',' csv header
;

街区レベルインポート実行例

PS D:\ldrtest> ls 01000-20.0a\01_2021.csv

    ディレクトリ: D:\ldrtest\01000-20.0a

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/02/24     19:42       37760681 01_2021.csv

PS D:\ldrtest>
myposdb=# \copy kokudo_address_gaiku from '01000-20.0a/01_2021.csv'  delimiter ',' csv header;
COPY 339280
時間: 1497.096 ミリ秒(00:01.497)
myposdb=#

街区レベル住所テーブル用DDL

create table sooni.kokudo_address_gaiku ( 
    prefectures_name            varchar(32),  -- 都道府県名
    city_name                   varchar(64),  -- 市区町村名
    oaza_town_chome_name        varchar(32),  -- 大字・丁目名
    koaza_alias                 varchar(64),  -- 小字・通称名
    block_number                varchar(10),  -- 街区符号
    coordinate_system_number    integer,            --座標系番号
    x_coordinate                numeric(11,3),      --X座標
    y_coordinate                numeric(11,3),      --Y座標
    latitude                    numeric(12,8),      -- 緯度
    longitude                   numeric(12,8),      -- 軽度
    residence_display_flag      varchar(4),  -- 住居表示フラグ
    represent_flag              varchar(4),  -- 代表フラグ
    before_update_history_flag  varchar(4),  -- 更新前履歴フラグ
    after_update_history_flag   varchar(4)   -- 更新後履歴フラグ
);