Versions Compared

Old Version 7

changes.mady.by.user Amy Smith

Saved on

compared with

New Version Current

changes.mady.by.user Amy Smith

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
stylenone

Liquibase Enterprise and CSV Files

...

Table of Contents
stylenone

Liquibase Enterprise and CSV Files

Liquibase Enterprise does have the ability to load and update data using CSV formatted files.

Note the following with the CSV loadData and loadUpdateData:

  1. The Deploy Report does not show the Generated SQL & Output.

  2. Rollback of CSV data files is not supported.

  3. CSV file format is mandatory and should contain headers in the first line.

  4. CSV file formats and CSV data files are not currently supported in Packager automation. Files must be placed manually or via external automation in the Resources directory of the Liquibase Project.

(Optional) Oracle Setup Data

...

Note you can include changeset attributes such as labels and contexts.

See Valid Data Types for supported values.

Code Block
<changeSet author="Kevin" id="2024-06-25-data-load-1">
  <loadData encoding="UTF-8" file="data/contacts.csv" tableName="contacts" schemaName="dbo">
    <column header="id" name="id" type="NUMERIC"/>
    <column header="activeflag" name="activeflag" type="BOOLEAN"/>
    <column header="firstname" name="firstname" type="STRING"/>
    <column header="lastname" name="lastname" type="STRING"/>
    <column header="age" name="age" type="NUMERIC"/>
    <column header="lastcall" name="lastcall" type="DATETIME"/>
  </loadData>
</changeSet>

...

The CSV file generates the following SQL code. 

Note the Deploy Report does not show the this Generated SQL & Output.

Code Block
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Chris', 'Klackson', 32, to_date('2025-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Samantha', 'Sallers', 47, to_date('2025-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Pete', 'Prosser', 50, to_date('2025-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Joeseph', 'Scala', 70, to_date('2025-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Umbert', 'Klassen', 31, to_date('2025-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Gary', 'Finer', 35, to_date('2025-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'));

...

Note you can include changeset attributes such as labels and contexts.

See Valid Data Types for supported values.

Code Block
<changeSet id="2024-06-25-contacts-data-update-1" author="Kevin" >
  <loadUpdateData
  encoding="UTF-8"
  file="data/updated_contacts_1.csv"
  tableName="contacts"
  schemaName="dbo"
  primaryKey="id">
    <column name="id" header="id" type="NUMERIC"/>
    <column name="activeflag" header="activeflag" type="NUMERIC"/>
    <column name="firstname" header="firstname" type="STRING"/>
    <column name="lastname" header="lastname" type="STRING"/>
    <column name="age" header="age" type="NUMERIC"/>
    <column name="lastcall" header="lastcall" type="DATETIME"/>
  </loadUpdateData>
</changeSet>

Step 4:

The changeset is now eligible to be deployed to the various databases on the pipeline. If you are using artifacts, be sure the package the changelog updates prior to running any deployments.

...

The CSV file generates the following SQL code. 

Note the Deploy Report does not show the this Generated SQL & Output.

Code Block
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 1;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (1, 1, 'Chris', 'Klackson', 32, to_date('2013-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 1, age = 32, firstname = 'Chris', lastcall = to_date('2025-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Klackson' WHERE id = 1;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 2;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (2, 0, 'Samantha', 'Sallers', 47, to_date('2013-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 0, age = 47, firstname = 'Samantha', lastcall = to_date('2025-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Sallers' WHERE id = 2;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 3;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (3, 1, 'Pete', 'Prosser', 50, to_date('2013-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 1, age = 50, firstname = 'Pete', lastcall = to_date('2025-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Prosser' WHERE id = 3;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 4;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (4, 0, 'Joeseph', 'Scala', 71, to_date('2012-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 0, age = 71, firstname = 'Joeseph', lastcall = to_date('2025-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Scala' WHERE id = 4;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 5;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (5, 0, 'Umbert', 'Klassenmass', 31, to_date('2012-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 0, age = 31, firstname = 'Umbert', lastcall = to_date('2025-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Klassenmass' WHERE id = 5;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 6;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (6, 1, 'Gary', 'Finer', 36, to_date('2013-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 1, age = 36, firstname = 'Gary', lastcall = to_date('2025-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Finer' WHERE id = 6;
END IF;
END;
/;

...