Order data indexing method, system, computer device and storage medium

Abstract

The present invention provides an order data indexing method, system, computer device and storage medium, and the solution method includes: according to a preset time threshold, obtaining first order data with a storage time not greater than the time threshold; generating a log file of the first order data, clustering the log file according to the primary keyword of the log file to obtain a data service layer wide table; dividing the data service layer wide table into several partitions; obtaining a query instruction, and indexing the primary keyword in the several partitions according to the query instruction to obtain target order data; the solution improves the efficiency of slow order data query when the order quantity is too large through column storage and distributed computing services. Compared with traditional databases, real-time query can be achieved in seconds, with faster query speed and faster response speed, and can be widely used in the field of computer technology.

Description

Technical Field

[0001] The present invention belongs to the field of computer technology, and in particular to an order data indexing method, system, computer equipment and storage medium. Background Art

[0002] With the development of mobile internet, people have an increasing demand for online shopping. As transaction volume and business logic on e-commerce platforms grow, the logical relationships between orders and customers generate massive amounts of data. Traditional data technology models typically use the relational database management system MySQL as a storage engine. MySQL can meet normal business needs when the data volume is small. However, existing data query solutions can slow down query speeds and data product responses when the data volume becomes excessive, leading to issues such as stuck user orders and data analysis. Summary of the Invention

[0003] In view of this, in order to at least partially solve one of the above technical problems, the embodiments of the present invention aim to provide a more convenient, more efficient and faster order data indexing method, system, device and storage medium that can cope with data concurrency.

[0004] In a first aspect, the technical solution of the present application provides an order data indexing method, the steps of which include:

[0005] According to a preset time threshold, obtaining first order data whose storage time is not greater than the time threshold;

[0006] generating a log file of the first order data, and clustering the log file according to a primary keyword of the log file to obtain a data service layer wide table;

[0007] Dividing the data service layer wide table into a plurality of partitions;

[0008] A query instruction is obtained, and target order data is obtained by indexing the primary keywords in the plurality of partitions according to the query instruction.

[0009] In a feasible embodiment of the present application solution, the order data indexing method further includes:

[0010] Acquire second order data whose storage time is longer than the time threshold, and perform offline cleaning on the second order data;

[0011] Generate a data service layer wide table according to a mapping relationship between a second field attribute of the second order data and a first field attribute of the first order data;

[0012] Clustering is performed based on the order data in the detail data wide table to obtain several data service layer wide tables.

[0013] In a feasible embodiment of the solution of the present application, generating the log file of the first order data and clustering the log file according to the primary keyword of the log file includes the following steps:

[0014] generating an original keyword of the first order data according to the log file, and combining the original keyword with the first character to obtain the primary keyword;

[0015] The first character is calculated by using a hash value of the first order data and the number of partitions.

[0016] In a feasible embodiment of the solution of the present application, before generating a log file of the first order data and clustering the log file according to a primary keyword of the log file to obtain a data service layer wide table, the method further includes:

[0017] Obtaining the first order data and performing desensitization processing on the first order data;

[0018] The desensitization treatment comprises the following steps:

[0019] Replacing the user information in the first order data with a symbol string;

[0020] The fields in the first order data are filtered according to the mapping relationship.

[0021] In a feasible embodiment of the solution of the present application, after the step of constructing a wide table of detailed data by combining the second order data after offline cleaning with the first order data, the method further includes at least one of the following steps:

[0022] Normalizing the order data in the wide table of detail data to unify the data format of the order data;

[0023] The order data in the detail data wide table is cleaned to reduce null values ​​and dirty data in the order data.

[0024] In a feasible embodiment of the solution of the present application, before the step of acquiring, according to the preset time threshold, first order data whose storage time is not greater than the time threshold, the method further includes:

[0025] Matching a data table using a regular expression, where the data table includes a number of the first order data;

[0026] Set the data warehouse parameters for obtaining the data table.

