Automatic migration from document databases to relational databases

Abstract

A technique is provided for automatically migrating documents from a document database to a relational database. In one technique, it is determined whether a set of documents from a document database system can be stored in a relational database system. If so, one or more entities to be normalized are identified based on the hierarchical structure of the set of documents. One or more scripts are generated based on the identified one or more entities. In a related technique, a set of documents from a document database system is stored. It is verified that the set of documents can be converted into one or more binary views. The data of the set of documents is normalized for storage in the relational database system. A script is generated that, when executed, generates the one or more binary views.

Description

Technical Field

[0001] This disclosure relates to databases, and more particularly to document migration from one type of database to another. Background Technology

[0002] Document databases are popular with developers because they make it easy to retrieve and store hierarchically organized data corresponding to application-level language objects. For example, documents (such as JSON documents) are a developer-friendly data access format that provides a flexible schema. The JSON document model avoids the need for decomposition or reconstitution and the complexity that comes with it. The downside of document databases is that, as a data storage format, they have significant limitations, especially as the complexity of applications accessing document databases increases.

[0003] A new feature in database technology known as "duality views" allows data to be stored in relational tables and accessed as JSON documents. In this way, this new feature combines the benefits of the relational world and the document world while avoiding their respective drawbacks. This new feature provides database developers with flexibility and simplicity. For this new feature to be successful, there should be a simple mechanism for users to migrate their data and applications from document databases to relational databases.

[0004] However, several challenges exist in supporting this mechanism. For example, binary views are declarative. In other words, users must define their relational schema and declare tables and binary views on that schema. Generally, not only is this definition difficult, but developers using document databases may also be unfamiliar with relational concepts and declarative programming. For such developers, figuring out the optimal schema and view definitions for their respective data is not easy. Additionally, for such developers, seamlessly migrating their applications and data to relational databases is challenging.

[0005] Current methods for migrating data from document databases to relational databases involve several complex, time-consuming, and often frustrating manual steps.

[0006] The methods described in this section are permissible methods, but not necessarily methods previously conceived or employed. Therefore, unless otherwise indicated, any method described in this section should not be considered prior art simply because it is included in this section. Attached Figure Description

[0007] Figure 1 This is a flowchart depicting the example system architecture in the embodiments; Figure 2 It is a simplified diagram depicting the sample set of departments, including multiple documents involving different departments; Figure 3 It is a simplified diagram depicting the sample set of employees, including multiple documents involving different employees; Figure 4 This is an example of a global pattern generated by the validator based on the employee set in the embodiment; Figure 5 This is a block diagram depicting two example pattern trees in the embodiments, one pattern tree for each document collection; Figure 6 It is a block diagram depicting the mapping between attributes from two nodes of the pattern tree in an embodiment; Figure 7 It is a block diagram depicting example normalized entities based on this mapping in the embodiments; Figure 8 It is a block diagram depicting another example of a normalized entity based on another document set in the embodiment; Figure 9 It is a block diagram depicting the foreign key relationship between two normalized entities in the embodiment; Figure 10 This is a block diagram depicting the entity-relationship (ER) model based on two normalized entities in the embodiment; Figure 11 This is a block diagram illustrating a computer system on which embodiments of the present invention can be implemented; Figure 12 This is a block diagram of a basic software system that can be used to control the operation of a computer system, as described in the embodiment. Detailed Implementation

[0008] In the following description, numerous specific details are set forth for purposes of explanation in order to provide a thorough understanding of the invention. However, it will be clear that the invention can be practiced without these specific details. In other instances, well-known structures and devices are illustrated in block diagram form to avoid unnecessarily obscuring the invention.

[0009] General Overview

[0010] A system and method are provided for automatically migrating documents from a document database to a relational database. In one technique, a three-stage process is implemented to ensure successful migration: (1) verifying that the document collection can be converted into a binary view, (2) normalizing the document collection into a relational table, and (3) generating scripts that, when executed, create tables, indexes, and / or binary views.

[0011] The embodiments improve upon techniques involving migrating documents from one type of database to another type of computer-related database. The embodiments are automated, fast, and hide many of the complexities of relational databases from developers. After documents are migrated to a relational database, users gain the benefits of relational databases, such as normalization, ACID properties, and security features. While the embodiments automate the most difficult and time-consuming steps in the migration process from a document database to a relational database with binary views, additional embodiments allow users to modify the automatically generated schema or entity-relational model before creating relational tables and binary views. In summary, the embodiments significantly lower the barriers for application developers to migrate documents from document databases to relational databases.

[0012] System Overview

[0013] Figure 1 This is a flowchart depicting an example system architecture 100 in an embodiment. System architecture 100 includes a document database (DDB) system 110, an automated document migration (ADM) system 120, and a relational database (RDB) system 130. ADM system 120 includes a validator 122, a normalizer 124, and a schema generator 126. Each of the validator 122, normalizer 124, and schema generator 126 can be implemented in software, hardware, or any combination of software and hardware. While the foregoing description indicates that the validator 122, normalizer 124, and schema generator 126 perform specific tasks or operations, ADM system 120 can perform those tasks / operations with more or fewer components or with the same components in different combinations of those tasks / operations.

[0014] DDB system 110 exports (or sends) one or more document collections to ADM system 120. An example of DDB system 110 is MongoDB. A document collection can involve one or more sets of documents. Each set of documents can be exported in a single transport via a single network connection. If multiple sets of documents exist, then each set of documents can be exported in different transports via a single network connection or via different network connections. Therefore, DDB system 110 can be communicatively coupled to ADM system 120 via one or more computer networks, such as a local area network (LAN), wide area network (WAN), or the Internet. Similarly, RDB system 130 can be communicatively coupled to ADM system 120 via one or more similar computer networks.

[0015] A document collection can include documents of the same format or documents of different formats. (Document format refers to the number of elements in the document, how the elements are organized in the document, and / or the data type of those elements.) For example, a document collection may include one or more documents containing employee data records formatted in a particular way, and another document collection may include one or more documents containing department data records formatted in a different way. As another example, a single document collection may include documents containing employee data records and documents containing department data records.

