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

khaja

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

Jim_Knicely

Hi,

This similar example works okay:

 [dbadmin@s18384357 ~]$ cat gzip.txt
 jim|knicely|pittsburgh|pa
 jack|demo|pittsburgh|pa
 dave|darcey|new york|ny

 [dbadmin@s18384357 ~]$ gzip gzip.txt
 gzip.txt.gz

 [dbadmin@s18384357 ~]$ 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

 [dbadmin@s18384357 ~]$ 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!

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