Dataset migration between federated database systems

Abstract

In one embodiment, a method for migrating data records to a federated database system includes: retrieving data records from a data source in a first federated database system; generating a data snapshot file based on the retrieved data records and data indicating the characteristics associated with the retrieved data records; generating a hash of the data snapshot file to prevent modification of the data snapshot file; storing the data snapshot file and the generated hash in data storage; and migrating the retrieved data records from the data snapshot file to a data target in a second federated database system, the method comprising: retrieving the data records from the data snapshot file stored in data storage; and providing the retrieved data records to the data target according to a mapping between the characteristics of the data source and the characteristics of the data target.

Description

Technical Field

[0001] (Claim of Priority) This application claims priority to U.S. Patent Application No. 18 / 442,567, filed Feb. 15, 2024, which claims priority to U.S. Provisional Patent Application No. 63 / 501,610, filed May 11, 2023, the entire contents of both of which are incorporated herein by reference.

Background Art

[0002] A federated database system is a distributed database management system that includes multiple data sources such as relational data sources and / or non-relational data sources (e.g., databases, tables, files, SQL servers, or other types of data sources) that store data records in various formats. The federated database system includes a federation server that manages the federated database, and the federated database functions as a single collective database that presents user access to multiple underlying data sources. The federated database system also includes a federated database system catalog, which contains information about data records in the federated database and data in the data sources of the federated database system.

Summary of the Invention

[0003] This specification describes flexible and efficient methods for migrating datasets from one environment to a different target environment, even when the target environment requires a record format different from the original format of the data. This is particularly useful when it becomes necessary to make the data processable in both the source and target environments, and / or when establishing data exchange between a source environment with a specific record format and a target environment with a specific record format. For example, these techniques relate to data migration to, from, and within a federated data catalog where datasets are stored in various different formats. These techniques may include automatically applying the correct transformations to the data as part of migrating the data in the context of a federated data catalog, for example, automatically transforming the record format of the source data to match the record format of the target environment. These techniques may also, or alternatively, include automatically masking sensitive fields to keep personally identifiable information secure and comply with data privacy requirements. During migration, relevant metadata identifying the source of the data is retained, which is important for audit trails and the traceability and maintenance of data lineage.

[0004] In one embodiment, a method for migrating data records to a federated database system includes: retrieving data records from a data source in a first federated database system; generating a data snapshot file based on the retrieved data records and data indicating the characteristics associated with the retrieved data records; generating a hash of the data snapshot file to prevent modification of the data snapshot file; storing the data snapshot file and the generated hash in data storage; and migrating the retrieved data records from the data snapshot file to a data target in a second federated database system, which includes: retrieving the data records from the data snapshot file stored in data storage; and providing the retrieved data records to the data target according to a mapping between the characteristics of the data source and the characteristics of the data target.

[0005] Some embodiments may include one of the following features or any combination of two or more of the following features.

[0006] The migration process involves verifying that the data snapshot files stored in data storage have not been edited, and retrieving data records from the data snapshot files is performed in response to this verification. In some cases, verifying that the data snapshot files have not been edited involves recalculating the hash of the data snapshot files stored in data storage and comparing the recalculated hash to the generated hash.

[0007] The mapping between the characteristics of the data source and the characteristics of the data target includes the specification of a second record format for the data records of the data target, and the migration includes determining the correspondence between the first record format of the data records from the data source and the second record format of the data records of the data target. In some cases, if the first record format differs from the second record format, the method includes converting the retrieved data records to the second record format according to the correspondence and providing the converted data records to the data target. In some cases, the method includes processing the data records provided to the data target according to the second record format by the second federated database system. In some cases, the second federated database system does not have built-in functionality for processing the retrieved data records according to the first record format.

[0008] The method includes providing the retrieved data records to the data target according to a mapping between the naming conventions used by the data source and the naming conventions used by the data target.

[0009] The data describing the characteristics associated with the retrieved data record includes one or more of the following: the name of the data source or the location of the data source.

[0010] Data describing the characteristics associated with the retrieved data record includes metadata associated with the data record. In some cases, the metadata associated with the data record includes the specification of the first record format of the retrieved data record.

[0011] Generating a data snapshot file involves including data that shows the data governance rules associated with the source system.

[0012] The method includes masking sensitive data, such as personally identifiable information, contained in one or more fields of the captured data record before generating a data snapshot file. In some cases, generating a data snapshot file based on the captured data record includes generating a data snapshot file that contains the masked data record. In some cases, metadata associated with the data record includes data that specifies the transformation used to mask the data contained in the captured data record. In some cases, generating a data snapshot file includes including data in the file that identifies one or more fields of the data record that were subject to masking. In some cases, the method includes identifying one or more fields containing sensitive data based on semantic analysis of the name of each of the one or more fields. In some cases, generating a hash includes generating a hash of data that indicates the masking algorithm applied to mask the sensitive data.

[0013] Retrieving data records involves selecting a subset of data records contained in a data source, and the selection is based on the values ​​in each of one or more fields of the data records contained in the data source. In some cases, generating a data snapshot file based on the retrieved data records involves generating a data snapshot file that contains the selected subset of data records. In some cases, metadata associated with data records includes data specifying the subsetting algorithm used to select a subset of data records contained in the data source. In some cases, generating a hash involves generating a hash of data indicating the selection algorithm applied to select a subset of data records.

[0014] The method includes generating data records to be included in the captured data records before generating a data snapshot file. In some cases, generating a data snapshot based on the captured data records includes including the captured data records and the generated data records in the data snapshot. In some cases, the method includes generating data based on the distribution of values ​​in each of one or more fields of the data records captured from the data source. In some cases, generating a hash includes generating a hash of data that indicates the data generation algorithm applied to generate the data records.

[0015] The method includes providing data indicating a data source in a first federated database system in response to a request for the lineage of a transformed data record in a data target.

[0016] The method includes providing data indicating the transformations applied to the data record in response to a request for the lineage of the transformed data record in the data target.

[0017] The retrieved data records are provided to the data target only after it has been confirmed that the data snapshot file has not been edited.

