Dockerコンテナで動作しているPostgreSQLへCSVファイル取込
Docker 環境で動作しているPostgreSQLへ郵便番号データ(CSVファイル)を取り込みます。Dockerコンテナの外からpsqlで接続しCSVファイルを取り込めばもっと手数が少なく済むとおもいますが、今回はあえてコンテナ内にCSVファイルを配備して、コンテナ内でpsqlをたたいて取り込んでみます。
今回取込CSVはこちらから調達します
読み仮名データの促音・拗音を小書きで表記しないもの - zip形式 日本郵便
郵便番号データのうち、「ホツカイドウ」のように促音・拗音を小書きで表記しないデータをご提供しています。
www.post.japanpost.jp
郵便番号データの説明 - 日本郵便
郵便番号データファイルは、郵便番号と住所等を対応させたデータベースです。
www.post.japanpost.jp
コンテナからホストマシンへのマウントポイントを確認する
以下の通り、コンテナからはD:\docker\post153\pgdataに対してアクセスができるので、こちらに取り込みたいCSV（ken_all.csv）を配備します。この時注意点があります。このダウンロードしたCSVファイルはShift_JIS+CRLFです。今回インポートするにはutf-8+LFに変換が必要です。やり方はいろいろあるとは思いますが私はpowershellで変換しました。
-- 以下コマンドの詳細を知りたい方はこちらが参考になります。
--
PS D:\docker> docker container inspect pos153cont | ConvertFrom-Json | Select-Object -ExpandProperty Mounts
Type : bind
Source : D:\docker\post153\pgdata
Destination : /var/lib/postgresql/data
Mode :
RW : True
Propagation : rprivateダウンロードしたCSVファイルをutf-8 + LF に変換する
-- 以下コマンドの詳細を知りたい方はこちらが参考になります。
--
PS D:\docker\post153\pgdata> (Get-Content .\KEN_ALL.CSV -encoding shift_jis) | % { $_ + "`n" } | Set-Content -LiteralPath .\KEN_ALL.CSV -NoNewline -encoding utf-8コンテナ内から取り込むCSVを確認する
PS D:\docker\post153> docker exec -it pos153cont bash
root@56e8c587e449:/# ls -lt /var/lib/postgresql/data/KEN_ALL.CSV
-rwxrwxrwx 1 root root 12339292 Jun 27 05:05 /var/lib/postgresql/data/KEN_ALL.CSV
root@56e8c587e449:/#取込を行うテーブルを作成する
取込テーブル用DDL
CSVは１５項目あるので、対応するテーブルも通常１５カラムの定義をするのですが、以下DDLは１６カラム作成しています。第一カラムは生成列で定義しているので、copyコマンドがCSVとのマッピングを外してくれて問題なく取り込みが完了します。
create table postal_code_imp ( prefecture_code varchar(2) generated always as (substr(jis_address_cd,1,2))stored ,jis_address_cd varchar(5) ,postal_code_old varchar(5) ,postal_code varchar(7) ,prefecture_kana varchar(20) ,shikucyoson_kana varchar(40) ,cyoiki_kana varchar(160) ,prefecture varchar(20) ,shikucyoson varchar(40) ,cyoiki varchar(160) ,multiple_postal_div varchar(1) ,koaza_div varchar(1) ,cyome_div varchar(1) ,multiple_regions_div varchar(1) ,update_div varchar(1) ,reason_div varchar(1) );
root@56e8c587e449:/# psql -U sooni -d udondb -h localhost
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.
udondb=# create table postal_code_imp (
udondb(# jis_address_cd varchar(5)
udondb(# , postal_code_old varchar(5)
udondb(# , postal_code varchar(7)
udondb(# , prefecture_kana varchar(20)
udondb(# , shikucyoson_kana varchar(40)
udondb(# , cyoiki_kana varchar(160)
udondb(# , prefecture varchar(20)
udondb(# , shikucyoson varchar(40)
udondb(# , cyoiki varchar(160)
udondb(# , multiple_postal_div varchar(1)
udondb(# , koaza_div varchar(1)
udondb(# , cyome_div varchar(1)
udondb(# , multiple_regions_div varchar(1)
udondb(# , update_div varchar(1)
udondb(# , reason_div varchar(1)
udondb(# );
CREATE TABLE
udondb=#
CSVデータ取り込み
丁目レベルインポート
\copy postal_code_imp from 'KEN_ALL.CSV' delimiter ',' csv header
udondb=# \copy postal_code_imp from '/var/lib/postgresql/data/KEN_ALL.CSV' delimiter ',' csv header
COPY 124577
udondb=#
-- あっという間に取込が完了します
--
udondb=# select count(*) from postal_code_imp;
count
--------
124577
(1 row)
udondb=#
