JPWO2018100734A1 - Data processing system - Google Patents

Abstract

  A data processing system according to an embodiment of the present invention is configured to manage one or more tables, accept search requests for the tables, and import data recorded in a file into the tables. When the data processing system accepts a data search request for a table, the data processing system checks whether there is a file that records the data to be imported for the table, and if there is no file that has not been imported, specifies the search request from within the table If there is a file that has not yet been imported, the data specified in the search request is searched from the table and the data specified in the search request is also searched for the file. .

Description

  The present invention relates to a data processing system.

  In recent years, big data analysis for diagnosis and prediction has been actively conducted by analyzing large amounts of time-series data such as sensor measurement values obtained from sensors installed in plant facilities, etc., and stock prices and exchange rates in the financial field. Has been done. In such big data analysis, a large amount of time-series data updated every moment is continuously collected, and analysis using the collected data is performed.

  Data used for big data analysis is often managed by a so-called database management system (DBMS). In a DBMS, particularly a relational database management system (RDBMS), a plurality of data is managed as tabular information called “table”. However, before the data is managed by the DBMS, for example, immediately after the data is acquired from the sensor or the like, it is generally stored in a storage device in a general file format such as a text file. Is.

  Many DBMSs provide a user with a function for storing a plurality of data stored in a file in a table managed by the DBMS. Hereinafter, this function is referred to as “import function”. By using the import function, the user does not need to insert data into the table one record at a time, and the data in the file can be stored in the table in a lump (called “import”).

  If the data in the table cannot be accessed during import, the user cannot perform tasks such as analysis during that time. Especially when there is a large amount of data in the file, it takes a long time to import, so the period during which the business cannot be executed becomes longer. In order to solve such a problem, some DBMSs support a technology (hereinafter referred to as “background import function”) that enables data import while accepting a search request to a table. Yes (Non-Patent Document 1). By using the background import function, the user can access the table even during import.

