Reflection generation system, reflection generation method, and reflection generation program

Abstract

To allow an automatic generation of a reflection for an SQL query by using a reflection generation history.SOLUTION: A reflection generation system 1 includes: a storage device 3 for holding a history 24 for reflections; and an operation device 2 for selecting, from the history, ones of which reflection generation source is different from that of a new query for generating a reflection, of the queries about previous reflections of the same content as the content of a reflection shown by the new query, on the basis of the content, and generating a reflection on the basis of the query of the selected previous reflection.SELECTED DRAWING: Figure 1

Description

Technical Field

[0001] The present invention relates to a reflection generation system, a reflection generation method, and a reflection generation program.

Background Art

[0002] Currently, with the spread of COVID-19, COVID-19-related data in various file formats is being transmitted daily from the government, local governments, hospitals, health centers, etc. From the perspective of the purpose of using these data, it is not allowed to spend a long time on data analysis. Therefore, a situation where high-speed data analysis is required has emerged. In addition, in stores, factories, etc. that handle daily data of POS (Point Of Sales) and IoT (Internet Of Things), it is necessary to complete the analysis of the daily data by the next day. That is, high-speed data analysis is also required in these industries and business types.

[0003]

[0004] This reflection is a function of the open-source SQL (Structured Query Language) engine Dremio (https: / / www.dremio.com) for data lakes. Reflection speeds up queries by having a partial copy of the data (extracted columns, calculation results of aggregation operations, etc.).

[0005] ​Essentially, it is the same functionality as a materialized view (also called a materialized view table) in an RDBMS (Relational Database Management System). However, this materialized view is a function that can only be applied to an RDB (Relational Database).

[0006] On the other hand, reflection is more useful because it can provide functionality equivalent to materialized views to non-RDB data in the data lake (semi-structured data such as CSV files, JSON files, etc.).

[0007] As a conventional technique related to the above-mentioned reflection, for example, a method has been proposed that provides a technique that enables efficient use of a materialized view table when performing database processing based on multiple queries (see Patent Document 1).

[0008] This technology is a materialized view table creation method for creating a materialized view table, characterized by comprising the steps of: detecting derived tables commonly used in multiple queries and storing them in a storage device as a derived table list; creating definition information for creating the derived tables in the derived table list as materialized view tables, storing it in a storage device, and instructing the creation of a materialized view table using the definition information; and converting the description in the query into a description that uses the created materialized view table and storing it in a storage device. [Prior art documents] [Patent documents]

[0009] [Patent Document 1] Japanese Patent Application Laid-Open No. 2006-343798 [Non-patent literature]

[0010] [Non-Patent Document 1] https: / / docs.dremio.com / acceleration / creating-reflections.html

Summary of the Invention

Problems to be Solved by the Invention

[0011] However, in the prior art, reflections for SQL queries (queries using SQL on a dataset) need to be created manually. The technique shown in Non-Patent Document 1 shows a method for creating reflections manually. When adopting this technique, it is necessary to create at least one SQL query for generating reflections for one SQL query. The manual creation of reflections for each SQL query as described above requires a large amount of man-hours and itself becomes an obstacle to high-speed data analysis.

[0012] Also, according to the technique shown in Patent Document 1, it is possible to detect derived tables commonly used in a plurality of queries (queries) and create a materialized view table.

[0013] However, this technique creates a new materialized view table using history information when using the same derived table as the one used in the past, and it cannot be applied to derived tables or SQL queries that are similar but not the same. Also, it cannot be applied when the query is a non-SQL query (tag query, natural language query, etc.). Furthermore, it is a technique limited to RDBs.

[0014] Therefore, an object of the present invention is to provide a technique that can automatically generate reflections for SQL queries using reflection generation history.

Means for Solving the Problems

