KR101544560B1 - An online analytical processing system for big data by caching the results and generating 2-level queries by SQL parsing - Google Patents

Abstract

The present invention relates to an online analysis processing method using a two-step query and result caching by SQL parsing of an analysis processing server for processing a request query for a database requested by a client, the method comprising: (a) Extracting a column name included in the request query; (b) a query (hereinafter referred to as a base query) referring to the same table as the table referred to by the request query, with the extracted column name as a reference item, and a query result data Generating an extended query that retrieves a query; (c) retrieving result data of the base query from the server cache of the server; (d) requesting data from the database with the basic query if there is no result data of the basic query in the server cache, and storing result data of the received basic query in the server cache; And (e) applying the extended query to result data of the base query to obtain result data of the extended query, and transmitting the obtained result data to the client.
By caching the basic query data among the requested queries, the query execution speed is greatly improved in the business intelligence analysis environment, and the analysis processing result can be provided to the user in real time by the above-described method.

Description

[0001] The present invention relates to an online analytical processing system for processing large amounts of data, and more particularly, to an online analytical processing system using two-level queries and result caching using SQL parsing.

The present invention separates the steps of a base query and an extender query through SQL parsing when a client requests a query to fetch data based on the basic query from a database storing large amounts of data or big data Memory-based server cache, and executing an extended query from the server cache data to extract necessary data. The present invention relates to a method for online analysis processing using a two-step query by SQL parsing and result caching.

Generally, business intelligence (BI) analyzes a company's vast amount of data in a formal or informal manner, such as statistical analysis, or processes the analyzed information into an easy-to-understand and easy-to-understand report, To a set of tools that allow you to proceed to

There are a lot of data that companies accumulate as they do business. These data convey the vividness of the business scene, and if it is properly analyzed, it can extract the information needed for the business. However, it is not so easy to draw meaningful analysis results from the large amount of data accumulated in the field.

Many tools have been developed individually for this analysis. Examples include data extraction and transformation (ETT) tools, online analytical processing (OLAP) tools for multidimensional data analysis, reporting tools for generating reports, and data mining tools for finding hidden associations between data. It is a type of business intelligence (BI) that forms a suite of tools into a suite of software.

However, conventional business intelligence (BI) has gathered a variety of analytical tools, but it was difficult to use universally, except for specific analysis, because users had to be skilled in dealing with various analytical tools. In order to improve these points, there have been proposed reporting techniques for searching and analyzing a database in a web environment [Patent Document 1]. In addition, an analysis report writing system based on an Excel interface is also proposed online [Patent Document 2].

Recently, as the importance of analysis of data such as SNS and social media continues to grow, there are many companies that need to analyze and collect big data for customer management and product promotion of products of companies ought. The term big data refers to data sets that have common software tools and computer systems to collect, manage, store, search, share, analyze, and visualize data belonging to a certain amount of time (data set). The size of the big data may have a range of terabytes, exabytes, or zeta bytes. Big data can exist in a variety of fields, including web logs, RFID, sensor networks, social networks, social data, Internet text and documents, Internet search indexing, POS (point of sale) data, , Photo recording, video recording, and electronic commerce.

On-line analytical processing (OLAP) systems are introduced and used to analyze these big data. One of the biggest problems is the delay in data processing speed. That is, since the time for processing a large amount of data becomes long, the user waits for an extremely long time in an on-line experience.

As shown in FIG. 1, the online analysis processing system according to the related art comprises a client installed in a user terminal, a BI server processing the data requirements of the client, and a database storing big data.

The user creates a report form (or a template) through a client on a web browser and requests data to be included in the report form to the BI server (step 1). That is, the server transmits necessary information such as a database code (DB code) and a query (SQL query) extracted from the report created by the client to the BI server. Next, the BI server connects the database and requests the necessary data (step 2). The database retrieves and extracts the set of requested data (or query result, cube data set), and transmits the extracted result data to the BI server (step 3). The BI server compresses the field information and data received from the database and transmits them to the client (step 4).

The conventional online analysis processing system as described above frequently takes 10 minutes or longer from the moment when the source data exceeds 10 million to receive the query result. For example, for a given site, 400 million results view only takes more than five minutes. It takes 15-30 seconds to format the data in the database.

The reason for this slow data processing is that the processing speed of the request to the database drops sharply. The database uses a data server that is normally commercialized and handles standard DB (Database) functions. Such a commercialized database, if the source table is huge, for example, if there are more than 100 million data, the query processing speed drops drastically in order to process a large amount of data.

In particular, even when using the view function, the query processing speed is very slow. In general, a view is a logical representation of a subset of data from one or more tables. It does not have actual data, but has a single SQL result. Views can be used to restrict access or to make complex queries easier, but they execute SQL internally on every request. Therefore, if the source's view is large or complex, the linked view may also be slowed down. In addition, even if the query itself is complicated by using functions such as a join function in the query, the processing speed is very slow.

