Data migration method and device, equipment and medium

Abstract

The invention relates to the technical field of big data and the field of financial science and technology, and discloses a data migration method, which comprises the following steps: acquiring to-be-migrated data, and filling the to-be-migrated data into a data file; obtaining an SQL statement template containing placeholders, and analyzing and identifying the placeholders in the template; establishing a mapping relationship between the placeholders and fields in the data file; combining the SQL grammar in the SQL statement template with the data in the data file to generate a complete SQL statement; adding an output position in the data file, and displaying the generated SQL statement; and copying the SQL statement to a database processing module for execution, and completing data migration and updating. According to the method, the SQL statement is automatically generated, and the to-be-migrated data is combined with the SQL statement template, so that the workload of manually writing the SQL statement is reduced, the error risk in the operation is reduced, the dependence on the file import permission of the database is not needed, the data migration process is simplified, and the data migration efficiency and accuracy are improved; and meanwhile, the consistency and the integrity of the data are ensured.

Description

Technical Field

[0001] The present invention relates to the fields of big data technology and financial technology, and in particular to a data migration method, device, equipment and storage medium. Background Art

[0002] In the financial field, data migration is a key operation that banks, securities, insurance and other institutions often face in scenarios such as business system upgrades, data center migration, compliance audits and big data analysis. The amount of data in the financial system is large and diverse, and the accuracy and security of the data are extremely high. Therefore, how to achieve data migration efficiently and accurately has become a core issue.

[0003] At present, common data migration methods in the financial industry mainly include the following categories:

[0004] Full data migration: Applicable to the initial deployment of the system or the overall migration of the data center, migrating all data in a system to a new database system.

[0005] Incremental data migration: usually used for daily business data synchronization or cross-system data updates, ensuring data consistency while migrating only changed data.

[0006] Abnormal data repair: mainly for abnormal situations in data update, through specific tools and SQL statements to quickly repair the problem data.

[0007] However, in the data migration process in the financial industry, database flushing is one of the common implementation methods, especially in the initialization of basic data and abnormal data update scenarios. This solution usually imports the data to be refreshed into a temporary table, and then migrates the data to the formal table through SQL statements.

[0008] The databases of many financial institutions have strict restrictions on file import permissions, which makes it impossible for operators to directly import file data into the database temporary table. This requires the use of third-party tools (such as PLSQL, etc.) to convert the data into SQL statements and then execute the import, which greatly increases the complexity of the operation.

[0009] For specific business scenarios, the database cannot directly generate the required SQL statements. Financial data migration often involves complex logic and business rules, which requires operators to manually write a large number of SQL statements. This is not only inefficient, but also prone to human errors. Especially when large amounts of data are involved, incorrect SQL statements may cause serious financial losses or data loss. Summary of the invention

[0010] The main purpose of the present invention is to provide a data migration method, device, equipment and storage medium, aiming to solve the technical problems that the prior art relies on manual writing of SQL statements during data migration, the operation is cumbersome and error-prone, and there is a lack of automated tools to efficiently and accurately generate and execute SQL statements.

[0011] To achieve the above object, the present invention provides a data migration method, comprising:

[0012] Get the data to be migrated;

[0013] Fill the data to be migrated into a data file;

[0014] Get the SQL statement template containing placeholders;

[0015] Parsing the SQL statement template and identifying placeholders in the SQL statement template;

[0016] Establishing a mapping relationship between the placeholder and the corresponding field in the data file;

[0017] Based on the mapping relationship, the SQL syntax in the SQL statement template is combined with the data in the data file to generate a complete SQL statement;

[0018] Adding an output location in the data file, and displaying the complete SQL statement according to the output location;

[0019] The complete SQL statement is copied to the database processing module for execution to complete data migration and update.

[0020] Furthermore, to achieve the above object, the present invention provides a data migration device, comprising:

[0021] A data acquisition module, used to acquire data to be migrated;

[0022] A data file management module, used to fill the data to be migrated into a data file;

[0023] SQL template management module, used to obtain SQL statement templates containing placeholders;

[0024] An SQL template parsing module, used to parse the SQL statement template and identify the placeholders in the SQL statement template;

[0025] A mapping relationship management module, used to establish a mapping relationship between the placeholder and the corresponding field in the data file;

[0026] An SQL generation module is used to combine the SQL syntax in the SQL statement template with the data in the data file based on the mapping relationship to generate a complete SQL statement;

[0027] An SQL display module, used to add an output position in the data file and display the complete SQL statement according to the output position;

[0028] The database execution module is used to copy the complete SQL statement to the database processing module for execution, thereby completing data migration and update.

[0029] Furthermore, to achieve the above-mentioned purpose, the present invention also provides a computer device, which includes a memory, a processor, and a data migration program stored in the memory and executable on the processor, and the data migration program, when executed by the processor, implements the steps of the data migration method described above.

[0030] Furthermore, to achieve the above-mentioned purpose, the present invention also provides a computer-readable storage medium, on which a data migration program is stored, and when the data migration program is executed by a processor, the steps of the data migration method described above are implemented.

[0031] Beneficial effects: The present invention relates to the fields of big data technology and financial technology, and discloses a data migration method, which obtains data to be migrated and fills it into a data file; obtains an SQL statement template containing placeholders, parses and identifies the placeholders in the template; establishes a mapping relationship between the placeholders and the fields in the data file; combines the SQL syntax in the SQL statement template with the data in the data file to generate a complete SQL statement; adds an output location in the data file to display the generated SQL statement; copies the SQL statement to the database processing module for execution to complete data migration and update. The present invention reduces the workload of manually writing SQL statements and the risk of errors in operations by automatically generating SQL statements and combining the data to be migrated with the SQL statement template. It does not need to rely on the file import permissions of the database, simplifies the process of data migration, improves the efficiency and accuracy of data migration, and ensures the consistency and integrity of the data. It is suitable for processing the migration and update of large quantities of data. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0033] Figure 1 A schematic diagram of an application environment of a data migration method in an embodiment of the present invention;

[0034] Figure 2 A schematic diagram of a flow chart of an embodiment of a data migration method of the present invention;

[0035] Figure 3 A schematic diagram of functional modules of a preferred embodiment of a data migration device of the present invention;

[0036] Figure 4 A schematic diagram of the structure of a computer device in one embodiment of the present invention;

[0037] Figure 5 FIG. 4 is another schematic diagram of the structure of a computer device in one embodiment of the present invention. DETAILED DESCRIPTION

[0038] It should be understood that the specific embodiments described herein are only used to explain the present invention, and are not used to limit the present invention.

[0039] The data migration method provided by the embodiment of the present invention can be applied in Figure 1 In the application environment, the user end communicates with the server end through the network. The server end can obtain the data to be migrated through the user end and fill it into the data file; obtain the SQL statement template containing the placeholder, parse and identify the placeholder in the template; establish a mapping relationship between the placeholder and the field in the data file; combine the SQL syntax in the SQL statement template with the data in the data file to generate a complete SQL statement; add an output position in the data file to display the generated SQL statement; copy the SQL statement to the database processing module for execution to complete data migration and update. The present invention reduces the workload of manually writing SQL statements and the risk of errors in operation by automatically generating SQL statements and combining the data to be migrated with the SQL statement template. It does not need to rely on the file import permission of the database, simplifies the process of data migration, improves the efficiency and accuracy of data migration, and ensures the consistency and integrity of the data. It is suitable for processing the migration and update of large quantities of data. Among them, the user end can be but not limited to various personal computers, laptops, smart phones, tablet computers and portable wearable devices. The server end can be implemented with an independent server or a server cluster composed of multiple servers. The present invention is described in detail below through specific embodiments.

