はじめに

独学エンジニアという教材を使用してプログラミングの学習をしています。

今回のログ解析システムは、【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（環境変数を設定）

【ステップ０】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:

【ステップ１】データベースとテーブルを作成する

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;

ダウンロードしたデータのテーブル定義を参考に、プライマリーキーは複合主キーとしています。

参考: 複合主キーとは

【ステップ２】テーブル内にデータをインポートする

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エラーの解消

【ステップ３】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;

【ステップ４】プログラムから 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'@'%';

参考: ユーザーに権限を設定する(GRANT文)

実行例

コマンドラインで実行すると、下記のようにログデータを解析できます。

★Wikipediaのアクセスログを分析します。

1: 最もビュー数の多い記事を表示
2: 人気記事の合計ビュー数を表示
9: 終了する

上記から選択してください(1, 2, 9を選択): 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
----------------------------------------

【ステップ５】README.md の作成

GitHub からのクローンで誰にでも使用してもらえるよう、README.md を作成しました。

気を付けた点

初見でもスムーズにプログラムが使用できるよう、作成するディレクトリ名やファイル名を明確にしました。
README.md を作り終えた後、実際にプログラムを動かせるか Github からクローンして試しました。つまづく点があったので気付いた点は修正しました。

おわりに

MySQL

MySQLでの作業はエラーでつまづく時間がほとんどでした。
今回のプロジェクトを通して、米袋の中の五粒ほどMySQLを理解できたのではないかと思います。
公式ドキュメントを読むのも苦でなくなってきました。

GitHub

以前に開発したブラックジャックゲームのときに比べ、ブランチの切り方やプルリクエストの仕方など、Gitの理解を深めて開発することができました。

+ﾟﾟ*｡＊⌒＊｡ﾟ＊⌒＊ﾟ｡＊⌒＊｡ﾟ＊⌒＊ﾟ｡ﾟ＊⌒＊ﾟ｡*ﾟ＊
それでは、ここまで読んでくださりありがとうございました☺