CN115114319A - Method, device and device for data query based on data wide table - Google Patents

Abstract

The disclosure relates to a method, a device, equipment and a storage medium for querying data based on a data wide table, which can be applied to the technical field of data processing. The method for querying data based on the data wide table comprises the following steps: receiving a query instruction of a user; and inquiring in the data wide table according to the field in the inquiry instruction to obtain inquiry data and output the inquiry data. The data wide table is obtained by summarizing data in a plurality of business data single tables, so that the required business data information can be inquired in real time by inquiring based on the data wide table, and the inquiry efficiency of the data information is improved.

Description

Method, device and device for data query based on data wide table

technical field

The present disclosure relates to the technical field of data processing, and in particular, to a method, apparatus, device and storage medium for performing data query based on a data wide table.

Background technique

With the rapid development of the Internet, e-commerce supported by Internet technology has also entered a period of rapid business development. When these services are implemented, multiple tables that record the business data information will be generated, so as to record and store the business information.

At the same time, with the increasing business volume, more and more business tables need to be stored, resulting in more and more complicated storage design for business tables. However, the complex data storage method will cause users to search for multiple databases and multiple data tables one by one when querying data in the background, resulting in a long response time for the entire process of data query.

Therefore, there is no suitable data query method in the prior art.

SUMMARY OF THE INVENTION

The present disclosure provides a method, device, device and storage medium for data query based on a data wide table, so as to realize real-time query of required business data information and improve the query efficiency of data information.

In a first aspect, the present disclosure provides a method for querying data based on a data wide table, including: receiving a query instruction from a user; querying the data wide table according to fields in the query instruction to obtain query data, and the data wide table is obtained by summarizing Obtained from data in multiple business data sheet tables; output query data.

In some possible implementations, querying the data wide table according to the fields in the query instruction to obtain the query data includes: according to the fields in the query instruction, querying the indexes corresponding to the fields in the data wide table; linking to the query according to the indexes data.

In some possible implementations, before receiving the query instruction from the user, the method further includes: acquiring multiple service data sheet tables; and obtaining a data wide table by aggregating data in the multiple service data sheet tables.

In some possible implementations, the data wide table is obtained by aggregating data in multiple business data sheet tables, including: determining a first field according to business information in the multiple business data sheet tables, where the first field is a data wide table The field used for display in the field; according to the first field, summarize the data in multiple business data sheet tables to obtain a data wide table.

In some possible implementations, according to the first field, aggregating data in multiple business data sheet tables to obtain a data wide table includes: parsing the second field in each business data sheet in the multiple business data sheet tables, The second field is used to indicate the association relationship between the data in the multiple business data sheet tables; according to the data range corresponding to the first field, the second field is used to summarize the associated data in the multiple business data sheet tables to Get the data wide table.

In some possible implementations, according to the data range corresponding to the first field, the second field is used to summarize the associated data in multiple business data sheet tables to obtain a data wide table, including: according to the data range of the first field For the corresponding data range, the data in multiple business data sheets is processed through multi-threading technology to obtain intermediate data from multiple business data sheets; the second field is used to obtain the associated data in the intermediate data; the associated data is aggregated , to get the data wide table.

In some possible implementations, the data change information in the multiple business data sheets is detected; when it is detected that the data in any single sheet changes, the data associated with any single sheet in the data wide table is updated.

In a second aspect, the present disclosure provides an apparatus for querying data based on a data wide table. The apparatus may be a chip or a system-on-chip in a terminal device, and may also be used in a terminal device to implement the first aspect and any possibility thereof. The functional modules of the implementation. The data query apparatus can implement the functions performed by the terminal in the first aspect and any possible implementation manners thereof, and the functions can be implemented by executing corresponding software through hardware. The hardware or software includes one or more modules corresponding to the above functions. The data query device includes: an acquisition module for receiving a user's query instruction; a processing module for performing a query in a data wide table according to fields in the query instruction to obtain query data, and the data wide table collects multiple business data by summarizing multiple business data. It is obtained from the data in a single table; the output module is used to output query data.

In some possible implementations, the processing module is further configured to: query the corresponding index of the field in the data wide table according to the field in the query instruction; and link to the query data according to the index.

In some possible implementations, the obtaining module is further configured to: before receiving a query instruction from a user, obtain multiple service data sheets; and summarize data in the multiple service data sheets to obtain a data wide sheet.

In some possible implementations, the acquiring module is further configured to: determine a first field according to the business information in the multiple business data sheet tables, where the first field is a field used for query in the data wide table; according to the first field, Summarize data from multiple business data sheet tables to obtain data wide tables.

