Importing data to a sink with an IDENTITY column

Learn how to import data to a sink that includes an IDENTITY column.

You can import the data from a valid source to a sink table (On-premises/Cloud Services) with an IDENTITY column. You create the IDENTITY column as either GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. For more information on table creation with an IDENTITY column, see Creating Tables With an IDENTITY Column in the SQL Reference Guide.

Before importing the data, make sure that the Oracle NoSQL Database table at the sink is empty if it exists. If there is pre-existing data in the sink table, migration can lead to issues such as overwriting existing data in the sink table or skipping source data during the import.

Sink table with IDENTITY column as GENERATED ALWAYS AS IDENTITY

Consider a sink table with the IDENTITY column created as GENERATED ALWAYS AS IDENTITY. The data import is dependent on whether or not the source supplies the values to the IDENTITY column and ignoreFields transformation parameter in the configuration file.

For example, you want to import data from a JSON file source to the Oracle NoSQL Database table as the sink. The schema of the sink table is: 

CREATE TABLE IF NOT EXISTS migrateID(ID INTEGER GENERATED ALWAYS AS IDENTITY, name STRING, course STRING, PRIMARY KEY
      (ID))
The Migrator utility handles the data migration as described in the following cases:
Source condition User action Migration outcome

CASE 1: Source data does not supply a value for the IDENTITY field of the sink table.

Example: JSON source file sample_noID.json

{"name":"John", "course":"Computer Science"}
{"name":"Jane", "course":"BioTechnology"}
{"name":"Tony", "course":"Electronics"}

Create/generate the configuration file.

Data migration is successful. IDENTITY column values are auto-generated. Migrated data in Oracle NoSQL Database sink table migrateID: 

{"ID":1001,"name":"Jane","course":"BioTechnology"}
{"ID":1003,"name":"John","course":"Computer Science"}
{"ID":1002,"name":"Tony","course":"Electronics"}

CASE 2: Source data supplies values for the IDENTITY field of the sink table.

Example: JSON source file sampleID.json

{"ID":1, "name":"John", "course":"Computer Science"}
{"ID":2, "name":"Jane", "course":"BioTechnology"}
{"ID":3, "name":"Tony", "course":"Electronics"}

Create/generate the configuration file. You provide an ignoreFields transformation for the ID column in the sink configuration template.

"transforms" : { "ignoreFields" : ["ID"] }

Data migration is successful. The supplied ID values are skipped and the IDENTITY column values are auto-generated.

Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":2003,"name":"John","course":"Computer Science"}
{"ID":2002,"name":"Tony","course":"Electronics"}
{"ID":2001,"name":"Jane","course":"BioTechnology"}

You create/generate the configuration file without the ignoreFields transformation for the IDENTITY column.

Data migration fails with the following error message:

"Cannot set value for a generated always identity column".

For more details on the transformation configuration parameters, see the topic Transformation Configuration Templates.

Sink table with IDENTITY column as GENERATED BY DEFAULT AS IDENTITY

Consider a sink table with the IDENTITY column created as GENERATED BY DEFAULT AS IDENTITY. The data import is dependent on whether or not the source supplies the values to the IDENTITY column and ignoreFields transformation parameter.

For example, you want to import data from a JSON file source to the Oracle NoSQL Database table as the sink. The schema of the sink table is: 

CREATE TABLE IF NOT EXISTS migrateID(ID INTEGER GENERATED BY DEFAULT AS IDENTITY, name STRING, course STRING, PRIMARY KEY
      (ID))
The Migrator utility handles the data migration as described in the following cases:
Source condition User action Migration outcome

CASE 1: Source data does not supply a value for the IDENTITY field of the sink table.

Example: JSON source file sample_noID.json

{"name":"John", "course":"Computer Science"}
{"name":"Jane", "course":"BioTechnology"}
{"name":"Tony", "course":"Electronics"}

Create/generate the configuration file.
Data migration is successful. IDENTITY column values are auto-generated. Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":1,"name":"John","course":"Computer Science"}
{"ID":2,"name":"Jane","course":"BioTechnology"}
{"ID":3,"name":"Tony","course":"Electronics"}

CASE 2: Source data supplies values for the IDENTITY field of the sink table and it is a Primary Key field.

Example: JSON source file sampleID.json

{"ID":1, "name":"John", "course":"Computer Science"}
{"ID":2, "name":"Jane", "course":"BioTechnology"}
{"ID":3, "name":"Tony", "course":"Electronics"}

Create/generate the configuration file. You provide an ignoreFields transformation for the ID column in the sink configuration template (Recommended).

"transforms" : { "ignoreFields" : ["ID"] }

Data migration is successful. The supplied ID values are skipped and the IDENTITY column values are auto-generated.

Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":1002,"name":"John","course":"Computer Science"}
{"ID":1001,"name":"Jane","course":"BioTechnology"}
{"ID":1003,"name":"Tony","course":"Electronics"}

You create/generate the configuration file without the ignoreFields transformation for the IDENTITY column.

Data migration is successful. The supplied ID values from the source are copied into the ID column in the sink table.

When you try to insert an additional row to the table without supplying an ID value, the sequence generator tries to auto-generate the ID value. The sequence generator's starting value is 1. As a result, the generated ID value can potentially duplicate one of the existing ID values in the sink table. Since this is a violation of the primary key constraint, an error is returned and the row does not get inserted.

See Sequence Generator for additional information.

To avoid the primary key constraint violation, the sequence generator must start the sequence with a value that does not conflict with existing ID values in the sink table. To use the START WITH attribute to make this modification, see the example below:

Example: Migrated data in Oracle NoSQL Database sink table migrateID:
{"ID":1,"name":"John","course":"Computer Science"}
{"ID":2,"name":"Jane","course":"BioTechnology"}
{"ID":3,"name":"Tony","course":"Electronics"}
To find the appropriate value for the sequence generator to insert in the ID column, fetch the maximum value of the ID field using the following query:
SELECT max(ID) FROM migrateID
Output: 
{"Column_1":3}
The maximum value of the ID column in the sink table is 3. You want the sequence generator to start generating the ID values beyond 3 to avoid duplication. You update the sequence generator's START WITH attribute to 4 using the following statement:
ALTER Table migrateID (MODIFY ID GENERATED BY DEFAULT AS IDENTITY (START WITH 4))

This will start the sequence at 4.

Now when you insert rows to the sink table without supplying the ID values, the sequence generator auto-generates the ID values from 4 onwards averting the duplication of the IDs.

For more details on the transformation configuration parameters, see the topic Transformation Configuration Templates.