Database writeback using an intermediary statement generator

Database writeback using an intermediary statement generator including receiving, by a statement generator, a table update request to update a table within a database on a cloud-based data warehouse, wherein the table update request comprises an update value and a selection of a row and a column from the table; verifying, by the statement generator, that the selection is updatable; generating, by the statement generator based on the selection and in response to the verification, an update database statement comprising a table identifier, a column identifier, a row identifier, and the update value; and sending, by the statement generator, the update database statement to the database on the cloud-based data warehouse, wherein the table of the database is updated in response to receiving the update database statement.

BACKGROUND

Field of the Invention

The field of the invention is data processing, or, more specifically, methods, apparatus, and products for database writeback using an intermediary statement generator.

Description of Related Art

Modern businesses may store large amounts of data in remote databases within cloud-based data warehouses. This data may be accessed using database query languages, such as structured query language (SQL). Manipulating the data stored in the database may require constructing complex queries beyond the abilities of most users.

SUMMARY

Methods, systems, and apparatus for database writeback using an intermediary statement generator. Database writeback using an intermediary statement generator includes receiving, by a statement generator, a table update request to update a table within a database on a cloud-based data warehouse, wherein the table update request comprises an update value and a selection of a row and a column from the table; verifying, by the statement generator, that the selection is updatable; generating, by the statement generator based on the selection and in response to the verification, an update database statement comprising a table identifier, a column identifier, a row identifier, and the update value; and sending, by the statement generator, the update database statement to the database on the cloud-based data warehouse, wherein the table of the database is updated in response to receiving the update database statement.

DETAILED DESCRIPTION

Exemplary methods, apparatus, and products for database writeback using an intermediary statement generator in accordance with the present invention are described with reference to the accompanying drawings, beginning withFIG.1.FIG.1sets forth a block diagram of automated computing machinery comprising an exemplary statement generator computing system (152) configured for database writeback using an intermediary statement generator according to embodiments of the present invention. The statement generator computing system (152) ofFIG.1includes at least one computer processor (156) or ‘CPU’ as well as random access memory (168) (‘RAM’) which is connected through a high speed memory bus (166) and bus adapter (158) to processor (156) and to other components of the statement generator computing system (152).

Stored in RAM (168) is an operating system (154). Operating systems useful in computers configured for database writeback using an intermediary statement generator according to embodiments of the present invention include UNIX™, Linux™, Microsoft Windows™, AIX™, and others as will occur to those of skill in the art. The operating system (154) in the example ofFIG.1is shown in RAM (168), but many components of such software typically are stored in non-volatile memory also, such as, for example, on data storage (170), such as a disk drive. Also stored in RAM is the statement generator (126), a module for database writeback using an intermediary statement generator according to embodiments of the present invention.

The communications adapter (167) is communicatively coupled to a wide area network (190) that also includes a cloud-based data warehouse (192) and a client computing system (194). The cloud-based data warehouse (192) is a computing system or group of computing systems that hosts a database or databases for access over the wide area network (190). The client computing system (194) is a computing system that accesses the database using the statement generator (126) on the statement generator computing system (152).

FIG.2shows an exemplary system for database writeback using an intermediary statement generator according to embodiments of the present invention. As shown inFIG.2, the system includes a client computing system (194), a statement generator computing system (152), and a cloud-based data warehouse (192). The client computing system (194) includes a graphical user interface (GUI) (202). The statement generator computing system (152) includes a statement generator (126). The cloud-based data warehouse (192) includes a database (204). The statement generator computing system (152) is an intermediary computing system between the client computing system (194) and the cloud-based data warehouse (192).

The GUI (202) is a visual presentation configured to present data sets in various forms including worksheets and graphical elements to a user. The GUI (202) also receives requests from a user for data sets from the database (204). The GUI (202) may be presented, in part, by the statement generator (126) and displayed on a client computing system (194) (e.g., on a system display or mobile touchscreen). The GUI (202) may be part of an Internet application that includes the statement generator (126) and is hosted on the statement generator computing system (152).

The database (204) is a collection of data and a management system for the data. A data set is a collection of data (such as a table or portion of a table) from the database (204). Data sets may be organized into columns and rows (also referred to as records). The particular columns, rows, and organization of the columns and rows that make up a data set may be specified in the database statement requesting the data set. A data set, as sent from the database to the statement generator computing system (152) and client computing system (194), may be a portion or subset of a source database table on the database.

The statement generator (126) is hardware, software, or an aggregation of hardware and software configured to receive a state specification from the client computing system (194), via the GUI (202). The statement generator (126) is also configured to generate database statements (i.e., queries) in response to manipulations of the GUI (202) described in the state specification. Such database statements may include writebacks to the database. Writeback refers to changes or updates to cells (i.e., fields) within a table on the database. The statement generator (126) may be part of a database query generator that generates the database statements.

The state specification is a collection of data describing inputs into the GUI (202). The state specification may include manipulations of GUI elements within the GUI (202) along with data entered into the GUI (202) by a user of the client computing system (194). Such manipulations and data may indicate requests for and manipulations of data sets. The state specification may be a standard file format used to exchange data in asynchronous browser-server communication. For example, the state specification may be a JavaScript Object Notation specification.

The state specification may include descriptions of elements that are used to apply changes to the data set. Such elements may include filters applied to the worksheet, the hierarchical level of the worksheet, joins performed within the worksheet, exposable parameters in the worksheet, and security for the worksheet. An exposable parameter is variable within a worksheet function that can be presented and adjusted without presenting, adjusting, or otherwise exposing the worksheet function itself. Security for the worksheet may include hiding portions of the data set that the particular user or a particular group of users is not authorized to view.

The statement generator (126) uses the state specification as input to compile a database statement (also referred to as a query). This compilation process may begin with state specification being converted into an abstract syntax tree. The abstract syntax tree may then be canonicalized into a canonicalized hierarchy. The canonicalized hierarchy may then be linearized into the worksheet algebra. The worksheet algebra may then be lowered into a relational algebra, which may then be lowered into the database query.

FIG.3shows an exemplary system for database writeback using an intermediary statement generator according to embodiments of the present invention. Specifically,FIG.3shows a worksheet GUI (300), which is a type of GUI as described inFIG.2. As shown inFIG.3, the exemplary worksheet GUI (300) includes a spreadsheet structure (302) and a list structure (304). The spreadsheet structure (302) includes a worksheet (shown as empty rows) with six columns (column A (306A), column B (306B), column C (306C), column D (306D), column E (306E), column F (306F)).

The worksheet GUI (300) is a GUI for presenting a worksheet. A worksheet is a presentation of a data set (such as a table, portion of a table, or combination of portions of different tables) from a database on a data warehouse. The spreadsheet structure (302) is a graphical element and organizing mechanism for the worksheet that presents the data set. The spreadsheet structure (302) displays the worksheet as rows of data organized by columns (column A (306A), column B (306B), column C (306C), column D (306D), column E (306E), column F (306F)). The columns delineate different categories of the data in each row of the worksheet. The columns may also be calculation columns that include calculation results using other columns in the worksheet.

The list structure (304) is a graphical element used to define and organize the hierarchical relationships between the columns (column A (306A), column B (306B), column C (306C), column D (306D), column E (306E), column F (306F)) of the data set. The term “hierarchical relationship” refers to subordinate and superior groupings of columns. For example, a database may include rows for an address book, and columns for state, county, city, and street. A data set from the database may be grouped first by state, then by county, and then by city. Accordingly, the state column would be at the highest level in the hierarchical relationship, the county column would be in the second level in the hierarchical relationship, and the city column would be at the lowest level in the hierarchical relationship.

The list structure (304) presents a dimensional hierarchy to the user. Specifically, the list structure (304) presents levels arranged hierarchically across at least one dimension. Each level within the list structure (304) is a position within a hierarchical relationship between columns (column A (306A), column B (306B), column C (306C), column D (306D), column E (306E), column F (306F)). The keys within the list structure (304) identify the one or more columns that are the participants in the hierarchical relationship. Each level may have more than one key.

One of the levels in the list structure (304) may be a base level. Columns selected for the base level provide data at the finest granularity. One of the levels in the list structure (304) may be a totals or root level. Columns selected for the totals level provide data at the highest granular level. For example, the totals level may include a field that calculates the sum of each row within a single column of the entire data set (i.e., not partitioned by any other column).

The worksheet GUI (300) may enable a user to drag and drop columns (column A (306A), column B (306B), column C (306C), column D (306D), column E (306E), column F (306F)) into the list structure (304). The order of the list structure (304) may specify the hierarchy of the columns relative to one another. A user may be able to drag and drop the columns in the list structure (304) at any time to redefine the hierarchical relationship between columns. The hierarchical relationship defined using the columns selected as keys in the list structure (304) may be utilized in charts such that drilling down (e.g., double click on a bar), enables a new chart to be generated based on a level lower in the hierarchy.

The worksheet GUI (300) may also include a mechanism for a user to request a table from a database to be presented as a worksheet in the worksheet GUI (300). Such a mechanism may be part of the interactivity of the worksheet. Specifically, a user may manipulate a worksheet (e.g., by dragging and dropping columns or rows, resorting columns or rows, etc.) and, in response, the worksheet GUI (300) may generate a request (e.g., in the form of a state specification) for a data set and send the request to the statement generator (126). Such a mechanism may also include a direct identification of the rows and columns of a database table that a user would like to access (e.g., via a selection of the rows and columns in a dialog box).

FIG.4shows an exemplary system for database writeback using an intermediary statement generator according to embodiments of the present invention. Specifically,FIG.4shows a table view GUI (400), which is a type of GUI as described inFIG.2. As shown inFIG.4, the exemplary table view GUI (400) includes a table view (402) and an update window (404). The table view (402) includes a table visualization (shown as empty values) with six columns (column A (406A), column B (406B), column C (406C), column D (406D), column E (406E), column F (406F)) and six rows (row A (408A), row B (408B), row C (408C), row D (408D), row E (408E), row F (408F)). The empty cell at column B (406B), row E (408E) has been selected (shown as shaded).

The table view GUI (400) is a GUI for presenting a table from a database. In contrast to the worksheet GUI shown inFIG.3, the table view GUI (400) may present only a single table or portion of a table from the database. The table view GUI (400) (through the table view (402)) may present a portion of a very large table (e.g., greater than one million records). Using the table view GUI (400), a user may update values in the table on the database. Specifically, changes made to a table presented in the table view (402) may then be pushed to the database on the cloud-based data warehouse.

The table view (402) is a graphical element for visualizing a table or portion of a table from the database. The table view (402) displays the table as rows (row A (408A), row B (408B), row C (408C), row D (408D), row E (408E), row F (408F)) of values organized by columns (column A (306A), column B (306B), column C (306C), column D (306D), column E (306E), column F (306F)).

The update window (404) is a graphical element for updating a value in the table presented in the table view (402). The update window (404) presents a field to a user (shown containing a value of “#Value”) allowing a user to enter a value into the selected cell of the table. The update window (404) also includes options to set the value of the cell to “null”, cancel the operation, or commit the change to the value (“update” button).

For further explanation,FIG.5sets forth a flow chart illustrating an exemplary method for database writeback using an intermediary statement generator according to embodiments of the present invention that includes receiving (502), by a statement generator (126), a table update request (520) to update a table within a database on a cloud-based data warehouse (192), wherein the table update request (520) comprises an update value and a selection of a row and a column from the table. The table update request (520) is an instruction to add or overwrite a value in a cell of a table on a database. The table update request (520) includes an update value which is a value that is to be written to the database. The selection is an indication of the particular cell (column identifier and row identifier) to which the update value is to be written. The table update request (520) also includes an identifier of the particular table to be updated. The table update request (520) may include a request to add a new row or a new column to the database. The table update request (520) may be received from the client computing system (194) in the form of a state specification or an invocation of one or more programing interfaces exposed by the statement generator (126).

The method ofFIG.5further includes verifying (504), by the statement generator (126), that the selection is updatable. Verifying (504), by the statement generator (126), that the selection is updatable is performed prior to sending the database statement (522) to the cloud-based data warehouse (192). Further, verifying (504), by the statement generator (126), that the selection is updatable is performed on the statement generator computing system rather than the cloud-based data warehouse (192).

Verifying (504), by the statement generator (126), that the selection is updatable may be carried out by determining that a user associated with the update request is authorized to update the selection. Specifically, the statement generator may compare the user's authorizations against the permission associated with the table, portion of the table, or specific targeted cell of the table. If the user is authorized to change the value of the cell, the statement generator (126) verifies that the selection is updatable.

Verifying (504), by the statement generator (126), that the selection is updatable may also be carried out by determining that the update value is an acceptable type of value. The statement generator (126) or database may be configured to only accept a particular type of value in the target cell. Acceptable types of values may include, for example, an alphanumeric string, a date, an integer, a currency value, etc. If the update value is one of the acceptable types of values, the statement generator (126) verifies that the selection is updatable.

Verifying (504), by the statement generator (126), that the selection is updatable may also be carried out by determining that the update value is one of a group of acceptable update values. The statement generator (126) or database may be configured to only accept one of a group of acceptable values in the target cell. Groups of acceptable values may include, for example, days of the week, months of the year, employees of a company, countries in Europe, etc. If the update value is one of the acceptable values in the group of values, the statement generator (126) verifies that the selection is updatable.

Verifying (504), by the statement generator (126), that the selection is updatable may also include retrieving permission information for the table from the cloud-based data warehouse. Specifically, the statement generator (126) may communicate with the database and/or the cloud-based data warehouse to obtain the permission information for the table. Permission information may include the permissions associated with the target cell for updating, the acceptable value types, and acceptable update values. Once the permission information for the selection is retrieve, the update value is compared to the permission information.

The method ofFIG.5further includes generating (506), by the statement generator (126) based on the selection and in response to the verification, an update database statement (522) comprising a table identifier, a column identifier, a row identifier, and the update value. Generating (506), by the statement generator (126) based on the selection and in response to the verification, an update database statement (522) comprising a table identifier, a column identifier, a row identifier, and the update value may be carried out by compiling the update database statement from the table update request (520). The update database statement may be a structured query language (SQL) statement.

The method ofFIG.5further includes sending (508), by the statement generator (126), the update database statement (522) to the database on the cloud-based data warehouse (192), wherein the table of the database is updated in response to receiving the update database statement (522). Sending (508), by the statement generator (126), the update database statement (522) to the database on the cloud-based data warehouse (192), wherein the table of the database is updated in response to receiving the update database statement (522) may be carried out by transmitting the update database statement (522) over a wide area network to the cloud-based data warehouse.

Sending (508), by the statement generator (126), the update database statement (522) to the database on the cloud-based data warehouse (192) may include passing credentials for a user associated with the update request to the cloud-based data warehouse. Altering the table on the database may require authentication using credentials for the user associated with the table update request (520) or credentials associated with the statement generator (126). The credentials or authentication token may be included in the update database statement (522).

The above features improve the operation of the computer system by providing a mechanism for users to update values in a database through a table visualization. This is accomplished by receiving a table update request, verifying portions of the request, generating the database statement, and sending the database statement to the database.

For further explanation,FIG.6sets forth a flow chart illustrating a further exemplary method for database writeback using an intermediary statement generator according to embodiments of the present invention that includes receiving (502), by a statement generator (126), a table update request (520) to update a table within a database on a cloud-based data warehouse (192), wherein the table update request (520) comprises an update value and a selection of a row and a column from the table; verifying (504), by the statement generator (126), that the selection is updatable; generating (506), by the statement generator (126) based on the selection and in response to the verification, an update database statement (522) comprising a table identifier, a column identifier, a row identifier, and the update value; and sending (508), by the statement generator (126), the update database statement (522) to the database on the cloud-based data warehouse (192), wherein the table of the database is updated in response to receiving the update database statement (522).

The method ofFIG.6differs from the method ofFIG.5, however, in that receiving (502), by a statement generator (126), a table update request (520) to update a table within a database on a cloud-based data warehouse (192), wherein the table update request (520) comprises an update value and a selection of a row and a column from the table includes presenting (602), through a graphical user interface on a client computing system (194), a table visualization of the table from the database on the cloud-based data warehouse (192); and detecting (604) a user interaction indicating the selection of the row and the column from the table visualization.

Presenting (602), through a graphical user interface on a client computing system (194), a table visualization of the table from the database on the cloud-based data warehouse (192) may be carried out by retrieving the table from the cloud-based data warehouse (192), generating the table visualization of the table, and sending, to the client computing system (194) the table visualization. A table visualization is a graphical presentation of a table or portion of a table from a database on a cloud-based data warehouse (192). The table visualization may be the worksheet or table view described above inFIG.3andFIG.4.

Detecting (604) a user interaction indicating the selection of the row and the column from the table visualization may be carried out by receiving a state specification describing the user interaction with the table visualization. Once the statement generator (126) receives the state specification, the state specification may be used to generate the update database statement. Specifically, generating the update database statement may include compiling the update database statement from a state specification of the graphical user interface on the client computing system.

In view of the explanations set forth above, readers will recognize that the benefits of database writeback using an intermediary statement generator according to embodiments of the present invention include:Improving the operation of a computing system by providing a mechanism for users to update values in a database through a table visualization, increasing computing system efficiency and usability.