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....
when using Pipe to bulk load data to Vertica query execution fails with SQL return code SQL_NO_DATA
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
PS:
The path definitions are not wrong they work just fine
+1Does it forum parser removed escaping?
+2Hi 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
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
+1Hi Adam!
Topic starter didn't mentioned it, but I think he is doing it with ODBC/ADO (or JDBC) - SQL_NO_DATA
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.hAlso I can emulate this issue on python with COPY STDIN(with incorrect implementation), so I think here can be 3 options:
#define SQL_NO_DATA 100
- 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.
- Bug in Ayan's client, and Im pretty sure, here is root of a problem, but he didn't provided a lot of info.
- May be Vertica have no some grants on locations or user isn't granted to do a COPY.
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?
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?
+2Hi 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
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
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
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
+2Hi 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
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
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
