A relational-based data import method for large-scale multi-modal data

Abstract

A kind of data import method based on relational large-scale multi-mode data, belong to computer technology field;Original data file, storage format and data model are obtained;Through data conversion module, original data file data information is obtained by parsing, reading and structuring;Data information is generated uniform relational data model file by block parallel writing method, and is stored as the same file storage format CSV;Pretreatment is carried out by data association module;Mode information is extracted, and mode information is sent to mode conversion module, and mode conversion module automatically generates relational table and establishes DDL statement;DDL statement and CSV file are sent to SQL processing terminal, and multi-mode data is uniformly converted into relational data by the COPY statement and DDL statement of database, and is stored in the bottom storage structure of relational database.The application stores multi-mode data in the same storage layer, so that query can be directly called from database storage layer, and data conversion efficiency is improved.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a relational data import method for large-scale multi-modal data. Background Technology

[0002] With the advent of the big data era, data is experiencing explosive growth, and data types are becoming increasingly diverse. Various unstructured databases have demonstrated significant advantages in handling large-scale datasets due to their flexibility and efficiency in querying specific data. However, given that mature relational databases built on relational data models still dominate the market today, using an RDBMS as a unified platform for storing and querying multi-model data is particularly important.

[0003] A relational database is a data storage and management system based on the relational model, typically used to store structured data. Relational databases organize data into a tabular format, with each table consisting of rows and columns. Each row represents a data record, and columns represent the record's attributes. Records with the same attributes form a table. Different tables are linked through primary keys and foreign keys, enabling complex data relationships and queries. While NoSQL databases excel in flexibility and scalability, relational databases are characterized by data integrity, transaction support, concurrency control, and data consistency and durability. These features make them highly reliable and secure in enterprise applications.

[0004] Schema mapping between multi-modal data refers to establishing correspondences between different data schemas when processing various types of data, enabling these data to be processed, transformed, and queried within a unified system. Multi-modal data typically includes relational data, graph data, document data, vector data, etc., which have different storage structures and models. The purpose of schema mapping is to achieve interoperability between these data types in order to effectively leverage the advantages of multi-modal databases.

[0005] In a multimodal data environment, each data type has its own schema definition, such as table structures in relational databases, vertices and edges in graph databases, key-value pairs in document databases, and feature vectors in vector databases. Schema mapping establishes a standardized transformation method that allows data to be converted from different schemas to a unified relational model, thereby enabling the unified storage of different data storage models. For example, mapping vertices and edges in graph data to table structures in a relational database involves mapping vertex attributes to vertex tables and edge relationships to edge tables, thus storing the graph data in the form of relational tables.

[0006] Currently, most existing relational databases (such as MySQL, PostgreSQL, and openGauss) typically cannot directly import multi-model data into their databases due to differences in modeling compared to file storage formats like JSON, NPZ, and HDF5, which store multi-model data. The core structure of relational databases is a tabular row-column model, while data formats like JSON, NPZ, and HDF5 each have complex hierarchical structures or multi-dimensional arrays, making direct mapping difficult. To accommodate these unstructured or semi-structured data types, it's often necessary to manually define the table structure, including determining column names, data types, and how nested data is flattened. However, this manual definition method consumes significant manpower and time, and becomes even more cumbersome when dealing with large-scale datasets.

[0007] The background of this invention is to address the aforementioned problems by providing a method for mapping large-scale multi-modal data to relational data in a relational database, automatically creating relational table schemas, and importing data. This minimizes the complexity and time-consuming nature of manual intervention in converting different model data into relational data and relational table schemas. The method aims to automatically analyze and identify the structural characteristics of multi-modal data, determine a unified storage standard for each model, and then map unstructured or semi-structured data (such as nested JSON structures, NPZ multidimensional arrays, and HDF5 hierarchical relationships) into structured table schemas suitable for relational databases. Simultaneously, the invention utilizes an intelligent table schema generation mechanism to automatically retrieve and generate corresponding table structures, ensuring data consistency and integrity within the relational database. This allows users to efficiently import and query multi-modal data without manually defining or adjusting table structures. This method not only simplifies the data import process but also significantly improves the accuracy and efficiency of data mapping, making it suitable for applications requiring the storage and querying of multiple types of data in relational databases. Summary of the Invention

