Patent Application
20200117745
Some embodiments described herein relate to root cause analysis, and in particular, to recommending potential root causes of failure using a visual timeline.
Programmers such as Information Technology (IT) personnel, product developers, product support personnel and others often receive requests for data export or data import across multiple environments. The request to export may be a request for a one-time extract of data, be based on a specified condition on a set of tables in a database, be a request for an extract of master data, be a request for a complete setup with dependency with an intent to import to a different environment, and the like.
The data is typically stored in databases. Different categories of data are in databases, such as master data, transaction data, and configuration data. The data may be encrypted to provide security and the encryption keys used to encrypt and decrypt the data varies between environments.
The programmers typically create a tool to perform the export and import of data so that subsequent export or import of the same data is quickly performed. However, schema changes (e.g., adding a column of data, dropping a column of data, adding a new dependency to an existing table, etc.) can occur any time during upgrades of products. This makes any existing tool written for performing the export/import of data of a product outdated or forced to upgrade to accommodate the changes in schema.
Some embodiments are directed to a method by a computer of a computing system for exporting data from a source database or importing data to a destination database. The method includes receiving a request to one of export data independently from a source database or import data independently to a destination database. Responsive to when the request is a request to export data from the source database, data to export and data to be excluded from exporting is determined. The source database is parsed to determine a location of the data to export and a location of the data to be excluded from export. The location of the data to export includes at least one extraction pointer to at least one column and a range of rows associated with each of the at least one column and the location of the data to be excluded from export comprises at least one exclusion pointer to at least one exclusion column Source database tables associated with the data to export and export relationships the data to export has with other data including the data to be excluded from export based on metadata is determined. The source database tables include a source database master table and source database configuration tables. An export java script object notation (JSON) object is created that comprises the export relationships, the location of the data to export, the location of data to be excluded from exporting, input values for the source database tables, and that designates the source database tables as export visitable tables. Based on the export JSON object, the export visitable tables to support export of the data to export are prepared and the data to export is extracted to a designated format by extracting the data while preserving the export relationships the data to export has with the export visitable tables.
The method for preparing an export visitable table may include creating a structured query language (SQL) statement from the export JSON object to read data from the source database. An input list is created for the export JSON object. Values of columns of the data to be extracted are binded. Execution of the SQL statement is initiated to extract the data from the source database into the export JSON configuration object. The extracted data is added to the export JSON object.
The method may further include decrypting an encrypted column to be exported using a unique key. The column is extracted after decryption. The column is encrypted after extraction using the unique key.
Corresponding computers configured to import and export data are disclosed. In some embodiments, the computer includes a processor and a memory coupled to the processor and storing computer readable program code that when executed by the processor causes the processor to perform operations includes receiving a request to one of export data independently from a source database or import data independently to a destination database. Responsive to when the request is a request to import data to the destination database, a pointer to data to be imported to a destination database is received. The data to be imported is moved to a predefined location. Import visitable tables associated with the data to import and import relationships the data to import has with other data based on metadata are determined. An import JSON object is created that includes the import relationships, the location of the data to import, identification of the visitable tables, and an identification of the destination database. For each of the visitable tables, a constraint on the visitable table is selected from a constraint cache. The constraint is applied on the data to import. A SQL query is created for the data to be imported. Creating the SQL query includes obtaining value meta data for creating the SQL query for each column of data in the data to be imported. Responsive to the column having a sequence enabled field, a sequence is set for the column. Responsive to the column being a foreign key, the value for the column is obtained from a reference table. Responsive to the column being configured for a replace list, the value is replaced in accordance with the import JSON object. Responsive to the column not having a sequence enable field or not being a foreign key or not being configured for the replace list, values of the data to be imported for the column are used. The SQL query is executed to import the data to be imported to the destination database.
The operations may further include determining whether the data to import is to be updated or is to be imported without being updated and responsive to the data to import is to be updated, obtain the constraint used for deciding the update. An update SQL query is created responsive to the data to import is to be updated and a SQL prepared query is created responsive to the data to import is to be imported without updating the data.
The operations may further include determining for each column in the data to import if the column is encrypted. Responsive to the column being imported is encrypted, a cryptographic key for the column is obtained. The column is decrypted using the cryptographic key prior to executing the SQL query. A determination is made as to whether the column is to be encrypted after being imported. Responsive to the column is to be encrypted after import, a second cryptographic key is obtained for encrypting the column after the column is imported. The column is encrypted after the column is imported using the second cryptographic key.
The operations may further include determining if a column of data in the data to export is sensitive data. Responsive to the column of data is sensitive data, the column of data is excluded from being exported. The operations to determine if a column of data is sensitive data include parsing each column of data in the data to export. For each column, the parsed data is compared to a pattern of sensitive data. Responsive to the parsed data matching the pattern of sensitive data, the column is designated as having sensitive data.
It is noted that aspects of the inventive concepts described with respect to one embodiment may be incorporated in different embodiments although not specifically described relative thereto. That is, all embodiments or features of any embodiments can be combined in any way and /or combination. These and other objects or aspects of the present inventive concepts are explained in detail in the specification set forth below.
Advantages that may be provided by various of the concepts disclosed herein include synchronizing environments where data is stored with different cryptographic keys, defining relations between tables independent of databases where the data is stored, adding or removing tables to an export list without having to entirely reprogram an extraction tool, and automating the import and export of data.
Other methods, devices, and computer program products, and advantages will be or become apparent to one with skill in the art upon review of the following drawings and detailed description. It is intended that all such additional systems, methods, or computer program products and advantages be included within this description, be within the scope of the present inventive concepts, and be protected by the accompanying claims.
The accompanying drawings are included to provide a further understanding of the disclosure and are incorporated in and constitute a part of this application. In the drawings:
Embodiments of the present inventive concepts now will be described more fully hereinafter with reference to the accompanying drawings. Throughout the drawings, the same reference numbers are used for similar or corresponding elements. The inventive concepts may, however, be embodied in many different forms and should not be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the inventive concepts to those skilled in the art. Like numbers refer to like elements throughout.
It will be understood that, although the terms first, second, etc. may be used herein to describe various elements, these elements should not be limited by these terms. These terms are only used to distinguish one element from another. For example, a first element could be termed a second element, and, similarly, a second element could be termed a first element, without departing from the scope of the present inventive concepts. As used herein, the term “or” is used nonexclusively to include any and all combinations of one or more of the associated listed items.
The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting. As used herein, the singular forms “a”, “an” and “the” are intended to include the plural forms as well, unless the context clearly indicates otherwise. It will be further understood that the terms “comprises,” “comprising,” “includes” or “including” when used herein, specify the presence of stated features, integers, steps, operations, elements, or components, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, components, or groups thereof.
Unless otherwise defined, all terms (including technical and scientific terms) used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this disclosure belongs. It will be further understood that terms used herein should be interpreted as having a meaning that is consistent with their meaning in the context of this specification and the relevant art and will not be interpreted in an idealized or overly formal sense unless expressly so defined herein.
The inventors have realized problems with the conventional approaches of importing and exporting data. These problems include specific configurations may vary between environments, schema changes (e.g., adding a column, dropping a column, adding a new dependency to an existing table etc.) can occur at any time during upgrade of products, which makes any existing tool written for import or export of data outdated or forced to upgrade to accommodate the new changes in schema or relation. Additionally, columns in tables that are specific to environments should not be exported so that while doing an import it should not override the existing data. Other problems realized by the inventors will become apparent in the description herein.
Some embodiments described herein provide methods or computer systems for automating the export of data from a source database and importing of data to a destination database. According to some embodiments, a request to one of export data independently from a source database or import data independently to a destination database. Responsive to when the request is a request to export data from the source database, data to export and data to be excluded from exporting is determined. The source database is parsed to determine a location of the data to export and a location of the data to be excluded from export. The location of the data to export includes at least one extraction pointer to at least one column and a range of rows associated with each of the at least one column and the location of the data to be excluded from export comprises at least one exclusion pointer to at least one exclusion column Source database tables associated with the data to export and export relationships the data to export has with other data including the data to be excluded from export based on metadata is determined. The source database tables include a source database master table and source database configuration tables. An export java script object notation (JSON) object is created that comprises the export relationships, the location of the data to export, the location of data to be excluded from exporting, input values for the source database tables, and that designates the source database tables as export visitable tables. Based on the export JSON object, the export visitable tables to support export of the data to export are prepared and the data to export is extracted to a designated format by extracting the data while preserving the export relationships the data to export has with the export visitable tables. Responsive to when the request is a request to import data to the destination database, a pointer to data to be imported to a destination database is received. The data to be imported is moved to a predefined location. Import visitable tables associated with the data to import and import relationships the data to import has with other data based on metadata are determined. An import JSON object is created that includes the import relationships, the location of the data to import, identification of the visitable tables, and an identification of the destination database. For each of the visitable tables, a constraint on the visitable table is selected from a constraint cache. The constraint is applied on the data to import. A SQL query is created for the data to be imported. Creating the SQL query includes obtaining value meta data for creating the SQL query for each column of data in the data to be imported. Responsive to the column having a sequence enabled field, a sequence is set for the column Responsive to the column being a foreign key, the value for the column is obtained from a reference table. Responsive to the column being configured for a replace list, the value is replaced in accordance with the import JSON object. Responsive to the column not having a sequence enable field or not being a foreign key or not being configured for the replace list, values of the data to be imported for the column are used. The SQL query is executed to import the data to be imported to the destination database.
As further described in
Initially, at operation 200, a request is received to export data independently from a source database or import data independently to a destination database. The request may be from a program, from a user, from a customer, etc. At operation 202, a determination is made as to whether the request is to export data or import data. The determination may be made by parsing the request to determine words in the request and compare the words to a list of words that indicate export and/or import.
At operation 204, when the request is to export data, the source database is determined. The identification of the source database may be in the request, may be identified by a product in the request where the product has a product database that stores the export data, etc. The identification may be performed by parsing the request to find the identification, searching a look-up table to find the source database associated with the export data, and the like.
At operation 206, the data to export and the data to be excluded from exporting are determined. The data to export may be specified in the request, may be specified in a product database, etc. The data to be excluded may be specified in the request, may be specified in the source database, may be classified as sensitive data, may be specific to the source database, etc. Turning to
Returning to
Turning to
At operation 212, a Java script object notation (JSON) object is created for export of the data. The JSON object may include the relationships, the location of the data to export, the location of the data to be excluded from exporting, input values for the source database tables, and a designation of the source database tables that are to be visitable tables.
At operation 214, each of the visitable tables to support export of data to be exported are prepared. Turning to
Turning to
Returning to
Returning to
Turning to
At operation 224, a JSON object for importing the data is created. The JSON object may include the relationships, the location of the data to import (e.g., a pointer to the predetermined location), identification of the visitable tables, and an identification of the destination database. The identification of the destination database may be specified in the request to import data.
Turning to
At operation 226, a constraint on each visitable table is selected from a constraint cache. The constraint is applied on the data to import. At operation 228, a SQL query is created for the data to import. Creating the SQL query includes obtaining value metadata for creating the SQL query for each column of data in the data to be imported. Turning to
Turning to
Turning to
Returning to
An example of using the methods and computers described is illustrated in
By applying a visitor design pattern to the tables 1200-1220 that are required for export, the tables needed to export the data can be considered as visitable objects. The class which is processing the exported data can considered as visitors. Considering tables as visitable objects solves the problem schema change impacts. All the tables to be visited tables results in the export of data to be readily configurable. This gives the flexibility of adding/removing tables to the export list.
To extract each bank table with dependency, then output of one table becomes input to extract from dependent table. The visitor pattern applied fits for both simple and complex requirements. When a product update adds another relation (e.g., TTPCONFIG 1222 to ARBANKINFO table 1210, then addition of the table to the visitable list will export the new relation without any code change. The combination of the visitor design pattern with the JSON object to SQL language as described above provides a solution to the problem identified of schema changes during an upgrade to a product.
For example, the JSON object format may be used to map the where clause fields with actual values. An example of JSON object format with the format illustrated below results in an easy to bind value. Any new column can be added to “where clause” without any change but with just configuration. On the other hand, when a SQL query is used, then a code change is required to bind the values. With format such as the JSON object format, the “json to sql” language will automatically bind the values along with actual data type taken from meta data cache. A sample of codes generated is illustrated below.
The phases in exporting include:
When using formats such as a JSON format, the same functionality can be extended to other SQL, NoSQL, Excel, etc. formats which are storing data in tabular formats irrespective of database being used.
Developing a JSON language with following methods is required:
During the operations to export and import, the following items in memory cache need to be maintained at the initialization stage:
1) Meta data of tables used:
Additionally, a custom constraint configuration option is used in one embodiment. A developer should be able to add additional constraint if required in the request received by the computer described herein. Custom constraint configuration may be in the form of JSON. The JSON Parser will convert the custom constraint configuration into an equivalent database constraint format. In the event that some tables in the source or destination database do not have Referential integrity (e.g., GUIDs) or primary key, the custom constraint will help us to maintain the data integrity during export/import of data. This handles situations where specific configurations vary between the source and destination environments.
An example of adding a custom referential constraint for a table and column is provided to add a referential constraint on column BANKID of table ARBRANDINFO. Similarly, Unique or Primary constraints can be added on the table. Any number of constraints can be added.
Continuing the list of items in in memory cache that need to be maintained at the initialization stage:
Configuring optionally skips the columns specified from export when cryptographic keys are not available.
Parser should have two methods:
either master key or bank key) then this solves the problem when a unique key used to identify a configuration or master data across issuer may not be same as a sequence ID generated by the database. Any new column if converted as encrypted by during product upgrade, then no code change is required by the tool, leading to automated conversion and automated import and export of data.
The phases of execution to export are described above. An example of data after export is:
An example of the class diagram for the export is illustrated in
Other examples of code generated by the conversion engine 100 for an embodiment of employee data is illustrated below:
There is no constraint defined at the table level for the tables considered in this example.
Visitable configuration (applicable for both export and import):
Configuration for identifying encryption columns (applicable for both export and import):
Configuration for any binary data column (in this example no binary columns like blob) (applicable for both export and import):
File name for the exported file (applicable for both export):
Configuration of custom constraint for each table (applicable for both export and import):
Configuration for JSON SQL (applicable for both export):
Turning now to
In the embodiment shown in
Thus, example systems, methods, and non-transitory machine readable media for reducing occurrences have been described. The advantages that may be provided by various of the concepts disclosed herein include synchronizing environments where data is stored with different cryptographic keys, defining relations between tables independent of databases where the data is stored, adding or removing tables to an export list without having to entirely reprogram an extraction tool, and automating the import and export of data.
As will be appreciated by one of skill in the art, the present inventive concepts may be embodied as a method, data processing system, or computer program product. Furthermore, the present inventive concepts may take the form of a computer program product on a tangible computer usable storage medium having computer program code embodied in the medium that can be executed by a computer. Any suitable tangible computer readable medium may be utilized including hard disks, CD ROMs, optical storage devices, or magnetic storage devices.
Some embodiments are described herein with reference to flowchart illustrations or block diagrams of methods, systems and computer program products. It will be understood that each block of the flowchart illustrations or block diagrams, and combinations of blocks in the flowchart illustrations or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general-purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified in the flowchart or block diagram block or blocks.
These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer readable memory produce an article of manufacture including instruction means which implement the function/act specified in the flowchart or block diagram block or blocks.
The computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions/acts specified in the flowchart or block diagram block or blocks.
It is to be understood that the functions/acts noted in the blocks may occur out of the order noted in the operational illustrations. For example, two blocks shown in succession may in fact be executed substantially concurrently or the blocks may sometimes be executed in the reverse order, depending upon the functionality/acts involved. Although some of the diagrams include arrows on communication paths to show a primary direction of communication, it is to be understood that communication may occur in the opposite direction to the depicted arrows.
Computer program code for carrying out operations described herein may be written in an object-oriented programming language such as Java® or C++. However, the computer program code for carrying out operations described herein may also be written in conventional procedural programming languages, such as the “C” programming language. The program code may execute entirely on the user's computer, partly on the user's computer, as a standalone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer. In the latter scenario, the remote computer may be connected to the user's computer through a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider).
Many different embodiments have been disclosed herein, in connection with the above description and the drawings. It will be understood that it would be unduly repetitious and obfuscating to literally describe and illustrate every combination and subcombination of these embodiments. Accordingly, all embodiments can be combined in any way or combination, and the present specification, including the drawings, shall be construed to constitute a complete written description of all combinations and subcombinations of the embodiments described herein, and of the manner and process of making and using them, and shall support claims to any such combination or subcombination.
In the drawings and specification, there have been disclosed typical embodiments and, although specific terms are employed, they are used in a generic and descriptive sense only and not for purposes of limitation, the scope of the inventive concepts being set forth in the following claims.
