KR20180085633A - Method and apparatus for processing query - Google Patents

Abstract

Provided are a query processing method and apparatus, which can increase query processing speed. The query processing method comprises selecting a partition corresponding to an input query if there are partitions in a data table when the query is input; selecting at least one partition column set corresponding to the input query when there is at least one partition column set in the selected partition; and processing the query for the selected partition column set.

Description

[0001] The present invention relates to a method and apparatus for processing a query,

The present invention relates to a method and apparatus for processing queries.

Recently, Hadoop (Hadoop), an open-source project to support parallel processing of large-scale data, has been studied extensively as major data processing has been studied. Hadoop is made up of Hadoop Distribute File System (HDFS), a platform for distributed storage and management of large amounts of data, and MapReduce (MR), a framework for large scale distributed parallel processing of data. Many query processing techniques using deuce have been studied.

Structured Query Language (SQL) -on-Hadoop is a system that provides SQL query processing for data stored in the Hadoop Distributed File System (HDFS). Most SQL-on-Hadoop systems are built on a new distributed processing model and framework without using the existing MapleDesk architecture from Hadoop. There are a number of SQL-on-Hadoop systems, including Apache Hive, Apache Tajo, Impala in Claudia, and Presto on Facebook.

The SQL-on-Hadoop system can distribute queries for large amounts of data distributed across multiple nodes, but in the process of moving data to the nodes that process the queries, many disk I / O and network Since the transmission is required, the query processing speed becomes slow. Techniques such as materialized views, query column sets, and data partitions have been utilized to improve the slow throughput of HDFS-based distributed data.

A problem to be solved by the present invention is to provide a method and apparatus capable of further improving a query processing speed.

According to an aspect of the present invention, there is provided a method of processing a query, the method comprising: selecting a partition corresponding to the input query, when a query is input; Selecting at least one partitioned column set corresponding to the input query if the selected partition has at least one partitioned column set; And processing the query for the selected partitioned column set.

Wherein the partition column set is a data structure in which, when the data table is divided into at least one horizontal partition, a column set including at least one column constituting the data table for each of the horizontal partitions is stored as a cache table have.

At least one partition column set is selectively formed for each partition of the data table, and the number of partition column sets formed for each partition may be different from the type of columns forming the partition column set.

Wherein the step of selecting the partitioned column set comprises: analyzing a condition of the input query; if at least one partitioned column set formed for the selected partition is formed, selecting one of the at least one partitioned column set You can select one partition column set.

The query processing method may further include: processing the query for the data table when the data table does not have a partition; And if the selected partition does not have a partition column set, processing the query for the selected partition.

The query processing device may be a distributed query processing engine.

According to another aspect of the present invention, there is provided a method of constructing a column set for query processing, the method comprising: analyzing a workload of a query to divide the data table into a plurality of horizontal partitions; And selectively configuring at least one partition column set including at least one column constituting the data table based on the analysis result of the query workload for each horizontal partition.

The number of partition column sets formed for each horizontal partition may be different.

The types of columns constituting the partition column set may be different for each horizontal partition.

The configuring may include storing the partitioned column set as a cache table.

The step of configuring may further include, if the plurality of partition column sets are formed for each horizontal partition, consolidating at least two of the plurality of partition column sets into at least one horizontal partition .

According to another aspect of the present invention, there is provided a query processing apparatus including: an input / output unit configured to receive a query; And a processor connected to the input / output unit and performing a query process, wherein the processor selects a horizontal partition corresponding to the input query from the horizontal partitions of the data table when a query is input through the input / output unit, Select at least one partitioned column set corresponding to the input query if the selected horizontal partition has at least one partitioned column set, and process the query for the selected partitioned column set.

Wherein the partition column set is a data structure in which, when the data table is divided into at least one horizontal partition, a column set including at least one column constituting the data table for each of the horizontal partitions is stored as a cache table have.

At least one partition column set is selectively formed for each partition of the data table, and the number of partition column sets formed for each partition may be different from the type of columns forming the partition column set.

Wherein the processor analyzes the condition of the input query and, if at least one partition column set is formed for the selected partition, selects one of the at least one partition column set based on the analysis result . ≪ / RTI >

Wherein the data block corresponding to the horizontal partition and the data block corresponding to the partition column set are distributedly stored in various nodes of the distributed file system, and the query processing device includes a partition column set of the horizontal partition corresponding to the input query The corresponding data block can be read and the query can be processed.

According to an embodiment of the present invention, a query processing speed can be improved by processing a query using a partitioned column set using a partition and a query column set integrally. In addition, by constructing a horizontal partition for a table of distributed data and analyzing a query workload for each horizontal partition, a partition column set is constructed, and the query processing performance can be improved by filtering the data processed in the query in advance.

