Method for automatic structuring of JSON data and incorporation into a database

Abstract

The application relates to the field of data processing and provides a JSON data automatic structuring and warehousing method. The main idea is to solve the problem of how to store different JSON data structures of various data sources in a warehouse. The scheme includes judging the type of the accessed JSON data source, obtaining JSON data by using different methods according to the type, pre-processing the data, checking and processing dirty data to obtain standard JSON fields, parsing and processing the obtained JSON of different JSON data sources, agreeing on the format of the data file, generating a standard data file, investigating the structuring processing progress of the data, generating an ok file, checking the accuracy of the generated data file, obtaining the standard data file after the checking, and performing batch processing on the data file to store the data file in the warehouse.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of data processing, and mainly aims at the method of automatic structured data merging into the database derived from the problem that JSON data is difficult to use and analyze. BACKGROUND

[0002] Risk control technology is one of the cornerstones of modern finance, and the risk control model plays a key role in risk control technology. In the Internet financial era, due to the inclusive nature of small and micro enterprises facing individuals, automated risk control is the only way to reduce risk costs, and the cornerstone of risk control technology comes from data, which is the fuel driving risk control technology.

[0003] Traditional risk control technology is mostly controlled by the risk control team of each institution in an artificial way. However, with the continuous development of Internet technology, the whole society is accelerating, and the traditional risk control method has gradually failed to support the business expansion of the institution; and the intelligent processing of multi-dimensional and large amounts of data by big data, and the batch standardized execution process can better meet the development requirements of risk control business in the information development era.

[0004] And now the storage method of big data is generally stored in unstructured database, one is convenient for storage, and the other is cost saving, which greatly increases the difficulty of analyzing this part of data, and another part of data is stored in structured database, but the data stored by the data type of JSON is usually stored in JSON. Taking the risk control scene as an example, when the risk control system evaluates the risk indicators of the customer, the indicators are generally obtained from the third-party data, and the risk control system generally takes out the required indicator data from the upstream system for analysis, and then a large part of the data of the third party is stored in the source system. These data are the cost sources of risk control. If these data cannot be used in time, it is a waste of data of the whole risk control system. Therefore, an automatic method for converting JSON data into structured data is used to effectively utilize all effective data of the whole risk control. SUMMARY

[0005] The purpose of the present application is to solve the problem of how to structure different JSON data of multiple data sources into a database.

[0006] In order to solve the above technical problems, the technical scheme adopted by the present application is as follows:

[0007] The present application provides a method for automatically structuring JSON data and merging into a database, comprising the following steps:

[0008] Step 1, judging the type of the accessed JSON data source, and using different methods to obtain JSON data according to different types;

[0009] Step 2, preprocessing of data, dirty data is checked and processed, and standard JSON fields are obtained;

[0010] Step 3: The JSON obtained from different JSON data sources is parsed and processed, and the format of the data file is agreed, and a standard data file is generated;

[0011] Step 4: The data structure processing progress of step 3 is explored, and an ok file is generated;

[0012] Step 5: The data file generated in step 3 is accuracy checked, and the standard data file after checking is obtained;

[0013] Step 6: The data file is processed in batches and stored in the database, and the data file storage is completed;

[0014] In the above technical solution, step 1 includes the following steps:

[0015] Step 1.1, obtain data from unstructured database, connect to Elasticsearch using API interface to obtain data, and take data by day as level;

[0016] Step 1.2, obtain data from structured database, connect to relational database, and take data by day as level using corresponding SQL;

[0017] Step 1.3, obtain stream data, obtain data from KAFKA, establish corresponding connection, and obtain stream data in KAFKA by consuming KAFKA.

[0018] In the above technical solution, step 2 includes the following steps:

[0019] Step 2.1, for the data obtained in step 1.1, first judge whether the data format is normal JSON data format, and adopt the means of eliminating the data of non-normal JSON format, to obtain normal JSON data;

[0020] Then, the data standardization processing is carried out, and the special characters and spaces in the data are replaced by the means of replacing these characters with spaces, to obtain standard JSON data;

[0021] Step 2.2, for the data obtained in step 1.2, the data is structured data field, and the special characters in the data field are eliminated by the means of eliminating the special characters, to obtain standard data;

