SYSTEMS AND METHODS FOR PERFORMING DATA FIXES

Embodiments of the present disclosure include techniques for performing data fixes. In one embodiment, a list of schemas and access information for the schemas is received from a schema manager. The schemas are batched for processing. During processing, schemas in a batch are processed in parallel. Processing includes applying pre-configured SQL commands. If the data fix is successful, applications may be deployed. If the data fix is not successful, application deployments may be blocked.

BACKGROUND

The present disclosure relates generally to software system, and in particular, to systems and methods for performing data fixes.

Databases store a wide variety of data, some of which may be sensitive and critical for business operations. If a business or security requirement arises such that a particular subset of the data stored in database needs to be changed, deleted, or modified, then a data fix mechanism needs to be in place to ensure that certain data requirements are satisfied. Data fixes in modern computer system environments is a complex problem where multiple limitations and challenges need to be considered for designing the solution. The conventional way of doing data fixes is for a system administrator with security privileges to manually login to a database and execute generic SQL commands to implement the fixes. However, this is a cumbersome process as there can be thousands of customer schemas in production across various regions and data centers.

The present disclose is directed to techniques for improving data fixes in database systems.

DETAILED DESCRIPTION

Described herein are techniques for performing data fixes. In the following description, for purposes of explanation, numerous examples and specific details are set forth in order to provide a thorough understanding of some embodiments. Various embodiments as defined by the claims may include some or all of the features in these examples alone or in combination with other features described below and may further include modifications and equivalents of the features and concepts described herein.

FIG.1illustrates a computer system100for performing data fixes according to an embodiment. Features and advantages of the present disclosure address the technical challenges of fixing data errors in a computer system. Additionally, embodiments of the disclosure address the challenges of deploying new applications so that the data is clean upon deployment. Embodiments of the present disclosure may be implemented in software as described herein and executed on one or more processors102. As illustrated inFIG.1, computer system100includes one or more databases103. Computer system100may support multiple tenants. A tenant is a group of users who share a common access to one or more software systems with specific privileges to the software instance. With a multitenant architecture, a software application is designed to provide every tenant a dedicated share of the instance, including its data, configuration, user management, tenant individual functionality and non-functional properties. Sensitive portions of tenant data may be singled out for special secure handling. Accordingly, software applications101a-nmay implement a wide range of functionalities that involve storing data for a particular tenant running the application in database103.

Here, such tenant data is stored in one or more databases in separate schemas for each tenant. For purposes of the present disclosure, a schema is software structure that includes tables, views, connection credentials, and may include other database artifacts (e.g., constraints, stored procedures and the like). As illustrated inFIG.1, database(s)103include separate schemas110a-nfor storing data for different tenants. In some instances, different tenant schemas may have a same schema structure but different connection credentials for different tenants to ensure the security of each tenant's data. For example, in some embodiments the data stored in each schema110a-nis master data corresponding to each tenant. Master data comprises a uniform set of identifiers and attributes that uniquely describe each tenant for processing operations that identify an identity of a particular tenant. For example, tenants may have their own uniquely identifiable master data, which is used in transaction processing and other operations where the tenant's identity is vital for further processing or for identifying the source of an action taken in the computer system. Master data of the tenants may be stored in dedicated database schemas of a region-specific data center, for example. If a business or security requirement arises such that a particular subset of the master data stored in the customer's dedicated schema needs to be changed, deleted, or modified, the data fix mechanisms of the present disclosure may ensure that the requirements are satisfied. Accordingly, each tenant may have their own schema in a database to provide clear data isolation between tenants. simultaneously providing the cost benefit of having multiple tenants housed in a single database. However, while this approach provides logical data isolation to the tenants in the ecosystem, other embodiments may place tenant data in separate databases for physical data isolation.

