Julie,
I believe that only the super user in Vertica can copy directly from a file. Normal users can still use the
COPY command, but they have to do so via the STDIN (Standard Input).
Here is an example:
There is a user in Vertica named
jknicely who has all privileges on a schema named
jknicely. In that schema is a table name
copy_test.
Code: Select all
jknicely=> \d jknicely.copy_test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
----------+-----------+--------+--------------+------+---------+----------+-------------+-------------
jknicely | copy_test | col1 | int | 8 | | f | f |
jknicely | copy_test | col2 | varchar(100) | 100 | | f | f |
jknicely | copy_test | col3 | numeric(5,2) | 8 | | f | f |
(3 rows)
In the user
jknicely’s home directory there is a text file named
jim.txt.
Code: Select all
[jknicely@verticatst01 ~]$ pwd
/home/jknicely
[jknicely@verticatst01 ~]$ cat jim.txt
1|Jim Knicely|100.23
2|Jingxuan Li|101.21
3|Philip Marlow|203.23
4|Peter Griffen|234.11
The user wants to copy its contents into the table
jknicely.copy_test. He will have to pipe the contents of the file into the STDIN of the
COPY command something like this:
Code: Select all
[jknicely@verticatst01 ~]$ cat /home/jknicely/jim.txt | /opt/vertica/bin/vsql -U jknicely -w hamster87 -c "copy jknicely.copy_test from stdin direct no escape null as '\N' exceptions '/home/jknicely/jim.err';"
Then we can check that it worked by querying the table:
Code: Select all
[jknicely@verticatst01 ~]$ /opt/vertica/bin/vsql -U jknicely -w hamster87
Welcome to vsql, the Vertica Analytic Database v5.1.1-0 interactive terminal.
Type: \h for help with SQL commands
\? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
jknicely=> select * from jknicely.copy_test;
col1 | col2 | col3
------+---------------+--------
1 | Jim Knicely | 100.23
2 | Jingxuan Li | 101.21
3 | Philip Marlow | 203.23
4 | Peter Griffen | 234.11
(4 rows)
I hope this helps!