Data import and query method and system based on double schema
The data import through dual schema rotation solves the problem of database performance bottlenecks, and realizes the function of simultaneous import and query, avoids IO exceptions, and reduces the coupling between business logic and schema switching.
Patent Information
- Application Number
- CN202411023536.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-29
- Publication Date
- 2024-12-06
AI Technical Summary
When processing large files and supporting large traffic database queries, the existing technology leads to database performance bottlenecks, resulting in IO exceptions, and the inability to process multiple files and queries at the same time.
Using the double schema method, by creating the first data table and the second data table in the first schema, recording the effective schema and the effective table, importing the data into the non-effective table, and switching to the effective table during querying. , realize data import and query simultaneous operation.
Effectively control IO pressure, avoid database IO exceptions, and realize the function of simultaneously importing and querying, without adjusting existing business logic, reducing the coupling between business logic and schema switching.
Smart Images

Figure CN119088815A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of data processing technology, and in particular to a dual-schema based data import and query method and system. Background Art
[0002] When inserting the data of large files into the MYSQL database in batches through scripts or using the MYSQL batch import command to process large files, the database will be under great pressure. The single SQL statement is too large, the MYSQL parsing speed is reduced, and in the batch processing process, in addition to processing a single large file, it is also necessary to process multiple other large files and support large-volume database queries. Since the database itself has certain performance bottlenecks, it is easy to cause poor database performance at this time, and it is impossible to process multiple files at the same time. It is easy to cause database IO abnormalities during the processing of files and the query process. For example, in the storage scenario, when the file content that needs to be imported into the database is specifically product code, net value, net value date and other data, the number of fund products on the market is more than 20,000, and the net value of a product can be as high as more than 6,000. It has become an urgent problem to support the full storage of product net value and the query of product net value. Summary of the invention
[0003] The purpose of the present invention is to provide a data import and query method and system based on dual schema to solve the above problems. In order to achieve the above purpose, the technical solution adopted by the present invention is as follows:
[0004] On the one hand, the present application provides a data import and query method based on dual schema, including:
[0005] Get the data to be imported;
[0006] Create a first data table in a first schema, and create a second data table in a second schema, wherein the structures of the first data table and the second data table are the same, and the headers of the first data table and the second data table correspond to the contents of the data to be imported;
[0007] Create a system parameter table, which is used to record the effective schema and effective table. The effective table refers to a table whose corresponding time of the last data in the first data table and the second data table is closest to the current time. The effective schema refers to the schema where the effective table is located.
[0008] Obtain the effective schema and effective table through the system parameter table;
[0009] Import the data to be imported into the ineffective data table;
[0010] After receiving the query request, the effective table is obtained through the system parameter table, and the required query data is obtained from the effective table.
[0011] On the other hand, the present application also provides a data import and query system based on dual schema, including:
[0012] A first acquisition unit, used to acquire data to be imported;
[0013] A first creation unit is used to create a first data table in a first schema and a second data table in a second schema, wherein the first data table and the second data table have the same structure, and headers of the first data table and the second data table correspond to contents of the data to be imported;
[0014] A second creation unit is used to create a system parameter table, wherein the system parameter table is used to record an effective schema and an effective table, wherein the effective table refers to a table in which the corresponding time of the last data in the first data table and the second data table is closest to the current time, and the effective schema refers to the schema in which the effective table is located;
[0015] The second acquisition unit is used to acquire the effective schema and the effective table through the system parameter table;
[0016] Import unit, used to import the data to be imported into the ineffective data table;
[0017] The query unit is used to obtain the effective table through the system parameter table after receiving the query request, and obtain the required query data from the effective table.
[0018] The beneficial effects of the present invention are:
[0019] The present invention imports data by rotating dual schemas to control IO pressure and avoid database IO anomalies. When performing database queries, the database table in the schema of unprocessed large files is switched to perform queries, thereby realizing the functions of simultaneous import and query. Furthermore, the dual schema method does not require adjustment of existing business logic, thereby reducing the coupling between business logic and switching schemas.
[0020] Other features and advantages of the present invention will be described in the following description, and partly become apparent from the description, or be understood by implementing the embodiments of the present invention. The purpose and other advantages of the present invention can be realized and obtained by the structures particularly pointed out in the written description, claims, and drawings. BRIEF DESCRIPTION OF THE DRAWINGS
[0021] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. It should be understood that the following drawings only show certain embodiments of the present invention and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without creative work.
[0022] Figure 1 A logic diagram for data import of the present invention;
[0023] Figure 2 This is a logical diagram of data query of the present invention. DETAILED DESCRIPTION
[0024] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all of the embodiments. The components of the embodiments of the present invention generally described and shown in the drawings herein can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present invention provided in the drawings is not intended to limit the scope of the claimed invention, but merely represents selected embodiments of the present invention. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.
[0025] It should be noted that similar reference numerals and letters represent similar items in the following drawings, so once an item is defined in one drawing, it does not need to be further defined and explained in the subsequent drawings. At the same time, in the description of the present invention, the terms "first", "second", etc. are only used to distinguish the description and cannot be understood as indicating or implying relative importance.
[0026] Embodiment 1:
[0027] This embodiment provides a data import and query method based on dual schemas, including:
[0028] S1. Get the data to be imported. The specific steps are as follows:
[0029] Create periodic tasks;
[0030] The data to be imported is received at fixed time intervals, and the data to be imported received each time includes the data to be imported received last time and new content.
[0031] In this embodiment, the fixed time interval is set to 24 hours, that is, the data to be imported is updated once a day, and the data to be imported is a .dat file. And the start time of the periodic task is set to the time when the work starts every day, so as to import and query the relevant data of the previous day's business.
[0032] S2. Create a first data table in the first schema, and create a second data table in the second schema. The structures of the first data table and the second data table are the same, and the headers of the first data table and the second data table correspond to the contents of the data to be imported.
[0033] In this embodiment, the first data table and the second data table store more than 10 million data items, which is sufficient to accommodate business data in complex businesses. For example, in a storage scenario, the data to be imported is specifically product code, net value and net value date, so the headers of the first data table and the second data table are also product code, net value and net value date.
[0034] S3. Create a system parameter table, which is used to record the effective schema and effective table. The effective table refers to a table whose corresponding time of the last data in the first data table and the second data table is closest to the current time. The effective schema refers to the schema where the effective table is located.
[0035] S4. Obtain the effective schema and effective table through the system parameter table.
[0036] In some specific embodiments, step S4 includes:
[0037] S41. Initially, the first data table and the second data table are both empty tables, and a data table is randomly selected as the effective table;
[0038] S42. Record the effective table and effective schema in the system parameter table.
[0039] Step S4 is the preparation for subsequent data import and query.
[0040] S5. Import the data to be imported into the ineffective data table, including:
[0041] S51. Split the data to be imported into multiple small files with fixed sizes.
[0042] In this embodiment, the fixed size is set to a fixed number of lines, such as 10 lines, 20 lines, 50 lines, etc. If the file is too large, it may time out or fail due to the incompatibility of the network environment. Splitting it into small files can improve the upload efficiency, more effectively utilize bandwidth, and improve the import efficiency of the data to be imported.
[0043] S52, importing multiple small files into the ineffective data table in batches;
[0044] S53, after the import of the data to be imported is completed, the data table that imports the data to be imported is recorded as the effective table in the system parameter table, and the schema where the effective table is located is recorded as the effective schema;
[0045] S53, after the import of the to-be-imported data is completed, another data table is recorded as an ineffective data table in the system parameter table, and the schema to which the ineffective data table belongs is recorded as an ineffective schema;
[0046] S54. Clear the data in the ineffective data table.
[0047] The purpose of step S54 is to clear the data in the ineffective data table in advance so that the data can be directly imported next time, thereby improving the import efficiency of the data to be imported.
[0048] S6. After receiving the query request, the effective table is obtained through the system parameter table, and the required query data is obtained from the effective table.
[0049] In some specific embodiments, step S6 includes:
[0050] S61, receiving a query request;
[0051] S62, obtaining the effective schema and effective table from the system record table;
[0052] S63, loading the data in the effective table through the i gnit cache;
[0053] S64, reading the data to be queried from the cache;
[0054] S65: After a preset time has passed, all data in the cache is deleted.
[0055] In this embodiment, the preset time in step S65 is set to ten minutes. Its purpose is to release memory after the query is completed, reduce space occupancy, and avoid affecting the next query.
[0056] In this embodiment, the data query process is as follows: Figure 2 shown.
[0057] It can be seen from steps 5 and 6 that the data import uses the currently ineffective data table, and the query uses the currently effective data table, so that the data import and query work can be carried out at the same time, which improves the business processing efficiency and reduces the waiting time, especially playing an important supporting role for business scenarios that require real-time data analysis and decision support. Traditional data import and query are set to different business logics respectively, and the business logic and schema need to be switched when importing and querying data. This application can import and query data at the same time, effectively reducing the coupling caused by switching business logic and schema, and has high robustness.
[0058] Embodiment 2:
[0059] The difference between this embodiment and embodiment 1 is that after the data to be imported is imported into the ineffective data table, it also includes determining whether the import of the data to be imported is normal:
[0060] S55, after the imported data is imported, the time for updating the system parameter table;
[0061] S56. If the time of the updated system parameter table is the current time, the data to be imported is imported normally, and the effective table and effective schema are recorded in the system parameter table;
[0062] S57. If the time of the updated system parameter table is not the current time, the data to be imported is re-imported.
[0063] This embodiment determines whether the import is normal by the time in the system parameter table, so that timely monitoring can be achieved. Once the time in the system parameter table is different from the current date, it is easy to find that the import of data from the previous day has failed.
[0064] The data import process in this application is as follows Figure 1 As shown, since each database only needs to create the first data table, the second data table and the system parameter table once to implement subsequent data import, Figure 1 This step is not shown.
[0065] Embodiment 3:
[0066] The difference between this embodiment and embodiment 1 is that the effective table and the effective schema are recorded in the system parameter table, including:
[0067] S421, setting different table names for the first data table and the second data table, and setting different numbers for the schemas where the first data table and the second data table are located;
[0068] S422, in the column of the table header "Effective Table" in the system parameter table, fill in the table names of the effective data tables in the first data table and the second data table;
[0069] S423, fill in the column of the effective schema in the table header of the system parameter table with the schema number of the effective data table;
[0070] S424, after each import is completed, the table name that previously existed in the column with the effective table header in the system parameter table is deleted, and the table name of another data table is filled in;
[0071] S425. In the column of the effective schema in the table header of the system parameter table, delete the schema number that previously existed and fill in the schema number of another data table.
[0072] This embodiment provides a clear management method for effective schema and effective table, making the modification of effective schema and effective table more intuitive and easy. In the scenario of this application where the effective schema and effective table need to be adjusted dynamically, by naming and numbering different data tables and schemas, and then using the system parameter table to manage them, the effective schema and effective table can be changed without stopping the system, thereby improving the flexibility and availability of the system. Clear naming and numbering make the maintenance and documentation of the database more convenient, which helps new staff members to quickly get started and existing staff members to work efficiently.
[0073] Embodiment 4:
[0074] Corresponding to the above method embodiment, this embodiment further provides a data import and query system based on dual schema, including:
[0075] A first acquisition unit, used to acquire data to be imported;
[0076] A first creation unit is used to create a first data table in a first schema and a second data table in a second schema, wherein the first data table and the second data table have the same structure, and headers of the first data table and the second data table correspond to contents of the data to be imported;
[0077] A second creation unit is used to create a system parameter table, wherein the system parameter table is used to record an effective schema and an effective table, wherein the effective table refers to a table in which the corresponding time of the last data in the first data table and the second data table is closest to the current time, and the effective schema refers to the schema in which the effective table is located;
[0078] The second acquisition unit is used to acquire the effective schema and the effective table through the system parameter table;
[0079] Import unit, used to import the data to be imported into the ineffective data table;
[0080] The query unit is used to obtain the effective table through the system parameter table after receiving the query request, and obtain the required query data from the effective table.
[0081] In some specific embodiments, the first acquiring unit includes:
[0082] Cyclic unit: used to create periodic tasks; receives the data to be imported at fixed time intervals, and each received data to be imported includes the data to be imported received last time and new content.
[0083] In some specific embodiments, the second acquiring unit includes:
[0084] An initial unit, used for initialization, when both the first data table and the second data table are empty, a data table is randomly selected as the effective table;
[0085] The recording unit is used to record the effective table and effective schema in the system parameter table.
[0086] In some specific embodiments, the introduction unit includes:
[0087] A splitting unit is used to split the data to be imported into multiple small files with fixed sizes;
[0088] The import subunit is used to import multiple small files in batches into an ineffective data table;
[0089] The recording unit is further used for recording, after the import of the data to be imported is completed, the data table into which the data to be imported is imported as a valid table in the system parameter table, and the schema where the valid table is located is recorded as a valid schema; recording another data table as an ineffective data table in the system parameter table, and recording the schema where the ineffective data table is located as an ineffective schema;
[0090] The first deleting unit is used to clear the data in the ineffective data table.
[0091] It should be noted that the recording unit in the second acquisition unit and the recording unit in the import unit are the same unit.
[0092] In some specific embodiments, the query unit includes:
[0093] An interface unit, used for receiving a query request;
[0094] The third acquisition unit is used to acquire the effective schema and effective table from the system record table;
[0095] The cache unit is used to load the data in the effective table through the i gnite cache;
[0096] A reading unit is used to read the data to be queried from the cache;
[0097] The second deleting unit is used to delete all the data in the cache after a preset time has passed.
[0098] In some specific embodiments, the introduction unit further comprises:
[0099] Update unit, used to update the system parameter table time after the import of the imported data is completed;
[0100] A judgment unit, used for recording the effective table and effective schema in the system parameter table when the time of the updated system parameter table is the current time, that the imported data is imported normally;
[0101] The return unit is used to re-import the data to be imported when the time of the updated system parameter table is not the current time.
[0102] In some specific embodiments, the recording unit includes:
[0103] A setting unit, used to set different table names for the first data table and the second data table, and set different numbers for the schemas where the first data table and the second data table are located;
[0104] The filling unit is used to fill in the table name of the effective data table in the first data table and the second data table in the column with the header "Effective Table" in the system parameter table; fill in the number of the schema to which the effective data table belongs in the column with the header "Effective Schema" in the system parameter table; after each import, the table name that existed previously in the column with the header "Effective Table" in the system parameter table is deleted, and the table name of another data table is filled in; the schema number that existed previously in the column with the header "Effective Schema" in the system parameter table is deleted, and the schema number to which another data table belongs is filled in.
[0105] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.
[0106] This application enables MYSQL to perform business operations normally in a database schema that requires a large number of query transactions, and at the same time supports data import processing of large files in another schema, avoiding jdbcException exceptions during business requests.
[0107] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art can easily think of changes or substitutions within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.
Claims
1. A data import and query method based on dual schema, characterized in that: include: Get the data to be imported; Create a first data table in a first schema, and create a second data table in a second schema, wherein the structures of the first data table and the second data table are the same, and the headers of the first data table and the second data table correspond to the contents of the data to be imported; Create a system parameter table, which is used to record the effective schema and effective table. The effective table refers to a table whose corresponding time of the last data in the first data table and the second data table is closest to the current time. The effective schema refers to the schema where the effective table is located. Obtain the effective schema and effective table through the system parameter table; Import the data to be imported into the ineffective data table; After receiving the query request, the effective table is obtained through the system parameter table, and the required query data is obtained from the effective table.
2. The dual-schema based data import and query method according to claim 1, characterized in that: Get the data to be imported, including: Create periodic tasks; The data to be imported is received at fixed time intervals, and the data to be imported received each time includes the data to be imported received last time and new content.
3. The dual-schema based data import and query method according to claim 1, characterized in that: Obtain the effective schema and effective table through the system parameter table, including: Initially, both the first data table and the second data table are empty tables, and a data table is randomly selected as the effective table; Record the effective table and effective schema in the system parameter table.
4. The dual-schema based data import and query method according to claim 1, characterized in that: Import the data to be imported into the ineffective data table, including: Split the data to be imported into multiple small files of fixed size; Importing multiple small files into an ineffective data table in batches; After the import of the data to be imported is completed, the data table that imports the data to be imported is recorded as the effective table in the system parameter table, and the schema where the effective table is located is recorded as the effective schema; After the imported data is imported, the other data table is recorded as an invalid data table in the system parameter table, and the schema where the invalid data table is located is recorded as an invalid schema; Clear the data in the ineffective data table.
5. The dual-schema based data import and query method according to claim 1, characterized in that: After receiving the query request, the effective table is obtained through the system parameter table, and the required query data is obtained from the effective table, including: receiving query requests; Get the effective schema and effective table from the system record table; Load the data in the effective table through the Ignite cache; Read the data to be queried from the cache; After the preset time, all data in the cache will be deleted.
6. The dual-schema based data import and query method according to claim 4, characterized in that: After importing the data to be imported into the invalid data table, it also includes judging whether the import of the data to be imported is normal: After the import of the data is completed, the time to update the system parameter table; If the time of the updated system parameter table is the current time, the data to be imported is imported normally, and the effective table and effective schema are recorded in the system parameter table; If the time of the updated system parameter table is not the current time, the data to be imported will be re-imported.
7. The dual-schema based data import and query method according to claim 3, characterized in that: Record the effective table and effective schema in the system parameter table, including: Set different table names for the first data table and the second data table, and set different numbers for the schemas where the first data table and the second data table are located; In the column of the table header "Effective Table" in the system parameter table, fill in the table names of the effective data tables in the first data table and the second data table; In the column of the effective schema in the table header of the system parameter table, fill in the schema number of the effective data table; After each import is completed, delete the existing table name in the column of the effective table in the system parameter table and fill in the table name of another data table; In the system parameter table, delete the existing schema number in the column with the effective schema header, and fill in the schema number of another data table.
8. A data import and query system based on dual schema, characterized in that: include: A first acquisition unit, used to acquire data to be imported; A first creation unit is used to create a first data table in a first schema and a second data table in a second schema, wherein the first data table and the second data table have the same structure, and headers of the first data table and the second data table correspond to contents of the data to be imported; A second creation unit is used to create a system parameter table, wherein the system parameter table is used to record an effective schema and an effective table, wherein the effective table refers to a table in which the corresponding time of the last data in the first data table and the second data table is closest to the current time, and the effective schema refers to the schema in which the effective table is located; The second acquisition unit is used to acquire the effective schema and the effective table through the system parameter table; Import unit, used to import the data to be imported into the ineffective data table; The query unit is used to obtain the effective table through the system parameter table after receiving the query request, and obtain the required query data from the effective table.
9. The dual-schema based data import and query system according to claim 8, characterized in that: The first acquisition unit includes: Cyclic unit: used to create periodic tasks; receives the data to be imported at fixed time intervals, and each received data to be imported includes the data to be imported received last time and new content.
10. The dual-schema based data import and query system according to claim 8, characterized in that: The second acquisition unit includes: An initial unit, used for initialization, when both the first data table and the second data table are empty, a data table is randomly selected as the effective table; The recording unit is used to record the effective table and effective schema in the system parameter table.
11. The dual-schema based data import and query system according to claim 8, characterized in that: The import unit includes: A splitting unit is used to split the data to be imported into multiple small files with fixed sizes; The import subunit is used to import multiple small files in batches into an ineffective data table; The recording unit is further used for recording, after the import of the data to be imported is completed, the data table into which the data to be imported is imported as a valid table in the system parameter table, and the schema where the valid table is located is recorded as a valid schema; recording another data table as an ineffective data table in the system parameter table, and recording the schema where the ineffective data table is located as an ineffective schema; The first deleting unit is used to clear the data in the ineffective data table.
12. The dual-schema based data import and query system according to claim 8, characterized in that: The query unit includes: An interface unit, used for receiving a query request; The third acquisition unit is used to acquire the effective schema and effective table from the system record table; Cache unit, used to load data from the effective table through Ignite cache; A reading unit is used to read the data to be queried from the cache; The second deleting unit is used to delete all the data in the cache after a preset time has passed.
13. The dual-schema based data import and query system according to claim 11, characterized in that: The import unit also includes: Update unit, used to update the system parameter table time after the import of the imported data is completed; A judgment unit, used for recording the effective table and effective schema in the system parameter table when the time of the updated system parameter table is the current time, that the imported data is imported normally; The return unit is used to re-import the data to be imported when the time of the updated system parameter table is not the current time.
14. The dual-schema based data import and query system according to claim 10, characterized in that: The recording unit includes: A setting unit, used to set different table names for the first data table and the second data table, and set different numbers for the schemas where the first data table and the second data table are located; The filling unit is used to fill in the table name of the effective data table in the first data table and the second data table in the column with the header "Effective Table" in the system parameter table; fill in the number of the schema to which the effective data table belongs in the column with the header "Effective Schema" in the system parameter table; after each import, the table name that existed previously in the column with the header "Effective Table" in the system parameter table is deleted, and the table name of another data table is filled in; the schema number that existed previously in the column with the header "Effective Schema" in the system parameter table is deleted, and the schema number to which another data table belongs is filled in.
