Integrated database table access

A method may include intercepting a database query from a client to a database. The database query may include a first identifier associated with a first database table. A second database table stored at the database may be determined to correspond to the first database table. The database query may be reconstructed by at least replacing the first identifier with a second identifier associated with the second database table. The reconstructed database query may be executed by at least sending, to the database, the reconstructed database query. Related systems and articles of manufacture, including computer program products, are also provided.

TECHNICAL FIELD

The subject matter described herein relates generally to database processing and more specifically to cross system integration.

BACKGROUND

A database may be configured to store an organized collection of data. The database may be coupled with a database management system (DBMS) that supports a variety of operations for accessing, managing, and/or updating the data held in the database. For example, at least some of the data in the database may be retrieved, updated, and/or deleted by executing one or more database queries such as, for example, structured query language (SQL) statements.

SUMMARY

Systems, methods, and articles of manufacture, including computer program products, are provided for executing database queries. In one aspect, there is provided a system. The system may include at least one data processor and at least one memory. The at least one memory may store instructions that result in operations when executed by the at least one data processor. The operations may include: intercepting a database query from a client to a database, the database query including a first identifier associated with a first database table; determining that a second database table stored at the database corresponds to the first database table; reconstructing the database query by at least replacing the first identifier with a second identifier associated with the second database table; and executing the reconstructed database query by at least sending, to the database, the reconstructed database query.

In some variations, one or more features disclosed herein including the following features can optionally be included in any feasible combination. The second database table may correspond to the first database table by at least including a same data as the first database table. The reconstructed database query may be executed instead of the database query. The execution of the reconstructed database query may include retrieving, based at least on the second identifier, data from the second database table. At least a portion of the data retrieved from the second database table may be sent to the client.

In some variations, the first database table may compressed to form an archive file associated with a third identifier. The second database table may be formed by at least decompressing the archive file. In response to the compression of the first database table and/or the decompression of the archive file, a mapping table may be updated to include at least a mapping between the first identifier and the second identifier. The correspondence between the first database table and the second database table may be determined based at least on the mapping between the first identifier and the second identifier included in the mapping table. The archive file may be stored at an offline data store instead of the database. The first database table may be removed from the database. The decompression of the archive file may restore, as the second database table, at least a portion of data included in the first database table.

In some variations, the database query and/or the reconstructed database query may be a structured query language statement. The reconstructed database query may be sent to the database by at least sending the reconstructed database query to a database management system coupled with the database.

DETAILED DESCRIPTION

An enterprise may store data across multiple data storage systems. For example, some data including, for example, recent and/or frequently used data, may be held in an online database for immediate access. Meanwhile, other data including, for example, older and/or infrequently used data, may be held in an offline data store. The offline data store may be an information lifecycle management system configured to maintain and discard data in accordance with data retention policies applicable to the enterprise. However, inefficiencies may arise when accessing data held in the offline data store. For example, a user may need to prepare a report requiring data from the offline data store but may have limited access to this data compared to the data held in the online database. As such, in some example embodiments, an adaptive controller may be configured to integrate data held in the offline data store such that the user may be able to access this data in a same manner as data held in the online database.

FIG. 1depicts a system diagram illustrating an integrated data storage system100, in accordance with some example embodiments. Referring toFIG. 1, the integrated data storage system100may include an adaptive controller110. The adaptive controller110may be communicatively coupled with a client120, a database management system130, and an offline data store140. The adaptive controller110may be communicatively coupled with the client120, the database management system130, and/or the offline data store140via a network150. It should be appreciated that the network150may be any wired and/or wireless network including, for example, a wide area network (WAN), a local area network (LAN), a virtual local area network (VLAN), a public land mobile network (PLMN), the Internet, and/or the like.

As shown inFIG. 1, the database management system130may be coupled with a database135, which may be any type of database including, for example, an in-memory database, a relational database, a non-SQL (NoSQL) database, and/or the like. The database management system135may support a variety of operations for accessing the data held in the database135. For instance, the client120may interact with the database management system130via a database application125at the client120. The client120may access data held in the database135by at least sending, via the database application125, one or more database queries (e.g., SQL statements) to the database management system130.

In some example embodiments, the database135may be an online database. As used herein, an online database may be a database configured to provide immediate access to data. For example, to support immediate access, the database135may organize data into one or more database tables including, for example, a first database table160A and/or a second database table160B. Organizing data into database tables may enable high speed search and/or retrieval of data. By contrast, the offline data store140may be optimized for long term storage of infrequently used data. Accordingly, the offline data store140may be configured to maximize storage capacity, for example, through various compression techniques including, for example, Lempel-Ziv encoding, Huffman encoding, arithmetic encoding, differential encoding, prefix and postfix compression, semantic compression, and/or the like. Here, the first database table160A may be a standard database table stored at the database135. Compressing a standard database table such as, for example, the first database table160A, may remove the formatting and/or structure associated with the standard database table. Furthermore, compressing the first database table160A may change the original identifier associated with the first database table160A.

As used herein, a database (e.g., the database135and/or the like) may refer to an organized collection of data objects including, for example, schemas, tables, queries, reports, views, and/or the like. Meanwhile, a database table (e.g., the first database table160A, the second database table160B, and/or the like) may refer to a set of data elements (e.g., values), which may have been organized into vertical columns and/or horizontal rows.

Referring again toFIG. 1, the offline data store140may store a plurality of archive files including, for example, an archive file145. These archive files (e.g., the archive file145) may be generated by applying one or more compression techniques to database tables including, for example, the first database table160A. Accessing the data included in the first database table160A may require unpacking at least the archive file145. The unpacking of the archive file145may include, for example, decompressing the archive file145to restore the data included in the first database table160A. For example, decompressing the archive file145may form at least the second database table160B, which may include the same data as the first database table160A. As noted, the first database table160A may be a standard database table. By contrast, the second database table160B may be a temporary database table that is formed by restoring the data included in the first database table160A, for example, by decompressing the archive file145.

The second database table160B may be held in the database135for immediate access by the client120. It should be appreciated that the compression of the first database table160A to form the archive file145and/or the decompression of the archive file145to form the second database table160B may change the identifier (e.g., name) of the first database table160A such that the second database table160B may have a different identifier than the first database table160A, even though both database tables may include the same data. In some example embodiments, the adaptive controller110may track this change in identifier. For example, the adaptive controller110may maintain a mapping table115. The mapping table115may store a mapping between a current identifier of the second database table160B and the original identifier of the first database table160A.

To further illustrate,FIG. 2depicts a flow of data between the online database135and the offline data store140, in accordance with some example embodiments. Referring toFIG. 2, the first database table160A may initially be associated with an identifier “T512W.” The first database table160A may be subject to one or more compression techniques including, for example, deduplication, Lempel-Ziv encoding, Huffman encoding, arithmetic encoding, differential encoding, prefix and postfix compression, semantic compression, and/or the like. Subjecting the first database table160A to one or more compression techniques may generate the archive file145, which may be associated with a different identifier “C4928KK.ADK” and stored at the offline data store140. Accessing the first database table160A may require restoring the first database table160A, which may include unpacking the archive file145at the database135. For example, the archive file145may be unpacked by at least decompressing the archive file145. Unpacking the archive file145, for example, through decompression, may generate the second database table160B, which may be associated with the identifier “998CCKTSW2.”

It should be appreciated that the second database table160B and the first database table160A may include identical data. However, the second database table160B may be associated with a different identifier than the first database table160A. That is, the second database table160B stored at the database135may be associated with the identifier “998CCKTSW2” and not the identifier “T512W” originally associated with the first database table160A. This change in identifier may prevent the client120from accessing the second database table160B by referencing the identifier (e.g., “T512W”) originally associated with the first database table160A. As such, in some example embodiments, the adaptive controller110may maintain the mapping table115, which may include a mapping between the identifier “T512W” and the identifier “998CCKTSW2.” In doing so, the adaptive controller110may be able to determine when the client120is accessing the second database table160B, even though the database query from the client120may reference the identifier “T512W” associated with the first database table160A.

For example, the client120may access the first database table160A when the client120generates a report (e.g., a Payroll Reconciliation Report) that requires data from the first database table160A. In order to generate the report, the client120may send, via the database application125, a database query to the database management system130. The database query may include, for example, a SQL SELECT statement referencing the identifier “T512W” originally associated with the first database table160A. However, as noted, compressing the first database table160A into the archive file145and subsequently decompressing the archive file145into the second database table160B may change the original identifier “T512W” associated with the first database table160A. That is, although the first database table160A and the second database table160B may include identical data, the second database table160B may be associated with the different identifier “998CCKTSW2.” Accordingly, the database management system130may be unable to resolve the identifier “T512W” included in the database query from the client120because the first database table160A with the identifier “T512W” may have been compressed and moved to the offline data store140and therefore no longer exists at the database135. Instead, the database135may include the second database table160B, which may include identical data as the first database table160A, but may be associated with the different identifier “998CCKTSW2.”

According to some example embodiments, the adaptive controller110may be configured to intercept the database query from the client120. Furthermore, the adaptive controller110may determine, based at least on the mapping table115, that the identifier “T512W” included in the database query may refer to the second database table160B. The adaptive controller110may further reconstruct the database query by at least replacing the identifier “T512W” with the identifier “998CCKTSW2” associated with the second database table160B. The reconstructed database query may be sent to the database management system130instead of the original database query from the client120. It should be appreciated that the database management system130may be able to resolve the identifier “998CCKTSW2” included in the reconstructed database query whereas the database management system130may be unable to resolve the identifier “T512W” included in the original database query from the client120. For instance, the database management system130may respond to the reconstructed database query from the adaptive controller110by at least accessing, based on the identifier “998CCKTSW2,” the second database table160B and retrieving the data required by the reconstructed database query. Data from the second database table160B may be returned to the client120where the client120may be able to generate a report (e.g., a Payroll Reconciliation Report) based on the data.

FIG. 3depicts a block diagram illustrating the adaptive controller110, in accordance with some example embodiments. Referring toFIGS. 1 and 3, the adaptive controller110may include a table tracker310, a query interceptor320, a table identifier330, and a query reconstructor340. It should be appreciated that the adaptive controller110may include different and/or additional components than shown inFIG. 3.

In some example embodiments, the table tracker310may be configured to generate, maintain, and/or update the mapping table150by at least tracking the compression and/or decompression of database tables. For example, the table tracker310may detect when the first database table160A is compressed to generate the archive file145. Alternatively and/or additionally, the table tracker310may detect when the archive file145is decompressed to generate the second database table160B. In response to detecting the compression of the first database table160A to generate the archive file145and/or the decompression of the archive file145to generate the second database table160B, the table tracker310may update the mapping table150to at least reflect the changes in the corresponding identifiers. As noted, the archive file145may be associated with a different identifier than the first database table160A even though the archive file145may be formed by decompressing the first database table160A. Moreover, the second database table160B may also be associated with a different identifier than the first database table160A even though the second database table160B may be generated by decompressing the archive file145and may include a same data as the first database table165A.

For instance, in response to detecting the compression of the first database table160A to form the archive file145, the table tracker310may update the mapping table150to indicate an association between the identifier “T512W” originally associated with with the first database table160A and the identifier “C4928KK.ADK” associated with the archive file145. Alternatively and/or additionally, in response to detecting the decompression of the archive file145to form the second database table160B, the table tracker310may further update the mapping table150to indicate an association between the identifier “C4928KK.ADK” associated with the archive file145and the identifier “998CCKTSW2” associated with the second database table160B. It should be appreciated that these updates to the mapping table150may indicate a mapping between the identifier “T512W” associated with the first database table160A and the identifier “998CCKTSW2” associated with the second database table160B.

In some example embodiments, the query interceptor320may be configured to intercept one or more database queries, for example, from the client120to the database management system130. For example, the client120may access one or more database tables at the database135by at least sending, to the database management system130, a database query. The database query may include, for example, a SQL SELECT statement that includes the identifiers of the one or more database tables. According to some example embodiments, the query interceptor320may intercept the database query in response to detecting one or more triggers including, for example, the execution of a database query by the database management system130.

In some example embodiments, the table identifier330may be configured to identify the database tables accessed by the database query intercepted by the query interceptor320. For example, the database query may include a SQL SELECT statement referencing the identifier “T512W” originally associated with the first database table160A. As noted, the first database table160A may have undergone compression and/or decompression, which may have changed the identifier “T512W.” For example, compressing the first database table160A to form the archive file145may have changed the identifier from “T512W” to “C4928KK.ADK.” Meanwhile, decompressing the archive file145to form the second database table160B may have further changed the identifier from “C4928KK.ADK” to “998CCKTSW2.” The table identifier130may determine, based at least on the mapping table150, that the identifier “T512W” included in the database query may be mapped to the identifier “998CCKTSW2” associated with the second database table160B.