[0040] See also Figure 2 , Figure 2 This is a flow chart of an embodiment of a data migration method provided by the present invention. It should be noted that although a logical sequence is shown in the flow chart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0041] like Figure 2 As shown, the data migration method proposed by the present invention includes the following steps:

[0042] S10, obtaining data to be migrated;

[0043] In this embodiment, the data to be migrated can be stored in a variety of data sources, including relational databases, non-relational databases, file systems (such as CSV, Excel, etc.), data warehouses, etc. In order to achieve data migration, it is first necessary to identify the type of data source and establish a connection. When identifying the data source, a communication connection can be established with the data source by providing an interface or protocol (such as JDBC, ODBC, FTP, etc.) to ensure that the required data to be migrated can be accessed from the data source.

[0044] Through configuration files, system parameters or user interfaces, users can select or enter the source of the data to be migrated, including database address, user name, password, table name, etc. Use a database access interface (such as JDBC or ODBC) or file reading tool to connect to the data source and obtain an initial data list for subsequent operations.

[0045] After identifying the data source, you need to analyze the data to be migrated to understand the data type and structure. Different data sources may store different types of data (such as text, values, dates, etc.), and the data structure (such as table structure, field definition, index information, etc.) also needs to be identified. This information is crucial in the subsequent data mapping and SQL statement generation process.

[0046] The structure and field information of the data table can be extracted through metadata query tools (such as INFORMATION_SCHEMA or SHOW TABLES commands in the database system). In the file system, data types and field information can be obtained by parsing information such as file headers and data row formats. Data analysis tools can also automatically identify the data type of a field, such as integers, floating point numbers, strings, etc.

[0047] The data source may contain a large amount of data, and usually it is not necessary to migrate all the data, so the data to be migrated needs to be selected and filtered. Users can define filtering conditions (such as time range, specific field values, row number limit, etc.) according to business needs to decide which data needs to be migrated.

[0048] Users can enter filtering conditions in the operation interface or filter data through SQL query statements. Common filtering conditions include WHERE clauses, time range restrictions, specific field value matching, etc. For file-type data, data can be selectively read through technical means such as row filtering and field matching.

[0049] Example description: In the financial field, data migration operations are widely used in business scenarios such as banks and securities trading systems. For example, when a bank system is migrating account information data, the system can automatically connect to the database, identify the account table structure and field information, and filter out account data with transaction records within a specific time range. Through automated tools, the system can directly obtain eligible account data and migrate it to a new database, reducing manual operations and ensuring data accuracy.

[0050] Through the above steps, the system can automatically obtain the data to be migrated, support multiple data sources and data structures, and allow users to flexibly screen and filter the data to be migrated. It effectively reduces manual intervention, avoids the complexity of manual data import, improves the flexibility and efficiency of data migration, and is particularly suitable for handling large-scale financial data migration scenarios, ensuring the accuracy and integrity of data migration.

[0051] S20, filling the data to be migrated into a data file;

[0052] In this embodiment, before filling the data to be migrated into the data file, it is necessary to determine which file format to use to store the data. Common data file formats include CSV, Excel, JSON, XML, etc., and each format has its advantages. For example, CSV and Excel are suitable for storing structured data, which is easy to view and edit; while JSON and XML are more suitable for nested data or complex data structures. The specific choice is determined by business needs and the compatibility of the target system.

[0053] The system provides a variety of data file format options for users to choose from. Users can choose the appropriate format according to their needs. The system automatically generates an empty file in the selected format and creates a header or structure definition for subsequent data writing.

[0054] The data to be migrated may come from different data sources, and the structure and format of the data may be inconsistent with the target file format. In this case, the data structure of the data to be migrated needs to be converted to ensure that the data can be correctly stored in the target data file. For example, map the table structure in the database to the column in Excel, or convert the nested data structure in the database to the key-value pair in JSON.

[0055] Use the data mapping tool to match the source data structure with the structure in the target file format. For database tables, each field can be mapped to a column in the file; for JSON, each field can be converted to a key-value pair. The system automatically converts the data format to ensure that the data is in the correct and complete format when it is written to the file.

[0056] Once the data structure conversion is completed, the data to be migrated will be written into the data file in the target format. The system should ensure that each data record corresponds to the data row (or key-value pair) in the target file to ensure data integrity and consistency.

[0057] Use data writing tools (such as Pandas, Apache POI, CSV writing tools, etc.) to write data into the file line by line. For Excel files, use library functions (such as openpyxl) to fill data into specified cells. The system traverses each record in the data source to be migrated through an automatic loop mechanism and writes its corresponding data to the corresponding position of the file.

[0058] After writing is completed, the system needs to verify the data file to ensure that all the data to be migrated has been successfully written and no omissions or errors have occurred. Data file verification includes checking the integrity of the file format, the integrity of the data records, and ensuring that each piece of data matches the target structure.

[0059] The system automatically scans the generated file, checks whether the data records correspond to the source data one by one, and verifies whether each record is written successfully. Verify whether the file structure is consistent with the target format, for example, check whether the columns and rows in the Excel file are correctly filled, or check whether the key-value pairs in the JSON file are complete. Users can also manually view the file content through the interface to confirm the data writing results.

[0060] Example: In financial institutions, data migration often requires exporting large amounts of data such as customer information and transaction records from old systems and filling them into Excel or CSV files for subsequent processing. For example, when a bank is migrating account data, the system extracts account information from the database, including fields such as account number, account balance, and customer name, and fills these data into Excel files line by line, with each field corresponding to a column in the Excel file.

[0061] Through the above steps, the system can automatically fill the data to be migrated into structured data files, such as Excel or CSV format, according to the needs. It not only realizes the seamless conversion of source data to target data files, but also ensures the consistency and integrity of data structure. Users can easily select the data file format and view and edit the generated data files, which greatly improves the flexibility of data migration and reduces the risk of human intervention.

[0062] S30, obtaining a SQL statement template including a placeholder;

[0063] In this embodiment, the SQL statement template is a predefined SQL statement, in which part of the content is replaced by placeholders (such as? or named placeholders such as @field_name), indicating data items that need to be dynamically replaced. This templated SQL statement allows the data to be migrated to be filled into the placeholder position during actual execution, thereby realizing automatic SQL generation.

[0064] The system provides users with an interface or configuration file for defining SQL statement templates. Users can enter SQL templates in the interface and use placeholders to represent dynamic data. Example template: UPDATE customers SETbalance=? WHERE account_id=?; where? represents the account balance and account ID to be migrated.

[0065] The system needs to provide an input channel that allows users to enter SQL statement templates containing placeholders. Users can define these templates through a graphical user interface (GUI) or a configuration file. The input process should be flexible and support multiple SQL syntaxes (such as INSERT, UPDATE, DELETE, etc.).

[0066] The user can enter the SQL template through the system's SQL template input interface or by uploading a configuration file. When entering, the system supports placeholders (such as? or @) to indicate the data fields that need to be replaced. The user can directly enter the SQL template through the text box in the interface, or upload a predefined SQL template file (such as a .sql file).

[0067] In order to map and generate SQL statements, the system needs to parse the input SQL statement template and identify the placeholders. The parsing process includes: identifying the SQL syntax structure and identifying the position and number of placeholders.

[0068] The system has a built-in SQL parser to parse the SQL template entered by the user. The parser extracts the placeholders in the SQL statement and records their positions in the template. The system parses the SQL statement through regular expressions or a special parsing library to extract the placeholders and their corresponding positions.