A commercially available database has a solution for processing queries faster by tuning the query itself in order to solve the above problems. However, since this tuning is also prepared for general situations, the tuning of the own system only has a certain limit, so the query speed itself can not be dramatically improved.

For example, a commercialized database will only deal with normal, standardized cases, so it will perform the same task repeatedly for the same or similar query requests.

Due to the above problems, the online analysis processing (OLAP) system according to the related art generates a very long waiting time on-line, and causes a great inconvenience to the user.

[Patent Document 1] Korean Patent No. 10-0497811 (published on Jun. 18, 2005) [Patent Document 2] Korean Patent No. 10-0969656 (published on July 14, 2010)

SUMMARY OF THE INVENTION The object of the present invention is to solve the above problems, and it is an object of the present invention to provide a method and system for separating large data or big data from basic data And an on-line analysis processing method using a two-step query by SQL parsing and result caching which extracts necessary data by storing the fetched data in an in-memory based server cache and executing an extended query from the data of the server cache .

In order to achieve the above object, the present invention relates to an online analysis processing method using a two-step query and result caching by SQL parsing of an analysis processing server for processing a request query for a database requested by a client, Parsing the request query to extract a column name included in the request query; (b) a query (hereinafter referred to as a base query) referring to the same table as the table referred to by the request query, with the extracted column name as a reference item, and a query result data Generating an extended query that retrieves a query; (c) retrieving result data of the base query from the server cache of the server; (d) requesting data from the database with the basic query if there is no result data of the basic query in the server cache, and storing result data of the received basic query in the server cache; And (e) applying the extended query to the result data of the basic query to obtain result data of the extended query, and transmitting the obtained result data to the client.

The present invention also relates to an online analysis processing method using a two-step query and result caching by SQL parsing of an analysis processing server for processing a request query for a database requested by a client, the method comprising: (a) Extracting a column name included in the request query; (b) a query (hereinafter referred to as a base query) referring to the same table as the table referred to by the request query, with the extracted column name as a reference item, and a query result data Generating an extended query that retrieves a query; (c) retrieving result data of the base query from the server cache of the server; (d) requesting data from the database with the request query if there is no result data of the base query in the server cache, and transmitting result data of the received request query to the client; And (e) requesting data from the database using the basic query, and storing result data of the received basic query in the server cache.

The present invention also provides an on-line analysis processing method using a two-step query and result caching by SQL parsing, wherein the server saves the result data of the extended query as a cache file in the server cache, ) If the cache file of the extended query is retrieved from the server cache after the step (b), transmitting the retrieved cache file to the client.

In addition, the present invention provides an on-line analysis processing method using a two-step query and result caching by SQL parsing, the method comprising: generating a unique key capable of identifying the column name in the step (a) , An alias is defined with respect to the column name in the reference item section of the basic query, and the extended query refers to the column using the alias.

According to another aspect of the present invention, there is provided an online analysis processing method using a two-step query and result caching by SQL parsing, wherein the unique key is obtained by hashing a database name, a reference table name, do.

According to another aspect of the present invention, there is provided an online analysis processing method using a two-step query and result caching by SQL parsing, wherein the basic query is composed of a reference item clause, a table reference clause and a condition clause, The table reference clause and the condition clause of the basic query have the same structure as the table reference clause and condition clause of the request query.

According to another aspect of the present invention, there is provided an online analysis processing method using a two-step query and result caching by SQL parsing, wherein, in the step (b), the extended query extracts result data of the basic query or the basic query And a clause other than the table reference clause is generated so as to have the same structure as the clause of the request query.

According to another aspect of the present invention, there is provided an online analysis processing method using a two-step query and result caching by SQL parsing, wherein, in the step (b), when an alias for a table is defined in the request query, And generates the extended query by deleting the alias and replacing the alias of the table with the name of the table.

In addition, the present invention provides an on-line analysis processing method using a two-step query and result caching by SQL parsing, the server cache comprising an in-memory storage and a cache disk, .

As described above, according to the online analysis processing method using the two-step query and result caching by SQL parsing according to the present invention, by caching the basic query data among the requested queries, the query execution speed is improved in the business intelligence analysis environment It is possible to provide an analysis processing result in real time to the user.

