Vertica 6.0 and COPY LOCAL issue

Moderator: NorbertKrupa

caprile
Newbie
Newbie
Posts: 13
Joined: Tue May 29, 2012 1:24 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by caprile » Wed Jun 13, 2012 9:45 am

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

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by id10t » Wed Jun 13, 2012 10:18 am

:shock: :shock: :shock:
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();
You are opening stream and immediately close, so how Java should write rejected rows? out.close() should by in catch and/or finally blocks

PS
Some tips:
  • 1. use interfaces i.e:

    Code: Select all

    OutputStream out = new FileOutputStream(...);...
    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.

caprile
Newbie
Newbie
Posts: 13
Joined: Tue May 29, 2012 1:24 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by caprile » Wed Jun 13, 2012 11:18 am

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

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by id10t » Wed Jun 13, 2012 12:44 pm

>> 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.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by id10t » Wed Jun 13, 2012 3:13 pm

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.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by id10t » Thu Jun 14, 2012 6:27 am

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.

caprile
Newbie
Newbie
Posts: 13
Joined: Tue May 29, 2012 1:24 pm

Re: Vertica 6.0 and COPY LOCAL issue

Post by caprile » Thu Jun 14, 2012 6:52 am

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.

Post Reply

Return to “JDBC”