Optimizing method for a data processing query for a table and optimizing program for a data processing query for a table

Abstract

An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

Description

OPTIMIZING METHOD FOR A DATA PROCESSING QUERY FOR A TABLE AND OPTIMIZING PROGRAM FOR A DATA PROCESSING QUERY FOR A TABLE

[0001] The present invention relates to an optimizing method and an optimizing program for a data processing query for a table.

[0002] In analysis of tabular data structured in rows and columns, extracting desired rows based on some given condition is known as Filter operation. It is one of the very frequent operations performed during data analysis. A filter operation consists of two parts as depicted in following diagram: ●mask-operation: creating True / False mask based on the given condition. ●take-operation: copying (taking) the rows whose associated mask is computed as True.

[0003] For example, Patent Literature 1 describes a technique to assist in reordering and optimizing a sequence of instructions to improve processing speed.

[0004] [PTL 1] JP H06-095862A

[0005] The disclosures of the above prior art document shall be incorporated by reference into this document. The following analysis has been made by the inventor.

[0006] For a table stored in columnar format, take-operation is a major bottleneck of filter-operations. The execution time of take-operation is usually directly proportional to the number of columns in the input table, i.e., with more columns, take-operation consumes more time. There are algorithms to optimize the take-operations using parallelism, etc., but in very frequent cases, it has been found that there are some unwanted take-operations (materialization) that can be avoided to speed up filter-operations. Especially when filter operations are performed one after another, the materialization of each step might cause a significant performance issue.

[0007] In view of the above problems, it is an object of the present invention to provide an optimizing method and an optimizing program that contribute to reduce the total execution time of performing a couple of filter operations one after another in a data processing query for a table.

[0008] According to a first aspect of the present disclosure, there is provided an optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

[0009] According to a second aspect of the present disclosure, there is provided an optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying a second filter into a reduced mask for the temporal table; and filtering the table by the reduced mask.

[0010] According to a third aspect of the present disclosure, there is provided an optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying each filter condition into a mask vector if the number of rows of the temporal table is more than a certain threshold; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

[0011] According to a fourth aspect of the present disclosure, there is provided an optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

[0012] According to a fifth aspect of the present disclosure, there is provided an optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying a second filter into a reduced mask for the temporal table; and filtering the table by the reduced mask.

[0013] According to a sixth aspect of the present disclosure, there is provided an optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying each filter condition into a mask vector if the number of rows of the temporal table is more than a certain threshold; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

[0014] Further, this program can be stored in a computer-readable storage medium. The storage medium may be non-transitory one such as a semiconductor memory, a hard disk, a magnetic recording medium, an optical recording medium, and the like. The present invention can also be realized as a computer program product.

[0015] According to each aspect of the present invention, there can be provided an optimizing method and an optimizing program that contribute to reduce the total execution time of performing a couple of filter operations one after another in a data processing query for a table.

[0016] Fig. 1 illustrates an example of a filter of filter.Fig. 2 illustrates a process in a first example embodiment of the present disclosure.Fig. 3 shows an example of a flow chart for IR optimization.Fig. 4 illustrates how expensive the cost of mask computation.Fig. 5 illustrates a method of a second example embodiment of the present disclosure.Fig. 6 shows a flowchart of a process of the second example embodiment of the present disclosure.Fig. 7 is a drawing illustrating an example of a hardware configuration of an apparatus to perform the method of the example embodiment(s) of the present disclosure.DESCRIPTION OF EXAMPLE EMBODIMENTS

[0017] <filter(s) of filter operation> It is found that sometime user finds it useful to write a series of filter-operations one-by-one as shown in below example: df = read_csv(“data.csv”) t1 = df.filter(df[“A”] > 2) t2 = t1.filter(t1[“B”] < 5) t3 = t2.filter(~t2[“C”].isnull())

[0018] Fig. 1 illustrates an example of a filter of filter. As shown in Fig. 1, in the process of a series of filter operations, materialization (take-operations) takes place with each filter operation. The cost of each filter increases with the increase in the number of columns.

