KR101737575B1

KR101737575B1 - Method and device for verifying data based on sql sentences generated automatically

Info

Publication number: KR101737575B1
Application number: KR1020150185130A
Authority: KR
Inventors: 박근대
Original assignee: 한국비앤에스시스템 주식회사
Priority date: 2015-11-27
Filing date: 2015-12-23
Publication date: 2017-05-29

Abstract

A method for verifying data on the basis of automatically generated structured query language (SQL) sentences according to an embodiment of the present invention comprises: (a) extracting source position identification information on data stored in a source server and target position identification information on a position of a target server where the data is to be stored; (b) generating a mapping logic by applying the source position identification information and the target position identification information to a transferring logic including a sentence for transferring the data to the target server; and (c) verifying the transferred data by comparing data before transfer with data after transfer with reference to the mapping logic after the data of the source server has been transferred to the target server.

Description

TECHNICAL FIELD [0001] The present invention relates to a method and apparatus for automatically generating SQL statements based on SQL syntax,

The present invention relates to a method and an apparatus for verifying data based on automatically generated SQL statements, and more particularly, to a method and apparatus for verifying data based on automatically generated SQL statements for increasing efficiency and reducing labor costs in data verification work, &Lt; / RTI >

Due to the development of Internet technology, all business systems of each company are being built using servers. These servers need to be constantly updated and managed, which requires data migration from the origin server to the new target server.

In this case, it is necessary to create a mapping logic for converting the table value and the column value of the data stored in the source server into the table value and the column value stored in the target server. In the conventional technology, It has been configured to write directly in Structured Query Language (SQL) syntax and manually manage the history of mapping logic changes. As a result, time is wasted when writing SQL, and the history of mapping logic changes for each developer is handled by the developer, making it impossible to manage the entire history and causing problems such as change history and data loss.

In addition, it is required to perform an operation to verify whether the data converted to the target server is fully implemented from the origin server. At this time, in the case of the related art, conversion data verification is performed in such a manner that a developer directly prepares a technical verification program, assigns a separate time at the completion of the data transfer, directly performs a verification program, and manually reports and compiles it. In this case, since the dependency on the program created by the developer becomes high, there arises a problem that the developer can not judge whether or not the data conversion verification is completed in the viewpoint of the customer.

Published Japanese Patent Application Publication No. 2003-0056153 (Publication date: 2003.07.04)

SUMMARY OF THE INVENTION The present invention has been made to solve the above problems of the related art, and it is an object of the present invention to automatically generate an SQL statement for data transfer and automatically extract and compare data to be verified on the basis thereof, A data verification method and a data verification apparatus are provided.

According to an aspect of the present invention, there is provided a method for validating an automatically generated Structured Query Language (SQL) syntax based on at least one of (a) source location identification information And extracting target location identification information for a location where the data is to be stored in a target server; (b) applying the source location identification information and the target location identification information to a migration logic including a syntax for transferring the data to a target server to generate mapping logic; And (c) verifying the diverted data by comparing the pre-diverted data with the post-diverted data with reference to the mapping logic after the data of the source server is transferred to the target server.

According to another aspect of the present invention, an automatically generated SQL (Structured Query Language) syntax-based data verification apparatus includes a memory for storing a program for performing a data verification method based on an automatically generated SQL statement; And a processor for executing a program stored in the memory, wherein, in accordance with the execution of the program, the processor is further configured to determine, based on the source location information for the data stored in the source server and the location Generating mapping logic by applying the source location identification information and the target location identification information to a migration logic including a syntax for extracting target location identification information and transferring the data to the target server, After the transfer to the target server, the transfer data is verified by comparing the pre-transfer data with the post-transfer data by referring to the mapping logic.

The present invention automates SQL statement generation and verification information input operations necessary for verifying data transferred from a source server to a target server, thereby improving problems caused by manual work by an operator, thereby increasing efficiency and reducing labor costs. Cost savings can be achieved. In addition, data transfer of corporate servers is usually done on holidays or weekends to reduce the impact on consumers, but it is not enough time to transfer and verify large amounts of data. At this time, it is possible to check whether there is an idle storage space that can be verified, and perform verification immediately upon completion of the transfer for each individual data (i.e., by performing escalation and verification in parallel) The total time required can be shortened remarkably.