[0069] To ensure that there are no errors when the SQL statement template is actually generated and executed, the system needs to verify the syntax and logic of the SQL template, including: verifying whether the number and position of placeholders are correct, checking the integrity of the SQL syntax, and ensuring that the template can be executed correctly.

[0070] After the user submits the SQL template, the system automatically performs template validation, including checking the correctness of the SQL syntax and the number of placeholder matches. For common SQL syntax errors or inconsistencies, the system prompts the user to modify them. Example: When the system finds that UPDATE customers SET balance=? WHERE account_id= is missing the second placeholder, it prompts the user that the SQL template is incomplete.

[0071] By providing a flexible SQL statement template input method and supporting automatic parsing and verification of placeholders, the process of writing complex SQL statements is simplified. The standardization and automatic processing of user-defined SQL templates are realized, so that the system can automatically generate corresponding SQL statements based on placeholders, which greatly reduces the workload of manual writing and modifying SQL statements and reduces the risk of errors.

[0072] S40, parsing the SQL statement template and identifying placeholders in the SQL statement template;

[0073] In this embodiment, the SQL statement template is a dynamic SQL code containing placeholders. The purpose of parsing this code is to identify the fixed part and the dynamic part (placeholders) therein so that the placeholders can be replaced with actual data later. Parsing requires analyzing the structure of SQL, including identifying keywords, table names, field names, etc., while ensuring that the original logical structure of SQL is not destroyed.

[0074] The system integrates an SQL parser that can perform lexical and grammatical analysis on the SQL templates input by the user, identify various parts in the SQL, including SELECT, INSERT, UPDATE and other operations, and extract the table name, field name and placeholder position. Use regular expressions (such as identifying? or @field_name) to parse placeholders and mark their specific positions in the SQL template.

[0075] Placeholders are dynamic parts in SQL templates, indicating that specific data needs to be filled in when SQL is executed. Common placeholder forms include? or named placeholders such as @field_name, which can represent values ​​or conditions in SQL statements. The system needs to identify all placeholders from the template to ensure that each placeholder can be correctly replaced with actual data in subsequent steps.

[0076] The system uses regular expressions or SQL parsing tools to identify placeholders in SQL templates and records the position, quantity, and type (such as numeric type, string type, etc.) of the placeholders. Example: For the SQL template UPDATE accounts SET balance = ? WHERE account_number = ? , the system can identify two ? placeholders and record their positions in the balance field and account_number condition respectively.

[0077] For a named placeholder (such as @balance), the system extracts the placeholder through pattern matching and saves its corresponding field name.

[0078] The number and order of placeholders must match the data mapped later to ensure that the generated SQL statement can correctly replace the dynamic data. Therefore, when parsing the SQL template, the system must not only identify the position of the placeholders, but also record the number and order of each placeholder so that accurate replacement can be made when generating SQL statements.

[0079] During the parsing process, the system will number the identified placeholders in the order of appearance and logically associate them with other parts of the SQL template to ensure that the data can be replaced in order later. By counting placeholders, the system ensures that the number of placeholders in the template is consistent with the number of data fields in the subsequent data mapping, avoiding SQL execution errors caused by data mismatch.

[0080] While parsing the placeholders, the system also needs to perform a syntax check on the SQL template to ensure the correctness of the SQL template. The purpose of the syntax check is to prevent the SQL statement from being executed due to spelling errors or non-standard use of placeholders in the SQL template.

[0081] After identifying the placeholders, the system will perform a basic syntax check on the SQL template to ensure that the keywords, punctuation marks, table names, field names, and placeholders in the SQL statement comply with the SQL standard syntax. Example: If the SQL template is UPDATEaccounts SET balance=? WHERE account_number=?, the system will check whether the use of keywords such as UPDATE, SET, and WHERE complies with the specification, and also check whether the field names such as balance and account_number match the table structure.

[0082] By parsing the SQL statement template, the placeholders in the SQL template can be accurately identified, and their position, quantity and order can be recorded. This ensures that when generating SQL statements, the system can accurately replace the placeholders with actual data, avoiding execution problems caused by manual replacement errors. By parsing the SQL template and identifying the placeholders, the system can automatically handle complex SQL statement generation tasks, significantly reducing manual operations and improving the efficiency and accuracy of SQL generation. It is particularly suitable for scenarios such as batch processing, migration and update of financial data.

[0083] S50, establishing a mapping relationship between the placeholder and the corresponding field in the data file;

[0084] In this embodiment, in the SQL statement template, there are placeholders for representing data that need to be dynamically replaced, while in the data file, each field (column) stores the data that actually needs to be filled in the SQL statement. Therefore, establishing a mapping relationship between the placeholder and the field in the data file is a key step. The mapping relationship will determine which field in the data file each placeholder should obtain data from, and ensure that the placeholder can be accurately replaced when generating the SQL statement.

[0085] After parsing the placeholders in the SQL statement template, the system scans the fields (column names) in the data file and associates each placeholder with the corresponding field according to the field name, order or other specific rules. The system prompts the user to manually select the placeholders in the SQL template to match the fields in the data file, or to achieve automatic correspondence between placeholders and fields through automatic mapping (such as field name or position matching).

[0086] The system needs to establish a mapping relationship between placeholders and data file fields according to certain rules. These rules can be based on the name of the field, the order of the field, or the type of the field. For example, the field name matching method can map the placeholder name in the SQL template to the column name of the data file; the order matching method corresponds to the relative position of the placeholder and the field.

[0087] If the placeholder in the SQL statement template is a named placeholder (such as @balance), the system will automatically search for the field name in the data file, and automatically establish a mapping relationship if the field name is consistent with the placeholder. If the SQL template uses anonymous placeholders (such as?), the system will map the order of the placeholders in the SQL template to the order of the fields in the data file. The system can also check whether the expected data type of the placeholder (such as number, string) matches the field type in the data file to further ensure the accuracy of the mapping.

[0088] After the mapping relationship is established, the system needs to verify it to ensure that each placeholder successfully corresponds to a field in the data file and that the two are consistent in type and quantity. If any unmatched or incorrect mapping relationship is found, the system should prompt the user to make corrections.

[0089] The system will check whether the number of placeholders is consistent with the number of fields in the data file, and prompt the user to correct redundant or missing mappings. The system will check whether the data type of the placeholder (such as numeric or text) is compatible with the data type of the corresponding field in the data file to ensure that the generated SQL statement can be executed correctly.

[0090] After establishing the mapping relationship between the placeholder and the data file field, the system needs to save these mapping relationships for use in the subsequent SQL statement generation process. The stored mapping relationship may include information such as the placeholder, data file field name, field location, and data type.

[0091] The system stores the mapping relationship in memory or database, recording the field name, field order, and field type corresponding to each placeholder. The mapping relationship can be exported as a configuration file for reuse in subsequent migration or update tasks.

[0092] By automatically or manually establishing a mapping relationship between placeholders and fields in data files, it is ensured that the placeholders in the SQL template can accurately correspond to the actual data. By establishing a mapping relationship, the system can automatically generate SQL statements, avoiding the tedious operation of manually replacing placeholders and improving operational efficiency. In addition, the verification and storage functions of the mapping relationship ensure the accuracy and consistency of SQL generation and reduce possible errors during execution.

[0093] S60, based on the mapping relationship, combining the SQL syntax in the SQL statement template with the data in the data file to generate a complete SQL statement;

[0094] In this embodiment, in the previous steps, the system has established a mapping relationship between the placeholders in the SQL template and the fields in the data file. In order to generate a complete SQL statement, it is first necessary to obtain the actual data corresponding to each placeholder from the data file line by line according to the mapping relationship. The value of each line of data will replace the corresponding placeholder in the SQL template to generate an actual executable SQL statement.