[0015] The reflection generation system of the present invention for solving the above problems includes a storage device that holds the history of reflections, and an arithmetic device that selects, from among queries regarding past reflections having the same content as that indicated by a new query for generating a reflection, a query whose reflection generation source is different from the new query based on the content of the reflection indicated by the new query for generating a reflection, and generates a reflection based on the selected query regarding the past reflection. Further, the reflection generation method of the present invention is characterized in that an information processing apparatus includes a storage device that holds the history of reflections, and based on the content of the reflection indicated by a new query for generating a reflection, selects, from among queries regarding past reflections having the same content as that, a query whose reflection generation source is different from the new query from the history, and generates a reflection based on the selected query regarding the past reflection.

[0016] Further, the reflection generation program of the present invention causes an information processing apparatus including a storage device that holds the history of reflections to execute a process of selecting, from among queries regarding past reflections having the same content as that indicated by a new query for generating a reflection, a query whose reflection generation source is different from the new query from the history, and generating a reflection based on the selected query regarding the past reflection.

Advantages of the Invention

[0017] According to the present invention, it becomes possible to automatically generate a reflection for an SQL query using the reflection generation history. This becomes possible.

Brief Description of the Drawings

[0018] [Figure 1] It is a diagram showing a configuration example of the reflection generation system of the present embodiment. [Figure 2] It is a diagram showing the overall process of the reflection generation method in the present embodiment. [Figure 3] This is a diagram showing an example of the flow of the reflection generation unit according to this embodiment. [Figure 4] This is a diagram showing an example of the flow of the query semantic analysis unit according to this embodiment. [Figure 5] This is a diagram showing an example of the flow of the reflection determination unit according to this embodiment. [Figure 6] This is a diagram showing an example of the configuration of the reflection history table according to this embodiment. [Figure 7] This is a diagram showing an example of the flow of the reflection generation unit according to this embodiment. [Figure 8] This is a diagram showing an example of the flow of the query semantic analysis unit according to this embodiment. [Figure 9] This is a diagram showing an example of the flow of the reflection generation unit according to this embodiment. [Figure 10] This is a diagram showing an example of the configuration of the reflection history table in this embodiment. [Figure 11] This is a diagram showing an example of the log file in this embodiment.

Embodiments for Carrying Out the Invention

[0019] <Configuration of the Reflection Generation System> Hereinafter, embodiments of the present invention will be described in detail with reference to the drawings. FIG. 1 is a diagram showing a configuration example of a reflection generation system 1 according to this embodiment. The reflection generation system 1 shown in FIG. 1 is a computer system that can automatically generate a reflection for an SQL query using a reflection generation history.

[0020] In this embodiment, the term "reflection" is used for explanation. However, as already described, it is essentially the same as the materialized view of the RDBMS. Therefore, the present invention is also applicable to the materialized view of the RDBMS.

[0021] As shown in FIG. 1, the reflection generation system 1 of this embodiment includes a CPU (Central Processing Unit) 2, a main memory device 3, an external memory device 4, and an input / output device 5.

[0022] Among these, the CPU 2 is an arithmetic device that reads and executes a program held in the main memory device 3 or the external memory device 4, performs overall control, and performs various determinations, calculations, and control processes.

[0023] The main memory device 3 is composed of a volatile memory element such as a RAM (Random Access Memory). This main memory device 3 includes a query 20, an SQL query 22, a reflection history table 24, a log file 26, and a reflection generation unit 42.

[0024] Among these, the query 20 is not an SQL statement, but for example, a query described orally, and is a query for generating a reflection. This query 20 is received and acquired from the user via the input / output device 5.

[0025] The SQL query 22 is a query described in the form of an SQL statement and is a query for generating a reflection. Note that this SQL query 22 may also include one obtained by converting the above-described query 20 by a query-to-SQL query conversion unit 42A1A described later.

[0026] The reflection history table 24 is a table that stores the history of reflections extracted and generated from a data lake to be the target of reflection generation.

[0027] The log file 26 is output for an appropriate person such as a system administrator, for example, and is a file that describes the content of reflection generation, the process of update, and the like.

