Automated extract, transform, and load process
A computer-implemented method is provided for an automated extract, transform, and load process for a target database comprising linked data. During the data transformation phase linked data elements are added as data to a data set.
1 . A computer-implemented method for an automated extract, transform, and load process for a target database comprising linked data, wherein the method comprises the following steps:
a data extraction phase in which source data is extracted from one or more data sources;
a data transformation phase in which the extracted source data is transformed to target data for a target database; and
a data load phase in which the target data is loaded into the target database, wherein the data transformation phase comprises the following steps:
receiving extracted source data comprising a first data set which is embodied by a first table stored in a file which comprises first data entities, wherein each row of the first data set corresponds to one of the first data entities, wherein each first data entity of the first data entities comprises:
a first data element; and
a first set of second data elements that are related to the first data element;
receiving extracted source data comprising a second data set which is embodied by a second table stored in a file and which comprises second data entities, wherein each row of the second data set corresponds to one of the second data entities, wherein each second data entity of the second data entities comprises:
a second data element;
creating a third data set comprising a third data entity for each second data element of the first set of second data elements in each first data entity of the first data set sequentially by means of a full table scan of all first data entities of the first data set in which each row of the first table embodying the first data set is read in a sequential order until all rows are read and processed, wherein the third data entity comprises:
the second data element; and
a second set comprising an aggregation of all first data elements that the second data element is related to in all the first data entities of the first data set;
creating a fourth data set comprising the second data set as data elements of the target data which is subsequently loaded into the target database, sequentially by means of a full table scan of all second data entities of the second data set in which each row of the second table embodying the second data set is read in a sequential order until all rows are read and processed including:
for each second data entity of the second data set, scanning the third data set to detect a third data entity that comprises a second data element that matches the second data element in the second data entity of the second data set;
upon detecting the third data entity that comprises a second data element that matches the second data element in the second data entity of the second data set, adding the second set corresponding to the second data element in the third data entity to the second data entity of the second data set to create a fourth data entity of the fourth data set;
wherein the third data set is not indexed during the data transformation phase or at least up to the creation of the fourth data set; and
wherein, during the data transformation phase:
no database index is stored for the third data set and no further data structure for a database index is created before or during the data transformation phase for the third data set;
no use is made of a relational database system for creating the third data set and the fourth data set; and
no relational database files associated to a relational database system are generated.
2 . The method according to claim 1 , wherein, during the data transformation phase, no database index is stored for any of the following additional data sets:
the first data set;
the second data set; and
the fourth data set.
3 . The method according to claim 1 , wherein the step of creating the fourth data set is further performed by means of a full table scan of the second data entities of the second data set in which for each uniquely found second data entity with a match, the corresponding second set of the detected third data entity is added once.
4 . The method according to claim 1 , wherein, the step of creating the fourth data set further comprises:
after creating a fourth data entity of the fourth data set,
removing from the third data set the detected third data entity for this created fourth data entity.
5 . The method according to claim 4 , wherein the data transformation phase, after completion of the step of creating the fourth data set, comprises the further steps of:
storing any remaining third data entities in the third data set as link errors; and
subsequently removing the third data set.
6 . The method according to claim 1 , wherein the third data set is a temporary data set stored in volatile memory which is removed after creation of the fourth data set.
7 . The method according to claim 1 , wherein the third data set comprises a Key Value Store in which the third data entities are key-value pairs comprising:
the second data element as key; and
the second set as value.
8 . The method according to claim 1 , wherein:
the first data element comprises a unique identifier of a first data entity of the first data set; and
the second data element comprises a unique identifier of a second data entity of the second data set.
9 . The method according to claim 1 , wherein the first data elements and the second data element are stored in a compressed form in each of the third data entities of the third data set.
10 . The method according to claim 1 , wherein the data transformation phase further transforms the fourth data set to target data for the data load phase.
11 . The method according to claim 1 , wherein the method comprises the further step of loading the target data into the target database to make the added second set available as a data element of the fourth data entity of the fourth data set.
12 . An automated extract, transform and load system, comprising a processor configured to perform the following steps:
a data extraction phase in which source data is extracted from one or more data sources;
a data transformation phase in which the extracted source data is transformed to target data for a target database; and
a data load phase in which the target data is loaded into the target database,
wherein the data transformation phase comprises the following steps:
receiving extracted source data comprising a first data set which is embodied by a first table stored in a file and which comprises first data entities, wherein each row of the first data set corresponds to one of the first data entities, wherein each first data entity of the first data entities comprises:
a first data element; and
a first set of second data elements that are related to the first data element;
receiving extracted source data comprising a second data set which is embodied by a second table stored in a file and which comprises second data entities, wherein each row of the second data set corresponds to one of the second data entities, wherein each second data entity of the second data entities comprises:
a second data element;
creating a third data set comprising a third data entity for each second data element of the first set of second data elements in each first data entity of the first data set sequentially by means of a full table scan of all first data entities of the first data set in which each row of the first table embodying the first data set is read in a sequential order until all rows are read and processed, wherein the third data entity comprises:
the second data element; and
a second set comprising an aggregation of all first data elements that the second data element is related to in all the first data entities of the first data set;
creating a fourth data set comprising the second data set as data elements of the target data which is subsequently loaded into the target database, sequentially by means of a full table scan of all second data entities of the second data set in which each row of the second table embodying the second data set is read in a sequential order until all rows are read and processed including:
for each second data entity of the second data set, scanning the third data set to detect a third data entity that comprises a second data element that matches the second data element in the second data entity of the second data set;
upon detecting the third data entity that comprises a second data element that matches the second data element in the second data entity of the second data set, adding the second set corresponding to the second data element in the third data entity to the second data entity of the second data set to create a fourth data entity of the fourth data set;
wherein the third data set is not indexed during the data transformation phase or at least up to the creation of the fourth data set; and
wherein, during the data transformation phase:
no database index is stored for the third data set and no further data structure for a database index is created before or during the data transformation phase for the third data set;
no use is made of a relational database system for creating the third data set and the fourth data set; and
no relational database files associated to a relational database system are generated.
13 . A non-transitory computer-readable storage medium carrying a computer program comprising instructions which, when the computer program is executed by a computer, cause the computer to carry out a method, wherein the method comprising the following steps:
a data extraction phase in which source data is extracted from one or more data sources;
a data transformation phase in which the extracted source data is transformed to target data for a target database; and
a data load phase in which the target data is loaded into the target database, wherein the data transformation phase comprises the following steps:
receiving extracted source data comprising a first data set which is embodied by a first table stored in a file and which comprises first data entities, wherein each row of the first data set corresponds to one of the first data entities, wherein each first data entity of the first data entities comprises:
a first data element; and
a first set of second data elements that are related to the first data element;
receiving extracted source data comprising a second data set which is embodied by a second table stored in a file and which comprises second data entities, wherein each row of the second data set corresponds to one of the second data entities, wherein each second data entity of the second data entities comprises:
a second data element;
creating a third data set comprising a third data entity for each second data element of the first set of second data elements in each first data entity of the first data set sequentially by means of a full table scan of all first data entities of the first data set in which each row of the first table embodying the first data set is read in a sequential order until all rows are read and processed, wherein the third data entity comprises:
the second data element; and
a second set comprising an aggregation of all first data elements that the second data element is related to in all the first data entities of the first data set;
creating a fourth data set comprising the second data set as data elements of the target data which is subsequently loaded into the target database, sequentially by means of a full table scan of all second data entities of the second data set in which each row of the second table embodying the second data set is read in a sequential order until all rows are read and processed including:
for each second data entity of the second data set, scanning the third data set to detect a third data entity that comprises a second data element that matches the second data element in the second data entity of the second data set;
upon detecting the third data entity that comprises a second data element that matches the second data element in the second data entity of the second data set, adding the second set corresponding to the second data element in the third data entity to the second data entity of the second data set to create a fourth data entity of the fourth data set;
wherein the third data set is not indexed during the data transformation phase or at least up to the creation of the fourth data set; and
wherein, during the data transformation phase:
no database index is stored for the third data set and no further data structure for a database index is created before or during the data transformation phase for the third data set;
no use is made of a relational database system for creating the third data set and the fourth data set;
no relational database files associated to a relational database system are generated.