Data import method, system and equipment and medium

Abstract

The invention relates to the technical field of data import, in particular to a data import method, system and device and a medium. The data importing method comprises the following steps: designing a db2import table by using a DB2 tool; constructing a user interface based on a db2import table, generating an import configuration table and an import link, and generating a template and a template link; extracting configuration parameters from the import configuration table, and obtaining single-source business data through the template; then monitoring a data import request, when the request is received, acquiring configuration parameters and service data associated with the request, adding the service data into a specified database service table according to the configuration parameters, and executing a single-source data table guide task; and when the received request is the request for importing the single-source data into the multiple tables, polling and executing the data importing task. According to the scheme, the problem that single-source data are imported into multiple heterogeneous tables is solved, and program compiling is simple, efficient and good in timeliness.

Description

Technical Field

[0001] The present invention relates to the technical field of data import, and in particular to a method, system, device and medium for data import. Background Art

[0002] During the development of a certain project, it is necessary to meet the requirement of importing single-source data into multiple tables. Single-source data refers to source data extracted from one data source. The multiple tables that are imported may be homogeneous tables or heterogeneous tables with different data models, data types and / or formats. The data migration process of importing single-source data into multiple heterogeneous tables usually requires consideration of technical points such as data model conversion, data format conversion, data migration, application adaptation, index reconstruction, data consistency assurance, and performance tuning.

[0003] In the prior art, for example, the Chinese invention patent with publication number CN114077602A discloses a data migration method and device, an electronic device, and a storage medium. The data migration method is a method for multi-threaded migration of data from different source tables or files to a unified database. However, the prior art does not disclose a technical solution that directly solves the problem of importing single-source data into multiple heterogeneous tables. On the other hand, secondary development based on the code of the existing import module is more efficient, but has the following shortcomings: first, it depends on the ability and habits of engineers; second, it requires debugging, resulting in low timeliness; third, when the requirements change, it often requires human and material resources to be invested again.

[0004] Therefore, in order to solve the problem of importing single-source data into multiple tables, especially to solve the problem of importing single-source data into multiple heterogeneous tables, it is necessary to provide a data import method that is easy to implement. Summary of the invention

[0005] In view of the shortcomings of the prior art and to solve the problem of importing single-source data into multiple tables, the present invention provides a data import method, which can realize the import of single-source data into a single table, the import of single-source data into multiple isomorphic tables, and the import method can be simple, convenient, flexible and efficient.

[0006] First, the present invention provides a method for importing data.

[0007] The method uses a DB2 tool to design a db2import table; based on the db2import table, a user interface is constructed, an import configuration table and an import link are generated, and a template and a template link are generated; configuration parameters are extracted from the import configuration table, and single-source business data is obtained through the template; then, a data import request is monitored, and when a request is received, configuration parameters and business data associated with the request are obtained, and the business data is added to a specified database business table according to the configuration parameters, and a single-source data import task is executed;

[0008] When the received request is a request to import single-source data into multiple tables, the business data is added to each database business table according to the configuration parameters corresponding to each database business table.

[0009] The method specifically comprises the following operations:

[0010] Design the db2import table through the SPR_IMP_TABLE table configuration table and the SPR_IMPORT_FIELD field configuration table;

[0011] After mapping each table element in the db2import table into configuration parameters, a user interface is constructed. An import configuration table and an import link are generated according to the configuration parameters. Then, a template and a template link are generated according to the import configuration table.

[0012] The import link is connected to the user interface for users to edit configuration parameters, and the template link is connected to the user interface for users to download the template and edit business data through the template and save it as an import file;

[0013] Listen for data import requests. When a request is received, parse the configuration parameters and business data associated with the request, obtain the table configuration parameters and field configuration parameters from the configuration parameters, extract the configuration identifier from the table configuration parameters, and then index the database business table that corresponds to the configuration identifier according to the configuration identifier, and add the business data to the indexed database business table according to the field configuration parameters to complete the import;

[0014] When there are multiple configuration identifiers parsed, the request received is a request to import single-source data into multiple tables. Each round only executes indexing and importing tasks according to one configuration identifier. Each configuration identifier is polled until the business data of the single source is added one by one to the multiple database business tables indexed, thereby realizing the multi-table import of single-source data.

[0015] The method of the present invention starts a separate instance each time an indexing or importing task is executed according to a configuration identifier.

[0016] Second, the present invention provides a data import system for executing the above data import method.

[0017] The system includes a db2import table design unit, a user interface construction unit, a configuration unit, a template unit, a monitoring unit, and a data import table execution unit;

[0018] The db2import table design unit is used to design the db2import table using DB2 tools;

[0019] The user interface construction unit is used to construct a user interface based on the db2import table;

[0020] The configuration unit is used to generate an import configuration table and an import link, and save all configuration parameters in the import configuration table;

[0021] The template unit is used to generate templates and template links, and save business data;

[0022] The monitoring unit is used to monitor the request for data import;

[0023] The data import table execution unit is used to obtain the configuration parameters and business data associated with the request when receiving the request, and add the business data to the specified database business table according to the configuration parameters.

[0024] Third, the present invention provides a device, specifically an electronic device.

