どんなCSVも楽々DBに取り込めるようにしてみた
2022.03.17
ものすごく簡単に
どんなCSVも楽々DBに取り込めるようにしてみました。
ものすごく簡単にです。
たぶんですけど。
とりあえず動作確認してみたい方はこちらから諸々参照して試してみてください。
ちなみにPythonで実装、DBはMySQL限定となってます。
あしからず。
ざっくり説明すると、
- 取り込みたいCSVファイルの情報を定義して、
- CSVファイルを所定のディレクトリに置いて、
- pythonを実行する
となります。
create文とかinsert文とかの作成は要りません。
あらやだ、簡単ですね。
まずは動作検証結果からご紹介（検証はMacでしてます）。
検証1: 郵便番号CSVをぱぱっと取り込んでみる
日本郵便 のサイトからCSVを取得。
折角なので（？）全国一括を取り込んでみます。
DLしたCSVの情報を調べて定義を追加します（詳細は GithubのReadme を参照）。
で、DLしたCSVを↑で定義したディレクトリに置いて実行。
1 2 3 4 5 | > pipenv run python 01_import.py postcd recreate Loading .env environment variables... [2022-03-11 17:06:58,495][INFO] 01_import.py:22 - # CSV Import Start. [2022-03-11 17:06:58,495][INFO] 01_import.py:62 - ## KEN_ALL.CSV -> t_postcd Start. [2022-03-11 17:06:58,937][INFO] db_service.py:78 - ### table recreate has done. [t_postcd] |
実行開始！
しかし全然終わらない。
うーん、12.3MB。
1 2 3 4 | [2022-03-11 17:06:58,937][INFO] db_service.py:78 - ### table recreate has done. [t_postcd] [2022-03-11 17:14:43,482][INFO] db_service.py:103 - ### table import has done. [124,531 records] [2022-03-11 17:14:43,484][INFO] 01_import.py:96 - ## KEN_ALL.CSV -> t_postcd End. [2022-03-11 17:14:43,484][INFO] 01_import.py:102 - # CSV Import End. [result: add 124,531 records to t_postcd] |
おわたー。約8分。
ではMySQL確認。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> show tables; +--------------+ | Tables_in_db | +--------------+ | t_postcd | +--------------+ 1 rows in set (0.00 sec) mysql> show full columns from t_postcd; +--------+------+-------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------+------+-------------+------+-----+---------+-------+---------------------------------+---------+ | pc_001 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | | | pc_002 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | | | pc_003 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | | 〜〜〜 | pc_014 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | | | pc_015 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | | +--------+------+-------------+------+-----+---------+-------+---------------------------------+---------+ 15 rows in set (0.00 sec) |
テーブル作成されてる。
ちなみに、項目は設定したプレフィックス＋連番、型はTEXT（ええ、全部TEXT型です）、CSVファイル1行目がヘッダ項目ならそれを項目コメントに設定（郵便番号CSVはヘッダ項目無しなので無し）。
1 2 3 4 5 6 7 | mysql> select count(*) from t_postcd; +----------+ | count(*) | +----------+ | 124531 | +----------+ 1 row in set (0.01 sec) |
約12万件。
本社（福岡）の郵便番号で検索してみる。
1 2 3 4 5 6 7 | mysql> select pc_003, pc_008, pc_009 from t_postcd where pc_003 = '8120038'; +---------+--------------------+-----------+ | pc_003 | pc_008 | pc_009 | +---------+--------------------+-----------+ | 8120038 | 福岡市博多区 | 祇園町 | +---------+--------------------+-----------+ 1 row in set (0.25 sec) |
いいですね（いいんですよね？）。
なんでだろう、世田谷にあこがれる。
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> select pc_003, pc_008, pc_009 from t_postcd where pc_008 = '世田谷区'; +---------+--------------+--------------------------------+ | pc_003 | pc_008 | pc_009 | +---------+--------------+--------------------------------+ | 1540000 | 世田谷区 | 以下に掲載がない場合 | | 1560044 | 世田谷区 | 赤堤 | | 1540001 | 世田谷区 | 池尻 | 〜〜〜 | 1580097 | 世田谷区 | 用賀 | | 1540023 | 世田谷区 | 若林 | +---------+--------------+--------------------------------+ 62 rows in set (0.25 sec) |
グッド。
次。
検証2: 駅データのCSVをさささっと取り込んでみる
駅データ.jp からCSVをダウンロード（無料ですが要会員登録）。
郵便番号と同じ要領でDLしたCSVの情報を調べて定義を追加して実行。
1 2 3 4 5 6 7 8 | > pipenv run python 01_import.py station recreate Loading .env environment variables... [2022-03-11 18:42:54,673][INFO] 01_import.py:22 - # CSV Import Start. [2022-03-11 18:42:54,673][INFO] 01_import.py:62 - ## station20220310free.csv -> t_station Start. [2022-03-11 18:42:54,752][INFO] db_service.py:78 - ### table recreate has done. [t_station] [2022-03-11 18:43:35,333][INFO] db_service.py:103 - ### table import has done. [10,883 records] [2022-03-11 18:43:35,335][INFO] 01_import.py:96 - ## station20220310free.csv -> t_station End. [2022-03-11 18:43:35,335][INFO] 01_import.py:102 - # CSV Import End. [result: add 10,883 records to t_station] |
1万件は1分弱。
MySQL検証。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> show tables; +--------------+ | Tables_in_db | +--------------+ | t_postcd | | t_station | +--------------+ 2 rows in set (0.00 sec) mysql> show full columns from t_station; +--------+------+-------------+------+-----+---------+-------+---------------------------------+----------------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--------+------+-------------+------+-----+---------+-------+---------------------------------+----------------+ | st_001 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | station_cd | | st_002 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | station_g_cd | | st_003 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | station_name | 〜〜〜 | st_014 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | e_status | | st_015 | text | utf8mb4_bin | YES | | NULL | | select,insert,update,references | e_sort | +--------+------+-------------+------+-----+---------+-------+---------------------------------+----------------+ 15 rows in set (0.00 sec) |
テーブル作成OK。
駅CSVはヘッダ行有りなので項目コメントにその値がセットされてる。
1 2 3 4 5 6 7 | mysql> select count(*) from t_station; +----------+ | count(*) | +----------+ | 10883 | +----------+ 1 row in set (0.00 sec) |
駅って日本にそんなにあるですねー？
とか思ったですが、どうやら駅と沿線でユニークとなるデータの模様。
ので、駅名と都道府県CDでグルーピングして件数カウントしてみる。
1 2 3 4 5 6 7 | mysql> select count(*) as st_uniq_cnt from (select 1 from t_station group by st_003, st_007) tmp; +-------------+ | st_uniq_cnt | +-------------+ | 9265 | +-------------+ 1 row in set (0.03 sec) |
それでも9265駅ってゆー。
コトコトことでん！
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> select st_003, st_006, st_009 from t_station where st_006 = '99802'; +--------------------------------------+--------+--------------------------------------------------+ | st_003 | st_006 | st_009 | +--------------------------------------+--------+--------------------------------------------------+ | 高松築港 | 99802 | 高松市多玉藻町９７-２ | | 片原町 | 99802 | 高松市鶴屋町９-１ | | 瓦町 | 99802 | 高松市常磐町１-３-１ | 〜〜〜 | 榎井 | 99802 | 仲多度郡琴平町榎井４００-３ | | 琴電琴平 | 99802 | 仲多度郡琴平町３６０-２２ | +--------------------------------------+--------+--------------------------------------------------+ 23 rows in set (0.03 sec) |
沿線数が多い駅Top10！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select st_003, count(*) as cnt from t_station group by st_003, st_007 order by cnt desc limit 10; +--------+-----+ | st_003 | cnt | +--------+-----+ | 新宿 | 13 | | 東京 | 11 | | 横浜 | 11 | | 渋谷 | 11 | | 大宮 | 9 | | 池袋 | 9 | | 上野 | 8 | | 新橋 | 7 | | 京都 | 7 | | 岡山 | 7 | +--------+-----+ 10 rows in set (0.04 sec) |
ほう、岡山。こんどうんちくしよーっと。
とまぁ、だいたいこんな感じです。
処理の概要について
では処理の概要についてざっくり説明します！と思ったですが、ちょっと長くなっちゃったのと、説明も長くなりそうなので、別途別記事で説明させていただきますね。
すみません。
※ こちら上記の Githubのリンク です
書いた人はこんな人
IT技術10月 27, 2023LocalStackを使ってファンアウトしてみた- IT技術6月 13, 2023【ISUCON部】ゆうれい部員がやってきた！
- IT技術3月 17, 2022どんなCSVも楽々DBに取り込めるようにしてみた
- IT技術6月 19, 2019AnsibleでMacの環境構築をしてみた
関連記事
採用情報
\ あの有名サービスに参画!? /
バックエンドエンジニア
\ クリエイティブの最前線 /
フロントエンドエンジニア
\ 世界を変える…！ /
Androidエンジニア
\ みんなが使うアプリを創る /
iOSエンジニア
