SpringBootアプリケーションでCSVからMySQLにデータをインポートする

Last updated at 2024-12-25Posted at 2024-12-25

migrationファイルを使用してMySQLにCSVからデータをインポートしたときに引っかかったのでメモ

目的

CSVファイルとmigrationファイルを読み込んでMySQLにデータをインポートする。
アプリケーション初回起動のmigration実行時にCSVデータがMySQLにインポートされるようにする。

環境

(前回と同じ)
WSL バージョン: 2.3.26.0
カーネルバージョン: 5.15.167.4-1
WSLg バージョン: 1.0.65
MSRDC バージョン: 1.2.5620
Direct3D バージョン: 1.611.1-81528511
DXCore バージョン: 10.0.26100.1-240331-1435.ge-release
Windows バージョン: 10.0.22631.4460
Rancher Desktop V1.16.0
MySQL 8.0

SpringBoot 3.3.5

前提条件

前回のようにmigrationの実行までできていること。

インポート対象CSVについて

今回は総務省市区町村コードのデータを加工して使用する。

次のようなCSVデータに加工する。
団体コードは6桁だが最後の桁は誤り検出用のため今回インポート不要とする。
最初の2桁は都道府県を表し、その後の3桁は市区町村を表す。

都道府県番号、市区町村番号、市区町村名、市区町村名(ひらがな)、市区町村イニシャル(ひらがな)
のカラムに登録する。

cities.csv

131016,東京都,千代田区,ﾄｳｷｮｳﾄ,ﾁﾖﾀﾞｸ
131024,東京都,中央区,ﾄｳｷｮｳﾄ,ﾁｭｳｵｳｸ
131032,東京都,港区,ﾄｳｷｮｳﾄ,ﾐﾅﾄｸ
131041,東京都,新宿区,ﾄｳｷｮｳﾄ,ｼﾝｼﾞｭｸｸ
131059,東京都,文京区,ﾄｳｷｮｳﾄ,ﾌﾞﾝｷｮｳｸ
131067,東京都,台東区,ﾄｳｷｮｳﾄ,ﾀｲﾄｳｸ
131075,東京都,墨田区,ﾄｳｷｮｳﾄ,ｽﾐﾀﾞｸ

migrationファイルの作成

-- V1.99__Insert_cities_dummy_data.sql
DROP FUNCTION IF EXISTS zenkakuhiragana;

DELIMITER // 

CREATE FUNCTION zenkakuhiragana(DATA TEXT CHARACTER SET utf8mb4) RETURNS TEXT CHARACTER 
SET
    utf8mb4 DETERMINISTIC BEGIN DECLARE kana1_len, 
    kana2_len INT;

DECLARE kana1_h VARCHAR(72) character set utf8mb4 DEFAULT 'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｯｬｭｮｧｨｩｪｫ ｰ｡｢｣､･1234567890';

DECLARE kana1_z VARCHAR(72) character set utf8mb4 DEFAULT 'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわをんっゃゅょぁぃぅぇぉ　ー。「」、・１２３４５６７８９０';

DECLARE kana2_h VARCHAR(52) character set utf8mb4 DEFAULT 'ｶﾞｷﾞｸﾞｹﾞｺﾞｻﾞｼﾞｽﾞｾﾞｿﾞﾀﾞﾁﾞﾂﾞﾃﾞﾄﾞﾊﾞﾋﾞﾌﾞﾍﾞﾎﾞﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟｳﾞ';

DECLARE kana2_z VARCHAR(26) character set utf8mb4 DEFAULT 'がぎぐげござじずぜぞだぢづでどばびぶべぼぱぴぷぺぽゔ';

SET
    kana1_len = CHAR_LENGTH(kana1_z);

SET
    kana2_len = CHAR_LENGTH(kana2_z);

WHILE kana2_len > 0 DO
SET
    data = REPLACE(
        data,
        SUBSTRING(kana2_h, kana2_len * 2 - 1, 2),
        SUBSTRING(kana2_z, kana2_len, 1)
    );

SET
    kana2_len = kana2_len - 1;

END WHILE;

WHILE kana1_len > 0 DO
SET
    data = REPLACE(
        data,
        SUBSTRING(kana1_h, kana1_len, 1),
        SUBSTRING(kana1_z, kana1_len, 1)
    );

SET
    kana1_len = kana1_len - 1;

END WHILE;

RETURN data;

END;
//
DELIMITER;

LOAD DATA LOCAL INFILE 'src\\main\\resources\\db\\csv\\cities.csv' INTO TABLE city FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (
    @group_code,
    @prefecture,
    @city,
    @city_kana
)
SET
    prefecture_id = SUBSTRING(@group_code, 1, 2),
    code = SUBSTRING(@group_code, 3, 3),
    name = @city,
    name_kana = zenkakuhiragana(@city_kana),
    kana_initial = zenkakuhiragana(SUBSTRING(@city_kana, 1, 1));

こちらの記事のソースを参考に半角カナを全角かなに変換して取り込むmigrationファイルを作成。

