MySQLのデータベースにCSVのテキストファイルを使ってデータをロードする方法
MySQLのデータベースにCSVのテキストファイルを使ってデータをロードする方法。
少ないレコード件数であれば、INSERT文を羅列しても良いですが、数千、数万以上のデータについては、テキストファイルにCSVデータを作成してデータベースへロードする方法が便利です。
１）MySQLへロードするCSVデータ
次に、データを格納するテーブルを作成します。
３） MySQLデータベースへのCSVファイルのロード
完全に自分のためのメモ。xampp上でWebサーバーのアクセスログを任意の条件で検索できるツールを作成しようとパーツを準備しています。
少ないレコード件数であれば、INSERT文を羅列しても良いですが、数千、数万以上のデータについては、テキストファイルにCSVデータを作成してデータベースへロードする方法が便利です。
１）MySQLへロードするCSVデータ
Webサーバーへのアクセスログを加工したものです。カンマ区切り、データはダブルコーテーションで括ってあります。
test_data.txt
時間フォーマット、"HH:MM:SS"
また、特にWindowsで作成する場合には、改行コードにも気をつけてください。改行コードは、CR+LFと、LFのときでMySQLへロードするときのオプションが異なります。どちらで作成したか覚えておいてください。
|
"2012-9-12","11:09:40","/js/screen.js","5189","1","user1"
"2012-9-12","11:09:40","/js/screen.js","5107","1","user1" "2012-9-12","11:09:40","/js/Validator.js","5189","2","user1"
"2012-9-12","11:09:40","/js/Validator.js","5107","2","user1"
"2012-9-12","11:09:40","/js/ValidatorMask.js","5189","1","user1"
"2012-9-12","11:09:40","/js/ValidatorMask.js","5107","1","user1"
"2012-9-12","11:09:40","/js/perform.js","5189","5","user1"
"2012-9-12","11:09:40","/js/test.js","5107","2","user1"
"2012-9-12","11:09:44","/common/WindowOpenAction.do","5091","2","user1"
"2012-9-12","11:09:44","/test2.do","5300","23","user1"
"2012-9-12","11:09:45","/test3.do","3106","26","user2"
"2012-9-12","11:09:45","/common/WindowOpenAction.do","5091","3","user1"
"2012-9-12","11:09:45","/test2.do","5299","47","user1"
"2012-9-12","11:09:46","/test4.do","3106","109","user2"
"2012-9-12","11:09:49","/test5.do","5315","340","user1"
"2012-9-12","11:09:51","/test2.do","5324","2571","user1"
"2012-9-12","11:09:53","/test6.do","5674","80","user1"
|
ここで注意が必要なのは、日付と時間のフォーマットです。下記のようにあらかじめ整形しておきます。
日付フォーマット、"YYYY-MM-DD"時間フォーマット、"HH:MM:SS"
また、特にWindowsで作成する場合には、改行コードにも気をつけてください。改行コードは、CR+LFと、LFのときでMySQLへロードするときのオプションが異なります。どちらで作成したか覚えておいてください。
（普通にWindowsのテキストエディタで作成すればCR+LF、cygwinのシェルスクリプトなどで整形して作成した場合には、LFになっています）
テーブル作成は、mysqlのコマンドラインで作成します。
２）MySQLへのテーブル作成
まずはログオン。
| C:\xampp\mysql\bin\ C:\xampp\mysql\bin>mysql -u root mysql C:\xampp\mysql\bin>mysql -u root -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.8 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> |
次に、データベースを作成します。
access_log という名前のデータベースを作成します。
create database : データベースの作成
show databases : システムに登録されているデータベースの一覧を確認できます。
| mysql> create database access_log; Query OK, 1 row affected (0.03 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | access_log | | bugtracker | | cdcol | | mysql | | performance_schema | | phpmyadmin | | test | | webauth | +--------------------+ 9 rows in set (0.19 sec) |
use データベース名 ：使用するデータベースを切り替えます。
create table ：log_data という名前でテーブルを作成します。
テーブル内のカラムはそれぞれ以下のような属性とカラム名で作成します。
| mysql> use access_log; Database changed mysql> create table log_data -> ( log_date date, -> log_time time, -> target varchar(255), -> transfer int, -> response int, -> user varchar(20) -> ); Query OK, 0 rows affected (0.14 sec) |
３） MySQLデータベースへのCSVファイルのロード
あらかじめ作成しておいたCSVファイルを作成したテーブルへ読み込みます。
load data infile
このコマンドのオプションはたくさんありますが、今回はカラムの区切り文字、データのくくり文字、改行コードを指定します。（自分はデータをcygwin上のシェルスクリプトで作成したので、\nですが、Windowsのメモ帳などのテキストエディタで作成したのであれば、\r\nを指定します。）
|
mysql> load data infile "c:\\tmp\\log\\test_data.txt" into table
-> log_data fields terminated by ',' enclosed by '"' lines
-> terminated by "\n" ; Query OK, 3293 rows affected (0.16 sec) Records: 3293 Deleted: 0 Skipped: 0 Warnings: 0 mysql> mysql> select * from log_data where log_time between "00:00:00" and "01:00:00"; +------------+----------+--------+----------+----------+------+ | log_date | log_time | target | transfer | response | user | +------------+----------+--------+----------+----------+------+ | 2012-09-12 | 00:01:39 | / | 212 | 8 | - | | 2012-09-12 | 00:01:39 | / | 212 | 9 | - | | 2012-09-12 | 00:02:23 | / | 212 | 2 | - | | 2012-09-12 | 00:02:49 | / | 210 | 2 | - | | 2012-09-12 | 00:03:18 | / | 214 | 7 | - | |
これでロード成功。
| mysql> select min(response),avg(response),max(response) from log_data; +---------------+---------------+---------------+ | min(response) | avg(response) | max(response) | +---------------+---------------+---------------+ | -1 | 248.6723 | 24896 | +---------------+---------------+---------------+ 1 row in set (0.00 sec) |