Hitachi Advanced Data Binder Setup and Operation Guide [online], Hitachi Ltd., (http://itdoc.hitachi.co.jp/manuals/3000/3000650150e/A0650150.PDF)

  In big data analysis, it is required to collect and analyze a large amount of time-series data in real time. When the background import function is used, the user can search the data in the table even while the file data is being imported into the table, but cannot search the data recorded in the file being imported. Therefore, the user must wait until the import process is completed in order to analyze the data recorded in the file.

  A data processing system according to an embodiment of the present invention is configured to manage one or more tables, accept search requests for the tables, and import data recorded in a file into the tables. When the data processing system accepts a data search request for a table, the data processing system checks whether there is a file that records the data to be imported for the table, and if there is no file that has not been imported, specifies the search request from the table If there is a file that has not yet been imported, the data specified in the search request is searched from the table and the data specified in the search request is also searched for the file. .

  According to the present invention, data can be accessed immediately without waiting for completion of data import.

It is a block diagram of a data processing system. It is an example of the table in which search object data is stored. It is an example of the format of an import file. It is a functional block diagram of a server. It is an example of a dictionary. It is an example (2) of a dictionary. It is an example of a data file management table. It is a flowchart of a file monitoring process. It is a flowchart of a file update check process. It is a flowchart of an import process. It is a conceptual diagram of a background import function. It is a flowchart of a search process. It is an example of the query before rewriting. It is an example of the partial query 2. It is an example of the query after rewriting. It is a flowchart of a query rewriting process. It is a generalization example of the query before rewriting. It is a generalization example of the query after rewriting. It is an example of the partial query 2 produced | generated by the query rewriting process in Example 2. FIG. 10 is a flowchart of query rewrite processing in the second embodiment. 10 is a flowchart of search processing in Embodiment 3. It is a comparison figure of Example 1 or 2 and Example 3. FIG. It is an example of the SQL query for storing the data of an import file in a table.

  Embodiments of the present invention will be described below with reference to the drawings. The embodiments described below do not limit the invention according to the claims, and all the elements and combinations described in the embodiments are essential for the solution of the invention. Is not limited.

(1) System Configuration FIG. 1 is a diagram showing a hardware configuration of a data processing system according to an embodiment of the present invention. The data processing system includes a database server 1 (hereinafter abbreviated as “server 1” or “DB server 1”), a client 2, and storage devices 3 and 4. The server 1 and the client 2 are connected to each other so as to be able to communicate with each other via a local area network (LAN) 6 configured using, for example, Ethernet. The server 1 is connected to the storage devices 3 and 4 via a network 5 (or called SAN 5) configured using, for example, a fiber channel (FibreChannel).

  The server 1 is a computer that processes a database access request received from a user of a data processing system (hereinafter referred to as a “user”), and includes a CPU 11, a memory 12, a network port 13 for connecting to the LAN 6, It has an input / output device 14 and a storage port 16. The memory 12 is a storage device such as a DRAM, for example, and is used to store the program or control information used when the program is executed when the CPU 11 executes the program. The CPU 11 is a component that executes a program for performing database access processing. In the data processing system according to the present embodiment, the server 1 is a so-called SMP (Symmetric Multi Processing) server, and includes a plurality of CPUs 11, and each CPU 11 may be configured to execute processes in parallel. Instead of providing a plurality of CPUs 11 in the server 1, a configuration in which one (or a plurality) of multi-core processors having a so-called plurality of processor cores in the server 1 may be provided.

  The input / output device 14 includes, for example, a device used when a user inputs information, such as a keyboard and a mouse, and a display (output) device such as a display and a printer. The storage port 16 is an interface for connecting the server 1 and the storage device 3.

  The client 2 is a computer used by the user to issue a reference update request to the database to the server 1 and receive an output of a processing result returned from the server 1. The client 2 includes a CPU 21, a memory 22, a network port 23 for connecting to the LAN 6, and an input / output device 24. The CPU 21, memory 22, network port 23, and input / output device 24 are the same as the CPU 11, memory 12, network port 13, and input / output device 14 of the server 1, respectively. In addition to the memory 22, the client 2 may include an auxiliary storage device such as a magnetic disk.

  The storage devices 3 and 4 are devices having a nonvolatile storage device such as a magnetic disk, and are devices for storing the database 31 and the import file 32. The storage devices 3 and 4 may be devices having a plurality of nonvolatile storage devices such as so-called disk arrays (or RAIDs). The storage device 3 is connected to the storage port 16 of the server 1 via the SAN 5.

  The data processing system according to the present embodiment collects and manages data generated by the external data source 8. For example, the data source 8 is a device including a sensor provided in a plant facility or the like, and is connected to the server 1 via a wide area network (WAN) 7 and a LAN 6. Although only one data source 8 exists in FIG. 1, a plurality of data sources 8 may exist.

  The data source 8 has a function of creating a file storing a large amount of information measured by the sensor and transmitting the created file to the server 1. The file sent to the server 1 is temporarily stored in the storage device 4, and information recorded in the file is then written (imported) into the table 300 in the storage device 3. The table 300 has a data structure created by the database management program 120 (described later). In this embodiment, the file sent from the data source 8 is called an “import file” (import file 32 in FIG. 1). In this embodiment, an example in which the storage device 4 storing the import file 32 and the storage device 3 storing the table 300 are different storage devices will be described. However, the table 300 and the import file 32 are the same. A configuration stored in the storage device 3 (or the storage device 4) may be employed.

  The memory 12 of the server 1 stores a program executed by the server 1 and control information used by the program. Examples of programs executed by the server 1 include a database management program 120, a file system program 121, an OS 122, and a data collection program 123.

  The OS 122 is a program that performs schedule control of various programs executed on the server 1. Further, the OS 122 according to the present embodiment may include a device driver that is a program for performing processing for providing abstracted hardware resources to various programs.

  The file system program 121 is a program that stores and manages files in the storage device 4 or the like. In this embodiment, the file system program 121 mainly accesses the import file 32 stored in the storage device 4, and the file system program 121 is not used when the database 31 is accessed. However, as another embodiment, the database 31 may be stored on a file system (data structure for storing and managing files) created by the file system program 121.

  The database management program 120 is a program sometimes called a relational database management system (RDBMS), creates a kind of tabular data structure called a “table”, and stores and manages the data in a table. In this embodiment, the table is created in the storage device 3. In principle, the database management program 120 mainly accesses data in the table. However, the database management program 120 according to the present embodiment supports table functions (described later) and is stored in a file (import file 32 stored in the storage device 4) managed by the file system program 121. You can also search for existing data. Details will be described later. The database management program 120 includes a program for processing a database access request (referred to as “query” or “SQL query”) from a user, and data for importing data stored in a file into the table 300. A program (referred to as an import program in this embodiment) is also included.

  The data collection program 123 is a program that performs processing of receiving a file from the data source 8 and storing it in the storage device 4.

  Management information used by these programs includes a dictionary 500 and a data file management table 700. Details of these will be described later.

  The program and management information described above are stored in the storage device 3 or 4 (or an auxiliary storage device (not shown) built in the server 1) when the server 1 is not operating. When the server 1 is started and necessary (when a search process or the like is performed), these programs and management information are read from the storage device 3 or 4 onto the memory 12 and used by the CPU 11. The server 1 may store in the memory 12 programs other than the programs described above and information other than the management information described above.

  A client program 221 exists in the memory 22 of the client 2, and the CPU 21 executes the client program 221. The client program 221 is a program that provides a GUI (Graphical User Interface) or a CLI (Command Line Interface) for a user to issue an information search instruction.

(2) Configuration of Database and Import File Next, the configuration of the database 31 and the import file 32 handled by the data processing system according to the present embodiment will be described. In the server 1 according to the present embodiment, the database management program 120 defines a database area (referred to as the database 31) for defining the table 300 or the like in the storage device 3, and one or more tables are defined in the database 31. 300 is defined.

  First, an example of a table 300 managed by the server 1 is shown in FIG. In this embodiment, the data (record) stored in the table 300 is time series data as an example. The time series data is, for example, a collection of measurement data that the data source 8 continuously acquires from the sensor.

  The table 300 includes a plurality of records having four columns of DATE (311), VALUE1 (312), VALUE2 (313), and VALUE3 (314). The columns of VALUE1 (312), VALUE2 (313), and VALUE3 (314) are columns in which physical quantities such as temperature measured by sensors are stored. DATE (311) is a column for storing the date and time when the measurement by the sensor was performed. However, the table 300 in FIG. 2 is an example, and each record may have four or more columns. Conversely, the number of columns in the table 300 may be less than 4.

  An example of the import file 32 is shown in FIG. In the present embodiment, a so-called CSV (Comma Separated Value) format is adopted as the file format of the import file 32. However, a file format other than the CSV format may be used. The data stored in the import file 32 may be stored in a compressed state, but in this embodiment, an example in which data is stored in the uncompressed state in the import file 32 will be described.

  Each line of the import file 32 corresponds to a record in the table 300. That is, each of the information stored in the columns (DATE (311), VALUE1 (312), VALUE2 (313), and VALUE3 (314)) of the record in the table is described in a form separated by commas. In FIG. 3, a row 320 is a set of information corresponding to one record in the table. Elements 321, 322, 323, and 324 are information stored in DATE (311), VALUE 1 (312), VALUE 2 (313), and VALUE 3 (314) of the table, respectively. Hereinafter, unless otherwise specified, the row of the import file 32 is also referred to as a “record”, like the record of the table 300.

(3) Functional Block Configuration Next, the functional blocks of the server 1 will be described with reference to FIG. In the server 1 according to the present embodiment, the dictionary management unit 201, the file collection unit 202, the file monitoring unit 203, and the query reception unit 204 are executed by the CPU 11 executing the above-described program (mainly the database management program 120). , The query rewriting unit 205, the query optimization unit 206, the query execution unit 207, the database access unit 208, and the table function processing unit 209. Hereinafter, the role of each functional block and the management information used by each functional block will be described.

  The dictionary management unit 201 creates a table in accordance with a table creation request received from the user. When creating a table, the dictionary management unit 201 records table definition information in the dictionary 500. The contents of the dictionary 500 will be described later.

  The query receiving unit 204 receives a database access request from the user, causes an appropriate functional block to perform processing related to the request, and returns the processing result to the user. The DB server 1 according to the present embodiment receives a database access request (referred to as a “query” or “SQL query”) described in SQL (Structured Query Language) from the client 2 and processes the query. For example, when it is determined that the query needs to be rewritten, the query reception unit 204 passes the query to the query rewriting unit 205.

  The query rewriting unit 205 is a functional block for rewriting an accepted query. Details of processing performed by the query rewriting unit 205 will be described later.

  The query optimization unit 206 is a functional block that analyzes a received query and determines an execution procedure (execution plan) of processing related to the query. The query execution unit 207 performs processing such as retrieval of records stored in the table according to the processing execution procedure determined by the query optimization unit 206.

  The database access unit 208 is a functional block that accesses a record stored in the table 300. The database access unit 208 reads and writes records according to instructions from the query execution unit 207, and returns the result to the query execution unit 207. For example, if the instruction from the query execution unit 207 is a record search instruction, the database access unit 208 reads the record from the table 300 and returns it to the query execution unit 207.

  The table function processing unit 209 is a functional block that reads a file (in particular, the import file 32). The table function is a function standardized by SQL 2003, and the database management program 120 according to the present embodiment supports the table function. The table function processing unit 209 has a function of reading the import file 32 and returning each row described in the import file 32 to the query execution unit 207 as table format data.

  The file collection unit 202 is a functional block that performs processing for storing a file (import file) transmitted from the data source 8 in the storage device 4. When the CPU 11 executes the data collection program 123, the server 1 functions as the file collection unit 202. The file monitoring unit 203 is a functional block that monitors the import file 32 stored in the storage device 4. Further, the file monitoring unit 203 manages the correspondence between the import file 32 and the table 300 by storing it in the data file management table 700.

  In the present embodiment, there is a place where the contents of the processing executed by the server 1 are described with the functional block such as the query rewriting unit as the subject. As described above, in the data processing system according to the present embodiment, when the program (mainly the database management program 120) is executed by the CPU 11, the server 1 operates as an apparatus including these functional blocks. Therefore, the actual processing subject is the CPU 11 of the server 1 to be precise. However, in order to prevent the description from becoming redundant, the flow of various processes may be described with the functional block as the subject. When the processing content is described with the functional block as the subject, it means that the subject of the processing is the CPU 11.

(4) Management Information Next, the dictionary 500 and the data file management table 700 will be described. When the server 1 creates a table, the attribute information of the table to be defined is recorded in the dictionary 500. An example of information recorded in the dictionary 500 when the table 300 shown in FIG. 2 is defined (created) will be described with reference to FIGS. The dictionary 500 includes SQL_TABLES (510) in which attributes of the table 300 are stored, and SQL_COLUMNS (520) in which attributes of each column of the table are stored. FIG. 5 shows the structure of SQL_TABLES (510), and FIG. 6 shows the structure of SQL_COLUMNS (520).

  SQL_TABLEs (510) has one or more records having columns of a schema name (511), a table identifier (512), a table ID (513), a chunk specification (514), and an import file storage directory path (516). Each time a table is created, one record is created in SQL_TABLE (510). The schema name (511), table identifier (512), and table ID (513) are the name of the schema to which the created table belongs (generally, the name of the user who instructed the table creation), and the created table. Identifier (name of the table specified by the user), and identification number of the created table. Since these are information managed by a known RDBMS, the detailed description is omitted. In this embodiment, the set of schema name and table identifier may be referred to as “table name”.

  Further, the table attributes managed by the database management program 120 according to the present embodiment include information such as chunk designation (514), maximum number of chunks (515), and import file storage directory path (516). The chunk designation (514) is a table for designating whether or not chunk creation is possible. The database management program 120 according to the present embodiment manages the data stored in the table in one import process as one chunk, and this chunk is called “chunk”. When the chunk designation (514) is “Y”, it means that a chunk can be created in the table. The maximum number of chunks (515) is the maximum number of chunks that can be created in the table. Information stored in the chunk specification (514) and the maximum number of chunks (515) is specified by the user when defining the table.

  The import file storage directory path (516) stores the name of the directory where the import file is stored. This directory name information is specified by the user when the user defines the table. If no directory name information is specified by the user at the time of table definition, a NULL value is stored in the import file storage directory path (516). In this embodiment, it is assumed that the contents (directory name) of the import file storage directory path (516) are different for each table (the user designates it as such). That is, the file (s) stored in one directory is imported into one specific table.

  SQL_COLUMNS (520) has one or more records having columns of schema name (521), table identifier (522), column name (523), column ID (524), data type (525), and data definition length (526). It is a kind of table. These are information managed by a known RDBMS. Each time a table is created, one SQL_COLUMN (520) that stores information about the table is created.

  The schema name (521) and the table identifier (522) are the same information as the table identifier (512) and the table ID (513) of the SQL_TABLEs (510), respectively. The column name (523) is the name of the column created in the table, and the column ID (524) is the identification number of the created column. The data type (525) specifies the type of data stored in the created column. As shown in FIG. 6, the data type is, for example, an integer type (INTAGER) or a date type (DATE). The data definition length (526) specifies the length (maximum length) of data stored in the created column.

  Next, a method for managing the import file by the data processing system according to the present embodiment will be described.

  An example of the data file management table 700 is shown in FIG. The data file management table 700 is a table for storing information about each import file, and information on one import file is stored in one record.

  The file path (703) is the file name of the import file. An absolute path name is used as the file name stored in the file path (703).

  On the other hand, the schema name (701) and the table identifier (702) are identification information of a table that is an import destination of a file (import file) having a name stored in the file path (703). The file path (703) on the top line in FIG. 7 is “/home/data_dir/aaa.csv”, the schema name (701) is “USER1”, and the table identifier (702) is “T1”. “/home/data_dir/aaa.csv” indicates that the schema name (701) should be imported (or has been imported) into a table having “USER1” and table identifier (702) “T1”. The imported flag (707) represents the processing state of the import file. Specifically, information indicating whether or not the file having the name recorded in the file path (703) has been imported into the table is stored in the imported flag (707). When the imported flag (707) is “N”, it means that the file having the name specified by the file path (703) has not been imported into the table, while the imported flag (707) is “Y”. In this case, the file has already been imported into the table (the table specified by the schema name (701) and the table identifier (702)).

  The file update date / time (705) represents the creation date / time (or update date / time) of the file, and the one-line insertion date / time (706) represents the date / time when this record was created in the data file management table 700 (or the date / time when the record was updated). ). The trigger for storing (inserting) a record in the data file management table 700 and the specific contents of the processing at that time will be described later.

  In the present embodiment, the data file management table 700 is also a table managed by the database management program 120. Therefore, when the server 1 accesses a record in the data file management table 700, it can be accessed by issuing an SQL query. The attribute information of the data file management table 700 is also stored in the dictionary 500.

