A data search method, system and medium based on doris

Abstract

The application relates to a Doris-based data search method, system and medium, wherein the method comprises the following steps: searching static attribute data of a preset entity in a Doris database to obtain an entity ID meeting a preset condition to generate a first temporary table; searching dynamic trajectory data of the preset entity based on an event time when the preset entity is in an active state to generate a second temporary table; and performing Inner join association on the first temporary table and the second temporary table to obtain target data meeting the preset condition and being active in a specified time period. Through the application, Doris-based data search is realized, not only static fields such as required entity IDs can be searched from the static attribute data of the entity, but also corresponding dynamic fields can be filtered from the dynamic trajectory data associated with the entity, and the data meeting the requirements is matched and associated to be returned in full, so that the problem that the existing database retrieval scheme has poor retrieval effect is solved.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a Doris-based data search method, system, and medium. Background Technology

[0002] Doris, as a high-performance, real-time analytical database with an MPP architecture, boasts efficient query performance, delivering sub-second response times and returning search results for massive datasets. Previously, data searching was largely implemented using the Elasticsearch search engine.

[0003] Elasticsearch can only search for specific articles or fields, and returns a specific number of search results directly. Returning more results results in a slower response time. It also cannot bind search results to time-based filtering conditions, making it difficult to achieve the goal of matching the search results of a certain entity to whether it appears in the time-based trajectory table.

[0004] Currently, there is no effective solution to the problem of poor retrieval results in existing database retrieval schemes in related technologies. Summary of the Invention

[0005] This application provides a data search method, system, and medium based on Doris, to at least address the problem of poor retrieval performance in existing database retrieval schemes in related technologies.

[0006] In a first aspect, embodiments of this application provide a data search method based on Doris, the method comprising:

[0007] The static attribute data of preset entities in the Doris database are searched to obtain entity IDs that meet preset conditions to generate a first temporary table. The preset entities include ships, aircraft and satellites.

[0008] Based on the event time when the preset entity is in an active state, the dynamic trajectory data of the preset entity in the Doris database is searched to generate a second temporary table;

[0009] Perform an inner join on the first temporary table and the second temporary table to obtain target data that meets the preset conditions and has been active within a specified time period.

[0010] In some embodiments, the method includes, prior to searching data of preset entities in the Doris database:

[0011] The static attribute data of the preset entity is concatenated to obtain entity field data;

[0012] The dynamic trajectory data of the preset entity is fully aggregated to obtain the first full aggregated data and the second full aggregated data;

[0013] The static attribute data, the dynamic trajectory data, the entity field data, the first full aggregate data, and the second full aggregate data are stored in the Doris database.

[0014] In some embodiments, the dynamic trajectory data of the preset entity is fully aggregated to obtain the first fully aggregated data, which includes:

[0015] The dynamic trajectory data generated by the preset entity every day is fully aggregated hourly to obtain the first full aggregated data, wherein the first full aggregated data includes the last dynamic trajectory data of each preset entity from 0:00 on the day to the current aggregation time.

[0016] In some embodiments, the dynamic trajectory data of the preset entity is fully aggregated to obtain second fully aggregated data, including:

[0017] The dynamic trajectory data generated by the preset entity each year is fully aggregated daily to obtain the second full aggregated data, wherein the second full aggregated data includes the last dynamic trajectory data of each preset entity from the preset year time point to the current aggregation time point.

[0018] In some embodiments, after searching the dynamic trajectory data of the preset entity in the Doris database based on the event time when the preset entity is in an active state to obtain a second temporary table, the method includes:

[0019] Based on the event time when the preset entity is in an active state, the first full aggregate data of the preset entity in the Doris database is searched to generate a third temporary table;

[0020] Based on the event time when the preset entity is in an active state, the second full aggregate data of the preset entity in the Doris database is searched to generate a fourth temporary table.

[0021] In some embodiments, performing an inner join on the first temporary table and the second temporary table to obtain target data that meets the preset conditions and has been active within a specified time period includes:

[0022] Perform a Full outer join on the second, third, and fourth temporary tables to generate a fifth temporary table;

[0023] Perform an inner join on the first temporary table and the fifth temporary table to obtain target data that meets the preset conditions and has been active within a specified time period.

[0024] In some embodiments, the static attribute data of the preset entity is concatenated to obtain entity field data, including:

[0025] The static attribute data of the preset entity is concatenated to obtain entity field data, and a corresponding Doris inverted index is created for the entity field data.

[0026] In some embodiments, searching the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions to generate a first temporary table includes:

