WO2023087673A1

WO2023087673A1 - Hierarchical data retrieval method and apparatus, and device

Info

Publication number: WO2023087673A1
Application number: PCT/CN2022/097345
Authority: WO
Inventors: 田标; 崔伟; 陈献青; 邓捷; 王刚; 王家宾; 黄文文
Original assignee: 天翼数字生活科技有限公司
Priority date: 2021-11-19
Filing date: 2022-06-07
Publication date: 2023-05-25
Also published as: CN114116716A

Abstract

Provided are a hierarchical data retrieval method and apparatus, and a device. The method comprises: in response to a login request sent by any user end, acquiring a user identifier corresponding to the user end (101); querying, from a preset database, attribution information corresponding to the user identifier, and determining a database hierarchical type corresponding to the user identifier (102); when a data retrieval request sent by the user end is received, acquiring a retrieval type corresponding to the data retrieval request (103); if the retrieval type is relational database retrieval, retrieving, according to the attribution information, a relational database corresponding to the database hierarchical type, so as to obtain first target hierarchical data (104); if the retrieval type is index database retrieval, retrieving, according to the data retrieval request and the attribution information, an ES index database corresponding to the database hierarchical type, so as to obtain second target hierarchical data (105); and returning the first target hierarchical data or the second target hierarchical data to the user end (106). On the basis of simplifying a retrieval architecture and a retrieval path, the efficiency of a data retrieval process for hundreds of millions of users is effectively improved.

Description

A Hierarchical Data Retrieval Method, Device and Equipment

technical field

The invention relates to the technical field of data retrieval, in particular to a hierarchical data retrieval method, device and equipment.

Background technique

Hierarchical data, also called directory tree, is similar to Windows Explorer on the UI, and refers to data stored layer by layer through a tree structure. Its management, search, and analysis are common needs in Internet business, such as the management, storage, and query of departments at all levels of the enterprise and their employees, as well as the upper and lower levels of data between employees; multi-level commodity categories, commodities, and related enterprise and user data management and query; the management and search of multi-level regions in the Internet of Things business and the equipment, equipment manufacturers and enterprises to which the equipment belongs after the equipment is put into use, user data, etc., the data level can have more than ten levels or even unlimited , the corresponding data volume may reach one million or even greater; the corresponding data volume of multi-level video categories, video search, user data management, search, etc. in the Internet of View business may reach billions or even greater. Users and administrators (including regional administrators) involved in these requirements often require different data permissions.

For this reason, traditional hierarchical data storage and search methods usually use relational database MySQL, Oracle or Microsoft SQL Server to store hierarchical data; or use field records to store and retrieve hierarchical relationships.

However, with the continuous expansion of Internet services with hundreds of millions of users, the amount of data associated with hierarchical tree entity data will further increase. For hierarchical data management and search services constructed according to traditional relational database theory, the common table design scheme is only It can loop or recursively use Java and other programming languages to query from the database, and the more hierarchical trees, the deeper the number of loops or the level of recursion; and because the application system and database often run on different servers, each access to the database will go through In the process of network communication, database server internal processing, hard disk I/O interaction and other processes, the system responds slowly and it is difficult to provide large-scale concurrent access when the amount of data and concurrency are large, which leads to a decrease in the search efficiency of hierarchical data and cannot Meet the needs of Internet business development.

Contents of the invention

The present invention provides a hierarchical data retrieval method, device and equipment, which solves the problem that the traditional hierarchical data retrieval method has low retrieval efficiency in the Internet business search process with hundreds of millions of users due to the limitation of its structure and retrieval path. Technical issues that meet the needs of Internet business development.

A hierarchical data retrieval method provided by the first aspect of the present invention includes:

In response to a login request sent by any client, obtain the user ID corresponding to the client;

Querying the attribution information corresponding to the user identifier from a preset database, and determining the database hierarchy type corresponding to the user identifier;

When receiving the data retrieval request sent by the client, obtain the retrieval type corresponding to the data retrieval request;

If the search type is a relational database search, then search the relational database corresponding to the database level type according to the attribution information to obtain the first target level data;

If the retrieval type is index database retrieval, then according to the data retrieval request and the attribution information, retrieve the ES index database corresponding to the database hierarchy type to obtain the second target hierarchy data;

returning the first target level data or the second target level data to the client.

Optionally, the step of querying the attribution information corresponding to the user identifier from a preset database, and determining the database hierarchy type corresponding to the user identifier includes:

Retrieving a preset database according to the user ID, obtaining attribution information corresponding to the user ID and caching it;

judging whether the attribution information includes a superior node ID;

If not, then determine that the database hierarchy type corresponding to the user identifier is a limited hierarchy type;

If yes, it is determined that the database hierarchy type corresponding to the user identifier is an unrestricted hierarchy type.

Optionally, the affiliation information includes a first affiliation organization ID, and the relational database includes a plane hierarchical tree information table and a first organization equipment association information table; when the database hierarchy type is a limited hierarchy type, the if If the retrieval type is a relational database retrieval, then the relational database corresponding to the database hierarchy type is retrieved according to the attribution information, and the step of obtaining the first target hierarchy data includes:

If the retrieval type is a relational database retrieval, then query the equipment information table of the first organization according to the ID of the first affiliation organization, and determine the corresponding equipment data and the branch ID of the hierarchical tree;

Querying the flat hierarchical tree information table according to the hierarchical tree branch ID to determine the corresponding hierarchical tree branch data;

An association between each branch data of the hierarchical tree and the device data is established to obtain first target hierarchical data.

Optionally, the affiliation information includes a second affiliation organization ID and the superior node ID, and the relational database includes a tree node information table, a tree node association relationship table, and a second organization equipment association information table; when the database level When the type is an unrestricted hierarchical type, if the retrieval type is a relational database retrieval, the step of retrieving the relational database corresponding to the database hierarchical type according to the attribution information to obtain the first target hierarchical data includes:

If the retrieval type is a relational database retrieval, then query the equipment information table of the second organization according to the ID of the second affiliation organization, and determine the corresponding equipment data, the ID of the superior node and the node ID of the hierarchical tree;

Retrieving the tree node information table by using the superior node ID and the hierarchical tree node ID to determine all associated tree node IDs;

Retrieving the tree node association relationship table according to each of the tree node IDs to obtain the first node association relationship corresponding to each of the tree node IDs;

Associating the tree nodes corresponding to each of the tree node IDs according to the first node association relationship and establishing an association between the upper-level node ID and the device data to obtain first target hierarchical data.

Optionally, the affiliation information includes a third organization affiliation ID; the ES index library includes a flat hierarchical tree index table and a first organization equipment association index table; when the database hierarchy type is a limited hierarchy type, the if The retrieval type is an index database retrieval, then according to the data retrieval request and the attribution information, retrieve the ES index database corresponding to the database hierarchy type, and obtain the second target hierarchy data, including:

If the retrieval type is an index database retrieval, then parsing the data retrieval request to obtain a search condition and a target data format;

Retrieve the organization equipment association index table according to the third organization attribution ID, and determine the corresponding equipment data;

Search the flat hierarchical tree index table according to the search condition, and determine the corresponding hierarchical tree information;

