イベントスケジューラーで定期CSVエクスポートのバッチジョブを実行する

はじめに

ちょっと手元で自動でデータが生成される環境がほしかったので、イベントスケジューラーでデータをインサートして、ストアドプロシジャーでCSVエクスポートする、という環境を構築した際のメモ。

テーブルの作成

いつものごとくイベントスケジューラー用のテーブルを作成する。ここでは5秒後に設定された条件でランダムに値がインサートされるテーブルを生成する。

# データベース作成
create database auto_event_log;
use auto_event_log;

# テーブル定義
CREATE TABLE auto_event_log.logs (
   id INT AUTO_INCREMENT,
    datetime DATETIME NOT NULL,
    value1 INT NOT NULL,
    value2 INT NOT NULL,
    category varchar(1) NOT NULL,
    flg INT NOT NULL,
    PRIMARY KEY (id)
);

# イベントスケジューラーの設定
# 不要になったらdrop event logs

create event logs
    on schedule every 5 SECOND
    starts now()
    do INSERT INTO auto_event_log.logs(datetime, value1, value2, category, flg) VALUES (
     NOW(),
     CASE WHEN MONTH(NOW()) IN (1,2,3) THEN FLOOR(RAND() * (100 * -1))
     WHEN MONTH(NOW()) IN (4,5,6) THEN FLOOR(RAND() * (10 * -2))
     WHEN MONTH(NOW()) IN (7,8,9) THEN FLOOR(RAND() * (100 * 1))
     ELSE FLOOR(RAND() * (10 * 2)) END,
     FLOOR(RAND() * 100),
     SUBSTRING(MD5(RAND()), 1, 1),
     floor(rand() * 2)
     );

データはこんな感じ。

mysql> select * from logs limit 10;
+----+---------------------+--------+--------+----------+-----+
| id | datetime            | value1 | value2 | category | flg |
+----+---------------------+--------+--------+----------+-----+
|  1 | 2020-10-28 15:51:46 |     17 |     60 | 7        |   0 |
|  2 | 2020-10-28 15:51:56 |      9 |     52 | c        |   0 |
|  3 | 2020-10-28 15:52:06 |      9 |     26 | 9        |   0 |
|  4 | 2020-10-28 15:52:16 |     16 |     19 | 1        |   1 |
|  5 | 2020-10-28 15:52:26 |      3 |      6 | e        |   0 |
|  6 | 2020-10-28 15:52:36 |      1 |      8 | d        |   0 |
|  7 | 2020-10-28 15:52:46 |     14 |      6 | d        |   0 |
|  8 | 2020-10-28 15:52:56 |      0 |     58 | f        |   0 |
|  9 | 2020-10-28 15:53:06 |     14 |     76 | 0        |   1 |
| 10 | 2020-10-28 15:53:16 |     19 |      2 | 0        |   1 |
+----+---------------------+--------+--------+----------+-----+
10 rows in set (0.00 sec)

ストアドプロシジャーの作成

さきほどのテーブルを1分ごとで条件でづけてCSVエクスポートするストアドプロシジャーexport_filtered_csvを作成する。' 'で空白を入れたりしている点は注意。

CREATE DEFINER=`root`@`localhost` PROCEDURE `export_filtered_csv`()
BEGIN
    SET @current_datetime = current_timestamp();
    SET @path = '/Users/aki/Desktop/import_csv/';
    SET @schema = 'auto_event_log.logs';
    SET @table = 'logs';

    SET @VarSQL = CONCAT(-- make col name and bind col
                        'SELECT "id", "datetime", "value1", "value2", "category", "flg" UNION ALL',' ',
                        -- export Time-constrained data
                        'SELECT * FROM',' ',@schema,' ','WHERE DATE_FORMAT(datetime, "%Y-%m-%d %h:%i:00") = DATE_FORMAT(NOW(), "%Y-%m-%d %h:%i:00")',' ',
                        -- INTO OUTFILE "~/<path_to_dir>/mysql2csv_<table_name>_yyyymmdd_hhmmss.csv"
                        'INTO OUTFILE',' ', '"',@path,'mysql2csv_',@table,'_',DATE_FORMAT(@current_datetime, '%Y%m%d_%H%i%s'),'.csv','"',' ',
                        -- FIELDS TERMINATED BY ','
                        'FIELDS TERMINATED BY',' ','","',' ',
                        -- ENCLOSED BY '"'
                        'ENCLOSED BY',' ', '\'"\'',' ',
                        ';');
    -- prepared statements(stmt) & excute & deallocate prepared statements              
    PREPARE stmt FROM @VarSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

export_filtered_csvのイベントを設定する。

# 不要になったらdrop event export_filtered_csv
create event export_filtered_csv
    on schedule every 60 SECOND
    starts now()
    do CALL export_filtered_csv();

時間が立つと、指定したフォルダにエクスポートされてくる。

➜ ls ~/Desktop/import_csv/
mysql2csv_logs_20201028_222350.csv  mysql2csv_logs_20201028_223150.csv  mysql2csv_logs_20201028_223950.csv  mysql2csv_logs_20201028_224750.csv
mysql2csv_logs_20201028_222450.csv  mysql2csv_logs_20201028_223250.csv  mysql2csv_logs_20201028_224050.csv  mysql2csv_logs_20201028_224850.csv
・・・
mysql2csv_logs_20201028_222950.csv  mysql2csv_logs_20201028_223750.csv  mysql2csv_logs_20201028_224550.csv  mysql2csv_logs_20201028_225350.csv
mysql2csv_logs_20201028_223050.csv  mysql2csv_logs_20201028_223850.csv  mysql2csv_logs_20201028_224650.csv

使いおわったらイベントスケジューラーはOFFにする。

mysql> SET GLOBAL event_scheduler = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW variables like 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

cronで設定する場合

エクスポート設定をイベントスケジュラーで設定しなくてもシェルスクリプトでmysqlクライアントでもできるはずだが、空のデータしかエクスポートされなかったので、一応メモしておく。cronの設定、環境変数もろもろためしたが、だめでした。試行錯誤した途中の結果なので、うまく動きません。

#!/bin/bash

location="/Users/aki/Desktop/import_csv"
user="*****"
pass="*****"
table="logs"
database="auto_event_log"

/usr/local/bin/mysql \
 -u$user \
 -p$pass \
 --column-names -e "SELECT * FROM $database.$table WHERE DATE_FORMAT(datetime, '%Y-%m-%d %h:%i:00') = DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:00');"  \
 > $location/$table"_mysql2csv_"$(date +"%Y%m%d_%H%M")"00".csv

cronの設定。

*/1 * * * * /usr/local/bin/bash /Users/aki/Desktop/mysql_export.sh