Computer system100may be used to modify data stored in a plurality of tenant schemas automatically. Computer system100includes a data fix software functionality (or just, “Data Fix”)111for modifying data, such as tenant data, automatically. For instance, Data Fix111may receive a list of a plurality of schemas110a-nstored in database103that are to have their tenant data updated. The list of schemas to be processed, as well as access information, may be obtained from a schema manager114, which includes security information115about the schemas (e.g., access credentials) as well as other information about the schemas116. The schema manager114may comprise a separate encrypted object storing administrative credentials for the schemas110a-n, for example. The following is an example of an administrative credential:

Data Fix111may comprise a plurality of pre-configured SQL commands (“SQL cmds”)112for performing data modification on schemas110a-n. Data Fix111may further include a batching code113to divide schemas110a-ninto a plurality of batches117A-N, each comprising a portion of the schemas (e.g., schema110i-j) from the database(s)103. For instance, for the plurality of batches, computer system100may automatically, without user intervention, apply the pre-configured SQL commands112to the schemas110i-jin a batch in parallel. The commands112applied to the schemas return results indicating that the schemas were either successfully modified or unsuccessfully modified, for example. Schema manager114advantageously provides access to schemas110a-nto apply the pre-configured SQL commands112without exposing the administrative credentials to a user. This advantageously increases the security of the system.

Features and advantages of the present disclosure include starting or blocking the deployment of an application based on the successful or unsuccessful automated modification of tenant data. In some embodiments, data fixes may be performed prior to deployment of application code. For example, code changes to an application may be deployed to a production environment (where users run the code) on a periodic basis. When the system initiates an application deployment, a data fix may be performed. An application deployment may be blocked from starting when a result of the data fix indicates a schema was unsuccessfully modified. On the other hand, the application deployment may start when a result of the data fix indicates the batches of schemas were successfully modified. In some embodiments, the application deployment is executed through one or more containers and data fix may be executed as a job separate from the execution of the containers. Containers are packages of software that contain the elements to run an application in the target environment. A container may comprise one runtime unit for a service/application (e.g., multiple containers of a single application for load balancing). In this way, containers virtualize deployment and allow applications to run in a private data center, public cloud, or even other target systems, for example. In this example, applications101a-nrun on containers150a-m. Each container may include one or more instances of an application, for example, and the same application be executed across multiple containers. An application running on a container without the latest code changes may need to be upgraded to capture new features, bug fixes, or the like. When deployment of a new container running an application with the latest code changes is initiated, data fixes according to the techniques described herein may be performed. If the data fixes are successful, the new container is deployed and made available to users. However, if the data fixes are unsuccessful, the deployment of the new container and application may be blocked until the data fixes are implemented. Automating the data fix process and connecting the results to the deployment process can dramatically improve the speed of deployments, such that only deployments with unsuccessful data fixes may require manual data fix procedures, for example.

FIG.2illustrates a method according to an embodiment. At201, a list of a plurality of schemas is received. The plurality of schemas store data for a plurality of tenants, where data corresponding to different tenants are stored in different schemas in one or more databases. At202, the plurality of schemas is divided into a plurality of batches, where each batch comprises a portion of the schemas. At203, a plurality of pre-configured SQL commands are applied to the schemas in each batch in parallel. At204, results are returned in response to executing the pre-configured SQL commands. The results indicate that the schemas were either successfully modified or unsuccessfully modified. At205, the system repeats steps203and204if more batches need to be processed. If there are no more batches, then the process moves to206. At206, an initiation of an application deployment is received. When the results indicate a schema was unsuccessfully modified, the application deployment is blocked from starting. However, when the results indicate the batches of schemas were successfully modified, the application deployment starts.

FIG.3illustrates a data fix system according to an embodiment, including a solution for master data multi-schema data fixes in a clustered environment.FIG.3illustrates components of the solution. A job301includes batch manager304, batch executor305, and data fix job302coupled to schema manager303. Schema manager303and batch executor305interface with database cluster310comprising databases311-312, which each include master data schemas313. The following is one example of a master data schema illustrating identifiers and attributes:

In this example, schema manager303is a secure cloud/object store that can manage tenants and their related metadata in a separate schema or a filesystem. It is the single source for getting all information related to the tenant, such as the database in which the schema of the tenant is stored, schema username, schema password, and other related information. Schema manager303returns the list of available schemas and their metadata to the data fix job302for further processing.

FIG.4Aillustrates an example of schema batch processing according to an embodiment. Batch Manager304uses the list of schemas401fetched by schema manager303to split the schemas into batches403-405. The batches may be of fixed size in some embodiments. The batch size may further be pre-configured in the code through a constants file402(described in more detail below). Constants file402may include the size of all the schema batches created (here, 4), which may be the same except for a last batch that may contain fewer schemas than the pre-configured batch size. Once the batches are created, the batches are passed to the main data fix job302for further processing.

To reduce the time taken for data fixes, data fixes may be done in parallel across schemas and databases in a batch (e.g., 100 schemas done 25 at a time as a batch, where each of the 25 schemas have their own thread). This advantageously reduces the time taken for the overall procedure and takes advantage of multi-threaded processors. Referring again toFIG.3, batch executor305takes a batch of schemas as input and returns the result of applying a data fix on every schema in the batch in parallel. For all schemas in the batch, a new thread is spawned, and the data fix is applied simultaneously using the threads available to it. Each thread makes a connection to one of the databases311-312using the schema information fetched from the schema manager and then applies the SQL commands pre-configured in the constants file.

Data fix job302fetches the list of schemas received from the schema manager303and sends it to batch manager304, which returns the schema batches for which a data fix needs to be applied. It also reads the Batch Size, Data Fix SQL Queries and SQL Query parameters that are pre-configured in the constants file.

Once the batches are created by batch manager304, the batches are iterated over (e.g., serially) and a call is made to batch executor305for each batch. Batch executor305processes the input and returns the result of the data fix for every batch. These results are collected and processed further to check if the data fix has failed for any schema in all the batches. As mentioned above, if a data fix procedure for all schemas has been successful, data fix job302is deemed to be successful, and a new application container deployment is allowed to start. Otherwise, data fix job302fails, which blocks the new application container deployment from starting. Consequently, a manual data fix may be performed for the schemas where data fix has failed. After the manual data fix has been applied on the failing schemas, the application and data fix job302can be restarted to make the new application deployment come up.

As mentioned above, embodiments of automatic execution of SQL commands stored in a constants file (402inFIG.4) may include multi-schema and multi-databases, parallel data fixing, retry logic, and separate data fix and application containers, for example. Rather than manually applying data fixes to all the customer schemas in production, certain embodiments read pre-configured SQL commands to be applied from the constants file, and then apply them to the schemas in the environment. The constants file stores values needed for the application to run, low level programming artifacts, and the location where to write data manipulation scripts to be run against master data schemas (e.g., the location of the file where the data fix scripts are stored). The pre-configured SQL commands in the constants file comprise DML commands needed for the data fix. In some embodiments, only one constants file is used because all master data schemas are the same. The SQL commands are pre-configured in the constants file, and are in the form of Prepared SQL statements. Prepared SQL statements are predefined queries comprising placeholders for input values and are not recompiled on each execution, which prevents SQL Injection attacks, for example.

While performing a data fix for a particular schema in the ecosystem, the data fix can fail due to several issues such as failing to acquire DB Lock required to perform the data fix, network and environment stability, and other factors. To overcome the above issue and add a layer of robustness to the overall solution, certain implementations may include retry logic (RL)350implemented in batch executor305, for example. The list of schemas for whom the data fix has failed is collected for every iteration. Then in the next iteration, the data fix procedure is reapplied to each of the failing schemas. Data Fix procedure re-tries are done for up to some predetermined number re-tries. For example, after the 3 re-tries, the final status of each schema's data fix is collected and returned to data fix job302for further processing. This adds a layer of robustness to the solution and ensures application does not crash due to momentary issues in the environment or database.

