Method for converting relational database into graph database

Abstract

The invention relates to the technical field of databases, in particular to a method for converting a relational database into a graph database. Comprising the following steps: S1, connecting a relational database by adopting an application framework, and migrating to generate a model file; s2, establishing a graph database, obtaining table names, foreign keys and indexes of the relational database according to the model file, and respectively generating points, edges and indexes corresponding to the graph database; s3, generating insertion statements of points and edges of the graph database, executing insertion operation, and migrating data in the relational database into the graph database; and S4, performing data verification by comparing the metadata, the data volume and the data query result of the relational database and the graph database. The method can adapt to various mainstream relational databases, the difficulty of indiscriminately converting the multi-source relational database into the graph database is realized, and the conversion process comprises points, edges and indexes of the graph database.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method for converting a relational database into a graph database. Background Art

[0002] In the prior art, there are methods for converting relational databases to graph databases. For example, Neo4j has proposed solutions for batch importing points and relationships, namely Load CSV and Neo4j-admin. The Load CSV mode requires the Cypher statement to import and convert, while Neo4j-admin requires the CSV file to be transferred, imported and converted. However, this method not only relies on the graph database itself, but also has certain format and size requirements for the CSV file.

[0003] The prior art also proposes an edge definition method based on field configuration and intermediate table to realize the connection between points. The edge definition method based on field configuration is to manually intervene to establish a relationship between a field in table A and a field in table B, while the edge definition method based on intermediate table definition is to formulate rules through JSON, define the attributes of the intermediate table and the edge, and realize the establishment of the relationship between table A and table B by setting rules; however, this method requires too much manual intervention to organize field configuration or intermediate table to define the edge, which is inefficient when facing a complex and huge database table structure;

[0004] The prior art also proposes to use the JSON format for data exchange, mapping the relational database data type to the JSON type, and then applying it to the graph database. The edge conversion is completed through the primary / foreign key constraints between the database tables and combined with Cypher statements to establish the relationship; however, the disadvantage is that when switching the data source, the JSON data type mapping table needs to be re-formulated, which is not flexible enough. In addition, the above solution does not involve the establishment of point and edge indexes of the graph database. Summary of the invention

[0005] The purpose of the present invention is to provide a method for converting a relational database into a graph database to solve the above technical problems;

[0006] The technical problem solved by the present invention can be achieved by adopting the following technical solutions:

[0007] A method for converting a relational database to a graph database, comprising:

[0008] Step S1, using an application framework to connect to a relational database, and migrating and generating a model file for displaying the tables of the relational database and the relationships between the tables in a model form;

[0009] Step S2, establishing a graph database, obtaining the table name, foreign key and index of the relational database according to the model file, and generating the points, edges and indexes corresponding to the graph database respectively;

[0010] Step S3, querying each table in the relational database according to the table name and table field of the relational database obtained from the model file, generating insert statements for the points and edges of the graph database, executing the insert operation, and migrating the data in the relational database to the graph database;

[0011] Step S4, perform data verification by comparing the metadata, data volume and data query results of the relational database and the graph database. If the verification is successful, the data transfer is completed; if the verification fails, return to execute step S1 or jump out of the current step for external debugging.

[0012] Preferably, in step S1, the application framework is a Django framework.

[0013] Preferably, in step S1, after generating the model file, the model file is further modified, including:

[0014] Adding index marks to table fields that have separate indexes in the relational database but are not reflected in the model file; and / or;

[0015] Correct any data types that have been incorrectly changed after data migration; and / or;

[0016] Add default values ​​to table fields that have default values ​​in the relational database but are not reflected in the model file.

[0017] Preferably, the method for generating points corresponding to the graph database in step S2 includes:

[0018] Step S211, traverse all fields under the model file;

[0019] Step S212, determine whether the current field is a foreign key, if it is a foreign key, return to execute step S211; if it is not a foreign key, obtain the field information of the current field, convert the field type of the current field into the type format of the graph database according to the preset mapping relationship, and determine whether the traversal of the model file is completed. If the traversal is completed, execute step S213; otherwise, return to execute step S211;

[0020] Step S213, combining the table name of the relational database corresponding to the current model file with all fields to generate a statement for creating a point.

[0021] Preferably, the method for generating the edge corresponding to the graph database in step S2 includes:

[0022] Step S221, traverse all fields under the model file;

[0023] Step S222, determine whether the current field is a foreign key, if not, return to execute step S221; if it is a foreign key, combine the table name and field name of the relational database corresponding to the model file into an edge name, create an edge statement, and determine whether all fields have been traversed. If so, end edge generation; otherwise, execute step S221.

[0024] Preferably, the method for generating the index corresponding to the graph database in step S2 includes:

[0025] Step S231, obtaining the table name corresponding to the model file, and establishing a point index with the table name corresponding to the model file;

[0026] Step S232, checking the composite index and the joint index in the model file, and establishing a point attribute index;

[0027] Step S233, traverse all fields of the model file;

[0028] Step S234, determine whether the current field is a foreign key. If it is a foreign key, create an edge index by combining the table name with the field name, and execute step S235; if it is not a foreign key, determine whether the current field is an index. If it is an index, create a point attribute index by combining the table name with the field name; if it is not an index, execute step S235;

