KR20160064569A - Sql query processing method using mapreduce - Google Patents

Abstract

The present invention relates to a method of processing big data in a data processing system consisting of: a web server (10) for providing data for processing big data; a master node (20) for dividing data of the web server (10) and delivering divided data into various distribution nodes such that a given task can be processed in parallel; a mapper (30) as a subnode for receiving map tasks allocated by the master node (20); and Reducer (40) as a subnode for receiving Reduce tasks. The method of processing big data comprises: an SQL query analyzing step (S10) of receiving the data from the web server (10) and determining, when an SQL query occurs, which data attributes are to be delivered to the mapper (30) and the Reducer (40); a data dividing step (S20) of extracting only the attributes which are acquired in the SQL query analyzing step (S10) from input data and dividing the extracted data for the mapper (30) and the Reducer (40); a map step (S30) of transmitting the divided data to the mapper (30) and outputting only an identification (ID) of a record which satisfies predefined SQL conditions; and a Reduce step (S40) of receiving record information for a record ID from a distributed file system (DFS) on the Reducer (40) by using the record ID outputted from the mapper (30), and then performing a computation process. According to the present invention, it is possible to process a large amount of data generated from a network service in a short period of time.

Description

{SQL QUERY PROCESSING METHOD USING MAPREDUCE}

The present invention relates to an SQL query processing method using MapReduce, and more particularly, to a method for improving big data processing performance in an SQL query processing method using MapReduce.

As the Internet develops, numerous data are being generated and circulated on the Internet, and many companies, especially search engine companies and web portals, collect and accumulate such a huge amount of data as much as possible, The ability to extract meaningful information as quickly as possible is becoming a competitive advantage for companies.

For this purpose, many companies are currently studying large-scale data distribution management and job-sharing parallel processing by constructing large-scale clusters at low cost, and MapReduce model is a representative distributed parallel processing It is attracting attention as one of processing methods.

The MapReduce model is a distributed parallel processing programming model proposed by Google to support distributed parallel operations on large amounts of data stored in a cluster composed of low-cost large nodes. In the MapReduce model, Is composed of two steps: a map step in which a map function created by a user is a main axis, and a reduction step in which a user-created reduction function is a main axis. In the map stage, the multiple stages are replicated as multiple tasks and distributed parallel execution is performed in each of the stages of the map and the reduction. In the map stage, basically, key / value pairs In the Reduce step, the business logic is applied to the key / value pair extracted in the map step, and the desired final result key / value And performs an operation of obtaining a pair.

However, the MapReduce model is designed to process large amounts of one-time data (eg, log data), and reads and processes the data allocated to the map functions from start to finish. The method of scanning the entire input data every time causes a deterioration of the work processing performance.

 In order to solve such a problem, Korean Patent Registration No. 10-1331383 (data processing method and apparatus, hereinafter referred to as "prior art") is available.

The prior art is a method for processing data in a MapReduce method, comprising the steps of: storing input data; checking if a stored index file exists for the input data; if the index file does not exist, Selecting only specific data satisfying a search condition for an index from the input data by using the stored index file if the index file exists, And processing the specific data in a map-driven manner, wherein the step of generating an index file comprises the step of generating an index by scanning the input data and generating an index file including the generated index .

However, according to the prior art, some of the input data may not be used for map or reduce operations, and unnecessary data transmission can not be prevented, thereby failing to improve computer performance.

Korean Patent No. 10-1331383 (Data Processing Method and Apparatus, Registration Date November 13, 2013)

DISCLOSURE OF THE INVENTION The present invention has been conceived to solve the above problems, and it is an object of the present invention to first determine what data is required for a map and a redo job when an SQL query is given, And selectively transmitting the data to the network, thereby improving the data processing performance by reducing the network and local I / O.

In order to solve the above-mentioned problem, the present invention provides an SQL query statement that receives data of the web server 10 and grasps which data attribute should be transmitted to the mapper 30 and the reducer 40 when an SQL query statement is generated. And extracts only the attributes identified through the analysis step S10 and the SQL query analysis step S10 from the input data and then splits the extracted data into data by the mapper 30 and the reducer 40 A map step S30 for transmitting the segmented data to the mapper 30 and outputting only the IDs of the records conforming to the given SQL condition and the DFS And a reduction step (S40) of receiving record information on the ID from the reducer 40 and performing an aggregation process.