In some possible implementations, the acquiring module is further configured to: parse the second field in each business data form in the multiple business data sheet tables, where the second field is used to indicate the difference between the data in the multiple business data sheet tables Association relationship; according to the data range corresponding to the first field, use the second field to summarize the associated data in multiple business data single tables to obtain a data wide table.

In some possible implementations, the acquiring module is further configured to: process the data in the multiple business data sheet tables through a multi-threading technology according to the data range corresponding to the first field, to obtain data from multiple business data sheet tables The intermediate data is the data corresponding to the fields of the data wide table; the second field is used to obtain the associated data in the intermediate data; the associated data is aggregated to obtain the data wide table.

In some possible implementations, the obtaining module is further configured to: after using the second field to summarize the associated data in the multiple business data sheet tables to obtain the data wide table, according to the data volume of the associated data, Determines the index of the associated data in the data-wide table.

In some possible implementations, the acquiring module is further configured to: detect data change information in multiple business data single tables; when detecting that the data in any single table changes, update the data in the wide data table with any one Data information associated with a single table.

In a third aspect, the present disclosure provides a terminal, comprising: a memory for storing processor-executable instructions; a processor; wherein the processor is configured to: when executing the executable instructions, to achieve the first aspect and the The method described in any possible embodiment thereof.

In a fourth aspect, the present disclosure provides a computer-readable storage medium, where computer-executable instructions are stored in the computer-readable storage medium, and after the computer-executable instructions are executed by a processor, the first aspect and any of its possible implementations can be implemented. The method described in the embodiment.

Compared with the prior art, the technical solution provided by the present disclosure has the following beneficial effects:

In the present disclosure, the query data is obtained and output by receiving the query instruction from the user, and performing a query in the data wide table according to the fields in the query instruction. The data wide table of the present disclosure is obtained by aggregating data in multiple business data single tables. Therefore, based on the data wide table of the present disclosure, the required business data information can be queried in real time, and the query of data information can be improved. efficiency.

It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not intended to limit the scope of protection of the present disclosure.

Description of drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the disclosure and together with the description serve to explain the principles of the disclosure.

1 is a schematic diagram of an implementation flow of a method for data query in an embodiment of the present disclosure;

2 is a schematic flowchart of another implementation of the method for data query in an embodiment of the present disclosure;

3 is a schematic structural diagram of a data query apparatus in an embodiment of the present disclosure;

FIG. 4 is a schematic structural diagram of an electronic device in an embodiment of the present disclosure.

Detailed ways

Exemplary embodiments will be described in detail herein, examples of which are illustrated in the accompanying drawings. Where the following description refers to the drawings, the same numerals in different drawings refer to the same or similar elements unless otherwise indicated. The implementations described in the illustrative examples below are not intended to represent all implementations consistent with this disclosure. Rather, they are merely examples of means consistent with some aspects of the present disclosure, as recited in the appended claims.

In order to illustrate the technical solutions described in the present disclosure, the following specific embodiments are used for description.

With the rapid development of the Internet, e-commerce supported by Internet technology has also entered a period of rapid business development. Common e-commerce includes online shopping of consumers, online transactions between merchants and online electronic payment. When these services are implemented, multiple tables that record the business data information will be generated, so as to record and store the business information. Taking the online shopping business of consumers as an example, when the business is implemented, a transaction table representing transaction information, a payment table representing payment information, and a channel table representing business channels are generated. Collecting the data recorded in the above-mentioned multiple tables recording business data information (which may also be recorded as an order table) can reflect all the information of the online shopping business. When the amount of data in the order table is small, all the order tables are usually stored in a relational database, and a structured query language (SQL) is directly used to query the database for associated queries to obtain all business information.

With the increasing business volume, more and more business tables need to be stored, resulting in more and more complicated storage design for business tables. For example, in order to avoid long query time caused by storing massive data in a single database, the business table is often stored in the way of sub-database and sub-table. However, complex storage design (such as sub-database and sub-table) will lead to the problem that it is impossible to directly use SQL to perform related queries. At the same time, the existing business query has a high degree of concurrency, and the relational database management system MySQL cannot withstand query requests with a high degree of concurrency, resulting in a long response time for query results and request timeouts.

Therefore, there is no suitable data query method.

In order to solve the above problems, the embodiments of the present disclosure provide a method for data query based on a data wide table, so as to realize real-time query of required business data information through the data wide table, and improve the query efficiency of data information.

FIG. 1 is a schematic flowchart of an implementation of a data query method in an embodiment of the present disclosure. Referring to FIG. 1 , the data query method may include S101 to S103 .

