転職したらスマレジだった件

スマレジのエンジニアやまてのテックブログです。マジレス大歓迎です。

トップ > 学習記録 > 独学エンジニアの課題として、ログ解析システムを作った。 Step1. 製作物の確認

独学エンジニアの課題として、ログ解析システムを作った。 Step1. 製作物の確認

学習記録 SQL ログ解析システム

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

adventar.org

前日は、よしたろう（@yoshitaro_yoyo） さんでした！

qiita.com

ここ２ヶ月で学んだこととのことですが深すぎてヤバすぎです。（感想が浅くてスミマセン）


こんにちは！

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

はじめに

今回から、独学エンジニアの課題としてを作った、ログ解析システムについて、完成までの道のりを投稿します。

GitHub の Public リポジトリとして、閲覧できます。

github.com

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

dokugaku-engineer.com


目的

ログ解析システムを作成した目的は、ソフトウェアエンジニアリングにおいて実際によく登場するタスクの一つである、ログデータを解析を実際にやってみることで、データベースとSQLスキルの向上を目指す、という目的です。

具体的には、以下のデータベースとSQLスキルの向上を目指しています。

  • データベースとテーブルを作成する
  • テーブル内にデータを保存する
  • SELECT文で検索する
  • プログラムからSQLを操作する

概要

Wikipedia のアクセスログの情報である pageviews ファイルを利用して、アクセスログを解析するシステムです。

「アクセスログを解析する」とは、アクセスログの情報を DB に取り込んで、コマンドラインから実行して、SQL の操作で、ある決まった条件のデータを取得する、という簡単なものです。


アクセスログには、読者がウェブページを読み込むたびにどのページが読み込まれたかが記録されています。これらの情報を使って、サイトのユーザーの活動に関する質問に答えるシステムを作成します。


以下の順に投稿します。

今回は Step1 として、 製作物の確認 をします。

完成した成果物について、何をするものか、使い方などをまとめて、どのようなものを作成したかのイメージをお伝えしたいと思います。

また、 Step2 〜 Step5 では、作成手順でのポイントやつまづいた点をまとめます。

目次

環境

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

1. Wikipedia のアクセスログのデータについて

以下、Wikipedia のアクセスログのデータのダウンロードと、データ形式についてです。

1-1. データのダウンロード

Wikipedia のアクセスログのデータは下記 URL からダウンロードします。

dumps.wikimedia.org

データについての全体の解説は、下記URLにて行われているのでご参照ください。

dumps.wikimedia.org

ダウンロードしたデータのテーブル定義は下記URLで解説されているのでご参照ください。

wikitech.wikimedia.org

1-2. データ形式

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

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
...

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

2. Wikipedia ログ解析システムでできること

できることは以下の二つです。

2-1. 最もビュー数の多いページの表示

最もビュー数の多い記事を、指定した記事数分だけビュー数が多い順にソートし、ドメインコードとページタイトル、ビュー数を表示する

（例）コマンドライン上で2記事と指定した場合、下記を表示する

”en”, “Main_Page”, 120
”en”, ”Wikipedia:Umnyango_wamgwamanda”, 112

2-2. ドメインコードの人気順の表示

指定したドメインコードに対して、人気順にソートし、ドメインコード名と合計ビュー数を表示する

（例）コマンドライン上で「en de」と指定した場合、下記を表示する

”en”, 10700
”de”, 5300

3. プログラムを使う準備

以下、プログラムを使う準備の手順です。

3-1. Docker コンテナの準備

Docker コンテナを生成・起動します。

# Docker コンテナの生成
docker compose build

# Docker コンテナの起動
docker compose up -d

3-2. DBのテーブル作成

DBのテーブル作成します。

docker compose exec app php databases/initialize_page_views_table.php

3-3. データのダウンロード

Wikipedia のアクセスログのデータは下記URLからダウンロードします。

dumps.wikimedia.org

ダウンロードしたファイルは解凍した状態で、 databases ディレクトリに移動します。

ファイル名は、 page_views に変更します。 （インポートの際にテーブル名と揃えておく必要があります）

3-4. データのインポート

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

docker compose exec db mysql -p

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

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

mysql> SET GLOBAL local_infile=ON;

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

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

mysql> quit

テキストファイルから 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 を入力します。

4. プログラムの使い方

「2. Wikipedia ログ解析システムでできること」の「2-1. 最もビュー数の多いページの表示」「2-2. ドメインコードの人気順の表示」の使い方についてです。

4-1. 最もビュー数の多いページの表示

最もビュー数の多い記事を、指定した記事数分だけビュー数が多い順にソートし、ドメインコードとページタイトル、ビュー数を表示する

下記コマンドを実行します。

docker compose exec app php most_viewed_pages.php 20

most_viewed_pages.php の後に半角スペースを開けて、指定するページ数を入力します。

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/create-program x [7:25:16]
$ docker compose exec app php most_viewed_pages.php 20
"en.m", "Main_Page", 122058
"en", "Main_Page", 69181
"en", "Special:Search", 26630
"de", "Wikipedia:Hauptseite", 20739
"en.m", "Special:Search", 19119
"ja", "メインページ", 18475
"es.m", "Wikipedia:Portada", 15335
"es", "Wikipedia:Portada", 15261
"fr", "Wikipédia:Accueil_principal", 14744
"thankyou", "Thank_You/en", 13449
"ru", "Заглавная_страница", 13336
"en", "Lotfi_A._Zadeh", 12864
"it", "Pagina_principale", 12731
"zh", "Wikipedia:首页", 10782
"pt", "Wikipédia:Página_principal", 10485
"de.m", "Wikipedia:Hauptseite", 10386
"ja.m", "メインページ", 9421
"en", "Bible", 9024
"fr.m", "Wikipédia:Accueil_principal", 8705
"en", "-", 7227

4-2. ドメインコードの人気順の表示

指定したドメインコードに対して、人気順にソートし、ドメインコード名と合計ビュー数を表示する

下記コマンドを実行します。

docker compose exec app php popular_domain_codes.php en de ja

popular_domain_codes.php の後に半角スペースを開けて、指定するドメインコードを 2 つ以上入力します。

# r_yamate @ mbp in ~/Documents/code/wikipedia-log-analysis-tool on git:feature/create-program x [7:35:12] C:16
$ docker compose exec app php popular_domain_codes.php en de ja
"en", 3556081
"ja", 367924
"de", 284178

5. 停止の方法

Docker コンテナを停止します。

docker compose stop

おわりに

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

次回は、 Step2. DBのテーブル作成 について投稿します。

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



一人で 25 記事を投稿するアドベントカレンダーもやっております。