As illustrated in cluster350, currently running application containers320are unaffected by the data fix process. However, new application containers321may be automatically blocked by batch manager304until a data fix has been successfully completed.

FIG.4Billustrates an example process flow for a data fix job according to an embodiment. At401, data fix queries, parameters, batch size, and a tenant list are read by the data fix job302. If the list is not empty, schema manager303returns the tenant list and schema information to data fix job302at402. At403, tenants are split into batches by batch manager304. At404, data fix job302sends the batches to batch executor305, which spawns threads for each tenant schema in a particular batch at405-406and may perform up to N (e.g., N=3) re-tries at407-408to obtain a successful result, for example. At409, results are collected and sent to the data fix job302, which collects results for the batches at410. At411, the results for all batches are processed. In this example, if there are any unsuccessful results, the job fails at412, and no action is taken on cluster350(no new container applications are created). However, if the results are successful at413, data fix job302passes and new applications are deployed at415and old versions are deleted at416.

FIG.5illustrates hardware of a special purpose computing system500configured according to the above disclosure. The following hardware description is merely one example. It is to be understood that a variety of computers topologies may be used to implement the above described techniques. An example computer system510is illustrated inFIG.5. Computer system510includes a bus505or other communication mechanism for communicating information, and one or more processor(s)501coupled with bus505for processing information. Computer system510also includes memory502coupled to bus505for storing information and instructions to be executed by processor501, including information and instructions for performing some of the techniques described above, for example. Memory502may also be used for storing programs executed by processor(s)501. Possible implementations of memory502may be, but are not limited to, random access memory (RAM), read only memory (ROM), or both. A storage device503is also provided for storing information and instructions. Common forms of storage devices include, for example, a hard drive, a magnetic disk, an optical disk, a CD-ROM, a DVD, solid state disk, a flash or other non-volatile memory, a USB memory card, or any other electronic storage medium from which a computer can read. Storage device503may include source code, binary code, or software files for performing the techniques above, for example. Storage device503and memory502are both examples of non-transitory computer readable storage mediums (aka, storage media).

In some systems, computer system510may be coupled via bus505to a display512for displaying information to a computer user. An input device511such as a keyboard, touchscreen, and/or mouse is coupled to bus505for communicating information and command selections from the user to processor501. The combination of these components allows the user to communicate with the system. In some systems, bus505represents multiple specialized buses for coupling various components of the computer together, for example.

Computer system510also includes a network interface504coupled with bus505. Network interface504may provide two-way data communication between computer system510and a local network520. Network520may represent one or multiple networking technologies, such as Ethernet, local wireless networks (e.g., WiFi), or cellular networks, for example. The network interface504may be a wireless or wired connection, for example. Computer system510can send and receive information through the network interface504across a wired or wireless local area network, an Intranet, or a cellular network to the Internet530, for example. In some embodiments, a front end (e.g., a browser), for example, may access data and features on backend software systems that may reside on multiple different hardware servers on-prem531or across the Internet530on servers532-534. One or more of servers532-534may also reside in a cloud computing environment, for example.

FURTHER EXAMPLES

Each of the following non-limiting features in the following examples may stand on its own or may be combined in various permutations or combinations with one or more of the other features in the examples below. In various embodiments, the present disclosure may be implemented as a system, method, or computer readable medium.

In one embodiment, the present disclosure includes a method of modifying data stored in a plurality of schemas comprising: receiving a list of a plurality of schemas, the plurality of schemas storing data for a plurality of tenants, wherein data corresponding to different tenants are stored in different schemas in one or more databases; dividing the plurality of schemas into a plurality of batches, each batch comprising a portion of the schemas; for the plurality of batches: automatically, without user intervention, applying a plurality of pre-configured SQL commands to the portion of the schemas in parallel; and returning a plurality of results in response to executing the pre-configured SQL commands, the plurality of results indicating that the portion of the schemas were either successfully modified or unsuccessfully modified; and receiving an initiation of an application deployment, wherein the application deployment is blocked from starting when the plurality of results indicates at least one schema was unsuccessfully modified, and wherein the application deployment starts when the plurality of results indicates the plurality of batches were successfully modified.

