JP6674871B2 - Heterogeneous database management device, method and program - Google Patents

Description

  The present invention relates to a heterogeneous database management device, method, and program.

  There is a multi-DB technology as a technology for managing a plurality of different tables in a plurality of types of different databases (abbreviated as DBs). Conventionally, when a table is generated, a user explicitly specifies the type of a storage destination DB, so that data can be read and written between a plurality of DBs and a management server (sometimes called a system) (for example, , Non-Patent Document 1).

IBM (2002) Garlic: A New Flavor of Federated Query Processing for DB2

  However, when the system operator and the client are different, the system operator cannot grasp the non-unit characteristics of each data included in the table (characteristics that cannot be determined from the data alone), and the client grasps the system configuration and the DB configuration. Since it is not possible, there is a problem that it is difficult to specify the DB type of the storage destination of the data according to the non-unit characteristics of the data.

  The present invention has been made in view of the above circumstances, and a purpose thereof is to manage a heterogeneous database in which a plurality of different tables can be specified with an appropriate DB type as a storage destination even when a system operator and a client are different. An object of the present invention is to provide an apparatus, a method, and a program.

  In order to achieve the above object, an aspect of the present invention includes the following components. That is, the heterogeneous database management device includes a data characteristic analysis unit and a rule generation unit. The data characteristic analysis unit acquires a request history table including data relating to one or more requests received from the client for each table ID, and a data table including data entities for each data ID. A characteristic information table including values for the above characteristic information items is generated. The rule generation unit extracts one or more pieces of property information from the property information table, applies the pieces of property information to determination logic for selecting an optimal database as a storage destination for each table ID from the one or more pieces of information, and applies the table ID. Generates a rule table that associates the most appropriate database when distributing to each.

  According to the present invention, it is possible to provide a heterogeneous database management apparatus, method, and program that can specify an appropriate storage destination DB type for a plurality of data having different requirements even when a system operator and a client are different.

FIG. 1 is a block diagram showing a heterogeneous database management device according to an embodiment. FIG. 2 is a sequence diagram showing that the distribution information DB of FIG. 1 defines each table as preprocessing. FIG. 13 is a sequence diagram showing that a rule table indicating a distribution rule is updated. FIG. 9 is a sequence diagram showing that a rule table is updated periodically. FIG. 6 is a sequence diagram showing that a data table is made permanent. FIG. 2 is a sequence diagram showing an update of the Secondary-DB in FIG. 1. FIG. 4 is a sequence diagram showing distribution of requests. The figure which shows an example of a characteristic information table. The figure which shows an example of the determination logic. The figure which shows an example of a rule table. The figure which shows an example of a request history table.

Hereinafter, a heterogeneous database management apparatus, method, and program according to an embodiment of the present invention will be described with reference to the drawings. In the following embodiments, the same operation is performed for the parts with the same numbers, and the overlapping description will be omitted.
A heterogeneous database management device (hereinafter, referred to as a heterogeneous DB management device) of the present embodiment will be described with reference to FIG.
The heterogeneous DB management device 100 according to the present embodiment includes a distribution engine unit 101, a distribution information DB 102, a memory 103, a data characteristic analysis unit 104, a rule generation unit 105, a persistence unit 106, an adapter A107, an adapter Z108, a DB-A109, a DB -Z110. The heterogeneous DB management device of the present embodiment targets a plurality of types of databases. The heterogeneous database management device corresponds to the system described above.