The map and the reduce function used in the mapper 30 and the reducer 40 are defined to receive a key-value pair and output another type of key value pair, Function is called in units of an aggregate key, and generates a final aggregation result.

The map step S30 includes a data transfer step S31 for transferring the record ID and record information to the input key value pair, a parsing step S32 for separating the received text format data into a plurality of attribute values, And a data output determination step (S33) of determining whether to output a specific record in the map function from the attribute value extracted in the parsing step (S32).

The redesing step S40 may include a specific record selection step S41 for determining which record is selected by the map function, a step S42 of reading a record for the ID from the DFS using the ID, A step S43 of fetching a record from the DFS and calculating an aggregation result necessary for each record, and an outputting step S44 together with the key value finally through the calculated aggregation result.

Accordingly, by using the SQL query processing method using MapReduce to shorten the SQL query processing time in the MapReduce framework, it is possible to process a large amount of data generated from social network services such as Facebook and Twitter in a shorter time It is effective.

1 is a conceptual diagram showing a conventional data processing method.
2 is a conceptual diagram showing a data processing method of the present invention.
3 is a configuration diagram of a data processing system according to the present invention.
4 is a flowchart of an SQL query processing method according to the present invention.
5 is a detailed flowchart of the map step S30 in FIG.
FIG. 6 is a detailed flowchart of the reduction step S40 in FIG.
7 is a log data table for a click on a web page advertisement according to an embodiment of the present invention.

 Hereinafter, the present invention will be described in detail with reference to the drawings.

 FIG. 1 is a conceptual diagram showing a conventional data processing method, and FIG. 2 is a conceptual diagram showing a data processing method of the present invention.

 1 and 2, in order to process a given task in the MapReduce, an existing data processing method first places an input file in a Distributed File System (DFS) , And splits the file into a plurality of logical units split.

When the input split is ready, the master node assigns a map or reduce job to each slave node, and then performs the following steps. A mapper 30 (Mapper) is assigned to the node to which the map operation is allocated for convenience, and a node (Reducer) 40 to which the reducing operation is assigned.

 (1) Map step S30 (Map phase): Each split is transmitted to one mapper 30. Each mapper 30 performs a selection / filtering operation for each record in the split. The work results are stored on the local disk of each mapper 30 for checkpointing.

 (2) Reduce phase S40 (Reduce phase): When all the mapper 30 finishes the work, the reducer 40 reads the data stored in the disk of the mapper 30 and performs an aggregation operation . The final aggregate result is stored in DFS again.

 As described above, the conventional data processing method has a problem that the network and the local I / O continuously occur in order to perform one mapping task.

Conventional data processing methods are needed to improve the failover performance, but at the same time, data processing efficiency is greatly reduced.

In order to solve the above problems, when analyzing data using an SQL query in the MapReduce framework, the present invention selectively extracts only the data necessary for the map operation and the redo operation, .

3 is a configuration diagram of a data processing system according to the present invention.

Referring to FIG. 3, the web server 10 includes a web server 10 for providing data for processing large data, a plurality of distributed nodes for dividing data of the web server 10, A mapper 30 which is a sub-node assigned a map job by the master node 20 and a reducer 40 which is a sub-node to which a redo job is allocated.

FIG. 4 is a flow chart of the SQL query processing method of the present invention, FIG. 5 is a detailed flowchart of the mapper 30 in FIG. 4, and FIG. 6 is a detailed flowchart .

4 to 6, the present invention is a method for processing big data in the data processing system of FIG. 3, wherein when an SQL query is received and data of the web server 10 is received, An SQL query analysis step S10 for determining whether a data attribute should be transmitted to the mapper 30 and the reducer 40 and a step for extracting only attributes identified through the SQL query analysis step S10 from input data, A data dividing step (S20) of dividing the extracted data into data by the mapper (30) and the reducer (40); sending the divided data to the mapper (30) And a reduction step S40 of receiving the record information on the ID from the DFS to the reducer 40 using the record ID outputted from the mapper 30 and performing the aggregation processing .