[0029] Step S235, determine whether all fields have been traversed, if so, the indexing is completed; otherwise, return to step S233.

[0030] Preferably, step S3 comprises,

[0031] Step S31, traverse all fields under the model file to find the primary key field and the foreign key field;

[0032] Step S32, initializing a cursor using information of the primary key field and the foreign key field, and determining a query language for a paging query through the cursor;

[0033] Step S33, executing the query language, obtaining the instance set of the model file, and determining whether there is unmigrated data. If so, executing step S34; if not, executing step S3 is completed;

[0034] Step S34, traversing the instance set, obtaining corresponding values ​​of the primary key field and the foreign key field, respectively obtaining the identifier of the current point and the identifier of the pointed point, and determining the edges and the relationship between the edges;

[0035] Step S35, obtaining corresponding values ​​according to all fields of the model file, and obtaining attribute information of the current point according to the field relationship mapping dictionary;

[0036] Step S36, determine whether the traversal is completed. If so, generate and execute insert statements for points and edges. Update the cursor information through the corresponding value of the primary key field. Determine the query language for paging query through the updated cursor and return to step S33; if not, return to step S34.

[0037] Preferably, in step S31, when traversing the model file, the fields of the model file, the table name associated with the foreign key field, and the field type relationship are saved in the field relationship mapping dictionary.

[0038] Preferably, step S31 includes:

[0039] Step S311, traverse all fields under the model file;

[0040] Step S312, determine whether it is a primary key, if not, execute step S313; if yes, check whether the primary key field is in the joint unique index, if yes, determine it as a joint primary key; otherwise, determine it as a single primary key;

[0041] Step S313, determine whether it is a foreign key, if not, return to step S311; if yes, obtain the foreign key field and the associated table name and field type of the foreign key field.

[0042] Preferably, step S4 comprises,

[0043] Step S41, extracting metadata from the relational database, establishing a mapping relationship between the relational database and the graph database, verifying the correspondence between the points and the edges, and outputting a verification success result if they correspond; and outputting a verification failure result if they do not correspond;

[0044] Step S42, counting the amount of data in each table in the relational database and counting the amount of data in each point in the graph database, and determining whether the amounts of data are the same, if so, outputting a verification success result; otherwise, outputting a verification failure result;

[0045] Step S43, perform data query, compare the query results in the relational database and the graph database to see if they are consistent, if so, output a verification success result; otherwise, output a verification failure result.

[0046] Beneficial effects of the present invention: Due to the adoption of the above technical solution, the present invention can adapt to a variety of mainstream relational databases, and realize the difficulty of indiscriminate conversion of multi-source relational databases into graph databases. The conversion process includes the points, edges and indexes of the graph database. At the same time, the data conversion method is more convenient, without excessive manual intervention, and does not care about the number of tables, the number of table fields, and the size of table data. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1A schematic diagram of the steps of a method for converting a relational database to a graph database in an embodiment of the present invention;

[0048] Figure 2 It is a flow chart of converting a relational database to a graph database in an embodiment of the present invention;

[0049] Figure 3 This is a schematic diagram of the steps of generating a map database point in an embodiment of the present invention;

[0050] Figure 4 A flowchart of generating a map database point in an embodiment of the present invention;

[0051] Figure 5 Schematic diagram of the step of generating edge in the graph database in an embodiment of the present invention;

[0052] Figure 6 A flow chart for generating edge maps from a map database in an embodiment of the present invention;

[0053] Figure 7 This is a schematic diagram of the steps of generating a map database index in an embodiment of the present invention;

[0054] Figure 8 A flowchart of generating a map database index in an embodiment of the present invention;

[0055] Fig. 9 A schematic diagram of the steps of migrating data from a relational database to a graph database in an embodiment of the present invention;

[0056] Fig.10 A flowchart of migrating data from a relational database to a graph database in an embodiment of the present invention;

[0057] Fig.11 A schematic diagram of the steps of finding primary keys and foreign keys in an embodiment of the present invention;

[0058] Fig.12 A schematic diagram of the steps of data verification in an embodiment of the present invention;

[0059] Fig.13 This is the ER diagram of the MySQL data set in the embodiment of the present invention;

[0060] Fig.14 Schematic diagram of metadata conversion points and edges in an embodiment of the present invention;

[0061] Fig.15 This is a comparison diagram of the MySQL table structure and NebulaGraph point attributes in an embodiment of the present invention;

[0062] Fig.16 A schematic diagram of a metadata conversion point index and an edge index in an embodiment of the present invention;

[0063] Fig.17This is a schematic diagram of data volume comparison results in an embodiment of the present invention;

[0064] Fig.18 This is a schematic diagram of query results in an embodiment of the present invention;

[0065] Fig.19 Schematic diagram of query results in an embodiment of the present invention. DETAILED DESCRIPTION

[0066] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0067] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0068] The present invention will be further described below in conjunction with the accompanying drawings and specific embodiments, but they are not intended to limit the present invention.

[0069] A method for converting a relational database to a graph database, such as Figure 1 , Figure 2 As shown, including,

[0070] Step S1, using an application framework to connect to a relational database, migrating and generating a model file for displaying the tables of the relational database and the relationships between the tables in a model form;