S101, receiving a query instruction from a user.

It should be understood that the terminal device executes S101, and may receive an instruction from the user for instructing to query the real-time stored information in the data width table.

The query instruction may be an instruction obtained by the terminal device when the user operates the terminal device. The query instruction usually includes various fields such as text and symbols, which are used to indicate the query information and can be recognized by the terminal device. Exemplarily, the query instruction may include a field "2022.05", and the terminal device can identify this field to instruct to query order information related to 2022.05.

S102, query the data wide table according to the fields in the query instruction to obtain query data.

It should be understood that, after executing S101, the terminal device can obtain the query instruction of the user. Then, the terminal device executes S102, and can identify the field in the query instruction, and perform data query in the data wide table according to the indication of the field to obtain query data.

The data wide table is obtained by aggregating data in multiple business data sheet tables. The data wide table may be a database table including a plurality of fields, each field corresponding to at least one data value. Data values can come from multiple business data sheet tables, and multiple business data sheet tables can be stored in the same database or stored in different databases. Data wide tables can associate indicators, dimensions, and attributes related to business entities by summarizing data values.

In some possible implementations, S102 may include: querying an index corresponding to the field in the data wide table according to the field in the query instruction; and linking to the query data according to the index.

It should be understood that when the terminal device executes S102, it can identify the field in the query instruction, search for the corresponding index according to the field's indication, and then search for the data value stored in the specified position in the data width table according to the index, and finally obtain the query data.

Among other things, an index can provide a pointer to a data value stored at a specified location in the data-wide table. Use the index to find the specific value that corresponds to a field in the instruction, then clockwise to find the storage location that contains the value, providing quick access to the data value in the data-wide table.

That is to say, by first querying the corresponding index in the data wide table, and then linking to the query data according to the index, the terminal device can change the original default full table scan query method to locate a specific value in the de-index list at one time. The storage location greatly reduces the workload of scanning data, so it can significantly increase the query speed.

S103, output query data.

It should be understood that the terminal device can obtain query data after executing S102. Then, the terminal device executes S103, and can output the query data obtained in S102.

The manner of outputting the query data may be set according to actual requirements, which is not specifically limited in the present disclosure. For example, when the user needs to see the query data on the page where the query instruction is input, the format of the output query data can be set to display the query data on the page; or, when the user needs to store and save the query data, the output query data can be set The form stores query data for the output document.

In the above embodiment, the terminal device executes S101 to S103, and can obtain and output the query data by receiving the query instruction from the user, and querying the data wide table according to the fields in the query instruction. The data wide table of the present disclosure is obtained by aggregating data in multiple business data single tables. Therefore, based on the data wide table of the present disclosure, the required business data information can be queried in real time, and the query of data information can be improved. efficiency.

In some possible implementations, FIG. 2 is a schematic flowchart of another implementation of the data query method in the embodiment of the disclosure. Referring to FIG. 2 , S201 may also be included before S101 .

S201: Acquire multiple business data sheet tables, and obtain a data wide table by aggregating data in the multiple business data sheet tables.

It should be understood that the terminal device may execute S201 before executing S101. The terminal device may acquire data sheet tables generated by multiple services, then aggregate data in the multiple service data tables, and obtain a data width table according to the aggregated data.

The manner of acquiring the data sheets generated by multiple services may be set according to actual requirements, which is not specifically limited in the present disclosure. For example, the terminal device obtains the data sheet table by accessing a corresponding database; or, the terminal device obtains the data sheet table by monitoring the flow process of the service department. At the same time, when aggregating data in multiple business data sheets, it is necessary to fully consider the situation that multiple business data sheets may have sub-databases and sub-tables. In a way that can cross databases, data information can be obtained.

In some possible implementations, S201 may further include: determining a first field according to business information in multiple business data sheet tables, where the first field is a field used for query in the data wide table; data in a single business data sheet table to obtain a data wide table.

It should be understood that, after acquiring the data sheet tables generated by the multiple services, the terminal device can acquire the service information in the multiple service data sheet tables. The terminal device selects the content of the service information to determine the first field. According to the first field, the data in the multiple business data sheet tables are aggregated to obtain a data wide table.

The first field is a field used for display in the data wide table. Specifically, after the terminal device acquires the data information stored in the data width table according to the query instruction, the terminal device outputs the data information. In order to keep the output data neat, the data information can be classified and displayed according to the first field. Therefore, when the terminal device aggregates the data sheet tables generated by multiple services, the first field can be determined in advance, and the data information related to the first field can be aggregated to obtain the data wide table. That is to say, the terminal device only summarizes the data information related to the first field, which avoids the aggregation of useless information and greatly saves storage space.