[0018] The characteristics of the data source include the first record format of the data records from the data source, and the characteristics of the data target include the second record format of the data records from the data target. In some cases, providing the retrieved data records to the data target according to the mapping includes converting the record format of the retrieved data records to match the second record format. In some cases, converting the record format of the retrieved data records to match the second record format includes reformatting the retrieved data records.

[0019] In a second aspect, which can be combined with the first aspect, the non - transitory computer - readable storage medium stores instructions that, when executed by one or more processors, cause the one or more processors to perform the operations of the foregoing aspect, including one of the foregoing embodiments or any combination of two or more of the foregoing embodiments.

[0020] In a third aspect, which can be combined with the first or second aspect, the system includes one or more processors coupled to a memory that stores instructions that, when executed by the one or more processors, cause the one or more processors to perform the operations of the foregoing aspect, including one of the foregoing embodiments or any combination of two or more of the foregoing embodiments.

[0021] Details of one or more implementations are set forth in the accompanying drawings and the description below. Other features and advantages will be apparent from the description and drawings, and from the claims.

Brief Description of the Drawings

[0022] [Figure 1] It is a schematic diagram of dataset migration between federated database systems. [Figure 2] It is a block diagram of the migration system. [Figure 3A] It is a data - flow graph. [Figure 3B] It is a data - flow graph. [Figure 4] It is a diagram of dataset migration. [Figure 5] It is a flowchart. [Figure 6] It is a diagram of a computing system.

Modes for Carrying Out the Invention

[0023] This specification describes a flexible and efficient method for migrating a dataset from one environment to a different target environment, even when the target environment requires a record format different from the original format of the data. For example, these techniques are relevant to data migration to, from, and within a federated data catalog where datasets are stored in various different formats. These techniques can include automatically applying the correct transformation to the data as part of migrating the data in the context of the federated data catalog, e.g., automatically transforming the record format of the source data to match the record format of the target environment. These techniques can alternatively or additionally include automatically masking sensitive fields to comply with data privacy requirements. During migration, the relevant metadata identifying the source of the data is retained, which is important for audit trail traceability and maintenance.

[0024] Generally, a migration system extracts data from a federated database system for migration to a target environment. The system stores the extracted data as a version - managed snapshot while retaining related information about the data, such as its record format, relationships within the extracted dataset or with other data, and the original source of the dataset. Further, the system applies appropriate transformations to the data as part of the migration process, e.g., transforming the record format of the extracted data to match the record format of the target environment or masking sensitive fields to comply with data privacy requirements.

[0025] One advantage of the methods described herein arises when they are applied to large collections of data obtained from heterogeneous data sources and intended for migration to a wide variety of target environments. For example, data stored in a federated database system is often supplied from a diverse set of data sources, and therefore the data can exist in the federated system in various different record formats. Sometimes, the format in which the data is stored in the federated system may not match the requirements of the data to be stored in the target environments, especially considering that a large number of target environments may be available for migration within the target federated system. The ability to transform the record format of the extracted data within a wide range of potential record format requirements makes these methods broadly applicable to the heterogeneous nature of federated database systems.

[0026] Another advantage of the techniques described herein is the ability to mask sensitive fields before taking a snapshot as part of the migration process and to link this information to the snapshot. Again, this masking capability is applicable across a wide variety of record formats, taking into account the diversity of data contained in the federated database system. Since data migration may involve retrieving data from a secure or controlled environment, the ability to mask sensitive information before taking a snapshot and then link the masked snapshot data to the masking and governance process in which it occurred is crucial for maintaining compliance with privacy and data security requirements.

[0027] Furthermore, the migration system maintains information that identifies and characterizes the extracted data throughout the migration process, allowing the data to be traced back to its original data source. For example, the migration system can generate unique identifiers, such as a key which is a hash of the data extraction itself, which can be used to trace the data extraction back to its origin. Maintaining this identification of the original source of the data extraction is important for maintaining relationships between datasets and for traceability in cases of quality issues, for example.

[0028] Figure 1 shows a schematic diagram of methods for migrating data, such as data records, in the context of a federated database system. Migration can be performed between data sources within a single federated database system, from a data source within one federated database system to a data source within a different federated database system, or from a data source within a federated database system to data storage that is not part of the federated database system, such as local data storage.

[0029] A federated database system is a distributed database management system that includes multiple data sources, such as relational and / or non-relational data sources (e.g., various types of databases, tables, files, or other types of data sources), that store data records in various formats. A federated database system includes a federated server that manages the federated database, which functions as a single, collective database presenting user-facing access to multiple underlying data sources. A federated database system also includes a federated database system catalog, which contains information about data records within the federated database and data within the data sources of the federated database system. Generally, a data record is structured data that includes fields containing values.

[0030] Migrating data records in the context of a federated database system can be challenging due to the various data formats supported by data sources within the federated database system, the various database schemas supported by data sources within the federated database system, and other differences arising from the diversity of data sources available within the federated database system. The data record migration techniques shown in Figure 1 take such differences into account and enable the automated migration of data from one data source to another, for example, within a single federated database system, or from one federated database system to another. Along with data migration, these techniques maintain relevant metadata indicating the origin of the data and any transformations applied to the data as part of the migration process, thus facilitating the traceability and auditability of the data lineage. Furthermore, these data migration techniques prevent the editing of data during the migration process. Moreover, these data migration techniques can comply with privacy or anonymization requirements.

[0031] Figure 1 shows two federated database systems 100a and 100b (collectively referred to as federated database system 100), but the techniques described herein are applicable to any suitable number of federated database systems. Each federated database system includes federated servers 102a and 102b, which include federated databases 104a and 104b and a federated database system catalog (not shown). Each federated database system 100 also includes one or more data sources; for example, federated database system 100a includes at least an Oracle database 106 and an XML file 108, and federated database system 100b includes at least an Informix database 110. A federated database system generally includes multiple data sources, e.g., more data sources than those shown.