(5) Process Flow The process flow executed in the data processing system will be described below.

(5-1) File Monitoring Process First, the flow of the file monitoring process will be described. The file monitoring process is executed by the file monitoring unit 203. In the file monitoring process, it is checked whether an import file is newly stored in the storage device 4 or whether the import file already stored is updated.

  The process of storing the file in the storage device 4 is performed by the file collection unit 202. The file collection unit 202 is executed in response to a file transmission request from the data source 8. The data source 8 always performs measurement using a sensor, and transmits a file storing the measurement results (file shown in FIG. 3) to the server 1 as appropriate. File transmission to the server 1 may be performed periodically, or irregular file transmission may be performed.

  In response to the data source 8 issuing a file transmission request to the server 1, the server 1 starts executing the file collection unit 202. The file collection unit 202 is a functional block realized mainly by the data collection program 123 and the file system program 121 being executed by the CPU 11. In this embodiment, it is assumed that the file transmission request issued by the data source 8 includes the file storage directory name along with the file. That is, the data source 8 knows the name of the storage directory of the import file in advance. Therefore, the file collection unit 202 only performs a process of storing the file received from the data source 8 in the directory specified by the file transmission request, and thus detailed description thereof is omitted.

  Hereinafter, the flow of the file monitoring process will be described with reference to FIG. The file monitoring process is a process that is executed periodically. That is, the execution trigger of the file monitoring process is irrelevant (independent) with the trigger of file transmission from the data source 8 to the server 1. If a stop request for the file monitoring process is received from the user, the file monitoring process stops.

  Step 1001: The file monitoring unit 203 waits for a predetermined time. The length of the waiting time may be a predetermined fixed value, or the file monitoring unit 203 may be configured so that the user can specify the waiting time.

  Step 1002: The file monitoring unit 203 refers to the dictionary 500 and identifies a directory name in which the import file is stored. Specifically, the file monitoring unit 203 can identify the directory name where the import file is stored by referring to the column 516 (import file storage directory path) of the SQL_TABLE (510) of the dictionary 500. When there are a plurality of records in the SQL_TABLEs (510) (that is, when a plurality of tables are defined in the server 1), the file monitoring unit 203 refers to each record of the SQL_TABLEs (510), and import file storage directory path (516) ) Identify all the directory names stored in. In the following, in order to avoid complicated explanation, a case where only one record in the import file storage directory path (516) has a directory name stored in the SQL_TABLEs (510) record will be described. To do. The directory specified here is called a “monitoring target directory”.

  Step 1003: The file monitoring unit 203 checks a file in the monitoring target directory. Details of the processing here will be described later (FIG. 9). As a result of executing step 1003, if a new file (import file) is stored in the monitoring target directory, the data file management table 700 is displayed. The contents of are updated.

  Step 1004: In step 1003, when the file monitoring unit 203 updates the contents of the data file management table 700 (step 1004: Yes), the file monitoring unit 203 next executes step 1005. If the file monitoring unit 203 has not updated the contents of the data file management table 700 in step 1003 (step 1004: No), the file monitoring unit 203 returns to step 1001 next.

  Step 1005: This step is performed when the file monitoring unit 203 updates the contents of the data file management table 700 in Step 1003 (that is, when a new import file is stored in the monitoring target directory or has already been stored). (If the import file has been updated). In step 1005, the file monitoring unit 203 performs an import process on the newly stored import file (or updated import file). The details of the import process will be described later.

  Step 1006: The file monitoring unit 203 confirms whether the user has issued a request to stop the file monitoring process. If the user issues a request to stop the file monitoring process, the file monitoring unit 203 ends the process. When the request for stopping the file monitoring process has not been issued, the file monitoring unit 203 repeats the process from step 1001 again.

  The file monitoring unit 203 performs the above processing. In the above description, the case where only one monitoring target directory is specified in step 1002 has been described. However, if a plurality of directory names are specified in step 1002, the file monitoring unit 203 performs the following for each specified directory. Steps 1003 to 1005 are performed.

  Next, details of the processing (referred to as file update check processing) performed in step 1003 of FIG. 8 will be described with reference to FIG. As in the description of FIG. 8, here, a case where only one monitoring target directory is specified in step 1002 of FIG. 8 will be described. If a plurality of directories are specified in step 1002, the file update check process described below is performed for each specified directory.

  Step 1201: The file monitoring unit 203 acquires information on each file stored in the monitoring target directory. Here, the information that the file monitoring unit 203 needs to read is information recorded in the data file management table 700. Specifically, it is recorded in the file path (703), the file size (704), and the file update date and time (705). Information. When a file (import file) sent from the data source 8 is stored in the storage device 4, the file collection unit 202 (data collection program 123 and file system program 121) adds a file name and file creation date / time ( Alternatively, attribute information such as update date and time is assigned and stored. Therefore, the file monitoring unit 203 can acquire information on each necessary file by reading the attribute information of each file assigned by the file system program 121 from the storage device 4.

  Step 1202: If all the files in the monitoring target directory have been processed after step 1203, the file monitoring unit 203 ends the processing. If there are files in the monitoring target directory that have not been processed in step 1203 and subsequent steps, the file monitoring unit 203 next executes step 1203.

  Step 1203: The file monitoring unit 203 selects information on one file to be processed in step 1204 and subsequent steps from the information on each file acquired in step 1201. In the following description, the information on the selected file is referred to as “target file information”, and the file having the target file information as attribute information is referred to as a “target file”. Then, the file monitoring unit 203 searches the row (record) in the data file management table 700 for a record having a file path (703) that is the same as the file name of the target file.

  Step 1204: If the result of the processing in step 1203 is that the record having the same file name as the target file does not exist in the data file management table 700 (step 1204: No), the file monitoring unit 203 performs step 1205, If there is, then step 1206 is executed. The case where the determination in step 1204 is negative is a case where a new import file is stored in the monitoring target directory.

Step 1205: The file monitoring unit 203 creates one record in the data file management table 700, and includes information on the target file in the file path (703), file size (704), and file update date and time (705) of the created record. Is stored. At this time, the file monitoring unit 203 records the current time (the time at which step 1205 is executed) in the row insertion date / time (706) of the created record, and the schema name (701) and the table identifier (702). Each records the schema name and table identifier of the table into which the target file is to be imported. Since the schema name and table identifier of the table into which the file is to be imported are recorded in SQL_TABLES (510), the file monitoring unit 203 monitors the directory name of the import file storage directory path (516) of SQL_TABLES (510). A row that is the same as the directory is specified, and information on the schema name (511) and table identifier (512) of the row is stored in the schema name (701) and table identifier (702) of the record created this time.

  Step 1206: When the information of the target file is newer than the information of the file recorded in the record in the data file management table 700 (step 1206: Yes), the file monitoring unit 203 executes step 1207, and otherwise The process is repeated from step 1202. Specifically, the file monitoring unit 203 performs the determination in step 1206 by comparing the update date and time of the file.

  Step 1207: If the information of the target file is newer than the information of the file recorded in the record in the data file management table 700, it means that the file has been updated. Therefore, the file monitoring unit 203 updates the file information recorded in the record in the data file management table 700 using the file information selected in Step 1203. Specifically, the file size (704) and the file update date / time (705) are updated using the information of the file selected in step 1203. Further, the file monitoring unit 203 updates the row insertion date / time (706) to the current time (the time when step 1207 is executed), and stores “N” in the imported flag (707).

  Next, the import process executed in step 1005 of FIG. 8 will be described with reference to FIG. In order to avoid complicated description, in the following, only one record (or import file) to be imported is updated (or updated in step 1003 in the data file management table 700 (or in step 1003) (or (If added)). In the following description of the import process, the import file that is the target of the import process (the process described in FIG. 10) is referred to as an “import source file”.

  In this embodiment, the file monitoring unit 203 supports a background import function. The background import function is a well-known function, and is a function that enables a search for a table that is an import process target during the import process. This function is realized by an import program included in the database management program 120, for example. For example, steps 1101 and 1102 described below are performed by the import program being executed by the CPU 11 of the server 1. Steps 1101 and 1102 are known processes.

  Step 1101: The file monitoring unit 203 creates a chunk in the import processing target table. Here, an empty chunk is created.

  Step 1102: The file monitoring unit 203 imports data from the import source file to the import processing target table. The data to be imported here is added to the chunk created in step 1101.

  Step 1103: After all the data in the import source file has been imported, the file monitoring unit 203 records the import source file among the records in the data file management table 700 (the file path (703) contains the import source file). The imported flag (707) of the record in which the file name is stored is changed to “Y”, and the process ends.

  An overview of the background import function and chunks used in the import process of FIG. 10 will be described with reference to FIG. In FIG. 11, areas 1501 and 1502 surrounded by dotted lines represent chunks. The chunk 1501 exists before the import process, and the chunk 1502 represents a chunk (empty chunk) created when step 1101 is executed.

  During the import process (step 1102), the data stored in the import source file is sequentially added to the chunk 1502, and the content of the chunk 1501 does not change. Therefore, when the user issues a search request to the table during data import (during execution of step 1102), the server 1 may perform a search process using the contents of the chunk 1501 and return the search result to the user. it can.

  When step 1102 is completed, the data stored in the chunk 1502 is also changed to the search process target in the search process. Therefore, when the user issues a search request to the table after the completion of step 1102, the server 1 searches for data from the chunk 1501 and the chunk 1502.

(5-2) Search Process Next, the flow of the search process will be described. As described above, if the database system supports a well-known background function, even if a search request for a table subject to import processing is received from the user during the import processing, the search result is displayed without causing the user to wait. Can return. However, as described above, when a search request for the import processing target table is received from the user during the import processing, the search target data is the data in the chunk 1501 of FIG. Data), and the data recorded in the import file cannot be searched. When the size of the import file is extremely large, the time required for the import process becomes long, so that the time during which the data recorded in the import file cannot be used becomes long.

  In order to solve the problem, the server 1 according to the present embodiment enables the search of the data stored in the import file when a search request is accepted during the import process. FIG. 12 shows the flow of search processing performed in the data processing system according to the present embodiment.

  Step 1301: When the query receiving unit 204 receives an SQL query from the client 2 used by the user, the query receiving unit 204 performs analysis for determining whether the query needs to be rewritten. Specifically, the query reception unit 204 first determines whether there is a record in the data file management table 700 in which the same table name as that described in the FROM clause of the received SQL query is registered. If there is such a record, the query reception unit 204 determines whether the imported flag (707) of the record is “N”. If there is a record whose imported flag (707) is “N”, it means that the import file for the table to be searched is stored in the storage device 4, but the import file has not yet been imported into the table. To do.

  Step 1302: If there is an import file that has not yet been imported to the table to be searched as a result of the processing in Step 1301, Step 1303 is performed next. On the other hand, when the import file information for the table to be searched is not registered in the data file management table 700, or the import file information for the table to be searched is registered, the import file has already been imported (imported). If the completed flag (707) is “Y”)), step 1303 is skipped.

  Step 1303: The query receiving unit 204 passes the received SQL to the query rewriting unit 205. The query rewriting unit 205 to which the query is passed performs a query rewriting process, which will be described later.

  Step 1304: The query reception unit 204 passes the query to the query optimization unit 206. The query optimization unit 206 generates an execution plan for the query. This process is the same as that performed in a known RDBMS.

  Step 1305: The query execution plan is passed to the query execution unit 207, and the query execution unit 207 performs processing according to the execution plan. The query execution unit 207 reads records from the table 300 and the import file 32 using the database access unit 208 and the table function processing unit 209, and records corresponding to the conditions specified by the query from the read records. To extract. Then, the query execution unit 207 returns the extracted record to the query reception unit 204. The query reception unit 204 outputs the returned result to the user (client 2).

  This is the overall flow of the search process. The details of each step will be described below.

