JPWO2018047250A1 - Database transition support apparatus and method - Google Patents

Abstract

The database migration support device is a device that supports creation of a distributed key-value store migrated from a relational database, and has a memory and a processor. The memory has a key evaluation program and a key evaluation presentation program. The key evaluation program generates at least one key candidate which is a candidate of a key to be used for the distributed key-value store based on at least one column set in the relational database, and generates the key candidate into data of the relational database and usage log. Evaluate based on. The key evaluation presentation program presents evaluation results of key candidates. The processor executes the key evaluation program and the key evaluation presentation program to present the evaluation result of the key candidate.

Description

  The present invention relates to a technology that supports migration of a database from a relational database to a key-value store.

  With the rapid growth of the IoT market, etc., there is a movement to shift databases from relational databases (RDB) to key value stores (KVS) in order to improve data capacity and processing performance. In database migration, it is required that applications that were operating before migration also operate in the same way after migration. However, in order to realize the KVS that can acquire the same data as the data that could be acquired from the RDB before migration, it was necessary to spend a lot of man-hours and manually design the key. On the other hand, Patent Document 1 discloses a technology that supports transition design to facilitate transition from RDB to KVS.

JP, 2014-211790, A

  When supporting the transition from RDB to KVS, the technology of Patent Document 1 only evaluates data items using the schema definition of RDB and the usage log of RDB, so when KVS after transition is applied to actual data It is not always possible to obtain the same data as the RDB before migration.

  An object of the present invention is to provide a technology capable of supporting KVS key design such that data similar to RDB can be acquired when actual data of RDB before migration is applied.

  A database migration support apparatus according to one embodiment of the present invention is an apparatus for supporting creation of a distributed key-value store migrated from a relational database, and has a memory and a processor. The memory has a key evaluation program and a key evaluation presentation program. The key evaluation program generates at least one key candidate which is a candidate of a key to be used for the distributed key-value store based on at least one column set in the relational database, and generates the key candidate into data of the relational database and usage log. Evaluate based on. The key evaluation presentation program presents evaluation results of key candidates. The processor executes the key evaluation program and the key evaluation presentation program to present the evaluation result of the key candidate.

  According to the present invention, since the results of evaluating each key candidate using data before migration and the usage log are presented, the effect of migration is evaluated using actual data, and the key of the appropriate distributed key-value store It is possible to create