[0032] The migration system 150 manages and implements the migration of datasets from one data source to another, such as between data sources within a single federated database system, or from a data source in one federated database system (e.g., federated database system 100a) to a data source in a different federated database system (e.g., federated database system 100b). Generally, the migration system 150 retrieves one or more datasets (e.g., including data records) from a data source in a federated database system (e.g., database 106 in federated database system 100a), stores the retrieved datasets in a data snapshot file, and provides the datasets to a data target (e.g., database 110 in federated database system 100b). Metadata characterizing the retrieved datasets is retained in the data snapshot file to help provide traceability. The snapshot file is protected, for example, by a hash function, which provides security by preventing editing of the datasets before migration to the target.

[0033] In the first part of the migration process, the migration system 150 retrieves one or more datasets 152 from a data source (e.g., database 106). The retrieved datasets may be in the form of tables, files, database schemas, or another preferred format. The retrieved datasets may be a single dataset from a single database in the federated database system 100a, or multiple datasets from a single or multiple databases in the federated database system 100a.

[0034] The migration system 150 writes the acquired dataset 152 to a data snapshot 154. The data snapshot 154 is a package (such as a directory and its files, an archive containing files, or other data objects such as an AWS bucket) containing the dataset and metadata that characterizes the dataset. For example, a data snapshot is a compressed data file containing the dataset and metadata that characterizes the dataset. In some examples, metadata can be recorded per dataset; for example, metadata characterizing an individual dataset 152 is stored in the data snapshot 154 in relation to the corresponding dataset. Alternatively or additionally, metadata related to all datasets 152 is stored in the data snapshot 154 without being associated with any particular dataset. The metadata characterizing a dataset may be metadata that characterizes the data records of the dataset, for example, the record format of the dataset or the parallelism (partitioning) of the dataset. The metadata may include metadata that characterizes the source of the dataset, for example, the identity of the federated database system 100a from which the dataset originated, the identity of the database within the federated database system 100a from which each dataset originated, and the name, catalog instance, version, or timestamp of each dataset in its original source database. The metadata can include metadata that characterizes the transformations applied to dataset 152 before it was written to the data snapshot (as described further below).

[0035] The migration system 150 also generates a unique identifier based on the contents of the data snapshot, such as a hash of the data snapshot contents. The data snapshot 154, along with the hash watermarked on the data snapshot, is stored in data storage 156, such as a version-controlled artifact repository. The data storage may be part of the migration system 150 or may be outside of the migration system 150.

[0036] In some examples, a single hash is generated based on the entire contents of the data snapshot, for example, based on all the datasets included in the data snapshot. In other examples, a hash is generated for each of the datasets included in the data snapshot.

[0037] In the second part of the migration process, the migration system 150 migrates the acquired dataset from the data snapshot 154 in data storage 156 to the target destination (e.g., database 110). Before migration, the migration system 150 verifies that the data snapshot 154 has not been edited by recalculating the hash and comparing the recalculated hash to the hash watermarked in the data snapshot 154. If the recalculated hash matches the hash in the data snapshot 154, the migration system 150 verifies that the dataset in the data snapshot has not been edited. In response to verifying that the data snapshot has not been edited, the migration system 150 retrieves the dataset from the data snapshot 154 in data storage 156 for migration to the target destination 110.

[0038] If a single hash is generated for the entire data snapshot 154, the hash recalculation verifies that none of the datasets have been edited. If a hash is generated for each dataset in the data snapshot 154, the migration system 150 can verify on a dataset-by-dataset basis that each dataset has not been edited. The migration system 150 can then extract individual datasets from the data snapshot 154 for migration to the target destination 110, for example, without necessarily migrating the entire contents of the data snapshot 154.

[0039] The migration system 150 applies a source-to-target mapping 158 when migrating a dataset from a data snapshot 154 to a target destination. The mapping 158 identifies a specific target destination (e.g., database 110) for the migration of the dataset. The mapping 158 can also specify transformations to be applied to the dataset so that the dataset format is compatible with the target destination format. The mapping 158 could be, for example, a default mapping applicable to periodic dataset migrations, a user-specified mapping 158, or a mapping automatically determined by the migration system 150. In a specific example, the user specifies a target destination 110 for a dataset to be migrated from a data source 106, and the migration system 150 automatically determines the relevant transformations based on an analysis of the formats of the data source 106 and the target destination 110. These relevant transformations may be specified by the mapping 158.

[0040] In one example, mapping 158 specifies the record format of data records at the target destination, and if the record format of data records in data snapshot 154 does not match the specified record format, the migration system 150 reformats (converts) the data records to match the record format at the target destination before migrating the data records to the target destination. Mapping 158 can specify the conversion to be applied, or mapping 158 can specify the target record format, and the migration system 150 can determine the conversion based on mapping 158 and the record format of the acquired data records.

[0041] In another example, mapping 158 specifies the naming conventions for schemas and tables in the target database 110, and migration system 150 renames the tables or schemas of the dataset in data snapshot 154 according to the target naming conventions. In an illustrative example, source database 106 may use dot notation for its database naming convention, while target database 110 uses slash notation. In another example, source database 106 may have DB_name.sales.table_name with the naming convention of hierarchical database.schema.table, but this table is renamed to DB_name.USsales.table_name to match the naming convention of target database 110.

[0042] In some examples, the source-to-target mapping 158 is specified by a user, for example, an administrator overseeing the migration process. In some examples, the source-to-target mapping 158 is determined automatically, for example, based on an automated analysis by the migration system 150 of the source database 106 and the target database 110.

[0043] The transformed dataset 160, generated by applying the mapping 158 to the dataset extracted from the data snapshot 154, is provided to the federated database system 100b for storage in a target, such as database 110.

[0044] In some examples, the migration system 150 applies one or more transformations to the acquired dataset before generating a data snapshot 154. Transformations can include masking sensitive data values, selecting a subset of data records from a data source to include in the dataset for migration, or generating data to include in the dataset for migration. Each of these transformations is described in the following paragraphs. Once the migration system 150 applies a transformation to the dataset, data characterizing the transformation is stored in the data snapshot 154. Access to this characterizing data facilitates the auditability and traceability of the migrated data records, as information specifying the applied transformation is stored and hashed together with the dataset itself.