(5-3) Query Rewriting Process Here, the query rewriting process performed in step 1303 will be described. However, before that, description examples of a query delivered from the user (referred to as a pre-rewrite query) and a query rewritten in step 1303 (referred to as a post-rewrite query) will be described.

  The query illustrated in FIG. 13 is an example of an SQL query (pre-rewrite query) issued by the user to the server 1. Since SQL is well known, only the outline of the query will be described here. Note that the numbers given to the top of each line of the query shown in FIG. 13 and the like are line numbers given for explanation.

  The description content of the pre-rewrite query in FIG. 13 will be briefly described. The pre-rewrite query in FIG. 13 is a query that searches the table “USER1.T1” specified by the FROM clause. Hereinafter, the table specified in the FROM clause of the query is referred to as “search target table”. In this embodiment, for simplification of description, an example will be described in which a user issues an SQL query in which a WHERE phrase (a phrase for specifying a condition of a record to be searched) is not specified. The query in FIG. 13 is a query that instructs to extract and output all records in the search target table “USER1.T1”.

  An attribute (column) may be specified after the SELECT clause of the SQL query. When the server 1 accepts such a query, only the specified column is output. However, for simplicity of explanation, an example in which an asterisk (*) is described after the SELECT clause of the SQL query (a case where the user is required to output all attributes in the record) will be described. .

  FIG. 13 shows an example in which only one search target table is designated. However, actually, a query in which a plurality of search target tables are specified in the FROM phrase may be received from the user. Such an example will be described later.

  In the present embodiment, when the import file 32 during data import for the search target table exists in the storage device 4, the query rewriting unit 205 rewrites the SQL query received from the user to perform subsequent processing. The query execution unit 207 is made to acquire records not only from the table 300 but also from the import file 32. In order to cause the query execution unit 207 to read the import file 32, the query rewriting unit 205 generates a query using a table function. An example of a query for reading the import file 32 described using a table function is shown in FIG.

  The query shown in FIG. 14 is a query for performing the same search as the pre-rewrite query of FIG. 13 on the import file 32 (file ‘/home/data_dir/aaa.csv’). Hereinafter, this query is referred to as “partial query 2”, and on the other hand, a query for retrieving data from the search target table specified by the pre-rewrite query is referred to as “partial query 1”. The partial query 1 is a query having substantially the same content as the pre-rewrite query.

  The difference between the query of FIG. 14 and the query of FIG. 13 is only the information specified in the FROM phrase. Specifically, the name of the search target table (USER1.T1) specified by the query in FIG. 13 is replaced with a table function (2nd to 7th lines in FIG. 14) described below. Other than that, there is no difference between the two.

  The function of the table function used here will be described. The function ADB_CSVREAD () described in the argument part of the table function TABLE () (lines 3 to 6 in FIG. 14) is a function for outputting each line read from the file (CSV file) specified in the argument. is there. TABLE () is a function that outputs a text line as table format data. In FIG. 14, only one file name is specified as an argument of ADB_CSVREAD (), but a plurality of file names can be specified as an argument of ADB_CSVREAD (). For example, when ADB_CSVREAD (MULTISET ['xxx.csv', 'aaa.csv']) is described, the files xxx.csv and aaa.csv are read. “COMPRESSION_FORMAT = NONE” described in the fifth line is an argument that is specified when the file specified by the argument is not in a compressed format. If the import file 32 to be read is in a compressed format, An argument such as “COMPRESSION_FORMAT = GZIP” is described.

  The query rewriting unit 205 creates the partial query 2 described above, and further creates a query for obtaining the union of the output results of the partial query 2 and the query for searching the records in the table 300 (partial query 1). To do. In this embodiment, this is called a post-rewrite query. FIG. 15 shows an example of a query after rewriting the pre-rewrite query of FIG. A portion described as “partial query 1” in the figure is a query for performing a record search in the table 300. In this example, the content of the partial query 1 is the same as the pre-rewrite query. The fifth and subsequent lines in FIG. 15 (partial query 2) are queries for retrieving records from the import file 32, and have the same contents as in FIG. The post-rewrite query is obtained by concatenating partial query 1 and partial query 2 with a “UNION ALL” operator (an operator for obtaining a union) described in the third line.

  However, the rewritten query described above is an example, and the same query as the query described in FIG. 15 or the like does not have to be generated. It suffices if a query that can search for the record of the condition specified by the pre-rewrite query from both the table 300 and the import file 32 is generated. Further, the processing actually performed by the query rewriting unit 205 according to the present embodiment is slightly different from that described above, and the description content of the rewritten query is different from that described in FIG. The processing actually performed by the query rewriting unit 205 will be described below.

  The flow of query rewriting processing performed by the query rewriting unit 205 will be described with reference to FIG. In the following description, an example in which the query shown in FIG. 17 is given as the pre-rewrite query will be described. FIG. 17 is a more generalized example of the query described in FIG. 13, in which a plurality of search target tables are designated. The FROM clause actually contains a specific table name specified by the user, but in the following, in order to explain an example of generalizing the query rewriting method, information on the table name specified in the query, The explanation is made using the query replaced with the variable.

  In order to avoid complicated description, an example in which no WHERE clause is specified and an asterisk is specified in the SELECT clause will be described as an example of the pre-rewrite query.

  In FIG. 17, the variables ($ B1), ($ B2),. . . , ($ Bn) each represents a table name to be searched. ($ B1), ($ B2),. . . , ($ Bn) corresponds to one table name. That is, the query in FIG. 17 is an example when there are a plurality of search target tables. Hereinafter, ($ Bx) may be referred to as “table ($ Bx)” (x is an integer of 1 to n).

  Here, the tables ($ B1), ($ B2),. . . , ($ Bn), when the table ($ B1) is imported from a file (file name is “/home/data_dir/aaa.csv”), the query in FIG. Is rewritten by the query rewriting unit 205. FIG. 18 is an example after the pre-rewrite query of FIG. 17 is rewritten. The third to fourth lines are the partial query 1 and the eighth to 14th lines are the partial query 2.

  Step 1401: The query rewriting unit 205 analyzes the SQL query received from the query receiving unit 204, and specifies one table to be searched (a table described in the FROM phrase). The table to be specified here is a search target table that has not yet been processed after step 1403.

  Step 1402: If there is a search target table that has not been processed after Step 1403 as a result of Step 1401 (Step 1402: No), the query rewriting unit 205 performs Step 1403 next. If all the search target tables have been processed after step 1403 and the search target table to be processed after step 1403 cannot be identified in step 1401 (step 1402: Yes), the process ends. .

  In the following description of step 1403 and later, an example in which the table ($ B1) is specified in step 1401 will be described.

  Step 1403: The query rewriting unit 205 searches for a record from the data file management table 700 using the table name ($ B1) of the table identified in Step 1401 as a key. Specifically, among the records in the data file management table 700, the table name (that is, the combination of the schema name (701) and the table identifier (702)) specified in step 1401 (that is, ($ B1)). Find records that match. As a result of this processing, a plurality of records may be found. This is a case where a plurality of import files 32 exist in the storage device 4.

  Step 1404: The query rewriting unit 205 determines whether there is a record whose imported flag (707) is “N” in the records identified in Step 1403. If there is one or more records whose imported flag (707) is “N” (step 1404: Yes), step 1405 is performed next. Conversely, if there is no record whose imported flag (707) is “N” in the record specified in step 1403, or the table name (schema name (701) and table in the data file management table 700). If there is no record whose identifier (702) is ($ B1) (step 1404: No), the query rewriting unit 205 next performs step 1401.

  Step 1405: The query rewriting unit 205 generates a partial query 1 using the received query and the table name specified in step 1401. As described above, the partial query 1 is a query for searching for a record from a search target table (not an import file).

