infobright 导入 导出

• 这句必须先执行：

SET SQL_LOG_BIN = 0;

全局修改：使用global修饰符（set global sql_log_bin=0) 版本低得无法用。

• 从服务器导入：这里导出只能导出到服务器上，不会在本地。如果数据库装在linux服务器，输出文件名目录要用"/tmp/xxx.csv", 然后去服务器对应目录找即可。

存在了数据库服务器上：

select * from channel_profile limit 13
into outfile '/tmp/channelProfile.zhibin'
CHARACTER SET utf8
fields terminated by ',' optionally enclosed by '"'
escaped by '"' lines terminated by '\n'

select * into outfile 'mydata.cvs' fields terminated by ',' from table1;

 

LOAD DATA INFILE '/tmp/channelProfile.zhibin' ignore into table channel_profile
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

load data LOCAL infile 'mydata.cvs' into table table2 fields terminated by ',';

 加上LOCAL, 会从本地导入。否则是数据库服务器上的路径。

• 默认导入导出的目录

是服务器上的：/usr/local/infobright-4.0.7-x86_64/data/statistics/mydata.cvs

其中statistics是数据库名称。

如果是mysql，导出目录是/var/lib/mysql/statisticsystem/+ mydata.cvs。任何你写的windows目录都会变成文件名。

 

• 可以从本地导入到远程：

LOAD DATA LOCAL INFILE 'D:/play_20170110.csv' into table log_play_2017_test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
;

select count(*) from log_play_2017_test
where request_time >=unix_timestamp("2017-01-10")
and request_time < unix_timestamp("2017-01-11")
;

当写在JDBC时，必须用\\ 替换上面的\, 因为是写在java的String字符串中，如\n需要替换成 \\n。

 

    @PersistenceContext(unitName = "statLog")
    private EntityManager em;

    @Transactional
    public boolean importtest() {
         em.createNativeQuery(" SET SQL_LOG_BIN = 0 ").executeUpdate();
        String sql = " LOAD DATA    INFILE  '/tmp/mydata.csv' ignore  into table test_table"
                + " CHARACTER SET utf8  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' ";
         ;
         
         logger.info(sql);
        int num=0;
        num= emStatLog.createNativeQuery(sql).executeUpdate();

        output("import result: the number of lines is " + num);
        output("Import success!");
        return true;
    }

 

jpa不能执行 select * from xxx into outfile test.csv, 只能用jdbc的方式。从hibernate取到jdbc session示例：

    @PersistenceContext(unitName = "primary")
    private EntityManager em;
    public void export()    {
        //到处profile
//        filepath = filepath.replace('\\', '/');
//escaped by '\"'  这个会导致字段是null时，输出 ，"N,"xxxx" 到处是坑啊
        String sql="select * from channel_profile  into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '\"'  lines terminated by '\\n'";

        logger.info(sql);

        Session session = em.unwrap(org.hibernate.Session.class);
        session.doWork(
                new Work() {
                    @Override
                    public void execute(Connection con) throws SQLException {
                        try ( PreparedStatement ps = con.prepareStatement( 
                                sql) ) {
                            ps.execute();
                          }
                          catch (Exception e) {
                            e.printStackTrace();
                          }
                    }
                }
                );
    }

pom.xml添加：

<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.1.4.Final</version>
<scope>provided</scope> <!--  只是在编译时候用hibernate jar包。执行时候不要hibernate jar包。 -->
</dependency>