[0071] Step S2, establish a graph database, obtain the table name, foreign key and index of the relational database according to the model file, and generate the points, edges and indexes corresponding to the graph database respectively;

[0072] Step S3, querying each table in the relational database according to the table name and table field of the relational database obtained from the model file, generating insert statements for the points and edges of the graph database, executing the insert operation, and migrating the data in the relational database to the graph database;

[0073] Step S4, perform data verification by comparing the metadata, data volume and data query results of the relational database and the graph database. If the verification is successful, the data transfer is completed; if the verification fails, return to execute step S1 or jump out of the current step for external debugging.

[0074] Specifically, this paper proposes a method for converting relational databases to graph databases that can adapt to a variety of mainstream relational databases, and realizes the difficulty of converting multi-source relational databases into graph databases without distinction. The conversion process includes the points, edges and indexes of the graph database. At the same time, it provides a more portable method for data conversion, which does not require excessive manual intervention and does not care about the number of tables, the number of table fields, and the amount of table data.

[0075] The overall concept of the present invention is to implement it based on object-relational mapping (Django ORM). The Django framework supports mainstream relational databases such as PostgreSQL, MariaDB, MySQL, Oracle and SQLite, that is, the present invention supports the conversion of these relational databases into graph databases.

[0076] Step S1 is used to sort out the relational database metadata, connect the current relational database through the Django project, and migrate to generate a model file, that is, to display the database tables and the relationships between the tables in the form of a model.

[0077] Step S2 is used to establish the metadata of the graph database. According to the model file, the table name, foreign key and index of the relational database can be obtained, and the corresponding points, edges and indexes of the graph database can be generated respectively.

[0078] Step S3 is used for data conversion. The table name and table fields of the relational database are obtained according to the model file. By querying all field data of each table, the insertion statements of the points and edges of the graph database are generated, and finally the operation is executed.

[0079] Step S4 is used for data verification, comparing the metadata of the relational database and the graph database, the table corresponding to the point, the table field corresponding to the point attribute, the table foreign key corresponding to the edge, the table primary key index corresponding to the point index, the table field (non-foreign key) index corresponding to the point attribute index, the table foreign key field corresponding to the edge index; at the same time, compare whether the query results of the two are consistent.

[0080] The Django model file effectively solves the problem of indiscriminate conversion of multi-source relational databases into graph databases, ensuring the constraints of the original relational database to the greatest extent. At the same time, metadata conversion is more convenient.

[0081] In a preferred embodiment, in step S1, the application framework is a Django framework.

[0082] Specifically, the preferred relational database in the present invention is MySQL, the graph database NebulaGraph (a distributed high-performance graph database), and the conversion data is based on the MySQL official data set.

[0083] In a preferred embodiment, in step S1, after the model file is generated, the model file is also modified, including:

[0084] Add index tags to table fields that have separate indexes in the relational database but are not reflected in the model file; and / or;

[0085] Correct any data types that have been incorrectly changed after data migration; and / or;

[0086] Add default values ​​to table fields that have default values ​​in the relational database but are not reflected in the model file.

[0087] Specifically, in step S1, the relational database metadata is sorted out.

[0088] First, prepare the Django project, create an application with the database name, configure the database source, and execute the migration command: python manage.py inspectdb>${app} / models.py to save the database metadata in the form of a Model file.

[0089] In order to ensure the constraints of the original database to the greatest extent, some corrections need to be done after the model file is generated:

[0090] 1. Add index tags to table fields that have separate indexes in the relational database but are not reflected in the model file. When table fields have separate indexes, they will not be reflected in the model file during migration. In this case, you need to display the tag db_index=True in the current field.

[0091] 2. Correct the data type that changes incorrectly after data migration. For example, when the table field is of BOOL type, it will be converted to IntegerField during migration and needs to be updated to BooleanField;

[0092] 3. Add default values ​​to table fields that have default values ​​in the relational database but are not reflected in the model file. When a table field has a default value, it is not reflected after migration. You need to add default="default value" to the current field.

[0093] In a preferred embodiment, Figure 3 , Figure 4 As shown, the method for generating points corresponding to the graph database in step S2 includes:

[0094] Step S211, traverse all fields under the model file;

[0095] Step S212, determine whether the current field is a foreign key, if it is a foreign key, return to step S211; if it is not a foreign key, obtain the field information of the current field, convert the field type of the current field into the type format of the graph database according to the preset mapping relationship, and determine whether the model file traversal is completed. If the traversal is completed, execute step S213; otherwise, return to step S211;

[0096] Step S213, combining the table name of the relational database corresponding to the current model file with all fields to generate a statement for creating a point.

[0097] Specifically, step S2 in the present invention includes four sub-steps, namely, establishing a graph space, establishing a point, establishing an edge, and establishing an index;

[0098] Before executing the step of establishing a point, a graph space needs to be established. The preferred relational database in the present invention is MySQL, and the graph database is NebulaGraph. The graph space is a collection of graph data isolated from each other in NebulaGraph, and is the basic unit for storing graph data, which is similar to the concept of database in MySQL.

[0099] Specifically, the present invention establishes the graph space grammar as follows:

[0100] CREATE SPACE [IF NOT EXISTS]<graph_space_name> (

[0101] [partition_num=<partition_number> ,]

[0102] [replica_factor=<replica_number> ,]

[0103] vid_type={FIXED_STRING( <n>)|INT

[64] } );

[0105] Among them, graph_space_name is the space name, which is the source database database name; partition_num is the number of partitions, which is determined according to the number of source databases; replica_factor is the number of replicas, which is determined according to the database reliability requirements; vid_type is the graph node VID type, and the table name + primary key value combination hash is used to obtain the first 32 bits of the string as the VID. Therefore, select FIXED_STRING(32) and adjust the number of hash bits according to the data volume.

[0106] CREATE SPACE`$database`(partition_num=5,replica_factor=1,vid_type=FIXED_STRING(32))

[0107] Specifically, get all models (classes) under the current application and process models concurrently, such as Figure 2 As shown, all fields under the current model file are traversed, the current field is determined to be non-foreign key, and its field name, field type, field default value, whether it is empty and other information are obtained. Finally, a mapping based on the model is generated, and a statement for creating a point is generated in combination with all its fields. The table field type needs to be converted. The conversion mapping is shown in Table 1 below.

[0108]

[0109]

[0110] Table 1

[0111] SMALLINT represents a small integer in MySQL, and is mapped to SmallIntegerField in Django, which is used to store integers in a small range. NebulaGraph maps it to INT16, which represents a 16-bit integer.

[0112] MEDIUMINT and INT in MySQL are used to represent medium-range and standard integers. Django maps them to IntegerField or AutoField (auto-increment field). NebulaGraph maps them to INT32, which represents a 32-bit integer.

[0113] In MySQL, BIGINT is used to store large integers. Django maps it to BigIntegerField or BigAutoField. NebulaGraph maps it to INT64, which means a 64-bit integer.

[0114] In MySQL, FLOAT and DOUBLE are used to store floating-point numbers, and DECIMAL is used for high-precision fixed-point numbers. In Django, both are mapped to FloatField and DecimalField. In NebulaGraph, both are mapped to DOUBLE, which is suitable for storing floating-point numbers.

[0115] In MySQL, CHAR is used for fixed-length strings, and VARCHAR is used for variable-length strings; in Django, both are mapped to CharField; in NebulaGraph, CHAR is mapped to FIXED_STRING, and VARCHAR is mapped to STRING;

[0116] In MySQL, TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT are used to store text of variable length; Django maps them to TextField; NebulaGraph maps them to STRING, which is used to store larger text data;

[0117] In MySQL, DATE, TIME, and DATETIME are used to represent date and time; the corresponding fields in Django are DateField, TimeField, and DateTimeField; the corresponding types in NebulaGraph are DATE, TIME, and DATETIME respectively;

[0118] In MySQL, BOOL represents a Boolean value; in Django, it is mapped to BooleanField; in NebulaGraph, it is directly mapped to BOOL.

[0119] Specifically, for each model, concurrent processing can be used to process multiple models at the same time to improve efficiency. After obtaining each model, all its fields need to be traversed. For each field, further determine whether it is a foreign key. For each non-foreign key field, extract field information including field name, field type, default value, and whether it is empty.

[0120] The field type needs to be converted. For details, refer to the conversion relationship defined in Table 1 above.

[0121] The syntax for creating a point using the extracted information is as follows:

[0122] CREATE TAG[IF NOT EXISTS]<tag_name> (

[0124] <prop_name><data_type>[NULL|NOT NULL][DEFAULT<default_value> ]

[0125] [{,<prop_name><data_type> [NULL|NOT NULL][DEFAULT<default_value> ]}...] );

[0127] Among them, tag_name is the table name, prop_name is the field name, data_type is the field type, NULL|NOTNULL means whether it is allowed to be empty based on the field attributes, and default_value is the default value of the field.

[0128] In this example, assume that the Salaries model has the following fields:

[0129] emp_no (employee number, type INT, NOT NULL);

[0130] salary(salary, type INT, NOT NULL);

[0131] from_date (start date, type is DATE, NULL);

[0132] to_date (end date, type DATE, NULL);

[0133] The final statement is CREATE TAG IF NOT EXISTS`salaries`(`emp_no`INT,`salary`INT,`from_date`DATE,`to_date`DATE);

[0134] In a preferred embodiment, Figure 5 , Figure 6 As shown, the method for generating edges corresponding to the graph database in step S2 includes:

[0135] Step S221, traverse all fields under the model file;

[0136] Step S222, determine whether the current field is a foreign key. If not, return to execute step S221; if it is a foreign key, combine the table name and field name of the relational database corresponding to the model file into an edge name, create an edge statement, and determine whether all fields have been traversed. If so, end edge generation; otherwise, execute step S221.

[0137] Specifically, all models (classes) under the current application are obtained, models are processed concurrently, all fields under the current model are traversed, foreign key fields are found, and a create edge statement is generated with table name + field name as the edge.

[0138] The syntax for creating an edge is as follows:

[0139] CREATE EDGE [IF NOT EXISTS]<edge_type_name> ();

