データベースへcsvでデータをインポートする

 今回はcsvで正規化されたデータをデータベースにインポートしていきます。これは、

データベースの正規化(エクセルでの処理) (https://smizunolab.blogspot.com/2024/06/blog-post_18.html)

の続きです。利用するcsvファイルは、csv にも掲載してあります。この5個のファイルを利用してやっていきます。

(1) テーブルの作成

今回のテーブル構成は、メインテーブル(corporation)1個と外部テーブル(classification, agreement, content, conclusion)の4つです。以下のようにルールを決めます

・全てのテーブルは「id」を持ち、int not null, auto_increment, primary key とする 

・メインテーブルでの外部キー：「外部テーブル名_id」

・外部テーブルの構成：「id」、「name」は必須、このidはメインテーブルへの参照とする

このルールでテーブルを作成すると、以下のSQL文になります。

	CREATE DATABASE IF NOT EXISTS disaster;
	USE disaster;
	-- Classification table
	CREATE TABLE classification (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
	);
	-- Agreement table
	CREATE TABLE agreement (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
	);
	-- Content table
	CREATE TABLE content (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
	);
	-- Conclusion table
	CREATE TABLE conclusion (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY (id)
	);
	-- Corporation (Main table)
	CREATE TABLE corporation (
	id INT NOT NULL AUTO_INCREMENT,
	classification_id INT NOT NULL,
	date_of_agreement DATE NOT NULL,
	partner VARCHAR(255) NOT NULL,
	agreement_id INT NOT NULL,
	content_id INT NOT NULL,
	conclusion_id INT NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (classification_id) REFERENCES classification(id),
	FOREIGN KEY (agreement_id) REFERENCES agreement(id),
	FOREIGN KEY (content_id) REFERENCES content(id),
	FOREIGN KEY (conclusion_id) REFERENCES conclusion(id)
	);

view raw gistfile1.txt hosted with ❤ by GitHub

これを用いて、データベースを作成します。今回のデータベースは「disaster」とします。

(2) データベースの用意

データベースはAWS RDSを利用します。下記を参考に、データベースに接続してください。

AWS RDSでデータベースを作成しCloudShellから接続してSQL文からデータを格納する(https://smizunolab.blogspot.com/2024/06/aws-rdscloudshellsql.html)

Cloud Shellに接続して、csvファイルを5個アップロードしておきます。

Cloud Shell からRDSで立ち上げたMySQLに接続します。まず、データベースとテーブルを(1)のSQL文で作成します。テーブルまで作成したら、csvからデータをインポートします。

	LOAD DATA LOCAL INFILE 'classification.csv'
	INTO TABLE classification
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(name);
	LOAD DATA LOCAL INFILE 'agreement.csv'
	INTO TABLE agreement
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(name);
	LOAD DATA LOCAL INFILE 'content.csv'
	INTO TABLE content
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(name);
	LOAD DATA LOCAL INFILE 'conclusion.csv'
	INTO TABLE conclusion
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(name);
	LOAD DATA LOCAL INFILE 'corporation.csv'
	INTO TABLE corporation
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(@dummy, classification_id, date_of_agreement, partner, agreement_id, content_id, conclusion_id)
	SET id = NULL;

view raw gistfile1.txt hosted with ❤ by GitHub

テーブルの中身を全て削除するときは、truncateでやりますが、今回外部キー制約を入れてあるので、制約を一度解除してからtruncateすればテーブルの中身は消すことができます。

	SET FOREIGN_KEY_CHECKS = 0;
	TRUNCATE TABLE classification;
	SET FOREIGN_KEY_CHECKS = 1;

view raw gistfile1.txt hosted with ❤ by GitHub

各テーブルに対して、select文で確認してみましょう。

他のテーブルも内容が入っているか確認してください。

(3) テーブルのリレーションを利用

corporationテーブルを表示すると、各テーブルのidで出力されます。この参照しているidを外部テーブルから値を取得するにはjoinを使います。

	SELECT
	corporation.id,
	classification.name AS classification_name,
	corporation.date_of_agreement,
	corporation.partner,
	agreement.name AS agreement_name,
	content.name AS content_name,
	conclusion.name AS conclusion_name
	FROM
	corporation
	JOIN
	classification ON corporation.classification_id = classification.id
	JOIN
	agreement ON corporation.agreement_id = agreement.id
	JOIN
	content ON corporation.content_id = content.id
	JOIN
	conclusion ON corporation.conclusion_id = conclusion.id;

view raw gistfile1.txt hosted with ❤ by GitHub

このように実行すると、値を連携することができます。

(4) データベースのバックアップ

最後に、今回作成した「disaster」データベースをテーブル、データごとバックアップします。

mysqldump -u admin -p --host=各自のエンドポイント disaster > disaster_backup.sql

これでCloud Shell上にバックアップが作成できました。

以上で、エクセルで正規化したデータをデータベースに格納することができました。またバックアップも取得できました。