[0025] The electronic device includes a memory and a processor; a computer program is stored in the memory; when the computer program is executed on the processor, the above-mentioned data import method is implemented.

[0026] Fourth, the present invention provides a medium, specifically a computer-readable storage medium.

[0027] The computer-readable storage medium stores computer instructions; when the computer instructions are executed on the electronic device, the data import method is implemented.

[0028] The present invention has the following beneficial effects.

[0029] (1) The data import method provided by the present invention can realize the import of single-source data into a single table, the import of single-source data into multiple isomorphic tables, and the import of single-source data into multiple heterogeneous tables, and the import method is simple, convenient, flexible and efficient.

[0030] (2) The data import method provided by the present invention obtains abnormal information for iteratively improving the robustness and stability of the backend program, and is also used to improve operation and maintenance and user experience.

[0031] (3) The data import method provided by the present invention can be used for general database automatic script editing. Traditional development and import takes 1-2 man-days, but the data import method provided by the present invention can be used to complete the task in half an hour.

[0032] (4) The data import method provided by the present invention enables a separate instance to read configuration information, execute import logic, etc. each time db2import is started, and supports hot deployment.

[0033] (5) The data import system provided by the present invention provides links or interfaces for accessing third-party systems through microservices, and has low coupling. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] Figure 1 This is a schematic diagram of the main principles of the data import method described in Example 2.

[0035] Figure 2 Diagram of the user interface configured for a table.

[0036] Figure 3 Illustration of the user interface configured for a field.

[0037] Figure 4 Schematic diagram of the user interface for configuring a list of Excel import tables Figure 1 ; The information displayed in the list is the table configuration parameters corresponding to the app_alert_type configuration table. The content displayed in the table workspace is the usage status after the user downloads the app_alert_type data import template and adds business data to the template; The field type of the alert level field in the business data shown in this figure is text type.

[0038] Figure 5 Schematic diagram of the user interface for configuring a list of Excel import tables Figure 2 ; The information displayed in the list is the table configuration parameters corresponding to the app_alert_type configuration table. The content displayed in the table workspace is the usage status after the user downloads the app_alert_type data import template and adds business data to the template; The field type of the alarm level field in the business data shown in this figure is digital.

[0039] Figure 6 Schematic diagram of the user interface for configuring a list of Excel import tables Figure 3 ; The information displayed in the list is the table configuration parameters corresponding to the three configuration tables: app_alert_type configuration table, mcmm_device configuration table, and mcmm_mat_material configuration table.

[0040] Figure 7 This is a schematic diagram of the data in the database business table that serves as the import target before the business data is imported.

[0041] Figure 8 A diagram of the user interface for adding business data using a template.

[0042] Fig. 9 Schematic diagram of the user interface for generating an import file after adding a template to business data.

[0043] Fig.10 for Figure 8 The business data shown is successfully added Figure 7 The data diagram behind the database business table is shown in the figure; the three rows of data marked in the box are Figure 8 Business data shown.

[0044] Fig.11 This is a schematic diagram of the operation principle of the db2import tool.

[0045] Fig.12 Flowchart for abnormal feedback.

[0046] Fig.13 The main flow chart of automatically constructing the method of executing SQL.

[0047] Fig.14 A sample diagram of table configuration for the SPR_IMP_TABLE table.

[0048] Fig.15 Sample diagram of configuring a table for the SPR_IMPORT_FIELD field.

[0049] Fig.16 This is a schematic diagram of the business data that needs to be imported in Example 6.

[0050] Fig.17 This is a schematic diagram of the contents of the mcmm_device device table before importing business data.

[0051] Fig.18 This is a schematic diagram of the contents of the mcmm_mat_material material table before importing business data.

[0052] Fig.19 This is a schematic diagram of the contents of the mcmm_device device table after importing business data.

[0053] Fig. 20 This is a schematic diagram of the contents of the mcmm_mat_material material table after importing business data.

[0054] Fig.21 This is a flow chart of a method for data import in Example 2. DETAILED DESCRIPTION

[0055] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, 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. It should be understood that the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and therefore should not be regarded as limiting the scope of protection. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technical personnel in this field without making creative work are within the scope of protection of the present invention.

[0056] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "disposed", "connected" and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be an indirect connection through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0057] Embodiment 1:

[0058] The DB2 command line tool db2import can be used to import data from external files into a DB2 database. You can also specify the format of the import file, the target table, and other import parameters. However, currently, when using the db2import command, you can only import a set of data into one or more homogeneous data tables at a time. It is not possible to directly import single-source data into multiple heterogeneous data tables at the same time. Single-source data refers to the same data source.

[0059] This embodiment mainly solves the problem of importing single-source data into a table, and does not discuss the solution of importing multi-source data into a table. The data import method described in this embodiment is applicable to the following application scenarios:

[0060] Application scenario 1: Import single-source data into a single table;

[0061] Application scenario 2: Importing single-source data into multiple homogeneous tables;

[0062] Application scenario 3: Importing single-source data into multiple heterogeneous tables.