In another embodiment, the present disclosure includes a computer system comprising: at least one processor; at least one non-transitory computer readable medium storing computer executable instructions that, when executed by the at least one processor, cause the computer system to perform a method of modifying data comprising: receiving a list of a plurality of schemas, the plurality of schemas storing data for a plurality of tenants, wherein data corresponding to different tenants are stored in different schemas in one or more databases; dividing the plurality of schemas into a plurality of batches, each batch comprising a portion of the schemas; for the plurality of batches: automatically, without user intervention, applying a plurality of pre-configured SQL commands to the portion of the schemas in parallel; and returning a plurality of results in response to executing the pre-configured SQL commands, the plurality of results indicating that the portion of the schemas were either successfully modified or unsuccessfully modified; and receiving an initiation of an application deployment, wherein the application deployment is blocked from starting when the plurality of results indicates at least one schema was unsuccessfully modified, and wherein the application deployment starts when the plurality of results indicates the plurality of batches were successfully modified.

In another embodiment, the present disclosure includes a non-transitory computer-readable medium storing computer-executable instructions that, when executed by at least one processor, perform a method of modifying data, the method comprising: receiving a list of a plurality of schemas, the plurality of schemas storing data for a plurality of tenants, wherein data corresponding to different tenants are stored in different schemas in one or more databases; dividing the plurality of schemas into a plurality of batches, each batch comprising a portion of the schemas; for the plurality of batches: automatically, without user intervention, applying a plurality of pre-configured SQL commands to the portion of the schemas in parallel; and returning a plurality of results in response to executing the pre-configured SQL commands, the plurality of results indicating that the portion of the schemas were either successfully modified or unsuccessfully modified; and receiving an initiation of an application deployment, wherein the application deployment is blocked from starting when the plurality of results indicates at least one schema was unsuccessfully modified, and wherein the application deployment starts when the plurality of results indicates the plurality of batches were successfully modified.

In one embodiment, the list of the plurality of schemas is received from a schema manager, the schema manager comprising a separate encrypted object storing administrative credentials for the plurality of schemas, the schema manager providing access to the plurality of schemas to apply the plurality of pre-configured SQL commands without exposing the administrative credentials to a user.

In one embodiment, automatically applying a plurality of pre-configured SQL commands to the portion of the schemas in parallel comprises: generating a thread for each schema of the portion of the schemas; connecting, by each thread, to one or more databases using the administrative credentials from the schema manager; and applying, by each thread, the plurality of pre-configured SQL commands to a particular one of the portion of the schema.

In one embodiment, the data stored in each schema is master data corresponding to each tenant, and wherein the master data comprises a uniform set of identifiers and attributes that uniquely describe each tenant for processing operations that identify an identity of a particular tenant.

In one embodiment, the plurality of schemas has a same structure of schemas comprising tables and views, and wherein different schemas have different connection credentials.

In one embodiment, the pre-configured SQL commands are predefined queries comprising placeholders for input values and are not recompiled on each execution.

In one embodiment, the pre-configured SQL commands are SQL data manipulation language (DML) commands that modify data and do not modify a structure of the schema.

In one embodiment, the pre-configured SQL commands are stored in a single file, the file further comprising values for running the application.

In one embodiment, the application deployment is executed through one or more containers and said method of modifying data is executed as a job separate from the execution of the one or more containers.

In one embodiment, the above techniques further comprising, before receiving the initiation of the application deployment, and when one of plurality of results indicates that one or more schemas were unsuccessfully modified, automatically re-applying the plurality of pre-configured SQL commands to the one or more schemas.

In one embodiment, the received list of a plurality of schemas is a pre-determined list of schemas.

In one embodiment, the plurality of schemas is stored in a same database.

In one embodiment, each schema is stored in a different database.