A method and device for multi-source data association extraction

Abstract

The application provides a multi-source data association extraction method and device, the method comprises the following steps: extracting the fields required in the demand from the tables with low refresh frequency in Elasticsearch and MySQL respectively, and storing the fields in the cache file in the form of memory cache, and storing the cache file in the computer memory in the form of file to form a first-level cache file; associating the first-level cache with the tables with high refresh frequency in the database through the same defined fields, and storing the associated data in the cache file in the computer memory in the form of hash table or array to form a second-level cache file; preprocessing the text file of the opposite end server, associating the text file with the second-level cache file through the same defined fields provided by the demand research specification, defining the related index algorithm, generating the final data and data structure; and inserting the final data into ClickHouse. In this way, the problems of difficult data storage in the specified time and data skew are solved.

Description

Technical Field

[0001] The embodiments of the present invention generally relate to the field of data processing technology, and particularly to a method and apparatus for multi-source data association extraction. Background Technology

[0002] With the ever-expanding volume of data in today's society, industries such as manufacturing, communications, and healthcare are generating massive amounts of unstructured real-time or delayed data. This data is characterized by its diverse structures, varied sources, and high degree of redundancy. This not only increases the repetitive workload for users from the data collection perspective, but also makes loading data from different data sources extremely cumbersome, increasing the probability of errors during data integration and storage. Furthermore, the excessive volume of data can easily lead to problems such as difficulty in data storage within the specified time and data skew. As data volume continues to grow, traditional data processing methods are no longer sufficient to meet practical needs, thus requiring a new technological solution to address these issues. Summary of the Invention

[0003] To address the above issues, this invention utilizes methods for data extraction, caching, and association to achieve data association and storage at the levels of hundreds of millions, tens of millions, and millions, thus resolving the problems of data difficulty in storage within a specified time and data skew caused by excessive data volume.

[0004] According to embodiments of the present invention, a method and apparatus for multi-source data association extraction are provided.

[0005] In a first aspect of the present invention, a method for multi-source data association and extraction is provided. The method includes:

[0006] S01: Extract the fields needed in the requirements from tables with low refresh frequency in Elasticsearch and MySQL respectively, and store them in a cache file in memory cache. The cache file is stored in computer memory as a file to form a first-level cache file.

[0007] S02: Obtain the first-level cache and associate it with tables in the database that have a high refresh frequency through the same defined fields, and store the associated data in the computer memory in the form of a hash table or array to form a second-level cache file;

[0008] S03: Preprocess the text file on the peer server, associate the text file with the second-level cache file using the same defined fields provided by the specifications during the requirements survey, define relevant indicator algorithms, and generate the final data and data structure;

[0009] S04: Insert the final data into ClickHouse.

[0010] Furthermore, the first-level cache file described in S01 and the second-level cache file described in S02 are updated periodically.

[0011] Furthermore, the filenames of the first-level cache file mentioned in S01 and the second-level cache file mentioned in S02 are both marked with a date, and the historical files are periodically cleaned up by reading the cache filenames through a shell script.

[0012] Furthermore, the specific steps for preprocessing the text file on the other end as described in S03 are as follows: the text file is divided into several different city files, and the city files with larger data volume are further split by time to generate multiple new files named with the original file name plus the city name plus the split time.

[0013] Furthermore, the relevant indicator algorithms described in S03 are defined according to requirements.

[0014] In a second aspect of the invention, an apparatus for multi-source data association extraction is provided. The apparatus includes:

[0015] Field extraction module: used to extract the fields needed for the requirements from tables with low refresh frequency in Elasticsearch and MySQL, and store them in a cache file in memory cache. The cache file is stored in computer memory as a file to form a first-level cache file.

[0016] Field association module: Used to associate the first-level cache with tables in the database that are refreshed frequently through the same defined fields, and store the associated data in the cache file in the computer memory in the form of a hash table or array to form a second-level cache file;