[0019] To reduce the total execution time of performing a couple of filter operations one after another in a data processing query for table, a first example embodiment of the present disclosure performs the following process.

[0020] Fig. 2 illustrates a process in the first example embodiment of the present disclosure. As shown in Fig. 2, the first example embodiment of the present disclosure modifies a series of filter-operations into AND-filter.

[0021] The first example embodiment of the present disclosure firstly detects the series of filter-operations in a data processing query for a table. The first example embodiment of the present disclosure secondly modifies each filter condition into a mask vector. The first example embodiment of the present disclosure thirdly performs logical AND operation on the mask vectors to obtain an ANDed mask vector. The first example embodiment of the present disclosure finally filters the table by the AND-filter.

[0022] For example, the above process for the series of filter-operations will be modified into the following process: df = read_csv("data.csv") m1 = df["A"] > 2 m2 = df["B"] < 5 m1 = ~df["C"].isnull() t3 = df[(m1 & m2) & m3]

[0023] Fig. 3 shows an example of a flow chart for IR optimization. In a data processing query for a table, intermediate representations (IRs) are generated from a user program. <user program> df = read_csv("data.csv") t1 = df[df["A"] > 2] t2 = t1[t1["B"] < 5] t3 = t2[~t2["C"].isnull()] <intermediate representation (IR)> %1 = read_csv(“data.csv”) %2 = project(%1, “A”) %3 = binop_gt(%2, 2) %4 = filter(%1, %3) %5 = project(%4, “B”) %6 = binop_lt(%5, 5) %7 = filter(%4, %6) %8 = project(%7, “C”) %9 = isnull(%8) %10 = not(%9) %11 = filter(%7, %10)

[0024] As shown in Fig.3, the first example embodiment of the present disclosure obtains intermediate representations (IRs) (step S1). The first example embodiment of the present disclosure detects the series of filter-operations (filter-of-filter) in IRs and checks whether intermediate tables have single usage (step S2). When there is filter-of-filter in IRs and intermediate tables have single usage (step S2; yes), the first example embodiment of the present disclosure optimizes the IR with ANDing mask. In the other case (step S2; no), the first example embodiment of the present disclosure keeps IR as it is.

[0025] As described above, the first example embodiment of the present disclosure reduces the number of times for filter-operation. Therefore, the first example embodiment of the present disclosure reduces the total execution time of performing a couple of filter operations one after another in a data processing query for a table. To explain in more detail, in the process of the series of filter operations, materialization (take-operations) takes place with each filter operation. The first example embodiment of the present disclosure reduces the total execution time of performing a couple of filter operations by reducing the number of times for filter-operation.

[0026] <mask computation> Fig. 4 illustrates how expensive the cost of mask computation. Now, assume a case to filter a table T1 with a condition (A>2 & B<5). In conventional filtering, the table T1 is filtered with the condition (A>2) and a table T2 is obtained. Next, the table T2 is filtered with the condition (B<5) and a table T3 is obtained.

[0027] On the other hand, the first example embodiment of the present disclosure modifies the condition (A>2 & B<5) into mask T4 and mask T5, multiplies the mask T4 and the mask T5, and obtains AND-filter T6. Finally, the table T1 is filtered by the AND-filter T6 and the table T3 is obtained. In case in Fig. 4, the mask T4, the mask T5 and the AND-filter T6 need 1_000_000 rows because the table T1 has 1_000_000 rows. It needs expensive cost for computation. When the first materialized table is very small, filter-after-filter is a better choice to avoid expensive mask computation.

[0028] Fig. 5 illustrates a method of the second example embodiment of the present disclosure. The exemplary case shown in Fig. 5 filters a table df by the condition (A>2 & B<5). The method of the second example embodiment of the present disclosure filters the table df and obtains a temporal table tmp. The method of the second example embodiment of the present disclosure uses a mask m1 for filtering the table df with condition (A>2). Next, the method of the second example embodiment of the present disclosure modifies a second filter (B<5) into a reduced mask reduced_m2 for the temporal table tmp. Finally, the method of the second example embodiment of the present disclosure filters the table df by a reduced mask reduced_m2 with the condition (B<5).

