独学エンジニアの課題として、ログ解析システムを作った。 Step3. データのインポート

この記事は「 つながる勉強会 Advent Calendar 2022 - Adventar 」の 22 日目の記事です。

adventar.org

前日は、すな（@suna_tech） さんでした！

www.sunapro.com

こんにちは！

スマレジ・テックファーム の Webエンジニア やまて と申します。

はじめに

「独学エンジニアの課題として、ログ解析システムを作った。」というタイトルで、課題の製作物完成までの道のりを投稿しています。

GitHub の Public リポジトリで誰でも閲覧できるようにしています。

github.com

独学エンジニア とは

『独学エンジニア』とは、Web開発（主にサーバーサイド）の動画学習教材です。

dokugaku-engineer.com

連載の目次

「独学エンジニアの課題として、ログ解析システムを作った。」の連載については、以下の順に投稿しています。

• Step1. 製作物の確認
• Step2. DBのテーブル作成
• Step3. データのインポート
• Step4. SELECT 文での検索
• Step5. プログラムからのSQLの操作①
• Step6. プログラムからのSQLの操作②

ログ解析システム カテゴリーの記事一覧 - 転職したらスマレジだった件

今回は Step3. データのインポート です。

目次

環境

	バージョン
macOS Big Sur	11.6
Docker Desktop	4.7.0
PHP	8.1.7
MySQL	8.0.28

インポートするデータ形式の確認

ダウンロードできるデータの形式は以下のような形式です。

domain_code	page_title	count_views	total_response_size
ドメインコード	ページタイトル	各時間のページ表示回数	合計レスポンスサイズ
aa	Main_Page	4	0
aa	Wikipedia	1	0

pageviews-20211201-000000.gz をダウンロードしてファイルを開くと、以下のようなファイルです。

aa Main_Page 4 0
aa Wikipedia 1 0
aa Wikipedia:Statistics 1 0
aa.b Main_Page 1 0
aa.d Main_Page 4 0
aa.m Main_Page 1 0
ab - 3 0
ab 1025 1 0
ab 1036 1 0
ab 1121 1 0
ab 1146 1 0
ab 1148 2 0
ab 1177 2 0
ab 1261 1 0
ab 1267 1 0
ab 1791 1 0
ab 1795 1 0
ab 1886 1 0
ab 1913 1 0
ab 1919 1 0
ab 1989 1 0
ab 338 1 0
ab 589 1 0
ab 984 1 0
ab Авикипедиа 1 0
ab Авикипедиа:Афорум 12 0
...

値の間はスペース区切りで、スクロールしても下まで辿り着くのが大変なくらいの行数のアクセスログについてのデータが書いてあります。

1. MySQL へのデータのインポート手順（完成形）

テキストファイルから MySQL へのデータインポート手順の完成形を確認します。

1-1. MySQL への root ユーザーでのログイン

Docker の db コンテナの MySQL に、root ユーザーでログインします。

docker-compose exec db mysql -p

1-2. local-infile の設定変更（ ON に設定）

下記 SQL を実行して、 local-infile を ON に設定します。

mysql> SET GLOBAL local_infile=ON;

1-3. GRANT 構文でのユーザー権限指定（SUPER に指定）

GRANT 構文で、ユーザー権限を SUPER に指定します。

mysql> GRANT SUPER ON *.* To test_user@'%';

mysql> quit

1-4. テキストファイルから MySQL へのデータインポート

テキストファイルから MySQL のテーブルへデータをインポートします。

docker-compose exec app mysqlimport -h db -u test_user -p -d --fields-terminated-by=' ' --local test_database databases/page_views

実行するとパスワードを聞かれるので、 test_user のパスワード pass を入力します。

以上が、手順の完成形です。

ここに至る前での試行錯誤の詳細について、以降で振り返ります。（失敗の連続でした！…完成形は短いですが、めっちゃ色々と調べました）

2. データのインポート方法の詳細の調査と実行

データのインポート方法の詳細について、調査したことをコマンド実行しながら試します。

2-x. テキストファイルから MySQL のテーブルへのデータインポート（失敗）

テキストファイルから MySQL のテーブルへのデータインポートするために、 mysqlimport コマンドを実行します。

以下の mysqlimport コマンドを実行します。（失敗）