It is a block diagram of the database migration system by this embodiment. It is a figure which shows the information which the program of the key design support apparatus 100 references or inputs / outputs. It is a sequence diagram which shows the whole operation | movement of database migration in the database migration system by this embodiment. It is a figure which shows an example of the vehicle information table schema definition 204. FIG. It is a figure which shows an example of the vehicle information table 203. As shown in FIG. It is a figure which shows an example of the utilization log 202. As shown in FIG. 7 is a diagram illustrating an example of KVS information 302. FIG. It is a flowchart of migration client determination processing S103. It is a flowchart of key evaluation process S107. It is a flowchart of a key candidate set creation process. It is a flowchart of simulation table preparation processing. It is a table showing an example of a key candidate used for explanation of simulation table preparation processing. It is a table showing an example of RDB table used for explanation of simulation table creation processing. It is a flowchart of a query simulation process. It is a flowchart of a score calculation process. It is a flowchart of a key evaluation presentation process. It is a figure which shows the example of the screen display by a key evaluation presentation process. It is a figure which shows the example of the screen display by a key evaluation presentation process. It is a figure which shows the example of the screen display by a key evaluation presentation process. It is a figure which shows the example of the screen display by a key evaluation presentation process. It is a sequence diagram of data migration processing. It is a figure which shows the example of the screen display by data transfer processing. It is a figure which shows the example of the screen display by data transfer processing. It is a table showing an example of calculation of the degree of sequentiality and the degree of dispersion.

  Hereinafter, embodiments of the present invention will be described with reference to the drawings.

  FIG. 1 is a block diagram of a database migration system according to the present embodiment. The hardware configuration is mainly shown in FIG. FIG. 2 is a diagram showing information that the program of the key design support device 100 refers to or inputs and outputs.

  Referring to FIG. 1, the database migration system includes a key design support apparatus 100, a relational database (RDB) 200, a distributed key value store (KVS) 300, and clients 401, 402, 403, and 404.

  The key design support apparatus 100 is an apparatus for supporting key design in the transition from the RDB 200 to the distributed KVS 300. The key design support device 100 includes an output device 110, an arithmetic device 120, an input device 130, a communication device 140, and a storage unit 150.

  The storage unit 150 has a program (PG) area 151 and a data area 152. In the PG area 151, a migration client determination PG 1511, a key evaluation PG 1512, a key evaluation presentation PG 1513, a data migration PG 1514, a key candidate set creation module 1515, a KVS table creation module 1516, a query simulation module 1517, and a score calculation module 1518 is stored.

  In the data area 152, RDB connection information 1521, migration countermeasure client information 1522, RDB utilization log 1523, key candidate set 1524, query aggregation 1525, RDB table 1526, simulation table 1527, key evaluation result 1528, and migration key information 1529 Is stored.

  The migration client determination PG 1511 determines a client to migrate the database from RDB to distributed KVS. As shown in FIG. 2, the migration client determination PG 1511 can refer to the usage log 202 of the RDB 200. For example, the migration client determination PG 1511 extracts the client using the RDB 200 from the usage log 202, and presents the client list to the designer through the output device 110. The migration client determination PG 1511 may decide a client for migrating the database from the RDB to the distributed KVS based on the information input from the input device 130 by the designer who has viewed the client list.

  The key evaluation PG 1512 evaluates each of the key candidates included in the key candidate set 1524 and gives a score. A score calculation module 1518 is used to calculate the score. The evaluation method will be described later. As shown in FIG. 2, the key evaluation PG 1512 can refer to the usage log 202 of the RDB 200, the vehicle information table 203, the vehicle information table schema definition 204, and the KVS information 302 of the distributed KVS 300. The key evaluation PG 1512 calculates the score of each key candidate using the referred information. The key evaluation result is accumulated in the key evaluation result 1528. Also, as shown in FIG. 2, the result of the key evaluation is presented to the designer from the output device 110 via the key evaluation presentation PG 1516.

  The key evaluation presentation PG 1513 presents the result evaluated by the key evaluation PG 1512 to the designer via the output device 110, for example. The designer looks at the key evaluation result, determines the migration key to be used for the distributed KVS after migration, and records it in the migration key information 1529. As shown in FIG. 2, the key evaluation presentation PG 1516 can obtain information on key selection input by the designer from the input device 130 and notify the data migration PG 1514.

  Further, since the key evaluation PG 1512 creates a simulation table of each key candidate in the evaluation process, when the migration key is determined, the simulation table of the migration key is notified to the data migration PG 1514.

  Data migration PG 1514 performs migration of data from RDB to distributed KVS. Specifically, in data migration, a key based on information of a predetermined column of RDB data stored in RDB table 1526 is associated with a data value and recorded in a KVS table (not shown). , Generate migration data. At that time, the data key (Key) and the data value (Value) can be acquired from the migration key simulation table. The data migration PG 1514 notifies the distributed KVS 300 of the created migration data.

  The key candidate set creation module 1515 generates a plurality of key candidates for the distributed KVS 300 after migration based on the information of the column of the RDB 200 before migration, and stores the plurality of candidates in the key candidate set 1524.

  The KVS table creation module 1516 creates a KVS table for storing data and keys in the distributed KVS 300 after migration.

  The query simulation module 1517 executes the simulation of the distributed KVS 300 using each key candidate, using the query obtained from the usage log of the RDB 200.

  The score calculation module 1518 calculates the score of each key candidate. The method of calculating the score will be described later.

  The RDB connection information 1521 is information for the client to connect to the RDB. For example, it is a URL for connecting to RDB.

  The migration target client information 1522 is information indicating a client that migrates the database from the RDB 200 to the distributed KVS 300.

  The RDB usage log 1523 is information in which the usage history of the RDB 200 before migration is accumulated. The RDB usage log 1523 includes a query for accessing the RDB 200 and a search result of the RDB 200.

  The key candidate set 1524 is information including a plurality of key candidates created by the key candidate set creation module 1515.

  The query set 1525 is information obtained by accumulating queries when the client uses the RDB. A query is extracted from the RDB usage log 1523 and stored in the query set 1525.

  The simulation table 1527 is a simulation table for simulating the distributed KVS by the key candidate or the distributed KVS by the migration key based on the query included in the query set 1525.

  The key evaluation result 1528 is information on the result of the key evaluation PG 1512 evaluating the key candidate.

  The migration key information 1529 is information of a key used when migrating from the RDB 200 to the distributed KVS 300. For example, the designer selects the migration key based on the evaluation result of the key candidate. Information of the selected migration key is stored in migration key information 1512.

  The RDB 200 includes a communication device 201, a usage log 202, a vehicle information table 203, a vehicle information table schema definition 204, a table 2 205, and a schema definition 2 206.

  The communication device 201 is a communication device for the RDB 200 to communicate with the key design support device 100 and the clients 401 and 402 via the communication network.

  The usage log 202 is history information accumulated by usage of the RDB 200 by the clients 401 and 402. The usage log 202 is acquired by the RDB 200 and notified to the key design support device 100. The key design support device 100 stores the RDB usage log 1523 in the data area 152.

  The RDB 200 is provided with a relational database that manages information on the current position and traveling speed of the vehicle in real time. The vehicle information table 203 is a table of a database in which information of vehicles is accumulated.

  The vehicle information table schema definition 204 is information that defines the structure of the vehicle information table 203.

  Table 2 205 is a table of another database, and schema definition 2 206 is information defining the structure of the other database.

  The distributed KVS 300 has a communication device 301 and stores KVS information 302. The migration data notified from the data migration PG 1514 is distributed to a plurality of nodes 303, 304, 305. The KVS information 302 is setting information regarding the configuration and operation of the distributed KVS 300. Here, as shown in FIG. 1 as an example, the database of the distributed KVS 300 is distributed to three nodes 303, 304, and 305.

  The communication device 301 is a communication device for the distributed KVS 300 to communicate with the key design support device 100, the RDB 200, and the clients 403 and 404 via the communication network.

  The KVS information 302 is setting information on the distributed KVS. The setting information includes information such as the number of nodes, the region size, and the presence or absence of automatic sharding.

  The clients 401 and 402 are clients that use the RDB 200 database. In the client 401, the application 1 is operating, and in the client 402, the applications 2a and 2b are operating.

  The clients 403 and 404 are clients using the distributed KVS 300 database.

  An outline of the configuration and operation of the key design support device 100 according to the present embodiment will be described.

  The key design support device 100 is a database migration support device that supports creation of a distributed key-value store (distributed KVS 300) migrated from a relational database (RDB 200), and includes a memory (storage unit 150) and a processor (arithmetic unit 120). And. The memory has a key evaluation program (key evaluation PG 1512) and a key evaluation presentation program (key evaluation presentation PG 1513).

  The key evaluation program generates at least one key candidate to be a candidate for the key to be used for the distributed key-value store based on at least one column set in the relational database, and data and utilization of the relational database for each key candidate. Evaluate each of the key candidates based on the logs. The key evaluation presentation program presents the evaluation result of the key candidate to the designer by screen display or the like.

  According to this, since the result of evaluating each key candidate is presented using the data before migration and the usage log, the effect of the migration is evaluated using actual data, so the key of the appropriate key-value store It can be easily created.

  In addition, the key evaluation program generates at least one key candidate to be a key candidate to be used for the distributed key-value store based on at least one column set in the relational database, and uses the relational database for each key candidate. Calculate the degree of appearance of the column that the key candidate is based on in the log and the access performance of the data distributed to multiple nodes of the distributed key value store, and based on the degree of conformance and the access performance Evaluate each of the key candidates.

  According to this, since a score based on the degree of acquisition of data similar to that before migration and the access performance to data after migration is presented after migration, the degree of fitness before and after migration is data It becomes possible to easily create an appropriate key-value store based on the evaluation in consideration of the access performance to the access.

  In addition, the key evaluation program calculates a score including the product of the matching degree and the access performance for each of the key candidates, and the key evaluation presentation program presents the designer with the score of each of the key candidates. According to this, it is possible to easily compare a plurality of key candidates because one score can be evaluated in consideration of the matching degree and the access performance.

  Further, the access performance includes the degree of sequential showing a higher value as the number of scans for the node is smaller in access to the database after migration of simulation based on usage log. Since key candidates with a small number of scans are highly evaluated, it is possible to support selection of key candidates that are likely to be accessed efficiently.

  In addition, the access performance further includes a degree of dispersion indicating a higher value as the scan is evenly distributed to the nodes in access to the database after migration of the simulation based on the usage log. The high score of the key candidates that the scan is evenly distributed to a plurality of nodes can support the selection of key candidates that are likely to be efficiently accessed to the distributed KVS.

  Also, the key evaluation program can change the weight of the degree of sequentiality and the degree of dispersion in the score by the operation of the user (designer). According to this, since the user can change the relevance and the weight of the access performance, it is possible to appropriately select which one is more important for key design.

  In addition, the key evaluation program selects predetermined ones from among the columns set in the relational database from the ones with the highest frequency of appearance by the query extracted from the usage log, and among these columns or combinations of those columns, Extract the data uniquely identifying the data and the primary key as key candidates. Since unique key candidates are created from a column with a large number of appearances in the usage log or a combination thereof, it is possible to create key candidates with high possibility of acquiring data as in the case before migration.

  In addition, the key evaluation program narrows down key candidates based on the degree of matching, performs simulation on the narrowed key candidates, and calculates access performance. Since the simulation is performed after narrowing down the key candidates, the processing amount of simulation can be reduced.

  Also, the key evaluation program allows the user to select a client to be migrated from the related database to the distributed key value store, and uses the usage log of the selected client for evaluation of the key candidate. The user interface for selecting a client allows the desired client to be selected and migrated from the relevant database to the distributed key-value store.

  Also, the key evaluation presentation program displays on the screen the evaluation result of any one key candidate and a button for determining the key candidate as a transition key. When the button is operated, the data migration program creates a distributed key-value store using the key candidate as a migration key. Since the key candidate and the evaluation result are presented to cause the user to determine the transition key, it is possible to assist in determining the key candidate and the transition key from which desired performance can be obtained.

  A more detailed description will be given below.

  FIG. 3 is a sequence diagram showing the overall operation of database migration in the database migration system according to the present embodiment.

  Referring to FIG. 3, first, a designer inputs RDB connection information (step S101). Subsequently, the migration client determination PG 1511 acquires client information of the usage log from the RDB 200 (step S102). Subsequently, the migration client determination PG 1511 executes migration client determination processing (step S103). Details of the migration client determination process will be described later.

  In the migration client determination process, the migration client determination PG 1511 presents the client list to the designer (step S104). When the designer inputs information of the migration client (step S105), the migration client determination PG 1511 determines the migration client and notifies migration client information to the key evaluation PG 1512 (step S106).

  The key evaluation PG 1512 that has received the migration client information executes key evaluation processing (step S107). Details of the key evaluation process will be described later. In the key evaluation process, the key evaluation PG 1512 acquires the usage log and schema definition table from the RDB 200 (step S108), generates key candidates based on the acquired information, and evaluates each key candidate, and the evaluation result Is notified to the key evaluation presentation PG 1513 (step S109).

  The key evaluation presentation PG 1513 that has received the key evaluation result executes key evaluation presentation processing (step S110). Details of the key evaluation presentation process will be described later. In the key evaluation presentation process, the key evaluation presentation PG 1513 presents the key evaluation result to the designer (step S111). When the designer who sees the evaluation result determines the migration key (step S112), the key evaluation PG 1512 notifies the data migration PG 1514 of key information indicating the migration key (step S113).

  The data migration PG 1514 that has received the key information executes data migration processing (step S114). Details of the data migration process will be described later. In the data migration process, the data migration PG 1514 acquires setting information from the designer (step S115) and causes the distributed KVS 300 to create the KVS table using the migration key and the setting information (step S115), and the key evaluation PG 1512 The simulation table of the migration key is acquired from (step S116), the distributed KVS 300 is made to execute data migration (step S117), and when data migration is completed, the designer is presented with the completion of data migration (step S118).

  The migration client determination processing S103, the key evaluation processing S107, the key evaluation presentation processing S110, and the data migration processing S114 will be described in detail below.

  FIG. 4A is a diagram showing an example of the vehicle information table schema definition 204. As shown in FIG. FIG. 4B is a diagram showing an example of the vehicle information table 203. As shown in FIG. FIG. 4C shows an example of the usage log 202. As shown in FIG. As an example, it is assumed that the RDB 200 stores a vehicle information table schema definition 204 shown in FIG. 4A, a vehicle information table 203 shown in FIG. 4B, and a usage log 202 shown in FIG. 4C.

  FIG. 5 shows an example of the KVS information 302. As shown in FIG. It is assumed that KVS information 302 shown in FIG. 5 is set in the distributed KVS 300.

  FIG. 6 is a flowchart of the migration client determination processing S103.

  In the migration client determination processing S103, the migration client determination PG 1511 first receives RDB connection information from the designer and stores it in the storage unit 150 (step S201).

  Subsequently, the migration client determination PG 1511 acquires client information from the usage log 202 of the RDB 200 (step S202). In the usage log 202, as illustrated in FIG. 4C, a history of queries in which the client uses the RDB 200 is accumulated. For each query, the client 2021 that requested the query, the query ID 2022, the query 2023, the search result 2024 of the RDB 200, and the number (the number of results) 2025 of data extracted from the RDB 200 as the search result are stored in association ing. The migration client determination PG 1511 acquires information of clients appearing in the client 2021 from the usage log 202, and creates a list of client information (step S202). Next, the migration client determination PG 1511 displays a list of client information on the screen of the output device 110 (step S203).

  FIG. 6 shows a screen display D203 of client information. Referring to the screen display D203, a check box for setting whether or not to be a migration target in association with the client name is displayed.

  Next, when the designer performs a click operation, the migration client determination PG 1511 displays a check in the check box in which the click operation has been performed (step S204). Clients for which the designer has checked the check box are subject to migration. In the example of FIG. 6, the client 1 and the client 2 are migration targets.

  Subsequently, when detecting that the execution button of the screen display D203 has been clicked (step S205), the transition client determination PG 1511 deletes the screen display D203 of the client information from the screen of the output device 110 (step S206). Information on the target client (migration client) is stored in the storage unit 150 (step S207).

  FIG. 7 is a flowchart of the key evaluation process S107.

  First, the key evaluation PG 1512 executes a key candidate set creation process (step S301) to generate a plurality of key candidates. Details of the key candidate set creation process S301 will be described later. When the process of step S301 ends, the usage log, the RDB query set, and the key candidate set are stored in the data area 152 illustrated in FIG. 7.

  Next, the key evaluation PG 1512 repeats the processing of step S302 to step S304 for all key candidates. First, the key evaluation PG 1512 executes a simulation table creation process (step S302) to create a simulation table 1527. Details of the simulation table creation process will be described later. When the process of step S302 ends, the RDB table, the simulation table, and the KVS information are stored in the data area 152 illustrated in FIG. 7.

  Next, the key evaluation PG 1512 executes a query simulation execution process (step S303) to calculate a query simulation result. The query simulation results are used to evaluate key candidates. Details of the query simulation execution process will be described later. When the process of step S303 is completed, the simulation result is stored in the data area 152 shown in FIG. The simulation results include the number of acquired records and the number of scans.

  Next, the key evaluation PG 1512 executes a score calculation process (step S304) to calculate the score of each key candidate. A score which is an evaluation result of each key candidate is obtained by the score calculation process. The score of each key candidate is used to determine the key to be used for the post-transition variance KVS. Details of the score calculation process will be described later. When the process of step S304 ends, the key evaluation result is stored in the data area 152 shown in FIG.

  Finally, the key evaluation PG 1512 outputs the key evaluation result (step S305). An example D305 of the key evaluation result is shown in FIG. The key evaluation results include the evaluation results of KeyA and KeyB. For example, KeyA has an average degree of sequentiality of 1.5 and an average degree of dispersion of 38.

  FIG. 8 is a flowchart of key candidate set creation processing.

  First, the key evaluation PG 1512 acquires information on the migration client that is the client targeted for migration (step S401). Next, the key evaluation PG 1512 receives the input of the use log 202 about the use of the RDB 200 by the migration client, and stores it in the storage unit 150 (step S402). Next, the key evaluation PG 1512 acquires a query from the usage log 202 of the storage unit 150, creates a query set, and stores it in the storage unit 150 (step S403).

  Next, the key evaluation PG 1512 counts the number of appearances of each of the columns (RDB column) of the RDB 200 that appear in the WHERE clause in the query of the query set (step S404). FIG. 8 shows a table D404 indicating the number of occurrences of each RDB column. Table D404 describes, for each RDB column, the number of occurrences and a flag indicating whether the RDB column is a primary key. For example, the number of occurrences of the RDB string "Time" is the largest 20 times.

  Next, the key evaluation PG 1512 selects a predetermined number of RDB columns in descending order of the number of appearances (step S405). Although three RDB columns are selected here, the number is not limited to three. FIG. 8 shows a table D405 showing three RDB columns for which the number of applications is high. In Table D405, an RDB column "Time", an RDB column "Car ID", and an RDB column "Speed" are shown.

  Next, the key evaluation PG 1512 extracts the schema definition related to the table appearing in the FROM clause of the query from the RDB 200, and adds the primary key of the schema definition as an RDB column to the table D 405 created in step S 405 Step S406). Also, at this time, it is conceivable that the primary key is not filed in the WHERE clause, but here, for convenience, applications of other RDB columns (upper three RDB columns) as the number of applications in the WHERE clause of the primary key I will set the average value of the number of times. Here, Table D406 is created. In the example of FIG. 8, since the number of applications for Time is 20, the number of applications for CarID is 10, and the number of applications for Speed is 5, (20 + 10 + 5) / 3 ≒ 12 is set. However, adopting the average value of the number of occurrences of other RDB columns is an example, and the present invention is not limited to this.

  Next, the key evaluation PG 1512 registers a unique RDB column or a list of RDB columns that uniquely identifies data, and a combination of an RDB or RDB column that does not uniquely identify data. A unique RDB column list is created, and the primary key in Table D406 is added to the unique RDB column list (steps S407 and S415).

  Furthermore, the key evaluation PG 1512 performs a series of processes (steps S408 to S415) of extracting a combination of RDB sequences that uniquely identifies data from the RDB sequence.

  First, the key evaluation PG 1512 adds an RDB column other than the primary key of the table D 406 to the non-unique RDB column list (step S 408). Subsequently, the key evaluation PG 1512 repeats the following processing (steps S410 to S412) on the RDB sequence in the non-unique RDB sequence list.

  First, the key evaluation PG 1512 deletes the target RDB column from the non-unique RDB column list (step S410). Next, the key evaluation PG 1512 determines whether the number of records acquired when the RDB sequence is uniquely designated in the WHERE clause is 1 (step S411). If the number of records that can be acquired is 1, the key evaluation PG 1512 adds the target RDB column to the unique RDB column list (step S 415). If the number of records that can be acquired is not 1, the key evaluation PG 1512 next adds the target RDB column to the non-unique RDB column list (step S412).

  When the processing of steps S410 to S412 is completed for the RDB columns in the non-unique RDB column list, the key evaluation PG 1512 combines the RDB columns of the non-unique RDB columns by a variable C to create a non-unique RDB column list (step S414). ), Returns to step S409. After the calculation of the variable C = C + 1 is performed in step S409, the processes of steps S410 to S413 are repeated.

  When the processes in steps S409 to S414 are executed up to the maximum value of C, the key evaluation PG 1512 next combines the RDB columns of the unique RDB column list to create KVS key candidates (step S416). FIG. 8 shows an example in which seven key candidates such as KeyA, B, C, D ··· G are created as the key candidate D416. For example, KeyA is a key candidate obtained by combining the RDB sequence "CarID" and the RDB sequence "Time".

  Next, the key evaluation PG 1512 calculates, for each of the created key candidates, an average value of the appearance frequency of the RDB sequence included in the key candidate, and holds the value as the matching degree of the key candidate (Step S417). FIG. 8 shows the matching degree calculation result D417. For example, the matching degree of Key A and Key B is 15, taking an average value of 10 and 20. Here, although the average value of the number of occurrences of the RDB sequence is used as the matching degree of the key candidate, the present invention is not limited to this. As another example, a total value of the number of occurrences may be used instead of the average value of the number of occurrences.

  Next, the key evaluation PG 1512 arranges the key candidates in descending order of the matching degree, extracts a predetermined number (two as an example here) of the head, and stores the key candidates in the storage unit 150 as a key candidate set 1524 ( Step S418). An example of the key candidate set 1524 is shown in FIG. In the example of FIG. 8, the key candidate set 1524 includes two key candidates, KeyA and KeyB.

  FIG. 9A is a flowchart of simulation table creation processing. FIG. 9B is a table showing an example of key candidates used to explain the simulation table creation process. FIG. 9C is a table showing an example of an RDB table used to explain the simulation table creation process.

  Referring to FIG. 9A, first, the key evaluation PG 1512 inputs the RDB table and stores it in the storage unit 150 (step S501). Subsequently, the key evaluation PG 1512 acquires key candidates to be simulated from the storage unit 150 (step S502). Here, the information of KeyA shown in FIG. 9B as an example is acquired as the key candidate D502.

  Next, the key evaluation PG 1512 is a pre-division simulation including a Key indicating a KVS key, a Value indicating a value of KVS, a Region indicating a divided region in which data is stored, and a Node indicating a node in which data is stored. A table D506 is created, and the RDB column included in the key candidate is registered as a Key of KVS, and the column names and values of other RDB columns are registered as Value in the pre-division simulation table D506 (step S503). In the upper row of the RDB table shown in FIG. 9C, PK is 1 and CarID is 1, and other data including a Time column, a Latitude column, a Longitude column, and a Speed column are registered. From the data, three pieces of data, each having the Latitude column, the Longitude column, and the Speed column as Value, are created and registered in the pre-division simulation table D506.

  Next, the key evaluation PG 1512 inputs the KVS information 302 used for the distributed KVS 300, and stores it in the storage unit 150 (step S504). As shown in FIG. 5, the KVS information 302 includes the number of nodes of the distributed KVS 300, the region size, and the like.

  Next, the key evaluation PG 1512 determines, based on the region size included in the KVS information 302, the region number indicating the region in which each data is stored, and additionally registers it in the pre-division simulation table D 506 (step S505). ). For example, each data may be sequentially assigned to a region, and the region number of that region may be registered. Although an example in which Region numbers are sequentially assigned to each data is shown here, the present invention is not limited to this. As another example, Region numbers may be randomly assigned to each data.

  Next, the key evaluation PG 1512 determines a node storing each area in the distributed KVS 300, and additionally registers a Node number in the Node column in the pre-division simulation table D506 (step S506). Here, as an example, each area is sequentially assigned to each node.

  Next, the key evaluation PG 1512 divides the pre-division simulation table D 506 for each Region number, and creates a simulation table D 508 (step S 507). Subsequently, the key evaluation PG 1512 stores the created simulation table D508 in the storage unit 150 (step S508).

  FIG. 10 is a flowchart of query simulation processing.

  First, the key evaluation PG 1512 acquires a simulation table to be used for simulation from the storage unit 150 (step S601). Subsequently, the key evaluation PG 1512 repeatedly executes the processing of steps S602 to S605 for all the queries in the usage log.

  First, the key evaluation PG 1512 identifies the data acquired by the query from the result of the query execution in the RDB usage log 1523 of the storage unit 150, and information of whether the data is acquired by the query in the simulation table 1527 Are registered (step S603). An example of the simulation table 1527 is shown in FIG. Y (Yes) in the case of acquisition, and N (No) in the case of acquisition are registered in the column “Is it acquired by a query?” In the simulation table D603 illustrated in FIG.

  Next, in the key evaluation PG 1512, for each simulation table for each area, N is added to the end of the column of “Is it acquired by query?”, And the information registered is The number of changes from N to N is counted, and the number is set as the number of scans (step S604). FIG. 10 shows a table D 604 indicating the number of scans for each area. For example, in an area called Region 1 stored in Node 1, the number of acquired records is 2 and the number of scans is 1. Then, the key evaluation PG 1512 sums up the number of scans for each node (step S605).

  When the series of processes in steps S602 to S606 are completed for all queries, the key evaluation PG 1512 next registers information on the query number, the number of records, and the number of scans in the table of the simulation result D605 (step S607) FIG. 10 shows a table D605 showing query simulation results. For example, in the query Q1.1, the number of acquired records is four and the number of scans is three in total.

  FIG. 11 is a flowchart of the score calculation process.

  First, the key evaluation PG 1512 acquires a query simulation result from the storage unit 150 (step S701).

  Next, the key evaluation PG 1512 calculates the sequential degree for each query in the query simulation result (step S702). The degree of sequentiality can be calculated by the following equation