[0027] In a feasible embodiment of the solution of the present application, the obtaining query instruction, indexing the target order data by the primary keyword in the plurality of partitions according to the query instruction, includes the following steps:

[0028] Obtaining several historical query results, and obtaining high-frequency fields in the historical query results;

[0029] An index table is constructed based on the high-frequency fields, and target order data is queried based on the index table.

[0030] In a second aspect, the technical solution of the present invention further provides an order data indexing system, the system comprising:

[0031] A data acquisition module, configured to acquire, according to a preset time threshold, first order data whose storage time is not greater than the time threshold;

[0032] a data classification module, configured to generate a log file of the first order data, cluster the log file according to a primary keyword of the log file to obtain a data service layer wide table; and divide the data service layer wide table into a plurality of partitions;

[0033] The data query module is used to obtain a query instruction, and to index the plurality of partitions using the primary keyword according to the query instruction to obtain target order data.

[0034] In a third aspect, the technical solution of the present invention further provides a computer device for indexing order data, comprising:

[0035] at least one processor;

[0036] at least one memory for storing at least one program;

[0037] When at least one program is executed by at least one processor, the at least one processor executes the method described in the first aspect.

[0038] In a fourth aspect, the technical solution of the present invention further provides a storage medium, in which a program executable by a processor is stored. When the program executable by the processor is executed by the processor, it is used to run the order data indexing method described in the first aspect.

[0039] The advantages and beneficial effects of the present invention will be partially given in the following description, and the rest can be understood through the specific embodiments of the present invention:

[0040] The technical solution of the present application sets a time threshold, obtains corresponding order data according to the time threshold, clusters and divides massive order data through the log file of the order data to obtain a data service layer wide table, and constructs corresponding partitions. The partition contains several data service layer wide tables, and then searches and compares the primary keywords in the partition according to the query instruction to obtain the target order data; the solution uses column storage and distributed computing services to improve the efficiency of slow order data query when the number of orders is too large. Compared with traditional databases, real-time query can be achieved in seconds, with faster query speed and faster response speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0042] Figure 1 A flowchart of a method for indexing order data provided by an embodiment of the present invention;

[0043] Figure 2 A flowchart of another order data indexing method provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0044] The embodiments of the present invention are described in detail below. Examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention and are not to be construed as limiting the present invention. The step numbers in the following embodiments are provided for ease of explanation only and do not limit the order of the steps. The order of execution of the steps in the embodiments can be adaptively adjusted according to the understanding of those skilled in the art.

[0045] Existing technologies typically use the relational database management system MySQL as a storage engine. While MySQL can meet the normal needs of business data in traditional data processing scenarios, in the common scenario of high-concurrency order data, the excessively large amount of data slows down query speeds, affecting the responsiveness of data products, and causing process and data analysis bottlenecks. To address these issues, this application provides a technical solution for rapidly indexing order data for big data.

[0046] First, as Figure 1As shown, the technical solution of the present application provides an order data indexing method, the main steps of which may include S100-S400:

[0047] S100: Acquire first order data whose storage time is no longer than the preset time threshold;

[0048] The time threshold is the pre-set validity period of the order data. Data within the validity period is available data. Data outside the validity period needs to be synchronized, loaded, and cleaned before being integrated with the available data. Specifically, in the embodiment, the order data within the validity period is first obtained from the storage space or storage container where the order data is initially aggregated. For example, the open source framework Canal can be used in the embodiment to synchronize the incremental data of the database to the data in the embodiment. The storage space where the order data is initially aggregated is a MySQL database. Canal can act as a MySQL slave, simulating the interaction protocol of the MySQL slave to send a dump protocol to MySQL Mater. MySQL Mater receives the dump request sent by Canal and starts pushing the binary log to Canal. Canal then parses the binary log and sends it to the storage destination, which includes but is not limited to MySQL, Kafka, and Elastic Search. Taking the validity period of one year as an example, the embodiment synchronizes the binary log of the order-related table to Kafka through Canal. It should be noted that, in the embodiment, when the amount of order data obtained is small, a single-partition topic can be established through Kafka, where the topic is the basic unit of Kafka data writing operation and a copy can be specified.

