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

Question

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 · Answer

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)

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register: