ETL Definition quick start guide

Learn how to set up and use an ETL definition to import data into ServiceNow tables.

Before you begin

Role required: admin

For this quick start guide, we're going to import data in the following JSON file to ServiceNow target tables. This JSON file contains hierarchical data for a school, classes, students and subjects. Save the following content to a JSON file.

[{ 
   "name": "schoolA", 
   "classes": [ 
   { 
    "name": "classA", 
    "students": [ 
    {"name": "studentA", "subjects": [{"name": "subjectA", "marks": 80}, 
     {"name": "subjectB", "marks": 90},{"name": "subjectC", "marks": 100}]}, 
    {"name": "studentB", "subjects": [{"name": "subjectA", "marks": 75}, 
     {"name": "subjectB", "marks": 85}, {"name": "subjectC", "marks": 95}]}
   ] 
  },{ 
   "name": "classB", 
   "students": [ 
   {"name": "studentC", "subjects": [{"name": "subjectA", "marks": 60}, 
    {"name": "subjectB", "marks": 70}, {"name": "subjectC", "marks": 80}]}, 
   {"name": "studentD", "subjects": [{"name": "subjectA", "marks": 55}, 
    {"name": "subjectB", "marks": 65}, {"name": "subjectC", "marks": 75}]} 
   ] 
  } 
 ] 
}]

Procedure

1. Create a data source and load data.

   1. Create a data source with a Format of JSON, and the Path for each row as //.

   2. Check the Data in single column option.

   3. Save the data source.

   4. Attach the above JSON file to the data source.

   5. Select Load All Records to load records to the import set table.

6.  Open the import set row created.

    The record should be saved to a single JSON column.

1. Create the target table structure to import data.

   1. In this example, we have a school with multiple classes, each class has multiple students, and each student has multiple subjects.

   2. Create a table structure to reflect these relationships.

   3. School -> name : string.

   4. Class -> name : string , school : reference to school.

   5. Student -> name : string, class : reference to class.

   6. Subject -> name : string, mark : integer, student : reference to student.

2. Create an ETL Definition to map the JSON record data to target tables.

   1. Go to System Import Sets > ETL Definition.

   2. Select New.

   3. Enter a name and save the record.

1. Create entities.

   1. Under the ETL Entities tab, select New.

   2. Set the Name as Import Set and save.

   3. Add import set entity fields for all the leaf values of the JSON.

      The field/path is the path from the root of the JSON and we mark arrays with [*].

4.  Go to ETL Definition and under ETL Entities, select **New**.

5.  Set the **Name** as `School`.

6.  Set the **Table** as the school table created in step 2.

7.  Set the **Path** as `school`.

8.  Save the Entity.

9.  Select **Generate fields** under Related Links.

    This should generate the **Name** field. Set the **Coalesce** to **true**.

10. Go to ETL Definition and under ETL Entities, select **New**.

11. Set the **Table** as the class table created in step 2.

12. Set the **Path** as `class[*]`.

    Using \[\*\] makes it an entity with multiple rows.

13. Select **Generate fields** under Related Links.

14. Since **School** in the ETL Entity fields is a reference field, modify the **Field/Path** to `school.name` and set **Coalesce** to **true** for the **Name** field because name is unique.

15. Add Entities to Student and Subject as well.

    For Subject, set **Coalesce** to **true** for both the **Name** and **Student** fields.

1. Add RTE Entity Mappings.

   1. Go to RTE Entity Mappings and select New.

   2. Set the Name to Import Set to school.

   3. Set the Source Entity to Import Set.

   4. Set the Target Entity to School.

   5. Keep the Order as 100.

   6. Under RTE Field Mappings, select New.

   7. For the Source Field, select School name.

      You can only select the Entity fields from the source entity.

   8. For the Target Field, select Name.

      You can only select the Entity fields from the target entity.

9.  Go to ETL Definition and under RTE Entity Mappings select **New**.

10. Set the **Name** to `Import set to class`.

11. Select **Source Entity** to **Import Set**.

12. Select **Target Entity** to **Class**.

13. Set **Order** to **200**.

14. Add the RTE Field Mappings to map **Name** and **School name**.

    The school **Target Field** should map to the **School name** of the import set. The system does the school look up using this value and sets the correct school reference.

15. Add mappings for Student and Subject as well.

1. Create a Robust Import Set Transformer record and run the import.

   1. Go to the Data source created in step 1.

   2. Select the Robust Transformer tab and select New.

   3. Set a Name.

   4. Set the Transformer definition to the ETL Definition we created earlier.

   5. Set Verbose.

      Selecting Verbose is not required, but enables you to debug the configuration. Switch this off before moving to production because it can negatively impact the performance.

6.  Select **Submit**.

7.  Select **Load All Records**.

8.  Select **Run Robust Transform**.

9.  Select **Transform**.

10. Go to the import set.

11. Select the Import Set Rows tab and open the import set row record.

12. If the configuration works correctly, it should show the import set row record with all the records inserted.