Dimensions with sequences/auto-increment

Comments

• marcothesane - Select Field - Administrator

  June 2017

  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
  Marco

  1
• Jim_Knicely - Select Field - Administrator

  June 2017

  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;
  CREATE SEQUENCE
  
  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 ;
  CREATE TABLE
  
  dbadmin=> INSERT INTO dim_cust (cust_no, cust_name, cust_dob) VALUES (1, 'VERTICA', sysdate);
   OUTPUT
  --------
        1
  (1 row)
  
  dbadmin=> SELECT * FROM dim_cust;
   cust_key | cust_no | cust_name |  cust_dob
  ----------+---------+-----------+------------
          1 |       1 | VERTICA   | 2017-06-21
  (1 row)
  

  1