docker-compose exec app mysqlimport -u test_user -D test_database -p --local databases/pageviews-20211201-000000

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [22:45:26]
$ docker-compose exec app mysqlimport -u test_user -D test_database -p --local databases/pageviews-20211201-000000
mysqlimport: unknown option '-D'

-D オプションはありませんでした。

以下の mysqlimport コマンドを実行します。（失敗）

docker-compose exec app mysqlimport -u test_user test_database -p --local databases/pageviews-20211201-000000

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [17:30:31] C:2
$ docker-compose exec app mysqlimport -u test_user test_database -p --local databases/pageviews-20211201-000000
Enter password:
mysqlimport: Error: 2002 Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

以下の点を改善します。

• test_database は、 -p, --local オプションより後に書く。

• インポート元のファイル名については、インポート先のテーブル名と揃える必要があるため、 以下の通りにファイル名を変更する。

  pageviews-20211201-000000 → page_views

ちなみに、エラー文にある /run/mysqld/mysqld.sock を作成してもうまくいかず。

以下の mysqlimport コマンドを実行します。（失敗）

docker-compose exec app mysqlimport -h db -u test_user -p --local test_database databases/page_views

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [6:41:17] C:1
$ docker-compose exec app mysqlimport -h db -u test_user -p --local test_database databases/page_views
Enter password:
mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

mysqlimport: Error: 1227 この操作を行うには、SUPER、SYSTEM_VARIABLES_ADMIN、SESSION_VARIABLES_ADMINのうち少なくとも1つの権限が必要です。

ユーザー権限が不足している状態で mysqlimport コマンドを実行すると権限エラーとなります。

SUPER、SYSTEM_VARIABLES_ADMIN、SESSION_VARIABLES_ADMIN のうち、少なくとも1つの権限が必要である、とのことです。

dev.mysql.com

test_user を SUPER 権限のあるユーザーにするための方法を試します。

2-1. mysqlimport コマンドを実行するためのユーザー権限付与

blog.katsubemakito.net

ユーザーとホストの確認

Docker の db コンテナの MySQL に、root ユーザーでログインします。

docker-compose exec db mysql -p

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [6:14:42] C:126
$ docker-compose exec db mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 841
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

MySQLの user と host を確認します。

SELECT user, host FROM mysql.user;

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test_user        | %         |
| healthchecker    | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.03 sec)

ユーザーの権限の確認

現在付与されている権限を SHOW GRANTS 構文で確認します。

以下は、ホストが % のユーザー test_user の権限の初期設定を確認しています。

• 確認：test_user@'%' の権限の初期設定

SHOW GRANTS for test_user@'%';

mysql> SHOW GRANTS for test_user@'%';
+--------------------------------------------------------------+
| Grants for test_user@%                                       |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`                        |
| GRANT ALL PRIVILEGES ON `test_database`.* TO `test_user`@`%` |
+--------------------------------------------------------------+
2 rows in set (0.01 sec)

• 参考：root@localhost の場合

SHOW GRANTS for root@localhost;

mysql> SHOW GRANTS for root@localhost;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

ユーザーの権限の指定

GRANT 構文で、権限を指定します。（テスト）

GRANT ALL ON db.test_database To test_user@'%';

mysql> GRANT ALL ON db.test_database To test_user@'%';
Query OK, 0 rows affected (0.02 sec)

SHOW GRANTS 構文で、現在付与されている権限を確認します。

SHOW GRANTS for test_user@'%';

mysql> SHOW GRANTS for test_user@'%';
+-----------------------------------------------------------------+
| Grants for test_user@%                                          |
+-----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`                           |
| GRANT ALL PRIVILEGES ON `test_database`.* TO `test_user`@`%`    |
| GRANT ALL PRIVILEGES ON `db`.`test_database` TO `test_user`@`%` |
+-----------------------------------------------------------------+
3 rows in set (0.00 sec)

指定は Query OK だったけど、内容はやり直し。

ユーザー権限の剥奪

REVOKE 構文で、指定した権限を削除します。

REVOKE ALL ON db.test_database FROM test_user@'%';

mysql> REVOKE ALL ON db.test_database FROM test_user@'%';
Query OK, 0 rows affected (0.03 sec)

SHOW GRANTS 構文で、現在付与されている権限を確認します。