[0063] First, this embodiment proposes a method for data import, which uses a DB2 tool to design a db2import table; builds a user interface based on the db2import table, generates an import configuration table and an import link, and generates a template and a template link; extracts configuration parameters from the import configuration table, and obtains single-source business data through the template; then monitors data import requests, and when a request is received, obtains the configuration parameters and business data associated with the request, adds the business data to the specified database business table according to the configuration parameters, and executes the single-source data import task; when the received request is to import single-source data into multiple tables, the business data is added to each database business table according to the configuration parameters corresponding to each database business table.

[0064] The underlying core logic of the above method is: by configuring import rules, the relationship between database metadata and fields and field types in the application is established to handle the structural differences of multiple data tables. Therefore, the above method generates a template based on the db2import table, and uniformly configures the import rules that associate the data to be imported and the target data table through the template, establishes the mapping relationship between database metadata and application fields and field types, solves the heterogeneity problem of multiple target data tables, and effectively solves the problem of multi-table import of data to be imported.

[0065] The method provided in this embodiment can also be described as: developing a db2import tool with configuration function and data import function based on DB2 tool, generating associated import configuration table and template based on db2import table, so as to obtain multiple corresponding relationships between the business data to be imported and each database business table as the import object, and then adding the business data to multiple database business tables respectively according to each corresponding relationship.

[0066] Furthermore, this embodiment supports multiple mainstream databases. Database types, such as mysql, oracle, sqlserver, postgresql, etc. This embodiment supports import files corresponding to multiple mainstream data formats. Import file formats, such as xls, xlsx, csv, txt, etc.

[0067] Secondly, this embodiment also provides a data import system for executing the above method.

[0068] The system includes a db2import table design unit, a user interface construction unit, a configuration unit, a template unit, a monitoring unit, and a data import table execution unit;

[0069] The db2import table design unit is used to design the db2import table using DB2 tools;

[0070] The user interface construction unit is used to construct a user interface based on the db2import table;

[0071] The configuration unit is used to generate an import configuration table and an import link, and save all configuration parameters in the import configuration table;

[0072] The template unit is used to generate templates and template links, and save business data;

[0073] The monitoring unit is used to monitor the request for data import;

[0074] The data import table execution unit is used to obtain the configuration parameters and business data associated with the request when receiving the request, and add the business data to the specified database business table according to the configuration parameters.

[0075] The system described in this embodiment can be developed with code with accuracy down to the minute level, which is more efficient than traditional code development. Moreover, the same familiar code and configuration can be used for a long time, which simplifies maintenance.

[0076] Embodiment 2:

[0077] This embodiment, based on Embodiment 1, describes in detail a method for importing data.

[0078] The method, such as Fig.21 As shown, the steps specifically include the following steps S1 to S4.

[0079] Step S1: Design the db2import table through the SPR_IMP_TABLE table configuration table and the SPR_IMPORT_FIELD field configuration table.

[0080] Step S2: Map each table element in the db2import table into configuration parameters and then build a user interface, generate an import configuration table and an import link according to the configuration parameters, and then generate a template and a template link according to the import configuration table.

[0081] Step S3: Connect the import link to the user interface for the user to edit the configuration parameters, and connect the template link to the user interface for the user to download the template and edit the business data through the template and save it as an import file.

[0082] Step S4: Listen for requests for data import. When a request is received, parse the configuration parameters and business data associated with the request, obtain the table configuration parameters and field configuration parameters from the configuration parameters, and then extract the configuration identifier from the table configuration parameters. Then, index the database business table that corresponds to the configuration identifier one by one according to the configuration identifier, and add the business data to the indexed database business table according to the field configuration parameters to complete the import.

[0083] When there are multiple configuration identifiers parsed, the request received is a request to import single-source data into multiple tables. Each round only executes indexing and importing tasks according to one configuration identifier. Each configuration identifier is polled until the business data of the single source is added one by one to the multiple database business tables indexed, thereby realizing the multi-table import of single-source data.

[0084] The method described in this embodiment builds a db2import tool based on the db2import table. The table elements of the db2import table are mapped to configuration parameters, and the configuration parameter items and configuration parameter values ​​constitute an import configuration table. A template is generated based on the import configuration table, and business data is added based on the template to generate an import file. The database business table is also associated through the import configuration table. As a result, business data is associated with a database business table through an import configuration table, so that multi-table import of single-source data can be realized simply, conveniently and quickly.

[0085] In another specific implementation, the following definitions are made for each table element in the db2import table:

[0086] Table 1 SPR_IMP_TABLE table configuration table

[0087] name type Enumeration Values Notes IMPORT_ID varchar(32) Table primary key IMPORT_TABLE varchar(64) Import Table TABLE_IDENTITY varchar(32) Table identifier, access key value IMPORT_ROW int(11) Import starting row number TABLE_DESC varchar(255) describe

[0088] Table 2 SPR_IMPORT_FIELD field configuration table

[0089] name type Enumeration Values Notes IMPORT_FIELD_ID varchar(32) Field primary key, field identifier COL_FIELD varchar(32) Import table name ALIAS varchar(64) Custom aliases IDX_ORDER int(4) Number of columns in the import file IMPORT_ID varchar(32) Parent ID FIELD_TYPE varchar(32) Field Type EXP_SQL varchar(256) Template export field escape IMP_SQL varchar(256) Import field escapes UPD_FILED int(1) Update primary key