As shown in FIG. 18, when the accepted query is that of FIG. 17 and the table ($ B1) is specified in step 1401, partial query 1 is designated.
SELECT * FROM ($ B1)
Is generated. That is, in the partial query 1 generated here, the same content (that is, “*”) as the accepted query is specified in the SELECT clause (line 3). In the FROM phrase, the table ($ B1) specified in step 1401 among the plurality of search target tables specified in the pre-rewrite query is specified.

  Step 1407: The query rewriting unit 205 generates a partial query 2 using the contents of the file path (703) of the record of the data file management table 700 specified in step 1403. The information specified in the SELECT clause of partial query 2 is the same as that of partial query 1. A table function including a file path (703) as an argument is specified in the FROM phrase of the partial query 2 (lines 9 to 14 in FIG. 18).

  Step 1408: The query rewriting unit 205 generates a query that outputs the union of the output results of the partial query 1 and the partial query 2 created so far. Specifically, as shown in FIG. 18 (lines 3 to 15), the query rewriting unit 205 generates a query in which the partial query 1 and the partial query 2 are connected by the “UNION ALL” operator. To do. Then, the search target table name specified in the FROM clause of the pre-rewrite query is rewritten to the query generated here. For example, when the table ($ B1) is selected in step 1401, the ($ B1) portion of the FROM clause of the pre-rewrite query is rewritten to the query generated here. FIG. 18 shows an example in which ($ B1) is rewritten.

  Thereafter, the query rewriting unit 205 executes the processing from step 1401 again. When the processing described above is performed for all the search target tables specified in the pre-rewrite query, the post-rewrite query is completed, and the query rewrite processing ends.