[0140] Take the Salaries model as an example. The table name is salaries. Assuming that the field emp_no is a foreign key, the edge name is generated based on the name of the foreign key field and the table name of the model. The edge naming rule is "table name-field name". Take the Salaries model as an example. The table name is salaries and the foreign key field is emp_no, so the edge name is salaries-emp_no.

[0141] In a preferred embodiment, Figure 7 , Figure 8 As shown, the method for generating the index corresponding to the graph database in step S2 includes:

[0142] Step S231, obtaining the table name corresponding to the model file, and establishing a point index with the table name corresponding to the model file;

[0143] Step S232, checking the composite index and the joint index in the model file, and establishing a point attribute index;

[0144] Step S233, traverse all fields of the model file;

[0145] Step S234, determine whether the current field is a foreign key. If it is a foreign key, create an edge index by combining the table name with the field name, and execute step S235; if it is not a foreign key, determine whether the current field is an index. If it is an index, create a point attribute index by combining the table name with the field name; if it is not an index, execute step S235;

[0146] Step S235, determine whether all fields have been traversed, if so, the indexing is completed; otherwise, return to step S233.

[0147] Specifically, all models (classes) under the current application are obtained, and models are processed concurrently. Indexes are divided into point indexes, point attribute indexes, and edge indexes. First, the table name corresponding to the model is obtained, and a point index is established; second, the composite index and joint index in the model are obtained, and if there is one, a point attribute index is established; finally, all fields under the model are traversed, and when the field is a foreign key, an edge index is established, or when the field is an index, a point attribute index is established, otherwise no operation is performed.

[0148] The syntax for creating an index is as follows:

[0149] CREATE{TAG|EDGE}INDEX[IF NOT EXISTS]<index_name> ON{<tag_name> |<edge_name>}([<prop_name_list> ]);

[0150] Taking the Salaries model as an example, the index naming convention is "idx-table name-field name", with a total of three indexes.

[0151] Point index: CREATE TAG INDEX IF NOT EXISTS`idx-salaries`ON`salaries`()

[0152] Point attribute index: CREATE TAG INDEX IF NOT EXISTS`idx-salaries-emp_no-from_date`ON`salaries`(`emp_no`,`from_date`)

[0153] Edge index: CREATE EDGE INDEX IF NOT EXISTS`idx-salaries-emp_no`ON`salaries-emp_no`()

[0154] More specifically, batch point and edge insertion statements are generated according to the edge-to-edge relationship mapping dictionary and the point-to-point attribute value mapping dictionary.

[0155] 1. Generate point insert statement

[0156] Batch insert syntax:

[0157] INSERT VERTEX[IF NOT EXISTS][tag_props,[tag_props]...]

[0158] VALUES VID:([prop_value_list])[,VID:([prop_value_list]),...];

[0159] Taking the Salaries model as an example, the nGQL is as follows:

[0160] INSERT VERTEX`salaries`(`emp_no`,`salary`,`from_date`,`to_date`)VALUES'vid1':($emp_no,$title,date("$from_date"),date("$to_date")),...,

[0161] 'vidn':($emp_no,$title,date("$from_date"),date("$to_date"));

[0162] 2. Generate edge insertion statements

[0163] Batch insert syntax:

[0164] INSERT EDGE [IF NOT EXISTS]<edge_type> ()VALUES

[0165] <src_vid> -><dst_vid> :()[,<src_vid> -><dst_vid> :(),...];

[0166] Taking the Salaries model as an example, the nGQL is as follows: INSER TEDGE `salaries-emp_no`() VALUES '$vid_src'->'$vid_dest':(),...,'$vid_srcn'->'$vid_destn':();

[0167] Update the cursor by combining the corresponding values ​​of the (primary key) and updating the cursor with the last instance data. Repeat the above steps until the query ends with no more data.

[0168] In a preferred embodiment, Fig. 9 , Fig.10 As shown, step S3 includes,

[0169] Step S31, traverse all fields under the model file to find the primary key field and foreign key field;

[0170] Step S32, using the information of the primary key field and the foreign key field to initialize the cursor, and determining the query language of the paging query through the cursor;

[0171] Step S33, execute the query language, obtain the instance set of the model file, and determine whether there is unmigrated data. If so, execute step S34; if not, execute step S3;

[0172] Step S34, traverse the instance set, obtain corresponding values ​​of the primary key field and the foreign key field, obtain the identifier of the current point and the identifier of the pointed point respectively, and determine the edge and the relationship between the edges;

[0173] Step S35, obtaining corresponding values ​​according to all fields of the model file, and obtaining attribute information of the current point according to the field relationship mapping dictionary;

[0174] Step S36, determine whether the traversal is completed. If so, generate and execute insert statements for points and edges. Update the cursor information through the corresponding value of the primary key field. Determine the query language for paging query through the updated cursor and return to step S33; if not, return to step S34.

[0175] Specifically, step S3 is to migrate all data in the relational database to the graph database. The core idea is to perform paging queries on each table in the relational database, generate vertex and edge execution statements for the graph database, and perform batch insertion operations.

[0176] In a preferred embodiment, in step S31, when traversing the model file, the fields of the model file, the table names associated with the foreign key fields, and the field type relationships are saved in the field relationship mapping dictionary.