[0045] One example of transformation is the masking of sensitive values ​​(PII) within data records, such as personally identifiable information (PII), including, for example, names, dates of birth, social security numbers, or other such information. Masking of sensitive values ​​is relevant, for example, when data records are migrated outside of a federated database system, because, for example, the data records may subsequently be exposed to or accessible by external systems or users. The migration system 150 can implement a masking algorithm that automatically detects PII, for example, based on semantic discovery analysis of field names to identify fields containing PII, and can mask these values ​​before storing the data records in a data snapshot 154. Semantic discovery is described in more detail in U.S. Patent Application No. 2020 / 0380212, the contents of which are incorporated herein by reference in their entirety. Data characterizing the masking algorithm, such as the names of fields identified by semantic discovery analysis, is stored in the data snapshot.

[0046] Another example of transformation is the selection of data records for migration. Selecting data records allows for the migration of a representative subset of data records contained in a data source (e.g., data records contained in database 106). Migrating a subset of data records may be useful, for example, if the migrated data records are to be used for downstream testing or data quality purposes. In the case of testing or data quality, processing the entire dataset may be resource-intensive, and sufficiently accurate testing or data quality results can be obtained by performing testing or data quality analysis on a representative subset of data records rather than the entire dataset. The migration system 150 can select data records for migration by implementing a subsetting function, for example, as described in U.S. Patent No. 9,892,026, the entirety of which is incorporated herein by reference. The selected data records, rather than the entire dataset in data source 106, are stored in a data snapshot. Data characterizing the subsetting algorithm (e.g., rules governing the selection of the subset of data records) are also stored in the data snapshot.

[0047] A third example of transformation is the generation of data to be included in data records for migration. Generating data may be relevant, for example, when data records are used for downstream testing or data quality purposes but do not contain data covering the full range of possible values ​​or categories. Data generation may include generating data to be included in one or more fields of existing data records obtained from data source 106, generating new data records to be migrated in addition to data records obtained from data source 106, or both. The migration system 150 may generate data to be included in data records for migration by implementing a data generation function, for example, as described in U.S. Patent No. 10,185,641, the entirety of which is incorporated herein by reference. The data records obtained from data source 106 are supplemented with generated values ​​in one or more fields of the obtained data records and / or newly generated data records and stored in a data snapshot. Data characterizing the data generation algorithm (e.g., rules governing the fields identified for data generation, profiles of the generated data, or other rules) are also stored in the data snapshot.

[0048] In some cases, a data snapshot, or one or more individual datasets within a data snapshot, may be edited after the data snapshot is generated, and a new hash may be recalculated after the editing. This can be useful, for example, to add a dataset to a data snapshot or to update one or more datasets before migration.

[0049] The generation of a data snapshot 154 as part of a dataset migration is advantageous because the data snapshot 154 retains information indicating the origin of the dataset (e.g., source database, source record format, timestamp of retrieval from source, etc.) and information indicating the transformations applied to the dataset during the migration process (e.g., masking, subsetting, data generation, reformatting, etc.). In response to a request to track the lineage of the dataset in the target database 110, the metadata within the data snapshot 154 can be retrieved to reveal the origin of the dataset and the transformations applied to it.

[0050] Figure 2 is a block diagram of the migration system 150. One or more datasets 204 are retrieved from data sources 202 in the federated database system and received by the transformation module 206 of the migration system 150. The transformation module also retrieves data "A" indicating the origin of the datasets 204, such as the identity and location of data source 202, the record format of the data records in data source 202, or other information regarding the origin of the datasets.

[0051] In some examples, only a subset of data records within a dataset in data source 202 is obtained as dataset 204 for migration. In these examples, the transformation module 206 implements a subsetting algorithm to select the data records that make up dataset 204. Data "B" characterizing the subsetting algorithm is generated.

[0052] In some examples, the transformation module 206 applies one or more transformations, such as data generation or data masking, to the acquired dataset 204. The application of these transformations results in a transformed dataset 204*, for example, a dataset with masked PII values, or a dataset in which additional data values ​​or data records are generated. Data "C" representing these transformations is generated, for example, data "C" characterizing the data generation and / or data masking algorithms.

[0053] The transformed dataset 204* and feature data A, B, and C are provided from the transformation module 206 to the snapshot generation module 208. The snapshot generation module 208 generates a data snapshot 210 containing the transformed dataset 204* and feature data A, B, and C. The snapshot generation module 208 also calculates a hash # of the contents of the data snapshot. In the illustrated example, the hash is based on the entire contents of the data snapshot 210, but as mentioned above, in some examples, the hash is calculated for each individual dataset. The calculated hash # is watermarked onto the data snapshot 210. Watermarking helps to ensure data integrity. One exemplary technique for watermarking a data snapshot involves embedding watermark data into the data snapshot 210, which can then be retrieved to determine whether any changes have been made to the data snapshot 210 after watermarking.

[0054] The snapshot generation module 210 stores the data snapshot 210, including the hash #, in data storage such as a version-controlled archive 220. In the illustrated example, the archive 220 is shown as part of the migration system 150, but in some examples, the archive 220 is outside of the migration system 150.

[0055] The migration module 212 manages the migration of datasets from the data snapshot 210 to the target destination 214, such as data storage within a federated database system, cloud-based data storage, or local data storage. Before providing the datasets to the target destination 214, the migration module 214 recalculates the hash of the data snapshot 210, or the hashes of one or more of the individual datasets within the data snapshot 210. If the recalculated hashes match the hashes watermarked on the data snapshot 210, this verifies that the datasets have not been edited since the data snapshot was generated and that the migration of the datasets can proceed.

[0056] The migration module 212 retrieves the dataset 204* from the data snapshot 210. The migration module 212 also retrieves data A, which characterizes the origin of the dataset. Based on the mapping "D" between the format of the dataset source 202 and the format of the target destination 214, the migration module 212 applies one or more transformations to the dataset 204* to generate the transformed dataset 204**. The transformations may include, for example, reformatting data records in the dataset, renaming tables or schemas in the dataset, or other transformations such as other format transformations. The migration module 212 then provides the transformed dataset 204** to the target destination 214.