[0095] The system reads the content of the data file line by line, and obtains the data of the corresponding column according to the mapping relationship between the placeholder and the field. For example, if the placeholder ? is mapped to the field balance, the system will read the data value of the row from the balance column of the data file. The system stores the data corresponding to each placeholder in a variable, ready to replace the placeholder in the SQL template.

[0096] Once the actual data corresponding to each placeholder is obtained, the system fills the data into the placeholder position of the SQL template to generate a complete SQL statement. Placeholders can be anonymous (such as?) or named (such as@balance). During the replacement process, the system also needs to ensure that the data type is correct. For example, the field value of the string type needs to be enclosed in single quotes, while the field of the numeric type does not need to be.

[0097] The system scans the SQL template, finds the position of each placeholder, and replaces the values ​​previously obtained from the data file one by one. For numeric data, the value is directly filled into the placeholder position; for string data, the system automatically adds single quotes. For example: UPDATE customers SET balance =? WHERE customer_id =?, if balance = 1000 and customer_id = 12345, the generated SQL statement is: UPDATE customers SET balance = 1000WHERE customer_id = 12345; for string fields such as name, the system automatically generates a form similar to 'John Doe'. The system will process special characters in SQL syntax, such as escaped single quotes or double quotes, to ensure that SQL statements can be executed correctly.

[0098] After the SQL statement is generated, the system needs to perform syntax and logic verification on it to ensure that the generated SQL statement is in the correct format and meets the requirements of the target database. This includes checking whether the generated SQL complies with the database's syntax rules, whether all placeholders are replaced, and whether the data type matches the field.

[0099] The system can verify whether the generated SQL statements conform to the standard SQL syntax through the built-in SQL syntax checker to avoid execution failures caused by incorrect SQL generation. The system can also check whether the SQL statements can be successfully executed by simulating their execution, for example, checking whether the data type conversion is correct.

[0100] For each row of data, the system will generate a complete SQL statement according to the above steps. If the data file contains multiple rows of records, the system needs to batch process each row and generate multiple corresponding SQL statements. The generated SQL statements can be directly used for database update, insert or delete operations.

[0101] The system reads the data file line by line and generates the corresponding SQL statement based on each line of data. For example, if there are three lines of records in the data file, the system will generate three SQL statements respectively and store them in a list or file for users to copy or directly execute. The generated SQL statements can be stored in a file or displayed in the system interface for users to view, copy or further operate.

[0102] By combining the SQL statement template with the actual data in the data file, a complete SQL statement can be automatically generated, reducing the workload of manually writing SQL statements and improving generation efficiency. By establishing a mapping relationship for placeholders and automatically replacing them, the system can efficiently process batch data and generate directly executable SQL statements, avoiding errors in manual replacement. At the same time, through automatic type processing and SQL verification, it ensures that the generated SQL statements can correctly reflect business needs when executed, which is particularly suitable for scenarios of large-scale data migration, update or repair in the financial field.

[0103] S70, adding an output position to the data file, and displaying the complete SQL statement according to the output position;

[0104] In this embodiment, after generating complete SQL statements, the system needs to find a suitable output location for these SQL statements so as to display them to the user. The output location can be a column in the data file (such as in an Excel or CSV file) or a separate area (such as a new field in a JSON file). The system needs to add a new output location in the data file according to user needs or preset rules to store and display the generated SQL statements.

[0105] The system creates a new column in the data file (for table files, such as Excel, CSV, etc.), which is used to store and display the SQL statement generated for each record. For files in other formats (such as JSON), the system can add a new field to each record, which is used to store the generated SQL statement.

[0106] After determining the output location, the system needs to associate each generated SQL statement with the corresponding record in the data file. Usually, one SQL statement is generated for each row of records, so the system will place the generated SQL statement in the output column or output area of ​​that row. In this way, users can intuitively view the SQL statement corresponding to each data.

[0107] After the system generates SQL statements line by line, it places them in the output columns of the corresponding lines in the data file. For example, for a data file containing customer information, the generated SQL statement can be placed in the last column of the line corresponding to the customer record. If it is a JSON file, the system can add a new field (such as "sql_statement") for each record and use the generated SQL statement as the value of this field.

[0108] By adding an output location in the data file, the system can clearly display the SQL statement corresponding to each record. Users can directly view the SQL statement generated by each record by opening the data file, and copy, execute or perform other operations as needed. The design of the display location needs to ensure the readability and operability of the data.

[0109] In Excel files, users can open the file and see the last column of each row showing the generated SQL statement. For example, for a customer data file, the last column of each row may show something like: UPDATEcustomers SET balance=1000WHERE customer_id=12345; in JSON files, users can see the generated SQL statement stored in the "sql_statement" field of each record. The system can support users to copy SQL statements or export them to other systems.

[0110] In order to meet the needs of different users, the system can provide the function of customizing the output location, allowing users to select or configure the output location according to their own workflow. For example, users can choose to output SQL statements to a new file, to a specific column in a specified file, or to output them as additional information to the notes area of ​​each record.

[0111] The system allows users to select or configure the output location in the interface, such as selecting the last column of the table file as the output location of the SQL statement, or creating a new file for each record to save the generated SQL statement. Users can specify different output file formats, such as CSV, Excel, or JSON, and the system automatically adjusts the output location based on the user's selection.

[0112] By adding output locations in the data file and displaying the generated SQL statements, the SQL statements generated by each data can be displayed intuitively, making it convenient for users to view, copy or execute in batches. The system supports flexible output location configuration. Whether adding new columns in table files (such as Excel, CSV) or adding new fields in JSON files, users can choose the appropriate output method according to specific needs. This function greatly improves the user's operating efficiency when processing a large number of SQL statements.

[0113] S80, copying the complete SQL statement to the database processing module for execution, completing data migration and update.

[0114] In this embodiment, after the system generates a complete SQL statement, it is necessary to provide a copy function to facilitate the user to extract the generated SQL statement from the data file or output interface for subsequent database operations. The copy operation should quickly and accurately copy the complete SQL statement to ensure that the user can directly paste it into the database processing module for execution.

[0115] The system provides a "Copy" button, and users can copy the generated SQL statements with one click. The system will store the SQL statements in the clipboard for users to paste directly into database management tools (such as SQL client, PLSQL, MySQL Workbench, etc.). The batch copy function is supported, and users can copy multiple SQL statements at one time, which is convenient for use in batch data migration or update.

[0116] After the user copies the SQL statement, it needs to be pasted into the database processing module. The database processing module refers to the tool or platform used to execute SQL statements, and common ones include SQL client tools, database command line interfaces, etc. After the user pastes the SQL statement into the processing module, it can be prepared to execute the operation.

[0117] The user opens the SQL window in the database management system and pastes the copied SQL statement into it. For batch operations, the user can choose to paste multiple SQL statements and execute multiple SQL operations at one time.

[0118] The system can provide users with guidance on how to paste and execute the copied SQL statements to ensure that users can operate correctly.

[0119] After the SQL statements are pasted into the database processing module, users can execute these SQL statements to complete data migration or update operations. During the execution process, the SQL statements will perform corresponding operations on the database according to their types (such as INSERT, UPDATE, DELETE), thereby writing or updating the data to be migrated into the database table.

[0120] When the user clicks the "Execute" button in the database management tool, the system will execute the SQL statement and complete the operation on the database. The system can record the execution log, including the executed SQL statement, execution time, number of rows affected by the operation, etc., to facilitate subsequent data review or problem troubleshooting.

