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
Importing data into an identity column
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Importing data into an identity column
Maybe you can try something like the following?
This is super simple example...
This is super simple example...
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Importing data into an identity column
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?
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?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Importing data into an identity column
Hi,
I can't test this, but something like this might work for you...
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...
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;
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Importing data into an identity column
Thanks, that works