The distribution engine unit 101 determines a request destination DB or memory according to the distribution rule generated by the data characteristic analysis unit 104. The distribution engine unit 101 specifies a table name from URI (Uniform Resource Identifiers) information of data included in a client request, and acquires storage destination DB information registered for each table name in a distribution rule.
When the distribution engine unit 101 receives a request for a new definition of a data table and receives a request for updating a distribution rule, the distribution engine unit 101 operates as follows. (1) Add data having a table ID corresponding to the rule table and the request history table. At that time, the Primary-DB (primary database) of the data table ID on the rule table is set in the memory. (2) When a request is received from the client, the data is read and written into a data table on the memory 103. Further, the request history table is updated. (3) When a certain amount of data is stored in the memory 103, the data characteristic analysis unit 104 is requested to perform data characteristic analysis. Note that a data table is generated each time a client data table is defined, and there are usually a plurality of data tables. The table ID is for identifying a plurality of data tables. In each data table, in order to record a plurality of data entities, a data ID specifying the data entity is associated with each data entity.
In other cases, the distribution engine unit 101 transmits a data request destination to the acquired storage destination DB. Here, in the case of a new data creation request, the Primary-DB is referred to. If the request is not a new data creation request, the Primary-DB and the Secondary-DB (secondary database) are referred to. In the case of a request for a plurality of tables, the request is decomposed for each storage destination DB and each request is executed.

  The memory 103 temporarily stores a data table and a request from the client. These data tables and requests are distributed to each database by the persistence unit 106 according to the instruction of the data characteristic analysis unit 104. Although the memory 103 is provided here for convenience, each unit in the heterogeneous DB management device 100 may have an information storage unit (buffer, memory, and the like) and store various data. Various modified versions are conceivable for storing data, and the modified versions may be adopted as long as the heterogeneous DB management device of the present embodiment functions.

  The data characteristic analysis unit 104 analyzes a single characteristic and a non-single characteristic of data for each table. The single characteristic is a characteristic of a single data, such as a size and the number of attributes. The non-simple characteristics are characteristics that cannot be known from the data alone, and include, for example, a request frequency and a condition number at the time of retrieval. The data characteristic analysis unit 104 executes the following at the time of a request from the distribution engine unit 101 or at a timing specified by a schedule. Note that the schedule may be obtained from an external file. (1) Obtain and analyze the request history of the corresponding data table from the distribution information DB 102, and generate characteristic information of this data table. (2) Request the rule generation unit 105 to generate a distribution rule using the generated characteristic information as an argument. When the distribution rule is generated, the request history regarding this data table is deleted from the distribution information DB 102, and the distribution rule is updated. (3) Finally, a request is made to the persistence unit 106 to make the data table held on the memory 103 permanent.

  DB-A109 to DB-Z110 are mutually different databases, and are respectively connected to the distribution engine unit 101 by adapters A107 to Z108. Although FIG. 1 shows only two databases, there are usually two or more databases and many databases.

  The rule generation unit 105 generates a distribution rule for distributing the storage destination DB for each table by using a determination logic for determining an optimal storage DB (any of DB-A109 to DB-Z110). The determination logic may be stored in the rule generation unit 105 or may be obtained from an external file. Before accepting a request from a client, the rule generation unit 105 defines a rule table on the distribution information DB.

  The rule generation unit 105 acquires the characteristic information of the table analyzed by the data characteristic analysis unit 104 from the data characteristic analysis unit 104, and, based on the acquired characteristic information and the determination logic, allocates a DB that stores the data of the table to a distribution rule ( (The rule table in FIG. 10 corresponds to the rule table). The distribution rule sets a Primary-DB and a Secondary-DB for each table. The Primary-DB stores data included in a table. When generating data, the Primary-DB is referred to, and when acquiring / updating / deleting / retrieving data, both the Primary-DB and the Secondary-DB are referred to. The Secondary-DB periodically confirms the presence / absence of data, and deletes the Secondary-DB from the distribution rule when the data runs out.

  The persistence unit 106 moves the substance of the data in the memory 103 to the heterogeneous DB according to the distribution rule at the timing when the generation of the distribution rule by the rule generation unit 105 or the completion of the update of the distribution rule by the distribution information DB 102. Or copy and make it permanent. The persistence unit 106 requests the distribution engine unit 101 to write data on the memory 103.

  The distribution information DB 102 stores a request history table including one or more attributes (for example, the number of times, time, total value, and number of conditions) among the types of requests (data generation, update, acquisition, deletion, and search), and characteristic information. A characteristic information table, which is a table including the information, and a rule table including a distribution rule are defined, and data is stored and accumulated in the defined tables. Note that the distribution rule table includes information in which a Primary-DB and a Secondary-DB as storage destination DBs are linked to a table name (may be indicated by a table ID).

Next, the assignment information DB 102 defining each table as pre-processing will be described with reference to FIG.
The data characteristic analysis unit 104 requests the distribution information DB 102 to create a request history table (step S201). The distribution information DB 102 defines the request history table (step S202), and notifies the data characteristic analysis unit 104 that the definition has been made. In the request history table, a table name item and one or more items indicating request contents are set. The request history table will be described later with reference to FIG.
Similarly, the data characteristic analysis unit 104 requests the distribution information DB 102 to create a characteristic information table (step S203). The distribution information DB 102 defines the characteristic information table (step S204), and notifies the data characteristic analysis unit 104 of the definition. In the characteristic information table, an item of a table name and one or more items indicating characteristic information on data are set. The characteristic information table will be described later with reference to FIG.

  Further, similarly, the data characteristic analysis unit 104 requests the definition of the rule table from the distribution information DB 102 (step S205). The distribution information DB 102 defines a rule table (step S206), and notifies the data characteristic analysis unit 104 of the definition. In the rule table, an item of a table name and items of a Primary-DB and a Secondary-DB are set. The rule table will be described later with reference to FIG.

Next, the operation of the heterogeneous DB management device 100 when a new data table is generated or when a request is transmitted to the heterogeneous DB management device 100 will be described with reference to FIG.
The distribution engine unit 101 receives a request for a new definition of the data table from the client, and performs the above-described operation (step S301). This is to define a new definition of the data table and a table ID of the data table. The data table ID of the requested data table is added to each of the rule table and the request history table, and is stored in the information DB 102 (steps S302 and S303). That fact is notified to the distribution engine unit 101 and the client (step S305). You are now ready to accept requests from clients.

  When the client sends a request to the heterogeneous DB management device 100 (step S305), the client executes a process according to the request. The distribution engine unit 101 instructs the data table including the data of the execution result to be cached in the memory 103 (step S306), and the memory 103 stores the data table (step S307).

  Thereafter, the distribution engine unit 101 instructs the distribution information DB 102 to update the request history table based on the data table stored in the memory 103 (step S308), and the distribution information DB 102 updates the request history table (step S309). . This update is an update of an item corresponding to the corresponding table ID. The fact is transmitted to the client (step S310). The distribution engine unit 101 requests the data characteristic analysis unit 104 to perform data characteristic analysis (step S311). This request is made, for example, when the amount of memory stored in the memory 103 exceeds a certain value.

  The data characteristic analysis unit 104 acquires the updated request history table from the distribution information DB 102, performs data analysis on the corresponding table ID (there may be a plurality of table IDs), and generates a characteristic information table (step S313). In this way, it is possible to obtain the characteristic information table by analyzing the non-unit characteristics of the data, and to specify the type of the storage destination DB for the data having the non-unit characteristics. The generated property information table is acquired by the rule generation unit 105, information is extracted from the property information table, and a decision logic (described with reference to FIG. 9) is applied to make a Primary-DB corresponding to the corresponding table ID. A database name and a database name to be a Secondary-DB are calculated, and item contents corresponding to the corresponding table ID of the rule table are generated. In other words, as a result of the determination logic, a combination of the table name and the storage destination DB (Primary-DB or Secondary-DB) is held as a distribution rule. Therefore, according to this rule table, it is possible to generate a distribution rule in consideration of the characteristics of DBs of different types.

  The rule generation unit 105 notifies the data characteristic analysis unit 104 and the distribution information DB 102 that the rule table has been created (step S315). Since the distribution rule of the corresponding table ID of interest has been generated, the distribution information DB 102 deletes the request history table updated in step S309 because it is unnecessary (step S316). The distribution information DB 102 updates the rule table by adding the item contents corresponding to the corresponding table ID of the rule table generated in step S314 to the rule table (corresponding to adding a row in FIG. 10). (Step S317). By automatically generating a rule table as a distribution rule by the heterogeneous DB management device, it is assumed that the client can grasp or specify the storage destination DB for data having non-single characteristics. There is no need to perform design work for

  It notifies the data characteristic analysis unit 104 and the distribution engine unit 101 that the update of the rule table has been completed for the corresponding rule ID in step S309.

Next, updating the rule table by applying the determination logic will be described with reference to FIG. The determination logic includes, for example, a setting to be executed only once after the data table is generated, and a setting to be executed a plurality of times periodically. A case in which the determination logic is periodically executed a plurality of times will be described.
The data characteristic analysis unit 104 requests the distribution information DB 102 for periodic analysis (step S401). This request may be an analysis for a specific plurality of table IDs or an analysis for one table ID. That is, it is possible to analyze one or more table IDs. The distribution information DB 102 receives the update of the rule table (step S402), and notifies the data characteristic analysis unit 104 of that.

  The allocation destination of the table ID to be analyzed is temporarily set to the memory 103. The process starts from step S305 in FIG. 3 for the target table ID. Finally, in step S317, item contents (determination result) indicating the storage destination DB corresponding to the corresponding table ID in the rule table can be obtained. When the result of this determination is different from the previous determination result and the storage destination DB, the storage destination DB of the previous determination result is defined as Secondary-DB, and the latest determination result is defined as Primary-DB. In the case where the determination logic is set to be executed only once or when the determination logic is executed a plurality of times but the determination results match, the value of the Secondary-DB does not exist.

Next, persistence in a database corresponding to each data table stored in the memory 103 will be described with reference to FIG.
After the rule table in FIG. 3 is updated, a notification to that effect is transmitted from the distribution information DB 102 to the data characteristic analysis unit 104, and in response to this, the data characteristic analysis unit 104 enters a process of data persistence and makes the data permanent. The processing for perpetuating the data stored in the memory 103 to the unit 106 is started (step S501). The perpetuation unit 106 acquires the data in the memory 103 (Step S502). Based on the rule table obtained in the processing of FIG. 3, the data entity corresponding to each data table in the memory 103 is moved from the DB-A109 to one of the databases of the DB-Z110 (steps S504 and 506). ) Stored there (permanence: steps S505 and S507), and return to the persistence unit 106 that the data has been made permanent. If there are a plurality of DB-A109 to DB-Z110, there is an arbitrary number and each specification is also arbitrary, but it is assumed that the specifications corresponding to the items of the characteristic information table are different in each database. In other words, any database specification may be used so that the selection logic is diversified in the determination logic of FIG.

