Infobrightお試しメモ - つれづれ食道楽

現在MySQLに存在するテーブルをInfobrightにimport(LOAD)する．

Windows版DL＆インストール．
user作成．
database作成．
user権限追加
character set設定
create table

AUTO_INCREMENTとかサポートしてない属性削除．
ENGINE=BRIGHTHOUSEに変更で↓．

CREATE TABLE ym(
id int(10),
b_no varchar(45),
b_name varchar(245),
price double,
cost double,
kikan varchar(45),
category varchar(45),
d_flg int(1),
updatetime datetime
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

MySQL：csvでexport

mysql> select * from ym into outfile "e:/work/dbcsv/ym2.csv" fields terminated by ',' enclosed by '"';
Query OK, 141 rows affected (0.01 sec)
mysql> select * from ym_id into outfile "e:/work/dbcsv/ym_id2.csv" fields terminated by ',' enclosed by '"';
Query OK, 5116 rows affected (0.03 sec)

.csvファイルをエディタ（秀丸とか）で開いて編集．
エンコード：UTF-8
自動(改行=CR+LF)
でSave．
一番最後は要改行．改行なしの[EOF]はNG.

csvがNGだと

ERROR 2 (HY000): Wrong data or column definition. Row: 1, field: 12.

↑こんなエラーがでる

LOAD DATA

mysql> LOAD DATA INFILE 'c:/work/ym2.csv' INTO TABLE ym FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 141 rows affected (0.81 sec)
Records: 141 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA INFILE 'c:/work/ym_id2.csv' INTO TABLE ym_id FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 5116 rows affected (0.86 sec)
Records: 5116 Deleted: 0 Skipped: 0 Warnings: 0