Particularly, in a distributed query processing system having a large number of OnLine Analytical Processing (OLAP) operations, only the partition column set corresponding to the query can be read and transmitted to the corresponding node, thereby increasing the query processing speed.

Figure 1 is an illustration of a partitioned column set according to an embodiment of the present invention.
2 is a flowchart of a process of configuring a partitioned column set according to an embodiment of the present invention.
3 is a flowchart of a query processing method according to an embodiment of the present invention.
4 is a diagram illustrating an example of a query process according to an embodiment of the present invention.
5 is a structural diagram of a query processing apparatus 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 so that those skilled in the art can easily carry out the present invention. 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 an element is referred to as "comprising ", it means that it can include other elements as well, without excluding other elements unless specifically stated otherwise.

Hereinafter, a method and apparatus for processing a query according to an embodiment of the present invention will be described with reference to the drawings.

In the embodiment of the present invention, a query is processed by using a partition column set which includes a database partition and a query column set.

Database partitioning is the physical partitioning of a table into small parts called partitions. A horizontal partition is a method of dividing the data of a table into several sub-tables based on the value of a specific key column. According to a criterion for dividing a record, a method such as a range partition or a hash partition Is used. Vertical partitions divide the data in a table into several sub-tables, each sub-table being a sub-table with disjoint sets of columns that do not overlap. At this time, the key columns of the original table are duplicated in all the subtables. With database partitions, query performance can be improved by processing only the database partitions needed for the query without processing the entire table data when the query is received. Particularly, when processing distributed data, it is very costly to transfer data generated between intermediate nodes of a query tree (abstract syntax tree), etc., between nodes. Therefore, by using database partitions, The processing speed can be improved by filtering at an early stage.

The query column set is a physical realization of only the columns frequently used in the WHERE, GROUP BY, HAVING clause, etc. of the query by analyzing the query workload. When the query is later received, It can speed up. The query column set is effective for increasing the query processing speed in a system that mainly includes on-line analytical processing (OLAP) operations such as a Structured Query Language (SQL) -on-Hadoop system. A query column set is distinct from a horizontal partition in that it does not store all the columns of the original table.

In the embodiment of the present invention, a query is processed by integrating a database partition and a query column set, and specifically, a partition column set structure integrating a horizontal partition and a query column set is provided. It provides a data structure that physically stores a table (also called a data table) of the database in horizontal partitions, and physically stores a set of columns for the divided horizontal partitions. Here, the column set for the horizontal partition of the table is called the partition column set.

Figure 1 is an illustration of a partitioned column set according to an embodiment of the present invention.

1, partition column sets 121, 122, 131, 141 and 142 are formed for the data table 100 (FIG. 1 (a)).

Divides the data table 100 into horizontal partitions that are divided to include all the data in accordance with the reference (for example, a range or a hash value) without overlapping. For example, as shown in FIG. 1, the data table 100 can be divided into three horizontal partitions 120, 130, and 140 according to the value of SHIPDATE (FIG. 1B). That is, if the value of SHIPDATE is greater than or equal to "1994-01-01", and the value of SHIPDATE is greater than or equal to "1997-01-01" A horizontal partition 2 130 including small data, and a horizontal partition 3 140 having a value of SHIPDATE including data larger than "1997-01-01 ".

In addition, the query column set 110, 111 can be configured for the data table 100. A query column set is a data structure that stores frequently used column sets for a data table as a cache table. A query column set is a set of frequently used columns in a query such as WHERE, HAVING, GROUPBY, and so on. A set of query columns can be created according to a table. For example, for the data table 100, a query column set 110 for {ORDERKEY, PARTKEY, LINENUMBER, SUPPKEY} and a column set 111 for {ORDERKEY, TAX, QUANTITY, SHIPDATE} (FIG. 1 (b)).

Based on the concept of the horizontal partitions 120, 130, and 140 and the query column sets 110 and 111 that can be configured for the data table 100 as described above, in the embodiment of the present invention, . A partitioned column set is a set of frequently used query columns for each horizontal partition of the data table as a cache table. For example, the partition columns 121, 122, 131, 141, and 42 may be configured by grouping frequently used columns for each of the horizontal partitions 120, 130, and 140 configured for the data table 100 (Fig. 1 (d)). When configuring such a partitioned column set, the number of partitioned column sets and the type of columns that make up the column set may vary from partition to partition.

By configuring the partition column set in this way, a lot of unnecessary data can be filtered and processed in accordance with the query in the entire data table, and the query processing speed can be increased.

2 is a flowchart of a process of configuring a partitioned column set according to an embodiment of the present invention.

Here, an example of configuring a partition column set in the distributed query processing engine is described as an example, but the present invention is not limited thereto.

