A distributed unstructured database association query method based on big data
By using data table generation, monitoring, synchronization and sharding algorithms in distributed unstructured databases, the problems of traditional relational databases in massive data storage and query performance are solved, and structured and unstructured databases are realized read and write separation and efficient data processing.
Patent Information
- Application Number
- CN202010738074.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2020-07-28
- Publication Date
- 2024-12-27
- Estimated Expiration
- 2040-07-28
AI Technical Summary
When traditional relational databases face the rapidly growing data volume, the query performance bottleneck is serious, making it difficult to solve the problem of massive data storage, and the write performance is affected, making it difficult to achieve read and write separation.
Using a distributed unstructured database association query method based on big data, the data table generation, monitoring, synchronization, ETL processing and sharding algorithm can be used to realize the read and write separation between structured database and unstructured database, and use Ogg and kafka to implement it. Data is synchronized and cleaned in real time, select appropriate sharding algorithms and index optimization, and optimize query performance.
It realizes the read and write separation between structured databases and unstructured databases, improves the writing and query performance, solves the problem of massive data storage, reduces the coupling degree of the transaction system, and can process and aggregate data in real time, improving the value of data Dimension.
Smart Images
Figure CN111930817B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing technology, and more specifically, to a distributed unstructured database association query method based on big data. Background Art
[0002] A relational database is a database built on the basis of a relational model. It uses mathematical concepts and methods such as set algebra to process data in the database. Various entities in the real world and various connections between entities are represented by relational models. The standard data query language SQL is a language based on relational databases. This language performs retrieval and operations on data in relational databases. The relational model consists of three parts: relational data structure, relational operation set, and relational integrity constraints.
[0003] Traditional relational databases involve queries on associations between tables, which can be implemented through views. However, with the rapid growth of business, the bottlenecks of relational databases have gradually become prominent, such as: (1) the amount of data has increased dramatically, and query performance bottlenecks have become serious; (2) the query bottleneck of traditional relational databases affects the write performance during the transaction process, and it is urgent to consider read-write separation; (3) traditional relational databases are difficult to solve the problem of storing massive data. Summary of the invention
[0004] In order to overcome the shortcomings of the prior art, the present invention provides a distributed unstructured database association query method based on big data, which can realize the read-write separation of structured database and unstructured database and solve the problem of massive data storage.
[0005] The technical solution adopted by the present invention to solve the technical problem is: a distributed unstructured database association query method based on big data, the improvement of which is that the method includes the following steps:
[0006] S1. Generate data tables. Combined with business requirements, use mapping to design data table instances that associate multiple tables to obtain multiple data tables.
[0007] S2. Data table monitoring: monitor the changes of each data table through Ogg to adapt to the real-time changes of business data;
[0008] S3, data synchronization, using the Ogg synchronization principle to achieve real-time synchronization of incremental data in multiple tables between the Oracle database and the Kafka cluster;
[0009] S4, ETL processing and data aggregation, through the Kafka cluster, clean and pre-process the data that has been synchronized in step S3, and store it in an unstructured database after aggregation;
[0010] S5. Selection of sharding algorithm: When the data volume reaches the Tb level, the sharding algorithm is designed in combination with the query scenario to achieve distributed massive storage of data.
[0011] Furthermore, in the step S1, the business requirements include but are not limited to scan code pre-authorization, aggregation code, direct connection and indirect connection services in the payment field.
[0012] Furthermore, in step S1:
[0013] When mapping and designing table instances for multi-table associations, it is necessary to design the landing rules from the source end to the target end to implement the prototype of the unstructured database association query table.
[0014] Furthermore, the implementation rules include but are not limited to the design of data partition keys of the distributed database and the pre-setting of frequently queried indexes.
[0015] Furthermore, in step S5, the query scenarios include but are not limited to:
[0016] The first scenario is a scenario where data is frequently written and read less frequently. Specifically, more than 10 million records are written and 10,000 to 20,000 records are read.
[0017] The second scenario is a scenario where data is frequently read and written less frequently. Specifically, there are 10,000 to 20,000 data items written and more than 10 million data items read.
[0018] Furthermore, for the first scenario, a hash sharding algorithm is used to distribute the specified key hash on different cluster nodes, balancing the data when it lands, thereby improving write performance.
[0019] Furthermore, for the second scenario, the range sharding algorithm is used to improve the performance of scanning and sorting data.
[0020] Furthermore, after step S5, the following steps are also included:
[0021] S6. According to the query scenario and the advantages of Nosq1 distributed storage, optimize the query index and optimize the data block distribution to solve the query performance bottleneck.
[0022] Furthermore, the method of optimizing the query index includes: each query needs to create a corresponding index.
[0023] Furthermore, the method of optimizing the query index includes: the design of a single index needs to satisfy multiple data queries.
[0024] Furthermore, the method of optimizing the query index includes: selecting and ordering index fields, taking into account query coverage and selectivity.
[0025] Furthermore, the method of optimizing the query index includes: creating indexes on fields that are updated frequently should be done with caution.
[0026] Furthermore, the method of optimizing the query index includes: for the array index, the number of future elements needs to be carefully considered.
[0027] Furthermore, the method of optimizing the query index includes: using B-tree index with caution on super-long string type fields.
[0028] Furthermore, the method of optimizing the query index includes: it is not advisable to create too many indexes on a single collection with high concurrent updates, wherein high concurrent updates refer to more than 10 million data being updated simultaneously.
[0029] Furthermore, the optimized data block distribution includes:
[0030] By setting the data sharding key, the data sharding key is set according to actual business needs.
[0031] Furthermore, in step S4, the step of cleaning the data includes a process of reviewing and verifying the data.
[0032] Furthermore, in step S4, the step of preprocessing the data includes data review, data screening and data sorting.
[0033] The beneficial effects of the present invention are: it can realize the separation of reading and writing of structured database and unstructured database, and in the transaction system, only the writing function of the structured database needs to be paid attention to, and the unstructured database undertakes the reading function; the use of distributed unstructured database can solve the problem of real-time storage of massive data. BRIEF DESCRIPTION OF THE DRAWINGS
[0034] Figure 1 The present invention is a flowchart of a distributed unstructured database association query method based on big data.
[0035] Figure 2 This is a structural principle diagram of a distributed unstructured database association query method based on big data of the present invention. DETAILED DESCRIPTION
[0036] The present invention is further described below in conjunction with the accompanying drawings and embodiments.
[0037] The following will clearly and completely describe the concept, specific structure and technical effects of the present invention in combination with the embodiments and drawings, so as to fully understand the purpose, characteristics and effects of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, other embodiments obtained by technicians in this field without creative work are all within the scope of protection of the present invention. In addition, all the connection / connection relationships involved in the patent do not refer to the direct connection of components, but refer to the formation of a better connection structure by adding or reducing connection accessories according to the specific implementation situation. The various technical features in the invention can be combined interchangeably without conflicting with each other.
[0038] The present invention discloses a distributed unstructured database association query method based on big data, through which the read-write separation of structured database and unstructured database is realized, and in the transaction system, only the write function of the structured database needs to be paid attention to, and the unstructured database undertakes the read function, thereby eliminating the coupling degree between transaction writing and query. Specifically.
[0039] Reference Figure 1 , Figure 2 As shown, a distributed unstructured database association query method based on big data, specifically, the method includes the following steps:
[0040] S1. Generate data tables. Combined with business requirements, use mapping to design data table instances that associate multiple tables to obtain multiple data tables.
[0041] In this embodiment, the business needs include but are not limited to scan code pre-authorization, aggregation code, direct connection and indirect connection business in the payment field. This business need is a common business need in the prior art and will not be described in detail in this embodiment. Furthermore, when mapping a table instance with multiple table associations, it is necessary to design the landing rules from the source end to the target end to realize the implementation prototype of the unstructured database association query table; the landing rules need to be set. In this embodiment, the landing rules are the design of the data sharding key of the distributed database and the pre-setting of the frequently queried index. It can be understood that in practice, the landing rules can be manually regulated;
[0042] S2. Data table monitoring: modify the data table instances of each Ogg data table to adapt to the real-time changes in business data, and monitor the changes in data in the Oracle database in real time;
[0043] Ogg is the abbreviation of Oracle GoldenGate, which is a log-based structured data replication software that can realize real-time capture, transformation and delivery of large amounts of transaction data, realize data synchronization between the source database and the target database, and maintain sub-second data latency. In this embodiment, since Oracle GoldenGate is a very mature technology in the prior art, it will not be described in detail in this embodiment.
[0044] S3, data synchronization, using the Ogg synchronization principle to achieve real-time synchronization of incremental data in multiple tables between the Oracle database and the Kafka cluster;
[0045] S4, ETL processing and data aggregation, through the Kafka cluster, clean and pre-process the data that has been synchronized in step S3, and store it in an unstructured database after aggregation;
[0046] In this embodiment, data is cleaned, i.e., ETL processing, and then lightly aggregated, which can improve the data value dimension; in the step S4, the step of cleaning the data includes the process of reviewing and verifying the data, the purpose of which is to delete duplicate information and correct existing errors to improve data consistency; the step of preprocessing the data includes data review, data screening, and data sorting;
[0047] S5. Selection of sharding algorithm: When the data volume reaches the terabyte level, a sharding algorithm is designed based on query scenarios to achieve distributed massive data storage.
[0048] In this solution, the above query scenarios include but are not limited to the first scenario and the second scenario.
[0049] The first scenario is a scenario where data is written frequently and read less frequently. Specifically, there are more than 10 million records written and 10,000 to 20,000 records read. In this case, the hash sharding algorithm is used to distribute the specified key hash on different cluster nodes, so that the data is balanced when it lands, improving the write performance.
[0050] The second scenario is a scenario where data is frequently read and written less frequently. Specifically, there are 10,000 to 20,000 data records written and more than 10 million data records read. In this case, the range sharding algorithm is used to improve the performance of scanning and sorting data, and achieve better query performance.
[0051] S6. According to the query scenario and the advantages of Nosq1 distributed storage, optimize the query index and optimize the data block distribution to solve the query performance bottleneck;
[0052] In this embodiment, the query index optimization is to create a corresponding index for each query; at the same time, the scheme for optimizing the data block distribution is to set a data sharding key, which is set according to actual business needs. In addition, in the step S6, the scheme for optimizing the query index may also include:
[0053] A. The design of a single index needs to satisfy multiple data queries;
[0054] B. The selection and order of index fields should take into account query coverage and selectivity;
[0055] C. Be cautious when creating indexes on fields that are frequently updated.
[0056] D. For array indexes, the number of future elements needs to be carefully considered;
[0057] E. Be cautious when using B-tree indexes on very long string type fields;
[0058] F. It is not advisable to create too many indexes on a single collection with high concurrent updates, where high concurrent updates refer to more than 10 million data items being updated simultaneously.
[0059] By optimizing the query index in step S6, the query performance bottleneck problem is solved.
[0060] Through the above statements, the distributed unstructured database association query method based on big data of the present invention can realize the separation of reading and writing of structured database and unstructured database. In the transaction system, only the write function of the structured database needs to be paid attention to, and the unstructured database takes over the read function, thereby eliminating the coupling degree between transaction writing and query; the data can be cleaned and lightly aggregated in real time to enhance the data value dimension; the use of unstructured database has no dependence on the data table structure, and the impact of the change of the data table structure on the system stability can be smoothed; the use of distributed unstructured database can solve the problem of real-time storage of massive data; there is a very big breakthrough in the high-concurrency query performance bottleneck; the problem of inconsistent data of the same data dimension in multiple systems can be solved.
[0061] The above is a specific description of the preferred implementation of the present invention, but the invention is not limited to the embodiments. Those skilled in the art can make various equivalent modifications or substitutions without violating the spirit of the present invention. These equivalent modifications or substitutions are all included in the scope defined by the claims of this application.
Claims
1. A distributed unstructured database association query method based on big data, characterized in that: The method comprises the following steps: S1. Generation of data tables: Based on business requirements, use mapping to design data table instances with multiple table associations to obtain multiple data tables; S2. Data table monitoring: monitor the changes of each data table through Ogg to adapt to the real-time changes of business data; S3, data synchronization, using the Ogg synchronization principle to achieve real-time synchronization of incremental data in multiple tables between the Oracle database and the Kafka cluster; S4, ETL processing and data aggregation, through the Kafka cluster, clean and pre-process the data that has been synchronized in step S3, and store it in an unstructured database after aggregation; S5. Selection of sharding algorithm: When the data volume reaches the terabyte level, a sharding algorithm is designed based on query scenarios to achieve distributed massive data storage. In step S5, the query scenarios include but are not limited to: The first scenario is a scenario where data is frequently written and read less frequently. Specifically, more than 10 million records are written, while 10,000 to 20,000 records are read. The second scenario is a scenario where data is frequently read and written less frequently. Specifically, there are 10,000 to 20,000 data records written and more than 10 million data records read. For the first scenario, a hash sharding algorithm is used to distribute the specified key hash on the nodes of different distributed unstructured database clusters, so that the data is balanced when it is landed, improving the write performance.
2. The method for associative query of a distributed unstructured database based on big data according to claim 1, characterized in that: In the step S1, the business needs include but are not limited to scan code pre-authorization, aggregation code, direct connection and indirect connection business in the payment field.
3. The method for associative query of a distributed unstructured database based on big data according to claim 1, characterized in that: In step S1: When designing a table instance for multi-table association in mapping, it is necessary to design the landing rules from the source end to the target end to realize the implementation prototype of the unstructured database association query table.
4. The method for associative query of a distributed unstructured database based on big data according to claim 3, characterized in that: The implementation rules include but are not limited to the design of data partition keys of the distributed database and the pre-setting of frequently queried indexes.
5. The method for associative query of a distributed unstructured database based on big data according to claim 1, characterized in that: For the second scenario, the range sharding algorithm is used to improve the performance of scanning and sorting data.
6. The method for associative query of a distributed unstructured database based on big data according to claim 4, characterized in that: After step S5, the method further includes the following steps: S6. According to the query scenario and the advantages of Nosq1 distributed storage, optimize the query index and optimize the data block distribution to solve the query performance bottleneck.
7. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: each query needs to create a corresponding index.
8. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: the design of a single index needs to satisfy multiple data queries.
9. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: index field selection and order, and query coverage and selectivity need to be considered.
10. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: creating indexes on fields that are updated frequently needs to be done with caution.
11. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: for the array index, the number of future elements needs to be carefully considered.
12. A method for associative query of distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: using B-tree index with caution on the field of super-long string type.
13. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The method of optimizing the query index includes: it is not advisable to create too many indexes on a single collection with high concurrent updates, wherein high concurrent updates refer to more than 10 million data being updated simultaneously.
14. The method for associative query of a distributed unstructured database based on big data according to claim 6, characterized in that: The optimized data block distribution includes: By setting the data sharding key, the data sharding key is set according to actual business needs.
15. The method for associative query of distributed unstructured database based on big data according to claim 1, characterized in that: In step S4, the step of cleaning the data includes the process of reviewing and verifying the data.
16. The method for associative query of distributed unstructured database based on big data according to claim 1, characterized in that: In step S4, the step of preprocessing the data includes data review, data screening and data sorting.