各領域のスキャン回数をｒ_ｎとする。スキャン回数が０の場合には除外される。理論的最初スキャン数をＳとする。Ｓは次の式により算出することができる

Number of scans of each region is r _n. If the number of scans is 0, it is excluded. Let S be the theoretical first scan number. S can be calculated by the following equation

続いて、キー評価ＰＧ １５１２は、クエリ毎に分散度を算出する（ステップＳ７０３）。分散度は次の式により算出することができる

Subsequently, the key evaluation PG 1512 calculates the degree of dispersion for each query (step S703). The degree of dispersion can be calculated by the following equation

σは標準偏差を表わす。ノード数をＮとし、各ノードのスキャン回数をＳｎとし、Ｓｎの平均値をμとしたとき、次の式が成り立つ

σ represents the standard deviation. Assuming that the number of nodes is N, the number of scans of each node is Sn, and the average value of Sn is μ, the following equation holds

図１１には、シーケンシャル度および分散度の情報が付加されたクエリシミュレーション結果Ｄ７０３が示されている。

FIG. 11 shows a query simulation result D703 to which information of the degree of sequentiality and the degree of dispersion is added.

  Furthermore, the key evaluation PG 1512 stores the average value of the degree of sequentiality for each query as the average degree of sequentiality, the average value of the degree of distribution for each query as the average dispersion degree, and stores them as the key evaluation result 1528 in the storage unit 150 Step S704). FIG. 11 shows a key evaluation result D704 as an example. In the example of FIG. 11, the key candidate KeyA has a matching degree of 15, an average sequential degree of 30, and an average dispersion degree of 38.

  FIG. 12A is a flowchart of key evaluation presentation processing. FIG. 12B, FIG. 12C, FIG. 12D, and FIG. 12E are figures which show the example of the screen display by a key evaluation presentation process.

  First, the key evaluation presentation PG 1513 acquires a key evaluation result 1528 from the storage unit 150 (step S801). Next, the key evaluation presentation PG 1513 calculates, for each key candidate, the product of the degree of fitness, the average sequentiality, and the average degree of dispersion included in the key evaluation result 1528 (step S802). Subsequently, the key evaluation presentation PG 1513 displays the key evaluation result, the degree of matching, the average sequential degree, the average degree of dispersion, the score, and the degree of dispersion weight input unit on the output device 110 as screen 1 for each key candidate. (Step S803). Screen 1 is shown in FIG. 12B. By moving the slide bar of the dispersion degree weight input unit, it is possible to change the ratio of the degree of order in the score calculation and the weighting of the degree of dispersion.

  Next, when the key evaluation presentation PG 1513 detects a click on the screen by the user (designer) (step S804), if the user click relates to a weight input, it recalculates the score with the changed weight. (Step S805). On the other hand, if the user click relates to selection of a key candidate, the key evaluation presentation PG 1513 first ends the display of the screen 1 showing the key evaluation result (step S806), and the key design detail screen related to the selected key (Screen 2) is displayed (step S807).

  Screen 2 is shown in FIG. 12C. In the example of screen 2 in FIG. 12C, the RDB columns included in KeyA are CarID and Time. The score is 17100. The matching degree is 15. The average sequential degree is 30. The average degree of dispersion is 38. Furthermore, a decision button for confirming the selected key for transition and a back button for returning to the previous screen are displayed.

  Next, when the key evaluation presentation PG 1513 detects a user click (step S808), the display of the key design detail screen (screen 2) is ended (step S809) if the user click is a click to a button to return. , And return to step S803.

  If the user click detected in step S808 relates to the average sequential degree, the key evaluation presentation PG 1513 displays the sequential degree detail screen (screen 3) (step S810). Screen 3 is shown in FIG. 12D. In the example of screen 3 shown in FIG. 12D, the sequential degree for each query of KeyA is displayed as a graph, and a back button is displayed. Here, when the key evaluation presentation PG 1513 detects a user click (step S811), the display of the sequential degree detail screen (screen 3) is ended (step S812), and the process returns to step S807.

  If the user click detected in step S808 is a click for selecting the average degree of dispersion, the key evaluation presentation PG 1513 displays the degree of dispersion detail screen (screen 4) (step S813). Screen 4 is shown in FIG. 12E. In the example of screen 4 shown in FIG. 12E, the degree of dispersion for each query of KeyA is displayed as a graph, and a back button is displayed. Here, when the key evaluation presentation PG 1513 detects a user click (step S814), the display of the dispersion degree detail screen (screen 4) is ended (step S815), and the process returns to step S807.

  If the user click detected in step S808 is for the determination button, the key evaluation presentation PG 1513 ends the display of the key design detail screen (screen 2) (step S816) and is selected, and the key design detail screen is selected. The key (here, KeyA) displayed on the screen is used as a shift key, the shift key information is stored in the storage unit 150 (step S817), and the process is ended.

  FIG. 13A is a sequence diagram of data migration processing. 13B and 13C are diagrams showing examples of screen display by data migration processing.

  The data migration PG 1514 first acquires the vehicle information table schema definition 204 obtained from the RDB 200, the KVS information 302 obtained from the distributed KVS 300, the migration key information 1529, and the migration key simulation table 1527 from the storage unit 150. (Step S901). The data migration PG 1514 displays a data migration setting screen in order to set a column family name, which is the name of a column family in KVS (step S902). A data transfer setting screen (screen 5a) is shown in FIG. 13B. On the screen 5, the column names of RDB and the column family names of KVS are shown in the correspondence table, and further, a message display section and an execution button are shown. Next, the data migration PG 1514 inputs a column family name by user operation (step S903). When the user click to the execution button is detected, the data transfer PG 1514 deletes the display if the message is displayed on the message display unit (step S 905).

  Next, the data migration PG 1514 determines whether all column family names have been input (step S906). If all column family names have not been input, the data migration PG 1514 displays a message “Please enter column family” on the message display unit (step S 907), and returns to step S 903. FIG. 13C shows a data migration setting screen (screen 5b) on which a message is displayed. On the screen 5b, the column family name corresponding to the RDB column "Longitude" is not set, and a message "Please enter a column family" is displayed.

  If all column family names have been input in step S906, the data migration PG 1514 creates a table definition (table with empty contents) of the KVS table in the distribution KVS 300 of the migration destination (step S908). The KVS table can store Key and Value in association with each other for a column family.

  Next, the data transfer PG 1514 transfers to the distributed KVS 300 by registering the Key and Value of the transfer key simulation table 1527 in the KVS table (step S 909). At this time, the divided simulation table illustrated in FIG. 9A is stored in the KVS table of each of the nodes 303, 304, and 305 of the distributed KVS 300 of the transfer destination.

  FIG. 14 is a table showing an example of calculation of the degree of sequentiality and the degree of dispersion. Here, the number of nodes is four, and two regions are stored in each node. Region R1 and R5 are stored in Node1. Region R2 and R6 are stored in Node2. Region R3 and R7 are stored in Node3. Region R4 and R8 are stored in Node4. The logical minimum scan number S is three.

  Example 1 has a degree of sequentiality of 100 and a degree of dispersion of 0.83. Example 2 has a degree of sequentiality of 50 and a degree of dispersion of 1.30. Example 3 has a degree of sequentiality of 37.5 and a degree of dispersion of 100. Example 4 has a degree of sequentiality of 2.3 and a degree of dispersion of 33.3.

  Example 1 is the largest degree of sequentiality. In Example 1, the number of scans matches the logical minimum number of scans. The degree of dispersion is largest in Example 3. In Example 3, the scan occurs the same number of times (twice) across all the nodes.

  The embodiments of the present invention described above are exemplifications for explanation of the present invention, and are not intended to limit the scope of the present invention only to those embodiments. Those skilled in the art can practice the present invention in various other aspects without departing from the scope of the present invention.