[0057] In some examples, the functionality of a migration system is implemented by one or more executable dataflow graphs, which are computer programs that, when executed, receive data records from data sources, process the data contained in the fields of the data records, and output the processed data to data targets. An executable dataflow graph is a computer program in graph form that contains nodes, which are executable data processing components and data resources such as data sources and data targets. A node can receive data records in the graph, process the data contained in the data records, such as the values ​​in the fields of the data records, and output the processing results in the data records, which are then forwarded to destinations in the graph, such as data resources, e.g., data targets. A data resource is a repository of data, such as data records, e.g., a source of data that is processed or used during the execution of the dataflow graph, or a destination (target) of processed data records that are output by the dataflow graph. Data resources can be, for example, files, databases (e.g., database tables), queues, objects, or other types of data sources or targets. Links connecting two nodes in the graph are provided for the flow of information and / or data (such as data records) between the nodes. An executable dataflow graph, when executed, can be configured to process data contained in fields of data records. A dataflow graph (sometimes called a graph) can be a data processing graph or plan that controls the execution of one or more graphs. In some examples, one or more data processing components of a dataflow graph are subgraphs. A dataflow graph implements graph-based computations performed on data flowing from one or more input datasets through the graphs of processing graph components to one or more output datasets, where the dataflow graph is specified by data structures in data storage, and the dataflow graph has multiple nodes specified by data structures connected by one or more links, where the links are specified by data structures and represent data flow between nodes.A runtime environment for a dataflow graph may be coupled to data storage and hosted on one or more computers. The runtime environment includes an execution module configured to read a stored data structure specifying the dataflow graph and to allocate and configure computing resources, such as processes, for performing calculations of graph components assigned to the dataflow graph by the execution module. The execution module may schedule and control the execution of assigned processes so that the methods described herein can be performed.

[0058] Figures 3A and 3B show exemplary data flow graphs 300 and 350 that implement simplified functionality of an embodiment of the migration system. Data flow graph 300 is a data flow graph for writing a dataset to a data snapshot and includes a component 302 for retrieving a dataset from a data source, a component 304 for reformatting the dataset (for example, for applying one or more transformations such as subsetting, masking, or data generation), and a component 306 for writing the reformatted dataset to the data snapshot. Data flow graph 350 is a data flow graph for migrating a dataset from a data snapshot to a data target and includes a component 352 for reading a dataset from a data snapshot, a component 354 for reformatting the dataset (for example, based on a source-to-target mapping that specifies a target format such as a record format or database schema naming convention), and a component 356 for writing the dataset to the data target.

[0059] Figure 4 shows another example of a dataflow graph 400 that implements functionality for migrating datasets in the context of a federated database system. The dataflow graph 400 reads datasets from multiple data sources 402a, 402b, 402c of different types / formats within a federated database system 401 and migrates the datasets to multiple data targets 452a, 452b, 452c within different federated database systems 451 using data snapshots 420. The dataflow graph 400 includes a data snapshot generation unit 404 implemented in parallel for each of the data sources 402. For each data source 402, the data snapshot generation unit 404 includes a read component 406 that reads the dataset from the data source 402, a filter component 408 that performs a filter operation on the data records of the dataset, a reformatting component 410 that reformattes the dataset, and a write component 412 that writes each reformatted dataset 402* to a data snapshot 420. In addition to the reformatted dataset 402*, the data snapshot 420 contains data characterizing the origin of the reformatted dataset 402* and the transformations applied to the dataset. The data snapshot 420, watermarked with a fingerprint (e.g., a hash), is stored in data storage, such as a version-controlled archive.

[0060] The data flow graph 400 includes a data migration unit 454 implemented in parallel for each dataset 402* that is migrated from the data snapshot 420 to the data target 452. For each dataset 402*, the data migration unit 454 includes a read component 456 that reads the dataset from the data snapshot 420, a reformatting component 458 that reformattes the dataset based on, for example, the format of the data target, and a write component 460 that writes each reformatted dataset 402** to its respective data target 452.

[0061] Referring to Figure 5, an exemplary process for migrating a dataset in the context of a federated database system involves: obtaining the dataset from a data source within the federated database system (500); applying transformations to the dataset (502); for example, masking sensitive values ​​(e.g., personally identifiable information) in the dataset's data records, selecting a subset of data records within the dataset, or generating data values ​​or data records to include in the dataset.

[0062] Generate a data snapshot (504). The data snapshot includes the transformed data records and data that characterizes the dataset, such as data identifying the origin of the dataset and / or data indicating the transformations applied to the dataset. Calculate a fingerprint (such as a hash) of the data snapshot (506). Store the hash-watermarked data snapshot in data storage, such as a version-controlled archive (508).

[0063] To migrate the dataset from the data snapshot to the target destination, a hash of the data snapshot is calculated and compared to a watermarked hash to verify that the dataset in the data snapshot has not been modified (510). In response to confirming that the dataset has not been edited, the dataset is retrieved from the data snapshot (512). Based on the mapping between the data source and the target destination, for example, the mapping between their formats, a transformation is applied to the dataset retrieved from the data snapshot (512). For example, a record format transformation is applied, or the database schema or table naming conventions are updated. The transformed dataset is provided to the target destination (514).

[0064] Figure 6 shows an example of a data processing system 850 for developing and running a data flow graph, in which the techniques described herein may be used. System 850 includes a data source 852 which may include one or more data sources, such as connections to storage devices or online data streams, each of which may store or provide data in any of the following formats (e.g., database tables, spreadsheet files, flat text files, or native formats used by mainframe computers). The data may be logistics data, analytical data, or industrial machinery data. The execution environment or runtime environment 854 includes a preprocessing module 856 and an execution module 862. The execution environment 854 may be hosted on one or more general-purpose computers under the control of a preferred operating system, such as a version of the UNIX operating system. For example, execution environment 854 may include a multinode parallel computing environment that includes a configuration of a computer system using a large number of processing units (such as a central processing unit, CPU) or processor cores, which may be local (e.g., a multiprocessor system such as a symmetric multi-processing (SMP) computer), locally distributed (e.g., a large number of processors coupled as a cluster or massively parallel processing (MPP) system), or remote or remotely distributed (e.g., a large number of processors coupled via a local area network (LAN) and / or a wide-area network (WAN)), or any combination thereof.