[0016] Overview of Automated Document Migration Systems

[0017] ADM system 120 (from DDB system 110) receives a document collection and performs operations on the documents in the document collection. These operations correspond to actions performed by components of ADM system 120, such as validation, normalization, and schema generation. Generally, ADM system 120 validates (or determines) whether the document collection can be converted into a relational format and a binary view, normalizes the document collection into relational tables (including deduplication of data), and generates database language (e.g., SQL) scripts to create tables, indexes, and / or binary views. Therefore, ADM system 120 performs the most challenging tasks in the document migration process. The primary task for users of ADM system 120 is to specify the location of the document collection, such as providing ADM system 120 with input specifying the (physical or logical) storage location of the document collection (or a subset thereof). In response, ADM system 120 performs a migration or generates and presents to the user scripts used to generate one or more database objects storing relational data derived from the document collection.

[0018] Figure 2 This is a simplified diagram depicting the example department set 200, which includes multiple documents 210-230 about different departments. The department set can be stored in a single file or in multiple files, with one file for each department document. In this example set, three departments are identified and data about each of these three departments is included. For example, each department document includes data about two employees.

[0019] Figure 3 This is a diagram depicting the sample employee set 300, including multiple documents 310-360 about the different employees. The employee set can be stored in a single file or in multiple files, with one document per employee. In this sample set, six employees are identified and data about each of these six employees is included. For example, each employee has an identifier, name, job title, and department.

[0020] Pattern Validation

[0021] Validator 122 performs schema validation on each document collection. A schema is the organization or structure of data in a database. A schema indicates (1) the relationships between objects and between objects and fields, (2) object names and field names, and (3) field types. For example, an object has a name and one or more fields, each with a name and a field type. An object can be a parent object, a child object, or both. A parent object is an object that has one or more child objects. A child object is an object that has a parent object. An object can be both a parent object (of one object) and a child object (of another object). A parent object can be a root object, meaning that the parent object is not a child object of any other object. A child object can be a leaf object, meaning that the child object is not a parent object of any other object. Examples of field types (or field types) include numbers, dates, and strings.

[0022] Validator 122 determines whether the document set has a consistent pattern. In other words, validator 122 determines whether a global pattern exists that all documents in the set satisfy. For example, a document set that passes the validation phase should not contain both employee documents and department documents.

[0023] Given a set of documents, the following conditions must be met: (1) a sufficient number of documents in the set satisfy a global schema (as described herein, the existence of at least one low-entropy field); and (2) there is sufficient overlap between the fields of the documents in the set. If at least one of these conditions is not met, then the global schema of the documents in the set does not exist and the validation fails. For example, the first employee document includes a field named "Job Title," while the second employee document does not include the "Job Title" field. Continuing this example, the second employee document includes a "Department Name" field, while the first employee document does not include the "Department Name" field. Additionally, both employee documents include an employee identifier, a name, and a department identifier. Therefore, there exists a global schema that satisfies all documents in the set, and there is overlap between the fields of the documents in the set.

[0024] The following is pseudocode for generating a global schema for a collection of documents: INIT global_schema to NULL #Constructing patterns for collections FOR each document in the collection FOR each field in the document IF field is in NOT in global_schema / / Check the complete path (with / / Whether any type already exists in global mode ADD<path, type> to global_schema ELSE ADD type to list of types for the field ENDIF ENDFOR ENDFOR # Prune global_schema to build the final global schema FOR each field in the global_schema REMOVE field if it appeared in a small % of docs REMOVE field if it has more than one type with >5% frequency KEEP the field type with highest frequency ENDFOR Examples of paths in employee documentation include " / ", " / empId", and " / dept / deptId". Therefore, a path can include a root indicator (such as " / ") and a series of one or more field names separated by " / ". Examples of field types include number, date, string, and object, where object is a field type that includes one or more other fields.

[0025] Figure 4 This is an example of a global schema 400 generated by verifier 122 based on the employee set using instructions based on the pseudocode above. Global schema 400 indicates the structure (e.g., relationships between fields) and the data type of each field. If not explicitly stated in a document, the data type of an entity in the document can be inferred, such as determining whether the value is a number, date, timestamp, time interval, mutable character, or string. However, for use in... Figure 4 In the global visualization mode 400, the data can simply be a set of <path, type> tuples. Furthermore, each <path, type> tuple can be associated with a frequency value indicating how many times that tuple appears in the document collection.

[0026] Global mode 400 includes a root node 410 representing an employee document, four child nodes 410-440 (three of which are child attribute nodes corresponding to non-object fields of the employee document, and one of which is a child object node corresponding to an object field of the employee document), and three grandchild nodes 442-446, or child nodes of child object node 440.

[0027] In the pseudocode example above, fields with high entropy are removed. High-entropy fields cannot be normalized to relation columns. A field has (or is) high entropy if it appears in a small percentage of documents (e.g., less than 10%) or if it is associated with multiple types, each occurring more frequently than a certain frequency. For example, if a field name is associated with a string type in 80% of all instances of that field name in the document collection and with a numeric type in the remaining 20% ​​of all those instances, then that field name will not be considered and therefore will not be a field or column in any resulting relation schema.

[0028] Handling fields with high entropy offers at least two options: (a) report documents containing such fields to users or administrators (and optionally, validate the remaining documents); or (b) (via flexible field support) place all such fields in flexible JSON columns. The following pseudocode illustrates a variation of option (a): INIT reject_count to 0 FOR each document in the collection IF json_schema(document) does NOT match global_schema RECORD document in error_log reject_count++ IF reject_count > config.rejectLimit FAIL validation ENDIF ENDIF ENDFOR Therefore, if a document from the document set (used to generate the global schema) does not match the global schema (“global_schema”) constructed by validator 122, the rejection count (which is initialized to zero) is incremented by one. If the rejection count exceeds the rejection limit, validation fails and the normalization and schema generation steps are skipped. If a document contains at least one field not found in the global schema, then the document does not match the global schema. For example, if the field “job title” is removed due to high entropy (e.g., the field is associated with a different data type more than 5% of the time) and the document contains the field “job title”, then the rejection count is incremented by one.