[0017] Text association module: used to preprocess text files on the peer server, associate text files with second-level cache files through the same defined fields provided by the specifications during the requirements survey, define relevant indicator algorithms, and generate final data and data structure;

[0018] Data storage module: Used to insert the final data into ClickHouse.

[0019] Furthermore, the first-level cache file in the field extraction module and the second-level cache file in the field association module are updated periodically.

[0020] Furthermore, the filenames of the first-level cache files in the field extraction module and the second-level cache files in the field association module are both marked with a date, and the historical files are periodically cleaned up by reading the cache filenames through a shell script.

[0021] Furthermore, the specific steps for preprocessing the peer text file in the text association module are as follows: the text file is divided into several different city files, and the city files with large data volume are further split by time to generate multiple new files named with the original file name plus the city name plus the split time.

[0022] Furthermore, the relevant indicator algorithms described in the text association module are defined according to requirements.

[0023] The above-mentioned English abbreviations are explained as follows:

[0024] Elasticsearch: A distributed search and analytics engine at the core of the Elastic Stack.

[0025] MySQL: A relational database management system

[0026] ClickHouse: A true columnar database management system

[0027] This invention enables the association and storage of data at the levels of hundreds of millions, tens of millions, and millions through methods of data extraction, caching, and association, solving the problems of data difficulty in storage within a specified time and data skew caused by excessive data volume.

[0028] It should be understood that the description in the Summary of the Invention is not intended to limit the key or essential features of the embodiments of the present invention, nor is it intended to restrict the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0029] The above and other features, advantages, and aspects of the various embodiments of the present invention will become more apparent from the accompanying drawings and the following detailed description. Wherein:

[0030] Figure 1 A flowchart of a method for multi-source data association extraction according to an embodiment of the present invention is shown;

[0031] Figure 2 A block diagram of an apparatus for multi-source data association extraction according to an embodiment of the present invention is shown. Detailed Implementation

[0032] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0033] According to an embodiment of the present invention, a method and apparatus for multi-source data association extraction are proposed. By extracting, caching and associating data, the association and storage of data at the levels of hundreds of millions, tens of millions and millions is realized, solving the problems of data being difficult to be stored in the database within a specified time due to excessive data volume and data skew.

[0034] The principles and spirit of the present invention will be explained in detail below with reference to several representative embodiments.

[0035] Figure 1 This is a schematic flowchart of a multi-source data association and extraction method according to an embodiment of the present invention. The method includes:

[0036] S01: Extract the fields needed in the requirements from tables with low refresh frequency in Elasticsearch and MySQL respectively, and store them in a cache file in memory cache. The cache file is stored in computer memory as a file to form a first-level cache file.

[0037] S02: Obtain the first-level cache and associate it with tables in the database that have a high refresh frequency through the same defined fields, and store the associated data in the computer memory in the form of a hash table or array to form a second-level cache file;

[0038] S03: Preprocess the text file on the peer server, associate the text file with the second-level cache file using the same defined fields provided by the specifications during the requirements survey, define relevant indicator algorithms, and generate the final data and data structure;

[0039] S04: Insert the final data into ClickHouse.

[0040] It should be noted that although the operation of the method of the present invention has been described in a specific order in the above embodiments and figures, this does not require or imply that the operations must be performed in that specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.

[0041] To provide a clearer explanation of the above-described method for multi-source data association and extraction, a specific embodiment will be used for illustration below. However, it is worth noting that this embodiment is only for better illustrating the present invention and does not constitute an improper limitation of the present invention.

[0042] The following specific example will further illustrate the method of multi-source data association and extraction in more detail:

[0043] IoT DPI performance data is collected, and the data file is pushed every 15 minutes in CSV format. The data volume is 30 million to 50 million records, and the content includes dozens of fields such as number, date, traffic, and authentication. In addition, Elasticsearch stores a table of numbers and customer information, as shown in Table 1.

[0044] Table 1

[0045]

[0046]

[0047] The relationship between phone numbers and base stations is shown in Table 2:

[0048] Table 2

[0049] Number Base station coding Base station name …… Msisdn1 Gnodeb1 Base Station 1 …… Msisdn2 Gnodeb2 Base Station 2 …… Msisdn4 Gnodeb4 Base Station 4 …… …… …… …… ……

[0050] The MySQL database contains a table of community information, as shown in Table 3 (approximately 2 million records).

[0051] Table 3

[0052] Base station coding Base station name Cell code Community Name …… Gnodeb1 Base Station 1 Nrcell1 Community 1 …… Gnodeb2 Base Station 2 Nrcell2 Community 2 …… Gnodeb3 Base Station 3 Nrcell3 Community 3 …… …… …… …… …… ……

[0053] The customer information table is shown in Table 4 (data volume: tens of thousands):

[0054] Table 4

[0055] Customer Code Customer Name Customer Level Service Model account Manager …… Company1 Customer 1 AA Exclusive Manger1 …… Company2 Customer 2 A Enjoy Manger2 …… Company4 Customer 4 ordinary Enjoy Manger3 …… …… …… …… …… …… ……

[0056] The project information table is shown in Table 5 (data volume: tens of thousands):

[0057] Table 5

[0058] Project Code Project Name Customer Code Customer Name …… Product1 Project 1 Company1 Customer 1 …… Product2 Project 2 Company2 Customer 2 …… Product3 Project 3 Company3 Customer 3 …… …… …… …… …… ……

[0059] The text file on the peer server is pulled to the local server via SFTP or FTP. A script reads the file and preprocesses it: the text file is divided into several different city files, and the city files with large amounts of data are further split by time, generating multiple new files named with the original filename plus the city name plus the split time. The file content is as follows:

[0060] City | Number | Time | Number of Successful Authentications | Total Number of Authentications | Number of Successful TCP Connections | Total Number of TCP Connections Nanjing | Msisdn1 | 20231123 | 1 | 3 | 2 | 2

[0061] Nanjing | Msisdn2 | 20231123 | 2 | 3 | 1 | 2

[0062] Nanjing | Msisdn3 | 20231123 | 4 | 4 | 4 | 5

[0063] Extract the relevant table fields, Customer Code and Base Station ID, from Elasticsearch and MySQL, and store them in a cache file. Then, associate the data in Tables 1-5 that involve the Customer Code and Base Station ID fields to form a new cache file, as shown in Tables 6 and 7.

[0064] Table 6

[0065] Customer Code Customer Name Number Customer Level Service Model account Manager Project Code Project Name Company1 Customer 1 Msisdn1 AA Exclusive Manger1 Product1 Project 1 Company2 Customer 2 Msisdn2 A Enjoy Manger2 Product2 Project 2

[0066] Table 7

[0067] Base station coding Base station name Cell code Community Name Number Gnodeb1 Base Station 1 Nrcell1 Community 1 Msisdn1 Gnodeb2 Base Station 2 Nrcell2 Community 2 Msisdn2

[0068] Specifically, the cached files and new cached files are updated once a day.

[0069] Define the relevant metric algorithms: Authentication success rate = Number of successful authentications / Total number of authentications; TCP establishment success rate = Number of successful TCP establishments / Total number of TCP connections.

[0070] Finally, Tables 6 and 7 are linked to the text file by number and entered into the ClickHouse result table, as shown in Table 8:

[0071] Table 8

[0072]

[0073]

[0074] Based on the same inventive concept, this invention also proposes a device for multi-source data association and extraction. The implementation of this device can be found in the implementation of the method described above; repeated details will not be repeated. Figure 2 As shown, the device 100 includes:

[0075] Field extraction module 101: It is used to extract the fields required by the requirements from tables with low refresh frequency in Elasticsearch and MySQL, and store them in a cache file in memory cache. The cache file is stored in computer memory as a file to form a first-level cache file.

