Extract-transform-load script generation

One embodiment provides a computer implemented method, including: receiving, from a user, a natural language query for data contained within at least one data repository; identifying at least one concept from the natural language query, wherein the at least one concept includes an entity and an intent; identifying a plurality of datasets satisfying the natural language query by querying the at least one data repository utilizing the at least one concept; ranking the dataset based on relevance to the query; generating an extract-transform-load script that extracts, transforms, and loads a dataset selected by the user from the plurality of datasets; and retrieving data included in the dataset utilizing the extract-transform-load script, wherein the retrieving includes returning the data to the user.

BACKGROUND

Digital data is stored in many different data repositories, for example, local data repositories, remote data repositories, cloud data repositories, and the like. The use of different data repositories provides different advantages. For example, a local data repository may provide the ability to more easily secure the data. However, a remote or cloud data repository allows for the data to be accessed from any location. Thus, users and entities employ a variety of different data repositories for storing and managing data. Additionally, within a single data repository different portions of a dataset may be stored in different sub-repository locations. For example, a cloud data repository may have tens, hundreds, or even thousands of different sub-repository locations. How the data is stored within each of these sub-repository locations is generally determined by policies or rules of the data repository administrator.

BRIEF SUMMARY

In summary, one aspect of the invention provides a computer implemented method, including: receiving, from a user, a natural language query for data contained within at least one data repository; identifying at least one concept from the natural language query, wherein the at least one concept includes an entity and an intent; identifying a plurality of datasets satisfying the natural language query by querying the at least one data repository utilizing the at least one concept; generating an extract-transform-load script that extracts, transforms, and loads a dataset selected by the user from the plurality of datasets; and retrieving data included in the dataset utilizing the extract-transform-load script, wherein the retrieving includes returning the data to the user.

Another aspect of the invention provides an apparatus, including: at least one processor; and a computer readable storage medium having computer readable program code embodied therewith and executable by the at least one processor; wherein the computer readable program code is configured to receive, from a user, a natural language query for data contained within at least one data repository; wherein the computer readable program code is configured to identify at least one concept from the natural language query, wherein the at least one concept includes an entity and an intent; wherein the computer readable program code is configured to identify a plurality of datasets satisfying the natural language query by querying the at least one data repository utilizing the at least one concept; wherein the computer readable program code is configured to generate an extract-transform-load script that extracts, transforms, and loads a dataset selected by the user from the plurality of datasets; and wherein the computer readable program code is configured to retrieve data included in the dataset utilizing the extract-transform-load script, wherein the retrieving includes returning the data to the user.

An additional aspect of the invention provides a computer program product, including: a computer readable storage medium having computer readable program code embodied therewith, the computer readable program code executable by a processor; wherein the computer readable program code is configured to receive, from a user, a natural language query for data contained within at least one data repository; wherein the computer readable program code is configured to identify at least one concept from the natural language query, wherein the at least one concept includes an entity and an intent; wherein the computer readable program code is configured to identify a plurality of datasets satisfying the natural language query by querying the at least one data repository utilizing the at least one concept; wherein the computer readable program code is configured to generate an extract-transform-load script that extracts, transforms, and loads a dataset selected by the user from the plurality of datasets; and wherein the computer readable program code is configured to retrieve data included in the dataset utilizing the extract-transform-load script, wherein the retrieving includes returning the data to the user.

DETAILED DESCRIPTION

The illustrated embodiments of the invention will be best understood by reference to the figures. The following description is intended only by way of example and simply illustrates certain selected exemplary embodiments of the invention as claimed herein. It should be noted that the flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, apparatuses, methods and computer program products according to various embodiments of the invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises at least one executable instruction for implementing the specified logical function(s).

The problem with data being stored across multiple data repositories is that it can be difficult to retrieve. For example, if a user is attempting to retrieve particular data, the user may provide a query that is intended to retrieve the desired data. Conventional systems utilize an extract-transform-load (ETL) script that extracts the data from the data repository or location, runs transformations on the extracted data to transform it into the required form, and then load the transformed data into the destination location. However, each ETL script is unique for a particular dataset. For example, the extraction script must be unique for the data repository where the data is stored. As another example, the transformation script is unique based upon both the data and the desired transformation. Thus, the data scientist, data steward, enterprise application developer, or the like, must generate a unique ETL script for the desired data and desired transformation. This script generation can become very complicated and time-consuming particularly when the data of the desired dataset is stored across multiple data repositories or sub-repository locations.

