データを扱うためにMariaDBに挑戦してみた

どうも、じぇいかわさきです。

皆さんはデータを扱う時、Excelとかに記述して使用していると思いますが、数が増えてくると大変ですよね。

そこで、大量のデータをデータベースに登録しておけば、後で加工するにしても非常に楽になると思いませんか。

今回、Excelにためておいたデータをフリーのデータベースにデータを登録し、以降活用が楽になるように環境整備しました。

大量のデータを、今後扱おうと考えている人には参考になるかも。

データが10,000を超えたらデータベースでしょう

集計や加工が簡単でデータを操作する感じがする
Webとの相性も良い

フリーのMariaDBを使ってデータを保存する

さて、ほとんどの人は表計算のExcelか互換アプリを使用してデータを保管したり、加工したりしていると思います。

しかし、Excelの場合にはカラムの順番を変えたりすると、データ自体も変わってしまうという特徴がありますね。

オリジナルのデータは常にどうだったのかを保持するために、必ずオリジナルを保存してから修正をしていると思います。

一般の人から見ると、Excelは非常に優れたアプリと思われるので、今更変えようとはなかなか思わないと思います。

でもよく考えてみていただきたいのは、Excelはあくまでもデータを表形式にして計算をするアプリであり、データを保管しておくアプリでは有りません。

マイクロソフトでも、Officeには表計算のExcelとデータベースのAccessは別物として準備しているのですから。

本来の使い方からすると、計測器などの設備から出力されるデータは、カンマ区切りのCSVというファイルが多いです。

このCSVは直接Excelで読み込むことができるので、ほとんどの人が読み込んだデータをそのままExcelで保管してしまっているのだと思います。

しかし、データを保存し蓄積するのはデータベースの役割ですから、やはりCSVデータもデータベースに流し込んでしまえば、後でExcelでも使えるし、他のアプリでも使えるようになるんです。

また、データベースにしておけば、今流行りのネットからのアクセスも可能になります。Webとの相性が非常に良いので、Excelなどのアプリが無くてもWebページからデータの読み書きができるようになるんです。

だから、データベースを活用するほうが、データの有効活用という意味からも、非常に有効な手段なんですね。

今回、フリーのMariaDBを使用しました。このMariaDBはMySQLからの派生ですから、基本のコマンドはほぼコンパチで使用できます。

MySQLといえばWordpressの心臓部にも使われていますが、信頼がおけるものですね。

実際にデータベースを作ってみよう

それでは実際に実行していきましょう。自分の環境はmacなので、Windowsの人はインストール方法が違いますので、そちらは調べてくださいね。

自分で調べると、忘れにくく学習には効果的ですから。

まず、MariaDBをインストールします。インストールするには開発環境用のインストーラHomeBrewを使用します。

brew install mariadb

これでエラーが出なければインストール完了です。仮にエラーが出た場合には、1つずつチェックして対処していくしか有りません。いろんな開発環境とか入っていなければ、素直にインストールできると思います。

それでは実際に使っていきましょう。最初にデータベースサービスを起動させます。

$ systemctl start mariadb.service

これでサービスが起動しますので、まずはrootでログインします。

******@NERV ~ % mysql -u root -p          
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.4.6-MariaDB Homebrew
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 

この状態になったらMariaDBにログインしたことになります。

まずはrootではなく、実際に操作するユーザーを作成しましょう。

ユーザーの作成はCREATE USERというコマンドを使用氏作成します。

次にユーザーに各種権限を与えます。今回、自分のローカル環境でrootの代わりに使用するため、全ての権限を与えます。

まず、できたばかりのユーザーの権限を確認すると

MariaDB [(none)]> show grants for neo@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for neo@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'neo'@'localhost' IDENTIFIED BY PASSWORD '*85675B75F5F7B31A69D0406356C83A86A1BE0227' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

となっており、何も権限が有りません。ここに、PRIVILEGESというコマンドで権限を付加します。

MariaDB [(none)]> grant all privileges on *.* to neo@localhost;
Query OK, 0 rows affected (0.024 sec)

さて、権限孵化後はどう変化したか確認しましょう。

MariaDB [(none)]> show grants for neo@localhost;
+---------------------------------------------------------------------------------------------------------------------+
| Grants for neo@localhost                                                                                            |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'neo'@'localhost' IDENTIFIED BY PASSWORD '*85675B75F5F7B31A69D0406356C83A86A1BE0227' |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

全ての権限が付与されたのがわかりますね。

これで一旦rootからログアウトして、実際に作ったユーザーでログインしなおします。

******@NERV ~ % mysql -u neo -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.6-MariaDB Homebrew
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 

今回、自分の趣味でロト6の当選データを1回から全部データベースしようと思っています。

まずデータベースを作成します。

MariaDB [(none)]> create database loto6;
Query OK, 1 row affected (0.002 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| iotdata            |
| loto6              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.001 sec)

loto6というデータベースができているのが確認できました。

次に、現在は使用しているデータベースがnoneになっていますので、loto6を使用するようにします。