[0090] The table elements of the SPR_IMP_TABLE table configuration table are shown in Table 1, and the table elements of the SPR_IMPORT_FIELD field configuration table are shown in Table 1.

[0091] Furthermore, a correspondence scheme is provided between each table element in the db2import table and table configuration parameters and field configuration parameters in the import configuration table.

[0092] Table 3 SPR_IMP_TABLE table configuration table table elements and table configuration parameters correspondence table

[0093] Table Elements Table configuration parameters Primary Key IMPORT_ID Table primary key Import table IMPORT_TABLE Import Table Table ID TABLE_IDENTITY Configuration ID Import starting row number IMPORT_ROW Start importing data rows Description TABLE_DESC Function Name

[0094] Table 4 Correspondence between table elements and field configuration parameters of SPR_IMPORT_FIELD field configuration table

[0095] Table Elements Field configuration parameters Primary Key IMPORT_FIELD_ID Field primary key Import table name COL_FIELD The name of the table to which the field belongs Custom alias ALIAS display name Import file columns IDX_ORDER EXCEL field order ParentIMPORT_ID Field Name Field Type FIELD_TYPE Field Type Template export field escape EXP_SQL Template field escape SQL Import field escape IMP_SQL Import field escape SQL Update primary key UPD_FILED Update field primary key

[0096] The table elements of the SPR_IMP_TABLE table configuration table and the table configuration parameters are mapped one by one in sequence, as shown in Table 3; the table elements of the SPR_IMPORT_FIELD field configuration table and the field configuration parameters are mapped one by one in sequence, as shown in Table 4. Among them, the table identifier TABLE_IDENTITY is mapped to the configuration identifier. The configuration identifier is the only access identifier of the database business table. One configuration identifier is associated with one database business table, and different configuration identifiers are associated with different database business tables.

[0097] The method described in this embodiment provides a tool for front-end users to edit configurations, which is denoted as the db2import tool. The main principle diagram of the db2import tool is as follows: Figure 1 As shown in the figure, after obtaining all configuration parameters, they are packaged into an import configuration table and an import link is generated. Based on the import configuration table, a template is generated and a template link is generated; the import link used to associate configuration parameters and the template link used to associate business data are connected to the user interface for users to edit configuration parameters and upload business data. After completion, the operation phase of data importing begins. Furthermore, various problems generated during the operation phase are classified into the abnormal knowledge base; through problem analysis and iteration, the robustness and stability of the backend program are improved, and the operation and maintenance efficiency and user experience are improved.

[0098] The other parts of this embodiment are the same as those of Embodiment 1, and thus will not be described in detail.

[0099] Embodiment 3:

[0100] This embodiment, based on Embodiment 1 or Embodiment 2, describes the method in combination with user operations in combination with specific application scenarios.

[0101] Provide specific application scenarios:

[0102] Configuration files: app_alert_type configuration table, mcmm_device configuration table, mcmm_mat_material configuration table;

[0103] Template: app_alert_type data import template;

[0104] Import file: After the app_alert_type data import template loads the business data;

[0105] Import objects: mcmm_device equipment table, mcmm_mat_material material table;

[0106] Among them, the app_alert_type configuration table, mcmm_device configuration table, and mcmm_mat_material configuration table are all built based on the db2import table; the app_alert_type configuration table is associated one-to-one with the app_alert_type data import template, the mcmm_device configuration table is associated one-to-one with the mcmm_device equipment table, and the mcmm_mat_material configuration table is associated one-to-one with the mcmm_mat_material material table.

[0107] In the above specific application scenario, the "app_alert_type data import template" with business data is used as the import file, and the business data in the import file is imported into two heterogeneous database business tables, "mcmm_device equipment table" and "mcmm_mat_material material table".

[0108] After the user interface is built, front-end users can use it to edit configuration parameters, add business data, send data import requests, and perform other operations.

[0109] User operation step 1: Configuration table configuration parameters.

[0110] like Figure 2 The following table shows the user interface corresponding to the table configuration. "Configuration ID" is the unique ID for accessing the database business table. The parameter value of "Start importing data rows" refers to the row from which the business data starts to be read. "Function name" is optional. "Import table selection" can select all current database business tables.

[0111] On this user interface, the front-end user edits the table configuration parameters: "Configuration ID: mcmm_device", "Start importing data row: 2", "Function name: Log audit use", "Import table selection: mcmm_device". This set of table configuration parameters means that when importing business data into the mcmm_device device table, the import starts from the second row of business data.

[0112] One configuration identifier is associated with one database business table, and multiple database business tables need to be configured multiple times. Therefore, in the case of importing business data into two database business tables, mcmm_device equipment table and mcmm_mat_material material table, the two database business tables need to be configured separately. Since the configuration method of the database business table is the same, the user interface showing the table configuration parameters of the mcmm_mat_material material table is omitted in the attached figure.

[0113] User operation step 2: Configure field configuration parameters.