Establishing the association between the hierarchical tree information and the device data and converting it into the target data format to obtain second target hierarchical data.

Optionally, the affiliation information includes a fourth organization affiliation ID, and the ES index library includes a tree node information index table and a second organization equipment association index table; when the database hierarchy type is an unrestricted hierarchy type, the If the retrieval type is index database retrieval, then according to the data retrieval request and the attribution information, retrieve the ES index database corresponding to the database hierarchy type, and obtain the second target hierarchy data, including:

If the retrieval type is an index database retrieval, then parsing the data retrieval request to obtain a search condition;

Retrieving the second organization's equipment association index table according to the fourth organization's attribution ID, and determining the corresponding equipment data and the hierarchical tree branch ID to which it belongs;

Searching the tree node information index table according to the search condition to obtain a plurality of hierarchical tree node IDs meeting the search condition;

Retrieving the second organization equipment association index table according to each of the hierarchical tree node IDs, and determining the second node association relationship corresponding to each of the hierarchical tree node IDs;

The tree nodes corresponding to each of the hierarchical tree node IDs are associated according to the second node association relationship, and an association with the device data is established to obtain second target hierarchical data.

Optionally, also include:

When receiving the device reallocation request sent by the additional client, creating an additional user identifier corresponding to the additional client;

determining corresponding additional device data according to the device reallocation request;

An association between the additional user identifier and the additional device data is established, and an additional ES index table is obtained and stored in the ES index library.

Optionally, also include:

Executing a preset configuration file according to a preset update cycle, performing field conversion on the relational data in the relational database, and obtaining ES index fields;

Synchronize the ES index field to the ES index library.

The second aspect of the present invention provides a hierarchical data retrieval device, including:

A user identification acquisition module, configured to obtain a user identification corresponding to the user terminal in response to a login request sent by any user terminal;

A database hierarchy type determining module, configured to query the attribution information corresponding to the user identifier from a preset database, and determine the database hierarchy type corresponding to the user identifier;

A retrieval type acquisition module, configured to obtain the retrieval type corresponding to the data retrieval request when receiving the data retrieval request sent by the client;

A relational data retrieval module, configured to retrieve the relational database corresponding to the database hierarchy type according to the attribution information if the retrieval type is a relational database retrieval, to obtain the first target hierarchical data;

An index data retrieval module, configured to retrieve the ES index library corresponding to the database hierarchy type according to the data retrieval request and the attribution information if the retrieval type is index database retrieval, to obtain second target hierarchical data;

A hierarchical data returning module, configured to return the first target hierarchical data or the second target hierarchical data to the client.

The third aspect of the present invention also provides an electronic device, including a memory and a processor, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the processor is executed as described in the first aspect of the present invention. In one aspect, the steps of the hierarchical data retrieval method described in any one aspect.

As can be seen from the above technical solutions, the present invention has the following advantages:

The present invention obtains the user identification corresponding to the user terminal by responding to a login request sent by any user terminal; queries the attribution information corresponding to the user identification from a preset database, and determines the database hierarchy type corresponding to the user identification; When a data retrieval request is made, obtain the retrieval type corresponding to the data retrieval request; if the retrieval type is a relational database retrieval, retrieve the relational database corresponding to the database hierarchy type according to the attribution information, and obtain the first target hierarchical data; if the retrieval type is an index database retrieval , then according to the data retrieval request and the ES index library corresponding to the attribution information search database level type, obtain the second target level data; return the first target level data or the second target level data to the client. Therefore, by dividing the retrieval type and database hierarchy type, on the basis of simplifying the retrieval structure and retrieval path, through the cooperation of database design and ES search library design, hierarchical tree data can be obtained through one SQL statement or several queries. Furthermore, the efficiency of the data retrieval process under the amount of hundreds of millions of users is more effectively improved.

Description of drawings

In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings that need to be used in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are only These are some embodiments of the present invention. For those skilled in the art, other drawings can also be obtained according to these drawings on the premise of not paying creative efforts.

FIG. 1 is a flow chart of the steps of a hierarchical data retrieval method provided by Embodiment 1 of the present invention;

FIG. 2 is a flow chart of steps of a hierarchical data retrieval method provided by Embodiment 2 of the present invention;

FIG. 3 is a flow chart of steps for an incremental synchronization process of hierarchical data provided by an embodiment of the present invention;

Fig. 4 is a flow chart of the steps of the retrieval query process of a kind of ES index library provided by the embodiment of the present invention;

FIG. 5 is a structural block diagram of a hierarchical data retrieval device provided by Embodiment 3 of the present invention.

Detailed ways

The embodiment of the present invention provides a hierarchical data retrieval method, device and equipment, which are used to solve the problem that the traditional hierarchical data retrieval method has low retrieval efficiency in the Internet business search process with hundreds of millions of users due to the limitation of its structure and retrieval path. Low, technical problems that cannot meet the needs of Internet business development.

In order to make the purpose, features and advantages of the present invention more obvious and understandable, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the following The described embodiments are only some, not all, embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without making creative efforts belong to the protection scope of the present invention.

Please refer to FIG. 1 . FIG. 1 is a flowchart of steps of a hierarchical data retrieval method provided by Embodiment 1 of the present invention.

A hierarchical data retrieval method provided by the present invention includes:

Step 101, in response to a login request sent by any client, obtain a user ID corresponding to the client;

User ID refers to the client ID carried in the login request when any client sends a login request.

In the embodiment of the present invention, the device responds to the login request sent by any client, and performs login verification on the login request, for example, whether the client has the corresponding login authority by checking whether the client is registered, etc., if the login If the verification is passed, the user ID corresponding to the client is obtained from the login request, so as to obtain the data basis for the subsequent client to query hierarchical data.

Step 102, query the attribution information corresponding to the user ID from the preset database, and determine the database hierarchy type corresponding to the user ID;

The preset database refers to a relational database that stores information such as a client-organization association table, a hierarchical tree branch ID set, and the like.

Affiliation information refers to the information indicating the association between the user ID and multiple affiliation organizations, user types and branches of the affiliation tree, including but not limited to the ID of the first affiliation organization, the ID of the second affiliation organization, and the ID of the third affiliation organization , the ID of the fourth affiliation organization, the ID of the superior node, and other information.

After obtaining the user ID carried in the login request, the user ID can be used as a search word to query the corresponding attribution information in the preset database, and at the same time, according to the user ID combined with the login request, the hierarchical database to which the user ID belongs can be judged Database hierarchy type.

Step 103, when receiving the data retrieval request sent by the client, obtain the retrieval type corresponding to the data retrieval request;

After determining the database level type corresponding to the user ID, if the data retrieval request sent by the client is received, the data retrieval request can be further responded to, and the corresponding retrieval type can be obtained to determine the retrieval method required by the data retrieval request. For example, relational database retrieval or indexed database retrieval, etc.

Step 104, if the search type is relational database search, search the relational database corresponding to the database level type according to the attribution information, and obtain the first target level data;

