Learn ETL rules by example

Abstract

Embodiments provide systems and methods for learning extraction, transformation, and loading mappings by example. Multiple features can be extracted from a source schema and a target schema. An example ETL mapping can be provided to a machine learning algorithm, the example ETL mapping including definitions for extracting data from a source table and loading the extracted data into a target table. Using the machine learning algorithm and based on the source schema, the target schema, and the extracted features, one or more ETL rules can be predicted that define logic for extracting data from the source schema and loading the extracted data into the target schema. Additional ETL mappings can be generated based on the predicted ETL rules, the additional ETL mappings providing additional definitions for extracting data from the source schema and loading the extracted data into the target schema.

Description

[0001] CROSS-REFERENCE TO RELATED APPLICATIONS

[0002] This application claims priority to U.S. Patent Application No. 15 / 953,873, filed on April 16, 2018, the disclosure of which is incorporated herein by reference. Technical Field

[0003] Embodiments of the present disclosure are generally directed to learning extract, transform, and load mapping by example. Background Art

[0004] The proliferation of computing and connected devices has created a large amount of data that needs to be managed. One aspect of data management that is typically performed by humans (such as data scientists) is extraction, transformation, and loading ("ETL") or extraction, loading, and transformation ("ELT"), both of which will be used interchangeably throughout this disclosure. Generally speaking, ETL is a step in a larger data migration process that moves data from a source schema to a target schema. This migration process typically has multiple layers, such as table / column mapping, extraction and loading rules / mapping (e.g., join conditions, unit of work determination, etc.), post-migration and / or pre-migration transformations, just to name a few. Therefore, while other aspects of data management have become agile and efficient, ETL can be a cumbersome process when utilizing modern conventional technologies. Summary of the invention

[0005] Embodiments of the present disclosure are generally directed to systems and methods for extracting, transforming, and loading mappings by learning by example, which provide substantial improvements over the related art.

[0006] In some embodiments, multiple features can be extracted from the source architecture and the target architecture, and these features include at least columns of multiple tables of the source architecture and the target architecture. Example ETL mappings can be provided to a machine learning algorithm, wherein the example ETL mapping includes a definition for extracting data from one or more tables of the source architecture and loading the extracted data into one or more tables of the target architecture. Using the machine learning algorithm and based on the source architecture, the target architecture, and the extracted features, one or more ETL rules can be predicted, and these rule definitions are used to extract data from the source architecture and load the extracted data into the logic of the target architecture. Additional ETL mappings can be generated based on the predicted ETL rules, the source architecture, the target architecture, and the extracted features, and these additional ETL mappings provide additional definitions for extracting data from one or more tables of the source architecture and loading the extracted data into one or more tables of the target architecture.

[0007] Features and advantages of the embodiments are set forth in the following description, or will be apparent from the description, or may be learned by practice of the present disclosure. BRIEF DESCRIPTION OF THE DRAWINGS

[0008] Other embodiments, details, advantages and modifications will become apparent from the following detailed description of preferred embodiments taken in conjunction with the accompanying drawings.

[0009] Figure 1 A system for learning by example to extract, transform, and load mappings according to an example embodiment is illustrated.

[0010] Figure 2 Illustrated is a block diagram of a computing device operably coupled to an ETL learning system according to an example embodiment.

[0011] Figure 3 A sample star data schema is illustrated according to an example embodiment.

[0012] Figure 4 A sample ETL specification according to an example embodiment is illustrated.

[0013] Figure 5 A sample ETL structure implemented by an ETL tool according to an example embodiment is illustrated.

[0014] Figure 6 A sample architecture mapping structure is illustrated according to an example embodiment.

[0015] Figure 7 Illustrated are sample ETL mapping expressions according to example embodiments.

[0016] Figure 8 A sample attribute vector according to an example embodiment is illustrated.

[0017] Fig. 9 An example method according to an example embodiment for learning by example to extract, transform, and load mappings according to an example embodiment is illustrated. DETAILED DESCRIPTION

[0018] The embodiments learn extraction, transformation, and loading mappings by example. The relationship between the source architecture and the target architecture can often be complex, making the application of machine learning for ETL non-simple. The embodiments utilize feature extraction of the source data architecture and the target data architecture for providing data points at a granular level to the machine learning algorithm, which enables improved learning. In addition, example mappings between the source data architecture and the target data architecture are provided to the machine learning algorithm defined according to the ETL framework. As a result, the machine learning algorithm can extrapolate trends based on the relationship shown between the target architecture and the source architecture in the example. These extrapolated trends can be defined at a detailed level that is conducive to machine prediction because they are defined at a granular level specific to the architecture. Subsequently, the machine learning algorithm can predict new ETL rules that can be used to complete the migration of data from the source to the target. The rule interpreter can then be used to generate ETL mappings based on the predicted new ETL rules.

[0019] Reference will now be made in detail to embodiments of the present disclosure, examples of which are illustrated in the accompanying drawings. In the following detailed description, many specific details are set forth in order to provide a thorough understanding of the present disclosure. However, it will be apparent to one of ordinary skill in the art that the present disclosure may be practiced without these specific details. In other cases, well-known methods, processes, components, and circuits are not described in detail to avoid unnecessarily obscuring aspects of the embodiments. Whenever possible, the same reference numerals will be used for the same elements.

[0020] Figure 1 A system for learning extraction, transformation, and loading mappings by example according to an embodiment is illustrated. System 100 includes a source architecture 102, a target architecture 104, feature extraction 106, an example ETL mapping 108, a machine learning component 110, an output ETL rule 112, a rule interpreter 114, and an output ETL mapping 116. Source architecture 102 and target architecture 104 can be any data architecture for storing data, such as relational data tables, flat files, NoSQL databases, message queues or topics, event streams, or combinations thereof. For example, source architecture 102 and / or target architecture 104 can include a set of relational tables configured according to a set of rules or standards, such as a third normal form ("3NF") architecture.

[0021] In general, each schema will include a data table with one or more columns of data. A schema is defined not only by the tables and the data they store, but also by the relationships between the tables. For example, a relationship between a first table and a second table can be defined by a foreign key that links the data stored in each of the first and second tables. In some embodiments, two tables can share multiple relationships (e.g., can have multiple foreign keys that define relationships between tables). Different types of relationships between tables will be further disclosed herein.

[0022] The design of a data architecture can often vary based on the designer. For example, a given data set with a given set of relationships can be successfully represented by multiple data architectures with different designs. Some designs may require table joins to retrieve a specific data set, while other designs do not. In addition, many legacy systems implement architectural designs that are not easily implemented by currently known databases. When considering how to extract data from a source architecture and load data into a target architecture, fine-grained details (such as naming conventions for tables, columns, and keys, etc.) play an important role in rule / mapping construction. Due to the highly subjective nature of architectural design, the rules / mappings for extracting, loading, and / or transforming data stored in the source architecture 102 to the target architecture 104 may be specific to these architectures.

