Unable to load the data using COPY STREAM when table has multiple partitions
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
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!