A relational database refers to a database built on the basis of a relational database model. It uses concepts and methods such as set algebra to process data in the database. It is also a set of tables organized into a formal descriptive table. Essence is a special collection of data items loaded with data in these tables that can be accessed or recalled in many different ways without reorganizing the database tables. The definition of a relational database results in a table of metadata or in a formal description of tables, columns, scopes and constraints. Each table (sometimes called a relation) contains one or more data types represented by columns. Each row contains a unique data entity of the type defined by the column. When creating a relational database, you can define the range of possible values for a data column and further constraints that may be applied to that data value. The SQL language is the interface of standard users and applications to relational databases. Its advantage is that it is easy to expand, and after the initial database creation, a new data type can be added without modifying all existing application software. The mainstream relational databases include oracle, db2, sqlserver, sybase, mysql, etc.

If it is determined that the retrieval type is relational database retrieval, since the data request in the data retrieval request has been extracted in the form of attribution information, at this time, attribution information can be used as a keyword or keyword, and the retrieval method of relational database retrieval is in the The relational database corresponding to the database hierarchy type is retrieved to obtain the first target hierarchy data.

Step 105, if the retrieval type is index database retrieval, according to the data retrieval request and the ES index database corresponding to the attribution information retrieval database hierarchy type, the second target hierarchy data is obtained;

The ES index library refers to a distributed, highly scalable, high real-time search and data analysis engine. Using the concept of relational databases, ES indexes are equivalent to tables in relational databases. An index can contain multiple fields of different data types, but ES indexes cannot be associated with tables. It can easily enable large amounts of data to be searched, analyzed and explored. Taking full advantage of the horizontal scalability of Elasticsearch can make data more valuable in the production environment. The implementation principle of Elasticsearch is mainly divided into the following steps. First, the user submits the data to the Elasticsearch database, and then uses the word segmentation controller to segment the corresponding sentence, and stores its weight and word segmentation results into the data. When the user searches for data At that time, the results will be ranked and scored according to the weight, and then the returned results will be presented to the user.

If the retrieval type is judged to be indexed database retrieval, it indicates that the retrieval method at this time is fuzzy search or full-text search. In order to improve the efficiency of subsequent searches, compound keywords or key words can be formed based on the search conditions specified in the data retrieval request and attribution information. word, search the ES index database corresponding to the database hierarchy type, so as to obtain the second target hierarchy data.

Step 106, return the first target level data or the second target level data to the client.

After the first target level data or the second target level data is acquired, it is returned to the client for display, so that the user can check the level data.

In the embodiment of the present invention, by responding to a login request sent by any client, the user identifier corresponding to the client is obtained; the attribution information corresponding to the user identifier is queried from a preset database, and the database hierarchy type corresponding to the user identifier is determined; When receiving the data retrieval request sent by the client, obtain the retrieval type corresponding to the data retrieval request; if the retrieval type is a relational database retrieval, then retrieve the relational database corresponding to the database hierarchy type according to the attribution information, and obtain the first target level data; if the retrieval If the type is index database retrieval, then according to the data retrieval request and the ES index library corresponding to the attribution information retrieval database level type, obtain the second target level data; return the first target level data or the second target level data to the client. Therefore, by dividing the retrieval type and database hierarchy type, on the basis of simplifying the retrieval structure and retrieval path, through the cooperation of database design and ES search library design, hierarchical tree data can be obtained through one SQL statement or several queries. Furthermore, the efficiency of the data retrieval process under the amount of hundreds of millions of users is more effectively improved.

Please refer to FIG. 2 . FIG. 2 is a flowchart of steps of a hierarchical data retrieval method provided by Embodiment 2 of the present invention.

Step 201, in response to a login request sent by any client, obtain a user ID corresponding to the client;

In the embodiment of the present invention, the specific implementation process of step 201 is similar to step 101, and will not be repeated here.

Step 202, query the attribution information corresponding to the user ID from the preset database, and determine the database hierarchy type corresponding to the user ID;

Optionally, step 202 may include the following sub-steps:

Retrieve the preset database according to the user ID, obtain the attribution information corresponding to the user ID and cache it;

Determine whether the attribution information contains the superior node ID;

If not, determine that the database hierarchy type corresponding to the user identifier is a limited hierarchy type;

In the embodiment of the present invention, the attribution information may include various contents. In order to further improve the subsequent retrieval efficiency, after obtaining the user identification, the preset database may be queried according to the user identification to obtain the attribution information corresponding to the user identification and cache.

At the same time, analyze the attribution information to judge whether it contains the information of the upper-level node ID. If it does not contain the upper-level node ID, it indicates that the affiliation organization ID in the attribution information does not have an associated upper-level node ID. The database hierarchy type corresponding to the user ID is limited. At this time, it can be determined that the database hierarchy type corresponding to the user ID is a limited hierarchy type; if it contains a superior node ID, it indicates that the affiliation organization ID in the attribution information at this time has an associated superior Node ID and hierarchical data are stored in the form of node information and node association relationship. At this time, it can be determined that the database hierarchical type corresponding to the user ID is an unrestricted hierarchical type.

It is worth mentioning that the affiliation information may also include a user type, which is used to limit the type to which the user terminal belongs, for example, the user terminal belongs to a certain organization, and the embodiment of the present invention does not limit this, such as the user terminal being an independent organization.

Step 203, when receiving the data retrieval request sent by the client, obtain the retrieval type corresponding to the data retrieval request;

In the embodiment of the present invention, the specific implementation process of step 203 is similar to that of step 103, and will not be repeated here.

In a specific implementation, a relational database can be constructed in the following ways:

1) Relational database with limited hierarchical type