Exemplarily, company A's core business is payment business, and each business domain in the payment link includes multiple order tables. For example, transaction table, payment table, channel table, etc. Due to the large amount of data in the order table, when company A stores these tables, it designs and stores the order tables of each business domain in four databases, and the database instances of each database are different. Further, in order to reduce the number of records in a single table, in order to reduce the time required for querying a single table and improve the throughput of the database, the number of sub-tables in each database is designed to be 64, so that the data is evenly distributed among multiple tables, and Does not affect queries. Company A can provide a page for the merchant's background order query, where the merchant queries the data information in the data wide table, and the page displays the data information according to the classification corresponding to the first field. Among them, the first field (transaction amount, payment status, third-party channel order number, etc.) displayed to the merchant comes from multiple order tables. That is, the first field includes the transaction amount from the order table, the payment status from the payment table, and the third-party channel order number from the channel table.

Referring to Table 1, it is the data information of the multiple service data sheet tables in the embodiment of the present disclosure.

Table 1

As can be seen from the above table, the first field (display field) of the data wide table can be summarized according to the information in the three data single tables in Table 1. It should be understood that, in practical applications, the data wide table can be designed according to business requirements. For example, there are three tables A, B, and C, and each table contains 10 fields. Well, the data wide table can be designed to have 30 first fields. However, when the terminal device needs to use the data wide table to solve a specific business requirement, the business requirement needs to use 3 first fields of A table, 4 first fields of B table, and 3 first fields of C table, then , in order to save space, the data wide table only needs to be designed to have 10 first fields.

Further, other data information of transaction table, payment table, and channel table can also be obtained from Table 1, such as the database name where each table is located, the table name, the database instance of the database where the table is located, the unique index of the table and the serial order number. . The transaction table, payment table, and channel table come from different databases and have different database instances (instance A, instance B, and instance C). Among them, the database instance is the program, which is the channel for accessing the database. The terminal device performs any operations on the data in the database, including data definition, data query, data maintenance, database operation control, etc., all performed under the database instance. Therefore, when aggregating data in multiple business data sheet tables in the embodiments of the present disclosure, data of different database instances will be involved.

In some possible implementations, S201 may further include: parsing the second field in each business data form in the multiple business data sheet tables; according to the data range corresponding to the first field, using the second field to The associated data in the data sheet table is aggregated to obtain a data wide table. The terminal device can summarize the associated data through the second field, and then store the associated data together to obtain a data wide table. Further, the terminal device only summarizes the data related to the first field, which saves storage space.

It should be understood that the terminal device executes S201, and can parse out the second fields in the multiple service order tables. Then, using the second field, look up the business data sheet table related to the second field. Finally, the terminal device obtains the data information corresponding to the first field in all the found service data sheets, and summarizes all the data information.

Wherein, the second field is used to indicate the association relationship between the data in the multiple business data sheet tables. Specifically, the second field may be a field common to multiple business data sheets. By analyzing the second field, the correlation between multiple services can be reflected, thereby reflecting the data correlation in each business data sheet.

Exemplarily, as shown in Table 1, it is assumed that according to the business logic, three business data sheet tables are generated in an existing business process. The order order of the three business data sheets is transaction table, payment table, and channel table, and the relationship between the three business data sheets is transaction table: payment table: channel table = 1:1:1, that is, one sheet A transaction table corresponds to a payment table and a channel table. And because the business data sheet needs to be designed to pass the unique order number generated by the source table (that is, the transaction table) to the downstream business and store it in the downstream business table, it can be known that the three business data sheets all contain [ Serial number] (equivalent to the second field). Then, when the terminal device uses the data wide table, it can obtain all business data sheets and all data information of the same business by obtaining the [serial order number] in each data sheet. Inevitably, all data information of the same business is associated data. In addition, if the relationship between the business data sheet table and the business data sheet table is 1:n, the business data sheet table with a ratio of n can be designed as the Nested type in Elasticsearch, and the Nested field can be processed by writing a Painless Script script. The bulk upsert operation can finally enable all the data in the single table of the business data to be queried by association.

In some possible implementations, S201 may further include: according to the data range corresponding to the first field, processing the data in the multiple business data sheet tables through a multi-threading technology to obtain the intermediate data from the multiple business data sheet tables. data; use the second field to obtain the associated data in the intermediate data; aggregate the associated data to obtain a data wide table.