The query reconstructor340may be configured to reconstruct the database query intercepted by the query interceptor320. The reconstructed database query may be sent to the database management system130instead of the original database query from the client120. In some example embodiments, the query reconstructor340may reconstruct the database query by at least replacing the identifier “T512W” included in the original database query with the identifier “998CCKTSW2,” as determined by the table identifier330. As noted, the identifier “T512W” may be associated with the first database table160A, which may include identical data as the second database table160B associated with the identifier “998CCKTSW2.” However, the database management system130may be able to resolve the identifier “998CCKTSW2” but not the identifier “T512W.” Accordingly, replacing the identifier “T512W” included in the original database query from the client120with the identifier “998CCKTSW2” may enable the database management system130to execute the database query. For example, the reconstructed database query may be sent to the database management system130where the reconstructed database query may be executed by at least retrieving, from the second database table160B and based on the identifier “998CCKTSW2,” the data required by the client120.

FIG. 4depicts a flowchart illustrating a process400for executing a database query, in accordance with some example embodiments. Referring toFIGS. 1-4, the process400may be performed by the adaptive controller110.

At402, the adaptive controller110may intercept a database query including a first identifier from the client120to the database135. For example, the client120may generate a report (e.g., a Payroll Reconciliation Report) that requires data from the first database table160A. As such, the client120may send, to the database management system130, a database query. The database query may include a SQL SELECT statement that includes the identifier “T512W,” which may be associated with the first database table160A prior to the compression and/or decompression of the first database table160A. In some example embodiments, the adaptive controller110may intercept the database query from the client120to the database management system130.

At404, the adaptive controller110may determine that the first database table160A associated with the first identifier corresponds to the second database table160B at the database135. For example, compressing the first database table160A to form the archive file145may change the identifier associated with the first database table160A from “T512W” to “C4928KK.ADK.” Meanwhile, decompressing the archive file145to generate the second database table160B may further change the identifier associated with the archive file145from “C4928KK.ADK” to “998CCKTSW2.” In some example embodiments, the adaptive controller110may track this change in identifier from “T512W” to “C4928KK.ADK” and/or from “C4928KK.ADK” to “998CCKTSW2.” For example, the adaptive controller110may generate, maintain, and/or update the mapping table150to reflect this change in identifiers. By tracking this change in identifiers, the adaptive controller110may determine, base at least on the mapping table150, that the identifier “T512W” included in the database query from the client120may refer to the second database table160B.

At406, the adaptive controller110may reconstruct the database query by at least replacing the first identifier with a second identifier associated with the second database table. For example, in some example embodiments, the adaptive controller110may reconstruct the database query by at least replacing the identifier “T512W” with the identifier “998CCKTSW2.” As noted, the identifier “T512W” may reference the original first database table160A while the identifier “998CCKTSW2” may reference the second database table160B. The database management system130may be unable to resolve the identifier “T512W” included in the database query from the client120because the first database table160A with the identifier “T512W” has been compressed and moved to the offline data store140and therefore does not exist at the database135. Instead, the database135may include the second database table160B, which may include identical data as the first database table160A, but may be associated with the different identifier “998CCKTSW2.”

At408, the adaptive controller110may send, to the database135, the reconstructed database query. For example, the reconstructed database query may include the identifier “998CCKTSW2,” which may reference the second database table160B. The database management system130may execute the reconstructed database query by at least retrieving, based at least on the identifier “998CCKTSW2,” at least a portion of the data from the second database table160B. The data retrieved from the second database table160B may be returned to the client120where the data may be used to generate a report (e.g., a Payroll Reconciliation Report).

FIG. 5depicts a block diagram illustrating a computing system500consistent with implementations of the current subject matter. Referring toFIGS. 1 and 5, the computing system500can be used to implement the adaptive controller110, the client120, the database management system130, and/or any components therein.

As shown inFIG. 5, the computing system500can include a processor510, a memory520, a storage device530, and input/output devices540. The processor510, the memory520, the storage device530, and the input/output devices540can be interconnected via a system bus550. The processor510is capable of processing instructions for execution within the computing system500. Such executed instructions can implement one or more components of, for example, the adaptive controller110, the client120, and/or the database management system130. In some example embodiments, the processor510can be a single-threaded processor. Alternately, the processor510can be a multi-threaded processor. The processor510is capable of processing instructions stored in the memory520and/or on the storage device530to display graphical information for a user interface provided via the input/output device540.

The memory520is a computer readable medium such as volatile or non-volatile that stores information within the computing system500. The memory520can store data structures representing configuration object databases, for example. The storage device530is capable of providing persistent storage for the computing system500. The storage device530can be a floppy disk device, a hard disk device, an optical disk device, or a tape device, or other suitable persistent storage means. The input/output device540provides input/output operations for the computing system500. In some example embodiments, the input/output device540includes a keyboard and/or pointing device. In various implementations, the input/output device540includes a display unit for displaying graphical user interfaces.