The distributed query processing engine stores the query workload according to the query processing. In order to construct a partition column set, as shown in FIG. 2, the query workload is first analyzed (S100) and the candidate horizontal partition is selected (S110). Based on the query workload analysis result, it is possible to determine a horizontal partition according to a certain criterion to construct a horizontal partition for the data table, and to select at least one of the configured horizontal partitions as the candidate horizontal partition. Or if the horizontal partitions are already configured for the data table, at least one of the horizontal partitions already configured may be selected as the candidate horizontal partition based on the query workload analysis result.

Thereafter, for each selected at least one candidate horizontal partition, a candidate partition column set is configured (S120). Based on the query workload analysis result for each candidate horizontal partition, the candidate partition column set is constructed by grouping frequently used columns. For example, a candidate partition column set can be constructed by grouping the columns corresponding to that column set, assuming that the column sets that were used frequently in the past will be used later.

 The candidate partition column sets thus generated fit very well into the query workload, so they can be processed quickly when executing a specific query, but the overall query performance can be rather poor. By combining several candidate partition column set candidates, certain query processing can be performed less quickly, but the overall query performance can be improved. Therefore, in order to improve the performance of the entire query, several candidate partition column set candidates are integrated (S130). Finally, a partition column set is configured through the integration process (S140). On the other hand, step S130 may be selectively performed.

In the meantime, the partition column set according to the embodiment of the present invention may be formed with zero or more for the horizontal partition. That is, at least one partition column set may be formed for an arbitrary horizontal partition, and a partition column set may not be formed for another horizontal partition. This may indicate that at least one partition column set may be selectively formed for each horizontal partition.

Next, a method of processing a query using a partitioned column set configured as described above will be described.

3 is a flowchart of a query processing method according to an embodiment of the present invention.

When a query is input from the terminal (S300), the query processing apparatus determines whether there is a partition in the data tables corresponding to the query (S310). If there is a partition (horizontal partition, etc.) in the data table corresponding to the query, the necessary partition is selected by analyzing the query (S320). For example, by analyzing conditional clauses such as WHERE, GROUPBY, and HAVING of a query, a partition having data necessary for a query among a plurality of partitions is selected.

Then, it is determined whether there is a column set (e.g., a partition column set) for the selected partition (S330). If the selected partition has a column set, for example, a partition column set, the query is analyzed to select a required partition column set (S340). That is, it analyzes WHERE, GROUPBY, HAVING, and SELECT clauses of the query to select the required partition column set. After the partition is selected and the partition column is selected for the partition, the query processor processes the selected partition column set (S350) and returns the result (S380).

On the other hand, if there is no partition column set in the selected partition, the query processing unit processes the selected partition (S360) and returns the result (S380).

On the other hand, if there is no partition in the data tables in step S310, the query processing unit processes the query on the data table (S390) and returns the result (S380).

In a distributed file system such as Hadoop, the original table is divided into logical partitions according to the range specified by the user, and the logical partitions are physically distributed redundantly in a plurality of nodes. When a query comes in, you can perform operations such as scans and joins only on partitions that contain data necessary for query processing. Therefore, unnecessary disk I / O and network transmission can be reduced since only the necessary data partitions can be moved to the node processing the data.

A process of processing a query by applying a partition column set to a distributed file system based on the query processing method according to the embodiment of the present invention will be described.

4 is a diagram illustrating an example of a query process according to an embodiment of the present invention.

Here, it is assumed that the horizontal partition and the partition column set are distributedly stored in various nodes of the distributed file system.

The horizontal partitions 411, 412 and 413 divide the data table 410 into logical horizontal partitions according to the reference (range or hash value, for example SHIPDATE in this example) 431, 433, and 435, and are distributedly stored in the plurality of data nodes N1 to N4. Here, the data node refers to the data node of Hadoop. The partition column sets 421 to 423 are configured for each horizontal partition and the partition column sets 421 to 423 are divided and stored into data blocks 432, 434 and 436 of a predetermined size.

In this way, when the query 401 is entered while the horizontal partitions of the data table and the data blocks corresponding to the partition column set corresponding to each horizontal partition are distributed and stored in the data nodes N1 to N4, The horizontal partition is selected. For example, the horizontal partition 411 is selected from the partitions according to the SHIPDATE (1993-09-02) value of the WHERE clause of the query 401, the partition column set 421 exists in the selected horizontal partition 411 , And the partition column set 421 includes all the columns required for the query, so that the partition column set 421 is processed for the query.