1 is a structural diagram of a system according to an embodiment of the present invention.
FIG. 2 is a block diagram of the components of an automatically generated SQL statement-based data verification apparatus according to an embodiment of the present invention.
FIG. 3 is a flow chart for explaining a method of verifying data based on an automatically generated SQL statement according to an embodiment of the present invention.
FIG. 4 is a flowchart for explaining the verification process (S140) of FIG. 3 in detail.

Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings, which will be readily apparent to those skilled in the art. The present invention may, however, be embodied in many different forms and should not be construed as limited to the embodiments set forth herein. In order to clearly illustrate the present invention, parts not related to the description are omitted, and similar parts are denoted by like reference characters throughout the specification.

Throughout the specification, when a part is referred to as being "connected" to another part, it includes not only "directly connected" but also "electrically connected" with another part in between . Also, when an element is referred to as "comprising ", it means that it can include other elements as well, without departing from the other elements unless specifically stated otherwise.

In this specification, the term " part " includes a unit realized by hardware, a unit realized by software, and a unit realized by using both. Further, one unit may be implemented using two or more hardware, or two or more units may be implemented by one hardware. On the other hand, 'to' is not limited to software or hardware, 'to' may be configured to be an addressable storage medium, and may be configured to play one or more processors. Thus, by way of example, 'parts' may refer to components such as software components, object-oriented software components, class components and task components, and processes, functions, , Subroutines, segments of program code, drivers, firmware, microcode, circuitry, data, databases, data structures, tables, arrays, and variables. The functions provided in the components and components may be further combined with a smaller number of components and components or further components and components. In addition, the components and components may be implemented to play back one or more CPUs in a device or a secure multimedia card.

Hereinafter, a system according to an embodiment of the present invention will be described in detail with reference to FIG.

The above system includes a source server 100 and a target server 200.

The source server 100 is a server for storing data. For example, the data may be information on card usage information managed by a credit card company, card user information, and the like, and may be related to various other information. The data stored in the server includes location identification information for each data. Specifically, the data may be information described in a table such as an Excel or the like. At this time, the location identification information of the data may be the name of the table or a column value in which the corresponding data is written in the table.

The data stored in the source server 100 may be transferred to the target server 200. At this time, the location of the data to be extracted from the source server 100 and the location identification information of the location to which the data is to be loaded in the target server 200 are matched with each other, and the data can be transferred after the matching is completed.