[0029] In the example pseudocode above where validation might fail, any failed documents are stored in the error log. In this option, users or system administrators can review the error log and determine whether to modify the global schema or documents to ensure document matching.

[0030] In option (b), no validation step is required. Instead, flexible field support is used to support the ingestion of high-entropy fields, where a "flex" JSON column is added at each level containing such a high-entropy field, and these fields are automatically embedded inside the "flex" JSON column. Below is an example table creation statement with "flex" JSON columns added to support and store high-entropy fields: CREATE TABLE author ( ID number, first varchar(128), last varchar(128), extras JSON(object), constraint pk1 primary key(id) ); The following is an example statement for creating a binary view, with an added "Flex" JSON column to support and store high-entropy fields: CREATE JSON Duality View authorV as author @insert @update @delete { _id : id, firstName : first, lastName : last, extras @flex } Normalization After validating the document set, normalizer 124 normalizes the data in the document set to prepare it for relational consumption or storage. Normalization generally involves deduplicating data across fields and identifying non-overlapping entities and the relationships between them. The output of this process is a set of normalized entities, their relationships, and the mapping from document fields to relational fields.

[0031] More specifically, normalization involves using the structure of the input document set as a guide to identify entities to be normalized. Each node in the global schema with non-null children becomes a candidate for normalization. All such nodes are added to the normalization set. Then, for each node in this normalization set, the node's primary key (PK) is identified. If no PK is found, an automatically generated identity PK column can be added to the node. Then, for each pair of nodes in the normalization set, it is determined whether the nodes represent the same entity. If so, the pair of nodes is combined into a single supernode.

[0032] Based on these steps, normalization comprises six stages: constructing a schema tree for each document collection, identifying candidate nodes to be normalized (where hierarchical information is used to identify which fields should be grouped in a single entity), identifying the primary key (PK) of each candidate entity or node, normalizing candidate entities, identifying foreign key (FK) references between entities, and constructing an entity-relationship (ER) model and mapping between document fields and entity attributes. These six stages are described in more detail in this paper.

[0033] Normalization Phase 1: Constructing the Pattern Tree

[0034] Figure 5 This is a block diagram depicting two example pattern trees 500 and 550 in the embodiments, one pattern tree for each document set. Pattern trees 500 and 550 can be generated by validator 122 using the processing described herein.

[0035] The employee schema tree 500 includes a root node 502, three sub-attribute nodes 510-530 (each of which corresponds to a field in the employee object), and a child object node 540 (which corresponds to the department object). This child object node includes three sub-attribute nodes 542-546 (each of which corresponds to a field in the department object). Child object node 540 is a singleton descendant, meaning that if a department object is a child node of an employee node, then only one department object exists.

[0036] The department schema tree 550 includes a root node 552, four sub-attribute nodes 560-575 (each of which corresponds to a field of a department object), and a child object node 580 (which corresponds to one or more employee objects). This child object node 580 includes two sub-attribute nodes 582-584 (each of which corresponds to a field of an employee object). The child object node 580 is an array descendant, meaning that if an employee object is a child of a department node, then multiple employee objects can exist.

[0037] Normalization Phase 2: Identifying candidate nodes for normalization

[0038] Given pattern trees 500 and 550, identify candidate nodes for normalization. A candidate node is each parent node that has non-empty children associated with one or more child nodes. Therefore, in pattern trees 500 and 550, each of nodes 502, 540, 552, and 580 is a candidate node because each of those nodes is a parent node.

[0039] In some scenarios, a document or entity may contain non-nested attributes from another document or entity. For example, an employee entity or document may contain a department identifier, department name, and department budget, all of which are at the same level as the employee identifier and employee name.

[0040] To handle this scenario of unnested attributes, a functional dependency tree (up to a depth of 2) is generated within the child objects, where the root is the primary key (PK). A functional dependency between two attributes (or fields) means that, given the value of one attribute, it is possible to accurately determine the value of the other. Based on this automated analysis given the example above, a functional dependency tree (FDT) can be generated, where the root of the FDT is the employee identifier (i.e., the PK), the department identifier is a child attribute of the root, and the department name and budget are child attributes of the department identifier.

[0041] Next, each subtree (at depth 1) can be considered an unnested entity if and only if (1) the difference between the cardinality (or the number of unique values) between the root and the subtree is greater than a certain threshold (e.g., at least twice) and (2) the subtree has at least one field at depth 2 (relative to the root in the FDT). For example, (1) is satisfied if there are at least two employees in each department (as indicated in the employee documentation). In other words, the number of unique employee identifiers will likely be much greater than the number of unique department identifiers. On the other hand, if there is a closer one-to-one relationship between employee identifiers and department identifiers, then it makes no sense to create another table to store department data, which would be redundant with the department data in the employee documentation / records.

[0042] In some scenarios, documents or entities may contain nested attributes within the same entity. For example, an employee entity or document might contain a name entity that is itself an object and includes a first name field and a last name field. In such scenarios, it would be beneficial to treat the nested entity as a set of nested attributes of the parent entity, rather than creating a separate relational table for the nested entity. This process would involve removing the nested entity and including its attributes as direct children of the parent entity. This "unnesting" is performed if (a) the nested entity has no primary key (PK) or (b) the cardinality difference between the parent entity and the nested entity is less than a certain threshold (e.g., 2).

[0043] Normalization Stage 3: Identify the PK of each candidate node