Next, updating of the Secondary-DB will be described with reference to FIG.
The rule generation unit 105 acquires a distribution rule from the distribution information DB 102 (Step S601). The rule generation unit 105 specifies a database, which is the Secondary-DB, based on the acquired distribution rule, and extracts the database name (step S602). The rule generation unit 105 searches whether a data entity is stored in each of the extracted databases, which is the Secondary-DB (step S603). Thereafter, the rule generation unit 105 determines whether data is stored in each of the extracted Secondary-DBs, and determines whether to update the data (step S604). In this update determination, the DB in which no data is stored deletes the description from the rule table (distribution rule), while the DB in which at least one data is stored is left as it is in the rule table. After that, the rule generation unit 105 transmits the result of the update determination to the distribution information DB 102, and the distribution information DB 102 updates the rule table (Step S606).

Here, how the request is distributed from the transmission of the request before the request is executed will be described with reference to FIG. Here, a case is assumed where a rule table (that is, a distribution rule) has already been determined.
When the client transmits the request and the distribution engine unit 101 receives the request, the distribution engine unit 101 specifies the table name from the URI information of the data included in the request, and stores a rule table stored in the distribution information DB 102 (distribution rules are described). Then, the storage destination DB information registered for each table name is acquired (step S702). Thereafter, when the distribution destination is the DB-A 109, it is transmitted to the DB-A 109 (step S703), and the request is executed by the DB-A 109 (step S704). On the other hand, if the distribution destination is DB-Z110, the data is transmitted to DB-Z110 (step S705), and the request is executed in DB-Z110 (step S706).