[0121] To ensure the success of data migration or update operations, the system needs to verify the execution results after executing the SQL statement. The purpose of verification is to check whether the SQL statement is successfully executed, whether the expected number of data rows is correctly affected, and whether the data in the database is correctly updated.

[0122] After executing the SQL statement, the system returns the execution result, including the success or failure status, the number of rows affected, and the error message (if any). Users can use this information to confirm whether the operation is successful. The system can provide a comparison tool that allows users to compare the executed data with the source data to ensure the correctness of the migration or update. For example, users can query a record in the database to confirm whether its value is consistent with the updated value in the SQL statement.

[0123] After the SQL statement is executed, the system can record the execution log and save the execution process and results of the SQL statement. These log information are of great significance for tracing the execution history, troubleshooting problems, and ensuring data integrity and accuracy.

[0124] The system automatically generates execution logs, recording the execution time of each SQL statement, the number of rows affected by the operation, the execution status (success or failure), error information (if any), etc. The logs can be stored in the database or exported as log files for users to view. Users can query or filter operation results through logs to ensure that each data migration or update step is executed correctly and keep operation records.

[0125] Example: In a scenario where a bank's customer accounts are updated in batches, the operator needs to execute the generated SQL statements into the database to update the customer's account balance. The generated SQL statements may include:

[0126] UPDATE accounts SET balance=1500WHERE account_id=12345;

[0127] UPDATE accounts SET balance=2000WHERE account_id=67890;

[0128] The operator clicks the "Copy" button to copy these SQL statements to the clipboard, then pastes them into the database management tool and executes the SQL statements. After execution, the system shows that two records have been successfully updated and returns the execution log for the user to view. Through this automated SQL statement generation and execution method, the bank can efficiently and securely update large quantities of customer data, avoiding the risk of errors in manual operations and ensuring the accuracy and traceability of account balance updates.

[0129] The operation process of data migration and update is simplified by copying and executing SQL statements. SQL statements can be quickly generated and pasted into the database processing module for execution, avoiding the complexity and error risk of manually writing SQL statements. The system also supports batch copying and execution, which is suitable for large-scale data operation scenarios. The execution result verification and logging functions ensure the accuracy and traceability of data migration and update.

[0130] The present invention relates to the fields of big data technology and financial technology, and discloses a data migration method, which obtains data to be migrated and fills it into a data file; obtains an SQL statement template containing placeholders, parses and identifies the placeholders in the template; establishes a mapping relationship between the placeholders and the fields in the data file; combines the SQL syntax in the SQL statement template with the data in the data file to generate a complete SQL statement; adds an output location in the data file to display the generated SQL statement; copies the SQL statement to a database processing module for execution, and completes data migration and update. The present invention automatically generates SQL statements and combines the data to be migrated with the SQL statement template, thereby reducing the workload of manually writing SQL statements, reducing the risk of errors in operations, and does not need to rely on the file import authority of the database, simplifying the process of data migration, improving the efficiency and accuracy of data migration, and ensuring the consistency and integrity of the data, and is suitable for processing the migration and update of large quantities of data.

[0131] In one embodiment, the above S10 includes:

[0132] S101, scanning data in a data source to identify data types and data structures that need to be migrated;

[0133] S102, determining a corresponding data access interface according to the identified data type and data structure;

[0134] S103: Acquire the data to be migrated through the data access interface.

[0135] In this embodiment, the first step of data migration is to obtain the data to be migrated from the specified data source. In this process, the system needs to scan the data source, identify and extract the specific data type and data structure to be migrated. The data source can be a relational database, a non-relational database, a file system (such as CSV, Excel, etc.) or a data warehouse. Data types may include numeric types, string types, date types, etc., and data structures generally refer to the organizational form of tables, records or file formats.