[0028] The reflection generation unit 42 is a function for executing the reflection generation method of this embodiment.

[0029] In addition, the external storage device 4 is composed of an appropriate non-volatile memory element such as an SSD (Solid State Drive) or a hard disk drive. This external storage device 4 stores the table 50 and the reflection 52.

[0030] Among these, the table 50 is a table in the data lake that is the target of column extraction by the reflection generation unit 42.

[0031] Also, the reflection 52 is the reflection generated by the above-described reflection generation unit 42.

[0032] In addition, the input / output device 5 is assumed to be an input device that receives key input or voice input from the user, and an output device such as a display that displays the processed data. <Flow Example> Hereinafter, the actual procedure of the reflection generation method in this embodiment will be described with reference to the drawings. Various operations corresponding to the reflection generation method described below are realized by a program that the reflection generation system 1 reads into the main storage device 3 and executes. And this program is composed of codes for performing various operations described below.

[0033] FIG. 2 is a diagram showing a main flow example of the reflection generation method in this embodiment. In this case, the reflection generation unit 42 of the reflection generation system 1 analyzes a query (either query 20 or SQL query 22) input via the input / output device 5 and generates a reflection. The details of this process will be described sequentially.

[0034] Subsequently, FIG. 3 shows the processing flow of the reflection generation unit 42 in this embodiment. In this flow, after the processing of the query semantic analysis unit 42A, the processing of the reflection determination unit 42B is executed.

[0035] Among these, the query meaning analysis unit 42A analyzes the meaning of the query 20 and converts it into an SQL query 22. Further, the reflection determination unit 42B creates a reflection using the SQL query 22 generated by the above-described query meaning analysis unit 42A and the reflection history table 24, and updates the reflection history table 24.

[0036] Here, the details of the processing in the above-described query meaning analysis unit 42A will be described. The query meaning analysis unit 42A executes the processing by the query-to-SQL query conversion unit 42A1A.

[0037] In this case, the query-to-SQL query conversion unit 42A1A converts the above-described query 20 into an SQL query 22. As already described, the query 20 is a general query other than an SQL query, and refers to a query made to a data set in various expression methods.

[0038] For example, it is possible to assume a tag query using the data set itself or a "tag" attached to a column of the data set. An example of a tag is a case where, when there is a data set (temporarily assuming the data set name is c2020) as shown in Table 1 below, the "surname" is assigned to the "Name 1" column, the "given name" is assigned to the "Name 2" column, and the data set is tagged as "Customer Data 2020". That is, when there is a data set (temporarily assuming the data set name is c2020) as shown in Table 1 below, the "surname" is assigned to the "Name 1" column, the "given name" is assigned to the "Name 2" column, and the data set is tagged as "Customer Data 2020". [Table 1] TIFF0007716243000001.tif19170 That is, a tag is a name (tag name) that represents the "meaning" of a data set or a column.

[0039] In an SQL query, it is necessary to describe a query that only an expert familiar with the data set can understand, such as SELECT Name 1, Name 2 FROM c2020, while in a tag query, it is possible to describe it in an easy-to-understand form for people, such as SELECT surname, given name FROM Customer Data 2020. In an SQL query, it is necessary to describe a query that only an expert familiar with the data set can understand, such as SELECT Name 1, Name 2 FROM c2020, while in a tag query, it is possible to describe it in an easy-to-understand form for people, such as SELECT surname, given name FROM Customer Data 2020. In an easy-to-understand form for people, such as SELECT surname, given name FROM Customer Data 2020.

[0040] Generally, a tag query management system maintains a correspondence table between tag names and the names of corresponding datasets, columns, etc., so when the query-to-SQL query conversion unit 42A1A converts a tag query to an SQL query, it uses the correspondence table to replace the tag name with the dataset name or column name.

[0041] Furthermore, a natural language query using natural language such as written language or speech can also be considered as the above-mentioned query 20. For example, in the case of written language, a query such as "Extract the first and last names of customers from the customer data for 2020" is applicable.