Accordingly, an embodiment provides a system and method for generating an extract-transform-load script for retrieving data that is spread across multiple data repositories and providing it to a user. The system receives a natural language query from a user that is requesting data that is contained within at least one data repository, but is likely spread across multiple data repositories or locations. From the natural language query, the system identifies at least one concept that includes an entity and an intent. The intent can be identified by enriching the natural language query utilizing ontologies and/or glossaries.

Using the identified concept, the system identifies a plurality of datasets that satisfy the natural language query. Generally, the system ranks the datasets based upon relevance to the query and provides the ranked list of the datasets to the user. The user can then select the desired dataset, combination of datasets, or portions of different datasets for retrieval. Whichever dataset is selected by the user, even if a combination of datasets, portions of datasets, or the like, will be referred to herein as the desired dataset or selected dataset for ease of readability. However, this is not intended to limit the scope of this disclosure in any way. Once the user has selected the desired dataset, the system generates an ETL script to retrieve the desired dataset from the data location(s) where the data of the dataset is stored. The generated ETL script can then be used to retrieve the data from the data location(s), transform the retrieved data into the desired form, and load the transformed data into the destination data location or otherwise return the data of the dataset to the user.

Thus, the described system and method provides at least the start of an artificial intelligence powered automated extract-load-transform and data processing platform. The system provides an easy technique for discovering, transforming, and sharing data for and across different groups of users, including those users who do not have a strong knowledge or background in the data management domain. In other words, the system provides a technique that allows any user to discover and share data without the user needing a strong knowledge in data management to find and access the data which may be stored across multiple data repositories.

The system is able to identify data intent(s) from an input query provided by the user and enrich the identified intent using a glossary or ontology of business concepts. The query can be provided in a natural language format which is used by the system to perform a contextual dataset search and does not require the user, or the system, to have a knowledge of the datasets or the location of the desired data before the query is provided. The system can utilize the query to quickly search and easily identify datasets that may be responsive to the query even if the datasets are found or stored across multiple data repositories. For example, the system is able to discover datasets located across different data sources, for example, over a hybrid/multi-cloud environment, that match the data intents identified in the query. In determining a relevance of a dataset to the query, the system can apply access and governance policy constraints and output a ranked listing of the datasets based upon a relevance to the received query. Once the datasets are identified and selected by the user, the system can automatically generate a valid extract-transform-load script in order to provide the selected dataset in a desired location. Additionally, the system can utilize user feedback to improve future executions.

Such a system provides a technical improvement over current systems for ETL script generation. Instead of the time-consuming manual techniques of ETL script generation of conventional systems, the described system and method provides a technique for automatically generating the ETL script where the generated ETL script is unique for the desired data, transformation, and destination data location. This automatic script generation is particularly useful when the data of a desired dataset is stored across multiple data repositories, data sources, or data locations. The user simply provides a natural language query that identifies the dataset the user is looking to retrieve and, from the query, the system is able to identify possible datasets that satisfy the query and generate an ETL script to retrieve a dataset selected by the user. Thus, the described system provides a technical improvement to the data storage and retrieval technical field by providing a system and method that is more efficient and that does not require extensive manual programming and found in conventional systems. Additionally, the system and method retrieves datasets that satisfy the natural language query of the user.

FIG.1illustrates a method for generating an extract-transform-load script for retrieving data that is spread across multiple data repositories and providing it to a user. At101the system may receive, from a user, a natural language query for data contained within at least one data repository. For ease of readability this disclosure will generally use the example of multiple data repositories, locations, or sources. However, the described system and method can also be utilized if the data is stored within a single data repository. In an example, the multiple data repositories may be located across different data sources of a hybrid or multi-cloud environment. In other words, the datasets may be stored in a hybrid or multi-cloud environment. Generally, in such an environment, the user does not have control over the exact location of the data and, therefore, the environment will be stored in multiple locations within the hybrid or multi-cloud environment. The query is received at the system and it does not matter if the user knows where the data is stored. In other words, the system is able to identify the location or locations of the data and does not need to be directed to the correct data repository by the user. This is particularly useful since the data of a resulting dataset may be distributed across multiple data sources.

The natural language query may be received from a user or client who is attempting to retrieve data related to the query. For example, a user may provide the query “Retrieve data for all employees hired after the 1stof January 2020.” As seen from this example query, the query is provided in natural language as opposed to machine language. In other words, a natural language query includes a query that is provided in an ordinary human language, for example, as if the user were talking to another person. The term query will be used throughout. The natural language query may be provided in any form, for example, as audible input, text input, haptic input, gesture input, or the like.

