MYSQL LOAD DATA INFILE命令可以把csv平面文件中的数据导入到数据库中。
linux下:
2.
LOAD DATA INFILE '/opt/hadoop/hive/hive/hive_testdata/t_hft_20141121.csv' INTO TABLE t_hft FIELDS TERMINATED BY ','
ENCLOSED BY '"';
--CHARACTER SET :mysql字符集,一定要加上,免去乱码麻烦
--INTO TABLE :导入到哪个表
--FIELDS TERMINATED BY :以什么作为分割符
-- ENCLOSED BY :被什么包围
windows:
--LINES TERMINATED BY:这个与linux不同,以什么作为一行的结尾。
linux下:
- LOAD DATA INFILE '/home/test/dump/ip_location.csv'
- INTO TABLE ip_location
- CHARACTER SET utf8
- FIELDS TERMINATED BY ',' ENCLOSED BY '"';
2.
LOAD DATA INFILE '/opt/hadoop/hive/hive/hive_testdata/t_hft_20141121.csv' INTO TABLE t_hft FIELDS TERMINATED BY ','
ENCLOSED BY '"';
--INTO TABLE :导入到哪个表
--FIELDS TERMINATED BY :以什么作为分割符
-- ENCLOSED BY :被什么包围
MySQL中导出CSV格式数据的SQL语句样本如下:
- select * from test_info
- into outfile '/tmp/test.csv'
- fields terminated by ',' optionally enclosed by '"' escaped by '"'
- lines terminated by '\r\n';
- select * from test_info
- into outfile '/tmp/test.csv'
- fields terminated by ',' optionally enclosed by '"' escaped by '"'
- lines terminated by '\r\n';
MySQL中导入CSV格式数据的SQL语句样本如下:
- load data infile '/tmp/test.csv'
- into table test_info
- fields terminated by ',' optionally enclosed by '"' escaped by '"'
- lines terminated by '\r\n';
- load data infile '/tmp/test.csv'
- into table test_info
- fields terminated by ',' optionally enclosed by '"' escaped by '"'
- lines terminated by '\r\n';
里面最关键的部分就是格式参数
windows:
- LOAD DATA INFILE "d:/insert_data.csv"
- REPLACE INTO TABLE DEMO
- CHARACTER SET gb2312
- FIELDS TERMINATED BY "," ENCLOSED BY ""
- LINES TERMINATED BY "\r\n";
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26495863/viewspace-1340329/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26495863/viewspace-1340329/