By establishing a flat hierarchical tree information table, an organization information table (saving organization id, name, address, contact information and other organization's own data), an organization and equipment association table, a user information table, a user and organization association table in the relational database, according to SQL The query condition indexes the table. Among them, the user information table is used to save the user terminal id, name, mobile phone number and other self-information. The field names of each node should be the abbreviation of the business entity name as much as possible, and the field names numbered in sequence should be used only in special cases. The user-institution association table is used to record the association relationship between the user and the institution to which it belongs.

Taking the hierarchical tree from province to street as an example, the flat hierarchical tree information table t_region_tree can be shown in Table 1 below:

字段含义field meaning	字段名field name	数据类型type of data	备注Remark
idid	idid	bigintbigint	主键，也是层级树分支idThe primary key is also the hierarchical tree branch id
省/直辖市State / Province	provinceprovince	varcharvarchar	the
省/直辖市idProvince/municipality id	province_idprovince_id	bigintbigint	the
市/辖区City area	citycity	varcharvarchar	the
市/辖区idcity/district id	city_idcity_id	bigintbigint	the
区/县District/County	countycounty	varcharvarchar	the
区/县iddistrict/county id	county_idcounty_id	bigintbigint	the
街道/镇street/town	streetstreet	varcharvarchar	the
街道/镇idstreet/town id	street_idstreet_id	bigintbigint	the

社区/村community/village	communitycommunity	varcharvarchar	the
社区/村idcommunity/village id	community_idcommunity_id	bigintbigint	the
底层村/小区Underground village/community	villagevillage	varcharvarchar	the
底层村/小区idUnderlying village/community id	village_idvillage_id	bigintbigint	the

Table 1

Among them, a table is used to record the complete branches from the top to the bottom of the hierarchical tree and the relationship information of each node in it. The order of the fields in the table from left to right corresponds to the actual order of the nodes from top to bottom of the hierarchical tree. The main Fields can include information such as the name of each node, node number, etc., and all records with the same root node constitute all branches of the root node tree. If you need to update the node name, take the street name as an example, execute update t_region_tree set street='new name' where street='old street name'. When deleting a node in the hierarchical tree, directly execute the update operation to clear the node field in the corresponding record, indicating that this node is ignored in the branch of the hierarchical tree to assemble the branch; the cleared node can also be written into a new node name through the update operation to make are added to the branches of the tree.

The equipment information table of the first institution may be shown in Table 2 below:

Table 2

As shown in Table 2, the main fields of the device information table of the first organization may include but not limited to ID of the first affiliated organization, device id, device name, IP address of the device, etc. It can also be a hierarchical tree branch id.

However, in the above example, the name of the province, city, county, and district as the node name changes very little. If the node name will change frequently, and the number of child nodes in a tree may be relatively large at the same time, in order to avoid the influence of the database update operation, you can also extract all the node names to create a node information table, the main fields include node id, node name, node Encoded so that only one record here needs to be updated each time the node name is updated. In the flat hierarchical tree information table, only the id or code of the node is saved for each hierarchical tree node, and the id and code are constructed by continuous self-incremental data. At this time, a record can store up to hundreds of hierarchical trees.

The structural advantages of the above Table 1 and Table 2 include: 1) meet the business needs of hierarchical tree management and storage; 2) can obtain all directory trees of an organization through a SQL query; 3) can obtain a node containing a node through a SQL query 4) Support user data permissions and data security, ensuring that a user can only see the data allowed by his permissions; 5) The impact of management operations such as adding, deleting, and modifying business data on other operations can be controlled within A limited range that does not grow larger as the amount of data increases.

2) Relational database of unrestricted hierarchical type

For a relational database of an unrestricted hierarchical type, the data that needs to be saved is generally large in scale. If the above-mentioned relational database of the restricted hierarchical type is used to save, it will easily lead to slow reading. At this time, the above-mentioned flat hierarchical tree information table can be split into The tree node information table t_node_link and the tree node association relationship table t_node_link are established at the same time as the second institutional equipment association information table.

Specifically, the tree node information table records each tree node ID, node name and other fields that can be added according to the business. The tree node association table can be shown in Table 3 below:

table 3

When t_node_link is used to save the node itself, high_id=node_id and distance are both 0; for the leaf node is_leaf is 1, otherwise it is 0. In addition, this table is also used to save the associated data between all non-leaf nodes and all their subordinate nodes, that is, the value of distance is at least 0.

With such a database design, not only can all the records of a tree be found at one time, but also the management and query of hierarchical tree data are not complicated. When adding or deleting nodes, only the changed position of the branch where the node is located and the records related to the subordinate nodes are affected. . t_node_link will not be affected when modifying node information. For example: when it is necessary to add (delete) or delete nodes, it can be easily realized through the high_id field, similar to adding or deleting nodes to a linked list, and adjusting the distance can easily calculate the relative value of the new level from the UI; to query a All nodes of each lower level of the node can use SQL: select*from t_node_link l, t_node n where n.name='the name of the node to be queried'and n.id=l.high_id and l.distance! =0, where the node id can also be replaced by the node number; to query all directly subordinate nodes of any node, you can use SQL: select*from t_node_link l, t_node n where n.name='node name to be queried'and n. id＝l.node_id and l.distance＝1; to query all the superior nodes of any node, you can use SQL: select*from t_node_link l, t_node n where n.name＝'the name of the node to be queried'and n.id＝ l.node_id and l.distance! = 0, through the node_id of the node table = the node_id and distance of the inter-node association table! =0; according to is_root=1, it is convenient to query the root nodes of all trees.

Step 204, if the search type is relational database search, search the relational database corresponding to the database level type according to the attribution information, and obtain the first target level data;

In an example of the present invention, the affiliation information includes a first affiliation organization ID, and the relational database includes a plane hierarchical tree information table and a first organization equipment association information table; when the database hierarchy type is a limited hierarchy type, step 204 may include the following subsections step:

If the retrieval type is a relational database retrieval, query the equipment information table of the first organization according to the ID of the first affiliation organization, and determine the corresponding equipment data and the branch ID of the hierarchical tree;

Query the plane hierarchical tree information table according to the hierarchical tree branch ID, and determine the corresponding hierarchical tree branch data;

An association between branch data of each hierarchical tree and device data is established to obtain the first target hierarchical data.

In this embodiment, when it is determined that the database hierarchy type is a limited hierarchy type and the retrieval type is a relational database retrieval, it means that the data to be retrieved at this time is a relational database of a limited hierarchy type. At this time, the first Affiliation organization ID, query the first organization equipment information table according to the first affiliation organization ID, determine the equipment data corresponding to the first affiliation organization ID and the corresponding hierarchical tree branch ID, and then query the plane hierarchical tree information table according to the hierarchical tree branch ID , determine the corresponding hierarchical tree branch data, that is, the above-mentioned entire hierarchical tree branch from the province to the bottom village, and finally establish the association between each hierarchical tree branch data and equipment data to obtain the first target hierarchical data.

In the specific implementation, you can also query by constructing SQL statements, where the SQL statements are: select t.*, d.device_id, d.device_name from t_org_device d, t_tree t where d.org_id=user_org_id and d.branch_id= t.id, where user_org_id is the first affiliation organization ID, branch_id is the hierarchical tree branch ID, and t.id is the hierarchical tree branch data.

After obtaining the branch data of the hierarchical tree, you can put the branch data of the hierarchical tree, all the device data under the branch and the corresponding branch ID of the hierarchical tree into a record, and the device data can be used as a collection type field to form the branch data of the branch ID of the hierarchical tree A property of an object.

In another example of the present invention, the affiliation information includes a second affiliation organization ID and a superior node ID, and the relational database includes a tree node information table, a tree node association relationship table, and a second organization equipment association information table; when the database hierarchy type is non- When defining the level type, step 204 may include the following sub-steps:

If the retrieval type is a relational database retrieval, query the equipment information table of the second organization according to the ID of the second affiliation organization, and determine the corresponding equipment data, upper node ID and hierarchical tree node ID;

Use the superior node ID and the hierarchical tree node ID to retrieve the tree node information table, and determine all associated tree node IDs;

Retrieve the tree node association relationship table according to each tree node ID, and obtain the first node association relationship corresponding to each tree node ID;

The tree nodes corresponding to each tree node ID are associated according to the first node association relationship, and the association between the superior node ID and the device data is established to obtain the first target level data.

In this embodiment, when it is determined that the database hierarchy type is an unrestricted hierarchy type and the retrieval type is a relational database retrieval, it means that the relational database to be retrieved at this time is an unrestricted hierarchy type, and the second affiliation organization ID can be queried. Organization equipment information table, determine the equipment data associated with the second affiliation organization ID, superior node ID and hierarchical tree node ID; then use the superior node ID and hierarchical tree node ID to retrieve the tree node information table to determine the hierarchical tree node ID All the tree node IDs of the entire hierarchical tree where the corresponding tree nodes are located, and then respectively retrieve the tree node association relationship table according to each tree node ID, to determine the first node association relationship between the tree nodes corresponding to each tree node ID, according to the The first node association relationship associates the tree nodes corresponding to each tree node ID and establishes an association between the superior node ID and the device data to obtain the first target hierarchical data.