[0076] Field association module 102: Used to obtain the first-level cache and tables in the database with high refresh frequency, associate them with the same defined fields, and store the associated data in the cache file in the computer memory in the form of a hash table or array to form a second-level cache file;

[0077] Text association module 103: It is used to preprocess text files on the peer server, associate text files with secondary cache files through the same defined fields provided by the specifications during the requirements survey, define relevant indicator algorithms, and generate final data and data structure;

[0078] Data storage module 104: Used to insert the final data into ClickHouse.

[0079] This invention proposes a device for multi-source data association and extraction. Through data extraction, caching, and association methods, it enables the association and storage of data at the levels of hundreds of millions, tens of millions, and millions, solving the problems of data difficulty in storage within a specified time and data skew caused by excessive data volume.

[0080] While the spirit and principles of the invention have been described with reference to several specific embodiments, it should be understood that the invention is not limited to the disclosed specific embodiments, and the division of aspects does not imply that features in these aspects cannot be combined for benefit; such division is merely for ease of description. The invention is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.

[0081] Regarding the limitation of the scope of protection of this invention, those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solution of this invention are still within the scope of protection of this invention.

Claims

1. A method for multi-source data association and extraction, characterized in that, The method includes: S01: Extract the fields needed in the requirements from tables with low refresh frequency in Elasticsearch and MySQL respectively, and store them in a cache file in memory cache. The cache file is stored in computer memory as a file to form a first-level cache file. S02: Obtain the first-level cache and associate it with tables in the database that have a high refresh frequency through the same defined fields, and store the associated data in the computer memory in the form of a hash table or array to form a second-level cache file; S03: Preprocess the text file on the peer server, associate the text file with the second-level cache file using the same defined fields provided by the specifications during the requirements survey, define relevant indicator algorithms, and generate the final data and data structure; S04: Insert the final data into ClickHouse.

2. The method of claim 1, wherein, The first-level cache file described in S01 and the second-level cache file described in S02 are updated periodically.

3. The method of claim 1, wherein, The filenames of the first-level cache file mentioned in S01 and the second-level cache file mentioned in S02 are marked with a date, and the historical files are cleaned up periodically by reading the cache filenames through a shell script.

4. The method of claim 1, wherein, The specific steps for preprocessing the text file on the other end as described in S03 are as follows: the text file is divided into several different city files, and the city files with large data volume are further split by time to generate multiple new files named with the original file name plus the city name plus the split time.

5. The method of claim 1, wherein, The relevant indicator algorithms described in S03 are defined according to requirements.

6. An apparatus for multi-source data association extraction, the apparatus comprising: The device includes: Field extraction module: used to extract the fields needed for the requirements from tables with low refresh frequency in Elasticsearch and MySQL, and store them in a cache file in memory. The cache file is stored in computer memory as a file to form a first-level cache file. Field association module: Used to associate the first-level cache with tables in the database that are refreshed frequently through the same defined fields, and store the associated data in the cache file in the computer memory in the form of a hash table or array to form a second-level cache file; Text association module: used to preprocess text files on the peer server, associate text files with second-level cache files through the same defined fields provided by the specifications during the requirements survey, define relevant indicator algorithms, and generate final data and data structure; Data storage module: Used to insert the final data into ClickHouse.

7. The multi-source data association extraction apparatus of claim 6, wherein, The first-level cache file in the field extraction module and the second-level cache file in the field association module are updated periodically. 8.The device of claim 6, wherein, The filenames of the first-level cache file in the field extraction module and the second-level cache file in the field association module are both marked with a date, and the historical files are cleaned up periodically by reading the cache filenames through a shell script.

9. The apparatus for multi-source data association and extraction according to claim 6, characterized in that, The specific steps for preprocessing the peer text file in the text association module are as follows: divide the text file into several different city files, and perform secondary cutting on the city files with large data volume by time to generate multiple new files named with the original file name plus the city name plus the cutting time.

10. The multi-source data association extraction apparatus of claim 6, wherein, The relevant index algorithms described in the text association module are defined according to requirements.

Images