[0042] When converting such a natural language query into an SQL query, the query-to-SQL query conversion unit 42A1A converts the natural language query into an SQL query using logic corresponding to a known method using machine learning (e.g., Victor Zhong, Caiming Xiong, and Richard Socher, "Seq2SQL: Generating Structured Queries FROM Natural Language using Reinforcement Learning," arXiv:1709.00103, 2017, https: / / arxiv.org / pdf / 1709.00103.pdf). A classifier is created to determine what each word corresponds to in SQL syntax, and SQL conversion is performed.

[0043] Also, in the case of a natural language voice query, one might imagine a voice asking, "Extract the first and last names of customers from the customer data for 2020." In this case, the query-to-SQL query conversion unit 42A1A can be expected to use Speech to Text technology, which is a technology for converting speech to text (e.g., https: / / eprints.lib.hokudai.ac.jp / dspace / bitstream / 2115 / 39653 / 1 / MP-SS1-3.pdf), to convert a spoken natural language query into a written natural language query, and then convert it into an SQL query.

[0044] In addition, it is also possible to assume an operation in which moving images such as sign language and body language (body language) are given to the image analysis engine, converted into what the body movements and postures mean, and further converted into an SQL query from the combination of the meanings. Of course, in that case, a correspondence table or machine learning model between what the body movements and postures mean and the combination of the meanings and the syntax and description of the SQL query is prepared in advance, and the query-to-SQL query conversion unit 42A1A can use it.

[0045] Subsequently, the flow of the above-described reflection determination unit 42B will be described with reference to FIG. 5. The reflection determination unit 42B takes the SQL query 22 as input and executes reflection generation by the reflection generation unit 42 using the reflection history table 24. It is.

[0046] As an example of the SQL query 22 exemplified here, for example, SELECT SUM(col 12) FROM table20202020, etc. are adopted.

[0047] First, in process 42B1, the reflection determination unit 42B extracts columns and tables used for reflection generation from the input SQL query 22. In the example of the above SQL query 22, this corresponds to extracting "col12" as the column and "table2020" as the table.

[0048] Subsequently, if the description of the aggregation operation is included in the above SQL query 22 in process 42B2, the reflection determination unit 42B extracts the description of the aggregation operation. In the example of the above SQL query 22, this corresponds to extracting the aggregation operation "SUM" for "col12". If the description of the aggregation operation is not included in the SQL query 22 (for example, in the case of SELECT col12 FROM table2020), the execution of this step is omitted.

[0049] Next, in process 42B3, the reflection determination unit 42B searches the reflection history table 24 (see Figure 6, described in detail later) for an entry (row) whose reflection target column is the same as the column extracted in step 42B1, whose aggregation operation method is the same as the aggregation operation extracted in step 42B2, and whose reflection target table is different from the table extracted in step 42B1.

[0050] Here, "identical" columns are considered to mean, but are not limited to, the following cases: For example, if at least one of the column names and tag names attached to the columns is the same, the columns are determined to be "identical."

[0051] Alternatively, columns are determined to be identical if the statistical information (count, average, maximum, etc.) and metadata of the data contained in the columns fall within a predetermined range. Metadata here refers to additional data about the data itself, such as the time the data was generated, data access permissions, and data size.

[0052] Furthermore, if the feature information of the data determined and generated using a predetermined logic (for example, that shown in https: / / www.hitachivantara.com / en-us / pdf / white-paper / using-ai-ml-to-power-data-fingerprinting-whitepaper.pdf) is the same, the columns may be determined to be "identical."

[0053] Additionally, columns that are determined to be the same using data catalog technology (e.g., the one shown at https: / / www.oracle.com / big-data / what-is-a-data-catalog / ) may also be treated as "identical."

[0054] On the other hand, "different" tables refer to the following cases, but are not limited to these: "Different" means that if the table names or tag names attached to the tables are different, the tables are determined to be "different."