At102the system identifies at least one concept from the natural language query. A concept includes an entity and an intent. In this case, entity refers to an entity as found in a sentence, phrase, or other linguistic element. Entities can be identified using entity and/or concept extraction techniques. For example, the system may employ an information extractor, semantic analysis technique, syntactic analysis technique, natural language parsing technique, or the like, to identify the entities contained within the natural language query. An intent may be explicit within the natural language query. In this case, the system may employ a concept extraction technique. However, an intent may also be implicit in the natural language query. To identify an implicit intent, the system enriches the entities or concepts identified within the query using a glossary or ontology of business concepts. The system can then append the identified intent, identified entities if not already identified in the query, and any other concepts not already explicitly identified in the query, to the query. This enriched query will then be used by the system in further steps.

At103the system determines if one or more datasets can be identified that satisfy the natural language query. In other words, the system queries the possible data locations with the enriched query to determine if any datasets may provide a responsive output to the query. A dataset may include data from one or more data repositories that are responsive to the query. Each possible dataset may not be completely unique from every other possible dataset. In other words, datasets may include data that is included in other possible datasets. To identify the plurality of datasets, the system utilizes the enriched query to identify relevant datasets. In identifying relevant datasets, the system may also take into account user constraints, dataset requirements, business context, policy and governance constraints, hardware and network constraints, and the like. These constraints, requirements, or the like, may change the relevancy of some datasets. The system may perform the querying while taking into account access and governance policies, at both a local and global level. Taking into account access and governance policies allows the system to ensure security of data that may be included in a dataset and prevents the sharing of data across data sources that do not have access to the data.

One technique for finding relevant datasets faster is to make the data within the data sources easier to search. To facilitate this, the system may perform metadata extraction on the stored data. The system may also annotate the data with business concepts. The metadata extraction and annotation allows the system to search the metadata and/or annotations instead of having to access every piece of datum, thereby providing a faster search. The metadata extraction and annotation may be performed a single time and may not be performed at the time of receipt of the query. Rather, this metadata extraction and annotation may be performed when the data is stored, at some predetermined time intervals, or the like. Thus, when the enriched query is utilized to perform a search, the search may be performed on the data, metadata, annotations, or a combination thereof.

If no datasets satisfying the query, particularly in view of user provided constraints, requirements, or the like, can be identified at103, the system may take no action at104. In other words, the system may determine that none of the datasets are policy compliant datasets. Alternatively, the system may provide an indication to a user that no datasets could be identified. The system may also provide an explanation for why a dataset could not be identified. For example, if the user provided constraints are too restrictive to identify a possible dataset, the system may notify the user of this reason for the inability to identify a dataset.

If, on the other hand, at least one dataset satisfying the query can be identified at103, the system may generate an extract-transform-load (ETL) script at105. The ETL script extracts the data from the data location(s), transforms the data into the desired form, and loads the data into an identified destination, for example, a data location designated by the user providing the query. Before generating the script, the system needs to identify which dataset to generate the script for. Accordingly, the dataset(s) are presented to the user for the user to select the desired dataset. In presenting the dataset(s) to the user, the system may rank the datasets based upon a relevance to the user query. Ranking the datasets includes not only ranking the tables of the datasets, but also ranking columns within the tables.

One technique that may be used in discovering a relevant dataset is to utilize a probabilistic inference network based weighting and ranking mechanism. Such a mechanism allows for ranking the datasets based upon their relevance to the identified data intents or entities of the query. This technique for ranking the datasets includes utilizing an unsupervised model that is built over the dataset to rank the dataset based upon any user provided constraints, requirements, or other information relevant to identifying a relevancy of the dataset. Using the unsupervised model, the system computes a probability that a given dataset is useful with respect to the user requirements or input query. Since the dataset contains both table level metadata and column level metadata, the system can rank both the table level metadata and column level metadata with respect to the input query.

The unsupervised model may have multiple parts, for example, a query independent information portion and a query dependent portion. The query independent portion is the portion of the algorithm that takes into account information that is not based upon the query, for example, the popularity of a particular dataset, how recent the data within a dataset is, costs, and the like. The query dependent portion is the portion of the algorithm that takes into account information that is based upon the query, for example, the relevancy of tables and columns to intents within the query, entities included in the query, and the like. In other words, the query dependent portion may take the information available about the data and match it against information included in the query to determine a relevancy of the data to the query. The algorithm then takes the query independent portion result and the query dependent portion result and combines the information into a single value for each dataset. The datasets can then be ranked based upon these values now corresponding to the dataset. The result is a ranked list of policy compliant datasets that can be presented to the user for selection of a desired dataset.