[0027] The Doris inverted index function searches the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions, thus generating the first temporary table.

[0028] Secondly, embodiments of this application provide a data search system based on Doris, the system being used to perform the method described in any of the first aspects above, the system including a static data retrieval module, a dynamic data retrieval module, and a matching and association module;

[0029] The static data retrieval module is used to search the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions to generate a first temporary table. The preset entities include ships, aircraft and satellites.

[0030] The dynamic data retrieval module is used to search the dynamic trajectory data of the preset entity in the Doris database according to the event time when the preset entity is in an active state to generate a second temporary table.

[0031] The matching and association module is used to perform an inner join on the first temporary table and the second temporary table to obtain target data that meets the preset conditions and has been active within a specified time period.

[0032] Thirdly, embodiments of this application provide a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in the first aspect above.

[0033] Compared to related technologies, this application provides a Doris-based data search method, system, and medium. The method searches the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions, generating a first temporary table. The preset entities include ships, aircraft, and satellites. Based on the event time when the preset entities are active, the method searches the dynamic trajectory data of the preset entities in the Doris database to generate a second temporary table. An inner join is performed on the first and second temporary tables to obtain target data that meets preset conditions and has been active within a specified time period. This achieves Doris-based data search, not only retrieving required static fields such as entity IDs from the static attribute data of entities like ships and aircraft, but also extracting corresponding dynamic fields such as latitude and longitude from the dynamic trajectory data associated with the entities. After matching and associating the data that meets the requirements, the entire dataset is returned, solving the problem of poor retrieval performance in existing database retrieval schemes. Attached Figure Description

[0034] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0035] Figure 1 This is a flowchart illustrating the steps of a Doris-based data search method according to an embodiment of this application.

[0036] Figure 2 This is a structural block diagram of a Doris-based data search system according to an embodiment of this application;

[0037] Figure 3 This is a schematic diagram of the internal structure of an electronic device according to an embodiment of this application. Detailed Implementation

[0038] To make the objectives, technical solutions, and advantages of this application clearer, the application is described and illustrated below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application. All other embodiments obtained by those skilled in the art based on the embodiments provided in this application without inventive effort are within the scope of protection of this application.

[0039] Obviously, the accompanying drawings described below are merely some examples or embodiments of this application. Those skilled in the art can apply this application to other similar scenarios based on these drawings without any inventive effort. Furthermore, it is understood that although the efforts made in this development process may be complex and lengthy, for those skilled in the art related to the content disclosed in this application, any changes to design, manufacturing, or production based on the technical content disclosed in this application are merely conventional technical means and should not be construed as insufficient disclosure of the content of this application.

[0040] In this application, the reference to "embodiment" means that a specific feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The appearance of this phrase in various places in the specification does not necessarily refer to the same embodiment, nor is it a separate or alternative embodiment that is mutually exclusive with other embodiments. It will be explicitly and implicitly understood by those skilled in the art that the embodiments described in this application may be combined with other embodiments without conflict.

[0041] Unless otherwise defined, the technical or scientific terms used in this application shall have the ordinary meaning understood by one of ordinary skill in the art to which this application pertains. The terms “a,” “an,” “an,” “the,” and similar words used in this application do not indicate quantity limitation and may indicate singular or plural. The terms “comprising,” “including,” “having,” and any variations thereof used in this application are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or device that includes a series of steps or modules (units) is not limited to the listed steps or units, but may also include steps or units not listed, or may include other steps or units inherent to these processes, methods, products, or devices. The terms “connected,” “linked,” “coupled,” and similar words used in this application are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. “Multiple” used in this application refers to two or more. “And / or” describes the relationship between related objects, indicating that three relationships may exist; for example, “A and / or B” can represent: A alone, A and B simultaneously, and B alone. The character " / " generally indicates that the preceding and following objects are in an "or" relationship. The terms "first," "second," and "third" used in this application are merely to distinguish similar objects and do not represent a specific ordering of the objects.

[0042] This application provides a data search method based on Doris. Figure 1 This is a flowchart illustrating the steps of the Doris-based data search method according to an embodiment of this application, as follows: Figure 1As shown, the method includes the following steps:

[0043] Step S102: Search the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions to generate a first temporary table. The preset entities include ships, aircraft and satellites.

[0044] Specifically, step S102 involves using the Doris inverted index function to search the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions, thereby generating a first temporary table.

