KR101737576B1

KR101737576B1 - Method and device for transmitting data based on sql sentences generated automatically

Info

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

Abstract

An automatically generated Structured Query Language (SQL) syntax-based data migration method according to an embodiment of the present invention includes: (a) source location identification information for data stored in a source server; Extracting target location identification information; (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) directing the data to be migrated to the target server according to the mapping logic.

Description

METHOD AND DEVICE FOR TRANSMITTING DATA BASED ON SQL SENTENCES GENERATED AUTOMATICALLY

More particularly, the present invention relates to a method and an apparatus for data transfer based on automatically generated SQL statements in order to increase efficiency and reduce labor costs in data transfer 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, in the case of a conventional tool for performing data migration based on a mapping logic created manually, it is necessary to manually register a table value for data stored in a source server and to connect to a table of a target server. Which is very inefficient and requires a long development period.

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 provide a data transfer method and data transfer method for automating data transfer by automatically generating an SQL statement for data transfer, In order to solve the problem.

According to an aspect of the present invention, there is provided an automatic generated Structured Query Language (SQL) syntax-based data migration method according to an embodiment of the present invention includes: (a) 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) directing the data to be migrated to the target server according to the mapping logic.

According to another embodiment of the present invention, an automatically generated SQL (Structured Query Language) syntax based data eviction apparatus includes a memory for storing a program for performing an automatically generated SQL syntax based data eviction method; 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, And directs the data to be transferred to the target server.

The present invention automates SQL syntax generation and data migration operations required to transfer data from a source server to a target server, thereby improving problems caused by manual work by a worker, thereby increasing efficiency and reducing labor costs. Can be achieved. In addition, in the case of data escape technology, the development cost and the development period can be drastically reduced considering that about 60% or more of the data is transferred from the source server to the target server at a ratio of 1: 1.

