Copy data and overwrite identity column

Mike_Gong_1

Hi, I exported a table with identity column. When I restore it, how can I load values of identity column from backup file. Thanks Mike

Mike_Gong_1

Hi, Any idea about this? I did some research but could not figure it out. If the values of identity column can not be reloaded from backup file, it is too risky to use it. Thanks

[Deleted User]

Hi Mike, Did you back up your data using our backup mechanism, or did you just dump it to a file via vsql or similar? I'm not super-familiar with our backup logic; that's how we would recommend that you make a backup copy of your table. If you're having trouble there, hopefully someone else can respond. If you just dumped to a .csv file or similar, and are loading using COPY: COPY does not allow loading IDENTITY columns; we can't (cheaply) prove that you haven't modified the column's contents to be something invalid. You have two options: First, have COPY ignore that field in the file using a COPY FILLER expression (in which case each record will get a new ID, which may not be what you want); second, temporarily ALTER the column to be a regular INTEGER column, then load the data, then re-add the default sequence value and update the current sequence value to match the data loaded so far. Adam

Mike_Gong_1

Thanks Adam! Yeah, I just dumped the table to csv file. It appears that this way will not work. I also tried COPY FROM VERTICA and EXPORT TO VERTICA but it did not work either. Not sure if database backup mechanism can do the magic. In traditional database, there is an option to override auto incremental value of database. It is a nice feature to have. Without this feature, I'm not confident to use identity/auto increment feature that is provided by database.

[Deleted User]

Hi Mike, Vertica's backup tools are documented here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#16935.htm As mentioned, you can override the auto-increment behavior in Vertica if you need to. Switch the column type back to a regular integer (by dropping the default expression associated with the sequence) for the duration of the operation. The reason that we make this difficult is that, if you can override the auto-increment feature of a table, then you can corrupt the data in the table. The feedback that we get more often is that people lose confidence in systems that make it easy to corrupt IDENTITY data in this way. That said, if you're part of a community that feels that it's important for IDENTITY columns to be easily modified, please do post an Idea here, and encourage your colleagues to comment on it supporting it. If it becomes clear that we've mis-read our general user opinion to date, then we will of course take another look. Adam

[Deleted User]

(For "post an Idea here", that'd be under the Ideas section of the site. You could post it here too, but the right people may not read it.)

zagnut

@Adam: I'm trying this a number of ways but keep getting the ROLLBACK 3018: Default expression of IDENTITY/AUTO_INCREMENT column "id" cannot be altered response: alter table mySchema.test alter column id drop default; alter table MySchema.test alter column id set default 0; Can you give the steps required to do the temporary drop, insert value, then re-indentity?

[Deleted User]

Hm... What version of Vertica are you running? I know I've done this before. But it unfortunately seems that the current version of Vertica doesn't allow it... Apologies for the misdirection; I will look around here to figure out what happened, hopefully this is something that an upcoming version of Vertica will address. In the meantime, a workaround would be to use a regular INT column with an explicit sequence. (Since that's how IDENTITY is implemented; you can see as much in the SEQUENCES system table.) If you set up a sequence yourself, you can always remove it or modify it yourself as well. Adam

Mike_Gong_1

Thanks Adam. I tried a backup/restore on table level. This approach addressed my concern to re-load exported data back with original values of identity column. vbr.py -t backup --config-file xxxxxxBackup.ini vbr.py -t restore --config-file xxxxxxBackup.ini

zagnut

version: Vertica Analytic Database v6.1.1-0 Actually, that's good. I like the idea of explicitly setting up an identity column to allow for manipulation. I'll give it a shot. Thanks.

zagnut

This will work great. @(anyone): Here's the droppable-identity-column "solution": dbadmin=> create sequence test_schema.test_seq; CREATE SEQUENCE dbadmin=> create table test_schema.test( id int default nextval('test_schema.test_seq'), txt varchar(50), primary key (id) ); CREATE TABLE dbadmin=> insert into test_schema.test(txt) values('test'); OUTPUT -------- 1 (1 row) dbadmin=> select * from test_schema.test; id | txt ----+------ 1 | test (1 row) dbadmin=> alter table test_schema.test alter column id set default -1; ALTER TABLE dbadmin=> insert into test_schema.test(txt) values('dummy row'); OUTPUT -------- 1 (1 row) dbadmin=> alter table test_schema.test alter column id set default nextval('test_schema.test_seq'); ALTER TABLE dbadmin=> insert into test_schema.test(txt) values('test 2'); OUTPUT -------- 1 (1 row) dbadmin=> select * from test_schema.test; id | txt ----+----------- -1 | dummy row 1 | test 2 | test 2 (3 rows) It would be nice to be able to do this for tables built with an IDENTITY column. @Adam: are there additional constraints involved we're not aware of preventing this?

zagnut

@Adam: this is not good. Unique constraint is not enforced: create sequence test_schema.test_seq; CREATE SEQUENCE dbadmin=> create table test_schema.test( id int not null unique primary key default nextval('test_schema.test_seq'), txt varchar(50) ); CREATE TABLE dbadmin=> insert into test_schema.test(txt) values('test'); OUTPUT -------- 1 (1 row) dbadmin=> insert into test_schema.test(id, txt) values(3, 'test 2'); OUTPUT -------- 1 (1 row) dbadmin=> insert into test_schema.test(txt) values('test 3'); OUTPUT -------- 1 (1 row) dbadmin=> select * from test_schema.test; id | txt ----+-------- 3 | test 2 1 | test 2 | test 3 (3 rows) dbadmin=> insert into test_schema.test(txt) values('test 4'); OUTPUT -------- 1 (1 row) dbadmin=> select * from test_schema.test; id | txt ----+-------- 3 | test 2 3 | test 4 1 | test 2 | test 3 (4 rows)

[Deleted User]

@zagnut: sequences are database objects that generate unique numbers in ascending or descending sequential order. If you are using it as a column default expression for a primary key column, you will not be guaranteed unique values if you manually insert data to that column. It should be used only if you want to maintain a unique auto generated ID (auto-increment) for a column without the user inserting any values. If you had did "select nextval('test_schema.test_seq'); " , then would have got '4'.

zagnut

@Satish: Right. I'd initially defined the table with an IDENTITY(1,1), but have a particular key i'd like to use for a dummy row. specifying IDENTITY in the CREATE TABLE seems to prevent me from temporarily dropping the IDENTITY constraint in order to insert that particular key and then let the IDENTITY run for the lifetime of that table. This seems to be a workaround, however I thought the UNIQUE constraint would catch the duplicate value. And it will, once I run ANALYZE_STATISTICS. I think the real solution is to pick say 0 as the dummy row key id for any particular dimension, start IDENTITY at 0 and make sure to insert the dummy record after the table is created. We're working with some existing tables, however, ergo the desire to be able to temporarily drop the IDENTITY and replace it.

sreeblr

As followup to you post if i use EXPORT to VERTICA its able to copy sequence from source to target server.

However if i dont have direction connection and have to create a dump of data using vsql its a challenge to insert into tables ignoring identity column since the sequence get reset.  if EXPORT To VERTICA is able to do this is there any setting which enables copy/insert to vertica tables and then start the sequence from the last sequence.

sreeblr

hi mike. can you provide steps /ini file used to copy table data between two vertica server. What all files need to be copied to target .

 

Can we do incremental for a table ?