[0136] The system connects to the data source and scans the available data tables, fields, and records through API or database query. Metadata (such as the database's INFORMATION_SCHEMA or file header information) is used to identify the available data types (such as integer, floating point, string) and data structures (such as tables, tree structures) in the data source. For example, the system can obtain a list of fields in a database table and their corresponding data types through SQL query, such as account_id (integer), balance (floating point), etc.

[0137] Once the data types and structures that need to be migrated in the data source are identified, the system needs to select the appropriate data access interface based on this information. Different data sources may use different access interfaces, such as JDBC, ODBC for relational databases, REST API or FTP for file systems. Therefore, the system needs to dynamically select or configure the correct access interface to ensure that data can be obtained smoothly.

[0138] The system automatically selects or prompts the user to select the appropriate data access interface based on the identified data type and structure. For databases, the system may access data through JDBC or ODBC interfaces; for file systems, it may read files through file parsers; for API services, it may obtain data through HTTP requests. For example, if the data source is identified as a relational database, the system will select the JDBC interface and build a query to obtain the data to be migrated.

[0139] After selecting the appropriate data access interface, the system will obtain the data to be migrated through the interface. The system needs to perform a read operation to extract a specific data set based on business requirements or user-defined filtering conditions (such as a specified time period, field value, etc.). This data will be used in the subsequent data migration process.

[0140] The system obtains the data to be migrated through database query or file reading methods. For example, the system can use SQL query to obtain specific data rows or columns in a table, or parse the data in CSV or JSON files through file reading tools. The system supports filtering conditions. Users can enter specific conditions through the interface (such as filtering transaction data in the last month), and the system reads data that meets the conditions through the data access interface. For example, the system extracts all account data with a balance greater than 1000 through SQL query, and the generated results can be stored in memory or data files.

[0141] This embodiment scans the data in the data source, automatically identifies the data type and structure to be migrated, and dynamically selects the appropriate data access interface, which greatly simplifies the data preparation work before data migration. The system can select the optimal data access method according to different data sources and data types to ensure efficient and accurate acquisition of the data to be migrated. This function improves the automation of data migration and reduces the possibility of human configuration errors.

[0142] In one embodiment, the above S20 includes:

[0143] S201, performing a data cleaning operation on the data to be migrated;

[0144] S202, converting the data to be migrated after the data cleaning operation into a format that matches the data file;

[0145] S203, writing the format-converted data to be migrated into the data file.

[0146] In this embodiment, data cleaning is a key step to ensure the quality of the data to be migrated. Before data migration, the data to be migrated may contain redundant data, incomplete data, or data that does not conform to the format. The data cleaning operation includes steps such as removing invalid records, repairing missing data, deleting duplicate records, and format normalization. This operation can improve the accuracy and consistency of the data and ensure that the data migrated to the target system meets the standards.

[0147] The system performs data cleaning operations through automated rules or user-defined conditions. For example, it deletes blank rows or empty fields, removes duplicate records, fixes missing values ​​(such as filling with default values), or standardizes date formats. The system can detect outliers or inconsistent data formats in the data and prompt the user to correct them. For example, when the system finds empty values ​​or non-numeric characters in the customer ID field, it can prompt the user to clean it or automatically delete these records. The cleaned data will be further processed to ensure the integrity and accuracy of the data.

[0148] The cleaned data may not be consistent with the format requirements of the target data file, so format conversion is required. Different types of data files (such as CSV, Excel, JSON, etc.) require different data formats. In order to ensure that the data can be correctly stored in the target data file, the cleaned data needs to be converted to the type and structure required by the target file format.

[0149] The system converts data according to the format requirements of the target data file. For example, the system converts date data to the date format that conforms to the target file (such as Excel), or converts numeric fields to the numeric format required by the target file. The data structure also needs to be adjusted, such as converting nested data into a flat table structure to ensure that it matches the two-dimensional table structure of CSV or Excel files. If the target data file is JSON or XML, the system needs to organize the data into nested key-value pairs or tree structures according to specific format rules.

[0150] After completing data cleaning and format conversion, the system needs to write the final data into the target data file. The data file can be a table file (such as CSV, Excel) or a structured file (such as JSON, XML). The system writes the data into the file in rows or key-value pairs to ensure that each record matches the data structure in the target file.

[0151] For table files (such as CSV, Excel), the system writes data to the file by row, with each column corresponding to a field in the target data file. The system automatically generates header information to ensure that the field name is consistent with the data column. For structured files (such as JSON, XML), the system writes data to the file according to a predetermined nested structure to ensure that the organization of key-value pairs, arrays, or objects meets the format requirements of the file. The system supports batch writing of large-scale data to ensure that performance and efficiency are not affected when processing large amounts of data to be migrated.

[0152] This embodiment ensures the accuracy and consistency of data during the migration process through the complete process of cleaning, format conversion and data file writing to the data to be migrated. Through automated data cleaning operations, redundancy and erroneous records in the data can be reduced, and data quality can be improved. The format conversion function enables the data to be migrated to adapt to different types of data file formats, ensuring the compatibility of the data with the target system. Finally, by writing the converted data into the target data file, the system can effectively handle the migration of large-scale data.

[0153] In one embodiment, the above S30 includes:

[0154] S301, providing an input interface for inputting an SQL statement template containing a placeholder;

[0155] S302, receiving the SQL statement template through the input interface;

[0156] S303: Perform a syntax check on the received SQL statement template to verify whether the position and quantity of the placeholders in the SQL statement template meet preset requirements.

[0157] In this embodiment, in order to allow users to input SQL statement templates containing placeholders, the system needs to provide an intuitive and easy-to-use input interface. This input interface allows users to input predefined SQL templates and use placeholders in the template to represent the parts that need to be dynamically replaced. The placeholder can be a standard ? symbol or a named placeholder (such as @field_name), which represents the data field in the SQL statement that will be dynamically replaced during execution.

[0158] The system can provide a graphical user interface (GUI) or a text input box, where the user can enter the SQL template and use placeholders to represent the fields that need to be dynamically filled. For example: UPDATE accounts SET balance =? WHERE account_id =?;

[0159] The input interface can provide auxiliary functions, such as template format prompts, placeholder auto-completion, etc., to help users write SQL templates more conveniently. The system can also provide preset SQL templates for users to choose from, and users can select the appropriate template and modify it according to their needs.

[0160] After the user completes the SQL template in the input interface, the system needs to receive and parse the template. The receiving process ensures that the SQL template entered by the user can be correctly understood by the system and records the number and position of placeholders for subsequent operations.

[0161] The system receives the SQL template entered by the user through an event listener (such as a button submit or enter confirmation), and the system stores the template in memory or database for use in subsequent processing steps. After receiving the template, the system can automatically parse the template, extract the placeholders, and record the number, position, and order of each placeholder. Example: The user enters in the input box: INSERT INTO customers(name,balance)VALUES(?,?);

[0162] The system will receive the template and record the two placeholders and their corresponding fields name and balance.

[0163] After receiving the SQL template input by the user, the system needs to perform a syntax check on the template to ensure that the SQL syntax in the template is correct, the placeholders are used in accordance with the specifications, and the number of placeholders matches the actual needs. The main goal of the syntax check is to verify that the SQL template can be executed correctly and that the position and number of placeholders meet the system preset standards.

[0164] The system parses and syntax-checks the received SQL template through an SQL parser or regular expressions. The system will check whether the SQL keywords (such as SELECT, INSERT, UPDATE, etc.) are used correctly, whether the table name and field name are valid, and whether the placeholders are in the correct position. The system will also verify the number of placeholders to ensure that each placeholder meets the preset requirements. For example, if the SQL template expects 2 placeholders, but the user only provides 1 placeholder, the system will prompt the user to modify it. If the system finds a syntax error or an incorrect number of placeholders, it will provide detailed error information to prompt the user to correct it. For example, if the user enters: UPDATE accounts SETbalance=? WHERE account_id;

[0165] The system will prompt "The number of placeholders does not match" and suggest that the user modify it.

[0166] This embodiment provides an easy-to-use input interface or file upload method, so that the system can flexibly receive SQL statement templates containing placeholders, parse them, and perform syntax checking. The automatic detection and verification function of placeholders ensures the correctness of SQL templates and avoids execution problems caused by incorrect placeholder positions or numbers. The syntax checking function further ensures that SQL templates can be executed smoothly, reducing the complexity and error rate of manually writing SQL statements, and is particularly suitable for scenarios of large-scale data migration and batch updates.

[0167] In one embodiment, the above S50 includes:

[0168] S501, identifying the field names in the data file;

[0169] S502, establishing a mapping relationship between the placeholder and the corresponding field according to the name of the placeholder and the field name in the data file;

[0170] S503: Store the mapping relationship.

[0171] In this embodiment, in a data file (such as a CSV, Excel or JSON file), each column or field has a name to indicate the corresponding data type or content. The system first needs to scan and identify the field names in the data file so that these fields can be subsequently mapped to the placeholders in the SQL template. The field names usually appear in the first line of the file (table header) or in the structure definition of the file (such as the key name in JSON).

[0172] The system reads the first row of the data file (for table files such as CSV and Excel) and obtains the field name of each column. For example, in an Excel file, the field names may include "name", "balance", "account_id", etc. For data files in JSON or XML format, the system parses the file structure to obtain the key name of each field (such as "customer_id", "transaction_date", etc.). The system saves the recognized field names to memory or variables for use in subsequent mapping steps.

[0173] Placeholders represent the parts in the SQL template that need to be replaced dynamically. The system needs to map these placeholders to the corresponding fields in the data file. The mapping relationship can be established based on the match between the placeholder name and the field name, or it can be achieved through sequential matching (the order of the placeholder and field names is the same).

[0174] If the SQL template uses named placeholders (such as @balance, @account_id), the system will automatically match the field names in the data file (such as balance, account_id) with the corresponding placeholders to establish a one-to-one mapping relationship. If the SQL template uses anonymous placeholders (such as?), the system matches the order of the placeholders with the order of the fields in the data file. For example, the first placeholder in the SQL template is mapped to the first column field in the data file, and the second placeholder is mapped to the second column field. The system can support both matching methods at the same time and prompt the user to manually correct the unmatched mapping relationship.

[0175] After the mapping relationship is established, the system needs to save the mapping relationship for use in the subsequent SQL statement generation process. The stored mapping relationship contains information such as placeholders, field names in the data file, field positions, and data types to ensure that the placeholders can be correctly replaced with actual data.

[0176] The system stores each placeholder with its corresponding field name and position in memory or in a database for use in subsequent SQL generation steps. The mapping relationship can be saved as a configuration file (such as JSON, XML format) or a database table, and users can reuse these mapping relationships in subsequent data migration processes to reduce the workload of repeated configuration.

[0177] This embodiment realizes the automatic correspondence between placeholders and actual data by identifying the field names in the data file and establishing a mapping relationship between the placeholders in the SQL template and the data file fields. This mapping relationship ensures the accuracy and automation of SQL statement generation and avoids errors caused by manual replacement of placeholders. The system supports two methods: named placeholders and sequential matching. It can flexibly respond to different SQL template structures and data file formats, significantly improving the efficiency of data migration and batch processing, and is particularly suitable for batch migration and update operations of financial data.

[0178] In one embodiment, the above S70 includes:

[0179] S701, creating a new output column or a new output area in the data file;

[0180] S702: Associating the complete SQL statement with a corresponding row in the data file, and displaying the SQL statement in the new output column or new output area.

[0181] In this embodiment, in order to display the generated complete SQL statements in the data file, the system needs to reserve a location in the data file to store these SQL statements. The usual way is to create a new output column (for table-type files) or a new output area (for structured files) for the data file (such as Excel, CSV or JSON). The output column or output area is used to store the generated SQL statements so as to correspond to each record.

[0182] For example, a table file (such as CSV or Excel): The system creates a new column in the table file as an output column to store the SQL statement corresponding to each record. For example, the system creates a column named "SQL statement" in the last column of the data file. JSON or other structured files: The system creates a new field (key-value pair) for each record to store the generated SQL statement. For example, the system adds a new field "sql_statement" to the object of each record to store the generated SQL statement.

[0183] The system can customize the output location according to user needs, such as allowing users to manually specify that SQL statements be output to a specific column or area of ​​the file.

[0184] Each record usually occupies one line in the data file, and the generated SQL statement should be associated with the corresponding record. Therefore, the system needs to ensure that the generated SQL statement can correspond to each line of record in the data file one by one and be displayed in the corresponding output column or area.

[0185] The system generates a corresponding SQL statement based on the data in each row, and writes the generated SQL statement into the output column of the row. For example, the SQL statement generated for a row of records will be written into the "SQL Statement" column of the row.

[0186] After the system generates the SQL statement for each record, it adds a new field to store the statement so that it is associated with the record in the data file. For example, the system writes the generated SQL statement to the "sql_statement" field of each JSON object.

[0187] After successfully generating and storing SQL statements in the data file, users can view the SQL statements corresponding to each record by opening the file. These SQL statements can be copied or exported to the database for execution to complete data update or migration.

[0188] Users can open the file through Excel or other spreadsheet software and view the SQL statements generated for each record in the "SQL Statement" column. For example, the last column in Excel will display the generated SQL statement, such as UPDATE accounts SET balance = 1000 WHERE account_id = 12345;.

[0189] In the JSON file, each record object will contain a new key-value pair, showing the generated SQL statement. Users can view these SQL statements through JSON viewing tools or text editors, and export and execute them as needed.

[0190] This embodiment creates a new output column or output area in the data file, so that the system can automatically generate SQL statements and associate them with each record, simplifying the display and operation process of SQL statements. Users can intuitively view the generated SQL statements and copy them to the database processing module for execution as needed. This improves the efficiency of batch data processing, and is particularly suitable for large-scale data migration or batch update scenarios, ensuring that the SQL statements generated for each record are accurate, reducing the complexity and error risks of manual operations.

[0191] In one embodiment, the above S80 includes:

[0192] S801, paste the copied SQL statement into the database processing module;

[0193] S802, before executing the SQL statement, checking whether the SQL statement complies with the syntax and requirements of the target database;

[0194] S803, if the SQL statement conforms to the syntax and requirements of the target database, the SQL statement is executed to complete the data migration and update operation;

[0195] S804, when the SQL statement is executed, verify the execution result and record the execution log information.

[0196] In this embodiment, after the system generates the SQL statement, the user can copy and paste it into the database processing module to execute these SQL operations. The database processing module refers to a tool or system for executing SQL statements, which may be a SQL management tool, a command line interface, or a database management system (such as MySQL, Oracle, etc.). In this step, the system needs to ensure that the generated SQL statement can be correctly pasted into the database processing module to prepare for execution.

[0197] The system provides a "copy" function to copy the generated SQL statement with one click, ensuring that the statement is complete and correct when copied. Users paste SQL statements in the database management system (such as PLSQL, MySQLWorkbench, etc.) into the SQL execution window for further execution. The system supports batch copy and paste to process multiple SQL operations at one time.

[0198] Before executing SQL statements, the system needs to perform syntax checking to ensure that the SQL statements comply with the syntax rules and constraints of the target database. Different databases may have different SQL syntax rules, and the system should perform verification before execution to avoid execution failures caused by execution errors or syntax incompatibility.

[0199] The system performs syntax checks on generated SQL statements through the built-in SQL parser or database connection interface. The check includes the correctness of SQL syntax, the existence of table names or field names, the matching of data types, etc. The system can perform specific syntax checks according to different target databases (such as MySQL, Oracle, SQL Server, etc.) to ensure that the SQL statement matches the constraints of the database. If the syntax check fails, the system will prompt the user to modify the SQL statement or automatically repair it to avoid incorrect execution.

[0200] If the SQL statement passes the syntax check, the system will allow the SQL statement to be executed to complete the data migration or update operation. The execution of the SQL statement will directly affect the data in the database, so the accuracy and legality of the statement must be guaranteed.

[0201] The user clicks the "Execute" button in the database processing module to execute the SQL statement. The system sends the SQL statement to the target database for processing through the database management interface. The system supports batch execution of multiple SQL statements to ensure that large amounts of data migration or update operations can be completed efficiently. The execution operation can be insert, update, delete or other data operations, depending on the type of SQL statement.

[0202] After executing the SQL statement, the system needs to verify the execution result to ensure that the SQL statement has been successfully executed and the data has been updated or migrated as expected. At the same time, the system should record the execution log information, including the executed SQL statement, execution time, number of record rows affected, and execution status (success or failure).

[0203] After the SQL is executed, the system verifies whether the SQL operation is successful by querying the number of rows affected and the execution status. If the operation fails, the system records the error message and prompts the user to check. The system generates a detailed execution log, recording the execution time of each SQL statement, the number of rows affected, the execution result (success or failure), etc. The log can be stored in the database or exported as a file for user review or future query. Through execution result verification and log recording, users can ensure the accuracy of data migration or update, and trace possible errors or abnormal situations.

[0204] This embodiment simplifies the operation steps of data migration and update by copying, pasting and executing the SQL statement process. Not only can SQL statements be generated, but also syntax checks can be performed before execution to ensure that the SQL statements are compatible with the target database, reducing the complexity and error risk of manually writing SQL statements. The execution result verification and logging functions provide users with traceability of operations, ensuring the accuracy and security of data migration or update.

[0205] In one embodiment, a data migration device is provided, and the data migration device corresponds one-to-one to the data migration method in the above embodiment. Figure 3 , Figure 3 This is a functional module diagram of a preferred embodiment of the data migration device of the present invention. Data acquisition module 10, data file management module 20, SQL template management module 30, SQL template parsing module 40, mapping relationship management module 50, SQL generation module 60, SQL display module 70 and database execution module 80. The functional modules are described in detail as follows:

[0206] A data acquisition module 10, used to acquire data to be migrated;

[0207] A data file management module 20, used to fill the data to be migrated into a data file;

[0208] SQL template management module 30, used to obtain SQL statement templates containing placeholders;

[0209] An SQL template parsing module 40 is used to parse the SQL statement template and identify the placeholders in the SQL statement template;

[0210] A mapping relationship management module 50, used to establish a mapping relationship between the placeholder and the corresponding field in the data file;

[0211] An SQL generation module 60 is used to combine the SQL syntax in the SQL statement template with the data in the data file based on the mapping relationship to generate a complete SQL statement;

[0212] SQL display module 70, used to add an output position in the data file and display the complete SQL statement according to the output position;

[0213] The database execution module 80 is used to copy the complete SQL statement to the database processing module for execution, thereby completing data migration and update.

[0214] In one embodiment, the data acquisition module 10 is specifically used for:

[0215] Scan the data in the data source to identify the data types and data structures that need to be migrated;

[0216] Determine the corresponding data access interface according to the identified data type and data structure;

[0217] The data to be migrated is obtained through the data access interface.

[0218] In one embodiment, the data file management module 20 is specifically used to:

[0219] Performing a data cleaning operation on the data to be migrated;

[0220] Convert the data to be migrated after the data cleaning operation into a format that matches the data file;

[0221] The data to be migrated after the format conversion is written into the data file.

[0222] In one embodiment, the SQL template management module 30 is specifically used to:

[0223] Provide an input interface for inputting SQL statement templates containing placeholders;

[0224] Receiving the SQL statement template through the input interface;

[0225] Perform a syntax check on the received SQL statement template to verify whether the position and number of placeholders in the SQL statement template meet the preset requirements.

[0226] In one embodiment, the mapping relationship management module 50 is specifically used to:

[0227] identifying field names in the data file;

[0228] According to the name of the placeholder and the field name in the data file, a mapping relationship between the placeholder and the corresponding field is established;

[0229] The mapping relationship is stored.

[0230] In one embodiment, the SQL presentation module 70 is specifically used to:

[0231] Create new output columns or new output areas in the data file;

[0232] The complete SQL statement is associated with the corresponding row in the data file, and the SQL statement is displayed in the new output column or the new output area.

[0233] In one embodiment, the database execution module 80 is specifically used to:

[0234] Paste the copied SQL statement into the database processing module;

[0235] Before executing the SQL statement, check whether the SQL statement complies with the syntax and requirements of the target database;

[0236] If the SQL statement conforms to the syntax and requirements of the target database, the SQL statement is executed to complete the data migration and update operations;

[0237] When the SQL statement is executed, the execution result is verified and the execution log information is recorded.

[0238] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 4 As shown. The computer device includes a processor, a memory, a network interface and a database connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile and / or volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external user terminal through a network connection. When the computer program is executed by the processor, it realizes the functions or steps of a data migration method service side.

[0239] In one embodiment, a computer device is provided. The computer device may be a user terminal, and its internal structure diagram may be as follows: Figure 5As shown. The computer device includes a processor, a memory, a network interface, a display screen and an input device connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external server through a network connection. When the computer program is executed by the processor, it realizes the functions or steps on the user side of a data migration method

[0240] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the following steps are implemented:

[0241] Get the data to be migrated;

[0242] Fill the data to be migrated into a data file;

[0243] Get the SQL statement template containing placeholders;

[0244] Parsing the SQL statement template and identifying placeholders in the SQL statement template;

[0245] Establishing a mapping relationship between the placeholder and the corresponding field in the data file;

[0246] Based on the mapping relationship, the SQL syntax in the SQL statement template is combined with the data in the data file to generate a complete SQL statement;

[0247] Adding an output location in the data file, and displaying the complete SQL statement according to the output location;

[0248] The complete SQL statement is copied to the database processing module for execution to complete data migration and update.

[0249] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0250] Get the data to be migrated;