[0023] In an embodiment, in order to configure the machine learning component 110 to generate accurate rule predictions, feature extraction 106 is performed on the source architecture 102 and the target architecture 104. For example, feature extraction 106 can be used to provide the machine learning component 110 with fine-grained details that enable ETL rule / mapping generation. In turn, the learning component 110 can perform analysis at this fine granularity to predict architecture-specific rules / mappings for the source architecture 102 and the target architecture 104. The features extracted for each architecture can include table names, column names, table types, foreign keys between tables, self-referential tables, and many other features. These extracted features can express common, commonly used structures or patterns in the architecture, can enhance the architecture information, and can simplify the work of the machine learning component 110.

[0024] Embodiments also provide the features extracted by feature extraction 106 to rule interpreter 114. For example, rule interpreter 114 may utilize the extracted features to interpret the predicted rules to generate output ETL mapping 116. Features that may be extracted by feature extraction 106 will be further disclosed herein.

[0025] An embodiment provides the machine learning component 110 with example ETL mappings 108 for extracting data from the source schema 102 and loading the data into the target schema 104. The example mappings 108 may be specific mappings between data (e.g., tables) of the source schema 102 and the target schema 104. Since the ETL mappings 108 are specific to a specific data migration task (e.g., migrating from the source schema 102 to the target schema 104), the learning component 110 may predict ETL rules for the specific migration task being implemented.

[0026] The embodiment passes the generated output ETL rules 112 to the rule interpreter 114, which can interpret the rules to generate output ETL mappings 116. For example, the machine learning component 110 can generate rules in the form of one or more conditional statements (e.g., if X and Y, then Z), and the rule interpreter 114 can generate the actual mapping between the source schema 102 and the target schema 104 by interpreting these rules. The embodiment provides the source schema 102, the target schema 104, and the features extracted from the feature extraction 106 to the rule interpreter 114. For example, the output ETL rules 112 can be interpreted into output ETL mappings 116 based on the specific structure of the source schema 102 and the target schema 104 and based on the specific features extracted from these schemas.

[0027] In some embodiments, when an ETL mapping 108 is provided for a specific mapping between a source architecture 102 and a target architecture 104, machine learning complexity introduced by the subjectivity of architecture design is mitigated. For example, because the specific training data provided to the machine learning component 110 represents the design differences between the source architecture 102 and the target architecture 104, the output ETL rules 112 predicted by the machine learning component 110 will be based on these specific design differences, rather than on the general design differences that may occur when using general architecture relationship training data. The rule interpreter 112 can then use these specific rules and use the architecture 102, the target architecture 104, and the extracted features from the feature extraction 106 to generate an output ETL mapping that is also specific to the source architecture 102 and the target architecture 104. The functionality implemented by the embodiment is an automated technique for generating an ETL mapping between a specific source architecture and a specific target architecture, which previously required a lot of manpower.

[0028] In some embodiments, source architecture 102, target architecture 104, feature extraction 106, example ETL mapping 108, machine learning component 110, output ETL rules 112, rule interpreter 114, and output ETL mapping 116 can be implemented by a single system or computing device, distributed across various computing devices, and in some embodiments across various locations, combinations of these, and can be configured in any other suitable manner. In example embodiments, the elements of system 100 can be software, hardware, or a combination of these.

[0029] Figure 2 2 is a block diagram of a computer server / system 210 according to an embodiment. Figure 2 As shown, the system 210 may include a bus device 212 and / or (one or more) other communication mechanisms configured to transmit information between various components of the system 210, such as the processor 222 and the memory 214. In addition, the communication device 220 may implement a connection between the processor 222 and other devices for the processor 222 by encoding data to be sent from the processor 222 to another device over a network (not shown) and decoding data received from another system over the network.

[0030] For example, the communication device 220 may include a network interface card configured to provide wireless network communications. Various wireless communication technologies may be used, including infrared, radio, Wi-Fi and / or cellular communications. Alternatively, the communication device 220 may be configured to provide a wired network connection(s), such as an Ethernet connection.

[0031] Processor 222 may include one or more general or special purpose processors to perform computational and control functions of system 210. Processor 222 may include a single integrated circuit, such as a microprocessing device, or may include multiple integrated circuit devices and / or circuit boards that work in concert to perform the functions of processor 222. In addition, processor 222 may execute computer programs stored in memory 214, such as operating system 215, learning component 216, and additional application module 218.

[0032] The system 210 may include a memory 214 for storing information and instructions for execution by the processor 222. The memory 214 may include various components for retrieving, presenting, modifying, and storing data. For example, the memory 214 may store software modules that provide functionality when executed by the processor 222. These modules may include an operating system 215 that provides operating system functionality for the system 210. These modules may include an operating system 215, a learning component 216, and additional application modules 218. The operating system 215 provides operating system functionality for the system 210. The learning component 216 may provide system functionality for predicting ETL rules / mappings that extract data from a source architecture and load it into a target architecture, or may also provide any other functionality of the present disclosure. In some cases, the learning component 216 may be implemented as an in-memory configuration.

[0033] The non-transitory memory 214 may include various computer-readable media that may be accessed by the processor 222. For example, the memory 214 may include random access memory (“RAM”), dynamic RAM (“DRAM”), static RAM (“SRAM”), read-only memory (“ROM”), flash memory, cache memory, and / or any other type of non-transitory computer-readable media.

[0034] Processor 222 is further coupled to a display 224 , such as a liquid crystal display (“LCD”), via bus 212 . A keyboard 226 and cursor control device 228 , such as a computer mouse, are further coupled to communication device 212 to enable a user to interface with system 210 .

[0035] In some embodiments, the system 210 may be part of a larger system. Thus, the system 210 may include one or more additional application modules 218 to include additional functionality. The additional application modules 218 may include various components of a data warehouse, including, for example, an operating system and a data warehouse object, Data Integrator ( Data Integrator, "ODI"), can be included in The data changed in the source system of Golden Gate and the Change Data Capture ("CDC") mechanism and Apache Kafka based on Kafka stream processing, and other suitable components. The database 217 is coupled to the bus 212 to provide centralized storage for the learning component 216 and the additional application module 218, and stores, for example, wireless device activities, and in some embodiments, user profiles, transaction history, etc. The database 217 can store data in an integrated collection of logically related records or files. The database 217 can be an operational database, an analytical database, a data warehouse, a distributed database, an end-user database, an external database, a navigation database, an in-memory database, a document-oriented database, a real-time database, a relational database, an object-oriented database, a Hadoop Distributed File System ("HFDS"), or any other database known in the art.

[0036] Although shown as a single system, the functionality of system 210 may be implemented as a distributed system. For example, memory 214 and processor 222 may be distributed across multiple different computers that collectively represent system 210. In one embodiment, system 210 may be part of a device (e.g., a smart phone, tablet, computer, etc.).