[0022] The data is JSON field, according to step 2.1, first judge the format of the data, and then eliminate the special fields by the means of standardizing the data, to obtain standard JSON data.

[0023] Step 2.3, KAFKA is streaming data, all data is continuous, sequentially process the data, standardize the JSON data, and get the standard JSON data.

[0024] KAFKA is streaming data, all data is continuous, in order to ensure the consistency of all data tasks, a scheme of judging the size of the data when processing the data is adopted to judge whether the streaming data of the day is consumed and processed.

[0025] In the above technical solution, step 3 includes the following steps:

[0026] Step 3.1.1: For data stored in the non-relational database Elasticsearch, connect to the database, then query the data that needs to be structured, in order to ensure efficiency, batch query is adopted, get the JSON data that needs to be structured from the database, then structure the data, first get the identifying data field in the JSON data to be used as an identifier to determine the source and attributes of the data, there is only one identifying data field, determined according to the business, then loop out the key and value of each node in the JSON data to ensure the completeness of the data, in order to improve efficiency, multi-threaded operation is used;

[0027] Step 3.1.2: After standardizing the data obtained in step 3.1.1, one JSON data becomes multiple data with the identification field as the index and the key and value as the attributes, the data is written into the data file, and the standard data file is generated, which is more convenient for importing the data into the corresponding database, the format of the data file is agreed, first, the first line of the data file is the table header field of the database table, and the fields are distinguished by a specific separator, the operation of writing the multiple data parsed from one JSON data into the file is adopted to generate the standard data file.

[0028] Step 3.2: For the data stored in the structured database, first confirm the type of the field, the field is a standard data field, use the scheme of writing to the data file to get the standard data file, for the JSON field data stored in the structured database, use the same operation in 3.1.1, first get the identifying data field in the JSON data to be used as an identifier to determine the source and attributes of the data, there is only one identifying data field, determined according to the business, then the key and value of each node in the JSON data are taken out in a loop to ensure the completeness of the data, according to the size of the data, the thread pool is used to parse the data, after the data processing is completed, a JSON data becomes multiple data with the identifying field as the index and the key and value as the attributes, the data is written into the data file to generate a standard data file, which is more convenient for importing data into the corresponding database, the format of the data file is agreed, first, the first line of the data file is the table header field of the database table, the fields are distinguished by a specific separator, the operation of writing the multiple data parsed from a JSON data into the file is used to generate a standard data file;

[0029] Step 3.3: For the data stored in KAFKA, first determine the consumer group where the data is located, then start consuming the data needed to be obtained, and the same as step 2.1, the data is preprocessed, some garbage data and dirty data are removed, after the data is obtained, in order to improve the efficiency of data processing, the multi-thread processing method is used to process the data, because the data in Kafka is consumed in real time, in order to keep the synchronization with other data, the data of the previous day is saved at the time point of date switching, and the data of the next day is generated to a new data file, and an identification file is generated to determine that the data of the previous day has been parsed, so that the data file of the previous day can be normally loaded into the corresponding database table.

[0030] In the above technical solution, in step 3, the JSON data is structured and processed, and the same standard format data file is generated according to the data of different channels, because the execution time of the task cannot be determined, an empty file named ok is generated to indicate the result of the structured processing operation of the file.

[0031] In the above technical solution, step 5 includes the following steps:

[0032] According to the format of the data file, the field data of each row of the data file is verified, if there is no mismatch, the original data file is copied and the abnormal data is deleted, a new data file is generated and the next operation is performed.

[0033] In the technical solution, step 6 comprises the following steps:

[0034] In step 5, the data file is checked for standardization to ensure the accuracy of the data file, and then the data file is imported into the corresponding database by using a script processing scheme, so that the operation of structuring the data from the JSON data and storing the data in the database is completed.

[0035] Because the technical solution is adopted in the application, the following beneficial effects are achieved:

[0036] Whether the JSON data is stored in any place, such as an unstructured database, a structured database or a stream platform, the data can be structured and stored in the database by using the complete operation. The completeness of the application ensures the accuracy and effectiveness of the data. The entire process can be automated by using the application. BRIEF DESCRIPTION OF DRAWINGS

[0037] Figure 1 The flowchart of the application is shown in the figure. DETAILED DESCRIPTION