[0049] S200: Generate a log file of first order data, and cluster the log file according to the primary keyword of the log file to obtain a data service layer wide table;

[0050] Among them, the log file (binary log) is used to record data modification records in the database; including but not limited to insert, update, delete, create, drop and alter related statements. The primary key (primary key) is one or more fields in a data table, and the primary key value is used to uniquely identify a record in the table. In the relationship between two tables, the primary key is used to reference a specific record from another table in one table. The data service layer (datawarehouse details, DWD) is the isolation layer between the business layer and the data warehouse; a wide table refers to a database table with more fields. It usually refers to a database table that is associated with indicators, dimensions, and attributes related to the business entity. Exemplarily, in the embodiment, the order data obtained in step S100 is synchronized to Kafka, and the order data is cleaned and desensitized as necessary through the Flink data flow programming model, and then the cleaned and desensitized order data is aggregated and written to Hbase to construct the data DWD layer in Hbase, and further generate a DWD wide table.

[0051] S300, dividing the wide table of the data service layer into several partitions;

[0052] A partition (Region) is the smallest unit of data distribution in an HBase cluster. When data is initially written to a table in this example, the table has only one Region. As data increases, the Region begins to grow. When it reaches a certain threshold, it is split into two Regions of roughly the same size. This threshold is the StoreFile size. Before the first Region split, all loaded data is placed on the server in the original Region. As the table grows, the number of Regions increases accordingly. Furthermore, the RegionServer is a core module in HBase, responding to user I / O requests and performing data reading and writing.

[0053] In addition, the embodiments also build a data warehouse base (DWS) layer for storing objective data, serving as an intermediate layer or a data layer for a large number of indicators. Based on the basic data on the DWB layer, the embodiments can integrate and summarize DWD wide tables into a service data layer for analyzing a specific subject domain, and obtain data service layer (DWS) wide tables for subsequent business queries, OLAP analysis, data distribution, etc. It is understood that RegionServer can be applied to any wide table in the embodiments.

[0054] S400: Obtain a query instruction, and perform indexing using primary keywords in several partitions according to the query instruction to obtain target order data;

[0055] Specifically, in the backend of this embodiment, Phoenix is ​​used to query DWS wide tables based on user query instructions for target order data. In this embodiment, Phoenix is ​​used to build an HBase SQL layer, allowing HBase to query data in tables created by Phoenix. If data is updated in Phoenix's tables, the HBase tables can also be updated. Performing an aggregate query on data in Phoenix results in a faster response time than existing query methods.

[0056] In the related art, during the writing process in HBase, a Region is controlled by a RegionServer. When a Region exceeds the default size, it is split into two smaller Regions. The new Region will write all new records, but the new Region will still be on the same RegionServer, resulting in uneven resource allocation in the cluster and affecting cluster performance. Therefore, to avoid the problem of hot spots in a single RegionServer, this embodiment performs salting. That is, in the step of generating a log file of the first order data in S200 and clustering the log file according to the primary keyword of the log file, step S210 can be included:

[0057] S210: Generate an original keyword for the first order data based on the log file, and combine the original keyword with the first character to obtain a primary keyword;

[0058] The first character is a 1-byte byte calculated by the hash value of the first order data and the number of partitions. The original keyword refers to the original primary key of the order data. The primary keyword refers to the primary key obtained by salting. Specifically, the calculation formula of the salting process in the embodiment is as follows:

[0059] new_row_key=((byte)(hash(key)%BUCKETS_NUMBER)+original_key

[0060] In this calculation formula, BUCKETS_NUMBER is the number of buckets formed by adding salt, original_key is the original primary key of the data, and the new primary key generated by the data will write the data to different regions. The data in each region is a subset of the original continuously increasing data. The data written to each node is load balanced.

[0061] In some optional embodiments, in order to fully utilize data outside the validity period, the method of this embodiment further includes steps S500-S600:

[0062] S500: Acquire second order data whose storage duration is greater than a time threshold, and perform offline cleaning on the second order data;

[0063] like Figure 2 As shown, for example, taking the time threshold set to 1 year as an example, the embodiment uses an offline cleaning method for order-related data that is more than one year old, imports the data into Hbase, and then synchronizes it with order data that is not more than one year old.

[0064] Specifically, in the embodiment, sqoop+dataX is used to synchronize MySQL data to the Hive data warehouse through offline cleaning, and then a Phoenix external table is built in Hive. The business processing logic consistent with real-time processing is used for the original data to build a wide table and write it to the Phoenix external table. After the writing is completed, Canal, Kafka and Flink services are started to write the data to Hbase in real time.

[0065] S600: Generate a data service layer wide table based on the offline cleaned second order data and the first order data according to a mapping relationship between the second field attribute of the second order data and the first field attribute of the first order data;

[0066] Specifically, the order-related data obtained in step S500 and the order data obtained in step S100 are summarized in the DWD layer in Hbase, and a data mapping relationship is generated in the DWD layer according to the content of each field in the summarized order data. First, the two parts of the order data before the summary are synchronized according to the mapping relationship so that the two parts of the order data table have the same field attributes and field record content. In addition, the mapping relationship can also be directly called when the subsequent business processing is needed if this information is needed; illustratively, the embodiment can convert the GPS longitude and latitude into detailed addresses of provinces, cities and districts; for example, GPS quick query generally uses geohash mapping for the geographic location knowledge base; then the GPS to be compared is converted into geohash and then compared with the geohash in the knowledge base to find the geographic location information.

[0067] In some optional embodiments, before step S200 of generating a log file of the first order data and clustering the log file according to a primary keyword of the log file to obtain a data service layer wide table, the method of the embodiment further includes step S110:

[0068] S110: Obtain first order data and perform desensitization processing on the first order data; wherein the desensitization processing process includes but is not limited to replacing user information in the first order data with a symbolic string or filtering fields in the first order data according to a mapping relationship;

[0069] For example, in the embodiment, the customer information in the order table is desensitized, the first three digits and the last four digits of the user are retained, and the remaining digits are represented by asterisks. The fields not involved in the report are filtered out, which improves the efficiency of the processing flow while also providing necessary privacy protection for the users involved in the solution.

[0070] In some optional embodiments, after step S600 of constructing a wide table of detailed data by combining the offline cleaned second order data with the first order data, the embodiment method further includes steps S610 and S620:

[0071] S610: Normalize the first order data and the second order data to unify the data format of the order data;

[0072] S620: Clean the first order data and the second order data to reduce null values ​​and dirty data in the order data.

[0073] Specifically, data within the validity period and data that is not within the validity period after cleaning and synchronization are aggregated in the DWD layer for data cleaning and normalization operations. Data cleaning: removes null values, dirty data, and data or records that exceed the limit range.

[0074] In some optional embodiments, the step S400 of obtaining a query instruction and indexing the target order data in a plurality of partitions using primary keywords according to the query instruction may include steps S410-S420:

[0075] S410: Obtain several historical query results, and obtain high-frequency fields in the historical query results;

[0076] S420: Build an index table based on the high-frequency fields, and query the target order data based on the index table;

[0077] Specifically, for some foreseeable queries, a common scenario set in the embodiment is to perform filtering and grouping query operations on several fields. Unlike traditional relational databases such as Mysql, the covering index does not need to use the index to find the data under the primary key. The pre-set fields are retained in the index; for distributed databases, the index may be stored in a different node from the data, and cross-point queries affect the efficiency of the query. By covering the index, the query performance is improved.

[0078] In some optional embodiments, before step S100 of acquiring first order data whose storage time is not greater than the preset time threshold according to the preset time threshold, the embodiment method may further include steps S001-S002:

[0079] S001. Match a data table using a regular expression, where the data table includes a plurality of first order data;

[0080] Specifically, the embodiment changes the Mysql binlog-format to ROW format; then modifies the instance.properties file and the canal.instance.filter.regex configuration to add tables that need to be synchronized. Regular expressions can be used to match tables related to the order "order"; and the number of partitions and hash partition primary keys of each table are controlled by modifying canal.mq.partitionsNum and canal.mq.partitionHash.

[0081] S002. Set the data warehouse parameters for obtaining data tables;

[0082] Specifically, the embodiment also needs to modify canal.properties and fill in the Kafka cluster parameter configuration, port number, message transmission parameters, etc.

[0083] In a second aspect, the technical solution of the present invention further provides an order data indexing system, the system comprising:

[0084] A data acquisition module is used to acquire first order data whose storage time is not greater than the time threshold according to a preset time threshold;

[0085] a data classification module, configured to generate a log file of the first order data, cluster the log file according to a primary keyword of the log file to obtain a data service layer wide table; and divide the data service layer wide table into a plurality of partitions;

[0086] The data query module is used to obtain query instructions, index the primary keywords in several partitions according to the query instructions, and obtain the target order data.

[0087] In a third aspect, the technical solution of the present invention also provides an order data indexing device, which includes: at least one processor; at least one memory for storing at least one program; when the at least one program is executed by at least one processor, the at least one processor runs the method in the first aspect.

[0088] From the above specific implementation process, it can be concluded that the technical solution provided by the present invention has the following advantages or strengths compared with the existing technology:

[0089] This technical solution uses column storage and distributed computing services to improve the efficiency of slow order data queries when the number of orders is too large. Compared with traditional databases, it can achieve real-time queries in seconds, with faster query speeds and faster response speeds.

[0090] In some optional embodiments, the function / operation mentioned in the block diagram may not occur in the order mentioned in the operation diagram. For example, depending on the function / operation involved, the two boxes shown in succession can actually be executed substantially simultaneously or the boxes can sometimes be executed in reverse order. In addition, the embodiment presented and described in the flow chart of the present invention is provided in an exemplary manner for the purpose of providing a more comprehensive understanding of the technology. The disclosed method is not limited to the operation and logic flow presented herein. Optional embodiments are contemplated in which the order of the various operations is changed and the sub-operations described as a part of a larger operation are performed independently.

[0091] In addition, although the present invention is described in the context of functional modules, it should be understood that, unless otherwise stated, one or more of the functions and / or features may be integrated into a single physical device and / or software module, or one or more functions and / or features may be implemented in separate physical devices or software modules. It is also understood that a detailed discussion of the actual implementation of each module is not necessary for understanding the present invention. More specifically, given the properties, functions, and internal relationships of the various functional modules in the devices disclosed herein, the actual implementation of the module will be understood within the ordinary skill of an engineer. Therefore, a person skilled in the art will be able to implement the present invention set forth in the claims using ordinary skill without undue experimentation. It is also understood that the specific concepts disclosed are merely illustrative and are not intended to limit the scope of the present invention, which is determined by the full scope of the appended claims and their equivalents.

[0092] The logic and / or steps represented in the flowchart or otherwise described herein may be considered, for example, as an ordered list of executable instructions for implementing logical functions, and may be embodied in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus, or device (such as a computer-based system, a system including a processor, or other system that can fetch and execute instructions from an instruction execution system, apparatus, or device).

[0093] Throughout this specification, reference to terms such as "one embodiment," "some embodiments," "examples," "specific examples," or "some examples" means that a specific feature, structure, material, or characteristic described in conjunction with that embodiment or example is included in at least one embodiment or example of the present invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.

[0094] While embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions, and variations may be made to the embodiments without departing from the principles and spirit of the invention, and that the scope of the invention is defined by the claims and their equivalents.

[0095] The above is a specific description of the preferred implementation of the present invention, but the present invention is not limited to the above embodiments. Those skilled in the art can make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A method for indexing order data, characterized in that: The following steps are involved: According to a preset time threshold, obtaining first order data whose storage time is not greater than the time threshold; generating a log file of the first order data, and clustering the log file according to a primary keyword of the log file to obtain a data service layer wide table; Dividing the data service layer wide table into a plurality of partitions; Obtaining a query instruction, and indexing the plurality of partitions using the primary keyword according to the query instruction to obtain target order data; The method further comprises: Acquire second order data whose storage time is longer than the time threshold, and perform offline cleaning on the second order data; A data service layer wide table is generated based on the mapping relationship between the second field attribute of the second order data and the first field attribute of the first order data; wherein, the two parts of order data before aggregation are synchronized according to the mapping relationship so that the two parts of the order data tables have the same field attributes and field record contents.

2. The order data indexing method according to claim 1, characterized in that: Generating the log file of the first order data and clustering the log file according to the primary keyword of the log file includes the following steps: generating an original keyword of the first order data according to the log file, and combining the original keyword with the first character to obtain the primary keyword; The first character is calculated by using a hash value of the first order data and the number of partitions.

3. The order data indexing method according to claim 1, characterized in that: Before generating a log file of the first order data and clustering the log file according to a primary keyword of the log file to obtain a data service layer wide table, the method further includes: Obtaining the first order data and performing desensitization processing on the first order data; The desensitization treatment comprises the following steps: Replacing the user information in the first order data with a symbol string; The fields in the first order data are filtered according to the mapping relationship.

4. The order data indexing method according to claim 1, characterized in that: After the step of generating a data service layer wide table based on a mapping relationship between the second field attribute of the second order data and the first field attribute of the first order data, the method further includes at least one of the following steps: Normalizing the first order data and the second order data to unify the data formats of the order data; Data cleaning is performed on the first order data and the second order data to reduce null values ​​and dirty data in the order data.

5. An order data indexing method according to any one of claims 1 to 4, characterized in that: Before the step of acquiring, according to a preset time threshold, first order data whose storage time is not greater than the time threshold, the method further includes: Matching a data table using a regular expression, where the data table includes a number of the first order data; Set the data warehouse parameters for obtaining the data table.

6. An order data indexing method according to any one of claims 1 to 4, characterized in that: The obtaining of the query instruction, indexing the target order data by the primary keyword in the plurality of partitions according to the query instruction, comprises the following steps: Obtaining several historical query results and high-frequency fields in the historical query results; An index table is constructed based on the high-frequency fields, and target order data is queried based on the index table.

7. An order data indexing system, characterized in that: include: A data acquisition module, configured to acquire, according to a preset time threshold, first order data whose storage time is not greater than the time threshold; a data classification module, configured to generate a log file of the first order data, and cluster the log file according to a primary keyword of the log file to obtain a data service layer wide table; Dividing the data service layer wide table into a plurality of partitions; A data query module, configured to obtain a query instruction, and index the plurality of partitions using the primary keyword according to the query instruction to obtain target order data; The system further comprises a module for performing the following steps: Acquire second order data whose storage time is longer than the time threshold, and perform offline cleaning on the second order data; A data service layer wide table is generated based on the mapping relationship between the second field attribute of the second order data and the first field attribute of the first order data; wherein, the two parts of order data before aggregation are synchronized according to the mapping relationship so that the two parts of the order data tables have the same field attributes and field record contents.

8. A computer device for indexing order data, characterized in that: include: at least one processor; At least one memory is used to store at least one program; when the at least one program is executed by the at least one processor, the at least one processor runs the method according to any one of claims 1 to 6.

9. A storage medium storing a program executable by a processor, characterized in that: The processor-executable program is used to run an order data indexing method as described in any one of claims 1 to 6 when executed by the processor.

Images