[0008] The technical problem this invention aims to solve is to overcome the shortcomings of storing large-scale multi-modal data in relational databases, such as low efficiency due to manual intervention, complex and long processes, and low accuracy of data mapping. From the perspective of intelligent and automated data conversion and schema conversion, this invention provides a database import system that maps large-scale multi-modal data into relational data within a relational database, belonging to the field of computer technology. By identifying different data models and automatically converting them into a unified data model using corresponding data processing rules, and automatically retrieving the unified data model to generate the corresponding relational table structure, the invention reduces the degree of manual intervention and data loss rate, thereby improving the efficiency of data storage and query performance. Users can efficiently store and manage multi-modal data in the relational database without manually defining complex table schemas, greatly enhancing the system's scalability and the flexibility of data integration.

[0009] This invention provides a relational-based data import method for large-scale multimodal data, such as... Figure 1 As shown, the process includes the following steps: Step 1, obtaining the original data file containing large-scale data, the storage format of the original data file, and the data model stored in the original data file; the original data file includes four types of data model files: relational data, document data, vector data, and attribute graph data, storing the four types of data models, each with its own fixed storage format;

[0010] Step 2: The original data file is parsed, read, and structured using the data conversion module to obtain the data information of the original data file; the data information includes field information, data values, and data relationships; the data conversion module includes a document data conversion submodule, a graph data conversion submodule, and a vector data conversion submodule.

[0011] Step 3: Generate a unified relational data model file from the data information obtained in Step 2 using the block parallel writing method, and store it in the same file storage format CSV (CommaSeparated Values).

[0012] Step 4: Preprocess the data in different data models through the data association module, merge the data in different data models into a nested structure, and store it together with the unified relational data model file obtained in Step 3 in the same file storage format CSV.

[0013] Step 5: Extract the schema information from the CSV file and send the schema information to the schema conversion module. The schema conversion module will automatically generate a relational table and establish DDL statements (Data Definition Language) according to the schema conversion rules.

[0014] Step 6: Send the DDL statements and CSV files to the SQL processing terminal of the database management system. Use the database's COPY and DDL statements to convert the multi-modal data into relational data and store it in the underlying storage structure of the relational database.

[0015] Furthermore, in step 1, the method for obtaining the original data file includes file upload, Distributed File System (DFS) reading, database export, and local file system reading; the database export is ETL extraction.

[0016] Identify the storage format of the original data file by its file extension;

[0017] The system automatically identifies the data model stored in the original data file based on user-input identification information or built-in system rules.

[0018] Furthermore, in step 2, the process of the data conversion module parsing, reading, and structuring the original data file is as follows:

[0019] Step 2.1, associate the data conversion module with the relational database cluster through the driver program;

[0020] Step 2.2, send the original data file in step 1 to the data conversion module;

[0021] Step 2.3, the data conversion module calls the data reading function. According to the storage format and data model of the original data file, use the custom function in the predefined relational database cluster to parse the original data file and read the data in the original data file, identify the field information, data values, and data relationships of the read data, and after unified flattening processing and data classification, convert it into structured one-dimensional determinant format data information m;

[0022] The custom function is a function in the database system that allows users to define their own functions. The custom function can be used to perform specific operations and is usually called in the form of an SQL query.

[0023] The custom function is used to expand the functions of the relational database. Compared with the traditional method of processing data outside the database and then transmitting it to the database, using the custom function method to process data inside the relational database can reduce the transmission of data between the database and the application program, thereby improving performance and stability; the data conversion is to extract the multi-mode data in the original file and finally structure it into one-dimensional determinant format data information through data processing.

[0024] Furthermore, in step 3, the method of parallel writing in chunks is as follows:

[0025] Step 3.1, evenly divide the data information m parsed and read by the data conversion module in step 2 into equal data chunks according to the number of threads n. The number of data chunks is n, and the data volume of each data chunk is chunk_size = m / n;

[0026] Step 3.2, calculate the start index and end index of each data chunk: The start index and end index of the kth data chunk (0 < k ≤ n, k is a positive integer) are calculated as follows: start index = (k - 1) × floor(m / n), end index = k × floor(m / n) - 1; generate the data chunk boundary through the start index and end index, and at the same time assign a unique tag to each data chunk, and the tag is chunk_1 to chunk_n;

[0027] Step 3.3, parallel write each data chunk into a unified relational data model file and store it as a CSV file in the same file storage format.

[0028] Compared to traditional multi-threaded write methods, the block parallel write method ensures that there is no overlapping data between data blocks without affecting data processing efficiency, and the results of the block parallel write method can be merged according to the original order of the data.

[0029] Furthermore, in step 4, the data association module includes an explicit field matching submodule, an implicit relationship mining submodule, and a data fusion submodule;

[0030] The data association module performs the following preprocessing steps for data from different data models:

[0031] Step 4.1: The data association module loads data from the multi-modal data.

[0032] Step 4.2: The explicit field matching submodule establishes preliminary associations between data in different data models based on explicit field matching rules;

[0033] Step 4.3, the implicit relationship mining submodule extracts features from data of different data models, and automatically discovers implicit relationships between fields of different data models through semantic similarity calculation and feature matching;

[0034] Step 4.3: The data fusion submodule merges the data from different data models into a nested structure and stores it together with the unified relational data model file obtained in Step 3 in a unified file storage format CSV.

[0035] The explicit field matching rule refers to directly establishing the relationship between data in different data models by explicitly defining existing fields; fields are usually common or shared identifiers of multi-model data, and the initial data association can be completed quickly and efficiently using fields;

[0036] The nested structure integrates four different model data types—attribute graph data, relational data, vector data, and document data—into a unified data structure. Graph data node data serves as the top-level structure, with each graph data node being a main entity. Graph data edge relationships describe the connections between nodes and are nested within the graph structure. Document data, relational data, and vector data are nested within nodes with explicit or implicit associations as additional attributes of the nodes.

[0037] The data association module enables nested processing of data associations for four data models: graph data, relational data, vector data, and document data by setting nesting priorities. This allows the connection between different data types to be established before the data is imported into the underlying database, significantly reducing the data association operations that traditionally require multi-table joins and complex queries within the database.

[0038] Furthermore, in step 5, the method for extracting the pattern information is as follows:

[0039] By retrieving the unified relational data model file after data transformation, and scanning the contents of the CSV files in steps 3 and 4, the field names, field order, and corresponding data value characteristics of the fields are obtained, thus obtaining the required pattern information.

[0040] Furthermore, in step 5, the pattern conversion rules are divided into field name parsing rules, field value type inference rules, field type priority rules, and DDL generation rules;

[0041] The process of creating the DDL statement is as follows:

[0042] First, the field name resolution rules extract the field names from the first row of the CSV file as the column names of the table;

[0043] Then, the field value type inference rule applies the preset type judgment functions isBoolean and isInteger to each field value in the first 100 rows to infer the most suitable data type for the field.

[0044] Next, the field type priority rule prioritizes the more stringent and suitable data type when inferring the field type by pre-setting the priority of the data type.

[0045] Finally, the DDL generation rules assemble the determined field names and inferred data types into SQL DDL statements.

[0046] The technical effects of this invention are as follows:

[0047] This invention achieves unified conversion and efficient storage of graph data, relational data, document data, and vector data in relational databases through automated data parsing, transformation, association, schema generation, and storage. It realizes the conversion of multiple data models to relational data models: automatically converting multiple data models into relational data models according to input and file type using predefined conversion rules, thus enabling multi-model data to be stored in the same database. It reduces manual intervention in the schema conversion process: the algorithm automatically scans the converted files, extracts information related to the relational table structure, and automatically generates table creation DDL statements, avoiding manual identification and creation of relational table structures. It improves data conversion efficiency: this invention converts large-scale data... Data is evenly distributed across different threads, and each thread is given its own scope for writing to a unified file. This truly enables multiple threads to perform segmented parallel write operations on the same file, improving data conversion efficiency. It also avoids data silos: traditional data storage methods are often scattered, with different types of data stored in different databases. This invention solves the data silo problem by converting multiple different data models and storing them in the same database. Furthermore, it accelerates query efficiency: by storing multi-model data uniformly in the same database storage layer, subsequent cross-model queries can directly retrieve data from the database storage layer, rather than querying data from different data models separately and then integrating them, thus accelerating data conversion efficiency. Attached Figure Description

[0048] Figure 1 This is an example diagram illustrating the data flow from multi-modal source data to the database storage layer in this invention;

[0049] Figure 2 This is a flowchart of the segmented multi-threaded parallel writing technology of the present invention;

[0050] Figure 3 This is an example diagram illustrating the data structure of the multimodal document, graph, and vector data of this invention;

[0051] Figure 4 This is an example diagram of the nested multi-modal data structure after processing by the data association module of this invention; Detailed Implementation

[0052] According to the technical solution provided in this disclosure, the following steps are taken: First, the original data file containing large-scale data, its storage format, and the data model of the data stored therein are obtained. A connection is established with the database, and the file is sent to the data conversion module. The data conversion module, based on the file storage format and data model, uses a pre-defined custom function set in the database to parse and read the data from the original data file. The read data is then sent to the data processing module. The data processing module allocates threads and divides the data into different data blocks on an even basis according to the number of threads. Multiple threads process different data blocks in parallel, writing them into a pre-defined unified data model and storing them in the same file storage format. The unified data model file after data conversion is retrieved, and its schema information is obtained and sent to the schema conversion module. The schema conversion module automatically generates relational table creation statements (DDL statements) according to schema conversion rules. The DDL statements and the corresponding unified data model file are then sent to the SQL processing terminal of the database management system. The unified data model file enters the database parser and executor together through the database copy statement and the DDL statements, and finally, data import is achieved through a custom syntax tree.

[0053] Figure 1 This is an example diagram illustrating the data flow of the entire method. The data modeling method includes steps S101 to S107;

[0054] In step S101, the original data file containing large-scale data, the file storage format, and the data model of the data stored therein are obtained based on the input and pattern recognition. The data model includes document data, vector data, graph data, relational data, etc. In the example of this invention, each data model has its fixed storage format, such as JSON for storing document data, NPZ for storing sparse vectors, HDF5 for storing dense vectors and graph data, CSV for storing relational data, etc.

[0055] In step S102, a connection is established with the relational database, and data storage files for different data models are sent to the data conversion module. The data conversion module parses and reads the original data files using pre-defined custom functions set in the database, based on the file storage format and data model. For example, for an HDF5 file storing graph data, the graph data is mainly stored in the form of in_edges and out_edges groups, with each group containing the datasets of outgoing and incoming edges for each node.

[0056] In step S103, the data processing function of the data conversion module will automatically allocate a certain number of threads according to the data volume and server configuration performance for the data parsed and read in step S102. At the same time, the data will be divided into different data blocks on an average basis according to the number of threads. Multiple threads will process different data blocks in parallel and write them to a pre-defined unified relational data model and store them in the same file storage format CSV. For example, for the graph data in S102, all datasets in the `in_edges` and `out_edges` groups are traversed, vertex names are extracted and stored in a vertex set, and vertex information is written to a specified vertex table CSV file using a single thread. The `out_edges` group is then traversed to extract edge relationships, reading the contents of all datasets within the group. The string in each dataset is used as the edge's `endid`, and the dataset name is used as the `startid`, forming a pair of data, which is then added to the `edges` vector, thus constructing a start-end pair (Edge) data structure. Using a multi-threaded, chunked writing approach, the number of threads `n` is automatically obtained based on the hardware's concurrency. The number of edges each thread should process is calculated. The `edges` vector with a data size of `m` is divided into `n` chunks, and a thread is assigned to each chunk. The `n` chunks are then labeled as chunks. The data is processed from 1 to chunkn to determine the order of the chunks. Since the start and end indices of each chunk are fixed (e.g., chunkk stores data from [(k-1)(m / n)] to [k(m / n)]), the data within each thread is processed in its original order. After all threads have finished processing their respective data blocks, due to the different processing speeds of the threads, the data is reordered according to the chunk labels after all thread tasks are completed, thus ensuring the original order of the data. Finally, the data is written to the CSV file. The entire process is as follows: Figure 2 As shown.