1 is a block diagram of an online analysis processing system according to the prior art;
FIG. 2 is a block diagram of a configuration of an overall system for performing an online analysis processing method using a two-step query and result caching by SQL parsing according to the present invention.
3 is a flowchart illustrating an online analysis processing method using a two-step query and result caching by SQL parsing according to a first embodiment of the present invention.
4 is an example of a request query according to the first embodiment of the present invention;
5 is an example of a basic query and an extended query according to the first embodiment of the present invention;
6 is a flowchart illustrating an online analysis processing method using a two-step query and result caching by SQL parsing according to a second embodiment of the present invention.
7 is a flowchart illustrating an online analysis processing method using a two-step query and result caching by SQL parsing according to a third embodiment of the present invention.
8 is a configuration diagram of a server cache according to a fourth embodiment of the present invention;
9 is a flow chart for explaining a first situation according to the present invention;
10 is a flowchart for explaining a second situation according to the present invention;
11 is a flowchart for explaining a third situation according to the present invention.
12 is a comparison table of the processing results according to the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, the present invention will be described in detail with reference to the drawings.

In the description of the present invention, the same parts are denoted by the same reference numerals, and repetitive description thereof will be omitted.

First, an overall system for implementing a resultant caching-based online analysis processing system and method according to the present invention will be described with reference to FIG.

