以前からお客さんのプロジェクトの管理画面のダッシュボードに日別や月別の売り上げ合計や会員の獲得数などをグラフで表示したいと思っていました。もちろん、グラフを作成するjavascriptのライブラリはいくつかありますが、習得にも開発にも時間がたくさんかかりそうです。そこで私の目に留まったのが、Googleデータポータル。データソースを指定してビジュアルなツールでちょいちょいとグラフ化できそうです。今回は、それを使用してMySQLのデータベースから月別に登録した会員数の棒グラフの作成の仕方を説明します。
完成は以下のような画面です。
準備：データベース
まず、データポータルが取得してくるデータのソースとなるデータベースの準備が必要です。コマンドラインから、以下を実行します。
$ mysql -u root mysql -p Enter password: mysql> create database l58; mysql> create user 'gds'@'%' identified by 'password'; mysql> grant select on l58.* to 'gds'@'%';
データベース名 ⇒ l58
ユーザー名 ⇒ gds
パスワード ⇒ password
権限 ⇒ SQL文でSELECTのみの実行が可能
アクセス権限 ⇒ インターネットのどこからも（上では、’gds’@’%’の%の指定により）
という設定です。しかし、たとえパスワードの認証があってもインターネットのどこからでもこのDBにアクセスできるのは不安ですね。これに関しては後に対応します。
次は、このデータベースを使用するLaravelのプロジェクトの作成ですが、ここでは最新のLaravelのバージョン（5.8）をインストールしたと仮定します。.envの設定には先のデータベース情報を使用することを忘れずに。
このデータベースでのDBテーブルの作成は、以下のようにmigrateを実行します。
$ php artisan migrate Dropped all tables successfully. Migration table created successfully. Migrating: 2014_10_12_000000_create_users_table Migrated: 2014_10_12_000000_create_users_table (0.02 seconds) Migrating: 2014_10_12_100000_create_password_resets_table Migrated: 2014_10_12_100000_create_password_resets_table (0.02 seconds)
今度は、DBテーブル、usersを会員のテーブルとして、ここにフェイクの会員レコードを100個作成します。もちろん、factory()のヘルパーを使用してレコード作成となりますが、１つ問題があります。デフォルトの設定では、作成された会員のレコード作成日時(created_at)が皆作成日が同じになってしまうのです。これでは月別の棒グラフでは今月のみの表示となってしまいます。
Fakerのドキュメントを閲覧すると、dateTimeThisYear()という関数があり、多分に今年の日付をランダムに作成してくれるようです。この関数を使い、以下のようにUserFactory.phpを編集して、created_atに値がランダムに振り当てられるようにします。
...
$factory->define(User::class, function (Faker $faker) {
$dtm = $faker->dateTimeThisYear($max = 'now', $timezone = 'Asia/Tokyo');
return [
'name' => $faker->name,
'email' => $faker->unique()->safeEmail,
'email_verified_at' => now(),
'password' => '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', // password
'remember_token' => Str::random(10),
'created_at' => $dtm,
'updated_at' => $dtm,
];
});
用意ができたところで、いつものようにtinkerでレコード作成してみましょう。
>>> factory(App\User::class, 100)->create();
=> Illuminate\Database\Eloquent\Collection {#2982
all: [
App\User {#2978
name: "鈴木 直子",
email: "kana93@example.com",
email_verified_at: "2019-07-18 20:06:46",
created_at: "2018-10-13 10:06:32",
updated_at: "2018-10-13 10:06:32",
id: 1,
},
App\User {#2976
name: "伊藤 幹",
email: "shuhei93@example.com",
email_verified_at: "2019-07-18 20:06:46",
created_at: "2019-05-17 21:42:06",
updated_at: "2019-05-17 21:42:06",
id: 2,
},
...
100個レコードできました。意図通りに、作成日時（created_at）もレコードごとに違います。しかし、何故か去年のレコードが作成されているのは変ですね。いつか調べてみます。
準備：MySQLのセキュリティ
先ほど設定したデータベースは、ユーザー情報があればインターネットのどこからでも3306のポートでアクセスできます。Googleデータポータルだけからのアクセスに制限したいなら、ここで書かれているIPのみに制限を課すことができます。
AWSならEC2でセキュリティグループを作成して以下のIPだけから、MySQLのポート(3306)にアクセスできる制限を設定できます。そして、このセキュリティグループを使用しているEC2のセキュリティに追加すればよいです。
データソースの作成
データベースとデータの準備ができたところで、今度はGoogleデータポータルに移ります。まずは、先のデータベースに接続するために、データソースなるものを作成します。Googleのアカウントですでにログインしていると仮定して、こちらへアクセスしてください。
以下の画面で、作成ボタンを押して、データソースを選択します。
次に、数あるデータコネクタから、MySQLを選択します。左上のデータソース名を無題から改名することもお忘れずに。
今度は、データベース情報を入力して、認証ボタンを押します。
認証が成功すると、DBテーブルのリストが右に表示されます。そこから、使用したいDBテーブルを選択します。ここでは、会員のusersを選択します。そして、右上の再接続をクリックしてください。
再接続したのちに表示される画面は、DBテーブル、usersの項目のリストです。なぜか、create_at, updated_atのtimestampの項目は「テキスト」となっているので、以下のように日時のタイプを選択しておいてください。
もう１つの作業として、最終の棒グラフでは、X軸を年月としたいので、年月のフィールドをcreated_atをもとに作成します。
以下のように、フィールドを追加で開かれる画面で、計算式をTODATE(created_at,'DEFAULT_DECIMAL', '%Y-%m')と設定します。ここ、MONTH(created_at)のように思われますが、グラフが月別の表示とならなくうまく行きません。Googleデータポータルは、MySQLのtimestampのデータタイプを文字列とみなしているためかもしれません。
レポートの作成
フィールドの設定が完了したところで、このデーターソースを使ってレポートを作成します。画面右上のレポートを作成ボタンを押してください。
開かれた画面で、縦棒グラフを追加します。
次に、以下のように、棒グラフの右のパネルで指定します。「使用可能な項目」から、適切な項目をドラグして以下にドロップします。
上の画面で行った設定は、
データソース ⇒ DS MySql（すでにデフォルトで設定されている）
期間のディメンション ⇒ created_at
ディメンション ⇒ 年月
指標 ⇒ idをドラグして、名前を「会員数」とし、集計方法を「件数」に変更。
並べ替え ⇒ 年月。降順から昇順に変更。
こう設定すると、X軸に年月、Y軸にに会員数と、指定のデータベースのデータをもとにした棒グラフが期待通りに表示されます。
表示モードとするとこのポストの最初の画面となります。右上の期間の指定を変えてみてください。応じて、棒グラフが変わります。
最後に、ここで作成したレポートは、どのように管理画面にはめ込むのでしょうか？
それは次回のポストとします。