Patent ID: 12204529

DETAILED DESCRIPTION

Example 1—Overview

It is increasingly common for enterprises to have data stored in a variety of systems, including in one or more local systems and in one or more cloud-based systems. The systems can be of different types—such as storing data in different formats (e.g., a relational database versus a database that stores JAVA documents) or storing data using different database management systems (e.g., using software and/or hardware provided by different vendors). Even where data is stored in the same format and using software of the same vendor, differences can exist in what data is stored at a particular location and the schema used to store it.

In order to help address these issues, database federation techniques have been used. In a federated database environment, requests for database operations, such as queries, can specify sources at a local database system or at a “remote” database accessed using data federation. In some cases, both local and remote data sources can be specified in the same query, such as having a query that retrieves data from a local database table and data from a data source of a remote, federated database system.

Database federation is typically used for “static” database objects, such as database tables or database views, or similar constructs. Issues can arise in providing remote access to other types of data objects, given that these data objects have information in a schema that is not typically accessible to a local system. Accordingly, room for improvement exists.

One particular class or category of data objects that can be difficult to access via federation is parameterized data objects. The use of a parametrized data object typically requires parameters that are provided by a client when a request involving the data object is made (unless default values are provided or null values are accepted). There parameters are passed to a definition of the data object and then data can be processed and returned in a response to a request.

The present disclosure describes two types of parameterized data objects—user-defined table functions and parameterized views. However, the disclosed techniques can be applied to other types of parameterized data objects in an analogous manner.

In the field of computer science, functions are units of code that typically accept one or more arguments or parameters, perform processing, and then return a result. In the database field, table functions also accept one or more parameters. The result of a table function is a table, where the results are determined at least in part on a parameter value, or parameter values, that are provided when the table function is called.

Parameterized views are similar to table functions, in that they contain a definition that can be used to retrieve data, but the definition depends on one or more parameter values that are supplied when the view is called/queried. As opposed to a table function, a parameterized view need not return results in the form of a table. Rather, results can be provided in the form of rows, a set of rows, or as discrete values.

Although parameterized views and user-defined table functions can accept parameter values, in at least some cases the views and functions are usable even if no parameters are supplied. For example, default parameter values can be provided. Or, code implementing a user-defined table function or a parameterized view can supply values for any parameters where values are not supplied when the user-defined table function or parameterized view is called.

Disclosed techniques provide for the retrieval of metadata for a remote, federated data object when a “virtual” data object is created at a local system, where the virtual data object is referenced by requests for database operations sent to the local system that access data stored at the federated system in the remote object. The metadata includes parameters that are associated with the remote data object. Information about the parameters used by the remote data object is stored on the local database system in association with the virtual data object. The parameter information can be used when a client submits a request to the local database system that accesses a virtual data object. In particular, the parameter information can be used in formatting a request to be sent to the federated system. The parameter information can be used for other purposes, such as checking that a request from a client is properly formed or formatted, for example analyzing the request to see whether it includes the correct number of parameters and that the supplied values are of the correct type. The parameter information, as well as other information in the virtual data object, can be accessed by clients to determine properties of the virtual data object and the remote data object to which it is mapped, such as to assist clients in formulating requests for database operations involving the virtual data object/remote data object.

Example 2 describes an example database system that can be used in implementing disclosed technologies. The database system can be an example of either a local database system or a federated database system that is accessed by the local system. Example 3 provides an example of a virtual table, where the virtual table includes a logical pointer that can be updated to point to different locations, including a location in a federated data source or in a local data source (including a local table, or a table maintained in a cache). Although, it should be appreciated that a virtual table can be implemented in a different manner, including in a way that is “statically” mapped to a particular federated data source. Examples 4-8 more specifically describe disclosed techniques for the use of virtual parameterized data objects that are mapped to remote parameterized data objects.

An advantage of disclosed technologies is that they can allow for more data to be accessed through data federation. Accessing data via federation can be beneficial, as it can avoid the use of computing resources that might be needed in order to replicate data from a remote data source or use with another system, including network and storage resources.

Example 2—Example Database Architecture

Database systems commonly operate using online transaction processing (OLTP) workloads, which are typically transaction-oriented, or online analytical processing (OLAP) workloads, which typically involve data analysis. OLTP transactions are commonly used for core business functions, such as entering, manipulating, or retrieving operational data, and users typically expect transactions or queries to be completed quickly. For example, OLTP transactions can include operations such as INSERT, UPDATE, and DELETE, and comparatively simple queries. OLAP workloads typically involve queries used for enterprise resource planning and other types of business intelligence. OLAP workloads commonly perform few, if any, updates to database records, rather, they typically read and analyze past transactions, often in large numbers.

FIG.1illustrates an example database environment100. The database environment100can include a client104. Although a single client104is shown, the client104can represent multiple clients. The client or clients104may be OLAP clients, OLTP clients, or a combination thereof.

The client104is in communication with a database server106. Through various subcomponents, the database server106can process requests for database operations, such as requests to store, read, or manipulate data (i.e., CRUD operations). A session manager component108can be responsible for managing connections between the client104and the database server106, such as clients communicating with the database server using a database programming interface, such as Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), or Database Shared Library (DBSL). Typically, the session manager108can simultaneously manage connections with multiple clients104. The session manager108can carry out functions such as creating a new session for a client request, assigning a client request to an existing session, and authenticating access to the database server106. For each session, the session manager108can maintain a context that stores a set of parameters related to the session, such as settings related to committing database transactions or the transaction isolation level (such as statement level isolation or transaction level isolation).