[0038] The embodiments of the application will be described in detail below. Although the application will be described and illustrated by combining some specific embodiments, it should be noted that the application is not limited to only these embodiments. On the contrary, the modifications or equivalent replacements made to the application should be covered in the scope of the claims of the application.

[0039] In addition, in order to better illustrate the application, numerous specific details are given in the specific embodiments below. Those skilled in the art will understand that the application can also be implemented without these specific details.

[0040] The application provides a method for automatically structuring and storing JSON data in a database, characterized in that different data files are generated in different scenarios, and standardized requirements are proposed for the generation of these data files. Each data file name contains a table for storing data and a data date corresponding to the data. For the generation standard of the data file, a data empty file with a file suffix ok is set, which represents the completion of the generation of the batch of data files. The next data storage operation can be performed, and the entire process is completed, realizing the conversion of data from JSON data to structured data and complete automation.

[0041] Specifically, the following steps are included:

[0042] Step 1: Determine the type of the accessed JSON data source, and acquire the JSON data by using different methods according to the type.

[0043] Step 1.1, obtain data from unstructured database, use API interface to connect to Elasticsearch to obtain data, take data at the level of day;

[0044] Step 1.2, obtain data from structured database, connect to relational database, and take data at the level of day with corresponding SQL;

[0045] Step 1.3, obtain stream data, obtain data from KAFKA, establish corresponding connection, and obtain stream data in KAFKA by consuming KAFKA;

[0046] Step 2, data preprocessing, check and process dirty data;

[0047] Step 2.1, for the data obtained in step 1.1, first judge whether the data format is normal JSON data format, adopt the means of eliminating the data with abnormal JSON format, and obtain normal JSON data. If a large amount of data has the same format problem, consider formatting the data to obtain standard JSON format data. Then perform data standardization processing, replace special characters and spaces in the data with spaces, and obtain standard JSON data.

[0048] Step 2.2, for the data obtained in step 1.2, the data is structured data field, adopt the means of eliminating special characters in the data field to obtain standard data. The data is JSON field, execute step 2.1, judge the format of the data, and then eliminate the special field to obtain standard JSON field.

[0049] Step 2.3, KAFKA is stream data, all data are continuous, in order to ensure the consistency of all data tasks, adopt the scheme of judging the size of data when processing data to judge whether the stream data of the day is consumed and processed.

[0050] Step 3: parse and process the JSON obtained from different JSON data sources, and agree on the format of the data file;

[0051] Step 3.1.1: For data stored in the non-relational database Elasticsearch, connect to the database, then query the data that needs to be structured, in order to ensure efficiency, use batch query to get the JSON data that needs to be structured from the database, and then structure the data. First, get the identifying data field in the JSON data to be used as an identifier to determine the source and attributes of the data. There is more than one identifying data field, which is determined according to the business. Then the key and value of each node in the JSON data are taken out in a loop to ensure the completeness of the data. In order to improve efficiency, multi-threaded operation is used.

[0052] Step 3.1.2: After standard processing of the structured data obtained in step 3.1.1, one JSON data becomes multiple data with the identification field as the index and the key and value as the attributes. The data is written into a data file to generate a standard data file, which is more convenient for importing data into the corresponding database. The format of the data file is agreed upon. First, the first row of the data file is the table header field of the database table, which is distinguished by a specific separator. The operation of writing multiple data parsed from one JSON data into the file generates a standard data file.

[0053] Step 3.2: For data stored in a structured database, first confirm the type of the field, which is a standard data field. The data is written into a data file to obtain a standard data file.

[0054] For JSON field data stored in a structured database, the same operation in 3.1.1 is used. First, get the identifying data field in the JSON data to be used as an identifier to determine the source and attributes of the data. There is more than one identifying data field, which is determined according to the business. Then the key and value of each node in the JSON data are taken out in a loop to ensure the completeness of the data. According to the size of the data, the thread pool is used to parse the data. After the data processing is completed, one JSON data becomes multiple data with the identification field as the index and the key and value as the attributes. The data is written into a data file to generate a standard data file, which is more convenient for importing data into the corresponding database. The format of the data file is agreed upon. First, the first row of the data file is the table header field of the database table, which is distinguished by a specific separator. The operation of writing multiple data parsed from one JSON data into the file generates a standard data file.