(5-4) Execution Plan Generation and Optimization Finally, execution plan generation and execution processing performed in steps 1304 and 1305 will be described. The execution plan generation processing in the present embodiment is not significantly different from that performed in a known RDBMS, so an outline of execution plan generation and execution processing in the present embodiment will be described.

  In the data processing system according to the present embodiment, since the server 1 includes a plurality of CPUs 11, several processes can be executed in parallel. Therefore, when generating an execution plan, the query optimizing unit 206 generates an execution plan such that these processes are executed in parallel when there are a plurality of processes that can be parallelized.

  Processes that can be parallelized are processes that do not depend on each other, for example. Conversely, processes with dependencies cannot be executed in parallel. For example, in the post-rewrite queries described in FIG. 15 and the like, there is no dependency between partial query 1 and partial query 2. In order to execute the partial query 1, it is necessary to read the table 300 in the storage device 3, and in order to execute the partial query 2, it is necessary to read the import file 32 in the storage device 4. And the import file 32 are not dependent on each other (there is no restriction such that the import file 32 cannot be read until the table 300 is read). Therefore, the query optimization unit 206 generates an execution plan for executing the reading of the table 300 and the reading of the import file 32 in parallel, and causes the query execution unit 207 to execute the execution plan. When the query execution unit 207 receives such an execution plan, for example, a task (thread) for reading the table 300 and a task for reading the import file 32 are generated, and both are executed in parallel.

  Further, when there are a plurality (for example, m) of import files 32 to be read in the partial query 2, the read processing of the m import files 32 can be executed in parallel. Therefore, the query optimization unit 206 generates an execution plan for executing the reading of the import file 32 in parallel, and causes the query execution unit 207 to execute the execution plan.

  This is the end of the description of the data processing system according to the first embodiment. Since the above-described processing is performed in the data processing system according to the first embodiment, when a query is received from the user while the data in the import file is being imported into the table, not only the records in the table but also the import file You can also search for records within.

  In the above, an example in which data import from an import file to a table is executed in the background by executing a dedicated program called an import program included in the database management program 120 has been described. However, methods other than importing with a dedicated program may be adopted. For example, the import process may be performed by creating an SQL query for inserting a record in the import file into the table and causing the server 1 to execute the query.

  FIG. 23 shows an example of an SQL query for storing import file data in a table. The query example shown in FIG. 23 is an example in which the import destination table name is “USER1.T1” and the import file name is “/home/data_dir/aaa.csv”. When the import process is realized using such an SQL query, the file monitoring unit 203 creates a query as shown in FIG. 23 instead of steps 1101 and 1102 in FIG. The query may be executed using the unit 207.

  Subsequently, a data processing system according to the second embodiment will be described. Since the hardware configuration and functional block configuration of the data processing system according to the second embodiment are the same as those described in the first embodiment, illustration is omitted.

  In the data processing system according to the second embodiment, part of the query rewriting process is different from that described in the first embodiment, but the other points are the same as those described in the first embodiment. In the following, the query rewriting process in the second embodiment will be described with respect to differences from the first embodiment.

  FIG. 19 illustrates an example of the partial query 2 generated by the query rewriting process according to the second embodiment. In this example of partial query 2, the server 1 executes the query shown in FIG. 13 (query whose search target table name is “USER1.T1” (schema name is “USER1”, table identifier is “T1”)). It is an example when received as a pre-rewrite query. The difference between the partial query 2 shown in FIG. 19 and the partial query 2 described in the first embodiment (for example, see FIG. 15 and FIG. 18) is the content specified in the argument of the function ADB_CSVREAD (). . In the partial query 2 described in the first embodiment, the file name is described in the argument of the function ADB_CSVREAD (). On the other hand, in the partial query 2 shown in FIG. 19, an SQL query is described instead of the file name as shown in the fifth to ninth lines in FIG. Hereinafter, this query is referred to as “partial query 3”.

  As shown in FIG. 19, the partial query 3 has a schema name (701) “USER1”, a table identifier (702) “T1”, and an imported flag (707) set in the data file management table 700. This is a query for extracting the contents of the file path (703) of the record “N”. This is a query for performing the same search as in step 1403 in the query rewriting process shown in FIG.

  With reference to FIG. 20, the flow of the query rewriting process according to the second embodiment, particularly the differences from the process described in the first embodiment will be described. The query rewriting process according to the second embodiment is different in that step 1406 is added to the query rewriting process described in the first embodiment, and step 1407 'is performed instead of step 1407 described in the first embodiment. Since other processes are the same as those described in the first embodiment, steps 1406 and 1407 'will be described below.

  After step 1405, the query rewriting unit 205 executes step 1406. In step 1406, as described above, the query rewriting unit 205 generates the partial query 3 for performing the same search as in step 1403.

  Thereafter, the query rewriting unit 205 generates a partial query 2 including the partial query 3 in step 1407 ′. The processing performed in step 1407 'is the same as that in step 1407 of the first embodiment except that the partial query 3 is described in the argument of the function ADB_CSVREAD ().

  When the partial query 2 including the partial query 3 is processed by the query optimization unit 206 and the query execution unit 207, first, the search process described in the partial query 3 (record search in the data file management table 700) is performed. . As a result, the file name of the import file is specified, and the search process described in the partial query 2 is performed for the specified file.

  Regardless of which query rewrite process is executed in any of the first and second embodiments, at the time of query execution (process in the query execution unit 207), data search is performed from the import file being imported. In other words, the results obtained by the user are the same in both the first and second embodiments. However, since there is a slight time delay between the query rewriting process and the process for the rewritten query being executed by the query execution unit 207, the following event occurs in the first embodiment. Sometimes.

  An example when query rewrite processing is performed in the data processing system according to the first embodiment will be described with reference to FIGS. 12 and 16 used in the first embodiment. Further, a case will be described in which one record of the data file management table 700 matching the condition is detected in step 1403 of the query rewriting process (FIG. 16) and the file path (703) of the record is “aaa.csv”. .

  In this case, the file name “aaa.csv” is described in the argument of the function ADB_CSVREAD () in the partial query 2 generated in step 1407 of the query rewriting process. As a result, when the query execution unit 207 executes processing for the query, the file “aaa.csv” is read and a record search is performed from the read file.

  However, after the query rewrite process is executed (after step 1303 in FIG. 12), the import process of the file “aaa.csv” is completed before the process of the query execution unit 207 (step 1305 in FIG. 12) is started. Sometimes. Since the contents of the file “aaa.csv” are already reflected in the table by the import process, it is not necessary to read the file “aaa.csv” in this case. However, in the first embodiment, the file name “aaa.csv” is described in the partial query 2 as an argument of the function ADB_CSVREAD (). Will read the file “aaa.csv” (unnecessary file read occurs).

  On the other hand, in the second embodiment, a partial query 2 as shown in FIG. 19 is generated, and the file name is not directly described in the partial query 2. The file name is specified at the time when the process of the query execution unit 207 (step 1305 in FIG. 12) is started. If the import processing of the file “aaa.csv” has been completed at this point, among the records in the data file management table 700, the imported flag (707) of the record whose file path (703) is “aaa.csv”. ) Is changed to “Y”. For this reason, when the query execution unit 207 according to the second embodiment executes the processing related to the partial query 2, the file “aaa.csv” is not read, and thus unnecessary file reading can be suppressed in the second embodiment.

  Subsequently, a data processing system according to the third embodiment will be described. Since the hardware configuration and functional block configuration of the data processing system according to the third embodiment are the same as those described in the first or second embodiment, illustration is omitted. In the data processing system according to the third embodiment, a part of the search process is different from that described in the first embodiment, but the other points are the same as those described in the first embodiment. In the following, the difference between the search process in the third embodiment and the first embodiment will be described.

  FIG. 21 illustrates a flow of search processing performed in the data processing system according to the third embodiment.

  Step 2301: When the query receiving unit 204 receives an SQL query from the client 2 used by the user, the query receiving unit 204 specifies a search target table specified in the query. Further, the query receiving unit 204 refers to the SQL_TABLEs (510) and specifies the import file storage directory path (516) of the search target table.

  Step 2302: The query receiving unit 204 calls the file monitoring unit 203 to check the files in the directory specified in Step 2301. The processing performed here is the same as in Step 1003 (FIGS. 8 and 9) described in the first embodiment, and a new import file is stored in the directory specified in Step 2301 or already in this directory. Check if the existing import file has been updated. Similarly to the processing described in the first embodiment, if a new import file is stored (or the import file is updated), the data file management table 700 is updated.

  Step 2303: The query receiving unit 204 determines whether query rewriting is necessary for the received query. The processing performed here is the same as in step 1301, and the query receiving unit 204 records in the data file management table 700 the same table name as the table name described in the FROM clause of the received SQL query. And whether or not there is a record whose imported flag (707) is “N” (that is, it is determined whether or not there is an import file that is not imported to the table to be searched).

  Step 2304: If there is an import file that has not yet been imported to the table to be searched as a result of the processing of step 2303 (step 2304: Yes), then step 2305 is performed. On the other hand, when the import file information for the table to be searched is not registered in the data file management table 700, or the import file information for the table to be searched is registered, the import file has already been imported (imported). If the completed flag (707) is “Y”)), step 2305 is skipped.

  Step 2305: This process is the same as step 1303, and query rewriting is performed.

  Step 2306: This process is the same as step 1304, so the description thereof is omitted here.

  Step 2307: Since this process is the same as step 1305, description thereof is omitted here.

  The difference between Example 3 and Example 1 (or Example 2) will be outlined with reference to FIG. In the data processing system according to the third embodiment, the trigger for the import file to be searched is different from that in the first or second embodiment. FIG. 22 shows that the import file 32 (named “aaa.csv”) is stored in the storage device 4 and then stored in the table 300 to be imported (named “USER1.T1”). It is the conceptual diagram which showed the flow until this time and the search object in each time for every Example. In the description here, the query issued by the user is a query for searching for a record in the table “USER1.T1” (for example, the query shown in FIG. 13).

  FIG. 22A shows an example in which the import file 32 is stored in the storage device 4 from the data source 8 at the time t0, and the import to the table 300 is performed at the time t2 (t2> t0). Yes. If a query is received from the user after time t2 (for example, time t1 ′ in FIG. 22B), the data processing systems according to the first to third embodiments target the table 300 and the import file 32 as a search target. Both records in 300 and records in import file 32 are output to the user.

  On the other hand, when a query is received from a user before time t2 (when import processing is started), for example, when a query is received at time t1 (note that t1 is in a relationship of t0 <t1 <t2). The data processing system according to Example 1 or 2 sets only the table 300 as a search target, and does not search for records in the import file 32. Since the import process is performed periodically, the import process is not immediately started after the import file 32 is stored in the storage device 4. Therefore, even when the import file 32 is stored in the storage device 4, there is a time when the user cannot access the records in the import file 32.

  However, the data processing system according to the third embodiment performs the processing of Step 2301 and Step 2302 when receiving a search request, and when the import file 32 is newly stored or the import file 32 is updated. The import file 32 is made a search target by performing the query rewriting process. Therefore, the user can access the record in the import file 32 immediately after the import file 32 is stored in the storage device 4.

  So far, several embodiments have been described, but these are examples for explaining the present invention, and the scope of the present invention is not intended to be limited only to these embodiments. That is, the present invention can be implemented in various other forms.

  For example, in the data processing system according to the embodiment described above, a client is provided separately from the DB server, and the user uses an input device and an output device of the client. However, it is not essential to provide a client, and the client program may be executed by the DB server. In this case, the user may issue an information search request using the input / output device of the DB server.

  Further, the number of DB servers is not limited to one. A plurality of DB servers may be provided in the data processing system, and search processing or the like may be executed in parallel by the plurality of DB servers.

  The various programs described above may be provided by a program distribution server or a storage medium readable by a computer, and may be installed in each device that executes the program. The computer-readable storage medium is a non-transitory computer-readable medium such as a non-volatile storage medium such as an IC card, an SD card, or a DVD.

  In addition, part or all of the processing of the program described in the above embodiment may be realized by dedicated hardware.

