when using Pipe to bulk load data to Vertica query execution fails with SQL return code SQL_NO_DATA

Ayan_Paul

Here is the copy command that is being executed:
COPY public.newTarget( NEWFIELD , NEWFIELD1 , NEWFIELD2 , NEWFIELD3 ) FROM LOCAL '\\.\pipe\newTarget_public_0_pipe_0' DELIMITER E'|' TRAILING NULLCOLS NULL E'' NO ESCAPE ENCLOSED BY E'"' RECORD TERMINATOR E'\r\n' SKIP 0 REJECTMAX 1 EXCEPTIONS 'C:\Temp\/Exceptions_newTarget_public_0_503000.dat' REJECTED DATA 'C:\Temp\/RejectedData_newTarget_public_0_503000.dat' AUTO STREAM NAME 'newTarget_public_0_503000' NO COMMIT;

Any idea on what issue this maybe....

Daniel_Leybovic

Hi!

Vertica COPY require full (absolute) path, not relative
This is a relative path:

\\.\pipe\newTarget_public_0_pipe_0

dot - means current directory(CD). And what is CD now? and yesterday?

http://en.wikipedia.org/wiki/Path_%28computing%29
http://www.computerhope.com/jargon/a/absopath.htm
http://www.computerhope.com/jargon/r/relapath.htm


PS
All your path definitions are wrong. Are you amateur? For what do you need Vertica?

Ayan_Paul

Well for most Windows Pipe names \\.\pipe\PipeName works however in this case the issue might be with the dot I will replace it with the server name or the IP address and try it out. I am assuming there are no other issues with the command I have pasted.

PS:
The path definitions are not wrong they work just fine

Daniel_Leybovic

Does it forum parser removed escaping?

[Deleted User]

Hi Ayan,

Hm...  I'm not personally too familiar with Windows pipes, but your syntax does look correct to me.  (There's an extra forward slash in the rejected-data paths, but I wouldn't immediately expect that to cause this particular issue.)

What version of the "vsql" client are you using?  vsql added support for Windows pipes relatively recently.

Also, I assume that you have verified that the other side of the pipe (the application providing the data) is set up properly?  That's the most common first cause of pipe-related issues on Linux; probably worth double-checking on Windows as well, if you haven't already.

Adam

Daniel_Leybovic

Hi Adam!

Topic starter didn't mentioned it, but I think he is doing it with ODBC/ADO (or JDBC) - SQL_NO_DATA 

    daniel@synapse ~ $ grep SQL_NO_DATA /usr/include/sql.h
    #define SQL_NO_DATA              100

Also I can emulate this issue on python with COPY STDIN(with incorrect implementation), so I think here can be 3 options:

1. Ayan's client is correct: so ODBC or whatever can't read from Widows pipes (they are little differs from *nix pipes). I can't check it, I have no Windows.
   
2. Bug in Ayan's client, and Im pretty sure, here is root of a problem, but he didn't provided a lot of info.
   
3. May be Vertica have no some grants on locations or user isn't granted to do a COPY.

Ayan_Paul

Hi Adam,

I am not using vsql to pipe the data into vertica but am directly using Windows pipes to write data into Vertica. From the application side I could create the pipe correctly so at the time I am firing the Copy LOCAL statement to the Vertica DB, the pipe is existent. The error I am getting was while opening the pipe. I have created the pipe in blocking mode so that until there s a reader to read the data my application wont continue. When I try to open the pipe Windows last Error message tells me "The pipe is being closed" .
The pipe paths are correct as I can see empty files being generated at the desired location.


Does copy local directly support the pipe mechanism over Windows platforms or do i need to use vsql?

[Deleted User]

Hi Ayan,

I'm afraid I'm a little confused by your question -- COPY LOCAL and vsql are unrelated; you can use COPY LOCAL with vsql, you can load data without COPY LOCAL and without using vsql.  Mix'n'match however you would like.

It sounds like Daniel correctly inferred that you are specifically trying to use ODBC?  In that case, let me amend my question -- which version of the client drivers (ODBC in this case; or JDBC, or vsql, or whatever your application uses) are you using?

I don't know offhand which versions of which client drivers support Windows pipes, but can check on that.  I know it's a relatively recent addition.

Adam

Ayan_Paul

Hi Adam,

Ok let me explain it this way. I want to use a Windows Pipe to load data into a Vertica DB since it is the fastest way to load the data. I know I have the option of staging the data to a local file and initiate the COPY LOCAL command but I don't want to wait because the data size may be substantial hence I chose the option of pipe. 
Yes I am using Vertica client drivers of the version 7.00 DLL: VERTICA_7.0_ODBC_3.5_64.DLL

If this version of client drivers dont support the Windows pipe loading mechanism, can u tell me which version may support?
I want to simply pass a windows pipe path in the 'pathtodata' part of the query and write data to that pipe to load a table.
If support of Windows pipes are not directly available then can you suggest what other option can I opt other than staging a local file?

Ayan

[Deleted User]

Hi Ayan,

Hm...  That version of the driver should support Windows pipes.

We do have customers using Windows pipes in the way that you describe.  So my first guess would be that the issue is environmental.  Let me know if you are able to confirm that the pipe is working correctly; that the application that you are reading data from is correctly sending data to the pipe.  (Vertica can't do much with its end of the pipe if there's nothing on the other end...)

If you're still stuck, and if no one else here has further thoughts (and if you have Vertica Enterprise Edition), I'd recommend opening a support case; asking for help to track down your problem.

Adam