[0055] Step 3.3: For the data stored in KAFKA, first determine the consumer group where the data is located, then start consuming the data needed to be obtained, and in the same way as step 2.1, preprocess the data, and first remove some garbage data and dirty data. After obtaining the data, in order to improve the efficiency of data processing, the same processing is performed on the data using multi-threading. Because the data in Kafka is consumed in real time, in order to keep the data synchronized with other data, the data of the previous day is saved at the time point when the date is switched, and the data of the next day is generated to a new data file, and an identification file is generated to determine that the data of the previous day has been parsed, so that the data file of the previous day can be normally loaded into the corresponding database table.

[0056] Step 4: Data structure processing progress probe, generate ok file;

[0057] In step 3, the JSON data is structured and processed, and the same standard format data file is generated according to the data of different channels. Because the execution time of the task cannot be determined, an empty file named ok is generated to indicate the completion of the structured processing of the file.

[0058] Step 5: Accuracy verification of the data file generated in step 3 to obtain a standard data file;

[0059] Step 5.1:

[0060] In step 2, the data in different scenarios is uniformly preprocessed, and in step 3, the JSON data is uniformly structured and processed to generate a data file. In step 4, an ok file representing the completion of data structure processing is generated. The next operation is to load the data file into the corresponding database table. Before performing this operation, in order to reduce the failure rate of the task, the data file is first verified.

[0061] Step 5.2:

[0062] In step 2, the data has been preprocessed, but some abnormal situations may still occur, which will cause the structure of the standardized data file to be abnormal. A data file has N fields in total, but the number of fields in a certain row of the data file is greater than N or less than N. This data file is defined as an abnormal data file. Once this data file enters the next step, an exception will occur, which will cause the subsequent task to fail. Because the entire process is continuous, the failure of a task will cause problems in the subsequent tasks. Therefore, in this step, all data files are verified.

[0063] Step 5.3. Accuracy check on the data file generated in step 3 to obtain a standard data file;

[0064] Step 5.3.1:

[0065] In this step, we will check the data file, because our final goal is to import the data into a structured database, so the data file must be a structured data file with a fixed format. We check the accuracy of the data file to determine if the data is complete and there are no problems, and then we will perform subsequent operations. First, we will check the field data volume of each line of the data file according to the data file format. If there is no mismatch, we will copy the original data file using a script, delete the abnormal data, generate a new data file, and perform the next step. This will not affect the current task execution and subsequent task execution, but the task will be recorded. When the maintenance personnel manually enter the copied original file, they will find the data and analyze it.

[0066] Step 5.3.2:

[0067] First, we manually add the abnormal data to the corresponding database table, then we determine the reason for the abnormal data, judge the cause of the abnormality, and add the solution to the abnormal reason to step 2, so that the task can be successfully executed when encountering the same problem.

[0068] Step 5.4

[0069] From the beginning of data preparation, data preprocessing, to data parsing and generating data files, and then checking the data files, in order to facilitate the analysis personnel to use these data, we need to load the data into the database, so that the analysis personnel can use the analysis tool to process these data.

[0070] Step 6: Batch processing of data files into the database to complete the data file into the database;

[0071] Step 6.1:

[0072] In step 5, the data file has been checked for standardization to ensure the accuracy of the data file, then a script processing scheme is used to import the data file into the corresponding database at one time, completing the data from JSON data structuring and into the database operation.

Claims