It should be understood that, in order to save storage space, the terminal device may process the data in the multiple service data sheet tables according to the data range corresponding to the first field. The terminal device can use the multi-threading technology to obtain the intermediate data from multiple business data sheets (that is, the data directly obtained from the business data sheet), and then use the second field to obtain the associated data in the intermediate data (that is, from the same data sheet). business data), and finally summarize the associated data to obtain a wide data table. Because the first field is the display field of the data wide table, summarizing the associated data in this range can avoid the storage of redundant data. At the same time, the use of multi-threading technology in the terminal device can greatly improve the query speed.

The multi-threading technology refers to a central processing unit (central processing unit, CPU) executing multiple programs at the same time. Specifically, the terminal device needs to process hundreds of billions or even trillions of data every day, and simultaneously execute multiple programs to summarize the data into a data wide table. In addition, considering that the database instances of different business domains are different and the design of sub-database and sub-table, it is necessary to select middleware adapted to different databases to complete the entire processing process. For example, first use parsing middleware to complete data parsing, then use message middleware to complete data transmission, and finally perform data synchronous storage.

Exemplarily, in order to ensure low latency of data synchronization, the terminal device can use the middleware canal to monitor binlog. By configuring the target data list and data routing rules monitored by canal instance, the binlog is parsed into json format data and written to the configured kafka. in the topic partition. The data routing rule may be that when trade_order_no is specified in the canal.mq.partitionHash parameter configured for multiple data sheet tables, the data of the multiple data sheet tables will be hashed according to trade_order_no when routing. That is to say, the same data as trade_order_no will be routed to the same partition of the topic and processed by the same consumer thread.

Further, Canal is a binlog parsing middleware, which can parse binlog binary files to generate data in json format. This component can configure the database and data table that the business cares about by writing regular expressions. The component also supports sending the parsed binlog data to the corresponding message queue according to the routing rules set in the configuration file. It can be seen that the data in the sub-database and sub-table can be processed through the Canal parsing middleware. In an example, Maxwell and FlinkCDC can also be selected as parsing middleware according to business requirements.

Further, as a message middleware, Kafka has the characteristics of high availability, high throughput, low latency, and high concurrency. The topic partition of Kafka can be set reasonably according to the size of the data volume in the current business unit time and the business tolerance for data loss. The number of consumers in Kafka is generally consistent with the number of topic partitions in Kafka to maintain high concurrency and high performance consumption. In an instance, rocketmq and rabbitmq can also be selected as message middleware according to business requirements.

In some possible implementations, the terminal device executes S201, and the process of aggregating the associated data to obtain the data wide table may be a process of uniformly processing the data and storing the data in Elasticsearch. In this process, the data needs to be mapped to Elasticsearch in different categories, so it is necessary to design a unified data processing framework.

Exemplarily, the terminal device may perform S201 by adopting logical processing unified by the framework.

1. Filter the data whose isDdl=true and dmlType=DELETE logical deletion. Among them, the data of isDdl=true is some DDL operations on the business table, such as modifying the field name, type, etc. These data do not belong to the business data class and are not suitable for building data wide tables; dmlType=DELETE indicates that the database delete operation, these data Storage can also be waived.

2. The data obtained in kafka comes from different databases and different tables. The table name design specification for this sub-library and sub-table is composed of library/table name + underscore + number, such as tb_trade_order_0. In theory, the data structures of the tables in the same library are the same, so when performing data mapping, you do not need to care about the numerical suffixes of these tables, but treat them as the data of the same table. That is, through regular expression matching and erasing the suffix (_0), it ensures that table data with the same name can be processed by the same policy logic, and solves the problem of data dispersion caused by sub-database and sub-table.

3. Field filtering operation. For example, the business data sheet table often contains some fields that do not have business meaning, or fields that do not need to be displayed externally. Then, such fields can be directly filtered out in S201 to save storage space.

4. Analyze the year and month (yyyyMM) part of the transaction order number to determine the index position written by Elasticsearch.

5. Convert the underscore of the JSON field to camel case, and add a prefix to the field.

6. Write the result set returned by the unified data processing interface to Elasticsearch in batches. Batch writing reduces the number of IO interactions with the storage side and can improve write performance.

The foregoing is a possible framework in the embodiments of the present disclosure. During specific implementation, it may be designed according to actual needs of services, which is not specifically limited in the embodiments of the present application. In addition, when designing a framework, you can sort out the common processing logic and abstract the special business logic processing flow into an interface. Then, for different services, the interface can be switched according to the actual business scenario.

In some possible implementations, S201 may further include: after using the second field to summarize the associated data in the multiple business data sheet tables to obtain a data wide table, determine the association according to the data volume of the associated data The index of the data in the data wide table.

