CN114138821A

CN114138821A - Database query method, system and electronic equipment

Info

Publication number: CN114138821A
Application number: CN202210029323.8A
Authority: CN
Inventors: 黄浩; 曹攀; 黄潮勇; 吴华夫
Original assignee: Guangzhou Smart Software Co ltd
Current assignee: Guangzhou Smart Software Co ltd
Priority date: 2022-01-12
Filing date: 2022-01-12
Publication date: 2022-03-04

Abstract

The application provides a database query method, a system and electronic equipment, wherein the database query method comprises the following steps: the method comprises the steps of receiving SQL sentences input by a user on a user terminal and sent by the user terminal, obtaining multidimensional expression MDX sentences according to the first SQL sentences, obtaining multidimensional data in a multidimensional database according to the MDX sentences, simultaneously converting the multidimensional data into relational data, generating a relational data table according to the relational data, then obtaining second SQL sentences according to address information of the first SQL sentences and the relational data table, inquiring in the relational data table according to the second SQL sentences to obtain data inquiry results, and meanwhile sending the data inquiry results to the user terminal. According to the technical scheme, the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the user relational data table, so that the user experience is improved.

Description

Database query method, system and electronic equipment

Technical Field

The present application relates to the field of database query, and more particularly, to a database query method, system and electronic device.

Background

Structured Query Language (SQL), which is a database Query Language for accessing data and querying, updating, and managing relational databases, is a common Language for querying and managing databases. The relational database has the advantages of simple syntax, convenient understanding and use and easy maintenance, thereby being widely applied to data analysis work and having a larger user group. Under the complex data analysis scenes of calculation loop ratio, synchronization ratio and the like, compared with a relational database, the multidimensional database has the advantages of high data processing speed, high reaction time and high query efficiency.

However, the user group of the SQL statement cannot directly use the SQL statement to query the multidimensional database and cannot return to the user relational data table. This is because different types of databases typically require different types of statements to define and operate on. For example, a relational database needs to be defined and operated by SQL, and a multidimensional database needs to be defined and operated by multidimensional Expressions (Multi-Dimensional Expressions), MDX for short.

In the conventional data query method, if a user group of SQL statements wants to query data from the multidimensional database, the user group needs to relearn the MDX statements, which brings inconvenience to the user.

Disclosure of Invention

In view of this, the present application provides a database query method, a database query system, and an electronic device, which solve the problems that a user cannot query data in a multidimensional database using a structured query statement and cannot return relational data to the user, and improve user experience.

A first aspect of the present application provides a database query method, including: receiving a first SQL statement sent by a user terminal and input by a user on the user terminal; obtaining an MDX statement according to the first SQL statement, and acquiring multidimensional data in a multidimensional database according to the MDX statement; converting the multidimensional data into relational data, and generating a relational data table according to the relational data; obtaining a second SQL statement according to the first SQL statement and the address information of the relational data table; querying in the relational data table according to the second SQL statement to obtain a data query result; and sending the data query result to the user terminal.

With reference to the first aspect, in some embodiments of the first aspect, obtaining an MDX statement according to the first SQL statement, and obtaining multidimensional data in the multidimensional database according to the MDX statement includes: the method comprises the steps of obtaining a minimum sub-query statement from a first SQL statement, wherein the minimum sub-query statement is a query statement nested in the innermost layer of a from clause in the first SQL statement, converting the minimum sub-query statement into an MDX statement according to a first conversion rule, and obtaining multi-dimensional data in a multi-dimensional database according to the MDX statement, wherein the first conversion rule is a corresponding relation between a fragment statement table in the SQL statement and a query language in the multi-dimensional database.

With reference to the first aspect, in some embodiments of the first aspect, obtaining the minimum sub-query statement from the first SQL statement comprises: and performing tree decomposition on the first SQL statement to obtain a plurality of fragment statements, and acquiring a minimum sub-query statement from the plurality of fragment statements.

With reference to the first aspect, in some embodiments of the first aspect, converting the multidimensional data into relational data comprises: and converting the multidimensional data into relational data through a second conversion rule, wherein the second conversion rule is a mapping relation between the dimension of the Schema multidimensional database and the field of the relational database.

With reference to the first aspect, in some embodiments of the first aspect, obtaining the second SQL statement according to the first SQL statement and the address information of the relational data table includes: and replacing the address information in the from clause of the first SQL statement with the address information in the relational data table to obtain a second SQL statement.

With reference to the first aspect, in some embodiments of the first aspect, the method of the first aspect further comprises: storing the relational data table in an internal memory database, wherein the querying is performed in the relational data table according to the second SQL statement to obtain a data query result, and the data query result includes: and inquiring in the relational data table stored in the memory database according to the second SQL statement to obtain a data inquiry result.

In a second aspect of the present application, a database query method is provided, where the database query method includes: the user terminal sends the first SQL statement to the server so that the server can convert the first SQL statement into an MDX statement, a multidimensional database is queried according to the MDX statement to obtain multidimensional data, the multidimensional data are converted into relational data, and a relational data table is generated according to the relational data; the method comprises the steps that a server receives a first SQL statement sent by a user terminal and input by a user on the user terminal; the server obtains a multi-dimensional expression MDX statement according to the first SQL statement and obtains multi-dimensional data in a multi-dimensional database according to the MDX statement; the server converts the multidimensional data into relational data and generates a relational data table according to the relational data; the server obtains a second SQL statement according to the first SQL statement and the address information of the relational data table; the server queries in the relational data table according to the second SQL statement to obtain a data query result; the server sends a data query result to the user terminal; and the user terminal receives a data query result, wherein the data query result is obtained by the server according to the query of the second SQL statement generated by the first SQL statement in the relational data table.

In a third aspect of the present application, there is provided a database query system, including: the receiving module is used for receiving a first SQL statement which is sent by a user terminal and input by a user on the user terminal; the statement conversion module is used for converting the first SQL statement into an MDX statement; the data conversion module is used for converting the multidimensional data into relational data and generating a relational data table according to the relational data; the query module is used for querying the second SQL statement in the relational database to obtain a data query result; and the sending module is used for sending the data query result to the user terminal.

In a fourth aspect of the present application, there is provided a database query system, including:

the user terminal is used for sending the first SQL statement to the server so that the server can convert the first SQL statement into an MDX statement, query the multidimensional database according to the MDX statement to obtain multidimensional data, convert the multidimensional data into relational data and generate a relational data table according to the relational data; the server is used for generating a first SQL statement and a second SQL statement according to the first SQL statement;

the server is used for receiving a first SQL statement which is sent by the user terminal and input by the user on the user terminal; obtaining a multidimensional expression MDX statement according to the first SQL statement, and obtaining multidimensional data in a multidimensional database according to the MDX statement; converting the multidimensional data into relational data, and generating a relational data table according to the relational data; obtaining a second SQL statement according to the first SQL statement and the address information of the relational data table; querying in the relational data table according to the second SQL statement to obtain a data query result; and sending the data query result to the user terminal.

In a fifth aspect of the present application, there is provided a computer-readable storage medium having stored thereon computer-executable instructions that, when executed by a processor, implement the database query method as described above.

In a sixth aspect of the present application, there is provided an electronic device comprising: a processor; a memory for storing processor-executable instructions; and the processor is used for executing the database query method.

According to the technical scheme, a server receives a first SQL statement sent by a user terminal and input by a user on the user terminal, obtains an MDX statement according to the first SQL statement, obtains multidimensional data in a multidimensional database according to the MDX statement, converts the multidimensional data into relational data, generates a relational data table according to the relational data, obtains a second SQL statement according to address information of the first SQL statement and the relational data table, queries in the relational data table according to the second SQL statement, obtains a data query result, and sends the data query result to the user terminal. According to the technical scheme, the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the user relational data table, so that the user experience is improved.

Drawings

Fig. 1 is a system diagram illustrating a database query method according to an embodiment of the present application.

Fig. 2 is a schematic flowchart illustrating a database query method according to an embodiment of the present application.

Fig. 3 is a schematic flowchart illustrating a database query process according to an embodiment of the present application.

Fig. 4 is a schematic flowchart illustrating a database query method according to another embodiment of the present application.

Fig. 5 is a block diagram illustrating a database query system according to another embodiment of the present application.

Fig. 6 is a schematic diagram of an SQL statement parse tree according to an embodiment of the present application.

FIG. 7 is a diagram of a query table of SQL statements and MDX statements.

FIG. 8 is a diagram illustrating a mapping relationship between SQL statements and MDX statements.

Fig. 9 is a block diagram illustrating a database query system according to an embodiment of the present application.

Fig. 10 is a block diagram illustrating a database query system according to another embodiment of the present application.

Fig. 11 is a block diagram illustrating an electronic device according to an embodiment of the present application.

Detailed Description

The technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.

The relational database is a database established on the basis of a relational database model, and the tables of the relational database store data by adopting two-dimensional tables, and are logical groups with related information arranged in rows and columns. The logical structure of a relational model is a two-dimensional table, which is composed of rows and columns and called a relation. A database may contain any number of data tables. A row in the data table is a tuple, or a record. Each column in the data table is called a field, and the table is defined by the various fields it contains, each field describing the meaning of the data it contains.

Structured Query Language (SQL), which is a database Query and programming Language, is used to access data and Query, update, and manage a relational database system. The SQL query statement comprises a SELECT clause, an FROM clause and a WHERE clause, wherein the SELECT clause is used for specifying columns in the two-dimensional table, and the WHERE clause is used for specifying rows in the two-dimensional table.

A multidimensional Database (MDD) is a Database that stores data in an n-Dimensional array. Dimension (Dimension) is a specific angle from which people view data, and is a type of attribute that considers a problem, and a collection of attributes constitutes a Dimension (e.g., a temporal Dimension, a geographic Dimension, etc.). The hierarchy of dimensions (Level) is a certain angle (i.e. a certain dimension) at which people observe data, for example, the hierarchy of temporal dimensions may include: date, month, quarter, year. Members of dimension (Member): one value of a dimension is a description of the location of a data item in a dimension, e.g., "day of month of year" is a description of the location in the time dimension. The metric (Measure) is a value of a multidimensional array, for example, 1 month in 2000, shanghai, notebook, 0000. A Tuple (Tuple) consists of several members. A Set (Set) is a Set of several members or a Set of several tuples in the same dimension.

MDX statements (multidimensionality expressions) are a query language that supports the definition and manipulation of multidimensional objects and data. Multidimensional databases are typically operated and defined using MDX statements. A standard MDX query statement may include three basic object constructs for MDX: member (Member), Tuple (Tuple), Set (Set). Similar to a relational database, each MDX query statement also includes a SELECT clause, a FROM clause, and a WHERE clause. Compared with a relational database, the multidimensional database has more dimensions, such as a time dimension, and has the advantages of improving the data processing speed, quickening the response time and improving the query efficiency. The SELECT statement is used to SELECT dimensions and members. WHERE is used to define specific dimensions that return data for multiple members and membership conditions that return data for a single member.

SQL has been supported by various database manufacturers, becomes a standard in the database industry, and has a larger user group, and although the multidimensional database has the advantages of fast data processing speed, fast response time, and high query efficiency compared with the relational database, the SQL is not known to the public. Therefore, how to enable a user of an SQL statement to quickly query data in a multidimensional database by using the SQL statement and return the data to the user's relational data is a problem that needs to be solved urgently. In order to solve the technical problems, the application provides the following technical scheme.

Fig. 1 is a system diagram illustrating a method for querying a database according to an embodiment of the present disclosure. As shown in fig. 1, a system of a database query method according to an embodiment of the present application includes: a user terminal 10 and a server 20, wherein the server 20 is further provided with a multidimensional database 21 and a memory database 22. It should be understood that, as another embodiment, the multidimensional database 21 and the memory database 22 may also be provided outside the server 20, in which case the server 20 may access the multidimensional database 21 and the memory database 22 through a data transmission line or a network.

The user terminal 10 may be any terminal device and system capable of accessing the server 20, including but not limited to a cell phone, a tablet computer, a notebook computer, and a computer. The server 20 may be a general-purpose computer device that provides computing or application services to other clients (e.g., notebook computers, smart phones) in a network, or may be a small computer device such as a notebook computer or a tablet computer.

According to an embodiment of the present invention, the in-memory database 22 is used for caching the relational data table temporarily generated according to the multidimensional dataset so as to perform query using SQL statements.

Fig. 2 is a schematic flowchart illustrating a database query method according to an embodiment of the present application. The method may be performed by a server. As shown in fig. 2, the method includes the following.

In step S110, a first SQL statement sent by the user terminal and input by the user on the user terminal is received.

Specifically, the user terminal may be communicatively connected to the server via a network, so that the server may receive a first SQL statement input by the user on the user terminal.

In step S120, an MDX statement is obtained according to the first SQL statement, and multidimensional data is obtained in the multidimensional database according to the MDX statement.

In an embodiment, the server converts the first SQL statement into the MDX statement according to a correspondence between a fragment statement table in the SQL statement and a query language in the multidimensional database, that is, a first conversion rule. Meanwhile, the server acquires the multidimensional data in the multidimensional database through the MDX statement. The first conversion rule may be a correspondence relationship mapped in advance according to the business semantics of the SQL statement and the MDX statement, that is, a part with the same business semantics may be mapped.

It should be understood that the fragment statement table in the SQL statement includes the syntax, functions, or fields in the SQL statement.

In step S130, the multidimensional data is converted into relational data, and a relational data table is generated from the relational data.

In one embodiment, the server converts the multidimensional data into the relational data according to a second conversion rule, wherein the second conversion rule is a mapping relation between the dimensions of the multidimensional database and the fields of the relational database. Meanwhile, the server generates a temporary relational data table according to the relational data.

For example, as shown in fig. 8, the server converts the data of the Cube in the multidimensional database into the relational data according to the second conversion rule, wherein the Cube in the multidimensional database includes: a data model in the multidimensional database, Cube; the dimension in the multidimensional database, namely Hierarchy; the hierarchy under the dimension of the multidimensional database, namely Level; a set of indicators in the multidimensional database, namely measurers; each index in the index set in the multidimensional database is Measure. The relational database includes: a relational Database model, namely Database; a Table of the relational database, Table 1; the column name under the relational database table, namely table1. gum; another Table in the relational database, Table 2; column of another table in the relational database, table2. The specific conversion rules are as follows:

cube conversion to Database: and mapping the data model in the multidimensional database to a relational database model to form a table relation in the relational database.

Hierarchy converts to Table 1: the dimensions in the multidimensional database are mapped into a Table named Table1 in the relational database.

Level translates to Table1. Colum-the hierarchy under the multidimensional database dimension maps to the column name under the Table1 Table.

Measurers is converted to Table 2: the set of indices in the multidimensional database map to a Table named Table2 in the relational database.

Measure conversion to table2. column: each index in the set of indices in the multidimensional database maps to the column name under the Table2 Table.

In step S140, a second SQL statement is obtained according to the address information of the first SQL statement and the relational data table.

In an embodiment, the generated relational data table is stored in the memory database to obtain a relational database, and the address information in the from clause in the first SQL statement and the address information in the relational data table are replaced to obtain a second SQL statement.

For example, the first SQL statement is select a from chart1, the address information in the from clause is chart1, and the address information of the relational data table is table1. At this time, the address information in the from clause in the first SQL statement is replaced with the address information in the relational data table, that is, chart1 is replaced with table1, and the finally obtained second SQL statement is select a from table1.

It should be understood that the address information of the relational data table may be a table name of the relational data table.

In step S150, query is performed in the relational database through the second SQL statement to obtain a data query result.

Specifically, the server queries in the relational database through the second SQL statement to obtain a data query result.

In step S160, the data query result is transmitted to the user terminal.

Specifically, the server sends the data query result to the user terminal.

In an embodiment of the application, the user terminal may be in communication connection with the server through a network, so that the server may receive the first SQL statement input by the user on the user terminal. And the server converts the first SQL statement into an MDX statement according to the first conversion rule. And then, the server acquires multi-dimensional data in the multi-dimensional database through the MDX statement, converts the multi-dimensional data into relational data through a second conversion rule, generates a relational data table according to the relational data, and stores the generated relational data table into the memory database to obtain the relational database. And the server replaces the address information in the from clause in the first SQL statement with the address information in the relational data table to obtain a second SQL statement, queries in a relational database through the second SQL statement to obtain a data query result, and sends the data query result to the user terminal.

According to the technical scheme provided by the application, the server obtains the MDX statement according to the first SQL statement, obtains the multidimensional data in the multidimensional database according to the MDX statement, converts the multidimensional data into the relational data, generates the relational data table according to the relational data, obtains the second SQL statement according to the address information of the first SQL statement and the relational data table, and queries in the relational data table according to the second SQL statement to obtain the data query result, so that the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the relational data table of the user, and the user experience is improved.

In another embodiment of the present application, the server converts the minimum sub-query statement in the SQL statement into an MDX statement according to a first conversion rule, and obtains the multidimensional data in the multidimensional database according to the MDX statement, where the first conversion rule is a correspondence between a fragment statement table in the SQL statement and a query language in the multidimensional database.

Specifically, the server obtains a minimum sub-query statement from the first SQL statement, where the minimum sub-query statement is a query statement nested in a innermost layer of the from clauses in the first SQL statement, for example, when the first SQL statement is select a from (select b from (select c from d)), that is, when two layers of query statements are nested in the from clauses in the first SQL statement, the query statement in the innermost layer is select c from d. The server converts the minimum sub-query statement into an MDX statement based on the first conversion rule, stores the first conversion rule, and acquires the multidimensional data in the multidimensional database through the MDX statement. Because the query range of the minimum sub-query statement in the first SQL statement is maximum, the data information amount of the relational data table finally obtained through the minimum sub-query statement is larger than that of the relational data table finally obtained through the first SQL statement, and more data query results can be reliably obtained through the second SQL statement. In addition, the server stores the first conversion rule, so that when a user uses the first SQL sentence with the same minimum sub-query to perform data query, the server can directly call the stored first conversion rule to convert the minimum sub-query sentence into the MDX sentence, the operation efficiency of the server is improved, and the user experience is improved.

In another embodiment of the present application, the first SQL statement is tree-decomposed to obtain a plurality of fragment statements, and a minimum sub-query statement is obtained from the plurality of fragment statements.

Fig. 6 is a schematic diagram of an SQL statement parse tree according to an embodiment of the present application. As shown in fig. 6, the SQL statement parser performs tree decomposition on the first SQL statement to obtain a plurality of fragment statements, and the server traverses the plurality of fragment statements to obtain the query statement at the innermost layer in the from clause, that is, the minimum sub-query statement. The server can convert the SQL statement into the MDX statement according to the corresponding relation between the result of the tree decomposition and the fragment statement table in the stored SQL statement and the query language in the multi-dimensional database.

According to the method and the device, the first SQL statement is subjected to tree decomposition by the SQL statement parser, so that the server can flexibly traverse a plurality of fragment statements, and the minimum sub-query statement can be quickly obtained.

In addition, the SQL sentence resolver carries out tree decomposition on the first SQL sentence, and the server can splice a plurality of fragment sentences and the address information of the relational data table to obtain a second SQL sentence.

For example, the first SQL statement is select a from (select b from (select c from d)), the SQL statement parser tree-decomposes the first SQL statement into select, a, from, select, b, from, and select c from d, the minimum sub-query statement is select c from d, the address information of the relational data table is table1, at this time, the multiple fragment statements are spliced with the address information of the relational data table, and the finally obtained second SQL statement is select a from (select b from 1).

Fig. 3 is a schematic flowchart illustrating a database query process according to an embodiment of the present application. As shown in fig. 3, the flow of the database query includes the following contents:

s210, inputting a first SQL statement;

specifically, a user inputs a first SQL statement in a user terminal and sends the first SQL statement to a server.

S220, decomposing the first SQL statement;

specifically, after receiving the first SQL statement, the server calls an SQL statement parser to perform tree decomposition on the first SQL statement, so as to obtain a plurality of fragment statements.

S230, acquiring a minimum sub-query;

specifically, the server traverses the plurality of segment sentences, so as to obtain the query sentence at the innermost layer in the from clauses, that is, the minimum sub-query sentence.

S240, converting the minimum sub-query statement into an MDX statement by using an SQL statement parser;

specifically, the server calls the SQL statement parser to convert the minimum sub-query statement into the MDX statement according to the first conversion rule. The first conversion rule may be, for example, an inverse rule of the second conversion rule described above.

S250, acquiring multidimensional data and converting the multidimensional data into relational data;

specifically, multidimensional data is obtained in the multidimensional database according to the MDX statement, and the multidimensional data is converted into relational data according to a second conversion rule. Meanwhile, the server generates a relational data table from the relational data.

S260, storing the relational data table into a memory database;

specifically, the server stores the generated relational data table in the memory database to obtain the relational database.

S270, splicing the SQL sentences, and obtaining a data query result by using a second SQL sentence;

specifically, the server splices the plurality of fragment statements and the address information of the relational data table to obtain a second SQL statement, and queries in the relational database according to the second SQL statement to obtain a data query result.

And S280, sending a data query result to the user terminal.

According to the technical scheme provided by the embodiment of the application, the server receives a first SQL statement input by a user at the user terminal, the SQL statement analyzer is called to carry out tree decomposition on the first SQL statement to obtain a plurality of fragment statements, and the server traverses the plurality of fragment statements to obtain the query statement at the innermost layer in the from clauses, namely the minimum sub-query statement. And the server calls an SQL statement parser, converts the minimum sub-query statement into an MDX statement according to a first conversion rule, so as to acquire multi-dimensional data in the multi-dimensional database based on the MDX statement, and converts the multi-dimensional data into relational data according to a second conversion rule. Meanwhile, the server generates a relational data table from the relational data and stores the generated relational data table into the memory database to obtain the relational database. And the server splices the plurality of fragment statements and the address information of the relational data table to obtain a second SQL statement, queries in the relational database according to the second SQL statement to obtain a data query result, and sends the data query result to the user terminal. According to the technical scheme, the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the user relational data table, so that the user experience is improved.

The implementation process of the functions and actions of each terminal in the system is specifically described in the implementation process of the corresponding step in the method described in fig. 1, and is not described herein again.

Fig. 4 is a schematic flowchart illustrating a database query method according to another embodiment of the present application. The method may be performed by a user terminal. As shown in fig. 4, the method includes the following.

In step S410, the user terminal receives a first SQL statement input by the user, and sends the first SQL statement to the server, where the first SQL statement is used to trigger the server to convert the first SQL statement into an MDX statement, query the multidimensional database according to the MDX statement to obtain multidimensional data, convert the multidimensional data into relational data, and generate a relational data table according to the relational data.

Specifically, the user terminal may be in communication connection with the server through a network, so that the server converts the first SQL statement into an MDX statement, queries the multidimensional database according to the MDX statement to obtain multidimensional data, converts the multidimensional data into relational data, and generates a relational data table according to the relational data.

In step S420, the server receives the first SQL statement sent by the user terminal and input by the user on the user terminal.

Specifically, the server receives a first SQL statement input by a user on a user terminal.

In step S430, the server obtains a multidimensional expression MDX statement according to the first SQL statement, and obtains multidimensional data in the multidimensional database according to the MDX statement.

Specifically, after receiving the first SQL statement, the server obtains a multidimensional expression MDX statement according to the first SQL statement, and obtains multidimensional data in a multidimensional database according to the MDX statement.

In step S440, the server converts the multidimensional data into relational data and generates a relational data table from the relational data.

Specifically, the server converts the acquired multidimensional data into relational data and generates a relational data table according to the relational data.

In step S450, the server obtains a second SQL statement according to the address information of the first SQL statement and the relational data table.

Specifically, the server obtains the second SQL statement according to the first SQL statement and the address information of the relational data table.

In step S460, the server performs query in the relational data table according to the second SQL statement to obtain a data query result.

Specifically, the server queries in the relational data table according to the second SQL statement to obtain a data query result.

In step S470, the server transmits the data query result to the user terminal.

Specifically, the server sends the data query result to the user terminal.

In step S480, the user terminal receives a data query result, where the data query result is obtained by the server querying in the relational data table according to the second SQL statement generated by the first SQL statement.

Specifically, the user terminal receives a data query result, where the data query result is obtained by the server querying in the relational data table according to the second SQL statement generated by the first SQL statement.

According to the technical scheme provided by the embodiment of the application, the user terminal sends a first SQL statement to the server so that the server can convert the first SQL statement into an MDX statement, a multidimensional database is queried according to the MDX statement to obtain multidimensional data, the multidimensional data are converted into relational data, a relational data table is generated according to the relational data, the server receives the first SQL statement sent by the user terminal and input by a user on the user terminal, the multidimensional expression MDX statement is obtained according to the first SQL statement, the multidimensional data are obtained from the multidimensional database according to the MDX statement, then the multidimensional data are converted into the relational data, and the relational data table is generated according to the relational data. Further, the server obtains a second SQL statement according to the first SQL statement and the address information of the relational data table, and queries in the relational data table according to the second SQL statement to obtain a data query result. And finally, the server sends a data query result to the user terminal. And the user terminal receives a data query result, wherein the data query result is obtained by the server according to the query of the second SQL statement generated by the first SQL statement in the relational data table. According to the technical scheme, the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the user relational data table, so that the user experience is improved.

Optionally, as another embodiment, the method further includes: storing the relational data table in an internal memory database, wherein the querying is performed in the relational data table according to the second SQL statement to obtain a data query result, and the data query result includes: and inquiring in the relational data table stored in the memory database according to the second SQL statement to obtain a data inquiry result.

According to the embodiment of the invention, since the memory database is located in the cache region, and the access speed of the cache region is higher than that of the relational database, the query efficiency can be improved by storing the relational data table converted from the multidimensional data in the memory database.

The following describes the conversion rule between SQL and MDX according to the embodiment of the present application with reference to fig. 5.

The user interface of the user device may provide a text box for entering the SQL statement 510 in order to receive the SQL statement entered by the user. The SQL to MDX converter 520 may convert the SQL statement into an MDX statement according to the first conversion rule, and perform a query from the MDX database 530 according to the MDX statement to obtain a multidimensional result set. Multidimensional result set to relational result set converter 540 converts the multidimensional result set to a relational result set and stores in memory database 560. The SQL query result 570 may be read from the in-memory database based on the modified address SQL statement 550.

It should be understood that a conversion rule may be set between the metadata of the SQL database and the metadata of the MDX database, so that the above-mentioned conversion process of the multidimensional result set into the relational result set is performed according to the conversion rule.

The technical scheme of the application is described below by taking the example of inputting an SQL statement to query sales promotion modes for member activities and celebrities to promote sales.

FIG. 7 is a diagram of a query table of SQL statements and MDX statements.

As shown in fig. 7, the relational database of SQL statements of the relational database may be mapped to a relational data set corresponding to MDX statements of the multidimensional database.

The SQL statement may be expressed as: a select promotion mode, celebrity promotion, sales from (select promotion mode, promotion medium, sum from T where promotion mode = member activity group by promotion mode, promotion medium) TT where sales > 10000. The results obtained if the query is directly made in the SQL database are shown in table1 below.

TABLE1

Sales promotion method	Medium	Sales amount
			Member activity	XXX1	XXXX1
Member activity	XXX2	XXXX2
			Member activity	XXX3	XXXX3
Member activity	XXX4	XXXX4
			Member activity	XXX5	XXXX5
Celebrity sales promotion	XXX1	YYYY1
			Celebrity sales promotion	XXX2	YYYY2
Celebrity sales promotion	XXX3	YYYY3
			Celebrity sales promotion	XXX4	YYYY4
Celebrity sales promotion	XXX5	YYYY5

If the technical scheme of the application is adopted for query, firstly, an MDX statement is constructed according to SQL input by a user. The MDX statement represents the query intent by a multidimensional result set, which can be reduced to include a row header and a column header and corresponding values, as shown in Table2 below, with the promotion being row header and the sales being column header. When the conversion from SQL to MDX is performed, a field (or name) may be identified from the SQL statement, for example, the promotion mode is a field in the SQL statement, and should be level (including members such as membership activities and celebrity promotions) in the MDX statement, and the medium is a field in the SQL statement, and is level (including various media as members) in the MDX statement. A tuple set is formed by multiplying the promotional mode members with the media members as a head of line for the MDX. Sales are indexed in MDX as the column header of MDX, so query intent can be constructed. And adding a where conditional statement, for example, a promotion mode = membership activity, and a promotion mode = celebrity promotion. In this way, SQL statements can be converted into MDX statements.

TABLE2

Accordingly, the MDX statement may be expressed as: select filters ({ [ promotion means ] - [ Member Activities ] - [ promotion means ] - [ celebrity promotion ] } [ promotion media ] - [ members ] - [ Measures ] - [ sales ] - [ 10000) on rows, [ Measures ] - [ sales ] on columns from [ sales ]. The results of the query for a particular MDX are shown in table1.

The two statements have the same business semantics and can establish a mapping relation between the SQL statement and the MDX statement according to the business semantics.

Further, according to the constructed MDX statement, inquiring in an MDX database to obtain corresponding metadata, establishing an empty relational data table in the memory database, for example, the table may include three fields of promotion media, promotion modes and sales, and then inserting the metadata corresponding to the multidimensional result set into the relational data table. Further, the clause at the innermost layer of the SQL statement may be modified, and the address of the from of the clause at the innermost layer is modified to the address of the relational data table, so as to obtain the modified SQL statement. And finally, querying the relational data table by using the modified SQL statement to obtain a final query result.

Fig. 9 is a block diagram illustrating a database query system according to an embodiment of the present application. The method may be performed by a server. As shown in fig. 9, the database query system 900 includes a receiving module 910, a statement translation module 920, a data translation module 930, a query module 940, and a sending module 950.

The receiving module 910 is configured to receive a first SQL statement sent by a user terminal.

The statement conversion module 920 is used for converting the SQL statement into an MDX statement.

The data conversion module 930 is used for converting the multidimensional data into relational data.

The query module 940 is configured to query the relational database through a second SQL statement.

The sending module 950 is configured to send the data query result to the user terminal.

According to the technical scheme provided by the application, the server receives a first SQL statement sent by the user terminal and input by the user on the user terminal, obtains an MDX statement according to the first SQL statement, obtains multidimensional data in the multidimensional database according to the MDX statement, converts the multidimensional data into relational data, generates a relational data table according to the relational data, obtains a second SQL statement according to the first SQL statement and the address information of the relational data table, queries in the relational data table according to the second SQL statement, obtains a data query result, and sends the data query result to the user terminal. According to the technical scheme, the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the user relational data table, so that the user experience is improved.

In another embodiment of the present application, the database query system further includes a replacement module, configured to replace address information in the from clause in the first SQL statement with address information in the relational data table, so as to obtain a second SQL statement.

In another embodiment of the present application, the database query system further includes a decomposition module, configured to perform tree decomposition on the first SQL statement to obtain a plurality of fragment statements.

Fig. 10 is a block diagram illustrating a database query system according to another embodiment of the present application. The method may be performed by a user terminal. As shown in fig. 10, the database query system 600 includes a user terminal 610 and a server 620.

The user terminal 610 is configured to send the first SQL statement to the server.

The server 620 is configured to receive a data query result sent by the server to the user terminal.

According to the technical scheme provided by the embodiment of the application, the user terminal sends the first SQL statement to the server, so that the server converts the first SQL statement into the MDX statement, queries the multidimensional database according to the MDX statement to obtain multidimensional data, converts the multidimensional data into relational data, and generates the relational data table according to the relational data. The server receives a first SQL statement sent by a user terminal and input by a user on the user terminal, obtains a multi-dimensional expression MDX statement according to the first SQL statement, and obtains multi-dimensional data in a multi-dimensional database according to the MDX statement; then, converting the multidimensional data into relational data, and generating a relational data table according to the relational data; further, a second SQL statement is obtained according to the first SQL statement and the address information of the relational data table, query is conducted in the relational data table according to the second SQL statement, a data query result is obtained, and the data query result is sent to the user terminal. And the user terminal receives a data query result, wherein the data query result is obtained by the server according to the query of the second SQL statement generated by the first SQL statement in the relational data table. According to the technical scheme, the data can be queried in the multidimensional database by using the structured query statement, and can be returned to the user relational data table, so that the user experience is improved.

The implementation process of the functions and actions of each terminal in the system is specifically described in the implementation process of the corresponding step in the method described in fig. 5, and is not described herein again.

Referring to FIG. 11, system 700 includes a processing component 710 that further includes one or more processors and memory resources, represented by memory 720, for storing instructions, such as applications, that are executable by processing component 710. The application programs stored in memory 720 may include one or more modules that each correspond to a set of instructions. Further, the processing component 710 is configured to execute instructions to perform the database query method described above.

The system 700 may also include a power component configured to perform power management of the system 700, a wired or wireless network interface configured to connect the system 700 to a network, and an input-output (I/O) interface. The system 700 may operate based on an operating system stored in memory 720, such as Windows Server, Mac OS XTM, UnixTM, LinuxTM, FreeBSDTM, or the like.

A non-transitory computer readable storage medium having instructions therein which, when executed by a processor of the system 700, enable the system 700 to perform a method of database querying, comprising: receiving an SQL statement to obtain a first SQL statement; obtaining an MDX statement through a first SQL statement, and obtaining multidimensional data in a multidimensional database through the MDX statement; converting the multidimensional data into relational data and generating a relational data table; obtaining a second SQL statement through the first SQL statement and an address statement of the relational data table; querying in a relational database through a second SQL statement to obtain a data query result; and sending the data query result to the user terminal.

Those of ordinary skill in the art will appreciate that the various illustrative elements and algorithm steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware or combinations of computer software and electronic hardware. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the implementation. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present application.

It is clear to those skilled in the art that, for convenience and brevity of description, the specific working processes of the above-described systems, apparatuses and units may refer to the corresponding processes in the foregoing method embodiments, and are not described herein again.

In the several embodiments provided in the present application, it should be understood that the disclosed method, system and electronic device may be implemented in other manners. For example, the above-described method embodiments are merely illustrative, and for example, the division of the units is only one logical functional division, and other divisions may be realized in practice, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.

The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.

In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit.

The functions, if implemented in the form of software functional units and sold or used as a stand-alone product, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application or portions thereof that substantially contribute to the prior art may be embodied in the form of a software product stored in a storage medium and including instructions for causing a computer device (which may be a personal computer, a server, or a network device) to execute all or part of the steps of the method according to the embodiments of the present application. And the aforementioned storage medium includes: various media capable of storing program check codes, such as a usb disk, a removable hard disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a magnetic disk, or an optical disk.

It should be noted that the combination of the features in the present application is not limited to the combination described in the claims or the combination described in the embodiments, and all the features described in the present application may be freely combined or combined in any manner unless contradictory to each other.

It should be noted that the above listed embodiments are only specific examples of the present application, and obviously the present application is not limited to the above embodiments, and many similar variations follow. All modifications which would occur to one skilled in the art and which are, therefore, directly derivable or suggested by the disclosure herein are to be included within the scope of the present application.

It should be understood that the terms first, second, etc. used in the embodiments of the present application are only used for clearly describing the technical solutions of the embodiments of the present application, and are not used to limit the protection scope of the present application.

The above description is only for the preferred embodiment of the present application, and is not intended to limit the scope of the present application. Any modification, equivalent replacement, improvement and the like made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A database query method, comprising:

receiving a first SQL statement sent by a user terminal and input by a user on the user terminal;

obtaining a multidimensional expression MDX statement according to the first SQL statement, and acquiring multidimensional data in a multidimensional database according to the MDX statement;

converting the multidimensional data into relational data, and generating a relational data table according to the relational data;

obtaining a second SQL statement according to the address information of the first SQL statement and the relational data table;

querying in the relational data table according to the second SQL statement to obtain a data query result;

and sending the data query result to the user terminal.

2. The method according to claim 1, wherein the obtaining of the MDX statement from the first SQL statement and the obtaining of the multidimensional data in the multidimensional database from the MDX statement comprises:

obtaining a minimum sub-query statement from the first SQL statement, wherein the minimum sub-query statement is a query statement nested in the innermost layer of the from clauses in the first SQL statement,

and converting the minimum sub-query statement into an MDX statement according to a first conversion rule, and acquiring multi-dimensional data in a multi-dimensional database according to the MDX statement, wherein the first conversion rule is a corresponding relation between a fragment statement table in the SQL statement and a query language in the multi-dimensional database.

3. The database query method of claim 2, wherein the obtaining the smallest sub-query statement from the first SQL statement comprises:

and performing tree decomposition on the first SQL statement to obtain a plurality of fragment statements, and acquiring a minimum sub-query statement from the plurality of fragment statements.

4. The database query method of claim 1, wherein the converting the multidimensional data into relational data comprises: and converting the multidimensional data into relational data through a second conversion rule, wherein the second conversion rule is a mapping relation between the dimension of the Schema multidimensional database and the field of the relational database.

5. The method according to claim 1, wherein obtaining a second SQL statement according to the first SQL statement and the address information of the relational data table comprises:

and replacing the address information in the from clause of the first SQL statement with the address information of the relational data table to obtain a second SQL statement.

6. The database query method according to any one of claims 1 to 5, further comprising:

storing the relational data table in an in-memory database,

wherein, the querying in the relational data table according to the second SQL statement to obtain a data query result includes:

and inquiring in the relational data table stored in the memory database according to the second SQL statement to obtain a data inquiry result.

7. A method of database querying, comprising:

the method comprises the steps that a user terminal sends a first SQL statement to a server, so that the server converts the first SQL statement into an MDX statement, a multidimensional database is inquired according to the MDX statement to obtain multidimensional data, the multidimensional data are converted into relational data, and a relational data table is generated according to the relational data;

the server receives the first SQL statement which is sent by a user terminal and input by a user on the user terminal;

the server obtains a multi-dimensional expression MDX statement according to the first SQL statement and obtains multi-dimensional data in a multi-dimensional database according to the MDX statement;

the server converts the multidimensional data into relational data and generates a relational data table according to the relational data;

the server obtains a second SQL statement according to the first SQL statement and the address information of the relational data table;

the server queries in the relational data table according to the second SQL statement to obtain a data query result;

the server sends the data query result to the user terminal;

and the user terminal receives a data query result, wherein the data query result is obtained by the server according to the query of the second SQL statement generated by the first SQL statement in a relational data table.

8. A database query system, comprising:

the system comprises a receiving module, a sending module and a receiving module, wherein the receiving module is used for receiving a first SQL statement which is sent by a user terminal and is input by a user on the user terminal;

the statement conversion module is used for converting the first SQL statement into an MDX statement;

the data conversion module is used for converting the multidimensional data into relational data and generating a relational data table according to the relational data;

the query module is used for querying the second SQL statement in the relational database to obtain a data query result;

and the sending module is used for sending the data query result to the user terminal.

9. A database query system, comprising:

the system comprises a user terminal, a relational data table and a relational database, wherein the user terminal is used for sending a first SQL statement to a server so that the server can convert the first SQL statement into an MDX statement, inquiring the multidimensional database according to the MDX statement to obtain multidimensional data, converting the multidimensional data into relational data and generating the relational data table according to the relational data; the relational data table is used for storing a first SQL statement and a second SQL statement, and the first SQL statement and the second SQL statement are used for generating a first SQL statement and a second SQL statement;

the server is used for receiving a first SQL statement which is sent by the user terminal and input by a user on the user terminal; obtaining a multidimensional expression MDX statement according to the first SQL statement, and acquiring multidimensional data in a multidimensional database according to the MDX statement; converting the multidimensional data into relational data, and generating a relational data table according to the relational data; obtaining a second SQL statement according to the address information of the first SQL statement and the relational data table; querying in the relational data table according to the second SQL statement to obtain a data query result; and sending the data query result to the user terminal.

10. A computer-readable storage medium having stored thereon computer-executable instructions, which when executed by a processor, implement the database query method of any one of claims 1 to 6.

11. An electronic device, characterized in that the electronic device comprises:

a processor;

a memory for storing the processor-executable instructions;

the processor, configured to perform the database query method according to any one of claims 1 to 6.