BACKGROUND

The advantages of modern data-warehouse-as-a-service (DWaaS) systems have motivated many companies to migrate from on-premises warehouse systems (e.g., Teradata or Oracle) to cloud-native systems (e.g., Microsoft Azure SQL or Amazon RedShift). However, this migration comes with challenges due to syntactic, semantic, functionality, and performance differences between data warehousing layers, requiring applications' queries to be rewritten and tested for the new platform. This rewriting and testing process can take years and cost tens of millions of dollars.

Adaptive data virtualization can reduce the costs of data warehouse migration by translating queries in real time from the on-premise system to the new vendor's SQL dialect. This considerably shortens the time required for rewriting data warehouse applications. However, applications' queries written for on-premises solutions are often highly optimized for the on-premises system, not the new DWaaS system. In addition, the queries may include customized features that exploit functionalities of the on-premise system which may not be available in the new DWaaS system. Therefore, simply rewriting the queries from the syntax of the on-premise system to the syntax of the DWaaS system is insufficient.

BRIEF SUMMARY

Some embodiments of the invention provide a novel database virtualization system (DVS) for automatically processing queries written and intended for one database system using a different database system, where the new (“target”) database system does not support all of the same functionality as the original (“source”) database system. Queries from a client or client application that are intended for the source database are intercepted by the database virtualization system and processed into a normalized query representation independent of any database platform. The normalized query can then be transformed into new queries that can be executed on the target database, which contains the (previously) migrated data. Upon receiving the result of the new queries from the target database, the results are similarly converted to the expected format of the source database system and provided to the client. From the perspective of the client, this “on-the-fly” process is seamless and transparent, so that the client is unaware that the query was not actually processed by the source database. Accordingly, there is no need to rewrite the client application for native queries to the target database.

In some embodiments, the database virtualization system augments the functionality of the target database in order to emulate certain features that are native to the source database but absent in the target database. Several examples of feature augmentation are presented, specifically: static emulation, dynamic emulation, emulation of updateable views, and catalog emulation.

Static emulation is the emulation of database object properties supported by the source database such as case sensitivity, set tables, default values, or certain data types (e.g., period data or byte type) which are not supported by the target database. In some embodiments, the database virtualization system (DVS) receives a query from a client. The query references an object in a source database, the object having two sets of properties. The DVS generates a full set of properties by retrieving one set of properties from the target database and retrieving the other set of properties from a metadata storage. Based on the full, combined set of properties, the DVS generates a new query for execution on the target database.

Dynamic emulation is the emulation of user-defined objects from the source database such as macros and stored procedures which are undefined on the target database. In some embodiments, the DVS receives a query referencing a set of operations (e.g., a macro or stored procedure) to perform on the source database. The DVS retrieves a definition for the set of operations from a metadata storage, and based on the retrieved definition, generates multiple queries. The generated queries successively execute on the target database and include at least one query that is generated based on a result of executing a previously generated query.

When supported by the source database, emulation of updateable views is necessary since in most target database systems, views are read-only queries that cannot modify the base tables. Views can be selectively restricted to certain users, such as a manager who has access to salary data from an employee table. However, some databases do allow modifications to a view, which propagates to the base tables in the database. In order to emulate this functionality on a target database that does not support updateable views, the updateable view must be translated to a query for the target database that modifies the data.

In some embodiments, the database virtualization system (DVS) receives a query for modifying data tuples presented in an updateable view defined for the source database. The DVS retrieves a definition of the updateable view from a metadata storage, the definition including a reference to a set of objects associated with the data tuples in the source database. Based on the retrieved definition, The DVS generates a second query referencing objects associated with the data tuples in the target database. The DVS then executes the generated query on the target database to modify the data tuples in the second database.

Catalog emulation is also necessary since any query referencing the database catalog of the source database would fail, because that catalog does not exist on the target database system. In some embodiments, catalogs are a collection of schemas, such as the SQL Information Schema. These schemas are a collection of system objects in some embodiments that describe the other objects in the database. In some embodiments, queries that access the catalog are referencing these system objects.

In some embodiments, the database virtualization system (DVS) receives from a client a query that requests a presentation of a system object of the source database. From a metadata storage, the DVS identifies a query for the target database that references system objects of the target database and generates the requested presentation of the system object of the source database. The DVS then replies to the client with the generated presentation.

In some embodiments, the database virtualization system also optimizes queries for the target database based on various characteristics of the target database that differ from those of the source database. Some examples of query optimization are here described, namely query batching and query unbatching.

Query batching is required in some embodiments when the target database imposes a higher cost on certain transactions than the source database does. In some embodiments, the database virtualization system (DVS) individually receives multiple queries from a database client where each query is written for a source database. The DVS performs an automated process to analyze properties of the target database to determine that the queries can be replaced by a single query to the target database. Based on that determination, the DVS combines the queries to generate a single query for the target database and forwards the generated single query to the target database for execution.

Query unbatching is used in some embodiments when the target database has stricter requirements on the types of database statements that can be grouped together in a single transaction. The database virtualization system (DVS) takes these characteristics and requirements into account by reordering queries to regroup the database statements. In some embodiments, the database virtualization system (DVS) receives a particular query from a database client, where the query is written for a source database. The DVS performs an automated process to analyze properties of the target database to determine that the received query should be replaced by multiple queries to the target database. Based on the determination, the DVS generates multiple queries for the second database and individually forwards each generated query to the target database for execution.

In some embodiments, information pertaining to feature augmentation is maintained by the database virtualization system as metadata associated with the objects referenced in the query. In the course of normalizing and transforming fully supported queries, the database virtualization system accesses metadata from the target database catalog that is associated with the referenced objects. However, for feature augmentation there is no corresponding metadata available in the target database catalog, since these features are unsupported. Accordingly, the metadata for feature augmentation is stored elsewhere in a separate storage maintained by the database virtualization system itself.

Furthermore, the database virtualization system also maintains information pertaining to query optimization as metadata associated with the supported target database systems. In some embodiments this metadata may be hard-coded as part of target database-specific routines. However, in other embodiments the metadata for query optimization is also stored in a metadata storage maintained by the database virtualization system. Storing this metadata in the metadata storage allows flexibility in adding support for more target systems and different versions.

In some embodiments, the metadata storage for feature augmentation and query optimization is maintained by the database virtualization system in a separate database instance. In other embodiments, the metadata storage is stored as a separate table in the target database system itself, to exploit existing target database backup and restore functionality.

The preceding Summary is intended to serve as a brief introduction to some embodiments of the invention. It is not meant to be an introduction or overview of all of the inventive subject matter disclosed in this document. The Detailed Description that follows and the Drawings that are referred to in the Detailed Description will further describe the embodiments described in the Summary as well as other embodiments. Accordingly, to understand all the embodiments described by this document, a full review of the Summary, Detailed Description and the Drawings is needed. Moreover, the claimed subject matters are not to be limited by the illustrative details in the Summary, Detailed Description and the Drawing, but rather are to be defined by the appended claims, because the claimed subject matters can be embodied in other specific forms without departing from the spirit of the subject matters.