It should be understood that the terminal device uses the second field to summarize the associated data in the multiple business data sheet tables, and stores the aggregated data in Elasticsearch to obtain a data wide table. In addition, the terminal device can also plan the Elasticsearch index according to the size of the stored data. By building an index, the terminal device can first query the corresponding index when using the data wide table, and then link to the query data according to the index, so that the original default full table scan query method can be changed to go to the index list once to locate a specific query. The storage location of the value greatly reduces the workload of the query, so it can significantly increase the speed of the query.

Among them, an index is a collection of interrelated documents, and Elasticsearch stores data in the form of JSON documents. Each document creates a relationship between a set of keys (names of fields or properties) and their corresponding values (strings, numbers, booleans, dates, groups of values, geographic locations, or other types of data). Elasticsearch uses an inverted index data structure, which is designed to allow very fast full-text searches. An inverted index lists every unique word that occurs in all documents, and can find all documents that contain every word. During the indexing process, Elasticsearch stores documents and builds an inverted index so that document data can be searched in near real-time.

Exemplarily, the terminal device may plan the split granularity and the number of shards of the data wide table index according to the size of the data volume of the traffic. Correspondingly, the terminal device can also divide the index into three areas: hot, warm, and cold according to the user's access characteristics. The hot area stores index documents that are frequently accessed and need to be modified after creation; the warm area stores read-only index documents that are accessed less frequently; the cold area stores read-only index documents that occasionally need to be accessed.

Further, the terminal device can also manage operations such as index creation, index alias setting, and index migration on hot/warm/cold nodes through the application programming interface (API) of timed task scheduling, so as to save machine costs and improve query performance. performance.

Exemplarily, an existing design rule for a transaction ticket number includes a year, month, and day (yyyyMMdd) part, for example, the transaction ticket number is 20220505Txxxx482953. Then, when building a split index by time granularity, it is possible to divide the index by month. When the terminal device parses the transaction order number as 20220505Txxxx482953, the data can be written into the index wt_trade_order-202205 (equivalent to one index per month). In addition, assuming that it is 2022.05.01, through scheduled task scheduling, wt_trade_order-202206 will be generated on the hot node, and wt_trade_order-202105 will be transferred from the hot node to the warm node.

In some possible implementations, the above method further includes S202. S202 can be executed before the above S101, S102, S103 and S201, can also be executed simultaneously with any of the above steps S101, S102, S103 and S201, or can be executed after the above S101, S102, S103 and S201.

S202: Detect data change information in a plurality of business data single tables; when it is detected that data in any single table changes, update the data associated with any single table in the data wide table.

It should be understood that the terminal device can obtain the data change information in multiple service data sheets in real time, and re-store the changed data in the data wide table through the component, that is, when the data in any single sheet is detected. When changes occur, the terminal device can update the data associated with any single table in the data wide table.

Exemplarily, as mentioned in the above description, the terminal device can use the middleware canal to monitor the binlog. By configuring the target data list and data routing rules monitored by the canal instance, the binlog is parsed into json format data and written to the configured kafka topic partition. middle. Among them, binlog refers to the binary log, which records all the changes on the database and saves it on the disk in binary form. Then, the middleware canal can obtain the data change information of the data single table in the database by monitoring the binlog, and then write the changed data information into the kafka topic partition, and finally update the data in the data wide table through the data synchronization processing framework.

In the above embodiment, the terminal device executes 201 to S202, and can generate a data wide table through multiple data single tables, and update the data information in the data wide table in real time, so that the required data information can be queried in real time through the data wide table. , to improve the query efficiency of data information.

Based on the same inventive concept, an embodiment of the present disclosure further provides an apparatus for data query based on a data wide table. The data query apparatus may be a chip or a system-on-chip in a terminal device, and may also be a device used in the terminal device to implement the above Functional modules of the methods described in various embodiments. The data query apparatus can implement the functions performed by the terminal device in the above embodiments, and these functions can be implemented by executing corresponding software through hardware. These hardware or software include one or more modules corresponding to the above-mentioned functions.

FIG. 3 is a schematic structural diagram of a data query device according to an embodiment of the disclosure. Referring to FIG. 3 , the data query device 300 includes: an acquisition module 301 for receiving a query instruction from a user; a processing module 302 for using The data wide table is obtained by querying the data wide table according to the fields in the query instruction, and the data wide table is obtained by summarizing the data in multiple business data single tables; the output module 303 is used for outputting the query data.

In some possible implementations, the processing module 302 is further configured to: query the corresponding index of the field in the data wide table according to the field in the query instruction; and link to the query data according to the index.