[0114] like Figure 3 The following figure shows the user interface corresponding to the field configuration. If "Yes" is selected for "Update Field Primary Key", the field will be checked. If a record of the field primary key exists, it will be updated; otherwise, a new record will be added. This is used to identify the field primary key. For example, if the field primary key value already has a record with IMPORT_FIELD_ID = 1, and there is no record with IMPORT_FIELD_ID = 2, then the record with IMPORT_FIELD_ID = 1 will be updated, and the record with IMPORT_FIELD_ID = 2 will be added.

[0115] "Display Name" is used to edit the display name of the template. The parameter value of "EXCEL Field Order" refers to the column of the EXCEL table to which the data imported by the primary key of the field corresponds. The data here, that is, business data, refers to the values ​​in the Excel format import file. At this time, the business data has not yet been stored.

[0116] "Template field escape SQL" is the template field escape rule; for example: the login account database storage is user_login, then through SELECT NAME FROM pri_user pu WHERE pu.USER_ID = tb_.user_login, escape and output the name "Yao Sujin".

[0117] "Import field escape SQL" is the import field escape rule; for example: the business table stores user_id, but the name is imported, then through SELECT user_id FROM pri_user pu WHERE NAME=?, the name "Yao Sujin" is escaped into user_id and stored in the business table. The question mark is the standard SQL writing method. The value is passed by the list value object to execute Statement.update(sql,value).

[0118] In this user interface, the front-end user edits the field configuration. In the metadata selection area, select the fields that need to be imported, such as "code", "name", "level", "remarks", and "create_time", and edit the properties of these fields; edit in the metadata form area: "Update field primary key: No", "Display name: Alarm level", "EXCEL field order: 3".

[0119] The user interface of the EXCEL import table configuration list is as follows Figure 4 , Figure 5 , Figure 6 As shown. Figure 4 As shown in the figure, the information displayed in the list is the records generated after the app_alert_type data import template database table is configured. The content displayed in the table workspace is the status of the business data displayed after the user downloads the database table through the template and opens it; the field type of the "alert level" field in the business data shown in this figure is text. Since the field type of the "alert level" is limited to numeric type in the configuration parameters, it is escaped through SQL, and the escaped value is as follows Figure 5As shown. Based on the db2import table, the structure of the database table and its structural parameters are configured by editing the parameter values ​​of the table elements of the db2import table. Each configured database table corresponds to a row of records in the import table configuration list. Therefore, based on the content of step S2, the user configures three database tables through the user interface: app_alert_type data import template, mcmm_device configuration table, and mcmm_mat_material configuration table. After the configuration is completed, the user interface of the EXCEL import table configuration list is as follows Figure 6 shown.

[0120] Configuration is divided into table configuration and field configuration, and field configuration is performed by adding, editing or setting fields. Therefore, front-end users can flexibly configure import fields through the user interface.

[0121] User operation step 3: After configuration is completed, download the corresponding template from the list.

[0122] The process of front-end users editing the parameters of table configuration and field configuration in the user interface is the process of configuring import conditions, and the import conditions are recorded as the import configuration table. After completing the configuration, the front-end user can download the corresponding template in the EXCEL import table configuration list. The template is automatically generated after the configuration is completed. Users can download and open templates by clicking "Template" in the "Operation" column of the list. Users can provide a link address for the interface service by clicking "Import Data" in the "Operation" column of the list, which can also be used to test the import effect after the configuration is completed. The template allows users to download and edit business data; the import link and template link are provided to third-party system access in the form of services.

[0123] User operation step 4: Add the imported business data according to the template.

[0124] like Figure 7 As shown in the figure, the data in the mcmm_device device table is schematically shown before the second round of business data is imported.

[0125] like Figure 8 As shown, use the "app_alert_type data import template" as a template, add a user interface diagram of business data, and import business data.

[0126] like Fig. 9 As shown in the figure, after the business data is imported into the template, an Excel table "app_alert_type data import template (3).xls" is generated. This Excel table is the import file containing the business data.

[0127] like Fig.10 As shown, Fig. 9The business data in the import file is successfully imported into the mcmm_device device table; the three rows of data marked in the box are Figure 8 The business data involved.

[0128] It should be noted that the three rows of data that make up this round of business data are in Figure 8 , Fig. 9 , Fig.10 The contents are the same, except that: Figure 8 The three rows of data are added to the template. Fig. 9 The three rows of data are added to the import file. Fig.10 The three rows of data are added to the database business table.

[0129] In another specific implementation, the system is provided to a third party system through two modes: link and interface;

[0130] Link import, such as: / imp / spr / imp.db2v?action=Import_Execl_Data&tableIdentity

[0131] =mcmm_mat_material,mcmm_device;

[0132] Interface import, such as: / imp / spr / imp.do? action=uploadExcel&tableIdentity

[0133] =mcmm_mat_material,mcmm_device.

[0134] The other parts of this embodiment are the same as those of Embodiment 1 or Embodiment 2, and thus will not be described in detail.

[0135] Embodiment 4:

[0136] This embodiment describes the programming idea of ​​the db2import tool based on any one of Embodiments 1 to 3. The user sends a request by submitting a request form, and the db2import tool receives the request form, which is equivalent to receiving the request. The request form at this time includes an import file containing business data and an import configuration table containing configuration identifiers.

