1. 必要なファイル
とりあえず必要なファイルは三種類。init.sql、.env、そして Dockerfile です。
3つとも同じディレクトリに配置します。
ls -a . .. .env Dockerfile init.sql
2. init.sql の内容
--init.sql CREATE DATABASE IF NOT EXISTS buzzing; USE buzzing; CREATE TABLE IF NOT EXISTS `yt_mst_cnl` ( `channel_id` varchar(40) NOT NULL, `channel_name` tinytext, `description` text, `thumbnail` text, `uploads_list` varchar(40) DEFAULT NULL, `published_at` date DEFAULT NULL, `data_update_date` date DEFAULT NULL, PRIMARY KEY (`channel_id`), KEY `idx_published_at` (`published_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE IF NOT EXISTS `yt_mst_vid` ( `video_id` varchar(20) NOT NULL, `video_name` text, `description` text, `thumbnail` text, `channel_id` varchar(40) DEFAULT NULL, `published_at` varchar(8) DEFAULT NULL, `data_update_date` varchar(8) DEFAULT NULL, PRIMARY KEY (`video_id`), KEY `fk_channel` (`channel_id`), KEY `idx_published_at` (`published_at`), CONSTRAINT `fk_channel` FOREIGN KEY (`channel_id`) REFERENCES `yt_mst_cnl` (`channel_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE IF NOT EXISTS `yt_pfm_cnl` ( `channel_id` varchar(40) DEFAULT NULL, `subscriber_count` bigint DEFAULT NULL, `hidden_subscriber_count` varchar(1) DEFAULT NULL, `view_count` bigint DEFAULT NULL, `video_count` int DEFAULT NULL, `data_date` varchar(8) DEFAULT NULL, KEY `fk_channel_pfm` (`channel_id`), CONSTRAINT `fk_channel_pfm` FOREIGN KEY (`channel_id`) REFERENCES `yt_mst_cnl` (`channel_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE IF NOT EXISTS `yt_pfm_vid` ( `video_id` varchar(20) DEFAULT NULL, `view_count` bigint DEFAULT NULL, `like_count` int DEFAULT NULL, `dislike_count` int DEFAULT NULL, `favorite_count` int DEFAULT NULL, `comment_count` int DEFAULT NULL, `most_used_words` text, `data_date` varchar(8) DEFAULT NULL, KEY `fk_video_pfm` (`video_id`), CONSTRAINT `fk_video_pfm` FOREIGN KEY (`video_id`) REFERENCES `yt_mst_vid` (`video_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE IF NOT EXISTS `yt_analysis_07` ( `channel_id` varchar(40) DEFAULT NULL, `channel_name` tinytext, `view_count` bigint DEFAULT NULL, `like_count` int DEFAULT NULL, `dislike_count` int DEFAULT NULL, `favorite_count` int DEFAULT NULL, `comment_count` int DEFAULT NULL, `video_count` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; DROP PROCEDURE IF EXISTS `buzzing`.`yt_analysis_07`; DELIMITER // CREATE PROCEDURE `buzzing`.`yt_analysis_07`(IN published_after VARCHAR(8)) BEGIN -- truncate the yt_analysis_07 table TRUNCATE TABLE yt_analysis_07; -- insert data into the yt_analysis_07 table INSERT INTO yt_analysis_07 SELECT channel_id, channel_name, view_count, like_count, dislike_count, favorite_count, comment_count, video_count FROM ( SELECT B.channel_id AS channel_id, MAX(C.channel_name) AS channel_name, SUM(A.view_count) AS view_count, SUM(A.like_count) AS like_count, SUM(A.dislike_count) AS dislike_count, SUM(A.favorite_count) AS favorite_count, SUM(A.comment_count) AS comment_count, COUNT(*) AS video_count FROM yt_pfm_vid A LEFT JOIN yt_mst_vid B ON A.video_id = B.video_id LEFT JOIN ( SELECT channel_id, MAX(channel_name) AS channel_name FROM yt_mst_cnl GROUP BY channel_id ) C ON B.channel_id = C.channel_id WHERE B.published_at >= @published_after GROUP BY channel_id ) T1 ORDER BY view_count DESC; COMMIT; END // DELIMITER ;
3. .env の内容
MYSQL_ROOT_PASSWORD=rootpassword MYSQL_USER=admin MYSQL_PASSWORD=password MYSQL_DATABASE=buzzing
4. Dockerfile の内容
# Dockerfile FROM mysql ADD init.sql /docker-entrypoint-initdb.d
5. コンテナの作成
% docker build -t docker_mysql:1.0 . % docker run --env-file .env --name docker_mysql -p 13306:3306 -it -d docker_mysql:1.0 % docker exec -it docker_mysql bash bash-4.4#
6. MySQL で確認
bash-4.4# mysql -u admin -p Enter password: mysql>
テーブルの確認
mysql> use buzzing; mysql> show tables; +-------------------+ | Tables_in_buzzing | +-------------------+ | yt_analysis_07 | | yt_mst_cnl | | yt_mst_vid | | yt_pfm_cnl | | yt_pfm_vid | +-------------------+ 5 rows in set (0.00 sec)
プロシージャの確認
mysql> call buzzing.yt_analysis_07('20230101'); Query OK, 0 rows affected (0.03 sec)
ちなみに DBeaver でもポート 13306 を通して接続できました。