Here, the characteristic information table will be described with reference to FIG. 8, which is a specific example.
The characteristic information table is created by the data characteristic analysis unit 104 from the data table stored in the memory 103 and the request history table stored and updated in the distribution engine unit 101. The request history table, which will be described in detail later with reference to FIG. 11, stores the following request statistical information for each table ID. The statistical information of this request includes, for example, the number of data generations, the latest and oldest data generation request reception times, the number of data updates, the latest and oldest data update request reception times, the number of data acquisitions, It includes the reception time of the oldest data acquisition request, the generation time of the deleted data, the deletion time of the deleted data, the number of data searches, and the number of search query conditions (list form).

The characteristic information table is configured with items expressing the single characteristics and the non-single characteristics of the data stored in the table. For example, one or more of the following (1) to ( 7 ) are included. (1) Total generation frequency of data in the table (for example, 10 (times / hour)): The number of data generations and the latest / oldest generation time are acquired from the request history table, and the generation frequency is calculated. (2) Total update frequency of data in the table (for example, 1 (times / hour)): The update frequency and the latest / oldest update time of the data are acquired from the request history table, and the update frequency is calculated. (3) Total acquisition frequency of data in the table (for example, 10 (times / hour)): The number of data acquisitions and the latest / oldest acquisition time are acquired from the request history table, and the generation frequency is calculated. (4) Average survival time of data in the table (for example, 148 (hours)): The total survival time and the number of deletions are acquired from the request history table, and the average value of the survival time is calculated. (5) Average size of data in the table (for example, 1000 (KB)): The data size of each data is obtained from the data table. Calculate the average value. (6) Average number of search queries in the table: The total number of search conditions and the number of searches are obtained from the request history table, and the average value of the search conditions is calculated. (7) Average number of attributes of data in the table: The number of attributes of each data is obtained from the data table. An average value is calculated (for example, 10 (attribute)).