Optionally, the method may also include the following steps:

Execute the preset configuration file according to the preset update cycle, perform field conversion on the relational data in the relational database, and obtain ES index fields;

Synchronize ES index fields to ES index library.

In the specific implementation, in order to ensure the stability and accuracy of the index library, the ES index library is usually built after establishing a certain scale relational database and running for a period of time. At the same time, in order to realize the periodic update of the ES index library, you can Execute the preset configuration file according to the preset update cycle, perform field conversion on the relational data in the relational database to obtain the corresponding ES index field, and then synchronize the ES index field to the ES index library.

Taking the synchronization of the above-mentioned hierarchical tree information table t_plain_tree as an example, the data synchronization of the ES index library can be realized through the following steps S11-S15 or steps S21-S26:

S11. Install related plug-ins such as logstash-integration-jdbc. Refer to the official document to configure the database connection and SQL for obtaining data: SELECT distinct p.*from t_plain_tree p, t_org_device d WHERE p.id=d.branch_id and p.id＞=:sql_last_value. More requirements can be realized by adjusting the SQL here;

S12. Edit the logstash configuration file to use the configuration items tracking_column=>id, record_last_run=>true, clean_run=>false to track and record the id field in pages, use the above SQL to query the data from MySQL and save it to ES. Use the plug-ins logstash-filter-mutate and logstash-filter-ruby to add, remove, split fields or change the value of the field, and use logstash-filter-geoip to parse it from the IP address database GeoLite2-City.mmdb according to the device IP The longitude and latitude corresponding to the device location, the names of provinces, cities and counties, and generate the corresponding fields in ES. In this case, the Chinese region name needs to be modified in ib/logstash/filters/geoip.rb under the logstash installation directory for each obtained region name Continue to call get("zh-CN"), because the IP address information here is saved in a csv file, so you can also maintain your own address library, and you can also call the IP analysis service through the ruby code in geoip.rb to obtain relevant Chinese area information ;

S13. Add the Chinese region information and latitude and longitude information to the fields device_location and device_pos of the ES index through the logstash-filter-mutate plug-in;

S14. Continue to edit the logstash configuration file, and use logstash-output-elasticsearch to write the above processing results to ES;

S15. Use the above configuration file to start logstash, run until the full amount of data migration is completed, and stop; here you can also use the schedule setting in the logstash configuration file to periodically synchronize data from MySQL to ES.

In another example, the open source data synchronization tool canal can be well decoupled from the business system because it directly operates MySQL's binlog, and its operation puts very little pressure on MySQL. However, when a hierarchical tree branch has not been fully formed Synchronizing it to ES will only increase useless ES update operations. Due to differences in business rules, it is common for business entity data in different states not to be fully synchronized. In addition, data processing and verification may require querying other Database or ES, so you need to control the incremental synchronization of data through the canal client. For details, please refer to Figure 3. The steps are as follows:

Create a canal listening client by implementing the canal interface InitializingBean and ParseCanal, parse the data changes, and obtain the changed database tables and data; when the data in the tables t_plain_tree and t_node changes, check whether there is any changed data in the table t_org_device If the device association record corresponding to the id does not exist, the corresponding node data will not be synchronized, otherwise the synchronization will continue; before formally synchronizing the data, first obtain the entity class corresponding to the changed data through reflection; when it is necessary to synchronize the data of t_plain_tree and t_node to In ES, if it is a new record, write the new record directly in the corresponding ES index, if it is an update operation, directly update the corresponding record in the ES index; if it is a delete operation, first delete the id corresponding to the delete operation in the organization equipment index record, and then delete the data in the corresponding index of the tables t_plain_tree and t_node; if the institutional device association table changes, for the new data in the table t_org_device, directly write the new record into the corresponding ES index; for this table The update and delete data of the database will update or delete the corresponding record in the ES index according to its id; if more data needs to be synchronized, it will continue to monitor whether data changes occur.

In the specific implementation, after the ES index library synchronizes the relational data, in order to facilitate the subsequent data retrieval, the table can be constructed in the following ways:

1) ES index library with limited hierarchical type

For the ES index library of unrestricted hierarchical type, it may include but not limited to node information index table, flat hierarchical tree index table and first organization device association index table.

Specifically, the flat hierarchical tree index table may be shown in Table 4 below:

Table 4

It is worth mentioning that in the above Table 4, fields that need to be searched for business can also be added to the index, such as the geographic location of the device, device name, device label, device capability, etc.

The first agency equipment association index table may be shown in Table 5 below:

table 5

It is worth mentioning that, based on the above-mentioned database and ES index design, two supporting measures are provided in the R&D implementation. On the one hand, it simplifies the development based on the Java language, minimizes the amount of code that must be developed, and improves the performance of search operations. Spring Data Elasticsearch inherits the advantages of Spring Data, including the fuzzy query and search of data in ES without writing implementation code and only needing interfaces and @Repository and @Query annotations. However, if its default findAll() method is used in production When there is a large amount of data in the environment, it is frequently called to view all the data, which may lead to unstable services; in addition, the content of the @Query annotation is written in the same way as the painless script of ES, which is not easy to understand and remember, and is far inferior to SQL query syntax in terms of ease of use , does not yet support controlling which fields in the index are returned in query results, the existing implementation actually returns all fields.

To solve the first problem, since the specific implementation code of the default findAll() method is in the class SimpleElasticsearchRepository, and by default in META-INF, it is specified in spring.factories that the ElasticsearchRepositoryFactory controls which class provides the implementation class of the ES query method. Therefore, you can inherit the SimpleElasticsearchRepository class, rewrite the findAll() method, add warn or error level alarm output, and add @Deprecated annotation to this method, then inherit ElasticsearchRepositoryFactory and call the newly developed SimpleElasticsearchRepository subclass, and Specify a subclass of ElasticsearchRepositoryFactory in spring.factories as RepositoryFactorySupport.

