We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


MOVE_PARTITIONS_TO_TABLE fails with ERROR: 5659 — Vertica Forum

MOVE_PARTITIONS_TO_TABLE fails with ERROR: 5659

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?

Comments

  • Prasanta_PalPrasanta_Pal - Select Field - Employee
    Check

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

    the output from both should match;


  • 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_PalPrasanta_Pal - Select Field - Employee
    Check the ORDER BY Clause which differs
  • 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?
  • 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_PalPrasanta_Pal - Select Field - Employee
    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.





  • Thanks, understood.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file