[0037] In embodiments, system 210 may be separate from the device and may provide the described functionality to the device remotely. In addition, one or more components of system 210 may not be included. For example, for functionality as a user or consumer device, system 210 may be a smartphone or other wireless device that includes a processor, memory, and display, but does not include a processor, memory, and display. Figure 2 One or more other components shown, and including those not shown Figure 2 Other components shown in FIG.

[0038] Figure 3A sample star data schema according to an example embodiment is illustrated. The data schema 300 includes a fact table 302, a dimension_1 table 304, a dimension_2 table 306, and a dimension_3 table 308. Generally speaking, in a star schema, a fact table holds facts about a domain, and a dimension table holds attributes of these facts. As a result, the fact table 302 has various foreign key relationships with the dimension_1 table 304, the dimension_2 table 306, and the dimension_3 table 308. In other words, some of the rows in the fact table are foreign keys ("FK"). A foreign key can be a link to a dimension table. A dimension table can be a table that stores context associated with an event referenced by one or more fact tables.

[0039] A star data schema is similar to a snowflake data schema, but has some differences. For example, a snowflake data schema contains dimensions that are normalized into multiple related tables, while a star schema has denormalized dimensions where each dimension is represented by a single table. Each of these schemas offers different advantages related to data redundancy, simplicity of query design, etc. For example, the storage efficiency advantage of normalization can result in a tradeoff in query efficiency over a normalized data schema.

[0040] Return to reference Figure 1 , the machine learning component 110 relies on feature extraction 106 to extract features that describe measurable and observable properties of the source architecture 102, the target architecture 104, and the example ETL mapping 108. Features can be domain specific and can encapsulate some of the typical aspects of a solution. In general, the more independent features that exist, the more likely they are to be learned. The following are sample features that can be extracted from the source architecture 102 and the target architecture 104:

[0041] Table or column name

[0042] The name can be the source of decision making when mapping sources to target tables / columns. Example characteristics include:

[0043] Source and target table / column names are the same

[0044] Folder names consist of the source / target table name with a prefix or suffix

[0045] A mapping name consists of the source or target table name with a prefix and a suffix.

[0046] The mapping name consists of the source name and the target name with fixed strings before, after, and between the table names.

[0047] Mapping names contain a monotonically increasing number among other columns with similar names.

[0048] The source table and the target table are similar, with a similarity distance of {1, 2, 3, 4, greater than 4}

[0049] Column names start with the string "UDF"

[0050] Table ends with "_L" or "_F"

[0051] The column name is "EFFECTIVE_DT"

[0052] ·

[0053] Some of the extracted features will be defined as part of the schema, and some features related to specific tables or prefixes and suffixes can be created based on the source and target schema.

[0054] Column Type

[0055] Column types can be used in various ETL rules / mapping decisions. Example column characteristics according to various embodiments are:

[0056] The source column and target column types are the same

[0057] The source is a subtype of the target type (for example, the source is VARCHAR(20) and the target type is VARCHAR(30))

[0058] The target column type is "Date"

[0059] Column belongs to the primary key

[0060] Column belongs to a foreign key constraint

[0061] The column belongs to the foreign key of the table

[0062] The column has a "Not Null" constraint

[0063] The table has {2, 3, or more} times as many NUMBER columns as other types

[0064] Table Relationships

[0065] Table relationships can be used to determine join or search conditions, sorting, and table identification. Example table relationship features according to various embodiments are:

[0066] Tables are self-referential

[0067] The table has an incoming foreign key constraint

[0068] The table has an outgoing foreign key constraint

[0069] The table has {1, 2, more} outgoing foreign key constraints

[0070] The table has {1, 2, more} incoming foreign key constraints

[0071] Table T1 references table T2

[0072] Table T1 is directly or indirectly connected to T2 via an outgoing foreign key

[0073] Table T1 is directly or indirectly connected to T2 via an outgoing foreign key with fewer than X indirect relationships.

[0074] In some cases, the designer's intent may not be obvious based on the underlying schema. Domain expertise may be useful for certain ETL rules / mapping decisions. For example, it is often unclear which tables in a highly connected data model to include in the dataset. In some cases, additional metadata may be added to the underlying database schema for tables and columns to enable the desired behavior. For example, 10 tables may be connected by foreign key constraints, but only a subset of them with the same "group1" attribute value may be included in the source dataset. In another example, a type can be assigned to a schema or table. For example, a schema can be a star schema with facts, dimensions, hierarchies, or bridge tables. The following illustrates some of the metadata that can be extracted during feature extraction:

[0075] The value of metadata attribute X is Y

[0076] Metadata attribute X is not defined

[0077] The value of metadata attribute X in table T1 is the same as the value of metadata attribute X in table T2

[0078] The value of metadata attribute X in table T1 is {smaller, larger} than the value of metadata attribute X in table T2

[0079] Metadata property X has the same name as {source, target} {table, column} Y

[0080] In some embodiments, metadata can provide information to the machine learning component 110 and / or the rule interpreter 114 that would not otherwise be easily conveyed from other inputs (e.g., other structures of the source schema 102, other structures of the target schema 104, other extracted features, and the example ETL mapping 108). Metadata can be represented as name-value pairs, depending on the particular domain-specific purpose. In some cases, the value can be a list / array of values.

[0081] Some implementations of the ETL generator may include one or more of the following metadata:

[0082] "Source Table" can be used in the target table to identify one or more source tables.

[0083] This can be used to resolve ambiguous situations, such as mismatches due to incorrect naming conventions.

[0084] "Schema Name" and "Schema Participants" can be used for source and target tables to clarify the overall schema used and the role each table (participant) plays. This can serve a similar purpose to other design patterns commonly seen in ETL use cases, for example.

[0085] "Groups" in source and target tables can be used to define the scope of the structure. For example, in a highly connected database schema, it can be challenging to determine which tables are related through FK constraints (which define the unit). In these cases, it is helpful to indicate the grouping.

[0086] "Filter conditions" in the target schema table can be used to express conditions on the source table to define a subset of information. Such conditions are usually driven by domain-specific details in the source system. Providing such information allows the learning algorithm to include the condition in the generated rules.

[0087] In some embodiments, default behavior can be implemented for some standardized metadata elements of the target ETL system. The above examples can be included in such common elements. In some embodiments, metadata can be used in other ways specific to the source architecture. In some embodiments, feature extraction 106 extracts multiple above example features from the source architecture 102 and the target architecture 104.

[0088] In some embodiments, the output ETL mapping 116 is related to the concept of automated architecture mapping, or the mapping of the source architecture 102 to the target architecture 104. The technology described in the embodiments of the present disclosure not only implements architecture mapping, but also implements the process of extracting data from the source table and transforming and loading the data into the target table, that is, the process known as ETL or ELT. Architecture mapping usually occurs in advance in order to successfully enable the ETL solution. In some embodiments, it is sufficient to determine which source table column(s) are mapped to which target table column(s). In simple cases, the matching source table columns and target table columns use sufficiently similar names and define related data types.