[0055] In addition, tables whose statistical information (number, average value, maximum value, etc.) or metadata of the data contained in the tables does not fall within a predetermined range are determined to be different.

[0056] Furthermore, if the feature information of the data determined and generated using a predetermined logic (for example, that shown in https: / / www.hitachivantara.com / en-us / pdf / white-paper / using-ai-ml-to-power-data-fingerprinting-whitepaper.pdf) is different, the tables may be determined to be "identical."

[0057] Additionally, tables that are determined to be different by data catalog technology (for example, the one shown at https: / / www.oracle.com / big-data / what-is-a-data-catalog / ) are also treated as "different." good.

[0058] Returning to the description of the flow, in the determination of step 42B4, if an entry is found in the reflection history table 24 as a result of the above-mentioned step 42B3 (42B4: Yes), the reflection determination unit 42B executes reflection generation (step 42B5).

[0059] On the other hand, if the result of the above judgment is that no entry is found in the reflection history table 24 (42B4: No), the reflection determination unit 42B, if necessary, instructs the generation of a reflection for the SQL query 22 via the user interface of the input / output device 5 (process 42B6), and terminates this flow.

[0060] This process starts when the reflection history table 24 is empty and the first reflection history This will be executed when creating or generating a reflection for an SQL query that is completely different from the previous ones (naturally, not listed in the reflection history table).

[0061] Note that when the target SQL query 22 is complex (e.g., SELECT name1 FROM table1 UNION SELECT name2 FROM table2) and there are multiple SELECT statements etc. in one SQL query 22, the flow in FIG. 5 shall be repeated for each SELECT statement etc.

[0062] Here, an example of the reflection history table 24 in this embodiment is shown in FIG. 6. In this reflection history table 24, the columns are configured such that, from left to right, there are an SQL query column, a reflection target table column, a reflection target column column, an aggregation operation method column, and a column identity information column. Also, in each entry (row) of the reflection history table 24 of this embodiment, information on the SQL query for which a previous reflection was created is stored.

[0063] Note that in the reflection target table column, the name of the table used for creating the reflection in the SQL query 22 is recorded. Also, in the reflection target column column, the name of the column used for creating the reflection in the SQL query 22 is recorded.

[0064] Also, in the aggregation operation method column, the aggregation operation method described in the SQL query 22 is recorded. However, when no aggregation operation is described, "NULL" is recorded in this column.