At this time, the third server 300a may perform an operation of matching the location of the data to be relocated and performing the relocation and verifying the transferred data. The third server 300a is an apparatus that is physically independent of the source server 100 and the target server 200. [ Meanwhile, the verification process may be performed by the processor 300b included in the target server 200. [ That is, depending on whether a program for performing the verification operation is stored in the memory of the third server 300a or in the memory of the target server 200, the task performing entity may be different.

Hereinafter, a server that is the subject of performing the data verification operation will be referred to as "data verification apparatus 300" for convenience.

Information stored in the data verifying apparatus 300 or information on a job performed by the data verifying apparatus 300 may be provided to the user through a client terminal (or "user terminal") connected to the data verifying apparatus 300 have. The client terminal means a communication terminal connected to the data verification apparatus 300 through wire or wireless, and may be configured by a computer, a smart phone, a display device, or the like.

Hereinafter, with reference to FIG. 2, the construction and operation of an automatically generated SQL statement-based data verification apparatus 300 according to an embodiment of the present invention will be described in detail.

The data verification apparatus 300 may be configured to include a memory for storing a program (or an application) for performing an automatically generated SQL statement-based data verification method, and a processor for executing the program. Here, the processor can perform various functions according to the execution of the program stored in the memory, and the detailed modules included in the processor according to each function are stored in the data location identification information extraction unit 310, the SQL syntax automatic generation unit 320, The syntax change history recording unit 330, the migration data verification unit 340, the idle storage space verification unit 350, and the verification result management and provision unit 360.

The data location identification information extraction unit 310 extracts the source location identification information for the data stored in the source server 100 and the target location identification information for the location where the data is to be stored in the target server 200. The source location identification information includes a name of a table in which data is stored in the source server 100 and a column value in which data is recorded in the table. The target location identification information includes the name of the table to be stored after transferring the data to the target server 200 and column values in the table.

The automatic SQL syntax generation unit 320 generates the mapping logic by applying the source location identification information and the target location identification information to the migration logic including the syntax for transferring the data to the target server 200.

Specifically, the SQL syntax automatic generation unit 320 automatically analyzes the escape logic stored in the data verification apparatus 300 and automatically parses the extracted SQL syntax and the loaded SQL syntax. Escalation logic means logic with only basic SQL statements tailored to the grammar for data extraction and loading, and the escape logic can be data in the form of excel (. Exl) or text documents (.txt). The extract SQL statement included in the escape logic includes the select clause, the from clause, and the where clause. The from clause is a statement that describes the name of the table for the data to be extracted. The select clause is a statement that describes the column values of the table. The where clause is a conditional statement describing the transfer condition. For example, in the conditional statement, a condition for mapping the number of data stored in the source server 100 to the number of data to be loaded in the target server 200 may be described. That is, in the case of 1: 1 transfer, a condition for loading one data stored in the source server 100 in the target server 200 may be described. The condition for the relationship between the number of data stored in the source server 100 and the number of data to be stored in the target server 200 to be 1: N and N: 1 is described, A condition may be described in which a new storage location is newly created and data is stored. The load SQL statement can include an insert clause and a load clause, and the insert clause is a statement in which the name of the table to which the data is to be loaded and the column values in the table are described.

The automatic SQL syntax generation unit 320 generates the mapping logic by inputting the corresponding one of the source location identification information and the target location identification information into each SQL statement based on the analysis result of the migration logic. In addition, the SQL syntax automatic generation unit 320 may be configured to directly store the generated mapping logic or transmit the SQL syntax to the client terminal connected to the data escape server, and store the data after confirmation by the user.

On the other hand, if the mapping logic based on the generated SQL statement is in violation of the standard SQL, a warning or re-input request notification may be provided to the client terminal connected to the data verification apparatus 300 in communication.

The SQL syntax change history recording unit 330 records and stores change information when at least one of the source location identification information, the target location identification information, and the escape logic is changed by the user and a change is made to the mapping logic. For example, when the electronic document (Excel or text document) for the source location identification information, the target location identification information, and the escape logic is previously stored in the data verification apparatus 300, (I.e., data before change, data after change, date and time of change, changed user, etc.) by comparing the new electronic document with the previously stored electronic document, and records and stores the change information To the mapping logic immediately. Alternatively, when a modification regarding the mapping logic is received from the client terminal, the mapping logic reflecting the modification can be stored immediately, and the history of the modification can be stored as the modification information.

The SQL syntax change history recording unit 330 may process the stored change information and store the modified change information in the form of statistics or a change history list. For example, it is possible to store a list in which change items are arranged in chronological order so that the change items at the time of the last change are arranged at the top, or to store change information in the form of a table or a graph.

Thereafter, when receiving a request for a change history of the mapping logic from the client terminal, the SQL syntax change log recording unit 330 may provide data corresponding to items requested by the user such as a list, a table, and a graph to the client terminal.

On the other hand, the migration data verifier 340 refers to the mapping logic, after the data of the source server 100 is transferred to the target server 200 based on the SQL syntax of the mapping logic, and then compare the data before the migration with the data after the migration Thereby validating the transferred data.

In the case of conventional validation, there was no way for the customer to verify that the validation had been done, since the developer only performed the verification and simply informed the result to the customer. Accordingly, the transfer data verifying unit 340 may request the designation of the verification target data through the client terminal connected to the data transfer apparatus, and then input the designation result and perform the verification on the designated data. For example, the transfer data verifier 340 may provide a user interface in which a check box is formed for each table, so that the user can designate only a table desired to be transferred. In particular, data (or tables) that can be verified only by applying complex logic can be excluded from the verification target by the user's selection. In the case of data (or tables) that require complex logic, manual correction by the developer or factors of change / addition of the integrated core (coa) can be computed and uploaded.

The manner in which the transfer data verification unit 340 performs the verification is as follows. Specifically, the migration data verifier 340 extracts the data stored in the source server 100 with reference to the source location identification information included in the mapping logic, and refers to the target location identification information included in the mapping logic, 200 < / RTI > Subsequently, the transfer data verifying unit 340 compares the data extracted from the source server 100 with the data extracted from the target server 200, and judges whether or not the data matches, thereby completing the verification. Then, the transfer-data verifying unit 340 can perform the number verification, the sum verification, and the code verification of the data transferred in the above-described manner. In other words, conventionally, the developer has manually written the pre-transfer data and the post-transfer data into the data comparison module and the verification has been performed, so that a lot of resources and development costs have been wasted. However, the migration data verifier 340 refers to the mapping logic automatically generated in the SQL syntax, automatically identifies and extracts the location of the pre-migration data and the post-migration data, and automatically performs the comparison, Cost can be reduced.

On the other hand, when a company that holds a vast amount of data (for example, a card company) wants to transfer data to a new server, it takes a very long time, at least 15-16 hours Is required. As a result, corporate data transfer is often done on weekends when it does not affect consumers. However, after all the data escapes are completed, if the verification is sequentially performed, it takes a long time as the data escapes, which can lead to a disruption to the business operation of the enterprise.

Accordingly, the idle storage space verification unit 350 determines whether there is an idle storage space having a predetermined size (in bytes) or more to record and store the verification result and verification data in the data verification apparatus 300. When it is determined that there is an idle storage space, the idle storage space verification unit 350 transmits the data to the migration data verification unit 340 so that the verification is performed in real time using the idle storage space .

At this time, data verification can be performed on a table basis. That is, a plurality of tables including a plurality of data are stored in the source server 100, and are transferred to the target server 200 on a table-by-table basis. Thus, if idle storage space is identified and it is determined that there is a table that has been migrated, verification can be performed for each migration end table.

Meanwhile, when data transfer is performed by the self-transfer module of the data verification apparatus 300, it is easy to confirm whether or not the transfer of the data (or the table) is completed. However, if the module of the external vendor The data verification can be performed in real time only by receiving a status value indicating whether the transfer of the data (or the table) from the external vendor's server has been terminated. For this purpose, the idle storage space checking unit 350 checks the transfer end status of each table included in the meta information or log information stored in the external company's server (i.e., the external server) Can be confirmed. Preferably, it is preferable to access the meta information and match the task ID for each table to check whether or not the transfer of each table is completed. However, if access to the meta information is not permitted, Can confirm whether or not the transfer is completed.

The verification result management and providing unit 360 can provide a result of performing the verification to the client terminal. At this time, the verification result management and providing unit 360 may provide only a verification result for the designated data when the user specifies the verification target in advance. Also, the verification result management and provisioning unit 360 can process and analyze the verification result in the form of a list, a table, or a graph to easily provide the user with the information. In addition, when the verification is performed directly on the terminated data, it is possible to provide a list and a result of the data being verified in real time.

Hereinafter, with reference to FIG. 3 and FIG. 4, an automatically generated SQL statement-based data verification method according to an embodiment of the present invention will be described in detail.

Since the above-described method is performed by the data verifying apparatus 300 described with reference to FIG. 2, even if there is a content omitted below, the content described with reference to FIG. 2 will be omitted.

First, the data verification apparatus 300 extracts the source location identification information for the data stored in the source server 100 and the target location identification information for the location where the data is to be stored in the target server 200 (S110).

Subsequently, mapping logic is generated by applying the source location identification information and the target location identification information to the migration logic (S120). The data verifying apparatus 300 can automatically analyze the escape logic, automatically parse the extracted SQL statement and the loaded SQL statement, and then generate the mapping logic by applying appropriate identification information. The generated mapping logic includes information as to which location in the target server 200 the specific data stored in the specific location of the source server 100 should be transferred.

The data verification apparatus 300 refers to the mapping logic and transfers the data according to the allowed access authority between the source server 100 and the target server 200 (S130). If the module of the external vendor is used, the external server may perform step S130.

Subsequently, the data verification apparatus 300 verifies the transferred data with reference to the mapping logic (S140).

Specifically, referring to FIG. 4, a verification target data designation result is received from a user using a client terminal (S141). Then, the data verification apparatus 300 determines whether there is an idle storage space for performing verification in the data verification apparatus 300 (S142). Then, the data verification apparatus 300 extracts the data stored in the source server 100 and the data transferred to the target server 200 by referring to the mapping logic (S143). At this time, it is possible to refer to the source location information and the target location identification information in the mapping logic. The data verification apparatus 300 compares the extracted data and verifies whether or not they are matched (S144). Then, the verification result is analyzed by using a table or a graph to provide the user with easy visibility (S145).

One embodiment of the present invention may also be embodied in the form of a recording medium including instructions executable by a computer, such as program modules, being executed by a computer. Computer readable media can be any available media that can be accessed by a computer and includes both volatile and nonvolatile media, removable and non-removable media. In addition, the computer-readable medium may include both computer storage media and communication media. Computer storage media includes both volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data. Communication media typically includes any information delivery media, including computer readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave, or other transport mechanism.

While the methods and systems of the present invention have been described in connection with specific embodiments, some or all of those elements or operations may be implemented using a computer system having a general purpose hardware architecture.

It will be understood by those skilled in the art that the foregoing description of the present invention is for illustrative purposes only and that those of ordinary skill in the art can readily understand that various changes and modifications may be made without departing from the spirit or essential characteristics of the present invention. will be. It is therefore to be understood that the above-described embodiments are illustrative in all aspects and not restrictive. For example, each component described as a single entity may be distributed and implemented, and components described as being distributed may also be implemented in a combined form.

The scope of the present invention is defined by the appended claims rather than the detailed description and all changes or modifications derived from the meaning and scope of the claims and their equivalents are to be construed as being included within the scope of the present invention do.

100: source server 200: target server
300: data verification device

Claims

A method for automatically generating a Structured Query Language (SQL) syntax-based data verification performed by a server,
(a) extracting source location identification information for data stored in a source server and target location identification information for a location where the data is to be stored in a target server;
(b) identifying an extracted SQL statement and a loaded SQL statement from an escape logic configured with a basic grammar for transferring the data to a target server, and storing the source location identification information and the target location identification Applying the information to generate mapping logic;
(c) verifying the transferred data by comparing the pre-transfer data with the post-transfer data by referring to the mapping logic after the data of the source server is transferred to the target server; And
(d) when receiving an electronic document representing the source location identification information, the target location identification information, and the ebb logic from the user, the previously stored electronic document and the received electronic document are compared with each other to extract change information And modifying the mapping logic based on the change information,
Wherein the change information includes source location identification information, target location identification information, time information, and user information before and after the change.

The method according to claim 1,
Wherein the source location identification information and the target location identification information are &
Wherein the name of the table indicating the location where the data is to be stored or stored and the column value in the table.

The method according to claim 1,
The mapping logic includes:
An automatically generated SQL statement-based data validation method written in SQL syntax.

delete

The method according to claim 1,
The step (c)
Identifies a storage location of data to be extracted by referring to the source location identification information included in the extracted SQL syntax, identifies a storage location where the extracted data is to be loaded with reference to the target location identification information included in the load SQL statement, And transferring the data of the source server to the target server according to conditions included in the conditional statement when the extracted SQL statement includes the conditional statement.

delete

The method according to claim 1,
(e) receiving a request for a change log of the mapping logic from the user after the step (d), providing the user with a list in which change items are arranged in chronological order based on the stored change information Further comprising the steps of: a) generating an automatically generated SQL statement based on the generated data;

The method according to claim 1,
The step (c)
The method comprising: requesting a user terminal to designate a verification target data; receiving a designation result; and performing verification on the designated data.

10. The method of claim 9,
The method comprises:
(f) analyzing the verification result of the designated data after the step (c) and providing the analyzed result to the user terminal.

The method according to claim 1,
The step (c)
(c-1) extracting data stored in the source server with reference to source location identification information included in the mapping logic, extracting data transferred to the target server by referring to target location identification information included in the mapping logic, ; And
(c-2) comparing the data extracted from the source server with the data extracted from the target server and performing verification by determining whether or not the data matches the data extracted from the target server, wherein the automatically generated SQL statement-based data verification Way.

The method according to claim 1,
The step (c)
(c-3) determining whether there is an idle storage space of at least a predetermined size (in bytes) in the server performing the data verification method; And
(c-4) performing verification of the migration end data using the idle storage space each time migration end data is generated after the data migration starts, if it is determined that the idle storage space exists ; And an automatically generated SQL statement-based data verification method.

13. The method of claim 12,
When a plurality of tables are stored in the source server and a plurality of data is stored for each table,
The step (c-4)
And performing verification of the migration end data on a table-by-table basis every time a table with the migration termination is generated.

14. The method of claim 13,
The step (c-4)
If the transfer of the data is performed by an external server, checking whether transfer of each table is completed or not by confirming the transfer end status value of each table included in the meta information or log information stored in the external server, And verifying the data in the identified table. &Lt; Desc / Clms Page number 21 >

The method according to claim 1,
The server for performing the automatically generated SQL statement-
Wherein the third server is a third server physically independent of the target server or the source server and the target server.

A data verification apparatus based on automatically generated Structured Query Language (SQL) syntax,
A memory for storing a program for performing an automatically generated SQL statement-based data verification method; And
And a processor for executing a program stored in the memory,
Wherein the processor extracts the source location identification information for the data stored in the source server and the target location identification information for the location where the data is to be stored in the target server,
Identifying the extracted SQL statement and the loaded SQL statement from the escape logic configured with a basic grammar for transferring the data to the target server, and storing the source location identification information and the target location identification information in the extracted SQL syntax and the loaded SQL syntax To generate mapping logic,
After the data of the source server is transferred to the target server, verifying the transferred data by comparing the pre-transfer data with the post-transfer data by referring to the mapping logic,
Stored electronic document and the received electronic document when the electronic document indicating the source location identification information, the target location identification information, and the escape logic is received from a user, extracts and stores the change information, Modifying the mapping logic based on the change information,
Wherein the change information includes source location identification information, target location identification information, time information, and user information before and after the change.