At this time, the map and the reduction function are defined to receive a key-value pair and output another type of key value pair.

The reduction function is called in units of an aggregation key, and generates a final aggregation result.

Herein, the key-value pair means a key-value pair, the key is data configured in a text format, and the Value means a sum (Count) of key data .

The map step S30 includes a data transfer step S31 for transferring a record ID and record information to an input key value pair, a parsing step for separating the received data of the text format into a plurality of attribute values S32) and a data output determination step (S33) for determining whether to output a specific record in the map function based on the attribute value extracted in the parsing step (S32).

The redesing step S40 may include a specific record selection step S41 for determining which record is selected by the map function, a step S42 for reading the ID record from the DFS using the ID, (S43) of calculating a necessary aggregation result for each record after fetching the record from the DFS, and finally outputting the key value together with the key value through the calculated aggregation result (S44).

 7 is a log data table for a click on a web page advertisement according to an embodiment of the present invention.

 A description will be made on the assumption that the log data table for the ADClick click on the Web page advertisement is an embodiment using the SQL query processing method in the MapReduce of the present invention.

Assuming that ADClick counts the number of clicks of Korean users, it is assumed that the click count is counted for each advertisement category such as fashion or home appliance.

 The SQL query for this,

 Q1. SELECT ADCategory, COUNT (*)

FROM ADClick

WHERE Nationality = "Korea"

GROUP BY ADCategory