As shown in FIG. 2, the overall system for implementing the present invention comprises a client 20, an analysis processing server 30, a BI server 50, and a database 60. In particular, the analysis processing server 30 has a server cache 40 for storing some data received from the database 60. [

The client 20 is a client program system installed in the user terminal 10 and has a user interface through a web browser. That is, the user performs a data analysis processing operation on-line via a screen interface such as a web browser or a web browser. At this time, the client 10 receives a command or the like of the user, executes the command, and displays the processing result on the screen or the web browser. On the other hand, the user terminal 10 is a computer terminal having a computing function such as a personal computer (PC), a PDA, and a smart phone.

Further, the client 20 requests the analysis processing server 30 to analyze and process data on-line, such as data request and data analysis, and obtains the result from the server 30 and displays the result on the web browser.

Next, the analysis processing server 30 is a server that processes online analysis processing (OLAP), receives a request for data analysis from the client 20, processes the analysis request, and sends the result to the client 20 Lt; / RTI >

In particular, the analysis processing server 30 fetches the data stored in the database 50 using the query requesting the data. A query is a data manipulation language that describes a question or inquiry that occurs when retrieving or updating data stored in a database. In a database, a query acts as a kind of command. It is expressed in the form of a structured query language (SQL) of a relational database, but in some cases, it may be expressed in a format other than SQL.

The analysis processing server 30 also includes a server cache 40 to temporarily store all or a part of the data fetched from the database 60. [ The server cache 40 may be implemented as a cache memory implemented on a memory (RAM or the like) of the analysis processing server, or may be implemented as a hard disk, a solid state disk (SSD) disk, or the like, and configured as a cache disk. Alternatively, all the data can be stored on a disk, and some data, that is, necessary data, can be loaded into the cache memory and used.

Next, the BI server 50 serves as a database (DB) interface server for relaying the database 50. [ That is, the BI server 50 receives the query from the analysis processing server 30 and fetches the data of the database 60 using the query. Or the DBMS of the database 60 to fetch the corresponding data.

In addition, the BI server 50 may request a query or receive data in accordance with a method of interfacing with the database, even if the BI server 50 is composed of a plurality of heterogeneous databases 60. In addition, the BI server 50 performs additional tasks for transmitting and receiving data, such as encrypting data, compressing data, and compressing files.

Next, the database 60 is a normal database (DB) for storing data, and has a DBMS for managing data, and performs operations such as data storage, deletion, and retrieval through queries. In particular, the database 60 is a commercialized database, and performs a data query service using a general query function for processing data.

In particular, the database 60 is a database for storing big data. Also, preferably, the database 60 comprises a relational database (RDB).

Next, an online analysis processing method using a two-step query and result caching by SQL parsing according to a first embodiment of the present invention will be described in more detail with reference to FIG.

As shown in FIG. 3, the online analysis processing method using the two-step query and result caching by SQL parsing according to the first embodiment of the present invention includes (a) receiving and parsing a request query (S11); (b) generating a basic query and an extended query (S12); (c) searching the server cache for the result of the base query (S13); (d) if not retrieved, retrieving the base query from the database and storing it in the server cache (S14); (e) acquiring a result of the request query by applying an extended query to the result of the basic query (S15); And (f) transmitting the result of the request query (S16).

First, a step S11 of receiving and parsing a request query will be described. The analysis processing server 30 receives the request query from the client 20 and parses the request query (S11).

The client 20 installed in the user terminal 10 requests the analysis processing server 30 to query the necessary data. Preferably, the request query is written as an SQL query. Figure 4 shows an example of a request query.

A request query created with an SQL query consists of a reference item clause (SELECT clause), a table clause and a join clause (FROM clause), a condition clause (WHERE clause), a group clause (GROUP BY) and an order clause (ORDER BY). A select list is a clause that defines the fields / columns of a desired data table. A table reference is a clause that defines a table from which data is to be retrieved. A join clause defines a join between tables. And the where clause is a clause that defines the condition. And a group by or order by clause is a clause that defines an aggregate or display form. The data fields defined in the request query, the reference tables, the variables in the conditional statements, etc. all refer to the fields, tables, etc. of the source data in the database 60.

The parsing of the request SQL query parses the request SQL query and returns the result of the select list, table reference, join clause, where clause, group by clause, , Order by clause, and so on.

In particular, extract the column name from the reference clause of the SELECT clause (or reference clause clause). Also, all the column names referenced in the join clause, condition clause, group clause, order clause, and the like are extracted.

If the request query is an SQL query, the SQL statement for parsing is:

query_block

: (subquery_factoring_clause)?

SELECT (((DISTINCT | UNIQUE) | ALL)?) Select_list

FROM (table_reference | join_clause) (COMMA (table_reference | join_clause)) *

where_clause?

hierarchical_query_clause?

group_by_clause?

order_by_clause?

| query_block ((UNION ALL?) | (INTERSECT | S_MINUS)) query_block

| LPAREN query_block RPAREN

In particular, when the reference item is a calculation formula, the column name included in the calculation formula is extracted. It also extracts the column names used in conditions or expressions referenced in other clauses, such as condition clauses.

Also, an identifier or unique key that can be distinguished from the other column names is generated for the extracted column name. The unique key is an identifier (or unique key) for the absolute name of the column. An absolute name is a name consisting of the name of the reference database, the name of the reference table, and the name of the column. Thus, the absolute name of a column can be expressed as:

Absolute name of column = <name of database>. <Name of table>. <Column name>

Or, if you do not really know the database, you might say:

Absolute name of column = <name of table>. <Column name>

In contrast to the absolute name, the column name is also called the relative name of the column.

The unique key of a column name is obtained by hashing using the absolute name of the column name. The formula for generating a unique key is:

[Equation 1]

Unique key = hash ((domain name) + database name + table name + column name + function name)

Therefore, the unique key of the column name performs the function of an identifier to identify the columns. That is, you can identify the column with a unique key.

The unique key of a column name is used to identify each column name through aliasing (alias) when generating a base query or a request query. All the column names of the basic query are written as aliases using the unique key of the column name so that all the column names in the table of the result data by the basic query are generated with the unique key. That is, the reason for aliasing a column name to an identifier (or unique key) is to identify the column in an automatically generated query.

For example, assume that query # 1 and query # 2 are as follows.

[Query # 1]

select t1. customer

from matirx_demo t1

[Query # 2]

select m.customer

from matrix_demo m

In this case, in the first and second queries, customer is the same column in the same table. However, since there is no alias key, we can determine that t1.customer and m.customer are different.

It is also assumed that queries # 1 and # 2 are as follows.

[Query # 1]

select t.id

from matrix_demo1 t

[Query # 2]

select t.id

from matrix_demo2 t

In this case, it looks the same because there is no unique key of an alias, but it is actually another column.

At this time, aliasing by applying a unique key generates a query as follows.

[Query # 1]

select t1. customer C9A59FD7B

from matirx_demo t1

[Query # 2]

Query # 2

select m.customer C9A59FD7B

from matrix_demo m

Therefore, if you look at the unique key "C9A59FD7B", you can see that it is the same database name, same table name, same column name, and same function name.

Also, preferably, the alias of the table name is removed from the table section, and all of the aliases of the table name are changed to the original table name. For example, in the previous example, if the MATRIX_DEMO is aliased to T, rename it to the original table name (MATRIX_DEMO).

Preferably, when a plurality of databases are used, an absolute name of a table for each table name in the table true adjustment is obtained. The absolute name of the table consists of the database name and the table name, and is expressed as <database name>. <Table name>.

On the other hand, table clauses and join clauses are included in the "FROM" clause in SQL statements. That is, the FROM clause is a clause for referencing a table, which consists of a table and a join. Therefore, the clauses that include table clauses and join clauses are referred to as "table reference clauses" below.

Next, the analysis processing server 30 generates a basic query and an extended query using the parsed request query (S12).

The basic query is a query that refers to the data in the database 60, and the extended query is a query that refers to the data extracted by the basic query (or the result data of the basic query). An example of a basic query and an extended query is shown in FIG.

5, the name of the data field such as " CUSTOMER ", "PRODUCT "," C ", or "C2 " described in the SELECT statement, the name of the reference table such as" MATRIX_DEMO " (Or variables in the data field) such as "YYYY" directly refer to the source data of the database 60 directly.

First, the basic query is generated as follows.

Create a reference to the column name (or column list) extracted from SQL parsing. At this time, preferably, when the reference item of the request query is an equation, a reference item of the base query is generated with the column name included in the calculation of the request query. Also, column names referenced in other clauses, such as condition clauses, are also created as reference items in the base query.

In the example of FIG. 4, the third reference item of the request query is a formula of "SUM (T.H_VAL)". Also, in the where clause, "T.YYYY = '2013'" contains the column name "T.YYYY" in the condition or its condition. Therefore, the column name "T.H_VAL" in the calculation formula "SUM (T.H_VAL)" and the column name "T.YYYY" in the condition "T.YYYY = '2013" It is created as a reference item in the item section.

Also, aliasing the unique key of the column name to the reference items is performed. That is, define the unique key as an alias.

In addition, the table clause (or table true control) of the basic query, the join clause, and the condition clause are configured in the same manner as the table clause, join clause, and condition clause of the request query.

Preferably, however, if the table name is aliased in the table section of the request query, the alias alias is deleted. In the reference clause, join clause, and condition clause, change all the table names with aliases to the table names (or absolute names).

For example, a request query might look like the following:

[Request Query 1]

select t.customer, sum (t.h_val)

from matrix_demo t

where t.yyyy = '2013'

group by t.cusomer

At this time, the basic query generated from the request query 1 is as follows.

[Basic Query 1]

SELECT MATRIX_DEMO.CUSTOMER C9A59FD7B, MATRIX_DEMO.YYYY CEB41FFF7, MATRIX_DEMO.H_VAL CB165E5C5

FROM MATRIX.MATRIX_DEMO

WHERE MATRIX_DEMO.YYYY = '2013'

That is, in the request query, we declare alias of table matrix_demo as "t", and all of the base queries are changed to matrix_demo, which is the name of the table.

Next, an extended query is created.

The extended query has the same structure as the request query except for the table clause, and refers to the result data table of the base query or basic query (the result table obtained by querying the underlying query from the origin database) instead of the table referenced in the table section.

Also, all the column names are converted into the unique keys of the columns. That is, since the table referenced by the extended query is the result table of the basic query, all the columns of the referencing table should be referred to by the inherent key declared in the basic query.

The extended query by [request query 1] and [basic query 1] is as follows.

[Extended Query 1]

SELECT MHC.C9A59FD7B, SUM (MHC.CB165E5C5) AS "CB165E5C5"

FROM ({@ ORIGINAL_SQL @}) MHC

WHERE MHC.CEB41FFF7 = '2013'

GROUP BY

MHC.C9A59FD7B

Here, "{@ ORIGINAL_SQL @}" indicates that the base query or the result table of the base query is referred to.

Therefore, when the data extracted by the basic query is acquired, the extended query is a query that is requested by referring to the result data of the acquired basic query. The result obtained by the extended query is identical to the result obtained by the original request query. Also, the set of result data obtained by the extended query is always smaller than the set of result data obtained by the basic query. That is, the data set of the extended query is a subset of the data set of the underlying query.

In addition, if there is no result data of the basic query, in [Extended Query 1], it is possible to obtain the result of the request query by replacing the base query with {@ ORIGINAL_SQL @} and querying the source database.

Next, it is searched whether the result data of the basic query is stored in the server cache 40 (S13).

Data (or result data of the base query) fetched from the database 60 by the base query is stored and stored in the server cache 40. The analysis processing server 30 searches the server cache 40 for the basic query obtained previously. That is, it is checked whether there is a basic query obtained before the stored basic query.

When a comparison process for searching is described, it is judged whether the reference item clause, the table clause / join clause, and the condition clause are the same. If the referencing tables (or joins between tables) are the same, compare the reference clause clause (SELECT clause) and the condition clause (where clause) equally. In particular, only the unique keys of a column name need to be compared in the REFERENCES section. That is, the aliases are the same but are compared.

For example, the SQL used in the applicant's matrix is SQL that is automatically generated using meta. If you select meta-item 1 and meta-item 2, you can automatically generate the table code, column code, and join condition of the corresponding meta-item. You can see that the auto-generated SQL in the same meta is the same with field alias comparisons. That is, if the conditional comparison is further performed, it is possible to check whether or not the basic query is reused. However, if you only compare queries without a meta, you should also compare the join relationships between the table and them.

Next, if the result data of the basic query is not stored in the server cache, the data is requested to the database 60 with the basic query, and when the result data of the basic query is received, the data is stored in the server cache 40 S14).

As described above, since the basic query is a query directly referring to the data stored in the database 60, the basic query is used to make a query request to the database 60 using the basic query. The query request is requested to the database 60 via the BI server 50 and the data extracted by the basic query in the database 60 is returned to the analysis processing server 30 via the BI server 50. [ The analysis processing server 30 stores the received result data of the basic query in the server cache 50.

On the other hand, the result data of the basic query is stored in the same form or the same structure as the data structure of the database 60. That is, if the data of the database 60 is stored in the form of a table, the result data of the basic query is also stored in the form of a table. The types and sizes of the fields of the result data stored in the server cache 40 are the same as the types and sizes of fields configured in the database 60. This is to allow the query to be executed even if the extended query refers to the result data of the basic query instead of the data stored in the database 60. [

At this time, the column names in the result table are converted into the unique keys of the columns.

Next, the analysis processing server 30 applies the extended query to the result of the basic query to obtain the result of the request query (S15).

The extended query has the same structure as the request query and is a query referring to the base query instead of referring to the database (60). Therefore, names (hereinafter referred to as database reference names) referencing the database 60 in the extended query are generated by changing them to names (hereinafter referred to as basic query reference names) referencing the result data of the basic query. As described above, the names of the tables referenced in the extended query are all changed to refer to the table (or result table) generated by the basic query, and the names (or column names) of the data fields referenced in the extended query are all based And the name of the data field generated by the query (or the unique key of the column name).

In step S13, the result data of the basic query may or may not be stored in the server cache 40. [ However, if it is not stored, in step S14, a query is sent to the database 60 as a basic query, and the data is received and stored in the server cache 40. Therefore, in this step S15, the result data of the basic query is always stored in the server cache 40. [

In addition, the extended query is a query referring to the data of the basic query. Therefore, an extended query can be applied to the result data of the underlying query. By applying the extended query with reference to the result data of the basic query, the result data of the originally requested request query can be obtained.

Finally, the resultant data obtained by applying the extended query is transmitted to the client 20 as a result of the request query (S16).

Next, an online analysis processing method using a two-step query by SQL parsing and result caching according to a second embodiment of the present invention will be described in more detail with reference to FIG.

As shown in FIG. 6, an online analysis processing method using a two-step query and result caching by SQL parsing according to a second embodiment of the present invention includes (a) receiving and parsing a request query (S21); (b) generating a basic query and an extended query (S22); (c) retrieving the result of the basic query in the server cache (S23); (d) if not retrieved, retrieving the request query from the database and transmitting (S24); (h) fetching the underlying query from the database and storing it in the server cache (S28); (e) if found, executing an extended query (S25); (f) applying an extended query to the result of the basic query to obtain a result of the request query (S26); And (g) transmitting the result of the request query (S27).

In comparison with the first embodiment described above, when the basic query is retrieved in the server cache, if the basic query can not be retrieved from the server cache, the request is sent to the database 60 as a request query and the result is sent to the client 20 immediately There is a difference at point S24. After transmitting the result data of the request query, the base query is requested again to the database 60 to store the result data of the base query in the server cache 40 (S28). Hereinafter, the parts omitted from the description will be referred to the description of the first embodiment described above.

First, the analysis processing server 30 receives and parses the request query from the client 20 (S21). This is the same as the first embodiment. Next, the analysis processing server 30 generates a basic query and an extended query using the parsing result (S22).

It is checked whether the result data of the basic query is stored in the server cache 40 (S23). Data (or result data of the base query) fetched from the database 60 by the base query is stored and stored in the server cache 40. The analysis processing server 30 searches the base query obtained previously in comparison with the base queries storing the result data in the server cache 40. [

Next, if the result data of the basic query is not stored in the server cache, the data is requested to the database 60 by the request query, and when the result data of the request query is received, it is transmitted to the client 20 S24). At this time, desired result data (or result table) can be acquired even if the extended query is directly requested to the database 60 after replacing the table section with the basic query in the extended query.

After transmitting the result data of the request query to the client 20, the analysis processing server 30 requests data from the database 60 with the basic query, and when receiving the result data of the basic query, (S28). In particular, the analysis processing server 30 requests the data of the basic query at a time when there is not much request from the database 60 and there is a margin for traffic by the scheduler, and stores the result in the server cache 40.

Next, the case where the result data of the basic query is stored in the server cache will be described. The analysis processing server 30 acquires the result of the request query by applying an extended query to the result data of the base query (S25). And transmits the obtained result data to the client 20 (S26).

Next, an online analysis processing method using a two-step query and result caching by SQL parsing according to a third embodiment of the present invention will be described in more detail with reference to FIG.

As shown in FIG. 3, an online analysis processing method using a two-step query and result caching by SQL parsing according to a third embodiment of the present invention includes (a) receiving and parsing a request query (S31); (b) a step S31 of generating a basic query and an extended query; (c) searching the cache file by combining the basic query and the extended query (S32); (d) if found, transmitting the cache file to the client (S33); (e) if not retrieved, performing (34) the first or second embodiment; And (f) storing the result data of the request query as a cache file (S35).

The third embodiment of the present invention is an embodiment that complements the first or second embodiment described above. That is, if the result data of the request query is stored in the cache file in a binary form and is requested again with the same query, the cache file is directly transmitted to the client 20.

The cache file is the result data of the request query is saved as a file. When the analysis processing server 30 creates the result data requested by the client 20 and finally transmits the data, the analysis processing server 30 transmits the result data in the form of a file. The cache file is the same file as when transmitting. Therefore, if a request is made with the same request query as that of the cache file, the cache file can be transferred immediately.

Preferably, the cache file is stored in the server cache 40 of the analysis processing server 40.

Specifically, the analysis processing server 30 receives and parses the request query from the client 20 (S30). This is the same as the first or second embodiment. The analysis processing server 30 combines the basic query and the extended query to compare the query of the stored cache file to search for the same query (S32).

 If the same query is in the cache file, the retrieved cache file is immediately transmitted to the client 20 (S33).

If there is no identical query, the third search step (S13, S23) of the first or second embodiment described above is performed (S34). That is, it searches the server cache for the result data of the underlying query. If the server cache has the result data of the underlying query, it creates an extended query against the underlying query to obtain the resulting data. And transmits the obtained result data to the client. If there is no result data of the underlying query in the server cache, the result data is retrieved from the database 60 with a base query or a request query. If the fetched result data is basic query data, the result data of the request query is generated through an extended query. And finally transmits the result data of the request query to the client 20.

Upon completion of the first or second embodiment, the result data (or result data transmitted to the client) of the generated base query and the extended query is stored in the server cache 40 as a cache file (S36).

Next, an online analysis processing method using a two-step query and result caching by SQL parsing according to a fourth embodiment of the present invention will be described in detail with reference to FIG.

The fourth embodiment of the present invention has the same configuration as the first to third embodiments described above. However, the configuration of the server cache 40 is further subdivided.

As shown in FIG. 8, the fourth embodiment of the present invention comprises a cache memory 41 and a cache disk 42 in the server cache 40.

The cache memory 41 is constituted by a RAM (Random Access Memory) of the analysis processing server 30. In particular, the cache memory 41 is comprised of in-memory storage. The cache disk 42 is constituted by a hard disk of the analysis processing server 30 or a solid state disk (SSD).

In the first to third embodiments of the present invention, the result data of the basic query stored in the server cache 40 are all stored in the cache memory 41. [ However, when the result data of the basic query is larger than the storage capacity of the cache memory 41, the result data exceeding the capacity of the cache memory is stored in the cache disk 42. [

At this time, the result data of the basic query transferred to the cache disk 42 is selected by a predetermined policy. As an example of a sorting policy, based on the frequency of access to result data, recent access time, and so on, the resultant data with the lowest access frequency or the most recent access time are selected.

In addition, the cache file of the third embodiment is stored in the cache disk 42.

The effects of the present invention will be described in more detail with reference to Figs. 9 to 12. Fig.

The present invention relates to a platform for processing big data based on business intelligence (BI). In particular, when the big data is requested, the response time is processed within a short time of 10 seconds or less, thereby realizing close processing in real time. The present invention uses a cache file and a cache memory table for near-real-time processing. To do this, we parse the request query and separate it into basic query and extended query.

It also defines the structure of file type loading / storing / filtering according to memory limitations. For example, if it takes up to 100M to load 5G on a memory table, then a 32G server environment can not have a billion in memory. Therefore, there is a need for a structure for quickly storing some data in a file form and loading it back into memory if necessary. We process only the desired data with the condition of the file itself (filtering and query parsing to extract the condition from the corresponding column).

The reason for separating the request query into the base query and the extended query is not to use a relatively relational database (RDB), but rather to use a server cache (in-memory) provided in the analysis processing server 30, Database). This dramatically improves speed. In-memory database is a database management system that is installed and operated in main memory of data storage. The processing speed is faster than that installed on the disk.

Specifically, when the first to fourth embodiments of the present invention are applied, the processing speed in each situation will be described.

First, the first situation is that both the basic query and the extended query are inconsistent. That is, the first situation corresponds to the case where the query is executed for the first time, and the overall processing speed is the same as that of the system according to the prior art.

9, a DB code and SQL information extracted from a matrix report are separated into a base query and an extended query and are requested to an analysis processing server (SOLAP server) (step 1) . Since there is no matching base query (BaseSQL) and an extended query (ExtendSQL), the query is requested to the BI server (Matrix Server) as the original request query (step ②). The BI server connects the target DB and requests data (step ③). The cube data is sent from the target DB (step ④). Then, field information and data are compressed and sent (step ⑤). And saves the sent file as a cache file (step 6). The cache file is sent to the browser (or client) (step 7). Finally, the scheduler executes the base query (BaseSQL) in the source DB and stores it in the cache memory 40 (Background execution) (step 8).

Next, the second situation is that the base query is matched and the extended query is inconsistent. This corresponds to a case where a cache memory table corresponding to a base query is created. The speed is about 10 seconds, which is improved by 10 to 50 times as compared with the first situation (or the prior art).

As shown in FIG. 10, first, a DB code and SQL information extracted from a matrix report are separated into a base query and an extended query (Step 1). If the base query is matched and there is no extended query, the target table of the extended query is changed to the table name stored in the server cache (step 2). Then, the cube data is sent from the server cache (step ③). Save the sent file as a cache file (step ④). Finally, the cache file is sent to the browser (step 5).

Next, the third situation is that both the base query and the extended query match. This corresponds to a case in which both the base query (BaseSQL) and the extended query (ExtendSQL) coincide with each other, and the speed is about 3 seconds, which is improved by more than 100 times as compared with the first situation or the prior art.

As shown in FIG. 11, the DB code and SQL information extracted from the Matrix report are separated into a base query and an extended query (Step 1). If both the base query (BaseSQL) and the extended query (ExtendSQL) match, a hash key value exists (step ②). Then, the cache file is sent to the browser (step 3).

A table comparing the processing speeds of the first to third situations is shown in Fig. When the same query is executed, the state is automatically passed. That is, in the first situation, in the second situation, in the second situation, to the third situation. Also, as the number of concurrent users increases, the frequency of cache file usage increases dramatically (more than 90% is expected). The majority of users will experience a reduction in query time from five to three seconds. Also, in the first execution, the data for the underlying query can be executed by the scheduler. That is, the data can be fetched at a leisure time through the scheduler, and it does not affect the perceived speed.

The invention made by the present inventors has been described concretely with reference to the embodiments. However, it is needless to say that the present invention is not limited to the embodiments, and that various changes can be made without departing from the gist of the present invention.

10: user terminal 20: client
30: Analysis processing server 40: Server cache
41: cache memory 42: cache disk
50: BI server 60: database

Claims (9)

A method for on-line analysis processing using a two-step query and result caching by SQL parsing of an analysis processing server that processes a request query for a database requested by a client,
(a) parsing the request query and extracting a column name included in the request query;
(b) a query (hereinafter referred to as a base query) referring to the same table as the table referred to by the request query, with the extracted column name as a reference item, and a query result data Generating an extended query that retrieves a query;
(c) retrieving result data of the base query from the server cache of the server;
(d) requesting data from the database with the basic query if there is no result data of the basic query in the server cache, and storing result data of the received basic query in the server cache; And
(e) applying the extended query to result data of the base query to obtain result data of the extended query, and transmitting the obtained result data to the client. How to process online analytics using query and result caching.
A method for on-line analysis processing using a two-step query and result caching by SQL parsing of an analysis processing server that processes a request query for a database requested by a client,
(a) parsing the request query and extracting a column name included in the request query;
(b) a query (hereinafter referred to as a base query) referring to the same table as the table referred to by the request query, with the extracted column name as a reference item, and a query result data Generating an extended query that retrieves a query;
(c) retrieving result data of the base query from the server cache of the server;
(d) requesting data from the database with the request query if there is no result data of the base query in the server cache, and transmitting result data of the received request query to the client; And
(e) requesting data from the database with the basic query, and storing result data of the received basic query in the server cache,
Wherein the server stores the resultant data of the extended query as a cache file in the server cache,
The method further comprises: (f) after the step (b), if the cache file of the extended query is retrieved from the server cache, transmitting the retrieved cache file to the client How to process online analytics using two-step query and result caching.
The method according to claim 1,
Wherein the server stores the resultant data of the extended query as a cache file in the server cache,
The method comprises:
(f) if the cache file of the extended query is searched from the server cache after the step (b), transmitting the retrieved cache file to the client. An on - line analytic processing method using result caching.
3. The method according to claim 1 or 2,
In the step (a), a unique key capable of identifying the column name is generated,
Wherein in the step (b), an alias is defined with respect to the column name in the reference item section of the basic query, and the extended query refers to the column using the alias. A method for on - line analysis processing using two - step query and result caching.
5. The method of claim 4,
Wherein the inherent key is obtained by hashing a database name of a corresponding column name, a name of a reference table, and a column name by using a two-step query by SQL parsing and result caching.
3. The method according to claim 1 or 2,
In the step (b), the basic query is composed of a reference item clause, a table reference clause, and a condition clause, and the table reference clause and the condition clause of the base query have the same structure as the table reference clause and the condition clause of the request query And a second step query and result caching by SQL parsing.
The method according to claim 6,
In the step (b), the extended query refers to the result data of the basic query or the basic query in the table reference section, and the clause other than the table reference clause is generated so as to have the same structure as the clause of the request query A method for on-line analysis processing using two-step query and result caching by SQL parsing.
8. The method of claim 7,
Wherein, in the step (b), when an alias for a table is defined in the request query, an alias of the table is deleted and an alias of the table is replaced with a name of the table to generate the extended query A method for on-line analysis processing using two-step query and result caching by SQL parsing.
3. The method according to claim 1 or 2,
Wherein the server cache comprises in-memory storage and a cache disk,
And storing result data of the basic query in the in-memory storage. 2. The online analysis processing method of claim 1, further comprising:

Images