[0065] The storage device providing the data source 852 may be local to the execution environment 854, for example, stored on a storage medium (e.g., a hard drive 858) connected to the computer hosting the execution environment 854, or it may be remote to the execution environment 854, for example, hosted on a remote system (e.g., a mainframe computer 860) that communicates with the computer hosting the execution environment 854 via a remote connection (e.g., provided by a cloud computing infrastructure).

[0066] The preprocessing module 856 reads data from the data source 852 and prepares a data processing application (e.g., an executable data flow graph) for execution. For example, the preprocessing module 856 may compile the data processing application, store and / or load the compiled data processing application into a data storage system 866 accessible from the execution environment 854, and perform other tasks to prepare the data processing application for execution.

[0067] The execution module 862 executes a data processing application prepared by the preprocessing module 856 to process a dataset and generate output data 864 resulting from the processing. The output data 864 may be stored again in the data source 852, or again in a data storage system 866 that has access to the execution environment 854, or may be used in other ways. The data storage system 866 also has access to an optional development environment 868 in which a developer 870 can design and edit the data processing application executed by the execution module 862. In some implementations, the development environment 868 is a system for developing applications as a data flow graph, including vertices connected by directed links (representing work elements, i.e., data flows) between vertices (representing data processing components or datasets). For example, such an environment is described in detail in U.S. Patent Application Publication No. 2007 / 0011668 entitled "Managing Parameters for Graph-Based Applications", the contents of which are incorporated herein by reference in their entirety. A system for performing such graph-based computations is described in U.S. Patent No. 5,966,072, entitled "Executing Computations Expressed as Graphs," the contents of which are incorporated herein by reference in their entirety. A dataflow graph created according to this system provides a method for obtaining information entering and leaving individual processes represented by graph components in order to move information between processes and define the execution order of processes. The system includes an algorithm for selecting a method of communication between processes from any available method (for example, communication paths via graph links can use TCP / IP or UNIX domain sockets, or data can be passed between processes using shared memory).

[0068] The preprocessing module 856 can receive data from various types of systems that can embody the data source 852, including different forms of database systems. The data may be organized as records, each having a value for a field (also called an "attribute" or "column"), which may include null values. When the preprocessing module 856 first reads data from the data source, it typically begins with some initial formatting information about the records in that data source. In some situations, the record structure of the data source may not be known initially and may instead be determined after analysis of the data source or data. Initial information about a record may include, for example, the number of bits representing different values, the order of fields in the record, and the type of value represented by the bits (e.g., string, signed / unsigned integer).

[0069] In other words, as generally applicable to the executable dataflow graphs described herein, an executable dataflow graph performs graph-based calculations on data flowing from one or more input datasets of a data source 852 to one or more output datasets via data processing components, the dataflow graph having nodes representing data processing components specified by data structures in data storage 864 and connected by one or more links, the links representing data flows between data processing components. The execution environment or runtime environment 854 is coupled to the data storage 864 and hosted on one or more computers, and the runtime environment 854 includes a preprocessing module 856 configured to read stored data structures specifying the dataflow graph, allocate and configure system resources (e.g., processes, memory, CPU, etc.) to execute calculations of data processing components assigned to the dataflow graph by the preprocessing module 856, and the runtime environment 854 includes an execution module 862 for scheduling and controlling the execution of calculations of data processing components. In other words, a runtime or execution environment 854 hosted on one or more computers is configured to read data from a data source 852 and process the data using an executable computer program represented in the form of a data flow graph.

[0070] The above method can be implemented using a computing system running suitable software. For example, the software may include procedures in one or more computer programs running on one or more programmed or programmable computing systems (which may be of various architectures such as distributed, client / server, or grid), each computing system including at least one processor, at least one data storage system (including volatile and / or nonvolatile memory and / or storage elements), and at least one user interface (for receiving input using at least one input device or port and providing output using at least one output device or port). The software may include, for example, one or more modules of a larger program that provide services related to the design, configuration, and execution of graphs. Modules of the program (e.g., elements of a graph) can be implemented as data structures or other organized data that conform to a data model stored in a data repository.

[0071] The software may be provided on a tangible, non-temporary medium such as a CD-ROM or other computer-readable medium (readable by, for example, a general-purpose or dedicated computing system or device), or it may be delivered via a network communication medium to a tangible, non-temporary medium of the computing system on which it is executed (e.g., encoded in a propagating signal), where it is executed. Some or all of the processing may be executed on a dedicated computer, or it may be executed using dedicated hardware such as a coprocessor, a field-programmable gate array (FPGA), or a dedicated application-specific integrated circuit (ASIC). The processing may be implemented in a distributed manner, in which different parts of the computation specified by the software are executed by different computation elements. Each such computer program is preferably stored in or downloaded to a computer-readable storage medium (e.g., solid-state memory or medium, or magnetic or optical medium) of a storage device accessible by a general-purpose or dedicated programmable computer, so as to configure and operate the computer to perform the processing described herein when the storage device medium is read by the computer. The system of the present invention may also be considered to be implemented as a tangible, non-temporary medium composed of a computer program, the medium configured in such a way causes a computer to operate in a specific, predetermined manner in order to perform one or more of the processing steps described herein.

[0072] Embodiment 1. A method for migrating data records to a federated database system, comprising: retrieving data records from a data source in a first federated database system; generating a data snapshot file based on the retrieved data records and data indicating the characteristics associated with the retrieved data records; generating a hash of the data snapshot file to prevent modification of the data snapshot file; storing the data snapshot file and the generated hash in data storage; and migrating the retrieved data records from the data snapshot file to a data target in a second federated database system, comprising: retrieving the data records from the data snapshot file stored in data storage; and providing the retrieved data records to the data target according to a mapping between the characteristics of the data source and the characteristics of the data target.

[0073] Embodiment 2. The method according to Embodiment 1, wherein the migration includes verifying that the data snapshot file stored in data storage has not been edited, and retrieving data records from the data snapshot file is performed in response to the verification.

[0074] Embodiment 3. The method according to Embodiment 2, wherein verifying that a data snapshot file has not been edited includes recalculating the hash of the data snapshot file stored in data storage and comparing the recalculated hash with the generated hash.