1: Server, 2: Client, 3, 4: Storage device, 5: SAN, 6: LAN, 7: WAN, 8: Data source

Claims (15)

A server, a first storage device storing one or more tables including a plurality of columns, and a second storage device for storing an import file having data to be imported into the table,
When the server accepts the search request,
An import incomplete file that is an import file that has data to be imported into the table that is a search target in the search request and that has not been imported into the table is stored in the second storage device. Determine if it exists,
If the import incomplete file does not exist, the data specified in the search request is searched from the table,
When the import incomplete file exists, the data specified in the search request is searched from the table, and the data specified in the search request is also searched from the import incomplete file.
Data processing system. The server, when an import incomplete file having data to be imported into the table to be searched in the search request exists in the second storage device,
Generating a first partial query that searches the table for records that meet the conditions specified in the search request;
Generating a second partial query that is a query for searching for records that meet the conditions specified in the search request from the incomplete import file;
Generating a query for obtaining a union of output results of the first and second partial queries;
Execute the process related to the query;
The data processing system according to claim 1. The second partial query includes a table function for outputting data in the format of the table from the import incomplete file.
The data processing system according to claim 2. The server analyzes the generated query, generates at least a task related to the first partial query and a task related to the second partial query, and executes the generated tasks in parallel.
The data processing system according to claim 2. The server manages a data file management table for associating and recording the table name of the table and the file name and processing state of an import file having data to be imported into the table,
When the server detects that a new import file has been stored in the second storage device, the server records the file name of the import file in the data file management table, and the processing status of the import file is Record that the import is incomplete,
When the import processing of the import file is completed, record that the processing status of the import file is an import completed status in the data file management table.
The data processing system according to claim 2. When the server generates the second partial query,
Searching for the import file whose processing status is incomplete among the import files having the import target data to the table that is the search target in the search request from the data file management table. 3 partial queries,
Generating a partial query including the third partial query as the second partial query;
The data processing system according to claim 5. When the server receives a search request, the server determines whether a new import file is stored in the second storage device.
The data processing system according to claim 5. In a data processing system comprising: a first storage device that stores one or more tables including a plurality of columns; and a second storage device that stores an import file having data to be imported into the table.
The data processing system is
a) accepting a search request;
b) An import incomplete file that is an import file that has data to be imported into the table that is a search target in the search request and that has not been imported into the table is stored in the second storage. Determining whether it exists in the device;
c-1) If the incomplete import file does not exist, searching the data specified in the search request from the table;
c-2) When the import incomplete file exists, the data specified in the search request is searched from the table, and the data specified in the search request is also searched from the import incomplete file. Searching, and
The data search method of the data processing system that executes In step c-2), the data processing system
Generating a first partial query that searches the table for records that meet the conditions specified in the search request;
Generating a second partial query that is a query for searching for records that meet the conditions specified in the search request from the incomplete import file;
Generating a query for obtaining a union of output results of the first and second partial queries;
Execute the process related to the query;
The data search method of the data processing system according to claim 8. The second partial query includes a table function for outputting data in the format of the table from the import incomplete file.
The data search method of the data processing system according to claim 9. The data processing system manages a data file management table for associating and recording a table name of the table and a file name and a processing state of an import file having data to be imported into the table,
The data processing system further includes:
A) detecting that a new import file is stored in the second storage device;
B) When it is detected in step a) that a new import file has been stored, the file name of the import file is recorded in the data file management table, and the processing status of the import file is not yet imported. Recording a certain fact,
C) When the import processing of the import file is completed, recording that the processing status of the import file is an import completion status in the data file management table;
The data search method of the data processing system according to claim 9, wherein: When the data processing system generates the second partial query,
Searching for the import file whose processing status is incomplete among the import files having the import target data to the table that is the search target in the search request from the data file management table. 3 partial queries,
Generating a partial query including the third partial query as the second partial query;
The data search method of the data processing system of Claim 11. Steps a) and b) are performed between steps a) and b).
The data search method of the data processing system of Claim 11. A processor of a data processing system comprising: a first storage device storing one or more tables including a plurality of columns; and a second storage device storing an import file having data to be imported into the table In addition,
a) accepting a search request;
b) An import incomplete file that is an import file that has data to be imported into the table that is a search target in the search request and that has not been imported into the table is stored in the second storage. Determining whether it exists in the device;
c-1) If the incomplete import file does not exist, searching the data specified in the search request from the table;
c-2) When the import incomplete file exists, the data specified in the search request is searched from the table, and the data specified in the search request is also searched from the import incomplete file. Searching, and
The computer-readable storage medium which recorded the program which performs this. In step c-2), the processor
Generating a first partial query that searches the table for records that meet the conditions specified in the search request;
Generating a second partial query that is a query for searching for records that meet the conditions specified in the search request from the import incomplete file;
Generating a query for obtaining a union of output results of the first and second partial queries;
Causing a process related to the query to be executed;
The computer-readable storage medium according to claim 14, wherein a program is recorded.

Images