The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

relation does not exist loading .tbl tables

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








Comments

  • Hi,

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

    Regards'

    Abhishek
  • Please provide <schema_name.table_name> in the copy command.
  • 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?


  • 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
  • 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..

  • 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
  • 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


  • Hi,

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

    Regards'

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

    What is the rror message that you see.

    Can you do vi /home/nicola/Scrivania/part.tbl
  • 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=>


  • Hi,

    Please exit from database with
    dbvertica=> \q


    Then execute below command from linux

     vi /home/nicola/Scrivania/part.tbl

  • In this way it opens the file!
  • Ok.

    Now please execute below command after connecting to database.

    dbvertica=> COPY dbvertica.part FROM '/home/nicola/Scrivania/part.tbl' DELIMITER '|' NULL ' ' DIRECT;
  • 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


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

    [[email protected] Scrivania]$ ls -lrt part.tbl
    -rwxrwxrwx. 1 dbadmin dbadmin 17137664 30 dic 12.51 part.tbl

  • Please login as dbadmin
    su dbadmin

    Then execute below comand:

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


  • this is the error:

    [[email protected] Scrivania]$ su - dbadmin
    Password:
    [[email protected] ~]$ 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
    [[email protected] ~]$



  • It still give error...Can someone help me?
  • 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
  • I have access denied, I need help because I'm newbie also for Fedora, I've installed it only for Vertica

    Thanks
  • 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
  • when I type cp part.tbl /home/dbadmin I get this error:

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


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.