[0075] Embodiment 4. The method according to any one of Embodiments 1 to 3, wherein the mapping between the characteristics of the data source and the characteristics of the data target includes the specification of a second record format for the data records of the data target, and the mapping includes determining the correspondence between a first record format for the data records from the data source and a second record format for the data records of the data target.

[0076] Embodiment 5. The method according to Embodiment 4, wherein, if the first record format differs from the second record format, the retrieved data record is converted to the second record format according to the correspondence, and the converted data record is provided to the data target.

[0077] Embodiment 6. The method according to Embodiment 4 or 5, further comprising processing data records provided to a data target according to a second record format by a second federated database system.

[0078] Embodiment 7. The method according to any one of Embodiments 4 to 6, wherein the second federated database system does not have built-in functions for processing the retrieved data records according to the first record format.

[0079] Embodiment 8. A method according to any one of Embodiments 1 to 7, comprising providing retrieved data records to a data target in accordance with a mapping between naming conventions used by a data source and naming conventions used by a data target.

[0080] Embodiment 9. The method according to any one of Embodiments 1 to 8, wherein the data indicating the characteristics associated with the acquired data record includes one or more of the name of the data source or the location of the data source.

[0081] Embodiment 10. The method according to any one of Embodiments 1 to 9, wherein the data indicating the characteristics associated with the acquired data record includes metadata associated with the data record.

[0082] Embodiment 11. The method according to Embodiment 10, wherein the metadata associated with the data record includes a specification of the first record format of the retrieved data record.

[0083] Embodiment 12. The method according to any one of Embodiments 1 to 11, wherein generating a data snapshot file includes including data that shows data governance rules associated with the source system.

[0084] Embodiment 13. The method according to any one of Embodiments 1 to 12, comprising masking sensitive data, such as data associated with personally identifiable information, contained in one or more fields of the acquired data record before generating a data snapshot file.

[0085] Embodiment 14. The method according to Embodiment 13, wherein generating a data snapshot file based on acquired data records includes generating a data snapshot file that includes masked data records.

[0086] Embodiment 15. The method according to Embodiment 13 or 14, wherein the metadata associated with the data record includes data specifying a transformation used to mask the data contained in the retrieved data record.

[0087] Embodiment 16. The method according to any one of Embodiments 13 to 15, wherein generating a data snapshot file includes including data in the file that identifies one or more fields of the data record that was subject to masking.

[0088] Embodiment 17. The method according to any one of Embodiments 13 to 16, comprising identifying one or more fields containing sensitive data based on semantic analysis of the names of one or more fields.

[0089] Embodiment 18. The method according to any one of Embodiments 13 to 17, wherein generating a hash is the process of generating a hash of data that indicates a masking algorithm applied to mask sensitive data.

[0090] Embodiment 19. The method according to any one of Embodiments 1 to 18, wherein retrieving data records comprises selecting a subset of data records contained in a data source, and the selection is based on the values ​​in each of one or more fields of the data records contained in the data source.

[0091] Embodiment 20. The method according to Embodiment 19, wherein generating a data snapshot file based on acquired data records includes generating a data snapshot file containing a selected subset of data records.

[0092] Embodiment 21. The method according to Embodiment 19 or 20, wherein the metadata associated with the data record includes data specifying a subsetting algorithm used to select a subset of data records contained in the data source.

[0093] Embodiment 22. The method according to any one of Embodiments 19 to 21, wherein generating a hash is the process of generating a hash of data that indicates a selection algorithm applied to select a subset of data records.

[0094] Embodiment 23. The method according to any one of Embodiments 1 to 22, comprising generating data records to be included in the acquired data records before generating a data snapshot file.

[0095] Embodiment 24. The method according to Embodiment 23, wherein generating a data snapshot based on acquired data records includes including the acquired data records and the generated data records in the data snapshot.

[0096] Embodiment 25. The method according to Embodiment 23 or 24, comprising generating data based on the distribution of values ​​in each of one or more fields of a data record obtained from a data source.

[0097] Embodiment 26. The method according to any one of Embodiments 23 to 25, wherein generating a hash comprises generating a hash of data that indicates a data generation algorithm applied to generate a data record.

[0098] Embodiment 27. The method according to any one of Embodiments 1 to 26, which provides data indicating a data source in a first federated database system in response to a request for lineage of converted data records in a data target.

[0099] Embodiment 28. The method according to any one of Embodiments 1 to 27, comprising providing data indicating the transformations applied to the data record in response to a request for the lineage of a transformed data record in a data target.

[0100] Embodiment 29. The method according to any one of Embodiments 1 to 28, wherein the retrieved data records are provided to the data target only after it has been confirmed that the data snapshot file has not been edited.

[0101] Embodiment 30. The method according to any one of Embodiments 1 to 29, wherein the characteristics of the data source include a first record format for data records from the data source, and the characteristics of the data target include a second record format for data records from the data target.

[0102] Embodiment 31. The method according to Embodiment 30, wherein providing the retrieved data records to a data target according to the mapping includes converting the record format of the retrieved data records to match a second record format.

[0103] Embodiment 32. The method according to Embodiment 31, wherein converting the record format of the retrieved data record to match a second record format includes reformatting the retrieved data record.

[0104] Embodiment 33. A non-temporary computer-readable storage medium for storing instructions, wherein, when an instruction is executed by one or more processors, it causes one or more processors to perform the operation described in any one of Embodiments 1 to 32.

[0105] Embodiment 34. A system comprising one or more processors coupled to memory, wherein the memory stores instructions, and when an instruction is executed by one or more processors, it causes one or more processors to perform the operation described in any one of Embodiments 1 to 32.

[0106] Several embodiments have been described. Nevertheless, it will be understood that various modifications can be made without departing from the spirit and scope of the invention. For example, some of the steps described above may be sequence-independent and therefore can be performed in an order different from that described.

[0107] Other implementation forms are also within the scope of the following claims.

Claims

