はじめに
独学エンジニアという教材を使用してプログラミングの学習をしています。
今回の ログ解析システム は、【PART データベースとSQL】の学習内容を集大成とするものです。その個人開発の過程を記録しました。
なぜログ解析システムなのか
ログデータの解析はソフトウェアエンジニアリングにおいて実際によく登場するタスクです。そのタスクを通じて、データベースとSQLスキルの向上を目指します。
ログ解析システムの概要
1. 最もビュー数の多い記事を、指定した記事数分だけビュー数が多い順にソートし、ドメインコードとページタイトル、ビュー数を提示します。
- 例)コマンドライン上で
2
記事と指定した場合、下記を表示します。
”en”, “Main_Page”, 120
”en”, ”Wikipedia:Umnyango_wamgwamanda”, 112
2. 指定したドメインコードに対して、人気順にソートし、ドメインコード名と合計ビュー数を提示します。
- (例)コマンドライン上で
en de
と指定した場合、下記を表示します。
”en”, 10700
”de”, 5300
開発をはじめる
開発にあたってのポイント
- データベースとテーブルを作成できる
- テーブル内にデータを保存できる
- SELECT文で検索できる
- プログラムからSQLを操作できる
開発環境
- Windows11 Home 16GB バージョン: 22H2
- Visual Studio Code バージョン: 1.81.0
- Docker Desktop for Windows v4.19.0
- PHP バージョン: 8.2.11
- MySQL バージョン: 8.0.32
使用したライブラリ
- Composer(PHPの依存管理ツール)
- Xdebug(デバッガ)
- PHP_CodeSniffer(コーディング規約に準拠しているかを検証)
- PHPMD(コード品質を向上)
- PHPStan(バグやエラーの検出)
- phpdotenv(環境変数を設定)
【ステップ0】Docker環境の構築
- PHPのDockerfileを作成します。
Docker Hubからどのイメージを選べばいいのか最初は全然わかりませんでした。コマンドラインプログラムなのでapache
不要なのかな?と思っていました。
コマンドラインプログラムでもApacheは必要でした!
- Apacheや他のウェブサーバーソフトウェアがない場合、PHPスクリプトが実行を終えた後すぐにコンテナが停止してしまいます。コンテナが終了しないようにするためには、バックグラウンドで実行されるApache等のソフトウェアが必要です。
FROM php:8.2-apache
WORKDIR /var/www/html
# PHP で必要なライブラリをインストール
RUN apt-get update \
&& apt-get install -y libonig-dev libzip-dev unzip mariadb-client \
&& docker-php-ext-install pdo_mysql mysqli mbstring zip bcmath \
&& pecl install xdebug \
&& docker-php-ext-enable xdebug
# composer のインストール
COPY --from=composer:2 /usr/bin/composer /usr/bin/composer
ENV COMPOSER_ALLOW_SUPERUSER 1
# ファイルのコピー
COPY ./src /var/www/html
COPY ./docker/app/php.ini /usr/local/etc/php/php.ini
COPY ./docker/app/xdebug.ini /usr/local/etc/php/conf.d/xdebug.ini
- MySQLのDockerfileを作成します。
FROM mysql/mysql-server:8.0
RUN microdnf install yum \
&& yum install -y glibc-langpack-ja glibc-locale-source git
ENV LANG ja\_JP.UTF-8
- docker-compose.ymlを作成します。
version: "3"
services:
app:
build:
context: .
dockerfile: ./docker/app/Dockerfile
volumes:
- ./src:/var/www/html
depends_on:
- db
db:
build:
context: .
dockerfile: ./docker/db/Dockerfile
ports:
- "53306:3306"
volumes:
- ./docker/db/my.cnf:/etc/mysql/my.cnf
- ./databases/page_views:/var/lib/mysql-files/page_views # インポートするデータをマウント
- mysql_data:/var/lib/mysql # mysql_dataという名前のDockerボリュームをマウント
env_file:
- ./docker/db/db-variables.env
-
# Dockerボリュームを使用することで、データの永続化を行い、コンテナが削除されてもデータが保持される
volumes:
mysql_data:
【ステップ1】データベースとテーブルを作成する
MySQLコンテナに入り、データベースを作成します。
CREATE DATABASE log_analysis;
続いてデーターベースを作成します。
CREATE TABLE page_views
(domain_code VARCHAR(20) NOT NULL,
page_title VARCHAR(300) NOT NULL,
count_views INTEGER NOT NULL,
total_response_size INTEGER NOT NULL,
PRIMARY KEY (domain_code, page_title)
)COLLATE utf8mb4_0900_bin;
ダウンロードしたデータのテーブル定義を参考に、プライマリーキーは複合主キーとしています。
【ステップ2】テーブル内にデータをインポートする
LOAD DATAステートメントを使用し、作成したテーブルにデータをインポートします。
下記は2つのエラーを乗り越えてやっとこさ作成したSQLです。
LOAD DATA INFILE '/var/lib/mysql-files/page_views' IGNORE
INTO TABLE page_views
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\n'
(@var1, @var2, @var3, @var4)
SET domain_code = @var1,
page_title = @var2,
count_views = @var3,
total_response_size = @var4;
エラー記録①
はじめ--secure-file-priv
エラーが出てつまりました。
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
データのマウント先がもともと/etc/mysql/
だったのですが、secure-file-priv
に設定された場所(/var/lib/mysql-files/
)に変更することで解消しました。
参考: MySQL secure-file-privに設定されている値を取得する
エラー記録②
Duplicate entry ‘〇〇' for key
エラーが出てつまりました。
ERROR 1062 (23000): Duplicate entry 'ace-Ôn_Keuë' for key 'page_views.PRIMARY'
参考: 【MySQL LOAD DATA】Duplicate entryエラーの解消
【ステップ3】SELECT 文の作成
1. 最もビュー数の多い記事を、指定した記事数分だけビュー数が多い順にソートし、ドメインコードとページタイトル、ビュー数を提示します。
SELECT
domain_code, page_title, count_views
FROM
page_views
ORDER BY
count_views DESC
LIMIT
:limit; -- 記事数を指定
2. 指定したドメインコードに対して、人気順にソートし、ドメインコード名と合計ビュー数を提示します。
SELECT
domain_code, count_views
FROM
page_views
WHERE
domain_code = :domain_code -- ドメインコードを指定
ORDER BY
count_views DESC
LIMIT
1;
【ステップ4】プログラムから SQL を操作する
-
PHPからMySQLを操作します。今回は PDO というモジュールを使用しました。
mysqli関数と比べると、PDOモジュールは私のような初学者にもわかりやすかったです。
参考: PHPでデータベースに接続するときのまとめ -
バリデーション処理を作成しました。
-
静的解析ツールによるリファクタリングを行いました。
テストも作成したかったのですが、難しくて後回しになっています・・・。
エラー記録③
PDOを使用してLOAD DATA
ステートメントを実行したところ、エラーが出ました。
# 認証エラー: MySQLサーバーで下記のホスト指定のユーザーが許可されていない!
SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'user'@'%’
MySQLユーザー(user)がMySQLサーバー上のファイルを読み書きできるようにするため FILE 権限を設定したところ、エラーが解消しました。
-- データをインポートできるようにファイル権限を設定
GRANT FILE ON *.* TO 'user'@'%';
実行例
コマンドラインで実行すると、下記のようにログデータを解析できます。
★Wikipediaのアクセスログを分析します。
1: 最もビュー数の多い記事を表示
2: 人気記事の合計ビュー数を表示
9: 終了する
上記から選択してください(1, 2, 9を選択): 1
★最もビュー数の多い記事を表示します
記事数を1以上の整数で指定してください: 5
----------------------------------------
”en.m”, ”Main_Page”, 122058
”en”, ”Main_Page”, 69181
”en”, ”Special:Search”, 26630
”de”, ”Wikipedia:Hauptseite”, 20739
”en.m”, ”Special:Search”, 19119
----------------------------------------
★Wikipediaのアクセスログを分析します。
1: 最もビュー数の多い記事を表示
2: 人気記事の合計ビュー数を表示
9: 終了する
上記から選択してください(1, 2, 9を選択): 2
★指定したドメインコードに対して、人気順にソートし、ドメインコード名と合計ビュー数を提示します
ドメインコードを指定してください(例: en de): en de
----------------------------------------
”en”, 69181
”de”, 20739
----------------------------------------
【ステップ5】README.md の作成
GitHub からのクローンで誰にでも使用してもらえるよう、README.md を作成しました。
気を付けた点
- 初見でもスムーズにプログラムが使用できるよう、作成するディレクトリ名やファイル名を明確にしました。
- README.md を作り終えた後、実際にプログラムを動かせるか Github からクローンして試しました。つまづく点があったので気付いた点は修正しました。
おわりに
MySQL
MySQLでの作業はエラーでつまづく時間がほとんどでした。
今回のプロジェクトを通して、米袋の中の五粒ほどMySQLを理解できたのではないかと思います。
公式ドキュメントを読むのも苦でなくなってきました。
GitHub
以前に開発したブラックジャックゲームのときに比べ、ブランチの切り方やプルリクエストの仕方など、Gitの理解を深めて開発することができました。
+゚ ゚*。*⌒*。゚*⌒*゚。*⌒*。゚*⌒*゚。゚*⌒*゚。*゚*
それでは、ここまで読んでくださりありがとうございました☺