1. A method for automatic structuring of JSON data and incorporating into a database, characterized in that, The method comprises the following steps: Step 1, judging the type of the accessed JSON data source, and obtaining the JSON data by different methods according to the type; Step 2, data preprocessing, dirty data is checked and treated, and standard JSON fields are obtained; Step 3: the JSON obtained from different JSON data sources is parsed and processed, and the format of the data file is agreed, and a standard data file is generated; step 3 comprises the following steps: Step 3.1.1: for the data stored in the non-relational database Elasticsearch, connect to the database, then query the data that needs to be structured, in order to ensure efficiency, batch query is adopted, the JSON data that needs to be structured is obtained from the database, and then the JSON data is structured, first, the identification data field in the JSON data is obtained to be used as an identifier to determine the source and attribute of the data, there is not more than one identification data field, which is determined according to the business, then the key and value of each node in the JSON data are taken out in a loop to ensure the completeness of the data, in order to improve efficiency, multi-thread operation is used; Step 3.1.2: after the standard processing of the data obtained in step 3.1.1, one JSON data becomes multiple data with the identification field as the index and the key and value as the attribute, the data is written into the data file to generate a standard data file, which is more convenient for importing the data into the corresponding database, and the format of the data file is agreed, first, the first row of the data file is the table header field of the database table, the fields are distinguished by a specific separator, the operation of writing the multiple data parsed from one JSON data into the file is adopted to generate a standard data file; Step 3.2: for the data stored in the structured database, first confirm the type of the field, the field is a standard data field, adopt the scheme of writing into the data file to obtain a standard data file, for the JSON field data stored in the structured database, the same operation in 3.1.1 is used, first, the identification data field in the JSON data is obtained to be used as an identifier to determine the source and attribute of the data, there is not more than one identification data field, which is determined according to the business, then the key and value of each node in the JSON data are taken out in a loop to ensure the completeness of the data, according to the size of the data, the thread pool is used to parse and process the data, after the data processing is completed, one JSON data becomes multiple data with the identification field as the index and the key and value as the attribute, the data is written into the data file to generate a standard data file, which is more convenient for importing the data into the corresponding database, and the format of the data file is agreed, first, the first row of the data file is the table header field of the database table, the fields are distinguished by a specific separator, the operation of writing the multiple data parsed from one JSON data into the file is adopted to generate a standard data file; Step 3.3: For the data stored in KAFKA, first determine the consumer group where the data is located, then start consuming the data needed to obtain, and in the same way as step 2.1, preprocess the data, first remove some garbage data and dirty data, and after obtaining the data, in order to improve the efficiency of data processing, also use multi-threaded processing method to process the data, because the data in Kafka is consumed in real time, in order to keep the synchronization with other data, at the time point of date switching, the data of the previous day is saved, and the data of the next day is generated to a new data file, and an identification file is generated to determine that the data of the previous day has been parsed, so as to normally load the data file of the previous day into the corresponding database table; Step 4: Data structuring processing progress exploration, generate ok file; Step 5: Accuracy verification of data file generated in step 3, get standard data file after verification; Step 6: Batch processing of data file for database operation, complete data file storage.

2. The method for automatically structuring and ingesting JSON data into a database according to claim 1, wherein, Step 1 includes the following steps: Step 1.1, obtain data from unstructured database, use API interface to connect to Elasticsearch to obtain data, take data by day; Step 1.2, obtain data from structured database, connect to relational database, and use corresponding SQL to obtain data by day; Step 1.3, obtain stream data, obtain data from KAFKA, establish corresponding connection, and obtain stream data in KAFKA by consuming KAFKA.

3. The method for automatically structuring and ingesting JSON data into a database according to claim 1, wherein, Step 2 includes the following steps: Step 2.1, for the data obtained in step 1.1, first judge whether the data format is normal JSON data format, and use the method of removing non-normal JSON format data to obtain normal JSON data; Then perform data standardization processing, replace special characters and spaces in the data with spaces to obtain standard JSON data; Step 2.2, for the data obtained in step 1.2, the data is structured data field, and the special characters in the data field are removed to obtain standard data; The data is JSON field, execute step 2.1, first judge the format of the data, and then remove the special fields to obtain standard JSON data; Step 2.3, KAFKA is stream data, all data is continuous, and the JSON data is standardized, and standard JSON data is obtained.

4. The method for automatically structuring and ingesting JSON data into a database according to claim 1, wherein, In step 3, the JSON data is structured, and the same standard format data file is generated according to different channel data, because the execution time of the task cannot be determined, an empty file named ok is generated to indicate the completion of the file structuring processing operation.

5. The method for automatically structuring and ingesting JSON data into a database according to claim 1, wherein, Step 5 includes the following steps: According to the format of the data file, the field data amount of each row of the data file is checked first, whether it matches the field data of the file format. If no mismatch occurs, the original data file is copied by using a script, and the abnormal data is deleted, a new data file is generated, and the next step is executed.

6. The method for automatically structuring and ingesting JSON data into a database according to claim 1, wherein, Step 6 includes the following steps: In step 5, the data file has been checked for standardization to ensure the accuracy of the data file. Then, by using a script processing scheme, the data file is imported into the corresponding database at one time, and the operation of data structured into a JSON data structure and into the database is completed.

Images