[0045] It should be noted that when performing data search queries, the static attribute data (entity table) of the preset entities is searched first. Doris inverted index functions (such as match_all, match_any) can be used to perform the required matching search operation to find the entity IDs that meet the preset conditions, so as to form the first temporary table tmp01.

[0046] Before step S102, the method includes step S101, which specifically includes the following steps:

[0047] Step S1011: Concatenate the fields of the static attribute data of the preset entity to obtain the entity field data;

[0048] Specifically, step S1011 involves concatenating the static attribute data of the preset entity to obtain entity field data, and creating a corresponding Doris inverted index for the entity field data.

[0049] It should be noted that the searchable fields of the static attribute data (entity table) of the preset entity are concatenated into a single field search_key, and a Doris inverted index is created for this field, with Unicode encoding selected.

[0050] Step S1012: Perform full aggregation on the dynamic trajectory data of the preset entity to obtain the first full aggregation data and the second full aggregation data;

[0051] Specifically, step S1012:

[0052] ① Perform full aggregation of the dynamic trajectory data generated by the preset entities every day every hour to obtain the first full aggregation data. The first full aggregation data includes the last dynamic trajectory data of each preset entity from 0:00 on the day to the current aggregation time.

[0053] It should be noted that the dynamic trajectory data (trajectory table) of the preset entities is fully aggregated hourly on the same day, and the aggregation is performed once per hour to obtain the first full aggregated data B1. B1 retains the last dynamic trajectory data of each preset entity from 0:00 on the same day to the current aggregation time. It is preferable to retain the maximum event time of the preset entity up to the current aggregation time and the aggregation table ID (including entity ID and current aggregation time merge_time).

[0054] ② Perform full aggregation of the dynamic trajectory data generated by the preset entities every year to obtain the second full aggregation data. The second full aggregation data includes the last dynamic trajectory data of each preset entity from the preset year time point to the current aggregation time point.

[0055] It should be noted that the dynamic trajectory data (trajectory table) of the preset entities are fully aggregated on a daily basis to obtain the first full aggregated data B2. This B2 retains the last dynamic trajectory data of each preset entity from the preset year time point (such as 1970-1-1 00:00:00) to the current aggregation time point. Preferably, the maximum event time of the preset entity up to the current aggregation time point and the aggregation table ID (including the entity ID and the current aggregation time point merge_time) are retained.

[0056] Step S1013: Store the static attribute data, dynamic trajectory data, entity field data, first full aggregate data, and second full aggregate data into the Doris database.

[0057] It should be noted that the static attribute data (entity table) and dynamic trajectory data (trajectory table) of the preset entities are stored in the Doris database. During subsequent data searches, the static attribute data (entity table) is searched to determine whether these preset entities have been active within the specified startTime-endTime time range. Active data is returned. Furthermore, storing the entity field data of the preset entities, the first full aggregate data, and the second full aggregate data in the Doris database also improves the accuracy of subsequent data searches.

[0058] Step S104: Based on the event time when the preset entity is in an active state, search the dynamic trajectory data of the preset entity in the Doris database to generate a second temporary table.

[0059] Specifically, step S104 involves filtering the event time field of the dynamic trajectory data (trajectory table), using Doris data retrieval functions (such as hours_sub and date_trunc functions) to operate on endTime, and finally filtering out dynamic trajectory data whose event time is greater than startTime and is between hours_sub(date_trunc(endTime,'hour'),1). The filtered dynamic trajectory data is then grouped by entity ID using a group by operation, and a second temporary table tmp02 is generated using the may_by function.

[0060] Following step S104, the method includes step S105, which specifically includes the following steps:

[0061] Step S1051: Based on the event time when the preset entity is in an active state, search the first full aggregate data of the preset entity in the Doris database to generate a third temporary table.

[0062] Specifically, step S1051 involves filtering the first full aggregated data B1 of the preset entity, formatting endTime to the time of the previous hour, and making the aggregated time field merge_time of the first full aggregated data B1 greater than startTime and equal to hours_sub(date_trunc(endTime,'hour'),1), thus forming the third temporary table tmp03.

[0063] Step S1052: Based on the event time when the preset entity is in an active state, search the second full aggregate data of the preset entity in the Doris database to generate a fourth temporary table.

[0064] Specifically, step S1052 involves filtering the second full aggregated data B2 of the preset entity, formatting endTime to the time of 0:00 on the same day, so that the aggregation time field merge_time of the second full aggregated data B2 is greater than startTime and equal to date_trunc(endTime,'day'), thus forming the fourth temporary table tmp04.