[0089] Embodiments provide example ETL mappings 108 that map source schema 102 to target schema 104. Example ETL mappings 108 can be represented in any form that can be easily parsed and is the basis for generating an ETL executable (e.g., an output ETL mapping 116 configured to execute to perform data migration). For example, the ETL Extensible Markup Language ("XML") specification format used in various embodiments can be similar to the ETL XML specification from the ETL tool Oracle Data Integrator ("ODI"). In some embodiments, the example XML ETL specification can include multiple parts:

[0090] Definition of mapping

[0091] ο Name of the mapping

[0092] ο The folder name (e.g., logical file location) where it is located in the ETL software tool

[0093] o Source tables and how to filter, find, and join sources. In various embodiments, sources can exist in source and target architectures.

[0094] οTarget table

[0095] o Extraction (e.g., Structured Query Language (SQL) or database link) and insertion strategies (e.g., increment, merge, append) configured using parameters

[0096] ο A mapping expression for each target column, which may contain constants, 1-1 column mappings, or expressions for one or more source columns

[0097] ·variable

[0098] οVariable name

[0099] οVariable Type

[0100] οVariable Definition

[0101] ·sequence

[0102] o Name of the sequence

[0103] o The type of sequence (e.g., DB sequence)

[0104] οSequence Definition

[0105] How to orchestrate the definitions of ETL mappings and other ETL primitives (also called packages)

[0106] οPackage Name

[0107] οThe folder name in the ETL tool

[0108] ο Sequential or parallel execution of mappings

[0109] ο Initialization of variables

[0110] οExecution of the process

[0111] In addition to the XML specification, the source and target database models contain characteristics to consider:

[0112] The names of the source and target schemas

[0113] Table name and column name

[0114] Column type

[0115] Not null, primary key and foreign key constraints

[0116] Unique index

[0117] In some embodiments, variables can be artifacts of the ETL tool to maintain state (numbers or strings) that can be referenced in mappings and ETL orchestration definitions. In some embodiments, sequences can be part of the ETL definition, typically defined in the database itself. For example, sequences can be provided as a data definition language ("DDL") script for the database, or can be generated separately.

[0118] In various embodiments, the extraction, transformation, and loading of data from a source schema to a target schema is structured as a collection of sub-projects, where any one or a group of sources are extracted, transformed, and loaded into one or a group of target tables. For example, ETL design can include two almost independent levels: determining which tables will be mapped as part of a unit of work, and designing mapping expressions between source columns and target columns. A unit of work can contain one or more mappings and various other details of an ETL solution, such as procedures, sequence definitions, variable definitions, etc. Example table and column mappings for these two levels are provided below:

[0119] Table mapping design

[0120] Table-level ETL design considers which source and target tables participate in the unit of work. There are some common patterns:

[0121] One-to-one mode: One source table is mapped to one target table.

[0122] Many-to-one pattern: Multiple source tables are joined into one data set and mapped to one target table. This is a common scenario when loading data from operational systems into a data warehouse using a star schema. For example, multiple tables representing a dimension hierarchy are joined to produce a denormalized dimension table.

[0123] One-to-many mode: A source table can map all or a subset of records to multiple target tables, which contain all or a subset of the source columns. This method can be used to separate unrelated table attributes into separate tables.

[0124] Type-Subtype Pattern: One source table is mapped to multiple target tables that separate common data into the type table and specific details into one or more subtype tables. This is a special case of the one-to-many pattern discussed above. The reverse is possible, mapping a source table with a type-subtype relationship to one target table.

[0125] Aggregate table pattern: Aggregate tables are typically defined within the target schema using aggregate functions on measurements and a "groupby" clause to aggregate levels derived from tables with finer granularity data.

[0126] Column Mapping Design

[0127] Column mappings have several common variations related to column types, column types and names, and available source columns:

[0128] Columns that are the primary key of the table and can be copied from the source, created using a DB sequence, or copied from the parent table using a lookup operation (e.g., using a left outer join).

[0129] Foreign key columns indicate that a lookup is performed on the referenced table in the target schema (eg, using a left outer join) to retrieve the foreign key column based on the natural key.

[0130] Columns can simply be copied 1-1 between the source and target tables.

[0131] A column can be mapped to the type of the target column using a simple expression such as CAST.

[0132] Certain system columns (e.g., data source ID, date updated, or inserted data) can be named consistently and will use the same expressions.

[0133] In some embodiments, the mapping of one or more source tables to a target table is organized into work units. In the simple case, a mapping is a unit of work that resides in a folder or package. In some implementations, a folder can be a concept for grouping related mappings, variable definitions, sequence definitions, or processes. Similarly, a package can define the order in which these related ETL elements are executed.

[0134] In some embodiments, the type-subtype pattern can be implemented as multiple mappings that map a source table to a type and one or more subtype tables. These mappings can create a unit of work located in a folder or package. In addition, the table structure may require that the target table containing the type information be loaded first, and then all other tables are loaded, or they may be loaded in parallel.

[0135] After providing the extracted features and example ETL mappings 108 to the machine learning component 110, new ETL rules / mappings can be predicted to extract data from the source architecture 102 and load the data into the target architecture 104. In some embodiments, the solution framework can be defined with different modules to determine various aspects of the solution. For example, one or more of these modules can be designed to be flexible and configurable so that a specific solution can be defined for a specific source / target architecture. In some examples, decisions can be expressed as rules, and in some examples, decisions can be expressed as probabilities or weights.

[0136] Traditional ETL generators typically hard-code these rules. Some generators allow ETL teams to manually write or configure rules to adapt the ETL generator framework to specific situations. This approach has obvious advantages over traditional generators, but it still requires a lot of man-hours. In contrast, in various embodiments, for different aspects of the ETL process, the provided example ETL mapping 108 can be used to automatically (i.e., without human intervention) learn ETL rules and configurations. The process of automatically generating ETL rules can be broken down into multiple components, and each component can involve decisions that can be learned. The embodiment can then provide the output ETL rules 112 to the rule interpreter 114 to derive the output ETL mapping 116, which can be used to implement migration between architectures.

[0137] In some embodiments, the machine learning component 110 may include a plurality of modules that may have aspects learned based on features extracted from the source schema 102, the target schema 104, and the example ETL mapping 108. For example, learning may be accomplished using a supervised learning algorithm that learns a combination of schema, table, and column features that determine aspects of an ETL solution as observed from the example ETL mapping 108.

[0138] In many respects, learning is not universal or general. For example, a designer of an ETL solution (e.g., providing example ETL mappings 108) can customize learning based on the provided examples (and in some cases, provided metadata) to use one or more learning techniques to fit a specific problem.

[0139] In some embodiments, various preparations may be performed before learning the ETL rules. For example, a table may capture operational data such as the data source of a row, the date and time of the initial load, the date and time of the last update, the user ID of the person who last updated the record, etc. These columns are typically system columns that all or most tables have. In some cases, groups of tables may define common columns that are an indication of a shared design. Examples include a type column for a type-subtype pattern, an effective date and current flag column for a version record, or a common LVL column that indicates a hierarchy. <n>Columns. Frequently used columns in the schema can be converted into additional features (e.g., “isCommonColumnType_x”) that can be used by the machine learning component 110 to predict additional ETL rules during learning.