[0057] In step S104, the data association module loads and preprocesses data from multi-modal source data such as documents, graphs, and vectors. First, the module performs explicit associations based on field matching for data from different models. Second, it extracts features from the data of different models, automatically discovering implicit relationships between models through semantic similarity calculation and feature matching. Finally, it merges the data from different models into a nested structure and stores it along with the data from step 3 in a unified file storage format, CSV. For example, for... Figure 3The example documents and graph data have already been obtained from their respective source files. For known or small-volume files, after obtaining the data from their respective source files, documents and graph nodes are directly associated through explicit field matching (such as the author field in document data and the name field in graph data). For unknown or large-volume files, the text data is first vectorized; for example, the content field of document data is vectorized using a pre-trained language model such as BERT or MiniLM to convert the text content into semantic vectors. For graph data, the names (name field) of the graph nodes are extracted as text features. Implicit associations are established by calculating the semantic similarity between the document content vector and the graph node name vector. Then, the corresponding interest vectors are directly associated through the vertex_id of the graph node. Finally, the associated data is organized to generate a graph node such as... Figure 4 The nested structure shown is then written to a CSV file.

[0058] In step S105, the schema information is obtained from the unified data model CSV file after data transformation and association in steps S103 and S104. This schema information is then sent to the schema transformation module, which automatically generates relational table creation statements (DDL statements) according to the schema transformation rules. For example, for storage... Figure 4 For a CSV file with a nested structure, the schema conversion algorithm first reads the field names vertex_id, name, and nested_structure from the first row of the CSV file. For each row of data, the fields are separated by the delimiter ",". Field values ​​enclosed in quotes are also supported. A maximum of 100 rows of data are read and sent to the subsequent data type inference function. The data type inference function provides a series of pre-defined type judgment functions, such as isBoolean and isInteger. It checks whether the field value conforms to these types, iterates through all the data in each field, determines the appropriate data type based on the data characteristics, and returns the corresponding data type. For example, the data type of the vertex_id field is inferred to be Integer, the data type of the name field is Text, and the data type of the nested_structure field is JSONB. Finally, the corresponding DDL statement is generated based on the determined data type.

[0059] In step S106, the DDL statements and the unified data model CSV file generated in step 2 are sent to the SQL processing terminal of the database management system. The unified data model file converts the multi-model data into relational data and stores it at the underlying relational database through the database copy statement and DDL statement.

Claims