[0029] The method of the second example embodiment of the present disclosure needs smaller cost than the conventional method, because the reduced mask reduced_m2 is smaller than the mask m1.

[0030] Fig. 6 shows a flowchart of the process of the second example embodiment of the present disclosure. The exemplary case shown in Fig. 6 filters the table by the condition (A>2 & B<5).

[0031] The first process of the second example embodiment of the present disclosure is to calculate first mask (m1=df[“A”]>2)(step S21). Next, the second example embodiment of the present disclosure checks if the number of rows of the temporal table is less than a certain threshold (step S22). If the temporal table is less than a certain threshold (step S22; yes), the process goes to step S26. The other case (step S22; no), the process goes to step S23.

[0032] In normal solution (step S22; no), the second example embodiment of the present disclosure calculates second mask (m2=df[“B”]<5) (step S23). The second example embodiment of the present disclosure calculates Anding Mask: m=m1&m2 (step S24). Finally, the second example embodiment of the present disclosure materializes table: ret=df[m] (step S25).

[0033] In optimized solution (step S22; yes), the second example embodiment of the present disclosure gets index associated with True values in mask m1: ind (step S26). Next, the second example embodiment of the present disclosure extracts rows from B column using “ind”: tmp=df[“B”].take(ind) (step S27). Next, the second example embodiment of the present disclosure calculates second mask: reduced_m2=tmp[“B”]<5 (step S28). Next, the second example embodiment of the present disclosure gets index associated with True values in mask, reduced_m2: ind2 (step S29). Finally, the second example embodiment of the present disclosure extracts rows from df using “ind2”: ret = df.take(ind2) (step S30).

[0034] As illustrated in Fig. 6, the second example embodiment of the present disclosure contains the first example embodiment of the present disclosure in normal solution (step S22; no). Therefore, the second example embodiment of the present disclosure is an improvement on the first example embodiment of the present disclosure.

[0035] <hardware configuration> Fig. 7 is a drawing illustrating an example of a hardware configuration of an apparatus to perform the method of the example embodiment(s) of the present disclosure. The apparatus may be configured as an information processing apparatus (computer) 10 having the hardware configuration shown in Fig. 7. It should be noted that the hardware configuration shown in Fig. 7 is merely an example of the hardware configuration realizing the function of the apparatus and is not intended to limit the hardware configuration of the apparatus. The apparatus may include hardware not shown in Fig. 7.

[0036] As shown in Fig 7, the computer 10 comprises a CPU (Central Processing Unit) 11, a primary storage device 12, an auxiliary storage device 13, and a NIC (Network Interface Card) 14, which is a communication interface. These elements are connected to each other by, for instance, an internal bus.

[0037] The CPU 11 executes an optimizing program. The primary storage device 12 is, for instance, a RAM (Random Access Memory) and temporarily stores the optimizing program executed by the computer 10 so that the CPU 11 can process it.

[0038] The auxiliary storage device 13 is, for instance, an HDD (Hard Disk Drive) and may store the optimizing program in the medium to long term. The optimizing program may be provided as a computer program stored in a non-transitory computer-readable storage medium. The auxiliary storage device 13 can be used to store the program stored in a non-transitory computer-readable storage medium over the medium to long term.

[0039] The NIC 14 provides an interface to an external terminal via a network. The NIC 14 is used to receive an image of the surface pattern of an end of the cable to be connected and images of the host name and a port of an apparatus or to transmit the work support information based on the result of individual product identification.

[0040] When the computer 10 as described above executes the optimizing program, the computer 10 performs the optimizing method in the example embodiment of the present disclosure.