In order to solve the second problem, special query annotations can be encapsulated for specific types of queries in ES. For example, for fuzzy queries, a new annotation FuzzyQuery and its processing flow can be encapsulated according to the implementation and processing flow of Query annotations, specifying the query that needs to be queried The field, the identification of the corresponding parameter value, and the logical relationship between the fields when assembling are processed by logical AND by default, and the parameters in the method where the FuzzyQuery annotation of the Repository interface is directly replaced in order at runtime; the specified return field list or For the list of fields to be excluded, directly generate the corresponding content through string processing in the painless script of the query request, and keep the same effect as the Query annotation in other respects. Then in the class ElasticsearchQueryMethod and ElasticsearchRepositoryFactory, analyze and annotate the content of FuzzyQuery according to the above-mentioned new functions and generate the corresponding painless script content, so as to avoid inputting {\″wildcard\″:{, \″bool\″: or {\″must \″: Such content can also avoid the troubles caused by many {, }, [,] symbols. Finally, after repackaging and generating the jar, replace the original jar file for the business system.

2) ES index library of unrestricted hierarchical type

Specifically, the specific construction of the flat hierarchical tree index table is similar to the above table 4, and the node information index table can be shown in the following table 6:

Table 6

The second agency equipment association index table may be shown in Table 7 below:

Table 7

It is worth mentioning that in the actual search, the supplyAsync() method of Java's CompletableFuture simultaneously initiates search requests to the above two indexes, and then summarizes all asynchronous requests through the allOf() method. Finally, the entire time-consuming is slightly higher than The longest of the two requests. When there is no need to return the result data in the form of a hierarchical tree, you can directly return the search results here. Otherwise, you can query all the corresponding nodes from the association information table between the nodes in the hierarchical tree through the Java code and then according to the node information obtained here. The branch data of the tree can then be assembled into a complete hierarchical tree data and returned.

Step 205, if the retrieval type is index database retrieval, according to the data retrieval request and the ES index database corresponding to the attribution information retrieval database hierarchy type, the second target hierarchy data is obtained;

In an example of the present invention, the affiliation information includes a third organization affiliation ID; the ES index library includes a flat hierarchical tree index table and a first organization equipment association index table; when the database hierarchy type is a limited hierarchy type, step 205 may include the following Substeps:

If the retrieval type is indexed database retrieval, the data retrieval request is parsed to obtain the search condition and the target data format;

According to the attribution ID of the third organization, retrieve the organization's equipment association index table, and determine the corresponding equipment data;

Search the flat hierarchical tree index table according to the search conditions to determine the corresponding hierarchical tree information;

The association between the hierarchical tree information and the equipment data is established and converted into the target data format to obtain the second target hierarchical data.

Search criteria refer to data restriction information that may not be exactly the same as the fields in the ES index library but is related. The target data format refers to the hierarchical tree data required by the client or the unique data format of the client.

In the embodiment of the present invention, when it is determined that the database hierarchy type is a limited hierarchy type, if index database retrieval is required, the data retrieval request may be parsed to obtain the search conditions and the target data format. According to the search conditions and the affiliation ID of the third organization, asynchronously search from the organization equipment management index table and the flat hierarchical tree index table to determine the equipment data corresponding to the third organization affiliation ID and the hierarchical tree information that meets the search conditions; and then establish a hierarchy The association between the tree information and the device data is converted into the target data format to obtain the second target level data.

In specific implementation, with reference to FIG. 4, the retrieval query of the ES index library can be realized through the following steps S31-S34:

S31. The user obtains the attribution ID of the third organization from the cache, and if not found, first queries the relational database MySQL, updates the cache, and then continues;

S32. Asynchronously search for eligible data from the institutional device management index table and the flat hierarchical tree index table according to the third organization's affiliation ID and search conditions, and obtain hierarchical tree information and device data that meet the search conditions as search results;

Optionally, in the above S32, if the node information of the tree and the relationship between the nodes of the plane hierarchical tree are respectively stored in different indexes, then asynchronously and simultaneously Search the institutional equipment management index table and the flat hierarchical tree index table to obtain the search results. Next, if you do not need to assemble the search results into tree data, you can directly return the results requested by the client, otherwise you can query MySQL again through the cache to obtain the tree node relationship data, and finally the application service assembles the result data into the required data from the client. returned after the data.

S33, if the number of records of the search result exceeds the specified threshold, the result of the first page (how many records are allowed to be configured on each page) and the total data volume of the result set are returned to the client;

S34. After the above-mentioned search results are organized into fields and data formats required by the client through the Java code in the memory, the second target level data is obtained and returned to the client;

In another example of the present invention, the affiliation information includes a fourth organization affiliation ID, and the ES index library includes a tree node information index table and a second organization equipment association index table; when the database hierarchy type is an unrestricted hierarchy type, step 205 can be Include the following sub-steps:

If the retrieval type is index database retrieval, then parse the data retrieval request to obtain the search conditions;

Retrieve the equipment association index table of the second organization according to the belonging ID of the fourth organization, and determine the corresponding equipment data and the branch ID of the hierarchical tree to which it belongs;

Search the tree node information index table according to the search conditions to obtain multiple hierarchical tree node IDs that meet the search conditions;

According to each hierarchical tree node ID, respectively retrieve the second mechanism equipment association index table, and determine the second node association relationship corresponding to each hierarchical tree node ID;

The tree nodes corresponding to the tree node IDs of each hierarchy are associated according to the second node association relationship, and the association with the device data is established to obtain the second target hierarchy data.

In the embodiment of the present invention, if the retrieval type is index database retrieval and the index database is an unrestricted hierarchical type, the data retrieval request is parsed to obtain the search conditions; the second organization's equipment association index table is retrieved according to the fourth organization's attribution ID, and the corresponding Device data and the branch ID of the hierarchical tree to which it belongs; search the tree node information index table according to the search conditions, and obtain multiple hierarchical tree node IDs that meet the search conditions; search the second organization's equipment association index table according to each hierarchical tree node ID to determine each level The second node association relationship corresponding to the tree node ID; associate the tree nodes corresponding to the tree node IDs of each hierarchy according to the second node association relationship, and establish an association with the device data to obtain the second target hierarchy data.

Step 206, return the first target level data or the second target level data to the client.

In the embodiment of the present invention, the specific implementation process of step 206 is similar to that of step 106, and will not be repeated here.

Step 207, when receiving the device reallocation request sent by the additional client, create an additional user identifier corresponding to the additional client;

Step 208, determine the corresponding additional device data according to the device reallocation request;

Step 209, establishing the association between the additional user identifier and the additional device data, obtaining an additional ES index table and saving it to the ES index library.

In a specific implementation, the client is usually under a certain organization, and the same organization can include multiple client terminals at the same time, but due to business requirements, it may be necessary to allocate equipment for a single user. In order to solve this technical problem, when receiving When the device reallocation request is sent by the additional client, create an additional user ID corresponding to the additional client, and determine the additional device data required by the additional client according to the device reallocation request, such as device name, device ID, and device location , device label, device capability, etc., and then establish the association between the additional user identifier and the additional device data to obtain the additional ES index table. To facilitate subsequent retrieval, the additional ES index table can be saved to the ES index library.

In an example of the present invention, Table 8 shows an additional ES index table of the embodiment of the present invention:

Table 8

In another example of the present invention, in order to distinguish such users and deal with them specially, in the application system design, it is possible to create a special user attribute for this kind of users or create a special user attribute to indicate whether they have been assigned separately equipment. If the user is controlled by role, before assigning equipment to the user, check whether the user is the required role, and the equipment can only be allocated to this type of user after the check is passed; The transaction wraps the operation of assigning equipment to users and setting this attribute value, so that after successfully saving the equipment assigned to the user in the database, the corresponding attribute value is always successfully set for the corresponding user. According to which of the above options is selected, the user's role or the above attribute data of the user are respectively extracted from the database when the user logs in, and then cached in the Redis cluster. When the subsequent user initiates a search request, it can be judged based on the value of the cached data that the corresponding Users search this part of the index, so as to realize the control of user data permissions synchronously.

It is worth mentioning that there may still be devices going offline at each tree node in the hierarchical tree. At this time, the device data can be updated in the following ways: There are two main technical methods for the perception of device going offline events: Based on the establishment and disconnection of the socket through the TCP/IP communication connection, based on the reported data of the device heartbeat, the latter will receive the heartbeat data of the device regularly and if it has not been received after a certain period of time, it will be considered that the device has been offline. Both require a dedicated device access service, which can receive device communication requests through the MQTT protocol, a custom protocol based on Netty, or even a device heartbeat request of the HTTP protocol.

The details of the real-time calculation scheme for the number of online devices include:

In order to calculate the online number of the subordinate devices of the hierarchical tree node in real time, whenever the online status of the device changes, the device access service instance can first save the device id (continuously increasing number) and its status in the memory through ConcurrentHashMap, and then confirm the device When the connection is established or disconnected, or when the device heartbeat request is received, or the device request is not received after a timeout, set the device online status to true and false respectively. When multiple devices have status changes at the same time, directly call ConcurrentHashMap concurrently through multiple threads The put() method can modify the state of the corresponding device id. Then, the application service instance in the device access service cluster regularly refreshes these data to the multi-master and multi-slave Redis cluster at regular intervals (the interval is allowed to be configured), and Redis saves the online status of the device through its bitset, and the master instance of each Redis. When the maximum number of levels in the hierarchical tree is fixed, the key of the bitset is the id of the branch of the hierarchical tree; when the maximum number of levels is not limited, the key is the id of the device next to the upper node. If the id changes, the key cached here needs to be updated synchronously. The serial number of each bit in the bitset is equal to the corresponding device id, and the value of this bit indicates that the device is offline or online respectively through 0 and 1. Through the SETBIT, GETBIT, and BITCOUNT commands of Redis, you can easily set and obtain the online status of the device id corresponding to the bit of a serial number, and the total number of online devices under a key. If the device IDs under a certain node are generally greater than a number, you can subtract a number from all device IDs when operating Redis, such as the smallest device ID among them, which helps to save memory usage. When it is necessary to obtain the number of online devices of each node of the hierarchical tree, a group of tree branch ids or device IDs of adjacent nodes can be sent in batches through the pipeline mechanism of Redis to obtain the total number of online devices of a key through BITCOUNT, and then the application The service assembles the resulting data and returns it. If the user can only access some devices under one node, the Redis pipeline mechanism can be used to send batches of commands to get the online status of a device id through GETBIT, and the device corresponding to the table name of 1 is returned online, and then accumulated in the application service memory The total number of online devices is returned to the client. The total number of devices of each node can be queried in the database and cached in the Redis cluster for subsequent query of the total number of devices under the node to be obtained directly from the cache. In Redis, the id of the tree branch or node can be used as the key through hash, and the total number of devices can be stored as an attribute. When a device is added, deleted or moved, the hash value of the corresponding key can be added or subtracted. , when the total device data of any key in Redis is found to be empty, the data is loaded from the data source and cached in the hash of Redis. If the implementation effect of the user-side hierarchical tree is to open the hierarchical tree step by step, and only open the first-level nodes at the beginning by default, then the implementation is simpler: as long as the user double-clicks to open a node, directly open the next-level tree node, When the last level of tree node is to be opened, the number of online devices can be obtained directly from Redis through the BITCOUNT command according to the id of this node. If the user can only access some of the devices, obtain the online status of each device from Redis in batches through the pipeline and accumulate them in memory. The result of accumulating the devices owned by the user is the total number of the current node. Number of online devices. Since the whole process is mainly carried out in memory, and the good scalability and availability of Redis multi-resident multi-slave cluster, the calculation of the number of online devices can not only be performed in real time, but also have better performance.

Please refer to FIG. 5 . FIG. 5 is a structural block diagram of a hierarchical data retrieval device provided by Embodiment 3 of the present invention.

An embodiment of the present invention provides a hierarchical data retrieval device, including:

The user identification acquisition module 501 is configured to obtain a user identification corresponding to the user terminal in response to a login request sent by any user terminal;

A database hierarchy type determining module 502, configured to query the attribution information corresponding to the user identifier from a preset database, and determine the database hierarchy type corresponding to the user identifier;

A retrieval type obtaining module 503, configured to obtain the retrieval type corresponding to the data retrieval request when receiving the data retrieval request sent by the client;

A relational data retrieval module 504, configured to retrieve the relational database corresponding to the database hierarchy type according to the attribution information if the retrieval type is a relational database retrieval, to obtain the first target hierarchical data;

The index data retrieval module 505 is used for if the retrieval type is an index database retrieval, then according to the data retrieval request and the ES index library corresponding to the attribution information retrieval database hierarchy type, obtain the second target hierarchy data;

Hierarchical data return module 506, used to return the first target hierarchical data or the second target hierarchical data to the client.

Optionally, the database hierarchy type determination module 502 is specifically used for:

Determine whether the attribution information contains the superior node ID;

Optionally, the affiliation information includes the first affiliation organization ID, and the relational database includes a plane hierarchical tree information table and a first organization equipment association information table; when the database hierarchy type is a limited hierarchy type, the relational data retrieval module 504 is specifically used for:

Optionally, the affiliation information includes a second affiliation organization ID and a superior node ID, and the relational database includes a tree node information table, a tree node association relationship table and a second organization equipment association information table; when the database hierarchy type is an unrestricted hierarchy type, The relational data retrieval module 504 is specifically used for:

Optionally, the affiliation information includes a third organization affiliation ID; the ES index storehouse includes a flat hierarchical tree index table and a first organization equipment association index table; when the database hierarchy type is a limited hierarchy type, the index data retrieval module 505 includes:

Optionally, the affiliation information includes a fourth organization affiliation ID, and the ES index library includes a tree node information index table and a second organization equipment association index table; when the database hierarchy type is an unrestricted hierarchy type, the index data retrieval module 505 includes:

Optionally, the device also includes:

A user identification creation module, configured to create an additional user identification corresponding to the additional user end when receiving a device reallocation request sent by the additional user end;

An additional equipment data determining module, configured to determine corresponding additional equipment data according to the equipment reallocation request;

The association establishing module is used to establish the association between the additional user identification and the additional device data, obtain the additional ES index table and save it to the ES index library.

Optionally, the device also includes:

The field conversion module is used to execute the preset configuration file according to the preset update cycle, perform field conversion on the relational data in the relational database, and obtain the ES index field;

The field synchronization module is used to synchronize the ES index fields to the ES index library.

An embodiment of the present invention also provides an electronic device, including a memory and a processor, and a computer program is stored in the memory. When the computer program is executed by the processor, the processor executes the hierarchical data retrieval method according to any embodiment of the present invention. step.

Those skilled in the art can clearly understand that for the convenience and brevity of the description, the specific working process of the above-described devices and modules can refer to the corresponding process in the foregoing method embodiments, which will not be repeated here.

In the several embodiments provided by the present invention, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only illustrative. For example, the division of the units is only a logical function division. In actual implementation, there may be other division methods. For example, multiple units or components can be combined or May be integrated into another system, or some features may be ignored, or not implemented. In another point, the mutual coupling or direct coupling or communication connection shown or discussed may be through some interfaces, and the indirect coupling or communication connection of devices or units may be in electrical, mechanical or other forms.

The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place, or may be distributed to multiple network units. Part or all of the units can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, each unit may exist separately physically, or two or more units may be integrated into one unit. The above-mentioned integrated units can be implemented in the form of hardware or in the form of software functional units.

If the integrated unit is realized in the form of a software function unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on such an understanding, the essence of the technical solution of the present invention or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium , including several instructions to make a computer device (which may be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage media include: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), magnetic disk or optical disc, etc., which can store program codes. .

As mentioned above, the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: it can still understand the foregoing The technical solutions recorded in each embodiment are modified, or some of the technical features are replaced equivalently; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the various embodiments of the present invention.

Claims

A hierarchical data retrieval method, characterized in that it includes:

In response to a login request sent by any client, obtain the user ID corresponding to the client;

Querying the attribution information corresponding to the user identifier from a preset database, and determining the database hierarchy type corresponding to the user identifier;

When receiving the data retrieval request sent by the client, obtain the retrieval type corresponding to the data retrieval request;

If the search type is a relational database search, then search the relational database corresponding to the database level type according to the attribution information to obtain the first target level data;

If the retrieval type is index database retrieval, then according to the data retrieval request and the attribution information, retrieve the ES index database corresponding to the database hierarchy type to obtain the second target hierarchy data;

returning the first target level data or the second target level data to the client.
The method according to claim 1, wherein the step of querying the attribution information corresponding to the user ID from a preset database and determining the database hierarchy type corresponding to the user ID includes:

Retrieving a preset database according to the user ID, obtaining attribution information corresponding to the user ID and caching it;

judging whether the attribution information includes a superior node ID;

If not, then determine that the database hierarchy type corresponding to the user identifier is a limited hierarchy type;

If yes, it is determined that the database hierarchy type corresponding to the user identifier is an unrestricted hierarchy type.
The method according to claim 2, wherein the affiliation information includes a first affiliation organization ID, and the relational database includes a plane hierarchical tree information table and a first organization equipment association information table; when the database hierarchy type is When defining the hierarchy type, if the retrieval type is a relational database retrieval, the step of retrieving the relational database corresponding to the database hierarchy type according to the attribution information to obtain the first target hierarchy data includes:

If the retrieval type is a relational database retrieval, then query the equipment information table of the first organization according to the ID of the first affiliation organization, and determine the corresponding equipment data and the branch ID of the hierarchical tree;

Querying the flat hierarchical tree information table according to the hierarchical tree branch ID to determine the corresponding hierarchical tree branch data;

An association between each branch data of the hierarchical tree and the device data is established to obtain first target hierarchical data.
The method according to claim 2, wherein the affiliation information includes a second affiliation organization ID and the superior node ID, and the relational database includes a tree node information table, a tree node association table and a second organization device Association information table; when the database hierarchy type is an unrestricted hierarchy type, if the retrieval type is a relational database retrieval, search the relational database corresponding to the database hierarchy type according to the attribution information, and obtain the first target Steps for hierarchical data, including:

If the retrieval type is a relational database retrieval, then query the equipment information table of the second organization according to the ID of the second affiliation organization, and determine the corresponding equipment data, the ID of the superior node and the node ID of the hierarchical tree;

Retrieving the tree node information table by using the superior node ID and the hierarchical tree node ID to determine all associated tree node IDs;

Retrieving the tree node association relationship table according to each of the tree node IDs to obtain the first node association relationship corresponding to each of the tree node IDs;

Associating the tree nodes corresponding to each of the tree node IDs according to the first node association relationship and establishing an association between the upper-level node ID and the device data to obtain first target hierarchical data.
The method according to claim 2, wherein the affiliation information includes the affiliation ID of the third organization; the ES index library includes a flat hierarchy tree index table and a first organization equipment association index table; when the database hierarchy type When the hierarchical type is defined, if the retrieval type is an index database retrieval, then according to the data retrieval request and the attribution information, search the ES index library corresponding to the database hierarchical type to obtain the second target hierarchical data ,include:

If the retrieval type is an index database retrieval, then parsing the data retrieval request to obtain a search condition and a target data format;

Retrieving the institutional equipment association index table according to the third institutional attribution ID to determine the corresponding equipment data;

Search the flat hierarchical tree index table according to the search condition, and determine the corresponding hierarchical tree information;

Establishing the association between the hierarchical tree information and the device data and converting it into the target data format to obtain second target hierarchical data.
The method according to claim 2, wherein the affiliation information includes a fourth organization affiliation ID, and the ES index library includes a tree node information index table and a second organization equipment association index table; when the database hierarchy type When it is an unrestricted hierarchy type, if the retrieval type is an index database retrieval, search the ES index library corresponding to the database hierarchy type according to the data retrieval request and the attribution information, and obtain the second target hierarchy data steps, including:

If the retrieval type is an index database retrieval, then parsing the data retrieval request to obtain a search condition;

Retrieving the second organization's equipment association index table according to the fourth organization's attribution ID, and determining the corresponding equipment data and the hierarchical tree branch ID to which it belongs;

Searching the tree node information index table according to the search condition to obtain a plurality of hierarchical tree node IDs meeting the search condition;

Retrieving the second organization equipment association index table according to each of the hierarchical tree node IDs, and determining the second node association relationship corresponding to each of the hierarchical tree node IDs;

The tree nodes corresponding to each of the hierarchical tree node IDs are associated according to the second node association relationship, and an association with the device data is established to obtain second target hierarchical data.
The method according to claim 1, further comprising:

When receiving the device reallocation request sent by the additional client, creating an additional user identifier corresponding to the additional client;

determining corresponding additional device data according to the device reallocation request;

An association between the additional user identifier and the additional device data is established, and an additional ES index table is obtained and stored in the ES index database.
The method according to any one of claims 1-7, further comprising:

Executing a preset configuration file according to a preset update cycle, performing field conversion on the relational data in the relational database, and obtaining ES index fields;

Synchronize the ES index field to the ES index library.
A hierarchical data retrieval device, characterized in that it includes:

A user identification acquisition module, configured to obtain a user identification corresponding to the user terminal in response to a login request sent by any user terminal;

A database hierarchy type determining module, configured to query the attribution information corresponding to the user identifier from a preset database, and determine the database hierarchy type corresponding to the user identifier;

A retrieval type acquisition module, configured to obtain the retrieval type corresponding to the data retrieval request when receiving the data retrieval request sent by the client;

A relational data retrieval module, configured to retrieve the relational database corresponding to the database hierarchy type according to the attribution information if the retrieval type is a relational database retrieval, to obtain the first target hierarchical data;

An index data retrieval module, configured to retrieve the ES index library corresponding to the database hierarchy type according to the data retrieval request and the attribution information if the retrieval type is index database retrieval, to obtain second target hierarchical data;

A hierarchical data returning module, configured to return the first target hierarchical data or the second target hierarchical data to the client.
An electronic device, characterized by comprising a memory and a processor, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the processor executes any one of claims 1-8. The steps of the hierarchical data retrieval method described in item .