1. A method for migrating data records to a federated database system, Retrieving data records from a data source within the first federated database system, Based on the acquired data records and the data indicating the characteristics associated with the acquired data records, a data snapshot file is generated. To prevent modification of the aforementioned data snapshot file, a hash of the aforementioned data snapshot file is generated, The data snapshot file and the generated hash are stored in the data storage. The process involves migrating the acquired data records from the data snapshot file to a data target in the second federated database system. Retrieving the data record from the data snapshot file stored in the data storage, A method comprising providing the retrieved data records to the data target in accordance with a mapping between the characteristics of the data source and the characteristics of the data target.

2. The method according to claim 1, wherein the migration includes verifying that the data snapshot file stored in the data storage has not been edited, and retrieving the data records from the data snapshot file is performed in response to the verification.

3. To confirm that the aforementioned data snapshot file has not been edited, Recalculating the hash of the data snapshot file stored in the data storage, The method of claim 2, comprising comparing the recalculated hash with the generated hash.

4. The method according to claim 1, wherein the mapping between the characteristics of the data source and the characteristics of the data target includes a specification for a second record format of the data records of the data target, and the migration includes determining a correspondence between a first record format of the data records from the data source and a second record format of the data records of the data target.

5. The method according to claim 4, comprising: converting the retrieved data record to the second record format according to the correspondence relationship if the first record format is different from the second record format; and providing the converted data record to the data target.

6. The method according to claim 4, further comprising processing the data records provided to the data target by the second federated database system in accordance with the second record format.

7. The method according to claim 4, wherein the second federated database system does not have built-in functions for processing the retrieved data records according to the first record format.

8. The method according to claim 1, comprising providing the retrieved data records to the data target in accordance with a mapping between a naming convention used by the data source and a naming convention used by the data target.

9. The method according to claim 1, wherein the data indicating the characteristics associated with the acquired data record includes one or more of the name of the data source or the location of the data source.

10. The method according to claim 1, wherein the data indicating the characteristics associated with the acquired data record includes metadata associated with the data record.

11. The method according to claim 10, wherein the metadata associated with the data record includes a specification of a first record format for the acquired data record.

12. The method according to claim 1, wherein generating the data snapshot file includes including data that indicates data governance rules associated with the source system.

13. The method according to claim 1, further comprising masking sensitive data, such as data associated with personally identifiable information, contained in one or more fields of the acquired data record before generating the data snapshot file.

14. The method according to claim 13, wherein generating a data snapshot file based on the acquired data records includes generating a data snapshot file that includes the masked data records.

15. The method according to claim 13, wherein the metadata associated with the data record includes data specifying a transformation used to mask the data contained in the acquired data record.

16. The method according to claim 13, wherein generating the data snapshot file includes including data in the file that identifies one or more fields of the data record that was subject to masking.

17. The method according to claim 13, comprising identifying the one or more fields containing the confidential data based on semantic analysis of the names of each of the one or more fields.

18. The method according to claim 13, wherein generating the hash includes generating a hash of data indicating a masking algorithm applied to mask the confidential data.

19. The method according to claim 1, wherein obtaining the data records comprises selecting a subset of the data records contained in the data source, the selection being based on the values ​​in each of one or more fields of the data records contained in the data source.

20. The method according to claim 19, wherein generating a data snapshot file based on the acquired data records comprises generating a data snapshot file containing the selected subset of the data records.

21. The method according to claim 19, wherein the metadata associated with the data record includes data specifying a subsetting algorithm used to select the subset of the data record contained in the data source.

22. The method according to claim 19, wherein generating the hash comprises generating a hash of data indicating a selection algorithm applied to select the subset of the data records.

23. The method according to claim 1, comprising generating data records to be included in the acquired data records before generating the data snapshot file.

24. The method according to claim 23, wherein generating a data snapshot based on the acquired data records includes including the acquired data records and the generated data records in the data snapshot.

25. The method according to claim 23, comprising generating data based on the distribution of values ​​in each of one or more fields of the data record obtained from the data source.

26. The method according to claim 23, wherein generating the hash includes generating a hash of data indicating a data generation algorithm applied to generate the data record.

27. The method according to claim 1, wherein, in response to a request for lineage of the converted data records in the data target, data indicating the data source in the first federated database system is provided.

28. The method according to claim 1, comprising providing data indicating the transformation applied to the data record in response to a request for the lineage of the transformed data record in the data target.

29. The method according to claim 1, wherein the retrieved data record is provided to the data target only after it has been confirmed that the data snapshot file has not been edited.

30. The method according to claim 1, wherein the characteristics of the data source include a first record format for the data records from the data source, and the characteristics of the data target include a second record format for the data records of the data target.

31. The method according to claim 30, wherein providing the retrieved data records to the data target according to the mapping includes converting the record format of the retrieved data records to match the second record format.

32. The method according to claim 31, wherein converting the record format of the retrieved data record to match the second record format includes reformatting the retrieved data record.

33. A non-temporary computer-readable storage medium for storing instructions, wherein when an instruction is executed by one or more processors, the instructions are sent to the one or more processors. Retrieving data records from a data source within the first federated database system, Based on the acquired data records and the data indicating the characteristics associated with the acquired data records, a data snapshot file is generated. To prevent modification of the aforementioned data snapshot file, a hash of the aforementioned data snapshot file is generated, The data snapshot file and the generated hash are stored in the data storage. The process involves migrating the acquired data records from the data snapshot file to a data target in the second federated database system. Retrieving the data record from the data snapshot file stored in the data storage, A non-temporary computer-readable storage medium that causes an operation to be performed, which includes providing the retrieved data records to the data target according to a mapping between the characteristics of the data source and the characteristics of the data target.

34. A system comprising one or more processors coupled to memory, wherein the memory stores instructions, and when an instruction is executed by the one or more processors, the one or more processors... Retrieving data records from a data source within the first federated database system, Based on the acquired data records and the data indicating the characteristics associated with the acquired data records, a data snapshot file is generated. To prevent modification of the aforementioned data snapshot file, a hash of the aforementioned data snapshot file is generated, The data snapshot file and the generated hash are stored in the data storage. The process involves migrating the acquired data records from the data snapshot file to a data target in the second federated database system. Retrieving the data record from the data snapshot file stored in the data storage, A system that causes operations to be performed, including providing the retrieved data records to the data target according to a mapping between the characteristics of the data source and the characteristics of the data target.

Images