1 is a structural diagram of a system according to an embodiment of the present invention.
2 is a block diagram of the components of an automatically generated SQL statement-based data transfer device in accordance with an embodiment of the present invention.
FIGS. 3A and 3B are diagrams for explaining a type of an automatically generated SQL statement-based data transfer method according to an embodiment of the present invention.
FIG. 4 is a flow chart for explaining an automatic generated SQL statement-based data transfer method according to an embodiment of the present invention.

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 can perform an operation of matching the location of the data to be relocated and performing the relocation. The third server 300a is an apparatus that is physically independent of the source server 100 and the target server 200. [ Meanwhile, the transfer process may be performed by the processor 300b included in the target server 200. [ That is, depending on whether a program for performing the transfer work 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 transfer work will be referred to as "data transfer apparatus 300" for the sake of convenience.

Information stored in the data transfer apparatus 300 or information on the operation performed by the data transfer apparatus 300 may be provided to the user through the client terminal connected to the data transfer apparatus 300. The client terminal means a communication terminal connected to the data transfer apparatus 300 via wire or wireless, and may be configured by a computer, a smart phone, a display device, or the like.

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

The data transfer apparatus 300 may be configured to include a memory for storing a program (or an application) for executing the automatically generated SQL statement-based data transfer method, and a processor for executing the above 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, A syntax change history recording unit 330, and a data transfer unit 340. [

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 transfer 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 violates standard SQL, it may provide a warning or re-input request notification to the client terminal connected to the data transfer 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 transfer 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 data transfer unit 340 commands the transfer of data from the source server 100 to the target server 200 according to the automatically generated mapping logic. Specifically, the data transfer unit 340 identifies the storage location of the data to be extracted by referring to the source location identification information included in the extracted SQL syntax, loads the extracted data by referring to the target location identification information included in the load SQL syntax When the conditional statement (that is, the where clause) is included in the extracted SQL statement, the data of the source server 100 is transferred to the target server 200 according to the condition included in the conditional statement.

Here, the data transfer unit 340 can transfer data in two ways according to the allowed access authority between the source server 100 and the target server 200. [

First, when an Open Data Base Connectivity (ODBC) connection is allowed between the source server 100 and the target server 200, the data stored in the source server 100 is stored in the source server 100 And directly loaded on the target server 200.

2, when an Open Data Base Connectivity (ODBC) connection is not allowed between the source server 100 and the target server 200 as shown in FIG. 3B, the data stored in the source server 100 and the source location information A table is transferred to a file of a predetermined format in an FTP (File Transfer Protocol) method and is loaded on the target server 200.

3B, the data transfer unit 340 transfers the table including the data stored in the source server 100 and the source location identification information to the source server 100 as a file of a predetermined format, The source server 100 may be instructed to transfer the file to the target server 200. [ 3B, when the source server 100 has the access right to the target server 200, the data transfer unit 340 transmits the data stored in the source server 100 and the table including the source location identification information To the target server 200 in a file format of a predetermined format. Then, the data location identification information extracting unit 310 extracts the source location identification information from the file transmitted to the target server 200, and extracts the target location identification information from the table stored in the target server 200. The automatic SQL syntax generation unit 320 generates the mapping logic by applying the extracted identification information to the transfer logic. The data transfer unit 340 transfers the data included in the file transferred to the target server 200 according to the mapping logic To the position corresponding to the target position identification information.

At this time, the 'preset format' may be a format different from the format of the data stored in the source server 100. For example, if the data is in Excel format, the preset format may be .dta or .out format. However, the present invention is not limited to this example, and various various formats may be applied. Alternatively, the preset format may be the same format as the data. For example, the data in the form of Excel can be directly transmitted to the target server 200 in an FTP manner.

On the other hand, the data transfer apparatus 300 may provide a user interface to the client terminal side including a check box for all or part of the data so that the user can select the transfer destination. If the user checks only a part of the data to be transferred, the data transfer apparatus 300 automatically generates the SQL statement for the checked data and transfers the data, or the data transfer using the SQL syntax is executed only for the checked data Can be done.

Also, the data transfer apparatus 300 may provide the client terminal side with information on the real-time transition progress status. In this case, it is possible to provide the client terminal with information as to which of the data items checked by the user is in the process of being currently transferred, whether any items have not yet been transferred, or whether there is an item in which an error occurred during the transfer.

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

Since the above-described method is performed by the data transfer 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 transfer 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 transfer 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 transfer apparatus 300 relays the data according to the allowed access authority between the source server 100 and the target server 200 with reference to the mapping logic (S130). If the ODBC connection between the source server 100 and the target server 200 is allowed, the data of the source server 100 can be immediately stored in the target location in the target server 200 by the SQL syntax included in the mapping logic have. On the other hand, when the ODBC connection is not allowed, the source server 100 transmits the file of the table including the data and the source location identification information to the target server 200 by the FTP method, You can create mapping logic to transfer data.

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 transfer device

Claims

In a data migration method based on an automatically generated Structured Query Language (SQL) syntax executed 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) directing the data to be migrated to the target server according to the mapping logic; 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 &
A name of a table indicating a location where each data is stored or to be stored, and a column value in the table.

The method according to claim 1,
The mapping logic includes:
An automatically generated SQL statement-based data transfer 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 a condition included in the conditional statement when the extracted SQL statement includes a 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: < RTI ID = 0.0 > a < / RTI >

The method according to claim 1,
The step (c)
And loading the data stored in the source server on the target server based on the mapping logic according to an Open Data Base Connectivity (ODBC) standard defined between the source server and the target server. Based data transfer method.

The method according to claim 1,
The method comprises:
(f) if an Open Data Base Connectivity (ODBC) connection is not allowed between the source server and the target server before the step (a), the data stored in the source server and the source location identification information are stored in advance in the target server Further comprising the step of instructing the destination server to transmit data and source location identification information of the source server to be stored in a file of a predetermined format,
The step (a)
Extracting the source location identification information from a file transmitted to the target server and extracting the target location identification information stored in the target server,
The step (c)
And loading the data contained in the file stored in the target server at a location corresponding to the target location identification information according to the mapping logic.

11. The method of claim 10,
Wherein the predetermined format includes:
Wherein the format of the data stored in the source server is different from the format of the data stored in the source server.

11. The method of claim 10,
The step (f)
(f-1) storing the data stored in the source server and the source location identification information in a file of a predetermined format in the source server, and then transmitting the file to the target server; or
(f-2) transmitting the data stored in the source server and the source location identification information to the target server when the source server has the access right to the target server, The method comprising the steps of: storing the data in a database;

13. The method of claim 12,
Wherein the step (f-1)
And sending the file to the target server in an FTP (File Transfer Protocol) manner.

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

In an automatically generated Structured Query Language (SQL) syntax based data eviction device,
A memory for storing a program for performing an automatically generated SQL statement-based data migration 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,
Extracting from the escape logic configured with a basic grammar for transferring the data to the target server, extracting SQL: syntax and a loaded SQL statement, and adding the source location identification information and the target location identification information to the extracted SQL syntax and the loading SQL syntax, To generate the mapping logic,
Command to transfer the data to the target server according 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.