I do not use a Servlet but a simple java class.
The main method where the COPY LOCAL statement is executed :
private void loadData(java.sql.Connection connection) throws Exception {
//create the empty file
FileOutputStream out = new FileOutputStream(new File("/var/VERTICA_FLOW/rejected/VERTICA_FLOW8.txt"));
out.close();
String sql = "COPY public.TEST_LOAD_TABLE (A, B, DATADATE FORMAT 'YYYYMMDDHH24MISS', N) FROM LOCAL '/var/work/VERTICA_FLOW8.txt' DELIMITER '|' TRAILING NULLCOLS REJECTED DATA '/var/VERTICA_FLOW/rejected/VERTICA_FLOW8.txt' DIRECT NO COMMIT;";
try {
System.out.println("Executing statement : " + sql);
connection.setAutoCommit(false);
Statement stmt = connection.createStatement();
stmt.execute(sql);
long insertedRow = stmt.getUpdateCount();
System.out.println("Inserted rows : " + insertedRow);
} catch (Exception ex) {
connection.rollback();
throw ex;
}
connection.commit();
}
where java.sql.Connection is the connection to the Vertiva database obtained by :
public Connection createConnection(String username, String password, String connectionUrl) throws SQLException {
return java.sql.DriverManager.getConnection(connectionUrl, username, password);
}
Thanks in advance
Vertica 6.0 and COPY LOCAL issue
Moderator: NorbertKrupa
Re: Vertica 6.0 and COPY LOCAL issue
Is it your "production" (final) code?
Code: Select all
private void loadData(java.sql.Connection connection) throws Exception {
//create the empty file
FileOutputStream out = new FileOutputStream(new File("/var/VERTICA_FLOW/rejected/VERTICA_FLOW8.txt"));
out.close();
PS
Some tips:
- 1. use interfaces i.e:
2. BufferedStreams can reduce requests of JVM to HDD, so where it possible use buffers! With dd utility you can find the best buffer for performance.
Code: Select all
OutputStream out = new FileOutputStream(...);...
Re: Vertica 6.0 and COPY LOCAL issue
It's only for test, for checking if JVM can write into rejected directory and after the out.close() the rejected file in on HDD into rejected directory.
I suppose that i don't need to open a OutputStream but Vertica have to do this for me and write the rejected record according to the COPY LOCAL statement part:
.....
REJECTED DATA '/var/VERTICA_FLOW/rejected/VERTICA_FLOW8.txt'........
.............
Have you check the behaviour of COPY LOCAL statement with REJECTED DATA at your PC?
Thanks
I suppose that i don't need to open a OutputStream but Vertica have to do this for me and write the rejected record according to the COPY LOCAL statement part:
.....
REJECTED DATA '/var/VERTICA_FLOW/rejected/VERTICA_FLOW8.txt'........
.............
Have you check the behaviour of COPY LOCAL statement with REJECTED DATA at your PC?
Thanks
Re: Vertica 6.0 and COPY LOCAL issue
>> Have you check the behaviour of COPY LOCAL statement with REJECTED DATA at your PC?
Still not. Have a work (as soon as possible i shall do it)
but don't wait for me specially - ask in my.vertica.com, may be it a new issue with JDBC in new drivers.
Still not. Have a work (as soon as possible i shall do it)
but don't wait for me specially - ask in my.vertica.com, may be it a new issue with JDBC in new drivers.
Re: Vertica 6.0 and COPY LOCAL issue
Hi caprile!
I tested COPY ... FROM LOCAL '/path' but all on local machine, i.e. DB and DATA SOURCE were on same machine and it's works properly - rejected data written, exceptions too.
Check your environment.
I tested COPY ... FROM LOCAL '/path' but all on local machine, i.e. DB and DATA SOURCE were on same machine and it's works properly - rejected data written, exceptions too.
Check your environment.
Re: Vertica 6.0 and COPY LOCAL issue
Ok... I tested COPY LOCAL via network - when DATA is not on executor node.
Work properly - no errors. I don't know why in MyVertica they wrote that this feature doesn't work as dbadmin.
PS: What OS are you working on? CentOS, RHEL? If so - install sudo, and make dbadmin as sudoer.
Work properly - no errors. I don't know why in MyVertica they wrote that this feature doesn't work as dbadmin.
PS: What OS are you working on? CentOS, RHEL? If so - install sudo, and make dbadmin as sudoer.
Re: Vertica 6.0 and COPY LOCAL issue
I wrote on myVertica forum,
http://my.vertica.com/forums/topic/vert ... cal-issue/
they wrote that COPY LOCAL write rejected file only with superuser and vertica engineering team is looking into it.
Anyway, my application is running on a different machine, the vertica database is running on a remote machine.
http://my.vertica.com/forums/topic/vert ... cal-issue/
they wrote that COPY LOCAL write rejected file only with superuser and vertica engineering team is looking into it.
Anyway, my application is running on a different machine, the vertica database is running on a remote machine.