Once the user selects a dataset, which may be a single dataset, portions of different datasets, a subset of a single dataset, or any combination thereof, the system can then generate the ETL script. Generation of the ETL script may be in a fully automated manner or may be in a user supervised manner. In the user supervised manner, the system generates recommendations for the ETL script while the user is writing or programming the ETL script. The user can then decide whether to accept, modify, or reject a recommendation. In the fully automated ETL script generation manner, the system automatically generates the ETL script. Generation of the ETL script is performed while ensuring that best practices are followed, any security, storage, or access constraints are maintained, and the like. Even when the data included in a selected dataset is spread across multiple data locations, the system is still able to generate an ETL script. The ETL script specifies the source, execution, and target bindings for extracting, transforming, and loading the selected datasets.

Once the ETL script is generated and/or approved by the user, the system utilizes the ETL script to retrieve the data included in the selected dataset at106. Retrieving the data includes returning the results of the query to the user at a desired location identified by the user. Each ETL script generation and data retrieval can be used to further train the system to improve subsequent ETL script generations and data retrievals. To facilitate this learning, the system allows the user to provide feedback from the entity and intent identification step to the ETL script generation step. In other words, the user can provide feedback for the entity and intent identification, dataset identification, and ETL script generation steps, in either the user supervised manner or the fully automated manner. Thus, the system becomes better at performing any of these steps with each iteration of the process.

FIG.2illustrates an overall system architecture of the described system and method. The system receives a constrained natural language query201from a user202. The system also receives business requirements203, user constraints, or the like, from the user. The system identifies entities and intents204from the query. In identifying the entities and intents the system may enrich the entities and intents utilizing a glossary or ontology of business concepts. As noted inFIG.2, this step also allows user feedback. Using the identified entities and intents, the system performs a dataset search subject to the identified constraints205. The search is performed on data spread across multiple data sources206. To perform the search more quickly, the system may extract metadata from the data207and also perform annotations on the dataset with business concepts208. The search may then be performed on the data, metadata, and/or annotations. The identified datasets are ranked based upon a relevance of the dataset to the query. Additionally, in performing the dataset identification the system selects those datasets that satisfy any access and policy constraints, user constraints, filters, or the like. The ranked list is then provided to a user for selection of the desired dataset(s). The user can also provide feedback on the dataset identification.

Once a dataset is selected by the user, the system generates an ETL script. The process of generating an ETL script can either be performed using an ETL script assistant209, automated ETL script generator210, or combination thereof. The ETL script assistant209can generate recommendations while a user is writing ETL jobs. In other words, the assistant works with the user as the user is writing an ETL script. As the user is writing the ETL script, the ETL script assistant209makes recommendations for the script. Additionally, the ETL script assistant209may suggest data cleaning. The automated ETL script generator210utilizes artificial intelligence to automate the generation of the ETL script. In generating the ETL script the automated ETL script generator210ensures that best practices are followed and that any network, storage, access, security, or other constraints are maintained. As noted, user feedback can be provided for either of these two processes. Once the ETL script is generated, the system utilizes an ETL script executor211to execute the generated script to extract the data of the selected dataset from the data location(s), transform the extracted data into the desired form, and load the transformed data into a destination data location identified by the user.

As shown inFIG.3, computer system/server12′ in computing node10′ is shown in the form of a general-purpose computing device. The components of computer system/server12′ may include, but are not limited to, at least one processor or processing unit16′, a system memory28′, and a bus18′ that couples various system components including system memory28′ to processor16′. Bus18′ represents at least one of any of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, and a processor or local bus using any of a variety of bus architectures. By way of example, and not limitation, such architectures include Industry Standard Architecture (ISA) bus, Micro Channel Architecture (MCA) bus, Enhanced ISA (EISA) bus, Video Electronics Standards Association (VESA) local bus, and Peripheral Component Interconnects (PCI) bus.

Characteristics are as follows:

Service Models are as follows:

Deployment Models are as follows:

Workloads layer90provides examples of functionality for which the cloud computing environment may be utilized. Examples of workloads and functions which may be provided from this layer include: mapping and navigation91; software development and lifecycle management92; virtual classroom education delivery93; data analytics processing94; transaction processing95; and extract-transform-load script generation and execution96, as described here throughout.