[0140] In another example, deriving information about the structure of the schema as an additional feature can help with learning. A classic difference is between a highly normalized operational schema and a denormalized and star-organized reporting schema (e.g., snowflake schema vs. star schema). This determination can often be made using an algorithm that can determine whether a schema is composed of stars. An example algorithm for making such a determination is provided in U.S. Patent Application Publication No. 2016 / 0078064 to Michael Sassin, entitled AUTOMATIC GENERATION OF LOGICAL DATABASE SCHEMAS FROMPHYSICAL DATABASE TABLES AND METADATA.

[0141] Related algorithms can classify star schema tables into facts, dimensions, hierarchies, bridge tables, lookup tables, etc. In most cases, the algorithm can use foreign key constraints to make these determinations. Naming conventions can also be learned, as well as the occurrence of certain columns or data types within columns, or the ratio between columns of type NUMBER and columns that are not numeric.

[0142] In some embodiments, to perform this type of learning, a larger star schema data set with pre-classified table types may be provided. Normalized schema tables may also be classified using similar techniques. In some implementations, the goal may be to identify model patterns or design prototypes. In other embodiments, this information may be provided as metadata or may otherwise be available to the learning component of the system.

[0143] In some embodiments, the ETL mapping is generated as a unit of work. For example, previously disclosed are techniques for identifying tables based on column mappings determined by an architecture mapping algorithm. In some embodiments, the unit of work defined by the architecture mapping is based on the unit of work defined by the example ETL mapping 108.

[0144] In some embodiments, the work units of the ETL solution can be classified. Each work unit can follow a previously disclosed solution pattern that provides a reusable solution for similar problems in a specific context. A variety of solution patterns have been established for denormalizing data, mapping type-subtype patterns, etc. Classification can be helpful as an additional derived feature because it can serve as a shortcut to more complex rules. In some embodiments, an algorithm is used to predict the pattern of the work unit. The pattern name can be provided to the machine learning component 110 as metadata.

[0145] In some embodiments, the learning algorithm can be implemented as a classification algorithm or a rule learning algorithm. In some cases, learning by example may be challenging when trying to learn complex decisions. Embodiments may also include supervised implementations in which the user is prompted to provide input (e.g., selection) based on the learning obtained. Example methods are further discussed in Dialog-Based Learning (DBL) for Adaptive Interface Agents and Programming-by-Demonstration Systems (Technical Report CMU-CS-93-175, Carnegie Mellon University, July 1993) by S. Bocionek and M. Sassin.

[0146] When learning a unit of work, join conditions are often needed to extract data sets such as from a star schema or any other suitable schema. For example, tables can be joined using conditions on columns defined using foreign key constraints between tables. The join can be an inner join or an outer join depending on the NOT NULL constraints on the foreign key columns. In some embodiments, variations of this standard approach can be learned based on the characteristics of the participating tables (e.g., columns participating in the join are surrogate keys, indexes, or just have fixed names, etc.).

[0147] When learning the work unit, the mapping expressions between the source architecture 102 and the target architecture 104 are also learned. The mapping expressions can be divided into several broad categories, including:

[0148] Mapping of primary key columns

[0149] Mapping of foreign key columns

[0150] Mapping of code columns

[0151] System column

[0152] Other columns

[0153] In some embodiments, the primary key columns are created using surrogate keys or by looking up the parent table (via a foreign key) to retrieve an existing primary key column. For example, the target mapping expression can be analyzed to make such a determination. The surrogate key value can be defined as a database sequence, which can be in the target mapping expression. If a natural key is used, then the expression can define the primary key column of the source table in the target mapping expression.

[0154] In some embodiments, a foreign key column in the source schema 102 is used to look up a value in a parent table in the source schema 102. A code column is transformed into a foreign key column in a similar manner using a lookup operation. In these examples, there is a set of well-established lookup or creation patterns that are not learned but provided by the ETL framework.

[0155] For example, a correlation expression can be defined by an example ETL mapping 108. For example, a common schema can be used when loading a dimension table into a star schema. The dimension table can define a surrogate key as a primary key and provide a natural key originating from the source system as a lookup key. In some cases, the dimension table can act as a lookup using a natural key to determine whether a dimension row has already been loaded, in which case the newly arrived data is treated as a change record that triggers a dimension update. Specific details (such as columns and actions and / or sequences for loading or updating dimensions) may vary depending on the implementation. For each instance of an ETL rule, the participating columns in the source and target tables can be identified, and the rules associated with their selection can be learned.

[0156] For example, assume that table S is mapped to target table T, which has a foreign key relationship with parent table T2. In order to populate the foreign key columns in T, source table S should have one or more natural key columns for the relationship that are also available in T2. ​​In some embodiments, the learning algorithm can use the extracted features of the columns used in the example ETL mapping 108 (e.g., extracted by feature extraction 106) to learn conventions about foreign column names. In most cases, some level of regularity is used in the naming convention (e.g., the name uses the name of the parent table and uses the same prefix or suffix). In some cases where the source or target table does not follow the convention that can be expressed by the features, column-level metadata can be added to supplement the architecture.

[0157] In some embodiments, the lookup of the coded value follows similar rules as in the case of a foreign key lookup. The potential difference is that the mapped value is retrieved instead of the key column of the row. In other respects, the code column can follow similar processing as the key column.

[0158] In some embodiments, system columns also follow rules, such as copying a source value with a constant name, inserting a function (e.g., system time), a constant (e.g., CURRENT_FLG is set to 'Y'), or looking up a table based on a provided identity (e.g., the user ID of the last updated user). These rules can be learned based on the example ETL mapping 108.

[0159] In some embodiments, there may be other columns that do not fall into one of the above categories. In many cases, these columns may be mapped 1-1 from the source columns (e.g., the data is simply passed to the target columns). Common variations are to check the source data for NULL values ​​and provide default values, or to truncate or coerce the source columns into different types. These rules may also be learned based on the example ETL mapping 108.

[0160] In some implementations, some ETL rules / mappings may be complex and therefore may not be conducive to an example-based learning environment. In these cases, the learning system may skip the complex rules / mappings and the user may manually generate these rules / mappings to achieve the final ETL solution. In other words, some embodiments that include complex rules and / or mappings between source and target architectures may rely on a mix of automated rule and mapping generation and manual rule and mapping generation.

[0161] Consider a use case that includes a source schema, a target schema, example ETL mappings, and learned ETL rules. For example, the appendix contains the SQL for generating the source and target schemas (with parent and child schemas). An implementation of the source schema may be a data warehouse staging schema. An implementation of the target schema may be a 3NF enterprise data warehouse schema with change logging.

