Dimensions with sequences/auto-increment

My dimension table surrogate keys are integers. When loading the dimension table, I check the table maximum and add 1 to it in the next row. By any chance, is that a problem? Because, the lookup and data loading with Pentaho is like 30ish rows per second.



  • marcothesanemarcothesane - Select Field - Administrator

    Let Vertica do the job. Especially, if you insert row by row, it's like trying to feed an elephant with a teaspoon. You need shovels or frontloaders for that job. That means, many, many, many rows in one SQL call.

    To be sure you get new rows inserted, define your table like so:
    CREATE TABLE dim_cust (
    cust_key IDENTITY NOT NULL
    , cust_no INT NOT NULL
    , cust_name VARCHAR(32)
    , cust_dob DATE
    , PRIMARY KEY (cust_key)
    ) UNSEGMENTED ALL NODES ; -- if it's a smaller table

    An IDENTITY key must not be referenced in an INSERT or UPDATE statement, so you would only reference cust_no, cust_name and cust_dob in your insert job by Pentaho. cust_key gets populated by Vertica.

    And make sure you load thousands of rows in one SQL call.

    Good luck

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    Marco is right, let Vertica do the work :)

    Fyi, in addition to IDENTITY columns, you can also use a sequence to generate a unique key value for a table column as a default...

    dbadmin=> CREATE SEQUENCE dim_cust_seq;
    dbadmin=> CREATE TABLE dim_cust ( cust_key INT DEFAULT dim_cust_seq.NEXTVAL, cust_no INT NOT NULL, cust_name VARCHAR(32), cust_dob DATE, PRIMARY KEY (cust_key)) UNSEGMENTED ALL NODES ;
    dbadmin=> INSERT INTO dim_cust (cust_no, cust_name, cust_dob) VALUES (1, 'VERTICA', sysdate);
    (1 row)
    dbadmin=> SELECT * FROM dim_cust;
     cust_key | cust_no | cust_name |  cust_dob
            1 |       1 | VERTICA   | 2017-06-21
    (1 row)

Leave a Comment

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