[0137] like Fig.11As shown, when the db2import tool receives a request form, it first parses it into a Map through the request form parameter general parser, then obtains the configuration identifier from the Map and splits the order, and then polls the configuration identifier to obtain the import configuration table, import file, and database business table corresponding to each configuration identifier; data rationality verification is performed during the polling process: if the data rationality verification is successful, the business data in the import file is imported into the database business table associated with the configuration identifier according to the configuration parameters in the import configuration table, for automatic script warehousing; if the data rationality verification is unsuccessful, the cause of the exception is fed back and the exception information is sent to the exception knowledge base.

[0138] The number of polling times is related to the number of import targets. If you only need to import business data into one database business table, poll once; if you need to import business data into two database business tables, poll twice; and so on. Since the configuration identifier corresponds to the database business table of the import target one by one, the db2import tool determines the number of polling times by parsing the number of configuration identifiers. Before polling, multiple request forms are split according to the configuration identifiers to form multiple groups of import files and import configuration tables; then each group of import files and import configuration tables is polled.

[0139] In another specific implementation, the data rationality check refers to determining in sequence whether the configuration identifier exists, whether the import file can be parsed, whether the import file matches the valid number of columns in the database business table, and whether the data types of the import file and the corresponding fields in the database business table match; if all are "yes", the data rationality check is successful; otherwise, the data rationality check is unsuccessful. Furthermore, if the automatic script fails to update the database when it is being stored, the cause of the exception will also be fed back and the exception information will be sent to the exception knowledge base. Therefore, the process of exception feedback is as follows: Fig.12 As shown, the judgments are made in turn as to whether the imported file can be parsed normally, whether the number of valid columns matches, whether the field type matches, and whether the update into the warehouse is successful. If there is an exception, the cause of the exception is fed back and the exception information is sent to the exception knowledge base.

[0140] The automatic script storage refers to: automatically constructing an execution SQL task after business data is added to the database business table to add or modify records.

[0141] Feedback of the abnormal cause and sending the abnormal information to the abnormal knowledge base means: on the one hand, sending the abnormal cause to the front-end user, and on the other hand, writing the abnormal information to the local file, waiting to retrieve the local file in idle time and updating the abnormal information to the abnormal knowledge base table for back-end iteration and improvement. The abnormal behavior during the execution of the db2import program is reported to the back-end, which analyzes the problem and iteratively improves the robustness and stability of the back-end program; it can also improve operation and maintenance efficiency and user experience.

[0142] Specifically, the source of the exception knowledge base is the records of all abnormal behaviors encountered during the execution of the db2import program.

[0143] Typical cases of abnormal behavior are as follows.

[0144] Typical case 1: Parsing of imported files is abnormal. When judging the parsability of the imported file, it is found that the parsing cannot be performed and the feedback is abnormal; for example: the format of the first sheet cannot be parsed! Null.

[0145] Typical case 2: Valid column number matching exception. Determine whether the configuration column number can be obtained in the import file. The configuration column number in the database configuration table must be less than or equal to the number of columns in the import file. If the configuration column number is greater than the number of columns in the import file, the feedback is abnormal.

[0146] Typical case 3: Field type matching exception. Verify whether the data in the row and column matches the type defined in the database field. For example, to determine whether the business data corresponds to a column field in the data of the database business table, it is necessary to verify whether the field types of the two match. If they do not match, an exception is reported.

[0147] Specifically, take the level field of the mcmm_device device table as an example:

[0148] Step a: The db2import tool first obtains all configuration parameters in the access URL, extracts the parameter value of the table identifier TABLE_IDENTITY to obtain "mcmm_device", that is, the value of the configuration identifier is "mcmm_device", and then indexes to the mcmm_device database configuration table, automatically forms select * from mcmm_device, and queries the metadata Result_Set_Meta_Data of the field;

[0149] Step b: Get the configuration parameters of the import field according to the table identifier TABLE_IDENTITY, and get the parameter value from the column number of the import file in Excel format corresponding to the field. For example, the value of the level configuration is the third column, which corresponds to the alarm level column of the import file in Excel format.

[0150] Step c: Take out the value 4 of the field level in step b. According to the Result_Set_Meta_Data in step a, the type of the field level is int. Perform integer conversion on the value of the field level. If the value is non-numeric, an error will be reported. The specific location where the exception will be polled, the number of rows and columns, and the type information to be converted are accurately output to the exception knowledge base for storage, which is convenient for backtracking and searching.

[0151] Typical situation 4: Update warehousing exception. If the business data import fails, the exception information is intercepted and saved to the exception knowledge base.

[0152] Furthermore, common exceptions to updating the database include:

[0153] 1) The value is too large. For example, if the length of a field is 2 and a field value greater than 2 is inserted, an exception message indicating that the value is too large will be returned.

[0154] 2) The text does not match the format string; for example, if the wrong date format is entered, the string '2021-01-01' is converted to a date, but the format is incorrectly specified as 'YY-MM-DD', this error will occur;