[0065] Step S106: Perform an inner join on the first temporary table and the second temporary table to obtain the target data that meets the preset conditions and has been active within the specified time period.

[0066] Step S106 specifically includes the following steps:

[0067] Step S1061: Perform a Full outer join on the second, third, and fourth temporary tables to generate a fifth temporary table;

[0068] It's important to note that in database query languages, a Full outer join is a type of join that returns all matching rows from both tables. If no match is found in one table, the columns in the other table are filled with NULL values. In other words, the result set of a Full outer join includes the results of both LEFT JOIN and RIGHT JOIN. For the Doris database, Doris supports Full outer join operations, allowing users to merge all records from two tables, even if there are no matching keys.

[0069] Step S1062: Perform an inner join on the first temporary table and the fifth temporary table to obtain the target data that meets the preset conditions and has been active within the specified time period.

[0070] It's important to note that in database query languages, an inner join is a type of join that returns a combination of matching rows from two tables. Only records that correspond to key values ​​in both tables will appear in the final result set. In other words, an inner join only selects records that exist in both joined tables. For the Doris database, an inner join is used to extract data from two or more tables. Records are returned only if the specified join condition is true, typically involving one or more key fields shared between the two tables.

[0071] Through the steps described above in this application embodiment, Doris-based data search is realized. It can not only search for the required static fields such as entity ID from the static attribute data of entities such as ships and aircraft, but also extract the corresponding dynamic fields such as latitude and longitude from the dynamic trajectory data associated with the entities. After matching and associating the data that meets the requirements, it returns all the data, which solves the problem of poor search results in existing database search schemes.

[0072] Compared to existing massive data search methods—which can only search entity tables and return results, unable to associate entity IDs with hundreds of billions of trajectory tables to determine if an entity has appeared in a trajectory table within any time period—the Doris-based data search method proposed in this embodiment supports single-field searches as well as various AND, OR, and NOT searches involving multiple fields. This new massive data search method performs correlation filtering searches from time, space, and conditional dimensions, enriching the search functionality and making it more than just a single search function. It improves the speed and accuracy of search while reducing the complexity of various operations.

[0073] It should be noted that the steps shown in the above process or in the flowchart of the accompanying figures can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases the steps shown or described may be executed in a different order than that shown here.

[0074] This application provides a data search system based on Doris. Figure 2 This is a structural block diagram of a Doris-based data search system according to an embodiment of this application, such as... Figure 2 As shown, the system includes a static data retrieval module, a dynamic data retrieval module, and a matching and association module;

[0075] The static data retrieval module is used to search the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions to generate the first temporary table. The preset entities include ships, aircraft and satellites.

[0076] The dynamic data retrieval module is used to search the dynamic trajectory data of preset entities in the Doris database based on the event time when the preset entities are in an active state, so as to generate a second temporary table.

[0077] The matching and association module is used to perform an inner join on the first temporary table and the second temporary table to obtain target data that meets preset conditions and has been active within a specified time period.

[0078] Through the static data retrieval module, dynamic data retrieval module, and matching and association module in this application embodiment, data search based on Doris is realized. It can not only search for the required static fields such as entity ID from the static attribute data of entities such as ships and aircraft, but also extract the corresponding dynamic fields such as latitude and longitude from the dynamic trajectory data associated with the entities. After matching and associating the data that meets the requirements, it returns all the data, which solves the problem of poor retrieval effect in existing database retrieval schemes.

[0079] It should be noted that the above modules can be functional modules or program modules, and can be implemented through software or hardware. For modules implemented through hardware, the above modules can reside in the same processor; or the above modules can be located in different processors in any combination.

[0080] This embodiment also provides an electronic device, including a memory and a processor, wherein the memory stores a computer program and the processor is configured to run the computer program to perform the steps in any of the above method embodiments.

[0081] Optionally, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor and the input / output device is connected to the processor.

[0082] It should be noted that the specific examples in this embodiment can refer to the examples described in the above embodiments and optional implementations, and will not be repeated here.

[0083] Furthermore, in conjunction with the Doris-based data search method in the above embodiments, this application embodiment can provide a storage medium for implementation. This storage medium stores a computer program; when executed by a processor, the computer program implements any of the Doris-based data search methods in the above embodiments.

[0084] In one embodiment, a computer device is provided, which may be a terminal. The computer device includes a processor, memory, a network interface, a display screen, and an input device connected via a system bus. The processor provides computing and control capabilities. The memory includes a non-volatile storage medium and internal memory. The non-volatile storage medium stores an operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs in the non-volatile storage medium. The network interface is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, it implements a Doris-based data search method. The display screen may be a liquid crystal display (LCD) or an e-ink display. The input device may be a touch layer covering the display screen, buttons, a trackball, or a touchpad mounted on the computer device casing, or an external keyboard, touchpad, or mouse.

