relation does not exist loading .tbl tables

Nicola_Cambara

Hi,
I've created schema and tables, then i must load .tbl data in these tables but I have the error relation does not exist! what's wrong?
dbvertica=> \set t_dbvertica 'dbvertica'
dbvertica=> \set input_file '''':t_dbvertica'/home/nicola/Scrivania/part.tbl'''
dbvertica=> COPY part FROM :input_file DELIMITER '|' NULL '' DIRECT;
ERROR 4566:  Relation "part" does not exist

I've created ssb schema following ssb column definition in appendix a(last 2 pages)
http://www.cs.umb.edu/~xuedchen/research/publications/StarSchemaB.PDF

Abhishek_Rana

Hi,

Can you check with "COPY schema_name.table_name FROM" syntax for COPY command.

Regards'

Abhishek

Vivek_V__Kumar

Please provide <schema_name.table_name> in the copy command.

Nicola_Cambara

ok, now I've got this:

dbvertica=> COPY dbvertica.part FROM :input_file DELIMITER '|' NULL '' DIRECT;
ERROR 2886:  Could not open file [dbvertica/home/nicola/Scrivania/part.tbl] for reading; file or directory doesn't exist
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file

I think the problem is the directory because i'm newbie of vertica and probably i failed setting t_dbvertica!
how I should set it?

Abhishek_Rana

Hi,

If your file is on path : /home/nicola/Scrivania/part.tbl

just \set input_file '/home/nicola/Scrivania/part.tbl'

Then use your copy command:

COPY dbvertica.part FROM :input_file DELIMITER '|' DIRECT;

Regards'

Abhishek

Nicola_Cambara

ok I've done this but it still not work:

dbvertica=> \set input_file '/home/nicola/Scrivania/part.tbl'
dbvertica=> COPY dbvertica.part FROM :input_file DELIMITER '|' NULL '' DIRECT;
ERROR 4856:  Syntax error at or near "/" at character 26
LINE 1: COPY dbvertica.part FROM /home/nicola/Scrivania/part.tbl DEL..

Abhishek_Rana

Hi,

IT is giving error  as quotes are not being put in copy syntax - as inherited from :input_file

Do it like this:

dbadmin=> \set input_file '\'/home/nicola/Scrivania/part.tbl\''

dbadmin=> COPY dbvertica.part FROM :input_file DELIMITER '|' NULL '' DIRECT;


I hope this should work for you.

Regards'

Abhishek

Nicola_Cambara

unfortunately it doesn't work


I've got this error:

dbvertica=>  \set input_file '\'/home/nicola/Scrivania/part.tbl\''
dbvertica=> COPY dbvertica.part FROM :input_file DELIMITER '|' NULL '' DIRECT;
ERROR 2886:  Could not open file [/home/nicola/Scrivania/part.tbl] for reading; Permiss denied
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file

Abhishek_Rana

Hi,

Check permissions of the file you are accessing. It should have read permission for user dbvertica.

Regards'

Abhishek

Nicola_Cambara

I've set permissions for reading and writing for all users but I've got the same error :S

Vivek_V__Kumar

Hi, 

What is the rror message that you see.

Can you do vi /home/nicola/Scrivania/part.tbl

Nicola_Cambara

whit vi this is the error:

dbvertica=>  \set input_file '\'vi /home/nicola/Scrivania/part.tbl\''
dbvertica=> COPY dbvertica.part FROM :input_file DELIMITER '|' NULL '' DIRECT;
ERROR 2886:  Could not open file [vi /home/nicola/Scrivania/part.tbl] for reading; File o directory doesn't exist
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file
dbvertica=>

Vivek_V__Kumar

Hi,

Please exit from database with
dbvertica=> \q


Then execute below command from linux

 vi /home/nicola/Scrivania/part.tbl

Nicola_Cambara

In this way it opens the file!

Vivek_V__Kumar

Ok.

Now please execute below command after connecting to database.

dbvertica=> COPY dbvertica.part FROM '/home/nicola/Scrivania/part.tbl' DELIMITER '|' NULL ' ' DIRECT;

Nicola_Cambara

Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbvertica=> COPY dbvertica.part FROM '/home/nicola/Scrivania/part.tbl' DELIMITER '|' NULL ' ' DIRECT;
ERROR 2886:  Could not open file [/home/nicola/Scrivania/part.tbl] for reading; Permiss denied
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file

Vivek_V__Kumar

Please go to this directory "/home/nicola/Scrivania" and do ls -lrt part.tbl and provide us with the output.

Nicola_Cambara

this is the output:

[nicola@localhost Scrivania]$ ls -lrt part.tbl
-rwxrwxrwx. 1 dbadmin dbadmin 17137664 30 dic 12.51 part.tbl

Vivek_V__Kumar

Please login as dbadmin
su dbadmin

Then execute below comand:

vsql -c "COPY dbvertica.part FROM '/home/nicola/Scrivania/part.tbl' DELIMITER '|' NULL ' ' DIRECT;"

Nicola_Cambara

this is the error:

[nicola@localhost Scrivania]$ su - dbadmin
Password:
[dbadmin@localhost ~]$ vsql -c "COPY dbvertica.part FROM '/home/nicola/Scrivania/part.tbl' DELIMITER '|' NULL ' ' DIRECT;"
ERROR 2886:  Could not open file [/home/nicola/Scrivania/part.tbl] for reading; Permiss denied
HINT:  Make sure the file is available on the specified node.  If using ON ANY NODE, it must be available at this path on all nodes in the cluster since any node could load the file
[dbadmin@localhost ~]$

Nicola_Cambara

It still give error...Can someone help me?

[Deleted User]

Hi Nicola,

Could you try one more thing?  Try "su dbadmin"; then "ls -l /home/nicola/Scrivania/part.tbl".

(If that gives "Permission Denied", then either move the file or change the permissions on its parent directories until the command works.  We can help with that if you're not sure how.)

Thanks,
Adam

Nicola_Cambara

I have access denied, I need help because I'm newbie also for Fedora, I've installed it only for Vertica

Thanks

Abhishek_Rana

Hi,


It certainly is a permission issue.

I would like you to put file "part.tbl" in "/home/dbadmin" directory.

Steps: 

1.

cd /home/nicola/Scrivania/

cp part.tbl /home/dbadmin

2.

su - dbadmin

3.

dbadmin=> COPY dbvertica.part FROM '/home/dbadmin/part.tbl' delimiter '|' DIRECT; 


Regards'

Abhishek

Nicola_Cambara

when I type cp part.tbl /home/dbadmin I get this error:

cp: cannot execute stat of"/home/dbadmin/part.tbl": Permiss denied