[0155] 3) The value is greater than the allowed precision specified for this column; for example, the integer digit of the floating-point type of the field is 2, and the value passed in is 123.45.

[0156] The prompt list of the abnormal knowledge base is shown in Table 5:

[0157] Table 5 Prompt list of abnormal knowledge base

[0158]

[0159]

[0160] In the prompt list of the abnormal knowledge base shown in Table 5, the abnormal method is marked as Import_Execl_Data.

[0161] When an exception occurs in the backend, the cause of the exception is first returned to the frontend user to prompt the user, and then the exception information is written to the local file. The db2import tool executes the scheduler to read the local file during idle time and updates the exception information to the exception knowledge base. The exception feedback method provided in this embodiment has comprehensive and accurate exception prompts and obvious intensive management iteration characteristics; and based on the import of intensive engine-type rule programs, it greatly improves the location and timeliness of solving operation and maintenance problems, and is easy to maintain.

[0162] The other parts of this embodiment are the same as any one of Embodiments 1 to 3, and thus will not be described in detail.

[0163] Embodiment 5:

[0164] This embodiment is specifically described on the basis of any one of the above-mentioned embodiments 1 to 4.

[0165] This embodiment is based on Fig.14 The example of SPR_IMP_TABLE table configuration table shown in the figure is as follows: Fig.15The example of the SPR_IMPORT_FIELD field configuration table shown in FIG. 1 provides a method for automatically constructing an SQL statement for execution. The main process of the method for automatically constructing an SQL statement for execution is as follows: Fig.13 As shown, the following steps are included:

[0166] Step T1: first extract the element value from the table identifier IMPORT_TABLE of the SPR_IMP_TABLE table configuration table to obtain the table name of the corresponding configuration table; for example: table name mcmm_device;

[0167] Step T2: Automatically construct the query table primary key SQL according to the table name to obtain the table primary key;

[0168] Step T3: According to "UPD_FILED=1", obtain the value of the field primary key in the imported business data;

[0169] Step T4: Automatically construct a query statement to determine whether there is a record with the field primary key: if it exists, prepare to construct an update SQL, obtain the table primary key type, convert the same type of object as the SQL value object, and go to step T5; if it does not exist, prepare to construct a new SQL, obtain the table primary key type, automatically create the table primary key value, store it in the SQL value object, and go to step T5; the value of the automatically created table primary key can be UUID or an auto-increment integer value according to different databases, etc.

[0170] Step T5: Get the field primary key in the SPR_IMPORT_FIELD field configuration table;

[0171] Step T6: Automatically construct a query table element to obtain the attribute value FIELD_TYPE of all field types;

[0172] Step T7: Import the row record order of the database configuration table, convert the value into a field type object, and store the value one-to-one for adding or updating scripts;

[0173] Step T8: Import each row of records in the database configuration table, match the fields and field values ​​one by one, and automatically build a new or update script;

[0174] Step T9: Execute the script added or updated in step T8 and add or update it to the database.

[0175] The other parts of this embodiment are the same as any one of Embodiments 1 to 4, and thus will not be described in detail.

[0176] Embodiment 6:

[0177] This embodiment, based on any one of the above-mentioned embodiments 1 to 5, describes a method for importing single-source data into multiple heterogeneous tables.

[0178] In this embodiment, the content of the business data to be imported is as follows: Fig.16 As shown; the import objects are two heterogeneous database business tables, mcmm_device equipment table and mcmm_mat_material material table; the field types of the mcmm_device equipment table are shown in Table 6; the field types of the mcmm_mat_material material table are shown in Table 7:

[0179] Table 6 Field types of mcmm_device equipment table

[0180]

[0181] Table 7 Field types of mcmm_mat_material material table

[0182]

[0183]

[0184] Before importing business data, the content of the mcmm_device device table is as follows: Fig.17 As shown, the contents of the mcmm_mat_material material table are as follows Fig.18 After importing the service data, the content of the mcmm_device device table is as follows: Fig.19 As shown, the contents of the mcmm_mat_material material table are as follows Fig. 20 shown.

[0185] The other parts of this embodiment are the same as any one of Embodiments 1 to 5, and thus will not be described in detail.

[0186] Embodiment 7:

[0187] This embodiment proposes an electronic device, including a memory and a processor; a computer program is stored in the memory; when the computer program is executed on the processor, the data import method of the above embodiment is implemented.

[0188] The other parts of this embodiment are the same as any one of the above-mentioned embodiments 1 to 6, so they will not be repeated here.

[0189] Embodiment 8:

[0190] This embodiment provides a computer-readable storage medium, on which computer instructions are stored; when the computer instructions are executed on the above-mentioned electronic device, the above-mentioned data import method is implemented.

[0191] The other parts of this embodiment are the same as any one of the above-mentioned embodiments 1 to 6, so they will not be repeated here.

[0192] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any simple modification or equivalent change made to the above embodiment based on the technical essence of the present invention shall fall within the protection scope of the present invention.

Claims