[0251] Fill the data to be migrated into a data file;

[0252] Get the SQL statement template containing placeholders;

[0253] Parsing the SQL statement template and identifying placeholders in the SQL statement template;

[0254] Establishing a mapping relationship between the placeholder and the corresponding field in the data file;

[0255] Based on the mapping relationship, the SQL syntax in the SQL statement template is combined with the data in the data file to generate a complete SQL statement;

[0256] Adding an output location in the data file, and displaying the complete SQL statement according to the output location;

[0257] The complete SQL statement is copied to the database processing module for execution to complete data migration and update.

[0258] It should be noted that the above functions or steps that can be implemented by the computer-readable storage medium or computer device can refer to the relevant descriptions on the server side and the user side in the aforementioned method embodiment. To avoid repetition, they will not be described one by one here.

[0259] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0260] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0261] It should be noted that if software tools or components other than those of the Company appear in the embodiments of the present application, they are only used for illustration and do not represent actual use. The above-described embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the above-mentioned embodiments, a person of ordinary skill in the art should understand that the technical solutions described in the above-mentioned embodiments can still be modified, or some of the technical features thereof can be replaced by equivalents; and these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A data migration method, characterized in that: The following steps are involved: Get the data to be migrated; Fill the data to be migrated into a data file; Get the SQL statement template containing placeholders; Parsing the SQL statement template and identifying placeholders in the SQL statement template; Establishing a mapping relationship between the placeholder and the corresponding field in the data file; Based on the mapping relationship, the SQL syntax in the SQL statement template is combined with the data in the data file to generate a complete SQL statement; Adding an output location in the data file, and displaying the complete SQL statement according to the output location; The complete SQL statement is copied to the database processing module for execution to complete data migration and update.