At this time, the data node N1 that processes the query reads and fetches the data block 432 for processing the query. Therefore, compared to the cost of reading the entire data table, transferring the entire data table to the corresponding node, or reading the corresponding horizontal partition and transferring the same to the corresponding node, in the embodiment of the present invention, only the partition column set is read and transmitted to the corresponding node. . In other words, both the I / O cost of reading data and the cost of transferring data to the node processing the query can be saved, thereby improving the query processing speed. By using the partitioned column set structure like this, data that is not needed in the query is filtered at the initial stage, thereby reducing unnecessary disk I / O and network transmission.

5 is a structural diagram of a query processing apparatus according to an embodiment of the present invention.

5, the query processing apparatus 1 according to the embodiment of the present invention includes a processor 10, a memory 20, and an input / output unit 30. The processor 10 may be configured to implement the methods described above based on Figs.

The memory 20 is connected to the processor 10 and stores various information related to the operation of the processor 10. [ The memory 20 stores instructions for operations to be performed by the processor 10 or temporarily stores the instructions loaded from a storage device (not shown).

The processor 10 may execute instructions that are stored or loaded into the memory 20. The processor 10 and the memory 20 are connected to each other via a bus (not shown), and an input / output interface (not shown) may be connected to the bus.

The input / output unit 30 is configured to output the processing result of the processor 10 or to receive the query and provide it to the processor 10.

The embodiments of the present invention are not limited to the above-described apparatuses and / or methods, but may be implemented through a program for realizing functions corresponding to the configuration of the embodiment of the present invention, a recording medium on which the program is recorded And such an embodiment can be easily implemented by those skilled in the art from the description of the embodiments described above.

While the present invention has been particularly shown and described with reference to exemplary embodiments thereof, it is to be understood that the invention is not limited to the disclosed exemplary embodiments, It belongs to the scope of right.

Claims (16)

A query processing apparatus, as a method for processing a query,
Selecting a partition corresponding to the input query if a partition exists in the data table when a query is input;
Selecting at least one partitioned column set corresponding to the input query if the selected partition has at least one partitioned column set; And
Processing the query for the selected partitioned column set
The query processing method comprising: The method according to claim 1,
Wherein the partitioned column set is a data structure in which, when the data table is divided into at least one horizontal partition, a column set including at least one column constituting the data table for each of the horizontal partitions is stored as a cache table, Query processing method. The method according to claim 1,
Wherein at least one partition column set is selectively formed for each partition of the data table, and the number of partition column sets formed for each partition is different from the type of columns forming the partition column set. The method of claim 3,
Wherein the step of selecting the partitioned column set comprises: analyzing a condition of the input query; if at least one partitioned column set formed for the selected partition is formed, selecting one of the at least one partitioned column set A query processing method that selects a set of partitioned columns. The method according to claim 1,
Processing the query for the data table if the data table does not have a partition; And
If the selected partition does not have a partition column set, processing the query for the selected partition
Further comprising the steps of: The method according to claim 1,
Wherein the query processing device is a distributed query processing engine. A method for constructing a set of columns for query processing,
Analyzing the workload of the query to divide the data table into a plurality of horizontal partitions; And
Selectively constructing at least one partition column set including at least one column constituting the data table based on the analysis result of the query workload for each horizontal partition
≪ / RTI > 8. The method of claim 7,
Wherein the number of partition column sets formed for each horizontal partition is different. 8. The method of claim 7,
Wherein a type of a column constituting a partition column set is different for each horizontal partition. 8. The method of claim 7,
Wherein the configuring step comprises storing the partitioned column set as a cache table. 8. The method of claim 7,
The constructing step
Integrating at least two of the plurality of partitioned column sets into at least one horizontal partition when a plurality of partitioned column sets are formed for each horizontal partition;
≪ / RTI > An input / output unit configured to receive a query; And
A processor connected to the input / output unit and performing a query processing,
The processor comprising:
A horizontal partition corresponding to the input query is selected from horizontal partitions of the data table when the input is a query through the input / output unit, and if there is at least one partition column set in the selected horizontal partition, Select at least one partitioned column set, and process the query for the selected partitioned column set. 13. The method of claim 12,
Wherein the partitioned column set is a data structure in which, when the data table is divided into at least one horizontal partition, a column set including at least one column constituting the data table for each of the horizontal partitions is stored as a cache table, Query processing device. 13. The method of claim 12,
Wherein at least one partition column set is selectively formed for each partition of the data table, and the number of partition column sets formed for each partition is different from the type of columns forming the partition column set. 15. The method of claim 14,
Wherein the processor analyzes the condition of the input query and, if at least one partition column set is formed for the selected partition, selects one of the at least one partition column set based on the analysis result To the query processing unit. 13. The method of claim 12,
A data block corresponding to the horizontal partition and a data block corresponding to the partition column set are distributedly stored in various nodes of the distributed file system,
Wherein the query processing device reads a data block corresponding to a partition column set of a horizontal partition corresponding to the input query, and processes the query.

Images