1. A method for data import, characterized in that: Use DB2 tools to design the db2import table; Build user interface based on db2import table, generate import configuration table and import link, generate template and template link; Extract configuration parameters from the imported configuration table and obtain single-source business data through templates; Then, the data import request is monitored. When a request is received, the configuration parameters and business data associated with the request are obtained, and the business data is added to the specified database business table according to the configuration parameters, and the single-source data import task is executed; When the received request is a request to import single-source data into multiple tables, the business data is added to each database business table according to the configuration parameters corresponding to each database business table.

2. A method for data importing according to claim 1, characterized in that: The specific operations include: Design the db2import table through the SPR_IMP_TABLE table configuration table and the SPR_IMPORT_FIELD field configuration table; After mapping each table element in the db2import table into configuration parameters, a user interface is constructed. An import configuration table and an import link are generated according to the configuration parameters. Then, a template and a template link are generated according to the import configuration table. The import link is connected to the user interface for users to edit configuration parameters, and the template link is connected to the user interface for users to download the template and edit business data through the template and save it as an import file; Listen for data import requests. When a request is received, parse the configuration parameters and business data associated with the request, obtain the table configuration parameters and field configuration parameters from the configuration parameters, extract the configuration identifier from the table configuration parameters, and then index the database business table that corresponds to the configuration identifier according to the configuration identifier, and add the business data to the indexed database business table according to the field configuration parameters to complete the import; When there are multiple configuration identifiers parsed, the request received is a request to import single-source data into multiple tables. Each round only executes indexing and importing tasks according to one configuration identifier. Each configuration identifier is polled until the business data of the single source is added one by one to the multiple database business tables indexed, thereby realizing the multi-table import of single-source data. A data import method according to claim 2, characterized in that: The table elements of the SPR_IMP_TABLE table configuration table include: primary key IMPORT_ID, import table IMPORT_TABLE, table identifier TABLE_IDENTITY, import start row number IMPORT_ROW, description TABLE_DESC; The table configuration parameters include: table primary key, import table, configuration identifier, start import data row, function name; The table elements of the SPR_IMP_TABLE table configuration table are mapped to the table configuration parameters one by one in sequence; The table elements in the SPR_IMPORT_FIELD field configuration table include: primary key IMPORT_FIELD_ID, import table name COL_FIELD, update primary key UPD_FILED, custom alias ALIAS, number of import file columns IDX_ORDER, parent IMPORT_ID, field type FIELD_TYPE, template export field escape EXP_SQL, import field escape IMP_SQL; The field configuration parameters include: field primary key, field table name, update field primary key, display name, EXCEL field order, field name, field type, template field escape SQL, import field escape SQL; The table elements of the SPR_IMPORT_FIELD field configuration table are mapped to the field configuration parameters one by one in sequence.

3. A method for data importing according to claim 2, characterized in that: When a request is received, it is first parsed into a Map through the form parameter universal parser, and then the configuration identifier is obtained from the Map and the order is split. Then, the configuration identifier is polled to obtain the import configuration table, import file, and database business table corresponding to each configuration identifier; During the polling process, data rationality check is performed: if the data rationality check is successful, the business data in the import file is imported into the database business table associated with the configuration table identifier according to the configuration parameters in the import configuration table, for automatic script storage; if the data rationality check is unsuccessful, the cause of the exception is fed back and the exception information is sent to the exception knowledge base.

4. A method for data importing according to claim 4, characterized in that: The data rationality check refers to judging in sequence whether the configuration identifier exists, whether the imported file can be parsed, whether the number of valid columns of the imported file and the database business table match, and whether the data types of the corresponding fields of the imported file and the database business table match; if all are "yes", the data rationality check is successful; in other cases, the data rationality check is unsuccessful.

5. A method for data importing according to claim 4, characterized in that: The automatic script storage refers to: automatically constructing an execution SQL task after business data is added to the database business table to add or modify records.

6. A method for data importing according to claim 4, characterized in that: The feedback of the exception cause and sending the exception information to the exception knowledge base means: on the one hand, sending the exception cause to the front-end user, and on the other hand, writing the exception information to the local file, waiting to retrieve the local file in idle time and updating the exception information to the exception knowledge base table for back-end iteration and improvement.

7. A data import system, used to execute the data import method according to claim 1; characterized in that: The system includes a db2import table design unit, a user interface construction unit, a configuration unit, a template unit, a monitoring unit, and a data import table execution unit; The db2import table design unit is used to design the db2import table using DB2 tools; The user interface construction unit is used to construct a user interface based on the db2import table; The configuration unit is used to generate an import configuration table and an import link, and save all configuration parameters in the import configuration table; The template unit is used to generate templates and template links, and save business data; The monitoring unit is used to monitor the request for data import; The data import table execution unit is used to obtain the configuration parameters and business data associated with the request when receiving the request, and add the business data to the specified database business table according to the configuration parameters.

8. An electronic device, characterized in that: It comprises a memory and a processor; a computer program is stored in the memory; when the computer program is executed on the processor, the method for importing data as described in any one of claims 1 to 7 is implemented.

9. A computer-readable storage medium, characterized in that: The computer readable storage medium has computer instructions stored thereon; When the computer instructions are executed on the electronic device as described in claim 9, the method for importing data as described in any one of claims 1 to 7 is implemented.

Images