[0162] Embodiments may implement ETL rules and / or mappings where a source table is used to load one or more target tables. Record versioning may be used to look up foreign keys of a schema using natural keys and version numbers. Metadata elements may be provided, including filter conditions. Common mapping expressions may be used for system columns of source and target schemas. Some example ETL mappings include simple 1-1 column mappings.

[0163] In some embodiments, the source and target schemas may include more than 500 tables. Such ETL projects may require a lot of manpower, time (e.g., years), and resources to perform manually. However, many mappings can be characterized by a small number of ETL patterns (e.g., 90% of mappings can be characterized by 2 or 3 ETL patterns). In some embodiments, an embodiment can learn about 100 low-complexity rules. In the considered use case, the source and target schemas follow a defined naming convention.

[0164] Components of an example ETL system include an ETL tool, an ETL specification, an ETL transformer, an ETL generator, a rule interpreter (e.g., similar to Figure 1 Rule interpreter 114) and learning components (e.g., similar to Figure 1 In a given implementation, a rule may be a decision and condition with an associated expression. In some cases, a template or a portion of a template may be linked to a rule.

[0165] Embodiments of ETL tools can express ETL components and mappings. For example, an ETL tool can transform definitions into executables that run against source and target systems. An example of an ETL tool used in some embodiments may be an Open Data Integrator ("ODI") ETL tool. An embodiment of an ETL specification may be a human-readable and machine-readable text format definition that specifies an ETL solution. In some embodiments, the ETL specification is largely independent of the target ETL system. An example ETL specification includes ETL specification used by Data Warehouse Factory ETL Transformers ("DWF ETL").

[0166] Figure 4 An example ETL specification according to an embodiment is illustrated. The specification 400 may include definitions for one or more of the following: folder and artifact names; references to source and target tables; filters, joins, lookups, unions, and aggregations; their target columns and expressions; additional instructions on how to extract and load data from and to the tables; the specification 400 is defined in XML format, but any other suitable format (e.g., JavaScript Object Notation ("JSON"), YAML non-markup language ("YAML"), plain text, etc.) may be implemented.

[0167] Embodiments of the ETL converter can convert the ETL specification into a definition used by the target ETL tool. DWF ETL implements an example converter for generating ODI definitions. Embodiments of the ETL generator can typically generate the ETL specification based on the source and target table definitions. For example, the generated ETL specification can be based on exported metadata, features extracted from the source architecture and / or the target architecture, and / or explicit metadata. In some embodiments, the ETL specification can be provided, for example, based on automated generation, manual generation, or a combination of these.

[0168] Embodiments of the learning component may define specific decisions in the context of an ETL framework (e.g., ETL specifications and other ETL configurations) and an ETL generator. Figure 1 As disclosed by the machine learning component 110 of the present invention, the learning component can generate ETL rules based on example ETL mappings. In this considered use case, the example ETL mappings can be expressed as ETL specification definitions and / or architecture metadata. The learning can be specific to (one or more) different rules / mappings / data groups of the architecture. In some embodiments, the learning component can be configured to implement multiple machine learning algorithms, each of which is configured to perform a specific learning task (and make specific machine predictions) for a different rule / mapping / data group.

[0169] Embodiments include utilizing machine learning to generate a portion of an architecture mapping between a source architecture and a target architecture. For example, based on example ETL mappings provided to a learning component, a portion of the mapping can be learned and predicted. In some implementations, the portion can be a large proportion of data from the source architecture (e.g., 90% of a table / column). Some implementations include utilizing manual mappings to generate a comprehensive set. In some embodiments, sufficient example ETL mappings can be provided to allow a learning component to generate a comprehensive set of mappings.

[0170] Various aspects of ETL learning performed by the learning component include the details of the ETL solution. Figure 5 The folder structure of the ETL tool (e.g., ODI) according to an embodiment is illustrated. Structure 500 includes folder 502, which can be the name of the ODI folder and / or subfolder used in mapping and other ETL processes. An embodiment of the folder naming scheme can include constants (e.g., "LOAD_DI_HDWF ...", as shown in the elements of folder 502) and / or can include the name of the source / target table. Structure 500 can also include ETL elements 504, which can be packages, mappings, and processes. These ETL elements 504 can also include the name of the source / target table. Mappings can usually be grouped based on the source table or target table related to the mapping.

[0171] Figure 6 The mapping structure according to an embodiment is illustrated. For example, the mapping structure 600 includes a table 602 used in a given mapping. These tables are usually defined in the "schema" of the ETL. The table can be referenced via a foreign key in the mapping structure. A filter 604 can be applied to the table 602, for example, to a source table. Example filter functions can include row filtering and column filtering based on rules / conditions. The filter (e.g., the rules / conditions of the filter) can be provided by metadata, or can be provided by other mechanisms. The filtering function can be a part of the data manipulation and / or transformation stage in the ETL processing flow. For example, the filter 604 can represent a data manipulation used to map source data from a source data table to a target data table in a related mapping.

[0172] Embodiments include performing joins to manipulate, transform, and / or filter data (e.g., source data). Joins may be performed using foreign key constraints. Lookup 606 may be a reference to a related target data table in a mapping structure. Lookup 606 may be similarly defined based on foreign key constraints. Mapping structure 600 may represent a structure for mapping between a source schema and a target schema according to various embodiments.

[0173] Figure 7 The mapping expressions according to the embodiments are illustrated. For example, the structure 700 includes mapping expressions 702, 704, 706 and 708. The mapping expressions can map source data (e.g., from a data table) to target attributes and data tables. In some cases, source columns can be mapped to target columns with similar names, as shown in mapping expression 704. In some cases, system functions such as sysdata can be used in mapping expressions, as shown in mapping expression 708.

[0174] In some cases, variables may be used in mapping expressions, such as variables that capture strings beginning with ":" or "#", as shown in mapping expression 702. In some cases, functions may be used to wrap columns, such as CAST, NVL, or some other custom function, as shown in mapping expression 706. Embodiments also include aggregate functions within mapping expressions, such as SUM, AVG, Group By, etc.

[0175] With reference to the considered use case and the source and target schemas disclosed in the appendix, the implemented learning component can learn multiple trends in the data based on the source schema, the target schema, the extracted features, and the example mappings (disclosed further below). System columns in the table can be used systematically in the source and target tables. For example, data records can be added, updated, or deleted, users can be created and updated, tagging functions can remain consistent, and so on.

[0176] Embodiments of the learning component can learn expressions for columns with the same name that appear in a large number of source and / or target tables. In some cases, the column mapping can define one or several variations (e.g., based on type) for each column name. The learning component can specifically determine the criteria for such variations.

[0177] Embodiments may include selecting data to train a learning component. For example, selecting mappings to the same target column in example ETL mappings may improve learning outcomes for some embodiments. Decisions regarding column mappings may be performed based on metadata directly related to the participating columns, and in some cases the metadata may be extended to include metadata for the table for the column, for example to include information such as a name or schema type. Example column metadata for the use case considered includes the name, characteristics related to the name (such as a prefix or suffix), the column data type, additionally added metadata elements, and the like.

[0178] Figure 8 800 may include a source schema, a target schema, and extracted feature data relevant to the use case under consideration. Column 802 depicts source column attributes, while column 804 depicts column target attributes. Features 806 depict features extracted from the source schema, such as prefixes and suffixes of the relevant column names. Mapping expressions 808 depict mapping expressions to be learned, for example, by a learning component.

[0179] In an embodiment, multiple example mapping expressions may be provided to the learning component in a use case. The example mapping expression "trg.update_date = src.update" appears 5 times in the use case data (e.g., the source schema and target schema in the appendix). In addition, the example mapping expression "trg.update_date = CAST(src.update, DATE)" appears 4 times in the use case data. The source column update_date uses the Date type in the first example mapping set and the Timestamp type in the second example mapping set. Accordingly, the CAST function is used in the mapping expression of the second example set. In the use case shown, the goal of the learning component may be to learn when similar mapping expressions are applied and to select one of these mapping expressions (e.g., whether to CAST) when appropriate.

[0180] Embodiments of the learning component may implement the ID3 decision tree learning algorithm or any other suitable learning algorithm to achieve the desired learning / prediction. For example, the learning component may learn the logic:

[0181] if exist(src.update_date)&type(src.update_date)='Timestamp'&

[0182] exist(trg.upate_dt)

[0183] then trg.update=CAST(src.upate_date,DATE)

[0184] else trg.update_dt=src.upate_date end

[0185] In an example use case, an embodiment of a learning component may learn / predict a mapping for a system column trg.update_dt. The learning / prediction may be based on similar example mappings provided to the learning component (e.g., the mapping expressions above for "trg.update" and "srcupdate"). Additionally, in a use case, an embodiment may learn / predict logic to determine when to select a mapping expression with a "CAST" function and when to select other mapping expressions. The logic of the learned mapping expression is similar to the logic in the provided example mapping expression, i.e., CAST is used when the data type is a timestamp. Similarly, other use cases may provide other example mapping expressions that illustrate patterns between a source schema and a target schema, where the learning component may learn when and how to apply similar mapping expressions to other aspects of the data to predict ETL rules.

[0186] Considering the use case, the learned ETL rules can be interpreted by the rule interpreter. For example, the rule interpreter can interpret the learned logic and generate an ETL mapping according to the rules (and the logic represented) based on the source schema, the target schema, and the extracted features. Specifically, an ETL mapping can be generated for the trg.update_dt column. The generated mapping can then be used in the ETL solution and can ultimately be used to implement data migration from the source schema to the target schema.

[0187] Although traditional ETL is considered in the embodiments, stream processing ETL can also be implemented. For example, Apache Kafka can be included in various embodiments, where the message stream can be used by various stream processing frameworks, such as Kafka Streams application programming interface ("API"), Apache Storm, Apache Spark, Apache Samza, etc. In some embodiments, stream processing can be implemented instead of batch processing to implement the various solutions disclosed.

[0188] Fig. 9 Illustrated is an example function for learning by example extraction, transformation and loading rules according to an example embodiment. In one embodiment, the following Fig. 9 The functions of the processor are implemented by software stored in a memory or other computer-readable or tangible medium and executed by a processor. In other embodiments, each function may be performed by hardware (e.g., by using an application specific integrated circuit ("ASIC"), a programmable gate array ("PGA"), a field programmable gate array ("FPGA"), etc.), or by any combination of hardware and software.

[0189] At 902, a plurality of features may be extracted from the source schema and the target schema, the features including at least columns of a plurality of tables of the source schema and the target schema. In some embodiments, the features may also include identifiers of the columns, metadata of the columns, foreign keys of the tables, and other suitable features. In some examples, the metadata of the columns in the source schema may indicate a relationship between the column and other columns / tables of the source schema, or may indicate other suitable data relationships.

[0190] At 904, example ETL mappings can be provided to the machine learning algorithm, where the example ETL mappings are definitions for extracting data from one or more tables of a source schema and loading the extracted data into one or more tables of a target schema. The example ETL mappings can be mapping expressions that define a relationship between one or more source columns of a source table and a target column of a target table.

[0191] In some embodiments, the example ETL mapping may include a mapping expression that implements at least one of a column name suffix or a column name prefix. In some embodiments, the example ETL mapping may include a mapping expression that implements one or more functions of the mapping expression applied to source columns.

[0192] At 906, using the machine learning algorithm and based on the source schema, the target schema, and the extracted features, one or more ETL rules can be predicted that define logic for extracting data from the source schema and loading the extracted data into the target schema. In some embodiments, the predicted ETL rules can define logic represented by an example ETL mapping.

[0193] In an embodiment, the example ETL mapping represents a relationship between a source schema and a first set of columns of a target schema. The predicted ETL rule may define logic for a relationship between a source schema and a second set of columns of the target schema, where the second set of columns is different from the first set of columns. In an embodiment, the additional ETL mapping includes a mapping expression for the second set of columns of the target schema based on the predicted ETL rule.

[0194] At 908, the predicted ETL rules can be interpreted. For example, a rule interpreter can interpret the logic defined in the ETL rules to generate a mapping expression between the source architecture and the target architecture. The interpretation can be based on the source architecture, the target architecture, and the extracted features. In some embodiments, an ETL specification that defines the details of the ETL solution is implemented, and the interpretation can be based on the ETL specification.

[0195] At 910, additional ETL mappings can be generated based on the predicted ETL rules, the source schema, the target schema, and the extracted features, the additional ETL mappings providing additional definitions for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema. For example, a rule interpreter can be used to generate the additional ETL mappings. In some embodiments, the additional ETL mappings can be mapping expressions that define a relationship between one or more source columns of a source table and a target column of a target table.

[0196] In an embodiment, the example ETL mapping may include a mapping expression that implements a function of a first type applied to a column of a source schema to load a first column of a target schema. The additional ETL mapping may include a mapping expression that implements a function of a first type applied to the column of the source schema to load a second column of the target schema.

[0197] The embodiments learn extraction, transformation, and loading mappings by example. The relationship between the source architecture and the target architecture can often be complex, making the application of machine learning to ETL non-simple. The embodiments utilize feature extraction of the source data architecture and the target data architecture to provide data points at a granular level to the machine learning algorithm, which enables improved learning. In addition, the example mapping between the source data architecture and the target data architecture is provided to the machine learning algorithm defined according to the ETL framework. As a result, the machine learning algorithm can extrapolate trends based on the relationship shown between the target architecture and the source architecture in the example. These extrapolated trends can be defined at a detailed level that is conducive to machine prediction because they are defined at a granular level specific to the architecture. Subsequently, the machine learning algorithm can predict new ETL rules that can be used to complete the migration of data from the source to the target. The rule interpreter can then be used to generate ETL mappings based on the predicted new ETL rules.

[0198] The features, structures, or characteristics of the present disclosure described throughout this specification may be combined in any suitable manner in one or more embodiments. For example, the use of "one embodiment", "some embodiments", "an embodiment", "certain embodiments", or other similar language throughout this specification refers to the fact that a particular feature, structure, or characteristic described in conjunction with that embodiment may be included in at least one embodiment of the present disclosure. Therefore, the appearance of the phrases "one embodiment", "some embodiments", "certain embodiment", "certain embodiments", or other similar language throughout this specification does not necessarily all refer to the same set of embodiments, and in one or more embodiments, the described features, structures, or characteristics may be combined in any suitable manner.

[0199] Those of ordinary skill in the art will readily appreciate that the embodiments discussed above may be practiced with steps in a different order and / or with elements in a configuration different from that disclosed. Thus, while the present disclosure contemplates the embodiments outlined, it will be clear to those skilled in the art that certain modifications, variations, and alternative configurations will be apparent while still within the spirit and scope of the present disclosure. Therefore, in order to determine the scope of the present disclosure, reference should be made to the appended claims.

[0200] appendix:

[0201] Source table:

[0202]

[0203]

[0204]

[0205]

[0206]

[0207]

[0208] Target table - Parent:

[0209]

[0210]

[0211]

[0212]

[0213] Target table-sub:

[0214]

[0215] < / n>

Claims

1. A method for learning extract, transform, and load (ETL) mapping by example, the method comprising: Extracting multiple features from a source schema and a target schema, wherein the features include at least columns of multiple tables of the source schema and the target schema; providing an example ETL mapping to a machine learning algorithm, wherein the example ETL mapping includes a definition for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema; predicting one or more ETL rules using the machine learning algorithm and based on the source schema, the target schema, and the extracted features, the one or more ETL rules defining logic for extracting data from the source schema and loading the extracted data into the target schema, wherein at least one predicted ETL rule includes conditional logic that selectively applies a function to data values ​​extracted from the source schema, the function transforming the data values; as well as generating additional ETL mappings based on the predicted ETL rules, the source schema, the target schema, and the extracted features, the additional ETL mappings providing additional definitions for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema, wherein the definition for at least one additional ETL mapping selectively applies the function to the data extracted from the source table of the source schema when loading the extracted data into the target table of the target schema.

2. The method of claim 1, wherein: The example ETL mapping and additional ETL mappings include mapping expressions that define a relationship between one or more source columns of a source table and a target column of a target table.

3. The method of claim 1, wherein: The example ETL mapping includes a mapping expression that implements at least one of a column name suffix or a column name prefix.

4. The method of claim 1, wherein: The at least one predicted ETL rule applies the function to a source column.

5. The method of claim 1, wherein The example ETL mapping includes a mapping expression that implements the function for loading a first column of the target schema, and The additional ETL mapping includes a mapping expression that implements the function for loading a second column of the target schema.

6. The method of claim 1, wherein: The predicted ETL rules define the logic represented by the example ETL mapping.

7. The method of claim 6, wherein: The example ETL mapping represents a relationship between the source schema and a first set of columns of the target schema, and The predicted ETL rule defines logic of a relationship between the source schema and a second set of columns of the target schema, and the second set of columns is different from the first set of columns.

8. The method of claim 7, wherein: The additional ETL mapping includes a mapping expression for the second set of columns of the target schema based on the predicted ETL rule.

9. The method of claim 1, wherein: The plurality of features include foreign keys for tables of the source schema and the target schema.

10. The method of claim 1, wherein: The plurality of features include metadata for columns of the source schema, the metadata indicating relationships between the columns of the source schema.

11. A non-transitory computer readable medium having stored thereon instructions that, when executed by a processor, cause the processor to learn an extract, transform, and load (ETL) mapping by example, the learning comprising: Extracting multiple features from a source schema and a target schema, wherein the features include at least columns of multiple tables of the source schema and the target schema; providing an example ETL mapping to a machine learning algorithm, wherein the example ETL mapping includes a definition for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema; predicting one or more ETL rules using the machine learning algorithm and based on the source schema, the target schema, and the extracted features, the one or more ETL rules defining logic for extracting data from the source schema and loading the extracted data into the target schema, wherein at least one predicted ETL rule includes conditional logic that selectively applies a function to data values ​​extracted from the source schema, the function transforming the data values; as well as generating additional ETL mappings based on the predicted ETL rules, the source schema, the target schema, and the extracted features, the additional ETL mappings providing additional definitions for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema, wherein the definition for at least one additional ETL mapping selectively applies the function to the data extracted from the source table of the source schema when loading the extracted data into the target table of the target schema.

12. The computer-readable medium of claim 11, wherein: The example ETL mapping includes a mapping expression that implements at least one of a column name suffix or a column name prefix.

13. The computer-readable medium of claim 11, wherein: The at least one predicted ETL rule applies the function to a source column.

14. The computer readable medium of claim 11, wherein The example ETL mapping includes a mapping expression that implements the function for loading a first column of the target schema, and The additional ETL mapping includes a mapping expression that implements the function for loading a second column of the target schema.

15. The computer-readable medium of claim 11, wherein: The predicted ETL rules define the logic represented by the example ETL mapping.

16. The computer-readable medium of claim 15, wherein: The example ETL mapping represents a relationship between the source schema and a first set of columns of the target schema, and The predicted ETL rule defines logic of a relationship between the source schema and a second set of columns of the target schema, and the second set of columns is different from the first set of columns.

17. The computer-readable medium of claim 16, wherein: The additional ETL mapping includes a mapping expression for the second set of columns of the target schema based on the predicted ETL rule.

18. A system for learning extract, transform, and load (ETL) mappings by example, comprising: A processing device in communication with a memory device, the processing device configured to learn an extract, transform, and load (ETL) mapping by example, the learning comprising: Extracting multiple features from a source schema and a target schema, wherein the features include at least columns of multiple tables of the source schema and the target schema; providing an example ETL mapping to a machine learning algorithm, wherein the example ETL mapping includes a definition for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema; predicting one or more ETL rules using the machine learning algorithm and based on the source schema, the target schema, and the extracted features, the one or more ETL rules defining logic for extracting data from the source schema and loading the extracted data into the target schema, wherein at least one predicted ETL rule includes conditional logic that selectively applies a function to data values ​​extracted from the source schema, the function transforming the data values; and generating additional ETL mappings based on the predicted ETL rules, the source schema, the target schema, and the extracted features, the additional ETL mappings providing additional definitions for extracting data from one or more tables of the source schema and loading the extracted data into one or more tables of the target schema, wherein the definition for at least one additional ETL mapping selectively applies the function to the data extracted from the source table of the source schema when loading the extracted data into the target table of the target schema.

19. The system of claim 18, wherein: The at least one predicted ETL rule applies the function to a source column.

20. The system of claim 18, wherein The example ETL mapping includes a mapping expression that implements the function for loading a first column of the target schema, and The additional ETL mapping includes a mapping expression that implements the function for loading a second column of the target schema.

Images