リンク先にSQLの説明があるので異なる部分を軽く補足。
LOAD DATA LOCAL INFILEこの部分はLOCALがないと外部ファイルを読み込むことができない。
MySQLでは現在文字コードはutf8mb4が推奨されているよう。

ディレクトリ作成

src/main/resourcesのdbフォルダ内にcsv,devディレクトリを作成する。

.
├── db
│   ├── csv
│   │   └── cities.csv
│   ├── dev
│   └── migration
│       └── V0.01__Create_city_table.sql
└── application.properties

csv
読み込み対象のCSVファイルを格納する。
dev
開発環境で使用したいダミーデータなどを格納する。

アプリケーション起動

基本的にはこのようにファイルを格納すればCSVデータが読み込まれる。
ただし、初回実行時にはエラーが発生することがあるため発生するログと必要な対応を以下説明していく。
主に以下の設定ファイルを修正する。

my.cnf
application.ymlまたはapplication-dev.yml

アプリケーション起動時エラーその1

Message:You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V1.99__Insert_cities_data.sql failed
----------------------------------------------
SQL State  : HY000
Error Code : 1419
Message    : You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Location   : db/dev/V1.99__Insert_cities_data.sql (C:\**\demo\target\classes\db\dev\V1.99__Insert_cities_data.sql)
Line       : 6
Statement  : CREATE FUNCTION zenkakuhiragana(DATA TEXT CHARACTER SET utf8mb4) RETURNS TEXT CHARACTER
SET
    utf8mb4 DETERMINISTIC BEGIN DECLARE kana1_len,
    kana2_len INT;

このログはストアドファンクションが使用できない設定になっていることを示している。
demo\infra\db\conf.d\my.cnfに次の一行を追加する。([mysqld]の項目に追加する。)

log_bin_trust_function_creators = 1

Messageにless safeと書かれているがこれはセキュリティ的な側面ではなく、関数の内容によってはDB内に不整合なデータが生成される可能性があるため。(任意の引数に対して戻り値が一つに定まらないような関数等が該当する)
十分関数の内容を確認、理解した上で実装が必要という意味でのless safeのよう。

my.cnfを更新したあとはMySQLの設定を更新しているのでdocker compose buildを行う必要がある。

アプリケーション起動時エラーその2

Message: Loading local data is disabled; this must be enabled on both the client and server sides

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V1.99__Insert_cities_data.sql failed
----------------------------------------------
SQL State  : 42000
Error Code : 3948
Message    : Loading local data is disabled; this must be enabled on both the client and server sides
Location   : db/dev/V1.99__Insert_cities_data.sql (C:\*\demo\target\classes\db\dev\V1.99__Insert_cities_data.sql)
Line       : 57
Statement  : LOAD DATA LOCAL INFILE 'src\\main\\resources\\db\\csv\\cities.csv' INTO TABLE city FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS (

発生原因:今回MySQLコンテナとは別にアプリケーション実行環境のcsvディレクトリ(ローカル)にcsvファイルを置いているから。(許可設定をしないとアクセスできない)

よって、メッセージにある通りMySQLサーバーとアプリケーションの設定を変更する必要がある。

1. MySQLサーバーの外部ファイル読み込み設定

demo\infra\db\conf.d\my.cnfにlocal_infileの一行を追加する。

[mysqld]
local_infile = 1

以前のMySQLでは設定せずとも外部ファイルが読み込めたが現在はデフォルトが0となっていて設定が必要。

2. アプリケーションの外部ファイル読み込み許可設定

demo\src\main\resources\application-dev.ymlのflyway:url:の末尾に?allowLoadLocalInfile=trueをつけると文字通り外部からのファイルの読み込みを許可することができる。

  flyway:
    enabled: true
    locations: classpath:db/migration,db/dev
    user: demouser
    password: demopassword
    url: jdbc:mysql://localhost:3306/mydatabase?allowLoadLocalInfile=true

今回はmigration時にcsvファイルを読み込むため、flywayのurl項目にallowLoadLocalInfileをつけている。もし、アプリケーション起動中などにcsvファイルを読み込む必要があればdatasourceのurl項目に同様の設定が必要である。

アプリケーション起動結果

migrationに成功していればレコードが2つできている。

mysql> SELECT * FROM flyway_schema_history;
+----------------+---------+--------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description        | type | script                        | checksum    | installed_by | installed_on        | execution_time | success |
+----------------+---------+--------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
|              1 | 0.01    | Create city table  | SQL  | V0.01__Create_city_table.sql  | -1257955382 | myuser       | 2024-12-25 15:13:49 |             21 |       1 |
|              2 | 1.99    | Insert cities data | SQL  | V1.99__Insert_cities_data.sql |  2088160095 | myuser       | 2024-12-25 15:13:49 |            428 |       1 |
+----------------+---------+--------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
2 rows in set (0.00 sec)

cityテーブルは日本をコンソールに表示できなかったのでA5:SQL-Mk-2で確認

無事csvからインポートできていることが確認できた。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up