2. The data migration method according to claim 1, characterized in that: Get the data to be migrated, including: Scan the data in the data source to identify the data types and data structures that need to be migrated; Determine the corresponding data access interface according to the identified data type and data structure; The data to be migrated is obtained through the data access interface.

3. The data migration method according to claim 1, characterized in that: Fill the data to be migrated into the data file, including: Performing a data cleaning operation on the data to be migrated; Convert the data to be migrated after the data cleaning operation into a format that matches the data file; The data to be migrated after the format conversion is written into the data file.

4. The data migration method as claimed in claim 1, characterized in that: Get the SQL statement template containing placeholders, including: Provide an input interface for inputting SQL statement templates containing placeholders; Receiving the SQL statement template through the input interface; Perform a syntax check on the received SQL statement template to verify whether the position and number of placeholders in the SQL statement template meet the preset requirements.

5. The data migration method according to claim 1, characterized in that: Establishing a mapping relationship between the placeholder and the corresponding field in the data file includes: identifying field names in the data file; According to the name of the placeholder and the field name in the data file, a mapping relationship between the placeholder and the corresponding field is established; The mapping relationship is stored.

6. The data migration method according to claim 1, characterized in that: Adding an output location in the data file and displaying the complete SQL statement according to the output location includes: Create new output columns or new output areas in the data file; The complete SQL statement is associated with the corresponding row in the data file, and the SQL statement is displayed in the new output column or the new output area.

7. The data migration method according to claim 1, characterized in that: The complete SQL statement is copied to the database processing module for execution to complete data migration and update, including: Paste the copied SQL statement into the database processing module; Before executing the SQL statement, check whether the SQL statement complies with the syntax and requirements of the target database; If the SQL statement conforms to the syntax and requirements of the target database, the SQL statement is executed to complete the data migration and update operations; When the SQL statement is executed, the execution result is verified and the execution log information is recorded.

8. A data migration device, characterized in that: The data migration device comprises: A data acquisition module, used to acquire data to be migrated; A data file management module, used to fill the data to be migrated into a data file; SQL template management module, used to obtain SQL statement templates containing placeholders; An SQL template parsing module, used to parse the SQL statement template and identify the placeholders in the SQL statement template; A mapping relationship management module, used to establish a mapping relationship between the placeholder and the corresponding field in the data file; An SQL generation module is used to combine the SQL syntax in the SQL statement template with the data in the data file based on the mapping relationship to generate a complete SQL statement; An SQL display module, used to add an output position in the data file and display the complete SQL statement according to the output position; The database execution module is used to copy the complete SQL statement to the database processing module for execution, thereby completing data migration and update.

9. A computer device, characterized in that: The computer device includes a memory, a processor, and a data migration program stored in the memory and executable on the processor. When the data migration program is executed by the processor, the steps of the data migration method according to any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium, characterized in that: The storage medium stores a data migration program, and when the data migration program is executed by the processor, the steps of the data migration method according to any one of claims 1 to 7 are implemented.

Images