100 ... key design support device, 110 ... output device, 120 ... arithmetic device, 130 ... input device, 140 ... communication device, 150 ... storage unit, 151 ... PG area, 1512 ... transition key information, 1513 ... key evaluation presentation PG 1514 Data migration PG 1515 Key candidate set creation module 1516 KVS table creation module 1517 Query simulation module 1518 Score calculation module 152 Data area 1521 RDB connection information 1522 Migration countermeasure client Information, 1522 ... Migration target client information, 1523 ... RDB utilization log, 1524 ... Key candidate set, 1525 ... Query set, 1526 ... RDB table, 1527 ... Simulation table, 1528 ... Key evaluation result, 1529 ... Migration key information 200 ... RDB, 201 ... communication device, 202 ... usage log, 2021 ... client, 2023 ... query, 2024 ... result, 203 ... vehicle information table, 204 ... vehicle information table schema definition, 300 ... distributed KVS, 301 ... communication device, 302 ... KVS information, 303 ... node, 304 ... node, 305 ... node, 401 ... client, 402 ... client, 403 ... client

Claims (11)

A database migration support device that supports creation of a distributed key value store migrated from a relational database, comprising
With memory and processor,
The memory generates at least one key candidate serving as a candidate for a key to be used for the distributed key-value store based on at least one column set in the relational database, and the key candidate is data of the relational database and A key evaluation program for evaluating based on a usage log, and a key evaluation presentation program for presenting the evaluation result of the key candidate,
The processor executes the key evaluation program and the key evaluation presentation program to present evaluation results of the key candidate.
Database migration support device. The key evaluation program generates at least one key candidate serving as a candidate for a key to be used for the distributed key-value store based on at least one column set in the relational database, and the relation for each of the key candidates is generated. The degree of matching indicating the degree of appearance of the column based on the key candidate in the usage log of the database and the access performance of the data distributed to the plurality of nodes of the distributed key-value store are calculated, Evaluating each of the key candidates based on the access performance;
The database migration support device according to claim 1. The key evaluation program calculates a score including the product of the matching degree and the access performance for each of the key candidates;
The key evaluation presentation program presents the score of each of the key candidates,
The database migration support device according to claim 2.   The database migration support device according to claim 1, wherein the access performance includes a sequential degree indicating a higher value as the number of scans for the node is smaller in accessing the database after migration of the simulation based on the usage log.   The access performance according to claim 4, wherein the access performance further includes a dispersion degree indicating a higher value as the scan is evenly distributed to the nodes, in access to the post-migration database of the simulation based on the usage log. Database migration support device. The key evaluation program is capable of changing the weight of the sequential degree and the degree of dispersion in the score by a user operation.
The database migration support device according to claim 5.   The key evaluation program selects, from among the columns set in the relational database, a predetermined number of applications from the one with the highest number of applications by the query extracted from the usage log, and selects one of the selected columns or a combination of the columns 2. The database migration support device according to claim 1, wherein one that uniquely identifies data is extracted as a key candidate, and further, a primary key is used as a key candidate.   8. The database migration support device according to claim 7, wherein the key evaluation program narrows down the key candidates based on the degree of matching, performs simulation on the narrowed key candidates, and calculates the access performance.   The database migration according to claim 1, wherein the key evaluation program causes the user to select a client to be migrated to the distributed key-value store from the related database, and uses the selected usage log of the client for evaluation of the key candidate. Support device. The memory further comprises a data migration program
The key evaluation presentation program displays an evaluation result of any one key candidate and a button for determining the key candidate as a transition key on the screen.
The data migration program creates the distributed key-value store using the key candidate as a migration key when the button is operated.
The database migration support device according to claim 1. A database migration support method for assisting in creating a distributed key-value store migrating from a relational database, comprising:
A key evaluation unit generates at least one key candidate as a candidate for a key to be used for the distributed key-value store based on at least one column set in the relational database;
The key evaluation unit evaluates each of the key candidates based on the data of the relational database and the usage log;
Key evaluation presenting means presents the evaluation result of the key candidate,
Database migration support method.

Images