In some possible implementations, the obtaining module 301 is further configured to: before receiving a query instruction from a user, obtain multiple service data sheets; and summarize data in the multiple service data sheets to obtain a data wide sheet.

In some possible implementations, the obtaining module 301 is further configured to: determine the first field according to the business information in the multiple business data sheet tables, where the first field is a field used for query in the data wide table; according to the first field , summarize the data in multiple business data sheet tables to obtain a data wide table.

In some possible implementations, the obtaining module 301 is further configured to: parse the second field in each business data form in the multiple business data sheet tables, where the second field is used to indicate the difference between data in the multiple business data sheet tables According to the data range corresponding to the first field, the second field is used to summarize the associated data in the multiple business data single tables to obtain the data wide table.

In some possible implementations, the obtaining module 301 is further configured to: process the data in the multiple business data sheet tables through a multi-threading technology according to the data range corresponding to the first field, and obtain data from multiple business data sheets The intermediate data of the table, the intermediate data is the data corresponding to the fields of the data wide table; the second field is used to obtain the associated data in the intermediate data; the associated data is aggregated to obtain the data wide table.

In some possible implementation manners, the obtaining module 301 is further configured to: after using the second field to summarize the associated data in the multiple business data sheet tables to obtain the data wide table, according to the data volume of the associated data , which determines the index of the associated data in the data-wide table.

In some possible implementations, the obtaining module 301 is further configured to: detect data change information in multiple business data single tables; when detecting that the data in any single table changes, update the data in the data wide table with any one Data information associated with a single table.

It should be noted that the specific implementation process of the acquiring module 301 , the processing module 302 and the outputting module 303 may refer to the detailed description of the embodiments in FIG. 1 and FIG. 2 , which are not repeated here for brevity of the description.

Based on the same inventive concept, an embodiment of the present disclosure provides a terminal, and the terminal may be the terminal described in one or more of the foregoing embodiments. FIG. 4 is a schematic structural diagram of a terminal in an embodiment of the disclosure. Referring to FIG. 4 , the terminal 400 may adopt general computer hardware, including a processor 401 and a memory 402 .

In some possible implementations, at least one processor may constitute any physical device having circuitry that performs logical operations on one or more inputs. For example, at least one processor may include one or more integrated circuits (ICs), including application specific integrated circuits (ASICs), microchips, microcontrollers, microprocessors, all or part of a central processing unit (CPU), graphics processing Unit (GPU), Digital Signal Processor (DSP), Field Programmable Gate Array (FPGA), or other circuit suitable for executing instructions or performing logical operations. Instructions executed by the at least one processor may be preloaded, for example, into memory integrated with or embedded in the controller, or may be stored in a separate memory. Memory may include random access memory (RAM), read only memory (ROM), hard disks, optical disks, magnetic media, flash memory, other permanent, fixed or volatile memory, or any other mechanism capable of storing instructions. In some embodiments, at least one processor may include more than one processor. Each processor may have a similar structure, or the processors may have different configurations that are electrically connected or disconnected from each other. For example, the processor may be a separate circuit or integrated in a single circuit. When more than one processor is used, the processors may be configured to operate independently or cooperatively. The processors may be coupled electrically, magnetically, optically, acoustically, mechanically, or by other means that allow them to interact.

According to an embodiment of the present invention, the present invention further provides a computer-readable storage medium, on which computer instructions are stored, and the instructions are executed by a processor to perform the steps of the above-mentioned data query method based on a data wide table. Memory 702 may include computer storage media in the form of volatile and/or nonvolatile memory, such as read-only memory and/or random access memory. Memory 402 may store operating systems, application programs, other program modules, executable code, program data, user data, and the like.

In addition, the above-mentioned memory 402 stores computer-executed instructions for realizing the functions of each module in FIG. 3 . The function/implementation process of each module in FIG. 3 can be implemented by the processor 401 in FIG. 4 calling the computer execution instructions stored in the memory 402 . For the specific implementation process and functions, refer to the above-mentioned related embodiments.

Other embodiments of the present disclosure will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This disclosure is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common general knowledge or techniques in the technical field not disclosed by this disclosure . The specification and examples are to be regarded as exemplary only, with the true scope and spirit of the disclosure being indicated by the following claims.

It is to be understood that the present disclosure is not limited to the precise structures described above and illustrated in the accompanying drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present disclosure is limited only by the appended claims.

Claims (18)

1. A method for querying data based on a data wide table is characterized by comprising the following steps:

receiving a query instruction of a user;