For other types of clients104, such as web-based clients (such as a client using the HTTP protocol or a similar transport protocol), the client can interface with an application manager component110. Although shown as a component of the database server106, in other implementations, the application manager110can be located outside of, but in communication with, the database server106. The application manager110can initiate new database sessions with the database server106, and carry out other functions, in a similar manner to the session manager108.

The application manager110can determine the type of application making a request for a database operation and mediate execution of the request at the database server106, such as by invoking or executing procedure calls, generating query language statements, or converting data between formats useable by the client104and the database server106. In particular examples, the application manager110receives requests for database operations from a client104, but does not store information, such as state information, related to the requests.

Once a connection is established between the client104and the database server106, including when established through the application manager110, execution of client requests is usually carried out using a query language, such as the structured query language (SQL). In executing the request, the session manager108and application manager110may communicate with a query interface112. The query interface112can be responsible for creating connections with appropriate execution components of the database server106. The query interface112can also be responsible for determining whether a request is associated with a previously cached statement or a stored procedure, and calling the stored procedure or associating the previously cached statement with the request.

At least certain types of requests for database operations, such as statements in a query language to write data or manipulate data, can be associated with a transaction context. In at least some implementations, each new session can be assigned to a transaction. Transactions can be managed by a transaction manager component114. The transaction manager component114can be responsible for operations such as coordinating transactions, managing transaction isolation, tracking running and closed transactions, and managing the commit or rollback of transactions. In carrying out these operations, the transaction manager114can communicate with other components of the database server106.

The query interface112can communicate with a query language processor116, such as a structured query language processor. For example, the query interface112may forward to the query language processor116query language statements or other database operation requests from the client104. The query language processor116can include a query language executor120, such as a SQL executor, which can include a thread pool124. Some requests for database operations, or components thereof, can be executed directly by the query language processor116. Other requests, or components thereof, can be forwarded by the query language processor116to another component of the database server106. For example, transaction control statements (such as commit or rollback operations) can be forwarded by the query language processor116to the transaction manager114. In at least some cases, the query language processor116is responsible for carrying out operations that retrieve or manipulate data (e.g., SELECT, UPDATE, DELETE). Other types of operations, such as queries, can be sent by the query language processor116to other components of the database server106. The query interface112, and the session manager108, can maintain and manage context information associated with requests for database operation. In particular implementations, the query interface112can maintain and manage context information for requests received through the application manager110.

When a connection is established between the client104and the database server106by the session manager108or the application manager110, a client request, such as a query, can be assigned to a thread of the thread pool124, such as using the query interface112. In at least one implementation, a thread is associated with a context for executing a processing activity. The thread can be managed by an operating system of the database server106, or by, or in combination with, another component of the database server. Typically, at any point, the thread pool124contains a plurality of threads. In at least some cases, the number of threads in the thread pool124can be dynamically adjusted, such in response to a level of activity at the database server106. Each thread of the thread pool124, in particular aspects, can be assigned to a plurality of different sessions.

When a query is received, the session manager108or the application manager110can determine whether an execution plan for the query already exists, such as in a plan cache136. If a query execution plan exists, the cached execution plan can be retrieved and forwarded to the query language executor120, such as using the query interface112. For example, the query can be sent to an execution thread of the thread pool124determined by the session manager108or the application manager110. In a particular example, the query plan is implemented as an abstract data type.

If the query is not associated with an existing execution plan, the query can be parsed using a query language parser128. The query language parser128can, for example, check query language statements of the query to make sure they have correct syntax, and confirm that the statements are otherwise valid. For example, the query language parser128can check to see if tables and records recited in the query language statements are defined in the database server106.

The query can also be optimized using a query language optimizer132. The query language optimizer132can manipulate elements of the query language statement to allow the query to be processed more efficiently. For example, the query language optimizer132may perform operations such as unnesting queries or determining an optimized execution order for various operations in the query, such as operations within a statement. After optimization, an execution plan can be generated, or compiled, for the query. In at least some cases, the execution plan can be cached, such as in the plan cache136, which can be retrieved (such as by the session manager108or the application manager110) if the query is received again.

Once a query execution plan has been generated or received, the query language executor120can oversee the execution of an execution plan for the query. For example, the query language executor120can invoke appropriate subcomponents of the database server106.

In executing the query, the query language executor120can call a query processor140, which can include one or more query processing engines. The query processing engines can include, for example, an OLAP engine142, a join engine144, an attribute engine146, or a calculation engine148. The OLAP engine142can, for example, apply rules to create an optimized execution plan for an OLAP query. The join engine144can be used to implement relational operators, typically for non-OLAP queries, such as join and aggregation operations. In a particular implementation, the attribute engine146can implement column data structures and access operations. For example, the attribute engine146can implement merge functions and query processing functions, such as scanning columns.

In certain situations, such as if the query involves complex or internally parallelized operations or sub-operations, the query executor120can send operations or sub-operations of the query to a job executor component154, which can include a thread pool156. An execution plan for the query can include a plurality of plan operators. Each job execution thread of the job execution thread pool156, in a particular implementation, can be assigned to an individual plan operator. The job executor component154can be used to execute at least a portion of the operators of the query in parallel. In some cases, plan operators can be further divided and parallelized, such as having operations concurrently access different parts of the same table. Using the job executor component154can increase the load on one or more processing units of the database server106, but can improve execution time of the query.