[0177] The present invention traverses all fields under the model, first finds the primary key field, and determines whether it is in unique_together. If it exists, it means that the corresponding field list of unique_together is a joint primary key, otherwise it is a single primary key; secondly, find the foreign key field, obtain its associated table name and field type; finally, initialize the cursor based on this information and determine the paging query language (SQL).

[0178] When traversing the model, save the fields, foreign key table names (or NULL), and field type relationships into the field relationship mapping dictionary.

[0179] The SQL template is: SELECT*FROM{table name}WHERE{}>{}ORDER BY{}LIMIT$size, where size = 1000, which can be adjusted according to cluster performance.

[0180] Take the Salaries model as an example. The table name is db_table = 'salaries'. The primary_key corresponds to the field emp_no'. In unique_together = (('emp_no', 'from_date'),), the joint primary key (`emp_no`, `from_date`) is determined. Then, according to the `emp_no` and `from_date` field types, the initial cursor cursor = (0,") is determined. Finally, after applying the SQL template, the SQL becomes SELECT * FROM salaries WHERE (`emp_no`, `from_date`) > (0,") ORDER BY `emp_no`, `from_date` LIMIT 1000.

[0181] Specifically, the present invention executes a query language, obtains an instance set of a model file, traverses the instance set, and converts the instance object into a dictionary;

[0182] Generate the identifier of the point of the current instance object in the graph database by combining the table name with the value of the primary key field and performing hash processing;

[0183] Get the foreign key field and the associated field type and table name through the field relationship mapping dictionary, get the edge name and the identifier pointed to by the edge, and save them in the edge-to-edge relationship mapping dictionary;

[0184] The attribute information of the current point is obtained through the field relationship mapping dictionary, converted according to the preset attribute type mapping rules, and saved in the point and point attribute value mapping dictionary.

[0185] The present invention executes native SQL query through Model.objects.raw (SQL statement), returns a model instance set, traverses the instance set, processes the instances one by one, and first converts the instance object into a dictionary (dict) to facilitate subsequent use of field names to obtain their corresponding values.

[0186] 1. Determine the point identifier (VID). The current instance is a point in the graph database. Each point is marked with a unique identifier. The table name + (joint) primary key value is combined and hashed to obtain the first 32 bits as the instance point identifier.

[0187] Take the Salaries model as an example.