MariaDB [(none)]> use loto6;
Database changed
MariaDB [loto6]> 

今までnoneだった部分がloto6に代わったのが確認できますね。

それではデータを格納するテーブルを作ります。

MariaDB [loto6]> create table result(
   -> kaisai int,
   -> date date,
   -> No1 int,
   -> No2 int,
   -> No3 int,
   -> No4 int,
   -> No5 int,
   -> No6 int,
   -> bonus int,
   -> primary key(kaisai));
Query OK, 0 rows affected (0.036 sec)
MariaDB [loto6]> show tables
   -> ;
+-----------------+
| Tables_in_loto6 |
+-----------------+
| result          |
+-----------------+
1 row in set (0.001 sec)
MariaDB [loto6]> show columns from result;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| kaisai | int(11) | NO   | PRI | NULL    |       |
| date   | date    | YES  |     | NULL    |       |
| No1    | int(11) | YES  |     | NULL    |       |
| No2    | int(11) | YES  |     | NULL    |       |
| No3    | int(11) | YES  |     | NULL    |       |
| No4    | int(11) | YES  |     | NULL    |       |
| No5    | int(11) | YES  |     | NULL    |       |
| No6    | int(11) | YES  |     | NULL    |       |
| bonus  | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
9 rows in set (0.006 sec)

resultというテーブルが作られて、テーブルの属性も確認できました。最初のkaisaiというカラムがプライマリーキーに設定してあります。

いよいよ、事前に用意しておりたロト6の当選データをCSVファイル化してありますので、これをテーブルの中にインポートしましょう。

MariaDB [loto6]> load data local infile "/Users/******/Downloads/loto6_data.csv"
   -> into table result
   -> fields terminated by ',';
Query OK, 1552 rows affected, 1551 warnings (0.015 sec)
Records: 1552  Deleted: 0  Skipped: 0  Warnings: 1551

見事に1552件のデータがインポートされました。Warningが出ていますが、DeleteもSkipも無いので問題はないでしょう。

実際にインポートされたデータをみてみると

MariaDB [loto6]> select * from result;
+--------+------------+------+------+------+------+------+------+-------+
| kaisai | date       | No1  | No2  | No3  | No4  | No5  | No6  | bonus |
+--------+------------+------+------+------+------+------+------+-------+
|      1 | 2000-10-05 |    2 |    8 |   10 |   13 |   27 |   30 |    39 |
|      2 | 2000-10-12 |    1 |    9 |   16 |   20 |   21 |   43 |     5 |
|      3 | 2000-10-19 |    1 |    5 |   15 |   31 |   36 |   38 |    13 |
|      4 | 2000-10-26 |   16 |   18 |   26 |   27 |   34 |   40 |    13 |
|      5 | 2000-11-02 |    9 |   15 |   21 |   23 |   27 |   28 |    43 |
|      6 | 2000-11-09 |    6 |   12 |   23 |   25 |   28 |   38 |    22 |
|      7 | 2000-11-16 |    7 |   19 |   21 |   23 |   33 |   35 |    26 |
|      8 | 2000-11-23 |   11 |   16 |   18 |   20 |   42 |   43 |    28 |
|      9 | 2000-11-30 |   11 |   19 |   23 |   38 |   39 |   42 |    15 |
|     10 | 2000-12-07 |    1 |    3 |   19 |   21 |   35 |   39 |    26 |
|     11 | 2000-12-14 |   12 |   26 |   32 |   37 |   40 |   42 |    16 |
|     12 | 2000-12-21 |   14 |   17 |   27 |   28 |   35 |   39 |    22 |
|     13 | 2000-12-28 |   13 |   29 |   31 |   37 |   41 |   42 |    17 |
|     14 | 2001-01-04 |    2 |    8 |   11 |   35 |   37 |   38 |    43 |
|     15 | 2001-01-11 |    4 |    6 |   11 |   20 |   21 |   28 |    36 |
|     16 | 2001-01-18 |    5 |   12 |   13 |   29 |   34 |   35 |    30 |
| 

こんな感じで1552件分のデータが表示されました。

これで過去データのデータベース化が完了です。

これを使って、予測を研究していきたいと思っております。

データを扱うためにデータベースを使ってみた　まとめ

今回は大量の保有データを効率よく使うために、CSVファイルからデータベースへ変更する手順を紹介しました。

自分はロト6の当選番号予測という目的が有りましたが、その他にも気象データや株式データ等大量に収集して、見え方を加工するようなことについては、非常に有効な手段だと思います。

このMariaDBを使うことで、PythonやPHPとの連携でWeb上からデータのアクセスができるようになりますので、今後はそのインターフェースを作って行きたいと思います。

なにか評価する時に、わざわざCSVファイルを作成するよりも、なにかこんな感じでデータベースを作っておくと、いろいろな評価にも使えますので、興味が湧いた方は是非作ってみてくださいね。

今回はプログラミング言語を活用するためのサブ環境であるデータベースのお話でした。