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

Unable to load the data using COPY STREAM when table has multiple partitions

edited January 20 in General Discussion

Hi Team,

I am using vertica as my database, I have created a table with multiple partitions using the below query in vertica.

CREATE TABLE employee ( EmpNameFirstName VARCHAR NOT NULL, EmpNameLastName VARCHAR NOT NULL, EmpCity VARCHAR NOT NULL, EmpState VARCHAR NOT NULL) PARTITION BY HASH (EmpCity, EmpState);

Now i am trying to load the data using my program which will create the below copy query to load the data using stream.

COPY employee FROM local STDIN GZIP DELIMITER '|' DIRECT STREAM NAME 'employee -224-0-20190120170125394' returnrejected NO COMMIT

I am unable to see the data in the employee table.
But when i have created the table with one partitions and trying to load the data with same program then data is populating in the table and i can see the data in the table.

Request your expertise to solve this issue.

Thanks,
Khaja

Answers

  • Jim_KnicelyJim_Knicely Administrator

    Hi,

    This similar example works okay:

     [[email protected] ~]$ cat gzip.txt
     jim|knicely|pittsburgh|pa
     jack|demo|pittsburgh|pa
     dave|darcey|new york|ny
    
     [[email protected] ~]$ gzip gzip.txt
     gzip.txt.gz
    
     [[email protected] ~]$ vsql -c "CREATE TABLE employee2 ( EmpNameFirstName VARCHAR NOT NULL, EmpNameLastName VARCHAR NOT NULL, EmpCity VARCHAR NOT NULL, EmpState VARCHAR NOT NULL) PARTITION BY HASH (EmpCity, EmpState);"
     CREATE TABLE
    
     [[email protected] ~]$ vsql -c "COPY employee2 FROM local STDIN GZIP DELIMITER '|' DIRECT STREAM NAME 'employee -224-0-20190120170125394' returnrejected NO COMMIT;" < /home/dbadmin/gzip.txt.gz
      Rows Loaded
     -------------
                3
     (1 row)
    

    Three records were loaded.

    But note I used a COPY command with the NO COMMIT option. So the data is not there anymore!

    [[email protected] ~]$ vsql -c "SELECT * FROM employee2;"
     EmpNameFirstName | EmpNameLastName | EmpCity | EmpState
    ------------------+-----------------+---------+----------
    (0 rows)
    

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.