1. A relational-based data import method for large-scale multi-modal data, characterized in that: The data import method includes the following steps: Step 1: Obtain the original data file containing large-scale data, the storage format of the original data file, and the data model stored in the original data file; the original data file includes four types of data model files: relational data, document data, vector data, and attribute graph data, storing the four types of data models, each with its own fixed storage format; Step 2: The original data file is parsed, read, and structured using the data conversion module to obtain the data information of the original data file; the data information includes field information, data values, and data relationships; the data conversion module includes a document data conversion submodule, a graph data conversion submodule, and a vector data conversion submodule. Step 3: Generate a unified relational data model file from the data information obtained in Step 2 using the block parallel writing method, and store it in the same file storage format CSV; Step 4: Preprocess the data in different data models through the data association module, merge the data in different data models into a nested structure, and store it together with the unified relational data model file obtained in Step 3 in the same file storage format CSV. Step 5: Extract the schema information from the CSV file and send the schema information to the schema conversion module. The schema conversion module will automatically generate a relational table and establish DDL statements according to the schema conversion rules. The pattern conversion rules are divided into field name resolution rules, field value type inference rules, field type priority rules, and DDL language generation rules; The process for establishing the DDL statement generation rules is as follows: First, the field name resolution rules extract the field names from the first row of the CSV file as the column names of the table; Then, the field value type inference rule applies the preset type judgment functions isBoolean and isInteger to each field value in the first 100 rows to infer the most suitable data type for the field. Next, the field type priority rule selects the data type that conforms to the inference field type priority rule by pre-setting the priority of the data type; Finally, the DDL generation rules assemble the determined field names and inferred data types into SQL DDL statements; Step 6: Send the DDL statements and CSV files to the SQL processing terminal of the database management system. Use the database's COPY and DDL statements to convert the multi-modal data into relational data and store it in the underlying storage structure of the relational database.

2. The data import method according to claim 1, characterized in that, In step 1, the method for obtaining the original data file includes file upload, distributed file system reading, database export, and local file system reading; the database export is ETL extraction. Identify the storage format of the original data file by its file extension; The system automatically identifies the data model stored in the original data file based on user-input identification information or built-in system rules.

3. The data import method according to claim 1, characterized in that, In step 2, the data conversion module parses, reads, and structures the original data file as follows: Step 2.1, associate the data conversion module with the relational database cluster through the driver; Step 2.2, send the original data file in Step 1 to the data conversion module; Step 2.3, the data conversion module calls the data reading function. According to the storage format and data model of the original data file, use the custom functions in the predefined relational database cluster to parse the original data file and read the data in the original data file, identify the field information, data values and data relationships of the read data, and convert them into structured one-dimensional determinant format data information m after unified flattening processing and data classification; The custom function is a function in the database system that allows users to define their own functions. The custom function can be used to perform specific operations and is called in the form of an SQL query.

4. The data import method according to claim 1, characterized in that, In Step 3, the method of parallel chunk writing is as follows: Step 3.1, evenly divide the data information m parsed and read by the data conversion module in Step 2 into equal data chunks according to the number of threads n. The number of data chunks is n, and the data volume of each data chunk is chunk_size = m / n; Step 3.2, calculate the start index and end index of each data chunk: The start index and end index of the kth data chunk are calculated as follows: 0 < k ≤ n, k is a positive integer, start index = (k - 1) × floor(m / n), end index = k × floor(m / n) - 1; Generate data chunk boundaries through the start index and end index, and at the same time assign a unique label to each data chunk, and the label is chunk_1 to chunk_n; Step 3.3, write each data chunk into the unified relational data model file in parallel and store it as a CSV file in the same file storage format.

5. The data import method according to claim 1, characterized in that, In Step 4, the data association module includes an explicit field matching sub-module, an implicit relationship mining sub-module and a data fusion sub-module; The preprocessing process of the data in the data association module for different data models is as follows: Step 4.1, the data association module loads data from multi-modal data; Step 4.2, the explicit field matching sub-module establishes a preliminary association for the data in different data models based on the explicit field matching rules; Step 4.3, the implicit relationship mining sub-module extracts features from the data of different data models, and automatically discovers the implicit association relationships between the fields of different data models through semantic similarity calculation and feature matching; Step 4.3, the data fusion sub-module fuses the data of different data models into a nested structure and stores it as a unified CSV file together with the unified relational data model file obtained in Step 3.

6. The data import method according to claim 1, characterized in that, In Step 5, the method for extracting the pattern information is: by retrieving the unified relational data model file after data conversion, scan the content in the CSV files in Steps 3 and 4 to obtain the field names, field arrangement order and data value characteristics corresponding to the fields, and obtain the required pattern information.

7. An electronic device, characterized in that, Including: One or more processors; A memory; One or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs being configured to perform the method as described in any one of claims 1-6.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores program code that can be invoked by a processor to execute the method as described in any one of claims 1-6.

Images