COPY FROM LOCAL DOESN'T WORK

kfruchtman

Hi!
I am tryin to insert to my table from a file on my computer to my remote node.
COPY mytable FROM LOCAL 'C:\disk1.txt' WITH DELIMITER E'\t';
with no success.
On MC I get the error:[Vertica]VJDBC File-based COPY LOCAL statements cannot be executed because they are disabled.
? where do I enable it?
and when I run the command via vsql through my remote terminal I get:
Could not open file [C:\disk1.txt] for reading (in function statFiles() at Bulkload.cpp:338)

All I am trying to do i copy from a local file on my windows computer to the remote node.
any ideas?
Thanks in advance

PavanVejju

This is copy from source table to destination table.**
./vsql -U dbadmin -w password -h sourceHost -d vertica -At -c "SELECT * from schema.table" \
| ./vsql -U dbadmin -w password -d vertica -c "COPY schema.table FROM STDIN DELIMITER '|';"

kfruchtman

Thanks for your answer but I believe that this doesn't answer my question since I am trying to copy (insert data ) from a source file on my windows to a destination remote host of vertica node and not from a source node to a destination node.I cannot use the above command because when I connect to the destination db I need the source db to be down and I cannot run those commands one after the other.

marcothesane

I think it helps, here, to have a look at the virtual "geography" you're fased with when connecting to Vertica via Management Console.
It is:
[your Windows system] -> connected_as_mc_user -> [ Mgmt Console Service] -> connected as dbadmin Vertica user ->[Vertica]
COPY FROM LOCAL / COPY FROM STDIN reads the file from the current directory on the system from where the Vertica connection is established. This would be, in the case you have here, on the middle tier on which Management Console resides. Not your Windows sytstem. If you were allowed to put a file as a private user on the server that houses the Management Console, you would probably have to answer a few questions on your security setup ...

So if you want to COPY FROM LOCAL / COPY FROM STDIN, from your Windows client system, use vsql, or tools like Dbeaver or DbVisualizer to fire your COPY command.

kfruchtman

Thanks!
Security is not an issue here since it is a setup only made for me and my tests and not production.
So how can I use the vsql locally?now I use it on a remote shell connecting to my vertica.I tried to use the copy from local on the vsql on the on the remote server but it gave me "Could not open file [C:\disk1.txt] for reading (in function statFiles() at Bulkload.cpp:338)" .
Can I use the vsql locally? ...and if yes how can I install only the vsql on my windows ? is it possible?

SergeB

You can download vsql here: https://www.vertica.com/download/vertica/client-drivers/

marcothesane

Go and get your Windows client package, here: https://www.vertica.com/download/vertica/client-drivers/
Then, install it to your Windows platform.
Then, search for [Environment Variables] in [Type here to search], at the launch bar in Windows, next to the Windows icon itself, and update your personal Environment variables:
Change your %path% to contain what it contains already, if any, and add: ;C:\Program Files\Vertica Systems\VSQL64 to it
then add:
VSQL_USER [the Vertica user you log in as]
VSQL_PASSWORD [the password of that user]
VSQL_HOST [the ip address of your first Vertica node]
VSQL_DATABASE [the name of your Vertica database]
Once all that is set, you can open any Windows command window, navigate , with cd, to any directory, and call vsql without the directory it sits in, just like in your Vertica node under Unix.

kfruchtman

I can't thank you enough :-)