[0065] Also, the column identity information column will record statistical information of the data included in the column, characteristic information of the data determined and generated by a predetermined logic, information on the metadata given to the column, determination information of the data catalog technology, etc. This column identity information column not only records a single piece of information but also a link (pointer It may also record (such as a tag).

[0066] Subsequently, the update process of the reflection history table 24 in the reflection generation unit 42 will be described with reference to FIG. 7. First, the reflection generation unit 42 in process 42B5A generates a reflection for the SQL query 22.

[0067] In this case, if the dataset is not in tabular form (RDB table) such as a CSV file, JSON file, spreadsheet file, etc., the reflection generation unit 42 uses software such as Dremio, which is an open-source SQL engine for data lakes (e.g., https: / / docs.dremio.com / data-sources / file-upload.html), to convert those file formats into tabular form.

[0068] Also, when the dataset is an XML file, the reflection generation unit 42 may read and convert it using a known data management system, etc. (e.g., https: / / docs.oracle.com / cd / E11882_01 / appdev.112 / e23094 / xdb26imp.htm#ADXDB5733).

[0069] Note that the method of generating a reflection is such that when the SQL query 22 is, for example, SELECT co l13 FROM table2019, in Dremio, an SQL query for generating a reflection such as ALTER DATASET table2019 CREATE RAW REFLECTION ref1 USING DISPLAY(col13) is generated and executed (e.g., https: / / docs.dremio.com / SQL-reference / SQL-commands / acceleration.html).

[0070] ​In the case of a materialized view of an RDBMS, for example, in PostgreSQL, a SQL query for reflection generation such as "CREATE MATERIALIZED VIEW ref1 AS SELECT col13 FROM table2019" is generated and executed (for example, https: / / www.postgreSQL.org / docs / 9.3 / SQL-CREATEmaterializedview.html).

[0071] Also, in process 42B5B, the reflection generation unit 42 updates the reflection history table 24 and ends the flow. This update process increments the number of entries (rows) in the reflection history table 24 by one, and writes the SQL query 22, the reflection target table and reflection target columns of the SQL query 22 extracted in process 42B1, the aggregation operation method extracted in process 42B2, and the column identity information used for identity determination of the reflection target table and reflection target columns there. The same will be written for the reflection target table and reflection target columns of the SQL query 22, the aggregation operation method extracted in process 42B2, and the column identity information used for identity determination of the reflection target table and reflection target columns.

[0072] Next, the process in the query semantic analysis unit 42A will be described based on FIG. 8. In this case, in process 42A1B, the query semantic analysis unit 42A determines whether the query 20 is a SQL query conforming to the SQL standard.

[0073] As a result of this determination, if the query 20 is a SQL query (42A1B: Yes), the query semantic analysis unit 42A copies the query 20 to the SQL query 22 in process 42A1C and ends the process.

[0074] On the other hand, as a result of the above determination, if the query 20 is not a SQL query (42A1B: No), the query semantic analysis unit 42A executes the query-to-SQL query conversion process 42A1B. The process of this query-to-SQL query conversion has already been shown in the description of FIG. 4.

[0075] Next, the processing of the reflection generation unit 42 will be described with reference to Fig. 9. In this case, in step 42B5C, the reflection generation unit 42 determines whether the storage capacity required to store the reflection to be created does not exceed the maximum storage capacity for reflections.

[0076] In this case, the determination method is to check whether the inequality of maximum reflection storage capacity > existing reflection storage capacity + storage capacity required to store the current reflection is satisfied.

[0077] The maximum reflection storage capacity is the maximum storage capacity that a user can use in primary storage (memory) or secondary storage (storage). It is assumed that the value for this maximum reflection storage capacity will be defined in a configuration file, etc.

[0078] The existing reflection storage capacity is the reflection history table 24 in Figure 10 (described later). The memory capacity required for this reflection is the total size of the columns handled by the reflection you are trying to create.

[0079] Note that the maximum reflection storage capacity, the existing reflection storage capacity, and the storage capacity required for the current reflection are all measured in the same unit of storage capacity (megabytes, etc.) before evaluating the inequality.

[0080] If the result of the determination in the above-mentioned process 42B5C is that the above-mentioned inequality is satisfied, that is, the storage capacity is not insufficient (42B5C: No), the reflection generation unit 42 proceeds to process 42B5A. This process 42B5A has already been described with reference to FIG.

[0081] On the other hand, if the result of the above determination is that the inequality is not satisfied, that is, the storage capacity is insufficient (42B5C: Yes), the reflection generation unit 42 transitions to process 42B5D.

[0082] In process 42B5D, the reflection generation unit 42 searches for a reflection that can be deleted. A reflection that can be deleted refers to, for example, an entry in the reflection history table 24 of FIG. 10 that satisfies the condition that the value in the size column is greater than the storage capacity required for this reflection, and that has a relatively older reflection creation time.

[0083] This search method is based on the observation that the older a reflection is, the more likely it is that the underlying dataset has been updated (i.e., the reflection is inconsistent with the original dataset).

[0084] Alternatively, among entries that satisfy the condition "size column value > storage capacity required for this reflection," entries with low reflection creation frequency may be identified as targets for deletion. This is based on the observation that entries with low creation frequency are less useful.

[0085] If an entry that satisfies the conditions for being deletable as described above is found (42B5D: Yes), the reflection generation unit 42 transitions to process 42B5E1. On the other hand, if an entry that satisfies the conditions is not found (42B5D: No), the reflection generation unit 42 ends the process.

[0086] In process 42B5E, the reflection generation unit 42 deletes the reflection corresponding to the entry found in process 42B5D, and deletes the entry in the reflection history table 24 of FIG.

[0087] 10 shows an example of another embodiment of the reflection history table 24. In this case, the columns in the reflection history table 24 include, from the left, an SQL query column, a reflection target table column, a reflection target column column, an aggregation operation method column, a column identity information column, a size column, a reflection creation time column, and a creation frequency column.

[0088] Of these, the SQL query column through the column identity information column are the same as those in the reflection history table 24 shown in Figure 6. On the other hand, the size column indicates the storage capacity required for the reflection corresponding to that entry (row). In the reflection history table 24 of Figure 10, MB (megabytes) is used as an example of a unit, but other units (such as GB (gigabytes)) may be used as long as they are consistent throughout the present invention.

[0089] Also, the reflection creation time column shows the reflection corresponding to that entry (row). The creation time is the time when the reflection was created. The creation frequency column indicates how often the reflection corresponding to that entry (row) was created.

[0090] Returning to the explanation of the flow, in process 42B5F, the reflection generation unit 42 updates the reflection history table 24 in Fig. 10, and ends the flow.

[0091] This update process, for example, adds one entry (row) to the reflection history table 24 in Figure 10 searched in process 42B3 in Figure 5, writes the SQL query 22, the reflection target table and reflection target column of the SQL query 22 extracted in process 42B1, the aggregation operation method extracted in process 42B2, the column identity information used to determine the identity of the reflection target table and reflection target column, the reflection size (the storage capacity required for this reflection), and the reflection creation time, and also increments the creation frequency by one.

[0092] The reflection generation unit 42 stores logs of various processes, such as the reflection generation performed as described above and the update of the reflection history table 24, in the log file 26. It is preferable that the reflection generation unit 42 outputs the log file 26 through the input / output device 5, for example, in response to a request from a user or at a predetermined time.

[0093] The best mode for carrying out the present invention has been specifically described above, but the present invention is not limited to this and can be modified in various ways without departing from the spirit of the present invention.

[0094] According to this embodiment, by using the reflection generation history, it is possible to automatically generate reflections not only for RDB but also for data in CSV, JSON, XML format files, etc., even when a similar but not identical query is used. In other words, it is possible to automatically generate reflections for SQL queries by using the reflection generation history.

[0095] The description of this specification makes at least the following clear: That is, in the reflection generation system of this embodiment, the arithmetic device may identify queries related to the past reflection of the same content that have the same column as the data extraction target specified in the new query, and select from the history, from the identified queries, queries whose reflection generation source is different from that of the new query.

[0096] This allows reflections to be generated from tables with different attributes, such as the time, season, or region when the target data was acquired or observed, even if the columns (i.e., the data and operation types that are the target of reflection generation) are the same. This makes it possible to efficiently generate reflections that can be used for comparing data over time or across regions. Furthermore, using the reflection generation history makes it possible to automatically generate reflections for SQL queries more efficiently.

[0097] In addition, in the reflection generation system of this embodiment, the storage device may further store information on the number of times a reflection has been generated in the history, and when generating the reflection, the calculation device may perform reflection generation in order of the number of times the reflection has been generated among the reflections that were the subject of the generation, until the usage of the reflection storage area reaches a predetermined standard.

[0098] This allows more useful reflections to be generated preferentially, taking into account the free space in the reflection storage area. As a result, reflections can be automatically generated for SQL queries more efficiently using the reflection generation history.

[0099] Furthermore, in the reflection generation system of this embodiment, when generating the reflection, if the usage of the reflection storage area exceeds a predetermined standard, the calculation device may delete reflections whose generation count is below the standard and then perform reflection generation.

[0100] This allows for the generation and storage of useful reflections while ensuring a storage location for reflections, and is expected to enable operations that allow for the continuous maintenance of effective reflections overall.Furthermore, by using the reflection generation history, it becomes possible to automatically generate reflections for SQL queries more efficiently.

[0101] In addition, in the reflection generation method of this embodiment, the information processing device may identify queries related to past reflections of the same content that have the same column as the data extraction target specified in the new query, and select from the history, among the identified queries, those whose reflection generation source is different from that of the new query.

[0102] In addition, in the reflection generation method of this embodiment, the information processing device may further retain information on the number of times reflections have been generated in the history in the storage device, and when generating the reflections, perform reflection generation in order of the number of times the reflections have been generated, until the usage of the reflection storage area reaches a predetermined standard.

[0103] Also, in the reflection generation method of the present embodiment, when the information processing apparatus generates the reflection and the usage status of the reflection storage area exceeds a predetermined standard, the information processing apparatus may execute reflection generation after deleting reflections whose generation times are below the standard.

Explanation of Signs

[0104] 1 Reflection generation system 2 CPU (Arithmetic unit) 3 Main memory device 4 External storage device 5 Input / output device 20 Query 22 SQL query 24 Reflection history table 26 Log file 42 Reflection generation unit 42A Query semantic analysis unit 42A1A Query-to-SQL query conversion unit 42B Reflection determination unit 50 Table 52 Reflection

Claims

1. A storage device that retains a history of reflections; An arithmetic unit that, based on the content of a reflection indicated by a new query for generating a reflection, selects, from among queries regarding past reflections having the same content as the new query, those for which the reflection generation source is different from the new query, from the history, and generates a reflection based on the selected queries regarding the past reflections; A reflection generation system, characterized by including the above.

2. The arithmetic unit: Identifies, as queries regarding past reflections having the same content, those for which the columns of data extraction targets specified by the new query are the same, and selects, from among the queries for which the columns of data extraction targets are the same, those for which the reflection generation source is different from the new query, from the history. The reflection generation system according to Claim 1, characterized by the above.

3. The storage device: Further retains information on the number of times of reflection generation in the history; The arithmetic unit: When generating a reflection, starting from those reflections with a large number of times of generation among the reflections targeted for generation, executes reflection generation until the usage status of the reflection storage area reaches a predetermined standard. The reflection generation system according to Claim 1, characterized by the above.

4. The arithmetic unit: When generating a reflection, if the usage status of the reflection storage area exceeds a predetermined standard, deletes reflections with a number of times of generation below the standard and then executes reflection generation. The reflection generation system according to Claim 3, characterized by the above.

5. An information processing apparatus: Is provided with a storage device that retains a history of reflections; Based on the content of a reflection indicated by a new query for generating a reflection, selects, from among queries regarding past reflections having the same content as the new query, those for which the reflection generation source is different from the new query, from the history, and generates a reflection based on the selected queries regarding the past reflections. A reflection generation method, characterized by the above.

6. The information processing apparatus: As a query regarding past reflections with the same content, identify those in which the columns of the data extraction target specified by the new query are the same, and among the queries with the same columns of the data extraction target identified, select from the history those in which the reflection generation source is different from the new query. The reflection generation method according to claim 5, characterized in that.

7. The information processing apparatus, In the history in the storage device, further holds information on the number of times of reflection generation, When generating the reflection, starting from those with a large number of times of generation among the reflections targeted for the generation, execute reflection generation until the usage status of the reflection storage area reaches a predetermined standard. The reflection generation method according to claim 5, characterized in that.

8. The information processing apparatus, When generating the reflection, if the usage status of the reflection storage area exceeds a predetermined standard, delete the reflections with the number of generations below the standard and then execute reflection generation. The reflection generation method according to claim 7, characterized in that.

9. In an information processing apparatus including a storage device that holds a history of reflections, Based on the content of the reflection indicated by a new query for reflection generation, select from the history those queries regarding past reflections with the same content in which the reflection generation source is different from the new query, and generate a reflection based on the selected query of the past reflection. A reflection generation program, characterized by causing the above processing to be executed.

Images