When such a query is given, it is first determined which data attribute should be passed to the mapper 30 and the reducer 40. [ At this time, the attributes transmitted to the mapper 30 are the keys used for the aggregation and the attributes used in the selection operation of the record.

At this time, 'ADUrl', 'ADName', 'ADCategory', 'UserID', and 'Nationality' presented in Q1 are not limited to the meaning as they are the record ID provided by the ADClick.

 Also, the aggregation key is used internally to create the ID list of the selected record, and the aggregation key corresponds to the attribute defined in the GROUP BY clause. Accordingly, the aggregation result is output based on the attribute defined in the GROUP BY clause. In Q1, the aggregation key is ADCategory, and click count is calculated for each ADC category. Then, the optional attributes correspond to the attributes defined in the WHERE clause of the query statement. In Q1, Nationality is used as an optional attribute.

Therefore, when Q1 is given, only the ADCategory and the Nationality attribute values are selectively transmitted to the mapper 30.

The attributes transmitted to the reducer 40 are the keys used for the aggregation and the attributes finally output. In Q1, ADCategory is used as the aggregation key, and the output attribute corresponds to the attributes defined in the SELECT clause of the query. In the case of Q1, since no separate output attribute is given, only the ADCategory of the attribute defined in FIG. 7 is transmitted to the reducer 40. [

 Thus, once the attributes to be passed are determined, a MapReduce program is created to perform the given query. The MapReduce program consists of two MapReduce functions, a Map function and a Reduce function.

The Map function is called on a record-by-record basis, and determines whether to output a given record to the reducer 40. The Reduce function is called in units of an aggregate key to generate a final aggregate result.

 Accordingly, both functions are defined to receive a key value pair and output another type of key value pair.

C1. Function Map (RowID, Record)

Parse Record into (ADCategory, Nationality)

If Nationality = "Korea"

Output (ADCategory, RowID)

End If

C1 represents the implementation part of the Map function to process it, given the query Q1. For convenience, the implementation part of the function is described in pseudocode. The record ID and the record information are transmitted to the input key value pair of the Map function. In C1, the two parameters are marked RowID and Record. The RowID indicates the logical location where a given Record is stored in the Distributed File System (DFS), and can be accessed at any time in the DFS using the RowID.

 At this time, the Record corresponds to a record having an actual value, and is divided into several attribute values through parsing.

Therefore, the ADCategory and the Nationality attribute values are extracted through the parsing, and the remaining attributes are not extracted to the mapper 30.

 After the parsing is completed, the Map function determines whether or not to output the given record, and the condition of the record selection condition is used as it is in the WHERE clause of the given query. The number of clicks per category in Q1 should be calculated only for records satisfying the WHERE clause, and if the selection condition is satisfied, the key value pair is output.

 At this time, the key of the key value pair is delivered as ADCategory, and the value of RowID is transmitted.

Therefore, the network I / O can be reduced by transmitting the RowID to the reducer 40 without outputting the Record.

Once the Map function is called for all input records, the MapReduce framework internally groups the output records based on the ADCategory value, which is the output key.

Then, we call the Reduce function to calculate the count based on each ADCategory value. The Reduce function is called for each ADCategory value and receives a key-value pair as an input parameter.

At this time, the key is an ADCategory, and the value is a group of RowID having an ADCategory value.

 For example, the records satisfying the WHERE condition in the log data table for the click on the web page advertisement of FIG. 7 are the 1 st, 3 rd, 5 th, and 6 rd records. Therefore, four records including <Fashion>, 1>, <Game>, 3>, <"Electronics", 5>, <"Electronics", 6> are output from the Map function.

 When the above Map function is executed, the MapReduce framework groups the output records based on the ADCategory value. In this case, three groups of <"Fashion", 1>, <"Game", 3>, <"Electronics", (5, 6)> are created.

The Reduce function is then called for each group.

 C2. Finction Reduce (Key, RewIDs)

COUNT: = 0

RecList: = getRecordsFromDFS (RowIDs);

For each record in RecList

Count: = Count + 1

End for

Output (Key, Count);

In the above Reduce function, refer to RowIDs to find out which record is selected by the Map function. Since the Map function outputs the ID of the corresponding record instead of the record satisfying the WHERE clause, the Reduce function must read the ID record from the distributed file system (DFS) using the corresponding ID.

The getRecordsFromDFS (RowIDs) function corresponds to a function created to perform this role. Also, after retrieving a record from the getRecordsFromDFS (RowIDs) function, the necessary aggregation result is calculated for each record. In case of Q1, it is defined to calculate the count of the record. In this case, it is possible to obtain the desired aggregation result by initializing the count variable to 0 and incrementing count by 1 for each record. The obtained aggregation result is finally output together with the ADC category key value.

Accordingly, by using the SQL query processing method using MapReduce to shorten the SQL query processing time in the MapReduce framework, it is possible to process a large amount of data generated from social network services such as Facebook and Twitter in a shorter time It is effective.

10: Web server
20: Master Node
30: Mapper
40: Reducer

Claims (5)

A master node 20 for dividing the data of the web server 10 and moving the data to a plurality of distributed nodes to process a given job in parallel; A method for processing big data in a data processing system comprising a mapper (30) which is a sub-node assigned a map job by a node (20) and a reducer (40)
An SQL query analysis step (S10) of determining which data attribute is to be delivered to the mapper (30) and the reducer (40) when an SQL query is received based on the data of the web server (10);
A data segmenting step (S20) of extracting only the attributes identified through the SQL query analysis step (S10) from the input data and then dividing the extracted data into the mapper (30) and the reducer (40);
A map step (S30) of transmitting the divided data to the mapper (30) and outputting only the IDs of the records meeting the given SQL condition; And a reduction step (S40) of receiving record information on the ID from the DFS using the record ID output from the mapper (30) to the reducer (40) and performing an aggregation process thereon An SQL query processing method using MapReduce.
The method according to claim 1,
Wherein the map and reduce function is defined to receive a key-value pair and to output another type of key value pair.
The method according to claim 1,
Wherein the reduction function is called in units of an aggregate key, and generates a final aggregation result.
The method according to claim 1,
The map step (S30)
A data transfer step (S31) of transferring record ID and record information to the input key value pair;
A parsing step (S32) of extracting and extracting data of the received text format into a plurality of attribute values;
And a data output determination step (S33) of determining whether to output a specific record in the map function from the attribute value extracted in the parsing step (S32).
The method according to claim 1,
The reduction step (S40)
A specific record selection check step (S41) for determining which record has been selected by the map function;
Reading the record of the ID from the DFS system 20 using the corresponding ID (S42);
A step (S43) of fetching a record from the DFS system (20) and calculating a necessary aggregation result for each record;
And an output step (S44) with the key value finally through the calculated aggregation result.

Images