Next, determination logic for the rule generation unit 105 to generate a distribution rule (rule table) will be described with reference to FIG.
The determination logic is logic for obtaining a rule table from the characteristic information table generated and analyzed by the data characteristic analysis unit 104 in step S312 using one or more characteristic information items. One example of the logic is shown in FIG. In the example of FIG. 9, first, the data is divided by the size of the average data size, and it is determined whether the average data size is smaller than a first threshold value (here, 1000 [KB]) (step S901). If it is determined in step S901 that the average data size is smaller than the first threshold, it is determined whether the total data generation frequency is higher than the total update frequency (step S902), and in step S901 the average data size is smaller than the first update frequency. If it is determined that the average data size is not smaller than the first threshold, it is determined whether the average data size is smaller than the second threshold (8000 [KB]) (step S903).

  If the total data generation frequency is higher than the total update frequency in step S902, it is determined whether the total data generation frequency is higher than the third threshold (step S904). If it is not larger than the total update frequency, it is determined whether the data service frequency is a predetermined value (X) (step S905).

  If the total data generation frequency is higher than the third threshold value in step S904, the database A is used. If the total data generation frequency is not higher than the third threshold value in step S904, the database B is used. If the data service frequency is a predetermined value in step S905, the database B is used. If the data service frequency is not the predetermined value in step S905, the database C is used. Further, if the average data size is smaller than the second threshold value in step S903, the database D is used. If the average data size is not smaller than the second threshold value in step S903, the database E is used.

  By applying the characteristic information table of the table ID corresponding to this determination logic, the rule generation unit 105 can determine the Primary-DB corresponding to this table ID, and if this determination logic is applied for each table ID, , A rule table shown in FIG. 10 can be created. FIG. 10 is a rule table showing distribution rules obtained by the rule generation unit 105. In the example of FIG. 10, when the table names (table IDs) are "userTable001", "userTable002", "userTable003", and "userTable004", the Primary-DBs are "A", "B", "C", and "D", respectively, and the Secondary-DB This indicates that the DB is “none”, “none”, “none”, and “A, B”.

Next, the request history table will be described with reference to FIG.
The request history table includes at least information of (1) a data generation request, (2) a data update request, (3) a data acquisition request, (4) a data deletion request, and (5) a data search request. Hold one or more.

  (1) When a data generation request is received, the following items are updated. 1. Number of times data was generated (times). +1 is added to the number of generations stored in the rule table. 2. Latest / oldest generation time (time). When the number of times of data generation is 0, the latest / oldest generation time is updated. When the number of times of data generation is not 0, the latest generation time is updated. 3. Data generation time ({ID: time}). A set of data ID and data generation time is added. The generation time may be recorded on the data table.

  (2) Update the following items when a data update request is received. 1. Number of data updates (times). +1 is added to the number of updates stored in the rule table. 2. Latest / oldest update time (time). When the data update count is 0, the latest / oldest update time is updated. When the data update count is not 0, the latest update time is updated.

  (3) When a data acquisition request is received, the following items are updated. 1. Number of times data was acquired (times). +1 is added to the number of acquisitions stored in the rule table. 2. Latest / oldest acquisition time (time). When the number of times of data acquisition is 0, the latest / oldest acquisition time is updated. When the number of times of data acquisition is not 0, the latest acquisition time is updated.

  (4) When a data deletion request is received, the following items are updated. 1. Number of deletes (times). Add +1 to the number of deletions stored in the rule table. 2. Total value of survival time (hours). Add the lifetime to be deleted to the lifetime stored in the rule table. The lifetime is calculated by subtracting the data generation time and the deletion request reception time. 3. Data generation time (time). Data ID / generation time information matching the ID of the deleted data from the data generation time stored in the rule table is deleted.

  (5) When a data search request is received, the following items are updated. 1. Number of searches (times). Add +1 to the number of generations stored in the rule table. 2. Sum of the number of search conditions (number). The number of attributes of data included in the generation request is added to the number of attributes stored in the rule table.

  FIG. 11 shows a specific example including the above information.