[0041] <Supplementary Notes> The above example embodiments may partially or entirely be described, but not limited to, as the following notes. (Note 1) An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask. (Note 2) An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying a second filter into a reduced mask for the temporal table; and filtering the table by the reduced mask. (Note 3) The optimizing method for a data processing query for a table according to Note 2, wherein the optimizing method modifies the second filter into the reduced mask for the temporal table and filters the table by the reduced mask if the number of rows of the temporal table is less than a certain threshold. (Note 4) An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying each filter condition into a mask vector if the number of rows of the temporal table is more than a certain threshold; performing logical AND operation on the mask vectors on obtain a new mask vector; and filtering the table by the ANDed mask. (Note 5) The optimizing method for a data processing query for a table according to Note 3 or 4, wherein the certain threshold is 50% of the number of rows of the temporal table. (Note 6) An optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask. (Note 7) An optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying a second filter into a reduced mask for the temporal table; and filtering the table by the reduced mask. (Note 8) The optimizing program for a data processing query for a table according to Note 7, wherein the optimizing program causes the computer to perform modifying the second filter into the reduced mask for the temporal table and filtering the table by the reduced mask if the number of rows of the temporal table is less than a certain threshold. (Note 9) An optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying each filter condition into a mask vector if the number of rows of the temporal table is more than a certain threshold; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask. (Note 10) The optimizing program for a data processing query for a table according to Note 8 or 9, wherein the certain threshold is 50% of the number of rows of the temporal table.

[0042] While each example embodiment of the present invention has been described, it is to be noted that it is possible to modify or adjust the example embodiments or examples within the whole disclosure of the present invention (including the Claims) and based on the basic technical concept thereof. Further, it is possible to variously combine or select (or at least partially remove) a wide variety of the disclosed elements (including the individual elements of the individual claims, the individual elements of the individual example embodiments or examples, and the individual elements of the individual figures) within the scope of the whole disclosure of the present invention. That is, it is self-explanatory that the present invention includes any types of variations and modifications to be done by a skilled person according to the whole disclosure including the Claims and the technical concept of the present invention. Particularly, any numerical ranges disclosed herein should be interpreted that any intermediate values or subranges falling within the disclosed ranges are also concretely disclosed even without explicit recital thereof. Further, the disclosure of each Patent Literature cited above is incorporated herein in its entirety by reference thereto.

[0043] 10 information processing apparatus (computer) 11 CPU (Central Processing Unit) 12 primary storage device 13 auxiliary storage device 14 NIC (Network Interface Card)

Claims

1. An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

2. An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying a second filter into a reduced mask for the temporal table; and filtering the table by the reduced mask.

3. The optimizing method for a data processing query for a table according to claim 2, wherein the optimizing method modifies the second filter into the reduced mask for the temporal table and filters the table by the reduced mask if the number of rows of the temporal table is less than a certain threshold.

4. An optimizing method for a data processing query for a table, comprising: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying each filter condition into a mask vector if the number of rows of the temporal table is more than a certain threshold; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

5. The optimizing method for a data processing query for a table according to claim 3 or 4, wherein the certain threshold is 50% of the number of rows of the temporal table.

6. An optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; modifying each filter condition into a mask vector; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

7. An optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying a second filter into a reduced mask for the temporal table; and filtering the table by the reduced mask.

8. The optimizing program for a data processing query for a table according to claim 7, wherein the optimizing program causes the computer to perform modifying the second filter into the reduced mask for the temporal table and filtering the table by the reduced mask if the number of rows of the temporal table is less than a certain threshold.

9. An optimizing program for a data processing query for a table, causing a computer to perform: detecting a series of filter-operations in the data processing query for the table; filtering the table by a first filter and obtaining a temporal table; modifying each filter condition into a mask vector if the number of rows of the temporal table is more than a certain threshold; performing logical AND operation on the mask vectors to obtain a new mask vector; and filtering the table by the ANDed mask.

10. The optimizing program for a data processing query for a table according to claim 8 or 9, wherein the certain threshold is 50% of the number of rows of the temporal table.

Images