[0085] In one embodiment, Figure 3 This is a schematic diagram of the internal structure of an electronic device according to an embodiment of this application, such as... Figure 3 As shown, an electronic device is provided, which can be a server, and its internal structure diagram can be as follows. Figure 3As shown, this electronic device includes a processor, a network interface, internal memory, and non-volatile memory connected via an internal bus. The non-volatile memory stores an operating system, computer programs, and a database. The processor provides computing and control capabilities, the network interface communicates with external terminals via a network connection, the internal memory provides an environment for the operation of the operating system and computer programs, the computer programs are executed by the processor to implement a Doris-based data search method, and the database stores data.

[0086] Those skilled in the art will understand that Figure 3 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the electronic device to which the present application is applied. The specific electronic device may include more or fewer components than shown in the figure, or combine certain components, or have different component arrangements.

[0087] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. This computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), RAMbus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and RAMbus dynamic RAM (RDRAM), etc.

[0088] Those skilled in the art should understand that the technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments have been described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0089] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are relatively specific and detailed, they should not be construed as limiting the scope of the invention patent. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this patent application should be determined by the appended claims.

Claims

1. A data search method based on Doris, characterized in that, The method includes: The static attribute data of a preset entity is concatenated to obtain the entity field data; The dynamic trajectory data of the preset entity is fully aggregated to obtain the first full aggregated data and the second full aggregated data; The static attribute data, the dynamic trajectory data, the entity field data, the first full aggregate data, and the second full aggregate data are stored in the Doris database; The static attribute data of the preset entities in the Doris database are searched to obtain entity IDs that meet the preset conditions to generate a first temporary table. The preset entities include ships, aircraft and satellites. Based on the event time when the preset entity is in an active state, the dynamic trajectory data of the preset entity in the Doris database is searched to generate a second temporary table; Based on the event time when the preset entity is in an active state, the first full aggregate data of the preset entity in the Doris database is searched to generate a third temporary table; Based on the event time when the preset entity is in an active state, the second full aggregate data of the preset entity in the Doris database is searched to generate a fourth temporary table; Perform a Full outer join on the second, third, and fourth temporary tables to generate a fifth temporary table; Perform an inner join on the first temporary table and the fifth temporary table to obtain target data that meets the preset conditions and has been active within a specified time period.

2. The method according to claim 1, characterized in that, The dynamic trajectory data of the preset entity is fully aggregated to obtain the first fully aggregated data, which includes: The dynamic trajectory data generated by the preset entity every day is fully aggregated hourly to obtain the first full aggregated data, wherein the first full aggregated data includes the last dynamic trajectory data of each preset entity from 0:00 on the day to the current aggregation time.

3. The method according to claim 1, characterized in that, The second full-collection data, obtained by fully aggregating the dynamic trajectory data of the preset entity, includes: The dynamic trajectory data generated by the preset entity each year is fully aggregated daily to obtain the second full aggregated data, wherein the second full aggregated data includes the last dynamic trajectory data of each preset entity from the preset year time point to the current aggregation time point.

4. The method according to claim 1, characterized in that, The static attribute data of the preset entity is concatenated to obtain entity field data, which includes: The static attribute data of the preset entity is concatenated to obtain entity field data, and a corresponding Doris inverted index is created for the entity field data.

5. The method according to claim 4, characterized in that, The static attribute data of preset entities in the Doris database is searched to obtain entity IDs that meet preset conditions, which are then used to generate the first temporary table, including: The Doris inverted index function searches the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions, thus generating the first temporary table.

6. A data search system based on Doris, characterized in that, The system is used to perform the method according to any one of claims 1 to 5, and the system includes a static data retrieval module, a dynamic data retrieval module, and a matching and association module; The static data retrieval module is used to search the static attribute data of preset entities in the Doris database to obtain entity IDs that meet preset conditions to generate a first temporary table. The preset entities include ships, aircraft and satellites. The dynamic data retrieval module is used to search the dynamic trajectory data of the preset entity in the Doris database according to the event time when the preset entity is in an active state to generate a second temporary table. The matching and association module is used to perform an inner join on the first temporary table and the second temporary table to obtain target data that meets the preset conditions and has been active within a specified time period.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1 to 5.

Images