[0044] In Phase 3, a primary key is identified for each candidate node. To do this, for each candidate node, all ordered pairs of its attributes are considered, and it is determined whether a functional dependency exists between each pair. If the "strength" of the dependency between two attributes is greater than a certain threshold... Therefore, there is a functional dependency between attributes a1 and a2. For example, strength(a1, a2) > where strength() is a function that measures dependencies and This is a specific threshold, such as 0.99. An example of the intensity function is as follows: strength(a1, a2) = (# the number of unique values ​​of a1) / (# the number of unique (a1, a2) value pairs) If the input set uses composite keys, then this functional dependency check will not work. To address this scenario with composite keys, if no single attribute is the primary key (PK), then consider groups of two (or more) attributes as candidate PKs.

[0045] A PK (Primary Key) attribute is an attribute that has a functional dependency on all other attributes of a candidate node. If such an attribute does not exist, then the candidate node has no PK and cannot be normalized to a separate entity. In this case, the candidate node can be considered as a nested set of attributes of its parent entity. On the other hand, if multiple attributes satisfy the PK definition, then the following rule can be used to break a tie: a. Preference for attributes with higher intensity values b. Prefer attributes with higher entropy values ​​(the more unique an attribute is, the higher its entropy value); the PK field should have high entropy because it is expected to have primarily unique values ​​(PK fields may not all be unique due to noise or duplicate data in the data; for example, several employees may belong to the same department, resulting in duplicate department information across employee documents. Therefore, not all department ID values ​​across employee documents will be unique). c. Preference for attributes with low variance (e.g., a stock price may be unique across many stock objects, but the price may have a large variance, unlike employee identifiers; variance can be the variance of attribute values ​​stored in bytes). d. Preference for attributes whose names end with id, Id, or ID. If multiple attributes are PK candidates, then one is selected as the PK of the entity in question, and the remaining attributes can be identified as unique keys (UK). Given Figure 5 For node 502, the following table lists the strength measurements for each pair of attributes:

[0046] Table A

[0047] Based on Table A, the attributes “empId” and “name” are functionally dependent on all other attributes of node 502, and both attributes meet the criteria for being the primary target (PK). The tie between “empId” and “name” is broken using Rule 3 (which favors attributes with lower variance in their individual values), resulting in the selection of “empId” as the PK.

[0048] Using this PK identification process, given that... Figure 5 ,for Figure 5 Candidate nodes in the dataset are identified by the following PKs:

[0049] Table B

[0050] Normalization Phase 4: Normalizing Candidate Entities

[0051] After identifying the PK (Primary Key) for candidate nodes, candidate entities are normalized. If a one-to-one relationship exists between at least one pair of attributes of N1 and N2, then the two nodes N1 and N2 can be normalized into a single node (or entity). Generally, their joins are performed based on the equality of the PK (or UK) of N1 and N2. If a one-to-one correspondence is found between the attributes in the join result, then N1 and N2 can be grouped into a single relation.

[0052] For example, here's one way to normalize node 502 (referred to as node1 in table C) and node 580 (referred to as node4 in table C) into a single entity. First, a join of node1 and node4 is performed based on their equality with respect to the PK (i.e., node1.empId = node4.empId).

[0053]

[0054] Table C

[0055] The number of rows in the join result should be at least (1-alpha)% of the size of the number of data points in the larger of the two nodes. Alpha can be a high value, such as 0.99. Furthermore, the size of the join result should be a significant percentage (e.g., >98%) of the size of the data contained in the smaller of the two nodes. The size of the data contained in a node is the number of data points in the node. For example, for an employee node, this size would be the number of employee objects. This latter condition eliminates matches caused by minute, coincidental overlaps in the domains of the parent attributes of the subtrees.

[0056] After generating the join results, a matrix (attrMatrix) is constructed, which has one cell for each pair of attributes from each node. This construction is performed by iterating over each row (R) in the join results, and incrementing attrMatrix[a1][a2] by 1 if R[a1] = R[a2]. This matrix is ​​presented as table D.

[0057]

[0058] Table D

[0059] After constructing attrMatrix, determine the mapping between node1 and node4, such that...

[0060] {(attrMatrix[a1][a2]) / (#number of records in the join set)} ≥

[0061] Where a1 is an attribute of node1 and a2 is an attribute of node4, and It is a threshold (e.g., 0.99), and

[0062] attrMatrix[a1][a2] ≥ attrMatrix[a1][a2']

[0063] For all other attributes a2' in node4, this latter condition ensures that the identified a2 has a higher value than other attributes in the same node.

[0064] Based on attrMatrix, a one-to-one mapping can be constructed between node1 and node4, where node1.empId maps to node4.empId and node1.name maps to node4.empname, but node1.designation does not map to any attribute in node4. This mapping is in Figure 6 As shown in the image.

[0065] based on Figure 6 The mappings indicated in the document, node1 and node4 (corresponding to nodes 502 and 580), can be normalized to a single entity, such as... Figure 7 As indicated in entity 700 depicted in the diagram. Entity 700 includes three attributes, which correspond to the three attributes of node1 and the two attributes of node4.

[0066] Similarly, node2 and node3 (corresponding to nodes 540 and 552) can be normalized to a single entity, such as Figure 8As indicated in entity 800 depicted in the diagram. Entity 800 includes four attributes, which correspond to the three attributes of node2 and the four attributes of node3.

[0067] Normalization Phase 5: Identifying FK references between entities

[0068] After normalizing two or more entities, the relationships between the normalized entities are considered in (JSON) schema. For a one-to-many relationship, the parent entity's key (PK) is added as the first key (FK) to the child entity. For a one-to-one relationship, the child entity's PK is added as the first key (FK) to the parent entity. For a many-to-many relationship, a mapping table with two columns can be created: the parent entity's PK is added as the first key (FK) to the child entity, and the child entity's PK is added as another FK. The combination of these two FKs can be tagged as the PK of the mapping table.

[0069] exist Figure 7 and Figure 8 In the example normalized entity, there is a one-to-many relationship between the department entity and the employee entity. The type of relationship (one-to-many or one-to-one) can be determined by analyzing one or more documents in one or more document collections and determining that the department documents include an array of employee objects rather than a single employee object. Therefore, normalizer 124 (such as...) Figure 9 As indicated in the document, the PK of department entity 800 is added as FK 902 to employee entity 900 (except that employee entity 900 corresponds to entity 700, which includes a department identifier to identify the department to which the employee belongs). In a one-to-one relationship, the parent entity can be determined based on the hierarchy of entities in a document (e.g., JSON) collection. For example, in an employee collection, the employee entity is the parent entity and the department entity is the child entity because in the JSON input, the department is a child object of the employee root object.

[0070] Normalization Stage 6: Constructing the ER Model

[0071] In stage 6 of the normalization process, normalizer 124 constructs the entity-relationship (ER) model and the mapping between document fields and entity attributes. Based on the JSON schema of each collection and the normalized entities, normalizer 124 constructs ER model 1000, such as... Figure 10 As described in the document. Furthermore, normalizer 124 constructs a mapping table (Table E) between the following document fields and table columns.

[0072]

[0073] Table E

[0074] Pattern generation

[0075] Schema generator 126 generates recommendations for table creation statements and / or binary view creation statements based on validated schemas and normalized entities. In relevant embodiments, a user (e.g., a database administrator) overrides one or more recommendations. Alternatively, user intervention is not required.

[0076] Regardless of the method used, schema generator 126 automatically generates one or more tables and / or binary views, for example, by executing create table statements and / or create binary view statements. After these relational database objects are generated, the database application seamlessly migrates to a relational database system (e.g., RDB system 130).

[0077] In this embodiment, users of the DDB system 120 are able to run their existing applications on top of these automatically generated binary views without significant changes (or without any changes).

[0078] The following is pseudocode for generating a table creation statement in Structured Query Language (SQL), a Data Definition Language (DDL) statement type. The example is not limited to SQL and can be applied to any database query language.

[0079] FUNCTION GENERATE_TABLE_DDL(SCHEMA_NODE)

[0080] PRINT 'CREATE TABLE ', SCHEMA_NODE.table_name, '('

[0081] FOR COLUMN in SCHEMA_NODE.column_list

[0082] PRINT COLUMN.name, ' ', COLUMN.type

[0083] IF (COLUMN.isPk)

[0084] PRINT 'PRIMARY KEY'

[0085] ENDIF

[0086] PRINT ', ' / / Except for the last column

[0087] ENDFOR

[0088] FOR FK in SCHEMA_NODE.fk_list

[0089] PRINT ', CONSTRAINT ', FK.name, ' FOREIGN KEY '

[0090] PRINT '(', FK.column_name, ')'

[0091] PRINT ' REFERENCES ', FK.target

[0092] PRINT '(', FK.target_column_name, ')'

[0093] ENDFOR

[0094] PRINT ');'

[0095] ENDFUNCTION

[0096] A schema_node is an object that contains data about a node in the generated schema tree. A schema_node has a type, such as root node, parent node, child node, or field. If it is a root node, then the schema_node indicates the table name and key data, such as which field is a private key or a foreign key. If the schema_node is a node with fields, then the schema_node will indicate the names of the fields.

[0097] Given the normalized employee entity described in this article as input, the above pseudocode will generate the following DDL statement: CREATE TABLE employee ( emp_id number PRIMARY KEY, name varchar2(50), designation varchar2(50), dept_id number, CONSTRAINT emp_fk FOREIGN KEY(dept_id) references department(dept_id) ); Additionally, given the normalized departmental entity described in this paper as input, the same pseudocode will generate the following DDL statement: CREATE TABLE department ( dept_id number PRIMARY KEY, name varchar2(50), budget number location varchar2(100) ); The following is pseudocode used to generate an index creation statement in SQL to create an index on a foreign key: FUNCTION GENERATE_INDEX_DDL(SCHEMA_NODE) FOR FK in SCHEMA_NODE.fk_list PRINT 'CREATE INDEX ', FK.name, '_index ' PRINT 'ON ', SCHEMA_NODE.table_name PRINT '(', FK.column_name, ')' ENDFOR ENDFUNCTION Given the normalized employee entity described in this paper as input, the above pseudocode will generate the following DDL statement (because the normalized employee entity has a foreign key, but the normalized department entity does not): CREATE INDEX emp_fk_index on employee(dept_id); The following is pseudocode used to generate a statement for creating a binary view in SQL: FUNCTION GENERATE_VIEW_DDL(SCHEMA_NODE) PRINT 'CREATE JSON RELATIONAL DUALITY VIEW ' PRINT SCHEMA_NODE.collection_name PRINT 'AS' CALL UNPARSE_NODE(SCHEMA_NODE) PRINT ';' ENDFUNCTION FUNCTION UNPARSE_NODE(SCHEMA_NODE) SWITCH (SCHEMA_NODE.type) CASE NODE: PRINT SCHEMA_NODE.alias_name, ' : ' CASE ROOT: PRINT SCHEMA_NODE.table_name, ' { ' FOR child in SCHEMA_NODE.children CALL UNPARSE_NODE(child) PRINT ', ' / / Except for the last child ENDFOR PRINT '} ' BREAK CASE FIELD: PRINT SCHEMA_NODE.alias_name, ' : ' PRINT SCHEMA_NODE.column_name BREAK ENDSWITCH ENDFUNCTION Binary views allow documents to be stored in a relational format, but accessed by applications in their native format (e.g., JSON). In this way, even if a document is actually stored in a relational format, applications accessing the document in its native format can still do so. Using binary views, the document presented to the user by the application will look the same as the document sent from DDB system 110 to ADM system 120. Given the normalized employee entity described in this paper as input, the above pseudocode will generate the following DDL statement: CREATE OR REPLACE JSON DUALITY VIEW employee_dv AS employee { empId: emp_id name: name designation: designation dept: department { deptId: dept_id deptName: name budget: budget } }; The data to the left of the colon is the field name in the original document, while the data to the right of the colon is the corresponding field name in the relational table, such as column name or table name.

[0098] Additionally, given the normalized departmental entity described in this paper as input, the same pseudocode will generate the following DDL statement: CREATE OR REPLACE JSON DUALITY VIEW department_dv AS department { deptId: dept_id name: name budget: budget location: location employees: employee [{ empId: emp_id empName: name }] }; Application Interface Documents from DDB system 110 can be imported into RDB system 130 in one or more ways. For example, an export API (e.g., "mongoexport") specifying a collection and optionally a set of documents, and an import API (e.g., "mongoimport") specifying the document database, user credentials, and the collection / documents to be exported, can be used to import one or more document (e.g., JSON) collections into a set of tables with a single (e.g., JSON) column.

[0099] After the document collection is stored in a single JSON column in a table in RDB system 130, a set of functions is executed to determine the relational schema of the input collection. This set of functions can be part of a PL / SQL package. Two or more functions can be used to validate, normalize, and generate the table schema. Alternatively, a single function can be used to perform each of these main operations. The advantage of having two or more functions is that it allows users / administrators to view and modify the ER model from the output of one or more validation and normalization functions. For example, a user can add or remove fields, add or remove FKs, add or modify relationships, etc. The user can then specify the modified ER model as input to another function (e.g., a schema generation function), which will automatically generate and execute DDL statements based on the modified ER model.

[0100] After generating the schema, document data from a single (e.g., JSON) column is moved to one or more binary views. For example, employee and department documents are moved from a single JSON column to multiple binary views. This can be done using an "insert as select" statement for each collection of documents.

[0101] The following code demonstrates how to use the application interface: DECLARE schema varchar2(32767); BEGIN schema := dbms_json_duality.infer_and_generate_schema(json_object('collectionNames' : ['emp', 'dept'])); execute immediate schema. END; In relevant embodiments, a graphical user interface (GUI) presents buttons or other selectable graphical elements that, when selected, perform the operations described herein. In this way, the user is not required to specify any code. Instead, the user may only be required to (1) specify the location where one or more document collections are stored and the target database in which the one or more document collections are stored, and (2) select a graphical button.

[0102] Hardware Overview

[0103] According to one embodiment, the technology described herein is implemented by one or more dedicated computing devices. The dedicated computing device may be hardwired to execute the technology, or may include digital electronic devices (such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs) that are permanently programmed to execute the technology, or may include one or more general-purpose hardware processors programmed to execute the technology according to program instructions in firmware, memory, other storage devices, or combinations thereof. Such a dedicated computing device may also implement the technology by combining custom hardwired logic, ASICs, or FPGAs with custom programming. The dedicated computing device may be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hardwired and / or program logic to implement the technology.

[0104] For example, Figure 11This is a block diagram illustrating a computer system 1100 on which embodiments of the present invention may be implemented. The computer system 1100 includes a bus 1102 or other communication mechanism for transmitting information and a hardware processor 1104 coupled to the bus 1102 for processing information. The hardware processor 1104 may be, for example, a general-purpose microprocessor.

[0105] Computer system 1100 also includes main memory 1106, such as random access memory (RAM) or other dynamic storage devices, coupled to bus 1102 for storing information and instructions to be executed by processor 1104. Main memory 1106 may also be used to store temporary variables or other intermediate information during the execution of instructions to be executed by processor 1104. When such instructions are stored in non-transitory storage media accessible to processor 1104, computer system 1100 becomes a dedicated machine customized for performing the operations specified in the instructions.

[0106] Computer system 1100 also includes a read-only memory (ROM) 1108 or other static storage device coupled to bus 1102 for storing instructions and static information for processor 1104. Storage devices 1110, such as disks, optical discs, or solid-state drives, are provided and coupled to bus 1102 for storing information and instructions.

[0107] Computer system 1100 may be coupled via bus 1102 to a display 1112, such as a cathode ray tube (CRT), for displaying information to a computer user. Input device 1114, including alphanumeric keys and other keys, is coupled to bus 1102 for transmitting information and command selections to processor 1104. Another type of user input device is a cursor control 1116, such as a mouse, trackball, or cursor arrow keys, for transmitting directional information and command selections to processor 1104 and for controlling cursor movement on display 1112. Such input devices typically have two degrees of freedom on two axes (a first axis (e.g., x) and a second axis (e.g., y)) to allow the device to specify a position in a plane.

[0108] Computer system 1100 may implement the techniques described herein using custom hard-wired logic, one or more ASICs or FPGAs, firmware, and / or program logic. This custom hard-wired logic, one or more ASICs or FPGAs, firmware, and / or program logic, combined with the computer system, enable computer system 1100 to become a special-purpose machine or to program computer system 1100 as a special-purpose machine. According to one embodiment, the techniques described herein are executed by computer system 1100 in response to processor 1104 executing one or more sequences of one or more instructions contained in main memory 1106. Such instructions may be read into main memory 1106 from another storage medium, such as storage device 1110. Execution of the sequence of instructions contained in main memory 1106 causes processor 1104 to perform the processing steps described herein. In alternative embodiments, hard-wired circuitry may be used instead of software instructions or in combination with software instructions.

[0109] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular manner. Such storage media can include non-volatile media and / or volatile media. Non-volatile media include, for example, optical discs, magnetic disks, or solid-state drives, such as storage device 1110. Volatile media include dynamic memory, such as main memory 1106. Common forms of storage media include, for example, floppy disks, flexible disks, hard disks, solid-state drives, magnetic tape or any other magnetic data storage media, CD-ROMs, any other optical data storage media, any physical media with a perforated pattern, RAM, PROMs and EPROMs, FLASH-EPROMs, NVRAMs, any other memory chips, or magnetic tape cassettes.

[0110] Storage media differ from transmission media but can be used in conjunction with them. Transmission media participate in the transfer of information between storage media. For example, transmission media include coaxial cables, copper wires, and optical fibers, including wires containing bus 1102. Transmission media can also take the form of sound waves or light waves, such as those generated during radio wave and infrared data communication.

[0111] Various forms of media can involve carrying one or more sequences of one or more instructions to processor 1104 for execution. For example, the instructions may initially be carried on a disk or solid-state drive of a remote computer. The remote computer may load the instructions into its dynamic memory and transmit them over a telephone line using a modem. A modem local to computer system 1100 may receive data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector may receive the data carried in the infrared signal, and appropriate circuitry may place the data on bus 1102. Bus 1102 carries the data to main memory 1106, from which processor 1104 retrieves and executes the instructions. The instructions received by main memory 1106 may optionally be stored on storage device 1110 before or after execution by processor 1104.

[0112] Computer system 1100 also includes a communication interface 1118 coupled to bus 1102. Communication interface 1118 provides bidirectional data communication coupled to network link 1120, which connects to local network 1122. For example, communication interface 1118 may be an Integrated Services Digital Network (ISDN) card, a cable modem, a satellite modem, or a modem providing data communication connectivity to a corresponding type of telephone line. As another example, communication interface 1118 may be a LAN card providing data communication connectivity to a compatible local area network (LAN). A wireless link may also be implemented. In any such implementation, communication interface 1118 transmits and receives electrical, electromagnetic, or optical signals carrying digital data streams representing various types of information.

[0113] Network link 1120 typically provides data communication to other data devices via one or more networks. For example, network link 1120 may provide a connection to host computer 1124 or to data equipment operated by Internet Service Provider (ISP) 1126 via local network 1122. ISP 1126 then provides data communication services via a global packet data communication network now commonly referred to as the “Internet” 1128. Both local network 1122 and Internet 1128 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through various networks, as well as signals on network link 1120 and through communication interface 1118, are example forms of transmission media that carry digital data to or from computer system 1100.

[0114] Computer system 1100 can send messages and receive data, including program code, through one or more networks, network links 1120, and communication interfaces 1118. In the Internet example, server 1130 can transmit requested code to the application through the Internet 1128, ISP 1126, local network 1122, and communication interface 1118.

[0115] The received code can be executed by processor 1104 when it is received, and / or stored in storage device 1110 or other non-volatile storage device for later execution.

[0116] Software Overview

[0117] Figure 12 This is a block diagram of a basic software system 1200 that can be used to control the operation of computer system 1100. Software system 1200 and its components, including their connections, relationships, and functions, are intended to be exemplary only and are not intended to limit the implementation of one or more example embodiments. Other software systems suitable for implementing one or more example embodiments may have different components, including components with different connections, relationships, and functions.

[0118] Software system 1200 is provided to instruct the operation of computer system 1100. Software system 1200, which may be stored on system memory (RAM) 1106 and fixed storage device (e.g., hard disk or flash memory) 1110, includes a kernel or operating system (OS) 1210.

[0119] OS 1210 manages the low-level aspects of computer operations, including managing process execution, memory allocation, file input and output (I / O), and device I / O. One or more applications, designated 1202A, 1202B, 1202C…1202N, can be “loaded” (e.g., transferred from fixed storage device 1110 to memory 1106) for execution by system 1200. Applications or other software intended for use on computer system 1100 can also be stored as downloadable computer-executable instruction sets, for example, for downloading and installation from internet locations (e.g., web servers, app stores, or other online services).

[0120] Software system 1200 includes a graphical user interface (GUI) 1215 for receiving user commands and data graphically (e.g., "click" or "touch gestures"). These inputs can then be manipulated by system 1200 according to instructions from operating system 1210 and / or (one or more) applications 1202. GUI 1215 also displays the results of operations from OS 1210 and (one or more) applications 1202, allowing the user to provide additional input or terminate the session (e.g., log off).

[0121] OS 1210 can execute directly on the bare hardware 1220 of computer system 1100 (e.g., one or more processors 1104). Alternatively, a hypervisor or virtual machine monitor (VMM) 1230 can be inserted between the bare hardware 1220 and OS 1210. In this configuration, VMM 1230 acts as a software "buffer" or virtualization layer between the bare hardware 1220 of computer system 500 and OS 1210.

[0122] VMM 1230 instantiates and runs one or more virtual machine instances (“guest machines”). Each guest machine includes a “guest” operating system (such as OS 1210) and one or more applications designed to run on the guest operating system, such as application(s)1202. VMM 1230 presents a virtual operating platform to the guest operating system and manages the execution of the guest operating system.

[0123] In some instances, VMM 1230 can allow a guest operating system to run as if it were running directly on the bare hardware 1220 of computer system 1100. In these instances, the same version of the guest operating system configured to run directly on the bare hardware 1220 can also run on VMM 1230 without modification or reconfiguration. In other words, VMM 1230 can provide full hardware and CPU virtualization to the guest operating system in some instances.

[0124] In other instances, the guest operating system can be specifically designed or configured to run on the VMM 1230 for improved efficiency. In these instances, the guest operating system is "aware" that it is running on the virtual machine monitor. In other words, the VMM 1230 can provide paravirtualization to the guest operating system in some instances.

[0125] Computer system processes include the allocation of hardware processor time, as well as the allocation of (physical and / or virtual) memory, memory allocation for storing instructions executed by the hardware processor, memory allocation for storing data generated by the execution of instructions by the hardware processor, and / or memory allocation for storing hardware processor state (e.g., register contents) between hardware processor time allocations when the computer system process is not running. Computer system processes run under the control of the operating system and can also run under the control of other programs executing on the computer system.

[0126] The basic computer hardware and software described above are intended to illustrate the basic underlying computer components that can be used to implement one or more example embodiments. However, the one or more example embodiments are not necessarily limited to any particular computing environment or computing device configuration. Instead, the one or more example embodiments can be implemented in any type of system architecture or processing environment that a person skilled in the art will understand from this disclosure as capable of supporting the features and functionality of the one or more example embodiments presented herein.

[0127] cloud computing

[0128] This article generally uses the term "cloud computing" to describe a computing model that enables on-demand access to a shared pool of computing resources, such as computer networks, servers, software applications, and services, and allows for the rapid provisioning and release of resources with minimal management effort or service provider interaction.

[0129] Cloud computing environments (sometimes called cloud environments or the cloud) can be implemented in various ways to best suit different requirements. For example, in a public cloud environment, the underlying computing infrastructure is owned by an organization that makes its cloud services available to other organizations or the general public. In contrast, private cloud environments are generally designed for use by a single organization or within a single organization. Community clouds are designed to be shared by several organizations within a community; while hybrid clouds include two or more types of clouds (e.g., private, community, or public) bound together by data and application portability.

[0130] Generally, cloud computing models enable some of the responsibilities that might have previously been provided by an organization's own IT department to be delivered as service layers within the cloud environment for use by consumers (inside or outside the organization, depending on the public / private nature of the cloud). Depending on the specific implementation, the precise definition of the components or features provided by or within each cloud service layer can vary, but common examples include: Software as a Service (SaaS), where consumers use software applications running on cloud infrastructure, while the SaaS provider manages or controls the underlying cloud infrastructure and applications; Platform as a Service (PaaS), where consumers can use software programming languages ​​and development tools supported by the PaaS provider to develop, deploy, and otherwise control their own applications, while the PaaS provider manages or controls other aspects of the cloud environment (i.e., everything in the runtime execution environment); and Infrastructure as a Service (IaaS), where consumers can deploy and run arbitrary software applications and / or provision processing, storage, networking, and other basic computing resources, while the IaaS provider manages or controls the underlying physical cloud infrastructure (i.e., everything below the operating system layer). Database as a Service (DBaaS) is a service in which consumers use database servers or database management systems running on cloud infrastructure, while DbaaS providers manage or control the underlying cloud infrastructure, applications, and servers, including one or more database servers.

[0131] In the foregoing description, embodiments have been described with reference to numerous specific details that vary depending on the implementation. Accordingly, the description and drawings should be considered illustrative rather than restrictive. The sole and exclusive reference to the scope of the invention, and the content that the applicant intends to define as the scope of the invention, is the literal and equivalent scope of the set of claims issued in this application, in the specific form of such claims, including any subsequent corrections.

[0132] In the foregoing description, embodiments have been described with reference to numerous specific details that vary depending on the implementation. Accordingly, the description and drawings should be considered illustrative rather than restrictive. The sole and exclusive reference to the scope of the invention, and the content that the applicant intends to define as the scope of the invention, is the literal and equivalent scope of the set of claims issued in this application, in the specific form of such claims, including any subsequent corrections.

Claims

1. A method comprising: Determine whether a set of documents from a document database system can be stored in a relational database system; In response to determining that the set of documents can be stored in a relational database system, one or more entities to be normalized are identified based on the hierarchical structure of the set of documents; Generate one or more scripts based on the one or more entities; The method is performed by one or more computing devices.

2. The method of claim 1, further comprising: Execute one or more scripts to generate one or more of the following: tables, indexes, or binary views.

3. The method of claim 1, wherein determining whether the set of documents can be stored in a relational database system comprises: For each document in the set of documents: For each field in each document: Determine whether each field is in a set of known fields; If it is determined that each field is not in the set of known fields, then each field is added to the set of known fields.

4. The method of claim 3, further comprising: Determine whether any of the fields in the set of known fields have high entropy values; In response to determining that the field has a high entropy value, the field is removed from the set of known fields.

5. The method of claim 4, wherein determining that a field has a high entropy value comprises: It was determined that the field was found in the document at a specific percentage below a first threshold. or The field is determined to be associated with two field types across the set of documents, and each of the two field types is associated with a percentage of documents in the set of documents that are above a second threshold percentage.

6. The method of claim 3, further comprising: For each document in the set of documents: Determine whether each document matches the set of known fields; In response to determining that each document does not match the set of known fields, the rejection count is modified; The rejection count is used to determine whether the validation of the set of documents failed.

7. The method of claim 4, further comprising: Add the field to the flex JSON column in the table that stores data originating from the set of documents.

8. The method of claim 1, further comprising: In the global mode used for the set of documents, multiple entities with non-empty child nodes are identified as candidates for normalization.

9. The method of claim 8, further comprising: For each of the plurality of entities, identify the primary key of each entity.

10. The method of claim 9, wherein identifying the primary key of each entity comprises: Identify multiple pairs of attributes for each entity: For each of the multiple pairs, determine whether there is a functional dependency between the attributes in each pair.

11. The method of claim 10, further comprising: In the attributes, the first and second attributes are identified as candidate private keys for a specific entity; Apply one or more rules from a set of rules to select either a first attribute or a second attribute as the private key of the specific entity.

12. The method of claim 8, further comprising: For each pair of entities among the plurality of entities: Determine whether each pair of entities represents the same entity; If it is determined that each pair of nodes represents the same entity, then each pair of entities is combined into a single entity.

13. The method of claim 12, wherein determining whether each pair of entities represents the same entity includes determining whether there is a one-to-one relationship between an attribute of the first entity in each pair of entities and an attribute of the second entity in each pair of entities.

14. The method of claim 8, further comprising: Normalize the first entity and the second entity among the plurality of entities; Identify the foreign key relationship between the first entity and the second entity.

15. The method of claim 8, further comprising: Normalize the first entity and the second entity among the plurality of entities; Generate an entity-relationship (ER) model that includes a first entity and a second entity; Generate the mapping between (1) the attributes of the first and second entities and (2) the table columns.

16. The method of claim 1, wherein generating the one or more scripts includes generating a plurality of scripts, the method further comprising: Execute the first script among the plurality of scripts that generates one or more tables; Execute the second script among the plurality of scripts to generate one or more binary views.

17. A method comprising: Stores a set of documents from a document database system; Verify that the set of documents can be converted into one or more binary views; Normalize the data of the set of documents so that they can be stored in a relational database system; Generate a script that generates the one or more binary views when executed; The method is performed by one or more computing devices.

18. One or more non-transitory storage media, storing instructions that, when executed by one or more computing devices, cause to perform the method of any one of claims 1-17.

Images