The query processing engines of the query processor140can access data stored in the database server106. Data can be stored in a row-wise format in a row store162, or in a column-wise format in a column store164. In at least some cases, data can be transformed between a row-wise format and a column-wise format. A particular operation carried out by the query processor140may access or manipulate data in the row store162, the column store164, or, at least for certain types of operations (such a join, merge, and subquery), both the row store162and the column store164. In at least some aspects, the row store162and the column store164can be maintained in main memory.

A persistence layer168can be in communication with the row store162and the column store164. The persistence layer168can be responsible for actions such as committing write transactions, storing redo log entries, rolling back transactions, and periodically writing data to storage to provided persisted data172.

In executing a request for a database operation, such as a query or a transaction, the database server106may need to access information stored at another location, such as another database server. The database server106may include a communication manager180component to manage such communications. The communication manger180can also mediate communications between the database server106and the client104or the application manager110, when the application manager is located outside of the database server.

In some cases, the database server106can be part of a distributed database system that includes multiple database servers. At least a portion of the database servers may include some or all of the components of the database server106. The database servers of the database system can, in some cases, store multiple copies of data. For example, a table may be replicated at more than one database server. In addition, or alternatively, information in the database system can be distributed between multiple servers. For example, a first database server may hold a copy of a first table and a second database server can hold a copy of a second table. In yet further implementations, information can be partitioned between database servers. For example, a first database server may hold a first portion of a first table and a second database server may hold a second portion of the first table.

In carrying out requests for database operations, the database server106may need to access other database servers, or other information sources, within the database system, or at external systems, such as an external system on which a parameterized data object is located. The communication manager180can be used to mediate such communications. For example, the communication manager180can receive and route requests for information from components of the database server106(or from another database server) and receive and route replies.

The database server106can include components to coordinate data processing operations that involve remote data sources. In particular, the database server106includes a data federation component190that at least in part processes requests to access data maintained at remote systems. In carrying out its functions, the data federation component190can include one or more adapters192, where an adapter can include logic, settings, or connection information usable in communicating with remote systems, such as in obtaining information to help generate virtual parameterized data objects or to execute requests for data using virtual parameterized data objects (such as issuing a request to a remote system for data accessed using a corresponding parameterized data object of the remote system). Examples of adapters include “connectors” as implemented in technologies available from SAP SE, of Walldorf, Germany. Further, disclosed techniques can use technologies underlying data federation techniques such as Smart Data Access (SDA) and Smart Data Integration (SDI) of SAP SE.

Example 3—Example Virtual Tables, Including with Updatable Logical Pointers

FIG.2illustrates a computing environment200in which disclosed embodiments can be implemented. The basic computing environment200ofFIG.2includes a number of features that can be common to different embodiments of the disclosed technologies, including one or more applications208that can access a central computing system210, which can be a cloud computing system. The central computing system210is shown as a monolithic/unitary system, but it should be appreciated that, particularly in a cloud environment, the central computing system can include a number of computing systems that function together as a single system. For example, the central computing system210can be implemented as a plurality of “nodes,” including an anchor node and zero or more non-anchor nodes. A central computing system210can also be a more typical “distributed” database system, which includes a master node and one or more worker nodes.

The central computing system210can act as such by providing access to data stored in one or more remote database systems212. In turn, the remote database systems212can be accessed by one or more applications214. In some cases, an application214can also be an application208. That is, some applications may only (directly) access data in the central computing system210, some applications may only access data in a remote database system212, and other applications may access data in both the central computing system and in a remote database system.

The central computing system210can include a query processor220. The query processor220can include multiple components, including a query optimizer222and a query executor224. The query optimizer222can be responsible for determining a query execution plan226for a query to be executed using the central computing system210. The query plan226generated by the query optimizer222can include both a logical plan indicating, for example, an order of operations to be executed in the query (e.g., joins, projections) and a physical plan for implementing such operations. Once developed by the query optimizer222, a query plan226can be executed by the query executor224. Query plans226can be stored in a query plan cache228as cached query plans230. When a query is resubmitted for execution, the query processor220can determine whether a cached query plan230exists for the query. If so, the cached query plan230can be executed by the query executor224. If not, a query plan226is generated by the query optimizer222. In some cases, cached query plans230can be invalidated, such as if changes are made to a database schema, or at least components of a database schema (e.g., tables or views) that are used by the query.

A data dictionary234can maintain one or more database schemas for the central computing system210. In some cases, the central computing system210can implement a multitenant environment, and different tenants may have different database schemas. In at least some cases, at least some database schema elements can be shared by multiple database schemas.

The data dictionary234can include definitions (or schemas) for different types of database objects, such as schemas for tables or views. Although the following discussion references tables for ease of explanation, it should be appreciated that the discussion can apply to other types of database objects, particularly database objects that are associated with retrievable data, such as materialized views. A table schema can include information such as the name of the table, the number of attributes (or columns or fields) in the table, the names of the attributes, the data types of the attributes, an order in which the attributes should be displayed, primary key values, foreign keys, associations to other database objects, partition information, or replication information.

Table schemas maintained by the data dictionary234can include local table schemas236, which can represent tables that are primarily maintained on the central computing system210. The data dictionary234can include replica table schemas238, which can represent tables where at least a portion of the table data is stored in the central computing system210(or which is primarily managed by a database management system of the central computing system, even if stored other than on the central computing system, such as being stored in a data lake or in another cloud service). Tables having data associated with replica tables schemas238typically will periodically have their data updated from a source table, such as a remote table244of a data store242of a remote database system212.

Replication can be accomplished using one or both of a replication service246of the remote database system212or a replication service248of the central computing system210. In particular examples, the replication service can be the Smart Data Integration (SDI) service, SAP Landscape Transformation Replication Server, SAP Data Services, SAP Replication Server, SAP Event Stream Processor, or an SAP HANA Direct Extractor Connection, all of SAP SE, of Walldorf, Germany.

In some cases, data in a remote database system212can be accessed by the central computing system210without replicating data from the remote database system, such as using federation techniques. The data dictionary234can store virtual table schemas252for virtual tables that are mapped to remote tables, such as a remote table244of a remote database system212. Data in the remote table244can be accessed using a federation service256, such as using the Smart Data Access protocol of SAP SE, of Walldorf, Germany. The federation service256can be responsible for converting query operations into a format that can be processed by the appropriate remote database system212, sending the query operations to the remote database system, receiving query results, and providing the query results to the query executor224.

The data dictionary234can include updatable virtual table schemas260that have updatable logical pointers262. The updated virtual table schemas260can optionally be associated with status information264. The table pointer262can be a logical pointer used to identify what table should be accessed for data of the corresponding virtual table schema260. For example, depending on the state of the table pointer262, the table pointer can point to the remote table244of a remote database system212or a replica table266(which can be generated from the remote table244) located in a data store268of the central computing system210. The data store268can also store data for local tables270, which can be defined by the local table schemas236.

The table pointer262can be changed between the remote table244and the replica table266. In some cases, a user can manually change the table pointed to by the table pointer262. In other cases, the table pointer262can be automatically changed, such as in response to the detection of defined conditions.

The status information264can include an indicator identifying a virtual table schema260as being associated with a remote table244or a replica table266. The status information264can also include information about the replication status of a replica table266. For example, once a request is made to change the table pointer262to point to a replica table266, it may take time before the replica table is ready for use. The status information264can include whether a replication process has been started, has been completed, or a progress status of generating the replica table266.

Changes to updateable virtual table schemas260and managing replica tables266associated with virtual table schemas can be managed by a virtual table service272. Although shown as a separate component of the central computing system210, the virtual table service272can be incorporated into other components of the central computing system210, such as the query processor220or the data dictionary234.

When a query is executed, the query is processed by the query processor220, including executing the query using the query executor224to obtain data from one or both of the data store242of the remote database system212or the data store268of the central computing system210. Query results can be returned to the application208. Query results can also be cached, such as in a cache278of the central computing system210. The cached results can be represented as cached views280(e.g., materialized query results).

The applications214can access data in the remote database system212, such as through a session manager286. The applications214can modify the remote tables244. When a table pointer262of an updateable virtual table schema260references a remote table244, changes made by the applications214are reflected in the remote table. When a table pointer262references a replica table266, changes made by the applications214can be reflected in the replica table using the replication service246or the replication service248.

Example 4—Example Definition of User-Defined Table Function, Creation of Mapping Thereto, and Accessing of User-Defined Table Function Using Virtual Parameterized Data Object

FIG.3illustrates a definition300for a user-defined table function. The user-defined table function has a name or identifier310, and the syntax of the user-defined table function definition300indicates that the user-defined table function has a single input parameter314. Note that the input parameter314has both a name/identifier (“val”) and a type (“INT,” representing an integer datatype). Line318of the definition300indicates that the function provides a table as the return type, and the syntax of line318indicates that the table has two columns having names/identifiers “a” and “b,” and which each are of type “INT” (integer).

Lines322define operations performed by the user-defined table function. The operations are SQL operations, in the form of a select statement330that selects an attribute “a” (334) from a table “mytab” (336) specified in a FROM operator (338). Column “b” of the return table is defined as the product of a column “b” (340) of the table336multiplied by the value of the input parameter314.

For the purposes of the present disclosure, the definition300is typically entered/executed/maintained at a remote/federated database system. That is, a local system can also include user-defined table functions, but disclosed techniques involve accessing user-defined table functions (or other parameterized data objects) at remote systems/using data federation.

Statement360provides an example of how the user-defined table function according to the definition300can be “registered” at a local computing system as a virtual data object (in this case a virtual user-defined table function). The statement360includes a “create” operator362that is followed by the keyword “virtual”364, which indicates that the data object to be created is a virtual data object that is accessed via data federation at a remote/federated database system. The statement360also provides an identifier366for the type of data object being created (virtually), in this case indicating that the data object is a “function.” The statement360then provides a name/identifier368for the remote data object to be used by the local system, and then a “path” to the remote data object to which the local, virtual data object will be “mapped,” in the form of an identifier370of the remote system, an identifier372of a database of the remote system, an identifier374of a schema of the database of the remote system, and an identifier376of the remote data object.

That the remote data object is a user-defined table function can be determined or indicated in a variety of ways. In one implementation, identifier366for the type of data object can indicate that the remote data object is a user-defined table function. In other implementations, the identifier366can indicate a general type of data object (a function, generally), and a more specific type of data object (such as whether the function is a user-defined function that does not return a table, is a user-defined function that returns a table, or is a “built in” function), can be determined when information about the remote data object is retrieved from the remote computing system.

Statement380provides an example of how a virtual user-defined table function can be accessed by a client. The statement380can be in a query language, such as SQL or a dialect thereof, and identifies that all data should be retrieved using the remote function identified by the identifier366and using a value382for the parameter314. So, when the virtual user-defined table function is called at the local system, parameters provided in the call will be provided to the remote system, used in the select statement330, and appropriate results returned by the remote system to the local system and either further processed or returned to a client.

Example 5—Example Definition of a Parametrized View, Creation of a Mapping Thereto, and Accessing of the Parameterized View Using a User Virtual Parameterized View

FIG.4illustrates code for implementing a parameterized view in a remote system, mapping a virtual data object to the parameterized view, and calling the virtual data object representing the parameterized view.

Code410creates a parameterized view at a remote system. Line414includes the “CREATE” command416, a type identifier418that indicates the data object being created is a view, an identifier/name420of the view410, and identifiers422of two parameters included in the view. Line424includes two instances426,428of the keyword “IN” which declares/provides details regarding the two input parameters, having respective names/identifiers430,432and respective datatypes434,436(integer and a character array of maximum length30). The code410further includes a view definition438, where information is selected from another data object440(base_tab). A filter/WHERE442clause of the view definition438includes the parameters422for respective attributes of the data object440. Thus, the use of the parameters makes the view410“dynamic” in that the filter conditions can be changed, by using different values for the parameters422, each time the view is accessed.

Code450creates a virtual data object in the local system that is mapped to the parameterized view created by the code410. In particular, the code450includes the “CREATE” command452and a type identifier454that indicates that the virtual data object being created is a virtual table (where, in this case a virtual view is considered a type of virtual table). In some cases, a type of virtual data object can be determined from the code450, while in other cases the code specifies a remote data object and communications with a remote system provide more information about a type of the referenced data object, which is then used to create the virtual data object with the appropriate type.

Code450provides a name/identifier456for the virtual table at the local system, and provides a path458for the corresponding parameterized view at the remote system, including the name/identifier422of the view at the remote system.

Code470is an example of how the parameterized view410can be called by a client using the virtual parameterized view created using the code450. A SELECT operation474specifies that all values (indicated using the wildcard value “*”) should be selected from the view, indicated by the identifier420. The SELECT operation474also provides values478for the parameters422, which will be supplied to the filter clause442. So, when the virtual parameterized view is called at the local system, parameters provided in the call will be provided to the remote system, used in the filter clause442, and appropriate results returned by the remote system to the local system and either further processed or returned to a client.

Example 6—Example Routing of Database Operations Involving a Remote Join

FIG.5is a diagram of a computing environment500illustrating how a virtual user-defined object, such as a virtual user-defined table function or a virtual parameterized view, can be created. The computing environment500includes a client510that communicates with a first database system514, and where the first database system communicates with a second database system518. The first database system514can be considered a “local” database system, in that it receives and processes requests from the client510.

The second database system518includes a parameterized data object522, such as a user-defined table function or a parameterized view, that includes one or more parameters526. The parameterized data object522can be created in any suitable manner using commands that are appropriate for the second database system518. In a particular example, commands having the general format of the code300ofFIG.3, or the code410ofFIG.4can be used. The parameterized data object522can access other data objects of the second database system518, such as tables526a,526b,526c, or a view528, where the view is defined with respect to tables526a,526c.

The first computing system514includes a virtual parameterized data object550that is “mapped” to the parameterized data object522, where typically the virtual parameterized data object and the parameterized data object are of the same type (that is, both are, or represent, user-defined table functions or parameterized views). The mapping can be created using a command analogous to code360or code450. The virtual parameterized data object550includes information about the parametrized data object522, including a list554of parameters used by the parameterized data object.

For creation, the client510sends a request558to create the virtual parameterized data object550. The request558includes information that is at least analogous to the information in the code360or the code450, including a name to be used for the virtual parameterized data object550and a name and path for the parameterized data object522at the second database system518.

In response to the request558, the first database system514sends one or more requests562to the second database system518. In one implementation, the first database system514sends a first request to the second database system518to confirm that the parameterized data object522exists at the second database system. If a response566from the second database system518indicates that the specified parameterized data object exists, the first database system514sends a second request to the second database system, requesting information about the parameterized data object522.

In particular, the request is to obtain parameters526used with the parameterized data object522, information about which can be stored by the first database system514in association with the virtual parameterized data object550, such as in the list of parameters554, and this information can be provided in the response566. The parameter information can include an identifier (name) of the parameter and a datatype of the parameter. Information about the parameters526can also include whether the parameters are associated with a default value/whether values are required for a particular parameter.

When the client510sends a request to access the virtual parameterized data object550, the first database system514can use definitional information for the virtual parameterized data object, such as the list of parameters554, to confirm that the request is property formulated—such as providing values for any required parameters, and that the provided values are of the appropriate datatype. In another implementation, rather than having two requests562and two responses566, a single request can be used to both confirm that the parameterized data object522exists and to obtain information about it to include in the virtual parameterized data object550, where a response indicates that the parameterized data object522does not exist, or provides information about parameters526and optionally other definitional information for the parameterized data object if the parameterized data object522does exist.

A procedure for processing a client request is further illustrated with respect toFIG.6.FIG.6includes components ofFIG.5, which are labelled as inFIG.5. The client510sends a request610to the first database system514that includes a call to the virtual parameterized data object550, including the name of the virtual parameterized data object and one or more parameter values for parameters in the list554. The first database system514processes the request610, such as using database components that can be analogous to those described with respect toFIG.1.

As part of processing the request610, the first database system514can analyze the request to confirm that the referenced virtual parameterized data object550exists, such as in a data dictionary or information schema, and checking to see if values are provided for all required parameters, and that supplied parameters have the appropriate datatype. If the request610specifies a virtual parameterized data object550that does not exist in the first database system514, the first database system can return an error to the client510. Otherwise, the first database system514sends a request620to the second database system518to cause the execution of the request operations using the parameterized data object522, with the values for the parameters526provided in the client request610.

In some cases, the request610provided by the client510can be directly sent to the second database system518after being received and processed by the first database system514. This is, the request610may include at least some commands that are executable by the second database system518without needing to be reformatted by the first database system514. In other cases, the first database system514can process or reformat the client request610for processing by the second database system518. In particular, if the first and second database systems514,518use common syntax, and typically if the virtual parameterized data object550and the parameterized data object522have the same name, then it may be possible to simply transmit the relevant portion of the request610to the second database system. However, often at least the names of the virtual parameterized data object550and the parameterized data object522will be different, and so in that case the first database system510can reformat the request610to use the name of the parameterized data object522. However, in other cases the first and second database systems514,518may be more operationally different, including where requests to access parameterized data objects differ in their syntax. In such case, the first database system514can store a template for accessing parameterized data objects522at the second database system518, and can populate the template with values from the request610. Or, the first database system514can otherwise include logic to translate the request610into a format that is executable by the second database system518.

The second database system518executes the request620, including using parameter values supplied in the request610. Execution of the request620can use components analogous to those described in conjunction withFIG.1. In the case where the parameterized data object522is a user-defined table function, the return type is a table, and a response630sent by the second database system518to the first database system514in response to the request620includes a results table634. The results table634can be generated by accessing one or more database objects526a-526c,528of the second computing system518.

In the case where the parameterized data object522is a parameterized view, the response can be a result set638that includes one or more rows, or a discrete value, depending on the nature of the view and the request620. The result set638can be generated by accessing one or more database objects526a-526c,528of the second computing system518.

The first database system514can then send execution results associated with the virtual parameterized data object550to the client510, where the execution results can be as provided by the second database system518, or where the execution results can be results that are obtained after further processing of the results from the second database system518, including results that are derived at least in part from executing the parametrized data object522, but which may not be identical to such results, or which may be a proper subset of such results.

Example 6—Example Storage of Information Regarding Virtual Parameterized Data Objects

FIG.7illustrates how data regarding virtual parameterized data objects can be stored, such as on a local computing system that processes requests involving virtual parameterized data objects and in turn sends requests to corresponding parameterized data objects in a remote system via data federation.

Table700illustrates information that can be maintained for parameters of a virtual parameterized data object. Various attributes are identified in column704, while example datatypes for these attributes are provided in column706. In a particular example, the information in the table700itself defines a table, where the columns of that table correspond to the attribute/rows of the table700. The rows of a table having a schema defined by the table700can have rows that provide particular values for the attributes704, for particular virtual parameterized data objects registered in the computing system. As has been described, the local computing system can make a call to confirm that a particular parameterized data object exists in the remote data system, and to obtain information, such as parameter information, for the parameterized data object, and that information can be stored in a table defined according to the schema of the table700.

The table700includes attributes that describe the virtual parameterized data object with which a parameter is associated. In particular, attribute710aidentifies a database schema associated with the virtual parameterized data object, attribute710bprovides a name (such as a name that might be used in a client query) of the virtual parameterized data object, and another identifier, such as a numeric identifier (for example, a UUID), for the virtual parameterized data object can be provided by attribute710c.

Identifying information can also be provided for particular parameters. For example, it may be desirable to be able to directly reference/locate a particular parameter, which can be accomplished using attribute710d, which provides an identifier (such as a numeric identifier, which can be a UUID) for the parameter, and attribute710e, which provides a name for a parameter, such as a name that may be used in a client request for a virtual parameterized data object that includes the parameter.

A variety of attributes can be included to provide information about the parameter, such as an attribute710fthat identifies a datatype for the parameters (such as whether the parameter value is a string, an integer, a Boolean value, etc., which as shown can be indicated using an integer value where specific integer values correspond to specific datatypes), an attribute710gthat specifies the length (such as a maximum length) of a parameter value (such as indicating a maximum length of a character array or string), and an attribute710hthat specifies a scale (identifying the location of a decimal point for numeric values) for the parameter. Methods/functions in computer science typically take parameters in a specific order. The order in which a parameter should be provided in a call to a remote parameterized data object is specified by attribute710i.

As noted, a particular application of disclosed techniques is for virtual user-defined table functions, where the return type is a table. To help process return results (such as to store results in an appropriate object instance and in an appropriate format for the corresponding object), it can be useful to know a particular schema associated with the table being returned and an identifier of the table, where these values can be provided for attributes710jand710k, respectively.

Parameters can have different types, such as whether they serve as input parameters, output parameters, or as both input and output parameters. Information about the function of a parameter can be provided as a value for an attribute710l. Attribute710lcan also be useful in scenarios where it is desired to make the table700more general, such as storing parameter information for parameterized data objects that are local, as well as virtual parametrized data objects that refer to remote parameterized data objects.

In addition, methods or functions can differ in whether a parameter is associated with a default value and whether null values are allowed for a particular parameter. The table700can reflect this information, where an attribute710mindicates whether a default value is specified and an attribute710nspecifies the default value, when a default value is provided. Note that a value in the attribute710nneed not correspond to the parameter datatype specified for the attribute710f. The value of the attribute710fcan be used to cast the value for the attribute710nto the correct datatype for the parameter in the call to the remote system. Attribute710ospecifies whether null values are allowed for a given parameter.

Table750represents one way of maintaining mapping information between local virtual parameterized data objects and remote parameterized data objects. As has been described, mapping information can be created when a request is made, such as from a client, to create a virtual parameterized data object. As with the table700, attributes in a column754of the table750can define a schema for another table that stores values for particular virtual parameterized data objects. Column756provides example datatypes for the attributes of column754.

Attributes758a,758b, and758care analogous to attributes710a,710b,710cof table700, providing a schema name where a virtual parameterized data object is defined, a name for the virtual parameterized data object, and another identifier for the virtual parameterized data object. Attribute758dis used to indicate a type of the virtual parameterized data object, which typically corresponds to a type of its corresponding parameterized data object. Examples of values that can be provided for attribute758dinclude “TABLE” (for a user-defined table function) and “VIEW” (for a parameterized view). Although the present disclosure has been described with respect to user-defined table functions, disclosed techniques can be used with other types of functions (aggregate functions or window functions, for example). An attribute758ecan be used to store information about a function type. More generally, the attribute758ecan be used to identify a subtype of the parameterized virtual data object indicated by a value for attribute758d.

Attributes758f-758ican be used to map a virtual parameterized data object to a parameterized data object. Tables750and700can be joined so that parameters in a local system can be correlated with a particular parameterized data object in a remote system. In particular, attribute758fidentifies a particular remote data source having the parameterized data object, attribute758gidentifies an adapter that is used to communicate with the remote data source, which can include information such as a network address, an API to be used, or logic to format requests in a manner that can be accepted and processed by the remote data source. For example, logic can include “connectors” as implemented in data federation technologies available from SAP SE, of Walldorf, Germany Attribute758hidentifies a schema of the remote data source that contains the parameterized data object, while attribute758iprovides a name/identifier of the particular parameterized data object in the schema of the remote system.

In some cases, table750can store information about remote data objects that are not parameterized. If so, the table750can include an attribute758jthat indicates whether a particular virtual data object is parameterized. A number of input parameters for a virtual parameterized data object can be indicated by a value for an attribute758k, while a number of return values can be provided using attribute758l.

Information about an “owner” of a virtual parameterized data object can be provided by a value for attribute758m, which information can be used for purposes such as determining who is allowed to access the object, or who is allowed to delete or update the object. A creation (or modification) time can be provided using attribute758n.

Example 8—Example Operations Involving Virtual Parameterized Data Objects

FIG.8illustrates a process800for executing a request for one or more database operations, where an operation specifies a virtual parameterized data object. The process800can be performed in the computing environments100,200,500, or600ofFIG.1,2,5, or6.

At a database system, a first request for one or more database operations is received at810. At least a first database operation of the one or more database operations is specified for a virtual parameterized data object, the virtual parameterized data object being mapped to a parameterized data object on a remote computing system accessed by the database system using data federation. Mapping information for the virtual parameterized data object is retrieved at820.

At,830, based at least in part on the mapping information, a second request for one or more database operations is generated. The second request includes at least a second database operation corresponding to the at least a first data operation. The second request is sent to the remote computing system at840. At850, execution results are received from the remote computing system. At least a portion of the execution results, or data derived at least in part therefrom, is sent at860in response to the first request.

Example 9—Computing Systems

FIG.9depicts a generalized example of a suitable computing system900in which the described innovations may be implemented. The computing system900is not intended to suggest any limitation as to scope of use or functionality of the present disclosure, as the innovations may be implemented in diverse general-purpose or special-purpose computing systems.

With reference toFIG.9, the computing system900includes one or more processing units910,915and memory920,925. InFIG.9, this basic configuration930is included within a dashed line. The processing units910,915execute computer-executable instructions, such as for implementing a database environment, and associated methods, described in Examples 1-8. A processing unit can be a general-purpose central processing unit (CPU), a processor in an application-specific integrated circuit (ASIC), or any other type of processor. In a multi-processing system, multiple processing units execute computer-executable instructions to increase processing power. For example,FIG.9shows a central processing unit910as well as a graphics processing unit or co-processing unit915. The tangible memory920,925may be volatile memory (e.g., registers, cache, RAM), nonvolatile memory (e.g., ROM, EEPROM, flash memory, etc.), or some combination of the two, accessible by the processing unit(s)910,915. The memory920,925stores software980implementing one or more innovations described herein, in the form of computer-executable instructions suitable for execution by the processing unit(s)910,915.

A computing system900may have additional features. For example, the computing system900includes storage940, one or more input devices950, one or more output devices960, and one or more communication connections970. An interconnection mechanism (not shown) such as a bus, controller, or network interconnects the components of the computing system900. Typically, operating system software (not shown) provides an operating environment for other software executing in the computing system900, and coordinates activities of the components of the computing system900.

The tangible storage940may be removable or non-removable, and includes magnetic disks, magnetic tapes or cassettes, CD-ROMs, DVDs, or any other medium which can be used to store information in a non-transitory way, and which can be accessed within the computing system900. The storage940stores instructions for the software980implementing one or more innovations described herein.

The input device(s)950may be a touch input device such as a keyboard, mouse, pen, or trackball, a voice input device, a scanning device, or another device that provides input to the computing system900. The output device(s)960may be a display, printer, speaker, CD-writer, or another device that provides output from the computing system900.

The communication connection(s)970enable communication over a communication medium to another computing entity, such as another database server. The communication medium conveys information such as computer-executable instructions, audio or video input or output, or other data in a modulated data signal. A modulated data signal is a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communication media can use an electrical, optical, RF, or other carrier.

The innovations can be described in the general context of computer-executable instructions, such as those included in program modules, being executed in a computing system on a target real or virtual processor. Generally, program modules or components include routines, programs, libraries, objects, classes, components, data structures, etc. that perform particular tasks or implement particular abstract data types. The functionality of the program modules may be combined or split between program modules as desired in various embodiments. Computer-executable instructions for program modules may be executed within a local or distributed computing system.

The terms “system” and “device” are used interchangeably herein. Unless the context clearly indicates otherwise, neither term implies any limitation on a type of computing system or computing device. In general, a computing system or computing device can be local or distributed, and can include any combination of special-purpose hardware and/or general-purpose hardware with software implementing the functionality described herein.

For the sake of presentation, the detailed description uses terms like “determine” and “use” to describe computer operations in a computing system. These terms are high-level abstractions for operations performed by a computer, and should not be confused with acts performed by a human being. The actual computer operations corresponding to these terms vary depending on implementation.

Example 10—Cloud Computing Environment

FIG.10depicts an example cloud computing environment1000in which the described technologies can be implemented. The cloud computing environment1000comprises cloud computing services1010. The cloud computing services1010can comprise various types of cloud computing resources, such as computer servers, data storage repositories, networking resources, etc. The cloud computing services1010can be centrally located (e.g., provided by a data center of a business or organization) or distributed (e.g., provided by various computing resources located at different locations, such as different data centers and/or located in different cities or countries).

The cloud computing services1010are utilized by various types of computing devices (e.g., client computing devices), such as computing devices1020,1022, and1024. For example, the computing devices (e.g.,1020,1022, and1024) can be computers (e.g., desktop or laptop computers), mobile devices (e.g., tablet computers or smart phones), or other types of computing devices. For example, the computing devices (e.g.,1020,1022, and1024) can utilize the cloud computing services1010to perform computing operators (e.g., data processing, data storage, and the like).

Example 11—Implementations

Although the operations of some of the disclosed methods are described in a particular, sequential order for convenient presentation, it should be understood that this manner of description encompasses rearrangement, unless a particular ordering is required by specific language set forth herein. For example, operations described sequentially may in some cases be rearranged or performed concurrently. Moreover, for the sake of simplicity, the attached figures may not show the various ways in which the disclosed methods can be used in conjunction with other methods.

Any of the disclosed methods can be implemented as computer-executable instructions or a computer program product stored on one or more computer-readable storage media, such as tangible, non-transitory computer-readable storage media, and executed on a computing device (e.g., any available computing device, including smart phones or other mobile devices that include computing hardware). Tangible computer-readable storage media are any available tangible media that can be accessed within a computing environment (e.g., one or more optical media discs such as DVD or CD, volatile memory components (such as DRAM or SRAM), or nonvolatile memory components (such as flash memory or hard drives)). By way of example and with reference toFIG.9, computer-readable storage media include memory920and925, and storage940. The term computer-readable storage media does not include signals and carrier waves. In addition, the term computer-readable storage media does not include communication connections (e.g.,970).

Any of the computer-executable instructions for implementing the disclosed techniques, as well as any data created and used during implementation of the disclosed embodiments, can be stored on one or more computer-readable storage media. The computer-executable instructions can be part of, for example, a dedicated software application or a software application that is accessed or downloaded via a web browser or other software application (such as a remote computing application). Such software can be executed, for example, on a single local computer (e.g., any suitable commercially available computer) or in a network environment (e.g., via the Internet, a wide-area network, a local-area network, a client-server network (such as a cloud computing network), or other such network) using one or more network computers.

For clarity, only certain selected aspects of the software-based implementations are described. Other details that are well known in the art are omitted. For example, it should be understood that the disclosed technology is not limited to any specific computer language or program. For instance, the disclosed technology can be implemented by software written in C++, Java, Perl, JavaScript, Python, Ruby, ABAP, Structured Query Language, Adobe Flash, or any other suitable programming language, or, in some examples, markup languages such as html or XML, or combinations of suitable programming languages and markup languages. Likewise, the disclosed technology is not limited to any particular computer or type of hardware. Certain details of suitable computers and hardware are well known and need not be set forth in detail in this disclosure.

Furthermore, any of the software-based embodiments (comprising, for example, computer-executable instructions for causing a computer to perform any of the disclosed methods) can be uploaded, downloaded, or remotely accessed through a suitable communication means. Such suitable communication means include, for example, the Internet, the World Wide Web, an intranet, software applications, cable (including fiber optic cable), magnetic communications, electromagnetic communications (including RF, microwave, and infrared communications), electronic communications, or other such communication means.

The disclosed methods, apparatus, and systems should not be construed as limiting in any way. Instead, the present disclosure is directed toward all novel and nonobvious features and aspects of the various disclosed embodiments, alone and in various combinations and sub combinations with one another. The disclosed methods, apparatus, and systems are not limited to any specific aspect or feature or combination thereof, nor do the disclosed embodiments require that any one or more specific advantages be present, or problems be solved.

The technologies from any example can be combined with the technologies described in any one or more of the other examples. In view of the many possible embodiments to which the principles of the disclosed technology may be applied, it should be recognized that the illustrated embodiments are examples of the disclosed technology and should not be taken as a limitation on the scope of the disclosed technology. Rather, the scope of the disclosed technology includes what is covered by the scope and spirit of the following claims.