[0188] VID = hash('salaries'-data_dict['emp_no']-data_dict[from_date'])[:32], named vid_src.

[0189] 2. Determine the edge EDGE and edge relationship. According to the field relationship mapping dictionary, obtain the foreign key field, field type and associated table name. The edge name is the table name minus the foreign key field name, and the identifier pointed to by the edge is the foreign key associated table name plus the corresponding value of the foreign key field. Then hash the first 32 bits and save them in the edge and edge relationship mapping dictionary, {'EDGE':["'$vid_src'->'$vid_dest':()"]}.

[0190] Taking the Salaries model as an example, the edge is salaries-emp_no, pointing to VID=hash('emp_no'-data_dict['emp_no'])[:32], named vid_dest.

[0191] 3. Determine point attribute information

[0192] According to the field relationship mapping dictionary in S31, all attribute information of the current point can be obtained. The attribute type is converted in combination with Table 1 and saved in the point and point attribute value mapping dictionary.

[0193] {'VID':'($emp_no,$title,date("$from_date"),date("$to_date"))'}.

[0194] More specifically, by combining the corresponding values ​​of the (primary key), the cursor is updated, see the cursor initialization operation in step S32, and the cursor is updated with the last instance data, and the above steps are repeated until the query ends without data.

[0195] In a preferred embodiment, Fig.11 As shown, step S31 includes,

[0196] Step S311, traverse all fields under the model file;

[0197] Step S312, determine whether it is a primary key, if not, execute step S313; if yes, check whether the primary key field is in the joint unique index, if yes, determine it as a joint primary key; otherwise, determine it as a single primary key;

[0198] Step S313, determine whether it is a foreign key, if not, return to step S311; if yes, obtain the foreign key field and the associated table name and field type of the foreign key field.

[0199] In a preferred embodiment, Fig.12 As shown, step S4 includes,

[0200] Step S41, extracting metadata from the relational database, establishing a mapping relationship between the relational database and the graph database, verifying the correspondence between the points and the edges, and outputting a verification success result if they correspond; if not, outputting a verification failure result;

[0201] Step S42, counting the data volume of each table in the relational database and the data volume of each point in the statistical graph database, and determining whether the data volumes are the same, if so, outputting a verification success result; otherwise, outputting a verification failure result;

[0202] Step S43, perform data query and compare whether the query results in the relational database and the graph database are consistent. If so, output a verification success result; otherwise, output a verification failure result.

[0203] Specifically, after the relational database is successfully converted into a graph database, data verification is performed, mainly from three aspects: metadata, data volume, and data query results.

[0204] Metadata verification: Fig.13 The ER diagram of the official MySQL data set is shown here. You can see that there are 6 tables and 6 relationships in total. Fig.14 This is the situation of nodes and edges in the graph database, with a total of 6 nodes and 6 edges. Nodes: represent the tables in the database, such as departments, dept_emp, dept_manager, employees, salaries, and titles. Edges: represent the relationship between tables, such as: dept_emp-dept_no means that the dept_no field in the dept_emp table is connected to the departments table; dept_manager-dept_no means that the dept_no field in the dept_manager table is connected to the departments table; salaries-emp_no means that the emp_no field in the salaries table is connected to the employees table. You can see that it corresponds to MySQL.

[0205] In addition, the comparison of indexes and attribute columns, taking the salary table as an example, Fig.15 You can see that the MySQL table has 4 columns, 1 composite primary key, and 1 foreign key, which correspond to the 4 attributes and attribute types of the salary point.

[0206] More specifically, Fig.15 As shown, the MySQL table structure table name: salaries

[0207] The 4 columns (attributes) are:

[0208] emp_no: integer (int(11)), cannot be empty (NOT NULL), indicates the employee number.

[0209] salary: integer (int(11)), cannot be empty, represents employee salary.

[0210] from_date: date type (date), cannot be empty, indicates the starting date of salary.

[0211] to_date: date type (date), can be empty, indicating the end date of salary.

[0212] Joint primary key: PRIMARY KEY (emp_no, from_date) means that emp_no and from_date together uniquely identify each record.

[0213] Foreign key: CONSTRAINT "Salaries_ibfk_1" FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE means that the emp_no field references the emp_no field in the employees table. When the corresponding employee record is deleted, the related salary record in the salaries table will also be automatically deleted.

[0214] In NebulaGraph, the attributes of the Salaries table are mapped to the attributes of the nodes, as follows:

[0215] Property name:

[0216] emp_no: corresponds to the emp_no field, the data type is int32.

[0217] salary: corresponds to the salary field, the data type is int32.

[0218] from_date: corresponds to the from_date field, the data type is date.

[0219] to_date: corresponds to the to_date field, and the data type is also date.

[0220] Index Fig.16 , where idx-salaries is equivalent to the primary key index, and idx-salaries-emp_no is equivalent to the foreign key `salaries_ibfk_1`

[0221] More specifically, nodes represent tables in the database, such as departments, dept_emp, employees, salaries, and titles. Edges represent the relationship between tables, such as the connection between dept_emp and departments, and the connection between salaries and employees.

[0222] idx-salaries is equivalent to the primary key index, and idx-salaries-emp_no is equivalent to the foreign key index, which means that the emp_no field in the salaries table references the emp_no field in the employees table. The foreign key salaries_ibfk_1 ensures that every emp_no in the salaries table must exist in the employees table.

[0223] The data volume verification method of the present invention comprises:

[0224] Step 1: Count the data volume of each MySQL table, obtain all models (classes) under the current application, traverse the model, obtain the table name and table foreign key, and execute Model.objects.count() to obtain the total number of the current table, and save it to the table name and quantity mapping dictionary stats1_dict.

[0225] Step 2: Count the data volume of each point in NebulaGraph. Switch the graph space in NebulaGraph Studio, execute submit job stats, and then execute show stats to get the number of all points and edges, and map the points, edges and quantities to the dictionary stats2_dict.

[0226] Step 3: Compare the two and determine whether the migration is successful based on stats1_dict == stats2_dict. The data volume comparison result is as follows: Fig.17 As shown, it can be seen that the data volume of the two is the same, so the example migration in this embodiment is successful.

[0227] Verify the data query results. Take the query of employee 10001's salary information as an example.

[0228] In MySQL, execute select * from salaries where emp_no=10001;

[0229] Execute MATCH(v:salaries{emp_no:10001})RETURN v in NebulaGraph;

[0230] The comparison results are shown in Fig.18 There are 17 rows of data in MySQL, corresponding to 17 points in NebulaGraph. In addition, the 13th row of data corresponds to the point with vid="f6656bfc524b9966cf8b47e806c5244f".

[0231] Take the employee information and salary of employee number 10001 as an example.

[0232] In MySQL, execute select*from employees a join salaries b on a.emp_no=b.emp_no where a.emp_no=10001;

[0233] Execute MATCH(v:salaries{emp_no:10001})-[e:`salaries-emp_no`*1..2]->(v2)RETURNe in NebulaGraph;

[0234] The comparison results are shown in Fig.19 As shown, MySQL requires a related query, and the employee information will be displayed redundantly; NebulaGraph uses the salary-emp_no edge for the related query, and only one point (employee) is displayed with 17 points (salary).

[0235] In summary, the present invention proposes a method for converting a relational database to a graph database that can adapt to a variety of mainstream relational databases, and solves the difficulty of converting multi-source relational databases into graph databases without distinction. The conversion process includes the points, edges and indexes of the graph database. At the same time, a more portable method is provided in terms of data conversion, which does not require excessive manual intervention and does not care about the number of tables, the number of table fields, and the size of table data.

[0236] The above description is only a preferred embodiment of the present invention, and does not limit the implementation mode and protection scope of the present invention. For those skilled in the art, it should be aware that all solutions obtained by equivalent substitutions and obvious changes made using the description and illustrations of the present invention should be included in the protection scope of the present invention.< / n>

Claims

1. A method for converting a relational database to a graph database, characterized in that: include, Step S1, using an application framework to connect to a relational database, and migrating and generating a model file for displaying the tables of the relational database and the relationships between the tables in a model form; Step S2, establishing a graph database, obtaining the table name, foreign key and index of the relational database according to the model file, and generating the points, edges and indexes corresponding to the graph database respectively; Step S3, querying each table in the relational database according to the table name and table field of the relational database obtained from the model file, generating insert statements for the points and edges of the graph database, executing the insert operation, and migrating the data in the relational database to the graph database; Step S4, perform data verification by comparing the metadata, data volume and data query results of the relational database and the graph database. If the verification is successful, the data transfer is completed; if the verification fails, return to execute step S1 or jump out of the current step for external debugging.

2. The method for converting a relational database to a graph database according to claim 1, characterized in that: In step S1, the application framework is a Django framework.

3. The method for converting a relational database to a graph database according to claim 1, characterized in that: In step S1, after the model file is generated, the model file is also modified, including: Adding index marks to table fields that have separate indexes in the relational database but are not reflected in the model file; and / or; Correct any data types that have been incorrectly changed after data migration; and / or; Add default values ​​to table fields that have default values ​​in the relational database but are not reflected in the model file.

4. The method for converting a relational database to a graph database according to claim 1, characterized in that: The method of generating points corresponding to the graph database in step S2 includes: Step S211, traverse all fields under the model file; Step S212, determine whether the current field is a foreign key, if it is a foreign key, return to execute step S211; if it is not a foreign key, obtain the field information of the current field, convert the field type of the current field into the type format of the graph database according to the preset mapping relationship, determine whether the traversal of the model file is completed, if the traversal is completed, execute step S213; Otherwise, return to step S211; Step S213, combining the table name of the relational database corresponding to the current model file with all fields to generate a statement for creating a point.

5. The method for converting a relational database to a graph database according to claim 1, characterized in that: The method for generating the edge corresponding to the graph database in step S2 includes: Step S221, traverse all fields under the model file; Step S222, determine whether the current field is a foreign key, if not, return to step S221; if it is a foreign key, combine the table name and field name of the relational database corresponding to the model file into an edge name, create an edge statement, determine whether all fields have been traversed, if yes, end edge generation; Otherwise, execute step S221.

6. The method for converting a relational database to a graph database according to claim 1, characterized in that: The method for generating the index corresponding to the graph database in step S2 includes: Step S231, obtaining the table name corresponding to the model file, and establishing a point index with the table name corresponding to the model file; Step S232, checking the composite index and the joint index in the model file, and establishing a point attribute index; Step S233, traverse all fields of the model file; Step S234, determine whether the current field is a foreign key. If it is a foreign key, create an edge index by combining the table name with the field name, and execute step S235; if it is not a foreign key, determine whether the current field is an index. If it is an index, create a point attribute index by combining the table name with the field name; if it is not an index, execute step S235; Step S235, determine whether all fields have been traversed, if so, the indexing is completed; otherwise, return to step S233.

7. The method for converting a relational database to a graph database according to claim 1, characterized in that: Step S3 comprises, Step S31, traverse all fields under the model file to find the primary key field and the foreign key field; Step S32, initializing a cursor using information of the primary key field and the foreign key field, and determining a query language for a paging query through the cursor; Step S33, executing the query language, obtaining the instance set of the model file, and determining whether there is unmigrated data. If so, executing step S34; if not, executing step S3 is completed; Step S34, traversing the instance set, obtaining corresponding values ​​of the primary key field and the foreign key field, respectively obtaining the identifier of the current point and the identifier of the pointed point, and determining the edges and the relationship between the edges; Step S35, obtaining corresponding values ​​according to all fields of the model file, and obtaining attribute information of the current point according to the field relationship mapping dictionary; Step S36, determine whether the traversal is completed. If so, generate and execute insert statements for points and edges. Update the cursor information through the corresponding value of the primary key field. Determine the query language for paging query through the updated cursor and return to step S33; if not, return to step S34.

8. The method for converting a relational database to a graph database according to claim 7, characterized in that: In step S31, when traversing the model file, the fields of the model file, the table name associated with the foreign key field, and the field type relationship are saved in the field relationship mapping dictionary.

9. The method for converting a relational database to a graph database according to claim 7, characterized in that: Step S31 includes, Step S311, traverse all fields under the model file; Step S312, determine whether it is a primary key, if not, execute step S313; if yes, check whether the primary key field is in the joint unique index, if yes, determine it as a joint primary key; otherwise, determine it as a single primary key; Step S313, determine whether it is a foreign key, if not, return to step S311; if yes, obtain the foreign key field and the associated table name and field type of the foreign key field.

10. The method for converting a relational database to a graph database according to claim 1, characterized in that: Step S4 comprises, Step S41, extracting metadata from the relational database, establishing a mapping relationship between the relational database and the graph database, verifying the correspondence between the points and the edges, and outputting a verification success result if they correspond; and outputting a verification failure result if they do not correspond; Step S42, counting the amount of data in each table in the relational database and counting the amount of data in each point in the graph database, and determining whether the amounts of data are the same, if so, outputting a verification success result; otherwise, outputting a verification failure result; Step S43, perform data query, compare the query results in the relational database and the graph database to see if they are consistent, if so, output a verification success result; otherwise, output a verification failure result.

Images