SHOW GRANTS for test_user@'%';

mysql> SHOW GRANTS for test_user@'%';
+--------------------------------------------------------------+
| Grants for test_user@%                                       |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%`                        |
| GRANT ALL PRIVILEGES ON `test_database`.* TO `test_user`@`%` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

ユーザー権限の指定

GRANT 構文で、権限を SUPER に指定します。

GRANT SUPER ON *.* To test_user@'%';

mysql> GRANT SUPER ON *.* To test_user@'%';
Query OK, 0 rows affected, 1 warning (0.01 sec)

SHOW GRANTS 構文で、現在付与されている権限を確認します。

SHOW GRANTS for test_user@'%';

mysql> SHOW GRANTS for test_user@'%';
+--------------------------------------------------------------+
| Grants for test_user@%                                       |
+--------------------------------------------------------------+
| GRANT SUPER ON *.* TO `test_user`@`%`                        |
| GRANT ALL PRIVILEGES ON `test_database`.* TO `test_user`@`%` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

USAGE → SUPER に変更されました。

2-2. local-infile パラメータの設定変更

以下の mysqlimport コマンドを実行します。（失敗）

docker-compose exec app mysqlimport -h db -u test_user -p --local test_database databases/page_views

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [7:22:19]
$ docker-compose exec app mysqlimport -h db -u test_user -p --local test_database databases/page_views
Enter password:
mysqlimport: Error: 3948, Loading local data is disabled; this must be enabled on both the client and server sides, when using table: page_views

MySQL 8.0 で LOAD DATA LOCAL INFILE は無効化されたようです。 有効にするには local-infile を ON に設定する必要があるようです。

mita2db.hateblo.jp

Docker の db コンテナの MySQL に、root ユーザーでログインします。

docker-compose exec db mysql -p

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [6:14:42] C:126
$ docker-compose exec db mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 841
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

下記 SQL を実行して、 local-infile を ON に設定します。

SET GLOBAL local_infile=ON;

mysql> SET GLOBAL local_infile=ON;
Query OK, 0 rows affected (0.00 sec)

MySQLの環境変数を確認するには、以下をSQLウィンドウに入力し実行します。

すべての変数が確認できますので、その中で「LOCAL_INFILE」を探し「ON」になっていることを確認します。

SHOW VARIABLES;

www.ipvx.info

2-3. テキストファイルから MySQL のテーブルへのデータインポート

以下の mysqlimport コマンドを実行します。（ほぼ成功！）

docker-compose exec app mysqlimport -h db -u test_user -p --local test_database databases/page_views

実行するとパスワードを聞かれるので、 test_user のパスワード pass を入力する。

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [7:30:18] C:127
$ docker-compose exec app mysqlimport -h db -u test_user -p --local test_database databases/page_views
Enter password:
test_database.page_views: Records: 5124896  Deleted: 0  Skipped: 0  Warnings: 15379916

データはテーブルに登録されたました！

カラムに入れるようにコマンドにオプションを加えます。

docker-compose exec app mysqlimport -h db -u test_user -p -d --fields-terminated-by=' ' --local test_database databases/page_views

• --fields-terminated-by=' ' をつけて、スペース区切りのデータがそれぞれカラムに入るようにします。
• ファイルをインポートする前に、インポート先のテーブルを空にするため、 -d オプションをつけます。

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/import-data x [7:26:35]
$ docker-compose exec app mysqlimport -h db -u test_user -p -d --fields-terminated-by=' ' --local test_database databases/page_views
Enter password:
test_database.page_views: Records: 5124896  Deleted: 0  Skipped: 0  Warnings: 2989

成功しました！

おわりに

今回は「独学エンジニアの課題として、ログ解析システムを作った。」の Step3. データのインポート でした。

次回は、Step4. SELECT 文での検索 について投稿します。

ありがとうございました。

参考

MySQL へのデータインポートについて、参考にしたページです。

dev.mysql.com

phpjavascriptroom.com

blog.katsubemakito.net

blog.katsubemakito.net

#Qiita の Contributions が 2000 超えた！🙏 pic.twitter.com/qjkCtRKj7L

— やまて｜Webエンジニア2年目 (@r_yamate) 2022年12月7日

こういうやり込み要素はアウトプットの楽しみの一つですね。

qiita.com