inquiring in a data wide table according to the fields in the inquiry instruction to obtain inquiry data, wherein the data wide table is obtained by summarizing data in a plurality of business data single tables;

and outputting the query data.

2. The method of claim 1, wherein the querying a data wide table according to a field in the query instruction to obtain query data comprises:

according to the fields in the query instruction, querying indexes corresponding to the fields in the data wide table;

and linking to the query data according to the index.

3. The method of claim 1, wherein prior to receiving the user's query instruction, the method further comprises:

acquiring a plurality of service data list tables;

and acquiring the data wide table by summarizing the data in the plurality of business data single tables.

4. The method of claim 3, wherein the obtaining the data wide table by aggregating data in the plurality of business data sheet tables comprises:

determining a first field according to the service information in the plurality of service data list tables, wherein the first field is a field used for displaying in the data wide table;

and summarizing the data in the plurality of business data list tables according to the first field to obtain the data wide table.

5. The method of claim 4, wherein aggregating data in the plurality of business datasheet tables to obtain the data-wide table according to the first field comprises:

analyzing a second field in each business data form in the plurality of business data form tables, wherein the second field is used for indicating the incidence relation among data in the plurality of business data form tables;

and summarizing the associated data in the plurality of business data list tables by using the second field according to the data range corresponding to the first field to obtain the data wide table.

6. The method according to claim 5, wherein the aggregating the associated data in the plurality of service data list tables using the second field according to the data range corresponding to the first field to obtain the data wide table comprises:

processing the data in the plurality of business data list tables by a multithreading technology according to the data range corresponding to the first field to obtain intermediate data from the plurality of business data list tables;

acquiring associated data in the intermediate data by using the second field;

and summarizing the associated data to obtain the data wide table.

7. The method of claim 5, wherein after the using the second field, aggregating associated data in the plurality of business datasheet tables to obtain the data wide table, the method further comprises:

and determining the index of the associated data in the data width table according to the data size of the associated data.

8. The method of claim 1, further comprising:

detecting data change information in the plurality of business data sheet tables;

when the change of the data in any single table is detected, the data associated with the single table in the data wide table is updated.

9. An apparatus for querying data based on a data wide table, comprising:

the acquisition module is used for receiving a query instruction of a user;

the processing module is used for inquiring in a data wide table according to the fields in the inquiry instruction to obtain inquiry data, and the data wide table is obtained by summarizing data in a plurality of business data single tables;

and the output module is used for outputting the query data.

10. The apparatus of claim 9, wherein the processing module is further configured to: according to the fields in the query instruction, querying indexes corresponding to the fields in the data wide table; linking to the query data according to the index.

11. The apparatus of claim 9, wherein the obtaining module is further configured to: before the query instruction of the user is received, acquiring a plurality of service data list tables; and summarizing the data in the plurality of business data list tables to obtain the data wide table.

12. The apparatus of claim 11, wherein the obtaining module is further configured to: determining a first field according to service information in the plurality of service data list tables, wherein the first field is a field used for query in the data wide table; and summarizing the data in the plurality of business data list tables according to the first field to obtain the data wide table.

13. The apparatus of claim 12, wherein the obtaining module is further configured to: analyzing a second field in each business data form in the plurality of business data form tables, wherein the second field is used for indicating the incidence relation among data in the plurality of business data form tables; and summarizing the associated data in the plurality of business data list tables by using the second field according to the data range corresponding to the first field to obtain the data wide table.

14. The apparatus of claim 13, wherein the obtaining module is further configured to: processing the data in the plurality of business data list tables by a multithreading technology according to the data range corresponding to the first field to obtain intermediate data from the plurality of business data list tables, wherein the intermediate data is data corresponding to the field of the data wide table; acquiring associated data in the intermediate data by using the second field; and summarizing the associated data to obtain the data wide table.

15. The apparatus of claim 13, wherein the obtaining module is further configured to: after the associated data in the service data list tables are summarized by using the second field to obtain the data wide table, determining the index of the associated data in the data wide table according to the data volume of the associated data.

16. The apparatus of claim 9, wherein the obtaining module is further configured to: detecting data change information in the plurality of business data sheet tables; and when detecting that the data in any single table changes, updating the data information associated with any single table in the data wide table.

17. A terminal, comprising:

a memory for storing processor-executable instructions;

a processor; wherein the processor is configured to: for implementing the method of any one of claims 1 to 8 when executing said executable instructions.

18. A computer-readable storage medium, characterized in that the readable storage medium stores an executable program, wherein the executable program, when executed by a processor, implements the method of any one of claims 1 to 8.

Images