技术领域Technical Field
本发明涉及数据库技术领域，特别是涉及一种oracle数据库多表关联实时数据处理方法及系统。The present invention relates to the field of database technology, and in particular to a method and system for real-time data processing of multi-table association in an Oracle database.
背景技术Background Art
目前通过在源端数据库创建触发器的方式进行捕捉同步，对客户源端数据库需要做操作，对源端数据库具有侵入性，对很多客户来说不允许在生产环境做任何操作。所以具有一定的局限性。针对源端分析到的中间文件到目标端数据库的方法，需要同步的数据很多，资源使用量大，很难从纷繁复杂的数据中实时获取到用户想获取的数据。还可能需要客户在系统里直接多表关联查询，并且直接查询效率比较慢且消耗源端数据库资源比较多。Currently, the capture and synchronization is performed by creating triggers in the source database, which requires operations on the customer's source database and is invasive. Many customers are not allowed to perform any operations in the production environment. Therefore, it has certain limitations. For the method of transferring the intermediate files analyzed from the source to the target database, a lot of data needs to be synchronized, and the resource usage is large. It is difficult to obtain the data that users want to obtain in real time from the complex data. It may also require customers to directly query multiple tables in the system, and the direct query efficiency is relatively slow and consumes more resources of the source database.
发明内容Summary of the invention
本发明旨在至少在一定程度上解决相关技术中的技术问题之一。The present invention aims to solve one of the technical problems in the related art at least to a certain extent.
为此，本发明提出了一种oracle数据库多表关联实时数据处理方法，不需要在数据库中建立触发器，并且不影响数据库原始性能，也不要额外权限，对数据库没有任何侵入性，因此，可以有效地实现数据的提取。To this end, the present invention proposes an Oracle database multi-table association real-time data processing method, which does not require the establishment of triggers in the database, does not affect the original performance of the database, does not require additional permissions, and has no intrusiveness to the database. Therefore, data extraction can be effectively realized.
本发明的另一个目的在于提出另一种oracle数据库多表关联实时数据处理方法。Another object of the present invention is to provide another Oracle database multi-table association real-time data processing method.
本发明的第三个目的在于提出一种oracle数据库多表关联实时数据处理系统。The third object of the present invention is to provide an Oracle database multi-table association real-time data processing system.
本发明的第四个目的在于提出另一种oracle数据库多表关联实时数据处理系统。The fourth object of the present invention is to provide another Oracle database multi-table association real-time data processing system.
为达上述目的，本发明一方面提出一种oracle数据库多表关联实时数据处理方法，应用于源端，所述方法包括：To achieve the above object, the present invention proposes a method for real-time data processing of multiple table associations in an Oracle database, which is applied to a source end. The method comprises:
根据oracle数据库中多表关联的sql语句的原始表和目标表中解析出的配置关系构建模型树；Build a model tree based on the configuration relationships parsed from the original table and target table of the SQL statement that associates multiple tables in the Oracle database;
根据所述模型树创建元数据信息；Creating metadata information according to the model tree;
根据确定的模型树的结构，并通过原始表的行伪列标识信息、元数据信息以及原始表和目标表中字段的对应关系生成对应的目标表结果集；According to the determined structure of the model tree, a corresponding target table result set is generated through row pseudo-column identification information of the original table, metadata information, and the correspondence between the fields in the original table and the target table;
将转换后的所述目标表结果集通过TCP/IP协议发送至目标端对应的接收程序，以完成多表关联查询结果集的实时共享。The converted target table result set is sent to the corresponding receiving program of the target end through the TCP/IP protocol to complete the real-time sharing of the multi-table association query result set.
本发明实施例的oracle数据库多表关联实时数据处理方法还可以具有以下附加技术特征：The Oracle database multi-table association real-time data processing method of the embodiment of the present invention may also have the following additional technical features:
在本发明的一个实施例中，根据oracle数据库中多表关联的sql语句的原始表和目标表中解析出的配置关系构建模型树，包括：In one embodiment of the present invention, a model tree is constructed based on the configuration relationship parsed from the original table and the target table of the SQL statement of multi-table association in the Oracle database, including:
对oracle数据库的配置文件中配置的sql语句的原始表和目标表配置项进行解析，得到完整的原始表的字段、数据类型以及目标表的字段；Parse the original table and target table configuration items of the SQL statement configured in the Oracle database configuration file to obtain the complete fields and data types of the original table and the fields of the target table;
解析数据类型以及原始表和目标表中字段的对应关系，并根据解析出的对应关系构建模型树。Parse the data types and the correspondence between the fields in the original table and the target table, and build a model tree based on the parsed correspondence.
在本发明的一个实施例中，在根据确定的模型树的结构之前，所述方法，还包括：In one embodiment of the present invention, before determining the structure of the model tree, the method further includes:
通过分析重做日志获取原始表的dml操作信息，并将所述dml操作信息存储到中间文件中；Obtain the DML operation information of the original table by analyzing the redo log, and store the DML operation information in an intermediate file;
读取所述中间文件。The intermediate file is read.
在本发明的一个实施例中，通过原始表的行伪列标识信息、元数据信息以及原始表和目标表中字段的对应关系生成对应的目标表结果集，包括：In one embodiment of the present invention, a corresponding target table result set is generated through row pseudo-column identification information of the original table, metadata information, and a correspondence between fields in the original table and the target table, including:
对原始表的插入操作进行判断：若原始表的字段的值满足配置文件中关联表的条件，影响目标表结果集，根据模型树完成原始表数据变化列到对应目标表数据变化列的转换，并根据元数据信息组装成第一中间文件结果集发送至目标端；The insert operation of the original table is judged: if the value of the field of the original table meets the condition of the associated table in the configuration file, it affects the result set of the target table, and the conversion of the original table data change column to the corresponding target table data change column is completed according to the model tree, and the first intermediate file result set is assembled according to the metadata information and sent to the target end;
对原始表的更新操作进行判断：若原始表更新的列不在目标表的字段中，判断为不影响目标表结果集，不发送给目标端；若原始表更新的列在目标表的字段中，根据模型树判断为不影响目标表结果集，不发送给目标端；若原始表更新的列在目标表的字段中，根据模型树判定为影响目标表结果集，根据模型树完成原始表数据变化列到对应目标表数据变化列的转换，并根据元数据信息组装成第二中间文件结果集发送至目标端；The update operation of the original table is judged: if the updated column of the original table is not in the field of the target table, it is judged that it does not affect the result set of the target table and is not sent to the target end; if the updated column of the original table is in the field of the target table, it is judged according to the model tree that it does not affect the result set of the target table and is not sent to the target end; if the updated column of the original table is in the field of the target table, it is judged according to the model tree that it affects the result set of the target table, and the conversion of the original table data change column to the corresponding target table data change column is completed according to the model tree, and assembled into a second intermediate file result set according to the metadata information and sent to the target end;
对原始表的删除操作进行判断：将原始表删除其中一条记录，根据模型树判断为影响目标表结果集，根据模型树完成原始表数据变化列到对应目标表数据变化列的转换，并根据元数据信息组装成第三中间文件结果集发送至目标端。Determine the deletion operation of the original table: delete one of the records in the original table, determine it as affecting the target table result set according to the model tree, complete the conversion of the original table data change column to the corresponding target table data change column according to the model tree, and assemble it into a third intermediate file result set based on the metadata information and send it to the target end.
为达上述目的，本发明另一方面提出一种oracle数据库多表关联实时数据处理方法，应用于目标端，所述方法包括：To achieve the above object, the present invention proposes, on the other hand, a method for real-time data processing of multi-table association in an Oracle database, which is applied to a target end and comprises:
通过TCP/IP协议接收第一中间文件结果集、第二中间文件结果集和第三中间文件结果集；Receiving the first intermediate file result set, the second intermediate file result set and the third intermediate file result set through the TCP/IP protocol;
按照目标系统的格式要求利用目标系统的接口分别顺序装载接收到的第一中间文件结果集、第二中间文件结果集和第三中间文件结果集对应的增量文件，以完成多表关联查询结果集的实时共享。According to the format requirements of the target system, the interface of the target system is used to sequentially load the incremental files corresponding to the received first intermediate file result set, the second intermediate file result set and the third intermediate file result set to complete the real-time sharing of the multi-table association query result set.
为达上述目的，本发明另一方面提出一种oracle数据库多表关联实时数据处理系统，包括：To achieve the above object, the present invention further provides an Oracle database multi-table association real-time data processing system, comprising:
模型解析模块，用于根据oracle数据库中多表关联的sql语句的原始表和目标表中解析出的配置关系构建模型树；The model parsing module is used to build a model tree based on the configuration relationship parsed from the original table and the target table of the SQL statement of multi-table association in the Oracle database;
元数据模块，用于根据所述模型树创建元数据信息；A metadata module, used for creating metadata information according to the model tree;
增量转换模块，用于根据确定的模型树的结构，并通过原始表的行伪列标识信息、元数据信息以及原始表和目标表中字段的对应关系生成对应的目标表结果集；An incremental conversion module is used to generate a corresponding target table result set according to the determined model tree structure and through the row pseudo-column identification information of the original table, metadata information, and the correspondence between the fields in the original table and the target table;
增量发送模块，用于将转换后的所述目标表结果集通过TCP/IP协议发送至目标端对应的接收程序，以完成多表关联查询结果集的实时共享。The incremental sending module is used to send the converted target table result set to the corresponding receiving program of the target end through the TCP/IP protocol to complete the real-time sharing of the multi-table association query result set.
为达上述目的，本发明另一方面提出一种oracle数据库多表关联实时数据处理系统，包括：To achieve the above object, the present invention further provides an Oracle database multi-table association real-time data processing system, comprising:
增量接收模块，用于通过TCP/IP协议接收第一中间文件结果集、第二中间文件结果集和第三中间文件结果集；An incremental receiving module, used for receiving a first intermediate file result set, a second intermediate file result set and a third intermediate file result set through a TCP/IP protocol;
数据装载模块，用于按照目标系统的格式要求利用目标系统的接口分别顺序装载接收到的第一中间文件结果集、第二中间文件结果集和第三中间文件结果集对应的增量文件，以完成多表关联查询结果集的实时共享。The data loading module is used to sequentially load the incremental files corresponding to the received first intermediate file result set, second intermediate file result set and third intermediate file result set according to the format requirements of the target system using the interface of the target system to complete the real-time sharing of multi-table association query result sets.
本发明实施例的oracle数据库多表关联实时数据处理方法和系统，对多表关联的复杂sql进行模型构建，构建的模型中包含关联查询涉及的所有原始表，定义为ctab，最终查询的结果集呈现的结果表定义为stab，模型构建的对应关系中包含ctab的各列元素和stab各列元素的对应关系，包含直接引用的列以及使用复杂函数转换的列元素；模型构建中包含了ctab的唯一元素和stab多条元素的对应关系的，然后通过实时得到ctab行数据的变化来根据模型树实时转换得到最终的stab的数据实时变化，完成最终的多表关联结果集实时数据分享。The Oracle database multi-table association real-time data processing method and system of the embodiment of the present invention constructs a model for complex SQL associated with multiple tables. The constructed model includes all original tables involved in the associated query, which are defined as ctab. The result table presented by the result set of the final query is defined as stab. The corresponding relationship constructed by the model includes the corresponding relationship between each column element of ctab and each column element of stab, including directly referenced columns and column elements converted using complex functions; the model construction includes the corresponding relationship between the unique element of ctab and multiple elements of stab, and then the real-time change of the final stab data is obtained by real-time conversion according to the model tree through the real-time change of ctab row data, thereby completing the real-time data sharing of the final multi-table association result set.
本发明附加的方面和优点将在下面的描述中部分给出，部分将从下面的描述中变得明显，或通过本发明的实践了解到。Additional aspects and advantages of the present invention will be given in part in the following description and in part will be obvious from the following description, or will be learned through practice of the present invention.
附图说明BRIEF DESCRIPTION OF THE DRAWINGS
本发明上述的和/或附加的方面和优点从下面结合附图对实施例的描述中将变得明显和容易理解，其中：The above and/or additional aspects and advantages of the present invention will become apparent and easily understood from the following description of the embodiments in conjunction with the accompanying drawings, in which:
图1是根据本发明实施例的一种oracle数据库多表关联实时数据处理方法的流程图；FIG1 is a flow chart of a method for processing multi-table association real-time data in an Oracle database according to an embodiment of the present invention;
图2是根据本发明实施例的另一种oracle数据库多表关联实时数据处理方法的流程图；2 is a flow chart of another method for processing multi-table association real-time data in an Oracle database according to an embodiment of the present invention;
图3是根据本发明实施例的一种oracle数据库多表关联实时数据处理系统的结构图；3 is a structural diagram of an Oracle database multi-table association real-time data processing system according to an embodiment of the present invention;
图4是根据本发明实施例的另一种oracle数据库多表关联实时数据处理系统的结构图。FIG. 4 is a structural diagram of another Oracle database multi-table association real-time data processing system according to an embodiment of the present invention.
具体实施方式DETAILED DESCRIPTION
需要说明的是，在不冲突的情况下，本发明中的实施例及实施例中的特征可以相互组合。下面将参考附图并结合实施例来详细说明本发明。It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments can be combined with each other. The present invention will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.
为了使本技术领域的人员更好地理解本发明方案，下面将结合本发明实施例中的附图，对本发明实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例仅仅是本发明一部分的实施例，而不是全部的实施例。基于本发明中的实施例，本领域普通技术人员在没有做出创造性劳动前提下所获得的所有其他实施例，都应当属于本发明保护的范围。In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.
下面参照附图描述根据本发明实施例提出的一种oracle数据库多表关联实时数据处理方法和系统。The following describes an Oracle database multi-table association real-time data processing method and system according to an embodiment of the present invention with reference to the accompanying drawings.
图1是根据本发明实施例的一种oracle数据库多表关联实时数据处理方法的流程图，如图1所示，应用于源端，该方法包括：FIG1 is a flow chart of a method for processing real-time data in an Oracle database with multi-table association according to an embodiment of the present invention. As shown in FIG1 , the method is applied to a source end and includes:
S1，根据oracle数据库中多表关联的sql语句的原始表和目标表中解析出的配置关系构建模型树。S1, build a model tree based on the configuration relationship parsed from the original table and target table of the SQL statement of multi-table association in the Oracle database.
具体地，选取一个或多个原始表，定义好目标表，需要增加原始表相关的rowid(用来唯一标记表中行的伪列)字段，跟换业务逻辑配置关联条件SQL(结构化查询语言)语句。Specifically, select one or more original tables, define the target table, add the rowid (pseudo column used to uniquely mark rows in the table) field related to the original table, and replace the business logic configuration association condition SQL (Structured Query Language) statement.
可以理解的是，本发明主要是对配置文件中配置的sql(配置关联条件的结构化查询语句)语句，ctab(原始表)和stab(目标表)配置项进行解析，解析出完整的原始表的字段，数据类型以及目标表的字段，数据类型以及原始表和目标表中字段的对应关系。It can be understood that the present invention mainly parses the sql (structured query statement configuring associated conditions) statements, ctab (original table) and stab (target table) configuration items configured in the configuration file, and parses out the complete fields of the original table, data types and the fields of the target table, data types and the correspondence between the fields in the original table and the target table.
进一步地，根据解析出的对应关系构建模型树。目标表中的字段需要额外增加一个或者多个原始表的rowid(用来唯一标记表中行的伪列)字段作为唯一标识。并且要给目标表中原始表的rowid(用来唯一标记表中行的伪列)字段上添加唯一索引。Furthermore, a model tree is constructed based on the parsed correspondence. The fields in the target table need to be additionally added with one or more rowid fields (pseudo columns used to uniquely mark rows in a table) of the original table as unique identifiers. And a unique index needs to be added to the rowid field (pseudo columns used to uniquely mark rows in a table) of the original table in the target table.
S2，根据模型树创建元数据信息。S2, creating metadata information based on the model tree.
可以理解的是，在Oracle数据库中，可以使用Oracle数据建模工具(Oracle DataModeler)来建立元数据模型。该工具支持多种建模方式，如ER(实体关系)、OO(面向对象)、UML(统一建模语言)等。在建立元数据模型时，需要考虑数据的层次结构、属性和关系，也要考虑到数据库对象的特殊性质，如存储类型、索引情况、权限等。It is understandable that in Oracle database, you can use Oracle Data Modeler to build metadata models. This tool supports multiple modeling methods, such as ER (entity relationship), OO (object-oriented), UML (unified modeling language), etc. When building metadata models, you need to consider the hierarchical structure, attributes and relationships of the data, as well as the special properties of database objects, such as storage type, index status, permissions, etc.
在建立元数据模型之前，需要定义元数据规范，即规定元数据的标准、命名规则、数据类型、格式等。这有助于保证元数据的一致性和可维护性。元数据规范可以根据实际需求进行定制，一般来说需要包含的内容如下：元数据的分类和组织方式；元数据的命名规则和命名空间；元数据的数据类型和数据格式；元数据的存储方式和管理规则；元数据的可视化和展示方式。Before establishing a metadata model, it is necessary to define metadata specifications, that is, to specify the standards, naming rules, data types, formats, etc. of metadata. This helps to ensure the consistency and maintainability of metadata. Metadata specifications can be customized according to actual needs. Generally speaking, the following contents need to be included: classification and organization of metadata; naming rules and namespaces of metadata; data types and formats of metadata; storage methods and management rules of metadata; visualization and display methods of metadata.
元数据仓库是一个集中存储元数据信息的数据库，它包含了各种元数据的定义、属性、结构和关系等信息。在Oracle数据库中，可以使用Oracle元数据管理器(OracleMetadata Manager)来建立和管理元数据仓库。建立元数据仓库需要注意以下几点：The metadata warehouse is a database that centrally stores metadata information. It contains the definitions, attributes, structures, and relationships of various metadata. In Oracle databases, you can use Oracle Metadata Manager to create and manage metadata warehouses. When creating a metadata warehouse, you need to pay attention to the following points:
选定适当的数据库引擎和存储设备；定义元数据的抽取、转换和加载规则；建立元数据的索引和查询功能；保证元数据的安全和保密性。Select appropriate database engines and storage devices; define metadata extraction, conversion, and loading rules; establish metadata indexing and query functions; and ensure the security and confidentiality of metadata.
进一步地，在建立好元数据模型和元数据仓库后，就可以开始整合和利用元数据。Oracle数据库提供了丰富的工具和接口，如Oracle Enterprise Manager(企业管理器)、Oracle Metadata API等，可以方便地获取、分析、治理和应用元数据信息。在整合和利用元数据时，也需要注意以下几点：将元数据与业务和应用系统相结合；利用元数据进行数据分析和智能决策；加强元数据的质量和可用性管理；建立完善的元数据管理流程和机制。Furthermore, after the metadata model and metadata warehouse are established, you can start integrating and using metadata. Oracle Database provides a variety of tools and interfaces, such as Oracle Enterprise Manager and Oracle Metadata API, which can easily obtain, analyze, manage and apply metadata information. When integrating and using metadata, you also need to pay attention to the following points: combine metadata with business and application systems; use metadata for data analysis and intelligent decision-making; strengthen metadata quality and availability management; and establish a complete metadata management process and mechanism.
综上所述，元数据建模是数据库管理的重要组成部分，Oracle数据库在元数据建模实践方面有着丰富的经验和技术方案。根据模型树创建元数据信息模型、定义元数据规范、建立元数据仓库以及整合和利用元数据，可以帮助数据库管理人员更好地了解数据、规划和管理数据资源和应用系统，提高数据的质量和可靠性，减少工作量和成本，保持数据的一致性和稳定性。In summary, metadata modeling is an important part of database management, and Oracle database has rich experience and technical solutions in metadata modeling practice. Creating metadata information models based on the model tree, defining metadata specifications, establishing metadata warehouses, and integrating and utilizing metadata can help database managers better understand data, plan and manage data resources and application systems, improve data quality and reliability, reduce workload and costs, and maintain data consistency and stability.
S3，根据确定的模型树的结构，并通过原始表的行伪列标识信息、元数据信息以及原始表和目标表中字段的对应关系生成对应的目标表结果集。S3, according to the determined structure of the model tree, and through the row pseudo-column identification information of the original table, metadata information, and the correspondence between the fields in the original table and the target table, a corresponding target table result set is generated.
可以理解的是，本发明通过分析重做日志获取到原始表的dml(数据库操作语言，用来操作数据库表中的记录，如增删改查等)操作，存储到中间文件中。It can be understood that the present invention obtains the DML (database operation language, used to operate records in the database table, such as adding, deleting, modifying, and checking) operations of the original table by analyzing the redo log, and stores them in the intermediate file.
进一步地，通过读取中间文件，根据建好的模型树结构，通过原始表的rowid(用来唯一标记表中行的伪列)信息，元数据信息、原始表字段和目标表字段的对应关系来生成对应的目标表结果集。Furthermore, by reading the intermediate file, according to the established model tree structure, the corresponding target table result set is generated through the rowid (pseudo column used to uniquely mark rows in the table) information of the original table, metadata information, the correspondence between the original table fields and the target table fields.
示例性地，对原始表的插入操作做如下判断：For example, the insert operation of the original table is judged as follows:
如果字段的值满足配置文件中关联表的条件，影响目标表结果集，会根据模型树完成原始表数据变化列到对应目标表数据变化列的转换。并根据元数据信息组装成第一中间文件结果集发送给目标端，如果字段的值不满足配置文件中关联表的条件，不会影响目标表结果集，不会发到目标端。If the value of the field meets the conditions of the associated table in the configuration file and affects the target table result set, the conversion of the original table data change column to the corresponding target table data change column will be completed according to the model tree. And it will be assembled into the first intermediate file result set according to the metadata information and sent to the target end. If the value of the field does not meet the conditions of the associated table in the configuration file, it will not affect the target table result set and will not be sent to the target end.
示例性地，对原始表的更新操作做如下判断：Exemplarily, the update operation of the original table is judged as follows:
原始表更新的列不在目标表的字段中，直接判定为不影响目标表结果集，不发送给目标端；The updated columns of the original table are not in the fields of the target table, so they are directly judged as not affecting the result set of the target table and are not sent to the target end;
原始表更新的列在目标表的字段中，根据模型树判定为不影响结果集，不发送给目标端；The updated columns of the original table are in the fields of the target table. According to the model tree, they are judged not to affect the result set and are not sent to the target end.
原始表更新的列在目标表的字段中，根据模型树判定为影响结果集，会根据模型树完成原始表数据变化列到对应目标表数据变化列的转换。并根据元数据信息组装成第二中间文件结果集发送给目标端。The updated columns of the original table are in the fields of the target table. According to the model tree, they are determined to affect the result set. The data change columns of the original table are converted to the corresponding data change columns of the target table according to the model tree. The second intermediate file result set is assembled according to the metadata information and sent to the target end.
示例性地，对原始表的删除操作会做如下判断：For example, the deletion operation of the original table will be judged as follows:
原始表删除其中一条记录，根据模型树判定为影响结果集，会根据模型树完成原始表数据变化列到对应目标表数据变化列的转换。并根据元数据信息组装成第三中间文件结果集发送给目标端。If one of the records in the original table is deleted, the model tree determines that it affects the result set, and the data change column of the original table is converted to the corresponding data change column of the target table according to the model tree. The third intermediate file result set is assembled according to the metadata information and sent to the target end.
S4，将转换后的目标表结果集通过TCP/IP协议发送至目标端对应的接收程序，以完成多表关联查询结果集的实时共享。S4, sending the converted target table result set to the corresponding receiving program on the target end through the TCP/IP protocol to complete the real-time sharing of the multi-table association query result set.
具体地，本发明将上述转换后的结果集通过TCP/IP(互联网基础的网络协议)协议发送给目标端对应接收程序。如果没有接收成功，会触发重传机制，直到目标端接收成功，才会进行新的转换处理。由此，在目标端完成查询结果集的实时共享。Specifically, the present invention sends the converted result set to the corresponding receiving program of the target end through the TCP/IP (Internet-based network protocol) protocol. If the reception is not successful, a retransmission mechanism will be triggered, and a new conversion process will not be performed until the target end receives it successfully. Thus, the real-time sharing of the query result set is completed at the target end.
根据本发明实施例的oracle数据库多表关联实时数据处理方法，源端分析到的中间文件可以直接复制到目标端数据库，也可以复制到中间数据库，或者复制到消息中间件再通过消费程序，抽取到不同的目标端数据库或者是大数据组件，本发明可以减少需要同步的数据，节约资源使用，也可以从纷繁复杂的数据中实时获取到用户关心的数据。本发明大大减轻了使用者查询转换关心数据的时间，提升了效率。同时不需要在源端数据库中创建额外的对象来辅助同步，对源端数据库没有侵入性，不会给用户的系统造成额外的负担。According to the Oracle database multi-table association real-time data processing method of the embodiment of the present invention, the intermediate file analyzed by the source end can be directly copied to the target end database, or copied to the intermediate database, or copied to the message middleware and then extracted to different target end databases or big data components through the consumer program. The present invention can reduce the data that needs to be synchronized, save resource usage, and can also obtain the data that users care about in real time from the complex data. The present invention greatly reduces the time for users to query and convert the data they care about, and improves efficiency. At the same time, there is no need to create additional objects in the source database to assist synchronization, which is not invasive to the source database and will not cause additional burden to the user's system.
为了实现上述实施例，如图2所示，本实施例中还提供了又一种oracle数据库多表关联实时数据处理方法，应用于目标端，方法包括：In order to implement the above embodiment, as shown in FIG2 , another method for real-time data processing of multi-table association in an Oracle database is provided in this embodiment, which is applied to the target end and includes:
S101，通过TCP/IP协议接收第一中间文件结果集、第二中间文件结果集和第三中间文件结果集；S101, receiving a first intermediate file result set, a second intermediate file result set, and a third intermediate file result set through a TCP/IP protocol;
S102，按照目标系统的格式要求利用目标系统的接口分别顺序装载接收到的第一中间文件结果集、第二中间文件结果集和第三中间文件结果集对应的增量文件，以完成多表关联查询结果集的实时共享。S102, according to the format requirements of the target system, using the interface of the target system, sequentially load the incremental files corresponding to the received first intermediate file result set, the second intermediate file result set and the third intermediate file result set, so as to complete the real-time sharing of the multi-table association query result set.
具体地，在源端输出的第一中间文件结果集、第二中间文件结果集和第三中间文件结果集数据基础上，目标端服务器通过TCP/IP(互联网基础的网络协议)协议，将接收到的数据流在目标端服务器磁盘存储。Specifically, based on the first intermediate file result set, the second intermediate file result set and the third intermediate file result set data output by the source end, the target end server stores the received data stream on the target end server disk through the TCP/IP (Internet-based network protocol) protocol.
进一步地，根据目标系统的格式要求，使用目标系统的接口分别顺序装载接收到的第一中间文件结果集、第二中间文件结果集和第三中间文件结果集对应的增量文件，完成多表关联查询结果集的实时共享。Furthermore, according to the format requirements of the target system, the interface of the target system is used to sequentially load the incremental files corresponding to the received first intermediate file result set, second intermediate file result set and third intermediate file result set to complete the real-time sharing of multi-table association query result sets.
根据本发明实施例的oracle数据库多表关联实时数据处理方法，不需要客户在系统里直接多表关联查询，针对直接查询效率比较慢且消耗源端数据库资源比较多，收取到目标端的数据可供业务端直接分析使用。本发明大大减轻了使用者查询转换关心数据的时间，提升了效率。同时不需要在源端数据库中创建额外的对象来辅助同步，对源端数据库没有侵入性，不会给用户的系统造成额外的负担。According to the Oracle database multi-table association real-time data processing method of the embodiment of the present invention, the client does not need to directly query the multi-table association in the system. The direct query efficiency is relatively slow and consumes more source database resources. The data collected at the target end can be directly analyzed and used by the business end. The present invention greatly reduces the time for users to query and convert the data they are concerned about, and improves efficiency. At the same time, there is no need to create additional objects in the source database to assist synchronization, which is non-invasive to the source database and will not cause additional burden to the user's system.
为了实现上述实施例，如图3所示，本实施例中还提供了又一种oracle数据库多表关联实时数据处理系统10，包括：In order to implement the above embodiment, as shown in FIG3 , this embodiment also provides another Oracle database multi-table association real-time data processing system 10, including:
模型解析模块100，用于根据oracle数据库中多表关联的sql语句的原始表和目标表中解析出的配置关系构建模型树；The model parsing module 100 is used to construct a model tree according to the configuration relationship parsed from the original table and the target table of the SQL statement of multi-table association in the Oracle database;
元数据模块200，用于根据所述模型树创建元数据信息；A metadata module 200, used to create metadata information according to the model tree;
增量转换模块300，用于根据确定的模型树的结构，并通过原始表的行伪列标识信息、元数据信息以及原始表和目标表中字段的对应关系生成对应的目标表结果集；The incremental conversion module 300 is used to generate a corresponding target table result set according to the determined structure of the model tree and through the row pseudo-column identification information of the original table, metadata information, and the correspondence between the fields in the original table and the target table;
增量发送模块400，用于将转换后的目标表结果集通过TCP/IP协议发送至目标端对应的接收程序，以完成多表关联查询结果集的实时共享。The incremental sending module 400 is used to send the converted target table result set to the corresponding receiving program of the target end through the TCP/IP protocol to complete the real-time sharing of the multi-table association query result set.
进一步地，上述模型解析模块100，还用于：Furthermore, the above-mentioned model parsing module 100 is also used for:
对oracle数据库的配置文件中配置的sql语句的原始表和目标表配置项进行解析，得到完整的原始表的字段、数据类型以及目标表的字段；Parse the original table and target table configuration items of the SQL statement configured in the Oracle database configuration file to obtain the complete fields and data types of the original table and the fields of the target table;
解析数据类型以及原始表和目标表中字段的对应关系，并根据解析出的对应关系构建模型树。Parse the data types and the correspondence between the fields in the original table and the target table, and build a model tree based on the parsed correspondence.
进一步地，上述在增量转换模块300之前，还包括增量分析模块，用于：Furthermore, before the incremental conversion module 300, the incremental analysis module is also included, which is used to:
通过分析重做日志获取原始表的dml操作信息，并将所述dml操作信息存储到中间文件中；Obtain the DML operation information of the original table by analyzing the redo log, and store the DML operation information in an intermediate file;
读取中间文件。Read the intermediate file.
进一步地，增量转换模块300，还用于：Furthermore, the incremental conversion module 300 is also used for:
对原始表的插入操作进行判断：若原始表的字段的值满足配置文件中关联表的条件，影响目标表结果集，根据模型树完成原始表数据变化列到对应目标表数据变化列的转换，并根据元数据信息组装成第一中间文件结果集发送至目标端；The insert operation of the original table is judged: if the value of the field of the original table meets the condition of the associated table in the configuration file, it affects the result set of the target table, and the conversion of the original table data change column to the corresponding target table data change column is completed according to the model tree, and the first intermediate file result set is assembled according to the metadata information and sent to the target end;
对原始表的更新操作进行判断：若原始表更新的列不在目标表的字段中，判断为不影响目标表结果集，不发送给目标端；若原始表更新的列在目标表的字段中，根据模型树判断为不影响目标表结果集，不发送给目标端；若原始表更新的列在目标表的字段中，根据模型树判断为影响目标表结果集，根据模型树完成原始表数据变化列到对应目标表数据变化列的转换，并根据元数据信息组装成第二中间文件结果集发送至目标端；The update operation of the original table is judged: if the updated column of the original table is not in the field of the target table, it is judged that it does not affect the result set of the target table and is not sent to the target end; if the updated column of the original table is in the field of the target table, it is judged according to the model tree that it does not affect the result set of the target table and is not sent to the target end; if the updated column of the original table is in the field of the target table, it is judged according to the model tree that it affects the result set of the target table, and the conversion of the original table data change column to the corresponding target table data change column is completed according to the model tree, and assembled into a second intermediate file result set according to the metadata information and sent to the target end;
对原始表的删除操作进行判断：将原始表删除其中一条记录，根据模型树判断为影响目标表结果集，根据模型树完成原始表数据变化列到对应目标表数据变化列的转换，并根据元数据信息组装成第三中间文件结果集发送至目标端。Determine the deletion operation of the original table: delete one of the records in the original table, determine it as affecting the target table result set according to the model tree, complete the conversion of the original table data change column to the corresponding target table data change column according to the model tree, and assemble it into a third intermediate file result set based on the metadata information and send it to the target end.
根据本发明实施例的oracle数据库多表关联实时数据处理系统，源端分析到的中间文件可以直接复制到目标端数据库，也可以复制到中间数据库，或者复制到消息中间件再通过消费程序，抽取到不同的目标端数据库或者是大数据组件，本发明可以减少需要同步的数据，节约资源使用，也可以从纷繁复杂的数据中实时获取到用户关心的数据。本发明大大减轻了使用者查询转换关心数据的时间，提升了效率。同时不需要在源端数据库中创建额外的对象来辅助同步，对源端数据库没有侵入性，不会给用户的系统造成额外的负担。According to the oracle database multi-table association real-time data processing system of the embodiment of the present invention, the intermediate file analyzed by the source end can be directly copied to the target end database, or copied to the intermediate database, or copied to the message middleware and then extracted to different target end databases or big data components through the consumer program. The present invention can reduce the data that needs to be synchronized, save resource usage, and can also obtain the data that users care about in real time from the complex data. The present invention greatly reduces the time for users to query and convert the data they care about, and improves efficiency. At the same time, there is no need to create additional objects in the source database to assist synchronization, which is not invasive to the source database and will not cause additional burden to the user's system.
为了实现上述实施例，如图4所示，本实施例中还提供了又一种Caché数据库逻辑复制到异构数据的处理系统20，包括：To implement the above embodiment, as shown in FIG. 4 , this embodiment further provides a processing system 20 for logically replicating a Caché database to heterogeneous data, including:
增量接收模块30，用于通过TCP/IP协议接收第一中间文件结果集、第二中间文件结果集和第三中间文件结果集；The incremental receiving module 30 is used to receive the first intermediate file result set, the second intermediate file result set and the third intermediate file result set through the TCP/IP protocol;
数据装载模块40，用于按照目标系统的格式要求利用目标系统的接口分别顺序装载接收到的第一中间文件结果集、第二中间文件结果集和第三中间文件结果集对应的增量文件，以完成多表关联查询结果集的实时共享。The data loading module 40 is used to sequentially load the incremental files corresponding to the received first intermediate file result set, second intermediate file result set and third intermediate file result set according to the format requirements of the target system using the interface of the target system to complete the real-time sharing of multi-table association query result sets.
根据本发明实施例的oracle数据库多表关联实时数据处理系统，不需要客户在系统里直接多表关联查询，针对直接查询效率比较慢且消耗源端数据库资源比较多，收取到目标端的数据可供业务端直接分析使用。本发明大大减轻了使用者查询转换关心数据的时间，提升了效率。同时不需要在源端数据库中创建额外的对象来辅助同步，对源端数据库没有侵入性，不会给用户的系统造成额外的负担。According to the Oracle database multi-table association real-time data processing system of the embodiment of the present invention, the client does not need to directly query the multi-table association in the system. The direct query efficiency is relatively slow and consumes more resources of the source database. The data collected at the target end can be directly analyzed and used by the business end. The present invention greatly reduces the time for users to query and convert the data they are concerned about, and improves efficiency. At the same time, there is no need to create additional objects in the source database to assist synchronization, which is non-invasive to the source database and will not cause additional burden to the user's system.
在本说明书的描述中，参考术语“一个实施例”、“一些实施例”、“示例”、“具体示例”、或“一些示例”等的描述意指结合该实施例或示例描述的具体特征、结构、材料或者特点包含于本发明的至少一个实施例或示例中。在本说明书中，对上述术语的示意性表述不必须针对的是相同的实施例或示例。而且，描述的具体特征、结构、材料或者特点可以在任一个或多个实施例或示例中以合适的方式结合。此外，在不相互矛盾的情况下，本领域的技术人员可以将本说明书中描述的不同实施例或示例以及不同实施例或示例的特征进行结合和组合。In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.
此外，术语“第一”、“第二”仅用于描述目的，而不能理解为指示或暗示相对重要性或者隐含指明所指示的技术特征的数量。由此，限定有“第一”、“第二”的特征可以明示或者隐含地包括至少一个该特征。在本发明的描述中，“多个”的含义是至少两个，例如两个，三个等，除非另有明确具体的限定。In addition, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In the description of the present invention, the meaning of "plurality" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined.
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410894151.XACN118820241B (en) | 2024-07-04 | 2024-07-04 | Method and system for processing multiple-table-associated real-time data of oracle database |
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410894151.XACN118820241B (en) | 2024-07-04 | 2024-07-04 | Method and system for processing multiple-table-associated real-time data of oracle database |
| Publication Number | Publication Date |
|---|---|
| CN118820241Atrue CN118820241A (en) | 2024-10-22 |
| CN118820241B CN118820241B (en) | 2025-04-25 |
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202410894151.XAActiveCN118820241B (en) | 2024-07-04 | 2024-07-04 | Method and system for processing multiple-table-associated real-time data of oracle database |
| Country | Link |
|---|---|
| CN (1) | CN118820241B (en) |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090193039A1 (en)* | 2008-01-28 | 2009-07-30 | Apollo Data Technologies, Llc | Data driven system for data analysis and data mining |
| US20140032528A1 (en)* | 2012-07-24 | 2014-01-30 | Unisys Corporation | Relational database tree engine implementing map-reduce query handling |
| CN105912595A (en)* | 2016-04-01 | 2016-08-31 | 华南理工大学 | Data origin collection method of relational databases |
| US20210157812A1 (en)* | 2019-11-25 | 2021-05-27 | Servicenow, Inc. | Metadata-based translation of natural language queries into database queries |
| CN113111082A (en)* | 2021-03-09 | 2021-07-13 | 深圳市教育信息技术中心(深圳市教育装备中心) | Structured query statement rewriting method, device, equipment and medium |
| CN113282599A (en)* | 2021-05-31 | 2021-08-20 | 平安国际智慧城市科技股份有限公司 | Data synchronization method and system |
| CN114036241A (en)* | 2021-12-06 | 2022-02-11 | 上证所信息网络有限公司 | Data increment synchronization method based on multi-table association query |
| CN115705330A (en)* | 2021-08-09 | 2023-02-17 | 顺丰科技有限公司 | Data query method and device, electronic equipment and readable storage medium |
| WO2024001493A1 (en)* | 2022-06-29 | 2024-01-04 | 京东方科技集团股份有限公司 | Visual data analysis method and device |
| CN117573691A (en)* | 2023-11-03 | 2024-02-20 | 天翼电子商务有限公司 | Database statement adjustment method and device, electronic equipment and storage medium |
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20090193039A1 (en)* | 2008-01-28 | 2009-07-30 | Apollo Data Technologies, Llc | Data driven system for data analysis and data mining |
| US20140032528A1 (en)* | 2012-07-24 | 2014-01-30 | Unisys Corporation | Relational database tree engine implementing map-reduce query handling |
| CN105912595A (en)* | 2016-04-01 | 2016-08-31 | 华南理工大学 | Data origin collection method of relational databases |
| US20210157812A1 (en)* | 2019-11-25 | 2021-05-27 | Servicenow, Inc. | Metadata-based translation of natural language queries into database queries |
| CN113111082A (en)* | 2021-03-09 | 2021-07-13 | 深圳市教育信息技术中心(深圳市教育装备中心) | Structured query statement rewriting method, device, equipment and medium |
| CN113282599A (en)* | 2021-05-31 | 2021-08-20 | 平安国际智慧城市科技股份有限公司 | Data synchronization method and system |
| CN115705330A (en)* | 2021-08-09 | 2023-02-17 | 顺丰科技有限公司 | Data query method and device, electronic equipment and readable storage medium |
| CN114036241A (en)* | 2021-12-06 | 2022-02-11 | 上证所信息网络有限公司 | Data increment synchronization method based on multi-table association query |
| WO2024001493A1 (en)* | 2022-06-29 | 2024-01-04 | 京东方科技集团股份有限公司 | Visual data analysis method and device |
| CN117573691A (en)* | 2023-11-03 | 2024-02-20 | 天翼电子商务有限公司 | Database statement adjustment method and device, electronic equipment and storage medium |
| Title |
|---|
| 李春青主编；何晶，冯坤，杨晓光，王佳欣副主编: "Oracle数据库应用与开发", vol. 2021, 31 July 2021, 天津大学出版社, pages: 6 - 7* |
| 程鲁明;肖菊香;: "oracle数据库批量数据无损迁移技术研究", 电子设计工程, no. 18, 18 September 2020 (2020-09-18)* |
| 袁满;彭广亮;: "元数据驱动的关系数据模型通用编辑系统", 科学技术与工程, no. 07, 8 March 2011 (2011-03-08)* |
| 许俊: "对Oracle多表关联更新的应用研究", 四川职业技术学院学报, no. 2015, 20 May 2015 (2015-05-20), pages 159 - 163* |
| Publication number | Publication date |
|---|---|
| CN118820241B (en) | 2025-04-25 |
| Publication | Publication Date | Title |
|---|---|---|
| US20250182352A1 (en) | Visual data analysis method and device | |
| US11360950B2 (en) | System for analysing data relationships to support data query execution | |
| CN110300963B (en) | Data management system in a large-scale data repository | |
| CN109101652B (en) | Label creating and managing system | |
| CN108038222B (en) | System of entity-attribute framework for information system modeling and data access | |
| CN102054025B (en) | Traffic information resource integration processing method and system | |
| EP3513313A1 (en) | System for importing data into a data repository | |
| US20050010550A1 (en) | System and method of modelling of a multi-dimensional data source in an entity-relationship model | |
| CN114218218A (en) | Data processing method, device and equipment based on data warehouse and storage medium | |
| CN107273506A (en) | A kind of method of database multi-list conjunctive query | |
| CN103559189B (en) | Electric analog training resource management system and method based on Metadata integration model | |
| US7099727B2 (en) | Knowledge repository system for computing devices | |
| CN112699100A (en) | Management and analysis system based on metadata | |
| CN115563817A (en) | Memory database-based simulation data access method and device and computer equipment | |
| CN115905313A (en) | MySQL big table association query system and method | |
| US20090022312A1 (en) | Apparatus and method for document synchronization | |
| CN111831696A (en) | Asset Information Storage Method and System Based on Graph Theory | |
| US20140143248A1 (en) | Integration to central analytics systems | |
| Castellanos et al. | Automating the loading of business process data warehouses | |
| CN118626496A (en) | Data integration method, device, server, medium and program | |
| CN117891813A (en) | Method and device for realizing data sharing based on data catalogue | |
| Das et al. | Industrial strength ontology management | |
| CN118820241A (en) | A method and system for real-time data processing of multiple table associations in an Oracle database | |
| JPH096666A (en) | Data management system | |
| CN118585528B (en) | Data query method and device based on dynamically configured label inverted index |
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |