A data query method and device

Abstract

The present invention provides a data query method and device, the method comprising: pre-configuring an associated virtual column for a specified real column in a data table, and configuring the data source of the virtual column; receiving a database query request for the data table; performing a first conversion from a virtual column to a real column on the database query request according to the association relationship between the real column and the virtual column in the data table; querying the data table using the database query request after the first conversion to obtain an intermediate query result; and determining a final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request.

Description

Technical Field

[0001] The present invention relates to the field of information processing technology, and in particular to a data query method and device. Background Art

[0002] The operator's logistics real-time data platform has real-time access to business data such as warehousing, distribution, customer service, merchants, and basic data configured for the business such as warehouses, sites, and delivery personnel. All of this data is stored in the Elasticsearch cluster (hereinafter referred to as ES).

[0003] In the prior art, ES does not support the association query between data tables. Therefore, when configuring reports for related businesses, it is necessary to pull the data of multiple data tables into a large business wide table based on the association relationship between the data tables, and then query the business wide table to query the reports of related businesses. For example: to customize a real-time monitoring report for warehouse distribution, it is necessary to pull the data of data tables such as order table, waybill table, and fulfillment time table into a large business wide table, and obtain the real-time monitoring report for warehouse distribution by performing real-time query on the business wide table.

[0004] Using business wide tables can solve the problem that ES queries do not support association queries between data tables, but it also brings many problems. For example, when updating the data tables in the generated business wide tables, the business wide tables need to be updated at the same time. The amount of updated data is very large, and when the association relationship of the business wide tables is complex, the association of the data tables that generate the business wide tables will increase the development complexity and data pulling performance of the business wide tables. Summary of the invention

[0005] In view of this, the purpose of the present invention is to provide a data query method and device, which does not require pulling all the data from multiple data tables into the same business wide table, thereby avoiding many problems caused by pulling all the data from multiple data tables into the same business wide table.

[0006] In order to achieve the above object, the present invention provides the following technical solutions:

[0007] A data query method is provided, which configures an associated virtual column for a specified real column in a data table in advance, and configures the data source of the virtual column. The method comprises:

[0008] receiving a database query request for the data table;

[0009] According to the association relationship between the real column and the virtual column in the data table, performing a first conversion from the virtual column to the real column on the database query request;

[0010] Using the database query request after the first conversion, query the data table to obtain an intermediate query result;

[0011] A final query result corresponding to the database query request is determined according to the intermediate query result and data sources of each virtual column in the database query request.

[0012] A data query device, comprising:

[0013] A configuration unit, used to configure an associated virtual column for a specified real column in a data table in advance, and configure a data source for the virtual column;

[0014] A receiving unit, configured to receive a database query request for the data table;

[0015] A conversion unit, configured to perform a first conversion from a virtual column to a real column on the database query request according to an association relationship between a real column and a virtual column in the data table;

[0016] A query unit, configured to query the data table using the database query request after the first conversion to obtain an intermediate query result;

[0017] A processing unit is used to determine a final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request.

[0018] An electronic device comprises: at least one processor, and a memory connected to the at least one processor via a bus; the memory stores one or more computer programs executable by the at least one processor; and the at least one processor implements the steps in the above-mentioned data query method when executing the one or more computer programs.

[0019] A computer-readable storage medium stores one or more computer programs, and the one or more computer programs implement the above data query method when executed by a processor.

[0020] It can be seen from the above technical solution that in the present invention, by configuring an associated virtual column for a specified real column in a data table, and configuring the data source of the virtual column, a first conversion from a virtual column to a real column is performed on a database query request for the data table according to the association relationship between the real column and the virtual column in the data table; the database query request after the first conversion is used to query the data table to obtain an intermediate query result; and the final query result corresponding to the database query request is determined according to the intermediate query result and the data source of each virtual column in the database query request. It can be seen that in the present invention, there is no need to pull data from multiple data tables into the same business wide table, but rather to implement associated queries on multiple data tables by configuring virtual columns for specified real columns in the data table, thereby avoiding many problems caused by pulling data from multiple data tables into the same business wide table. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] Figure 1 is a flow chart of a data query method provided by an embodiment of the present invention;

[0022] Figure 2 It is a schematic diagram of the architecture of the real-time platform according to an embodiment of the present invention;

[0023] Figure 3 It is a structural schematic diagram of a data query device according to an embodiment of the present invention;

[0024] Figure 4 It is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0025] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the technical solution of the present invention is described in detail below in conjunction with the accompanying drawings and according to embodiments.

[0026] See also Figure 1 , Figure 1 is a flow chart of a data query method provided by an embodiment of the present invention, such as Figure 1 As shown, the method mainly includes the following steps:

[0027] Step 101: Pre-configure an associated virtual column for a specified real column in a data table, and configure the data source of the virtual column.

[0028] In the present invention, the data column (with column name and column value) actually existing in the data table is called a real column, and an associated virtual column is configured for the specified real column in the data table, so that the real column and the virtual column establish an association relationship. There is a value correspondence relationship or a value conversion relationship between the real column and the virtual column with an association relationship. In the present invention, the data source of the virtual column is used to represent the value correspondence relationship or the value conversion relationship between the real column associated with the virtual column and the virtual column.

[0029] In the present invention, by configuring an associated virtual column for a specified real column in a data table and configuring the data source of the virtual column, a user can query data that does not exist in the data table (ie, the virtual column), which will be described in detail later.

[0030] In the embodiment of the present invention, the data sources of the virtual column are as follows: data table, conversion function, regular matching rule, which are introduced one by one below:

[0031] 1) Data Sheet

[0032] In the data table that is the data source of the virtual column, the value correspondence between the real column and the virtual column is stored.

[0033] Normally, a data table does not exist in isolation, but has an association relationship with one or more other data tables. For example, two data tables contain one or more identical data columns, and these identical data columns are associated with each other. Then, using this association relationship, an associated query on the two data tables can be implemented. However, since ES does not support associated queries in data tables, in an embodiment of the present invention, an associated virtual column can be configured for the one or more identical data columns in one of the data tables (this virtual column is another several (one or more) data columns in another data table, and has a value corresponding relationship with the one or more identical data columns), and the other data table is configured as the data source of this virtual column.

[0034] The following uses the site table shown in Table 1 and the waybill table shown in Table 2 as examples for explanation:

[0035] siteId siteName 00000001 Shanghai Hongkou Station 00000002 Shanghai Yangpu Station 00000003 Shanghai Quyang Railway Station 00000004 Jinan Huaqiang Station

[0036] Table 1

[0037] Waybill number (seqNum) Order time Estimated time of arrival (rTime) siteId 12345 2019-04-29 21:01:41 2019-04-30 10:00:00 00000001 45662 2019-04-29 17:30:02 2019-04-30 10:00:00 00000002 76554 2019-04-28 23:48:34 2019-04-29 15:00:00 00000003 29898 2019-04-28 20:34:25 2019-04-29 15:00:00 00000004

[0038] Table 2

[0039] It can be seen from Table 1 and Table 2 that the two tables have the same real column "siteId". The associated virtual column "siteName" can be configured for the real column "siteId" in Table 2, as shown in Table 3. The data source of the virtual column "siteName" is configured as Table 1.

[0040] Waybill number (seqNum) Order time Estimated time of arrival (rTime) siteId siteName (virtual column) 12345 2019-04-29 21:01:41 2019-04-30 10:00:00 00000001 45662 2019-04-29 17:30:02 2019-04-30 10:00:00 00000002 76554 2019-04-28 23:48:34 2019-04-29 15:00:00 00000003 29898 2019-04-28 20:34:25 2019-04-29 15:00:00 00000004

[0041] Table 3

[0042] In the present invention, the data table used as the data source of the virtual column can be a basic data table in ES, such as the site table shown in Table 1 above; or it can be a user-defined data table, such as a user-defined waybill type value and its meaning comparison table, as shown in Table 4:

[0043] Waybill Type meaning 0 Initial test status 1 Collection 2 duly delivered 3 Rejection

[0044] Table 4

[0045] 2) Conversion function

[0046] The conversion function represents the value conversion relationship between the real column and the virtual column;

[0047] In actual applications, for some data columns in a data table, users may not need to pay attention to the values ​​of these data columns, but need to pay attention to the values ​​obtained after the values ​​of these data columns are converted by the conversion function. For this situation, in an embodiment of the present invention, associated virtual columns can be configured for these real columns in the data table, and the data source of the virtual column is configured as the conversion function.

[0048] The following is an example of the order table shown in Table 5.

[0049]

[0050]

[0051] Table 5

[0052] In the order table shown in Table 5 above, there is a data column called "address". The value of "address" includes the complete delivery address, such as "Shijiazhuang University, Shijiazhuang City, Hebei Province", but the user may only be interested in the delivery province in the delivery address. In this case, an associated virtual column "province" can be configured for the real column "address", and the data source of the virtual column is configured as the conversion function: substring(address, 0, 3).

[0053] In the order table shown in Table 5 above, there are two real columns, "price" and "actual_price". The former indicates the price of the product, and the latter indicates the actual payment amount. If the user is not interested in the values ​​of these two real columns, but only in the discount amount, then an associated virtual column "discount_price" can be configured for the real columns "price" and "actual_price", and the data source of the virtual column is configured as the conversion function: price-actual_price.

[0054] 3) Regular matching rules

[0055] Regular matching rules represent the value conversion relationship between real columns and virtual columns.

[0056] In practical applications, some fields in a data table are represented by numbers, and it is difficult for users to understand their meaning. However, what users are really interested in may be the meaning of the numbers rather than the numbers themselves.

[0057] For example, there is a time-limited field sendpay in the data table, which is represented by a 500-digit number. It is difficult for users to understand the specific meaning of the 500-digit number. However, this 500-digit number actually only corresponds to 6 delivery categories. Regular expressions can be used to distinguish these 6 delivery categories, as shown in Table 6:

[0058] Regular Expressions Shipping Category 1.{0} 211 2.{0} Next day delivery 9.{0} 411 1.{33}3 Evening delivery 3.{0} Next-day delivery 4.{0} 4 days to arrive

[0059] Table 6

[0060] The above Table 6 is actually a regular matching rule, in which each row represents a regular matching sub-rule. It can be seen that each regular matching sub-rule includes a regular expression and a delivery category value, and its specific meaning is: the delivery category corresponding to all sendpay values ​​that match the regular expression in the regular matching sub-rule is the delivery category in the regular matching sub-rule.

[0061] The following takes the order table shown in Table 5 as an example. There is only a real column "sendpay" in the order table. However, users may not pay attention to the 500-bit sendpay value, but the delivery category corresponding to the sendpay value. Then, the associated virtual column "sendpay_name" can be configured for the real column "sendpay" in the order table, and the data source of the virtual column "sendpay_name" is the regular matching rule shown in Table 5 above.

[0062] From the above introduction about the data source of the virtual column, it can be seen that in the present invention, configuring the data source of the virtual column is actually configuring the data table information (such as the table name) storing the value correspondence relationship between the virtual column and the associated real column, configuring the conversion function representing the value conversion relationship between the virtual column and the associated real column, or configuring the regular matching rule representing the value conversion relationship between the virtual column and the associated real column.

[0063] In addition, it should be noted that, in the embodiments of the present invention, the number of real columns and virtual columns with an associated relationship can be a one-to-one (i.e., one real column corresponds to one virtual column), a one-to-many (i.e., one real column corresponds to multiple virtual columns), many-to-one (i.e., multiple real columns correspond to one virtual column), or many-to-many (i.e., multiple real columns correspond to multiple virtual columns). Regardless of the corresponding relationship, the virtual column value corresponding to the real column value can be determined according to the data source of the virtual column.

[0064] This step 101 only needs to be performed once during the initial configuration phase.

[0065] Step 102: receiving a database query request for the data table;

[0066] When a user needs to query data in a data table, a query interface can be used to trigger a database query request for the data table to a backend service device. After the backend service device receives the database query request, if it is determined that the database query request does not contain a virtual column, the data table can be queried according to a normal data query process. If it is determined that the database query request contains a virtual column, subsequent steps 103 to 105 need to be executed to perform data query.

[0067] Step 103: Perform a first conversion from virtual columns to real columns on the database query request according to the association relationship between real columns and virtual columns in the data table.

[0068] In actual applications, since virtual column values ​​are not stored in the data table, when there are virtual columns in the database query request, it is actually impossible to directly query the data table. Therefore, it is necessary to perform a first conversion from virtual columns to real columns on the database query request. The database query request after the first conversion only includes real columns. Therefore, the data table can be queried based on the database query request after the first conversion to obtain an intermediate query result. Thereafter, the final query result can be determined based on the intermediate query result and the data source of the virtual column.

[0069] In actual applications, a database query request generally includes a query target column and a query condition. For example, in the database query request "select seqNum siteName from table3 wheresiteId=00000001" for the above Table 3 (table3), the query target columns are seqNum and siteName, and the query condition is siteId=00000001. The query condition column in the query condition is siteId, and the column value in the query condition is 00000001.

[0070] In the embodiment of the present invention, according to the association relationship between the real column and the virtual column in the data table, the first conversion from the virtual column to the real column is performed on the database query request, which specifically includes the following steps:

[0071] S11. If the query target column of the database query request includes a virtual column, the virtual column is replaced with a real column associated with the virtual column;

[0072] S12: If the query condition column of the database query request includes a virtual column, use the real column associated with the virtual column as the query target column of the database query request, and delete the query condition to which the virtual column belongs from the query condition of the database query request.

[0073] For example, in the above database query request "select seqNum siteName from table3 wheresiteId=00000001", siteName is a virtual column configured for the real column siteId in table3. According to the above method, the above database query request can be converted into a new database query request "selectseqNum siteId from table3 where siteId=00000001", and then the new query request can be used to search table3 to obtain an intermediate query result.

[0074] In actual applications, when querying a data table, the same data column does not need to be queried multiple times. Therefore, in the above step S12, if the real column associated with the virtual column is already the query target column of the database query request, there is no need to use the real column associated with the virtual column as the query target column of the database query request again. It is only necessary to directly delete the query condition to which the virtual column belongs from the query condition of the database query request.

[0075] Step 104: Query the data table using the database query request after the first conversion to obtain an intermediate query result.

[0076] Since the query target column and the query condition column in the database query request after the first conversion are both real columns in the data table and there are no virtual columns, the data table can be queried according to the normal data query process.

[0077] Step 105: Determine a final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request.

[0078] In an embodiment of the present invention, determining a final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request specifically includes the following steps:

[0079] S21, for each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column;

[0080] S22: query the intermediate query result using the database query request after the second conversion to obtain a final query result of the database query request.

[0081] In the embodiment of the present invention, the data sources of the virtual column include three types: data table, conversion function, and regular matching rule. According to different data sources of the virtual column, the corresponding second conversion method for the database query request is also different, which is specifically introduced as follows:

[0082] I) The data source of the virtual column is the data table:

[0083] As mentioned above, when the data source of a virtual column is a data table, the data table serving as the data source of the virtual column stores the correspondence between the real column associated with the virtual column and the value of the virtual column.

[0084] When the data source of the virtual column is a data table, the second conversion of the database query request is actually to convert the database query request into a database association query request for the intermediate query result obtained in step 104 and the data table as the data source of the virtual column.

[0085] In the embodiment of the present invention, the step 21 of performing a second conversion on the database query request based on the data source of each virtual column of the database query request includes:

[0086] When the data source of the virtual column is a data table, the database query request is converted into a left join query request, and the left join query request meets the following conditions:

[0087] The query target column of the left join query request is the query target column in the database query request;

[0088] The left table and the right table of the left join query request are respectively the intermediate query result and the data table;

[0089] The connection condition of the left join query request is: the real column value in the intermediate query result is equal to the real column value in the data table;

[0090] The query conditions of the left join query request are all query conditions in the database query request whose query condition columns are not virtual columns.

[0091] Taking the database query request "select seqNum siteNamefrom table3 where siteId=00000001" for the above table 3 (table3) as an example, the new database query request obtained after the first conversion of the database query request is "select seqNum siteId from table3 where siteId=00000001", and the intermediate query results obtained by querying table 3 using the new database query request are specifically shown in Table 7:

[0092] Waybill number (seqNum) siteId 12345 00000001

[0093] Table 7

[0094] After the database query request is converted to the data source (i.e., Table 1) based on the virtual column siteName, the left join query request is: "select seqNum siteName from table7 left join table1on table7.siteId=table1.siteId where table7.siteId=00000001". This left join query request is a database association query request. The final query result of the database query request can be obtained by performing an association query on Table 7 and Table 1 using this database association query request, as shown in Table 8:

[0095] Waybill number (seqNum) siteName 12345 Shanghai Hongkou Station

[0096] Table 8

[0097] In actual implementation, since ES does not support associative query, if the technical solution of the present invention is applied in ES, the intermediate query results and the data source table 1 of the virtual column "siteName" need to be loaded in advance into an embedded database that supports associative query, such as H2.

[0098] Therefore, in the embodiment of the present invention, in step 104, after querying the data table using the database query request after the first conversion to obtain the intermediate query result, the intermediate query result needs to be further stored in the embedded database supporting the associated query. In the above step 22, before querying the intermediate query result using the database query request after the second conversion, the following judgment operation needs to be further performed: if there is a virtual column whose data source is a data table in the database query request, the data table representing the data source of the virtual column is loaded into the embedded database supporting the associated query.

[0099] II) The data source of the virtual column is the conversion function:

[0100] The data sources of the virtual column include: a conversion function representing the value conversion relationship between the real column associated with the virtual column and the virtual column;

[0101] When the data source of the virtual column is a conversion function, the second conversion of the database query request includes converting the virtual column in the query target column and the query condition column, mainly replacing the virtual column in the query target column and the query condition column with its corresponding conversion function.

[0102] In the embodiment of the present invention, for each virtual column of the database query request, performing a second conversion on the database query request based on the data source of the virtual column includes:

[0103] S31. When the data source of the virtual column is a conversion function, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: the conversion function AS the virtual column name; if the virtual column is a query condition column of the database query request, the column name in the query condition column is converted into the conversion function;

[0104] S32: Delete all query conditions in the database query request whose query condition columns are not virtual columns.

[0105] The following is an example of the order table shown in Table 5:

[0106] Assume that an associated virtual column "province" has been configured for the real column "address" in Table 5, and an associated virtual column "discount_price" has been configured for the real columns "price" and "actual_price", where the data source of the virtual column "province" is the conversion function substring(address, 0, 3), and the data source of the virtual column "discount_price" is the conversion function: price-actual_price.

[0107] For the following database query request for Table 5, "select order_id, province, discount_price from table5 where create_time>'2019-04-28 00:00:00'and create_time<='2019-04-29 00:00:00'", the new database query request obtained after the first conversion of the database query request is "select order_id, address, price, actual_price from table5 where create_time>'2019-04-28 00:00:00'and create_time<='2019-04-29 00:00:00'". The intermediate query results obtained by querying Table 5 using the new database query request are specifically shown in Table 9:

[0108]

[0109] Table 9

[0110] After the database query request is subjected to the second conversion based on the data source of the virtual columns "province" and "discount_price" (i.e., the conversion function substring(address, 0, 3) and the conversion function price-actual_price), the new database query request is selected order_id, Substring(address, 0, 3) AS province, Price-actual_price AS discount_price from table9. The final query result of the database query request can be obtained by querying Table 9 using the new database query request, as shown in Table 10:

[0111]

[0112] Table 10

[0113] III) The data source of the virtual column is the regular matching rule

[0114] The data source of the virtual column includes: a regular matching rule that characterizes the value conversion relationship between the real column associated with the virtual column and the virtual column; the regular matching rule includes at least one regular matching sub-rule; each regular matching sub-rule includes a regular expression and its corresponding matching value.

[0115] When the data source of the virtual column is a regular matching rule, the second conversion of the database query request includes the conversion of the query target column and the virtual column in the query condition column, mainly replacing the query target column with a multi-condition judgment statement (case when then statement) generated based on the regular matching rule, and replacing the virtual column in the query condition column with a regular expression corresponding to the column value in the query condition column.

[0116] In the embodiment of the present invention, for each virtual column of the database query request, performing a second conversion on the database query request based on the data source of the virtual column includes:

[0117] For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column, including:

[0118] S41. When the data source of the virtual column is a regular matching rule, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: "multiple conditional judgment statements generated based on the regular matching rule AS the virtual column name"; if the virtual column is a query condition column in a query condition of the database query request, a regular matching sub-rule whose matching value is the column value in the query condition is determined, and the query condition is converted into the following format: "real column name associated with the virtual column regular expression in the regular matching sub-rule";

[0119] S42: Delete all query conditions in the database query request whose query condition columns are not virtual columns.

[0120] The following is still an example of the order table shown in Table 5:

[0121] Assume that an associated virtual column "sendpay_name" has been configured for the real column "sendpay" in Table 5, and the data source of the virtual column "sendpay_name" is the regular matching rule shown in the above Table 6.

[0122] For the following database query request "select order_id, sendpay_name fromtable5 where sendpay_name = '211'" for Table 5, the new database query request obtained after the first conversion of the database query request is "select order_id, sendpay from table5". The intermediate query result obtained by querying Table 5 using the new database query request is specifically shown in Table 11:

[0123]

[0124]

[0125] Table 11

[0126] After the second transformation of the database query request based on the data source of the virtual column "sendpay_name" (i.e., the regular matching rules shown in Table 6), the new database query request obtained is "select order_id, case when sendpay regex '1.{0}' then '211' when sendpay regex '2.{0}' then 'Beijing Standard Delivery' when sendpay regex '3.{0}' then 'Ultra Fast Delivery' else '' end AS sendpay_name from table11 where sendpay regex '1.{0}'". Querying Table 11 with this new database query request can obtain the final query result of the database query request, as shown in Table 12 specifically:

[0127] Order number (order_id) Sendpay_name 100001 211

[0128] Table 12 (table12)

[0129] The above has introduced respectively the second transformation methods of the database query request corresponding to the three data sources of the virtual column. In actual applications, there may be multiple virtual columns in the database query request, and the data sources of these virtual columns may be the same or different. When there are different data sources for multiple virtual columns in the database query request, only need to perform the second transformation of the database query request based on each virtual column respectively.

[0130] For example, the user sends a database query request for the above Table 5: "select order_id, sendpay_name, province, discount_price from table5 where create_time > '2019-04-28 00:00:00' AND create_time <= '2019-04-29 00:00:00'". After the background server receives this database query request, during the process of determining its final query result, it needs to perform the first transformation and the second transformation on it, where

[0131] The new database query request obtained by performing the first transformation on the database query request is "select order_id, sendpay, address, price, actual_price from table5 where create_time > '2019-04-28 00:00:00' AND create_time <= '2019-04-29 00:00:00'". And assume that the intermediate query result obtained by querying using the database query request after the first transformation is mid_result.

[0132] The new database query request obtained by performing the second transformation on the database query request is: "select order_id, case when sendpay regex '1.{0}' then '211' when sendpay regex '2.{0}' then 'Jingzhunda' when sendpay regex '3.{0}' then 'Express Delivery' else '' end AS sendpay_name, Substring(address, 0, 3) AS province, price - actual_price AS discount_price from mid_result".

[0133] The above has described in detail the data query method provided by the embodiments of the present invention. In practical applications, this method can be applied to Figure 2 the real-time platform shown, such as Figure 2 shown:

[0134] The real-time platform accesses the online production database data (including Mysql, oracle, sql server) in real time through kafka.

[0135] The real-time platform includes a background system, a wide table calculation system, ES, a report calculation system, and a report display platform. Among them,

[0136] The background system includes functional modules such as a management configuration center, data monitoring, and data compensation and recovery. The management configuration center in it is responsible for configuring the association relationships of wide tables (that is, the associations between all single tables used to generate wide tables) according to business requirements.

[0137] The wide table calculation system includes multiple functional modules for wide table generation such as data persistence, secondary distribution, JMQ, and a real-time wide table engine. The wide table calculation system processes data into a wide table according to the association relationships of wide tables and stores it in ES.

[0138] In addition to storing wide data tables, ES also stores various single data tables. Here, wide data tables and single data tables are collectively referred to as data tables. In order to query data that does not exist in the data table in ES in the real-time platform, you can configure an associated virtual column sendpay_name for the specified real column in the target data table in ES (such as sendpay in Table 5) according to the needs, and configure the data source of the virtual column, so that sendpay_name can be queried from Table 5.

[0139] The report display platform can generate the monitoring reports required by users by querying the target data table in ES, including querying the virtual columns configured for the specified real columns in the target data table. The report display platform is a visual custom report platform. Product business personnel can trigger database query requests for wide tables or single tables in ES by dragging the corresponding fields (Chinese names) to the query condition area and result display area. After receiving the data query request, the backend server returns the final query results to the report display platform so that it can generate visual reports.

[0140] The report calculation system can also generate the monitoring reports required by users by querying the target data table in ES, including querying the virtual columns configured for the specified real columns in the target data table. The report calculation system can query the wide data table or single data table in ES without a visual interface by configuring query SQL and some scheduling strategies, and return the final query results to the user through the JSF interface. The user can decide the display effect of the final query results.

[0141] The present invention also provides a data query device, which is combined with Figure 3 Provide detailed explanation.

[0142] See also Figure 3 , Figure 3 is a schematic diagram of the structure of a data query device according to an embodiment of the present invention. Figure 3 As shown, the device comprises:

[0143] The configuration unit 301 is used to configure the associated virtual column for the specified real column in the data table in advance, and configure the data source of the virtual column;

[0144] The receiving unit 302 is used to receive a database query request for the data table;

[0145] A conversion unit 303, configured to perform a first conversion from a virtual column to a real column on the database query request according to the association relationship between the real column and the virtual column in the data table;

[0146] A query unit 304, configured to query the data table using the database query request after the first conversion to obtain an intermediate query result;

[0147] The processing unit 305 is used to determine the final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request.

[0148] Figure 3 In the device shown,

[0149] The conversion unit 303 performs a first conversion from a virtual column to a real column on the database query request according to the association relationship between the real column and the virtual column in the data table, including:

[0150] If the query target column of the database query request includes a virtual column, the virtual column is replaced with a real column associated with the virtual column;

[0151] If the query condition column of the database query request includes a virtual column, the real column associated with the virtual column is used as the query target column of the database query request, and the query condition to which the virtual column belongs is deleted from the query condition of the database query request.

[0152] Figure 3 In the device shown,

[0153] The processing unit 305 determines the final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request, including:

[0154] For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column;

[0155] The intermediate query result is queried using the database query request after the second conversion to obtain a final query result of the database query request.

[0156] Figure 3 In the device shown,

[0157] The data source of the virtual column includes: a data table storing a correspondence between a real column associated with the virtual column and a value of the virtual column;

[0158] The processing unit 305 performs a second conversion on the database query request based on the data source of each virtual column of the database query request, including:

[0159] When the data source of the virtual column is a data table, the database query request is converted into a left join query request, and the left join query request meets the following conditions:

[0160] The query target column of the left join query request is the query target column in the database query request;

[0161] The left table and the right table of the left join query request are respectively the intermediate query result and the data table;

[0162] The connection condition of the left join query request is: the real column value in the intermediate query result is equal to the real column value in the data table;

[0163] The query conditions of the left join query request are all query conditions in the database query request whose query condition columns are not virtual columns.

[0164] Figure 3 In the device shown,

[0165] The data tables include basic data tables and custom data tables stored in the ES cluster;

[0166] After the query unit 304 uses the database query request after the first conversion to query the data table to obtain the intermediate query result, it further includes: storing the intermediate query result in an embedded database supporting associated query;

[0167] The processing unit 305 uses the database query request after the second conversion to query the intermediate query result, and before obtaining the final query result of the database query request, further includes: if there is a virtual column in the database query request whose data source is a data table, then the data table representing the data source of the virtual column is loaded into the embedded database that supports associated queries.

[0168] Figure 3 In the device shown,

[0169] The data sources of the virtual column include: a conversion function representing the value conversion relationship between the real column associated with the virtual column and the virtual column;

[0170] The processing unit 305 performs a second conversion on the database query request based on the data source of each virtual column of the database query request, including:

[0171] When the data source of the virtual column is a conversion function, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: the conversion function AS the virtual column name; if the virtual column is a query condition column of the database query request, the column name in the query condition column is converted into the conversion function;

[0172] All query conditions in the database query request whose query condition columns are not virtual columns are deleted.

[0173] Figure 3 In the device shown,

[0174] The data source of the virtual column includes: a regular matching rule that characterizes the value conversion relationship between the real column associated with the virtual column and the virtual column; the regular matching rule includes at least one regular matching sub-rule; each regular matching sub-rule includes a regular expression and its corresponding matching value;

[0175] The processing unit 305 performs a second conversion on the database query request based on the data source of each virtual column of the database query request, including:

[0176] When the data source of the virtual column is a regular matching rule, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: "multiple condition judgment statements generated based on the regular matching rule AS the virtual column name"; if the virtual column is a query condition column in a query condition of the database query request, a regular matching sub-rule whose matching value is the column value in the query condition is determined, and the query condition is converted into the following format: "real column name associated with the virtual column regular expression in the regular matching sub-rule";

[0177] All query conditions in the database query request whose query condition columns are not virtual columns are deleted.

[0178] The embodiment of the present invention further provides an electronic device, such as Figure 4 As shown, the electronic device 400 includes: at least one processor 401, and a memory 402 connected to the at least one processor 401 through a bus; the memory 402 stores one or more computer programs that can be executed by the at least one processor 401; when the at least one processor 401 executes the one or more computer programs, the following is implemented Figure 1 Steps in the data query method shown.

[0179] The embodiment of the present invention further provides a computer-readable storage medium, wherein the computer-readable storage medium stores one or more computer programs, and when the one or more computer programs are executed by a processor, the following is implemented: Figure 1 The data query method shown.

[0180] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A data query method, characterized in that: Pre-configuring an associated virtual column for a specified real column in a data table and configuring a data source for the virtual column, the method includes: receiving a database query request for the data table; According to the association relationship between the real column and the virtual column in the data table, performing a first conversion from the virtual column to the real column on the database query request; Using the database query request after the first conversion, query the data table to obtain an intermediate query result; According to the intermediate query result and the data sources of each virtual column in the database query request, a final query result corresponding to the database query request is determined; the data source of the virtual column is used to represent the value correspondence or value conversion relationship between the real column associated with the virtual column and the virtual column; the data sources of the virtual column include the following: data table, conversion function, regular matching rule; Determining a final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request includes: For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column; The intermediate query result is queried using the database query request after the second conversion to obtain a final query result of the database query request.

2. The method according to claim 1, characterized in that According to the association relationship between the real column and the virtual column in the data table, performing a first conversion from the virtual column to the real column on the database query request includes: If the query target column of the database query request includes a virtual column, the virtual column is replaced with a real column associated with the virtual column; If the query condition column of the database query request includes a virtual column, the real column associated with the virtual column is used as the query target column of the database query request, and the query condition to which the virtual column belongs is deleted from the query condition of the database query request.

3. The method according to claim 1, characterized in that: The data source of the virtual column includes: a data table storing the correspondence between the real column associated with the virtual column and the value of the virtual column; the data table includes a basic data table and a custom data table stored in the ES cluster; For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column, including: When the data source of the virtual column is a data table, the database query request is converted into a left join query request, and the left join query request meets the following conditions: The query target column of the left join query request is the query target column in the database query request; The left table and the right table of the left join query request are respectively the intermediate query result and the data table; The connection condition of the left join query request is: the real column value in the intermediate query result is equal to the real column value in the data table; The query conditions of the left join query request are all query conditions in the database query request whose query condition columns are not virtual columns.

4. The method according to claim 3, characterized in that: After querying the data table to obtain an intermediate query result using the database query request after the first conversion, the method further includes: storing the intermediate query result in an embedded database supporting associated query; Before querying the intermediate query result using the database query request after the second conversion, the method further includes: if there is a virtual column whose data source is a data table in the database query request, loading the data table representing the data source of the virtual column into an embedded database supporting associated query.

5. The method according to claim 1, characterized in that The data sources of the virtual column include: a conversion function representing the value conversion relationship between the real column associated with the virtual column and the virtual column; For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column, including: When the data source of the virtual column is a conversion function, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: the conversion function AS the virtual column name; if the virtual column is a query condition column of the database query request, the column name in the query condition column is converted into the conversion function; All query conditions in the database query request whose query condition columns are not virtual columns are deleted.

6. The method according to claim 1, characterized in that The data source of the virtual column includes: a regular matching rule that characterizes the value conversion relationship between the real column associated with the virtual column and the virtual column; the regular matching rule includes at least one regular matching sub-rule; each regular matching sub-rule includes a regular expression and its corresponding matching value; For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column, including: When the data source of the virtual column is a regular matching rule, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: "multiple condition judgment statements generated based on the regular matching rule AS the virtual column name"; if the virtual column is a query condition column in a query condition of the database query request, a regular matching sub-rule whose matching value is the column value in the query condition is determined, and the query condition is converted into the following format: "real column name associated with the virtual column regular expression in the regular matching sub-rule"; All query conditions in the database query request whose query condition columns are not virtual columns are deleted.

7. A data query device, characterized in that: The device includes: A configuration unit, used to configure an associated virtual column for a specified real column in a data table in advance, and configure a data source for the virtual column; A receiving unit, configured to receive a database query request for the data table; A conversion unit, configured to perform a first conversion from a virtual column to a real column on the database query request according to an association relationship between a real column and a virtual column in the data table; A query unit, configured to query the data table using the database query request after the first conversion to obtain an intermediate query result; A processing unit, configured to determine a final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request; the data source of the virtual column is used to represent the value correspondence or value conversion relationship between the real column associated with the virtual column and the virtual column; the data sources of the virtual column include the following: data table, conversion function, regular matching rule; The processing unit determines the final query result corresponding to the database query request according to the intermediate query result and the data source of each virtual column in the database query request, including: For each virtual column of the database query request, performing a second conversion on the database query request based on a data source of the virtual column; The intermediate query result is queried using the database query request after the second conversion to obtain a final query result of the database query request.

8. The device according to claim 7, characterized in that The conversion unit performs a first conversion from a virtual column to a real column on the database query request according to the association relationship between the real column and the virtual column in the data table, including: If the query target column of the database query request includes a virtual column, the virtual column is replaced with a real column associated with the virtual column; If the query condition column of the database query request includes a virtual column, the real column associated with the virtual column is used as the query target column of the database query request, and the query condition to which the virtual column belongs is deleted from the query condition of the database query request.

9. The device according to claim 7, characterized in that The data source of the virtual column includes: a data table storing a correspondence between a real column associated with the virtual column and a value of the virtual column; The processing unit, for each virtual column of the database query request, performs a second conversion on the database query request based on a data source of the virtual column, including: When the data source of the virtual column is a data table, the database query request is converted into a left join query request, and the left join query request meets the following conditions: The query target column of the left join query request is the query target column in the database query request; The left table and the right table of the left join query request are respectively the intermediate query result and the data table; The connection condition of the left join query request is: the real column value in the intermediate query result is equal to the real column value in the data table; The query conditions of the left join query request are all query conditions in the database query request whose query condition columns are not virtual columns.

10. The device according to claim 9, characterized in that The data tables include basic data tables and custom data tables stored in the ES cluster; After the query unit queries the data table using the database query request after the first conversion to obtain the intermediate query result, the method further comprises: storing the intermediate query result in an embedded database supporting associated query; The processing unit uses the database query request after the second conversion to query the intermediate query result, and before obtaining the final query result of the database query request, further includes: if there is a virtual column in the database query request whose data source is a data table, then the data table representing the data source of the virtual column is loaded into an embedded database that supports associated queries.

11. The device according to claim 7, characterized in that The data sources of the virtual column include: a conversion function representing the value conversion relationship between the real column associated with the virtual column and the virtual column; The processing unit, for each virtual column of the database query request, performs a second conversion on the database query request based on a data source of the virtual column, including: When the data source of the virtual column is a conversion function, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: the conversion function AS the virtual column name; if the virtual column is a query condition column of the database query request, the column name in the query condition column is converted into the conversion function; All query conditions in the database query request whose query condition columns are not virtual columns are deleted.

12. The device according to claim 7, characterized in that The data source of the virtual column includes: a regular matching rule that characterizes the value conversion relationship between the real column associated with the virtual column and the virtual column; the regular matching rule includes at least one regular matching sub-rule; each regular matching sub-rule includes a regular expression and its corresponding matching value; The processing unit, for each virtual column of the database query request, performs a second conversion on the database query request based on a data source of the virtual column, including: When the data source of the virtual column is a regular matching rule, if the virtual column is a query target column of the database query request, the query target column is converted into the following format: "multiple condition judgment statements generated based on the regular matching rule AS the virtual column name"; if the virtual column is a query condition column in a query condition of the database query request, a regular matching sub-rule whose matching value is the column value in the query condition is determined, and the query condition is converted into the following format: "real column name associated with the virtual column regular expression in the regular matching sub-rule"; All query conditions in the database query request whose query condition columns are not virtual columns are deleted.

13. An electronic device, comprising: at least one processor, and a memory connected to the at least one processor via a bus; The memory stores one or more computer programs that can be executed by the at least one processor; it is characterized in that when the at least one processor executes the one or more computer programs, the method steps described in any one of claims 1-6 are implemented.

14. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores one or more computer programs, and when the one or more computer programs are executed by a processor, the method of any one of claims 1 to 6 is implemented.

Images