MOVE_PARTITIONS_TO_TABLE fails with ERROR: 5659

Anna_Aronova

I have 2 tables identical in structure and partition key. When I run MOVE_PARTITIONS_TO_TABLE, it fails with an error: "ERROR 5659:  Source and target table does not match".
What should I check?

Prasanta_Pal

Check

select export_objects('','<table1>');
select export_objects('','<table2>');

the output from both should match;

Anna_Aronova

They match:

source table:

CREATE TABLE ali_part.file_metrics(    sprint_id numeric(38,0) NOT NULL,    build_id numeric(38,0) NOT NULL,    ts timestamp NOT NULL,    build_type varchar(80),    metric1 float,    metric2 float,    metric3 float,    metric4 float)PARTITION BY (file_metrics.sprint_id);


CREATE PROJECTION ali_part.file_metrics /*+createtype(L)*/( sprint_id, build_id, ts, build_type, metric1, metric2, metric3, metric4)AS SELECT file_metrics.sprint_id,        file_metrics.build_id,        file_metrics.ts,        file_metrics.build_type,        file_metrics.metric1,        file_metrics.metric2,        file_metrics.metric3,        file_metrics.metric4 FROM ali_part.file_metrics ORDER BY file_metrics.sprint_id,          file_metrics.build_id,          file_metrics.ts,          file_metrics.build_type,          file_metrics.metric1,          file_metrics.metric2,          file_metrics.metric3,          file_metrics.metric4SEGMENTED BY hash(file_metrics.ts, file_metrics.metric1, file_metrics.metric2, file_metrics.metric3, file_metrics.metric4, file_metrics.sprint_id, file_metrics.build_id, file_metrics.build_type) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

Target table:
CREATE TABLE ali_part.temp_metrics(    sprint_id numeric(38,0) NOT NULL,    build_id numeric(38,0) NOT NULL,    ts timestamp NOT NULL,    build_type varchar(80),    metric1 float,    metric2 float,    metric3 float,    metric4 float)PARTITION BY (temp_metrics.sprint_id);


CREATE PROJECTION ali_part.temp_metrics_v1 /*+createtype(L)*/( sprint_id, build_id, ts, build_type, metric1, metric2, metric3, metric4)AS SELECT temp_metrics.sprint_id,        temp_metrics.build_id,        temp_metrics.ts,        temp_metrics.build_type,        temp_metrics.metric1,        temp_metrics.metric2,        temp_metrics.metric3,        temp_metrics.metric4 FROM ali_part.temp_metrics ORDER BY temp_metrics.ts,          temp_metrics.build_type,          temp_metrics.metric1,          temp_metrics.metric2,          temp_metrics.metric3,          temp_metrics.metric4SEGMENTED BY hash(temp_metrics.ts, temp_metrics.metric1, temp_metrics.metric2, temp_metrics.metric3, temp_metrics.metric4, temp_metrics.sprint_id, temp_metrics.build_id, temp_metrics.build_type) ALL NODES KSAFE 1;

SELECT MARK_DESIGN_KSAFE(1);

Prasanta_Pal

Check the ORDER BY Clause which differs

vikas_Omer

I am getting a similar error where I am using the following 
CREATE TABLE x_temp LIKE y INCLUDING PROJECTIONS;
I insert some data into x_temp and then

SELECT MOVE_PARTITIONS_TO_TABLE ('x_temp', 20141119, 20141119, 'y');

It gives me -
an error occurred during query execution: ERROR:  Source and target table does not match

What should I do?

vikas_Omer

Above stated problem is solved because there was missing projection in y table while it is created by default for x_temp. I created projection for y and it worked.
Problem here is
CREATE TABLE x_temp LIKE y INCLUDING PROJECTIONS; 
- when there were no projections for y table then x_temp shouldn't have any projection as well. 

Is this how it is suppose to work?
Please let me know.

Prasanta_Pal

First,  your table 'y' had no projection
Then when you created x_temp table using "INCLUDING PROJECTIONS", x_temp had not projection created.

Now when you insert data into  x_temp, vertica creates super projection for this table automatically.

But till then  the table 'y' had not projection.

So the x_temp and y are not same.

It is expected behaviour.

Make sure that both tables are matching, by checking the design of table and projection of the table. You can use  select export_objects() command to review them.

vikas_Omer

Thanks, understood.