Importing data into an identity column

Moderator: NorbertKrupa

Post Reply
asterite
Newbie
Newbie
Posts: 5
Joined: Wed May 08, 2013 9:02 pm

Importing data into an identity column

Post by asterite » Thu May 09, 2013 1:54 pm

Hello all,

We decided using vertica instead of mysql for some of our tables. The problem is that the production application is already running and has some data, so we'd like to migrate the existing data to vertica. We need identity columns in the vertica tables, because they were identity columns in mysql, but we can't find a way to force values into identity columns.

We tried:
1. Inserting data into identity columns
2. Creating just integer columns for the "id" column, inserting data and then trying to change the column type to be identity, but couldn't find a way

How can I solve this problem?

Thanks,
Ary

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Importing data into an identity column

Post by JimKnicely » Fri May 10, 2013 3:57 am

Maybe you can try something like the following?
CopyIdentity.png
CopyIdentity.png (49.69 KiB) Viewed 12684 times
This is super simple example...
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

asterite
Newbie
Newbie
Posts: 5
Joined: Wed May 08, 2013 9:02 pm

Re: Importing data into an identity column

Post by asterite » Fri May 10, 2013 2:47 pm

Thanks for your reply.

In your example you are ignoring the id column that you have in jim.txt.

In our scenario, our entities might not have sequential ids because some might have been deleted. We need to import existing data with their ids (because they are related to other entities which in turn have ids that might not be in sequence).

I tried this:

test_db=> create table t1 (c1 identity, c2 varchar);
test_db=> \! cat /home/dbadmin/jim.txt
2|Jim
4|Brian
6|Patrick
8|Peng
test_db=> copy t1 (fc1 filler int, fc2 filler varchar, c2 as fc2) from '/home/dbadmin/jim.txt';
Rows Loaded
-------------
4
(1 row)

test_db=> select * from t1;
c1 | c2
----+---------
1 | Jim
2 | Brian
3 | Patrick
4 | Peng
(4 rows)

Is there a way to accomplish this, or will I need to remap all existing ids and their relations?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Importing data into an identity column

Post by JimKnicely » Fri May 10, 2013 3:36 pm

Hi,

I can't test this, but something like this might work for you...

Code: Select all

CREATE TABLE t1 (c1 IDENTITY, c2 VARCHAR);

CREATE TABLE t1_temp (c1 INT, c2 VARCHAR);

COPY t1_temp FROM '/home/dbadmin/jim.txt';

INSERT INTO t1 (c2)
  SELECT tab2.c2
    FROM (SELECT row_number() over () FROM tables CROSS JOIN columns) tab1
    LEFT JOIN (SELECT c1, c2 FROM t1_temp) tab2
      ON tab2.c1 = tab1.c1;

DELETE FROM t1 WHERE c2 IS NULL;

DROP TABLE t1_temp;
How many records are you loading? You may need to alter the (SELECT row_number() over () FROM tables CROSS JOIN columns) so that it can return a row count = to your greatest identity value in the source...
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

asterite
Newbie
Newbie
Posts: 5
Joined: Wed May 08, 2013 9:02 pm

Re: Importing data into an identity column

Post by asterite » Fri May 10, 2013 7:58 pm

Thanks, that works :-)

Post Reply

Return to “New to Vertica”