According to different database management apparatus according to the above embodiment, the size and separate from the "independent characteristics" of the data itself, such as the number of attributes, not adversely or al from the data itself, such as the query frequency and search method "Non independent characteristics Even if the data has "", the rule table determines the database to store different types for each data ID, so even if the client and the system operator who is familiar with the database configuration are different, You can specify an appropriate storage destination database. Therefore, even if the client does not know the configuration of the database, it can specify an appropriate database as a storage destination even with a request having non-simple characteristics from the client.

  Note that the present invention is not limited to the above-described embodiment as it is, and can be embodied by modifying constituent elements in an implementation stage without departing from the scope of the invention. Various inventions can be formed by appropriately combining a plurality of constituent elements disclosed in the above embodiments. For example, some components may be deleted from all the components shown in the embodiment. Further, components of different embodiments may be appropriately combined.

100: Heterogeneous DB management device, 101: Assignment engine unit, 102: Assignment information DB, 103: Memory, 104: Data characteristic analysis unit, 105: Rule generation unit, 106: Permanence unit, 107: Adapter A, 108: Adapter Z, 109 ... Database A, 110 ... Database B.

Claims (8)

The present apparatus and inclusive request history table data for one or more requests the system configuration received from different clients for each table ID, and data entities I containing, acquires the data table designated by the table ID, each table ID from the data table, the independent characteristics obtained from the data itself, the data characterization of generating characteristic information table that includes one or more properties information of characteristic information including the non-independent characteristics can not be obtained from the data itself Department and
One or more characteristic information is extracted from the characteristic information table, and the characteristic information is applied to a determination logic for selecting an optimal database as a storage destination for each table ID from the one or more characteristic information, and is assigned to each table ID. A heterogeneous database management apparatus comprising:   The heterogeneous database management device according to claim 1, wherein the rule table sets a primary database and a secondary database for each table ID.   3. The heterogeneous database management device according to claim 1, further comprising a persistence unit that writes a substance of temporarily stored data to a corresponding database in accordance with the rule table. 4.   Obtain the rule table, specify the table ID from the URI information of the data included in the request, send the request to the storage destination database of the table ID according to the rule table, and in the case of a new creation request, set the primary database as the storage destination The heterogeneous database management device according to any one of claims 1 to 3, wherein a primary database and a secondary database are stored as storage destinations for requests other than newly created requests. The data characteristic analysis section, a heterogeneous database management apparatus is the apparatus and inclusive request history table data for one or more requests the system configuration received from different clients in each table ID, it contains do the data entities, the table obtains the data table specified by the ID, from this data table for each table ID, a single characteristic obtained from the data itself, one or more characteristics information of characteristic information including the non-independent characteristics can not be obtained from the data itself and generating characteristic information table containing,
The rule generation unit extracts one or more pieces of property information from the property information table, and applies the pieces of property information to a determination logic that selects an optimal database as a storage destination for each table ID from the one or more pieces of property information, Generating a rule table for associating an optimal database when sorting by table ID. 6. The heterogeneous database management method according to claim 5, further comprising a step of writing the entity of the temporarily stored data to a corresponding database by the persistence unit in accordance with the rule table.   Obtain the rule table, specify the table ID from the URI information of the data included in the request, send the request to the storage destination database of the table ID according to the rule table, and in the case of a new creation request, set the primary database as the storage destination 7. The heterogeneous database management method according to claim 5, wherein a primary database and a secondary database are stored in the case of a request other than a new request.   A program for causing a computer to execute each step of the heterogeneous database management method according to any one of claims 5 to 7.