CN110659263B - Heterogeneous database data exchange method - Google Patents
Heterogeneous database data exchange method Download PDFInfo
- Publication number
- CN110659263B CN110659263B CN201910904905.4A CN201910904905A CN110659263B CN 110659263 B CN110659263 B CN 110659263B CN 201910904905 A CN201910904905 A CN 201910904905A CN 110659263 B CN110659263 B CN 110659263B
- Authority
- CN
- China
- Prior art keywords
- type
- message
- cmsp
- data
- columns
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 26
- 238000012545 processing Methods 0.000 abstract description 4
- 238000013459 approach Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 230000005540 biological transmission Effects 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 238000013508 migration Methods 0.000 description 1
- 230000005012 migration Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 230000001360 synchronised effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/214—Database migration support
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
- G06F16/254—Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/275—Synchronous replication
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a heterogeneous database data exchange method, which relates to the technical field of data processing, and aims to formulate a data standard format, store data to be exchanged among heterogeneous databases in the standard format, synchronously exchange heterogeneous data through a CMSP (compact messaging service) Topic, transmit heterogeneous data through a CMSP SN (remote control system) and exchange heterogeneous databases in different places.
Description
Technical Field
The invention discloses a heterogeneous database data exchange method, and relates to the technical field of data processing.
Background
Big data develop rapidly, and various industries accumulate a large amount of data, some of the data are stored in a relational database such as Oracle, sqlServer, mysql, some of the data are stored in a non-relational database such as redis, mongodb, and some of the data are stored in a file such as CSV, XML, and the like. In data processing, data migration or conversion between heterogeneous databases is often required, or data from files or applications is saved to a database.
The existing commercial or open source ETL tool, such as Oracle GoldenGate for short OGG, mainly surrounds the Oracle synchronization tool, is used for capturing the DML and DDL operations of the database through the Extract process running on the source end, reconstructing the DML or DDL operations through the Replicate running on the target end and applying the operations to the target end database, has strong invasiveness to the source system and the target system, needs corresponding authority and deployment process treatment, lacks good expansion and customization capability, and is not applied to many-to-one scenes. For the sql approach, field Jing Shouxian is used and the performance is poor. There is also a method for exchanging data with CSV and XML files, but this approach is equally poor in performance and cannot meet the needs of large data exchanges.
The cloud message service platform, CMSP for short, is a strict fifo (first in first out) cloud message middleware product, which can reliably transmit messages in a Topic to other topics. Different topics can be located in the same server or in different servers in different networks at different locations. CMSP SN: the Send Node, chinese name is the sending Node, is the application for pushing message in CMSP.
The invention provides a heterogeneous database data exchange method, which is characterized in that a data standard format is formulated, data to be exchanged among heterogeneous databases are stored in the standard format, heterogeneous exchange is carried out through CMSP Topic, data in external files such as CSV, JSON, XML and the like can be converted into the standard format, or a third party application directly generates the standard format, other applications can read the standard format from the CMSP Topic and convert the standard format into a required database format for data exchange.
Disclosure of Invention
Aiming at the problems in the prior art, the invention provides a heterogeneous database data exchange method which can convert data in databases with different sources and different structures into storage data in other databases and can adapt to the processing requirements of big data and mass data.
The specific scheme provided by the invention is as follows:
the data exchange method of heterogeneous databases, the data to be exchanged among heterogeneous databases are stored in a message standard format, and the content of the message standard format is as follows:
message type, source data type, operation type of message, destination table name, time zone of source table, number of columns in message, name of columns, type of columns, number of records and record value;
messages are cached and exchanged in the heterogeneous database through the CMSP Topic, and messages are transmitted and exchanged in the heterogeneous database through the CMSP SN.
In the method, a message in a standard format at least contains data of a heterogeneous database.
In the method, messages with different standard formats are cached through different CMSP topics.
In the method, the number of records is the same as the number of columns in the message, the sequence of records is consistent with the sequence of columns, and the recorded values represent the values of the columns.
The values of the columns in the method are divided into a numerical value type, a character type and a binary type, wherein the numerical value type is represented by a character string type, and the numerical value type is divided into an integer type and a floating point type.
A heterogeneous database data exchange system comprises a CMSP platform,
the CMSP platform stores the data to be exchanged among heterogeneous databases in a message standard format, and the content of the message standard format is as follows in sequence:
message type, source data type, operation type of message, destination table name, time zone of source table, number of columns in message, name of columns, type of columns, number of records and record value;
the CMSP platform caches and performs the same-place heterogeneous database exchange on the message through the CMSP Topic, and transmits the message in different places through the CMSP SN and performs the different-place heterogeneous database exchange.
In the system, the CMSP platform caches messages in different standard formats through different CMSP topics.
CSV data or JSON data or XML data in an external file of a CMSP platform in the system are also stored in a message standard format, cached through a CMSP Topic and exchanged with a same-place heterogeneous database, and transmitted in a different place through a CMSP SN and exchanged with a different-place heterogeneous database.
The invention has the advantages that:
the invention provides a heterogeneous database data exchange method, which is characterized in that a data standard format is formulated, data to be exchanged among heterogeneous databases are stored in the standard format, synchronous heterogeneous exchange is carried out through CMSP Topic, and remote transmission and remote heterogeneous database exchange are carried out through CMSP SN.
Drawings
FIG. 1 is a schematic diagram of a heterogeneous database switch framework of the system of the present invention;
FIG. 2 is a schematic diagram of a system of the present invention for a heterogeneous database switch framework;
FIG. 3 is a schematic flow chart of the method of the invention.
Detailed Description
The invention provides a heterogeneous database data exchange method, wherein data to be exchanged among heterogeneous databases are stored in a message standard format, and the content of the message standard format is as follows in sequence:
message type, source data type, operation type of message, destination table name, time zone of source table, number of columns in message, name of columns, type of columns, number of records and record value;
messages are cached and exchanged in the heterogeneous database through the CMSP Topic, and messages are transmitted and exchanged in the heterogeneous database through the CMSP SN.
And also provides a heterogeneous database data exchange system corresponding to the method, which comprises a CMSP platform,
the CMSP platform stores the data to be exchanged among heterogeneous databases in a message standard format, and the content of the message standard format is as follows in sequence:
message type, source data type, operation type of message, destination table name, time zone of source table, number of columns in message, name of columns, type of columns, number of records and record value;
the CMSP platform caches and performs the same-place heterogeneous database exchange on the message through the CMSP Topic, and transmits the message in different places through the CMSP SN and performs the different-place heterogeneous database exchange.
The present invention will be further described with reference to the accompanying drawings and specific examples, which are not intended to be limiting, so that those skilled in the art will better understand the invention and practice it.
The method of the invention realizes the local and heterogeneous database exchange with the external, and the specific process is as follows:
the data to be exchanged among heterogeneous databases are stored in a message standard format, and the content of the message standard format is as follows in sequence:
the message type, source data type, operation type of the message, destination table name, time zone of the source table, number of columns in the message, name of the columns, type of the columns, number of records and record value, as in table 1,
TABLE 1
Wherein, each field information is shown in table 2:
TABLE 2
The N format of the record is shown in table 3,
TABLE 3 Table 3
Wherein, the information of each field is shown in the table 4 as follows, table 4
The number of records is the same as the number of columns in the message, the recording sequence is consistent with the sequence of the columns, the recorded values represent the values of the columns, the values of the columns are divided into a numerical value type, a character type and a binary type, wherein the numerical value type is represented by a character string type, the numerical value type is divided into an integer type and a floating point type, the character string is used for representing the numerical value type, and the numerical value type is ended with \0';
the character type is ended with '\0', and for the time type with time meaning, the character type is used for representing, and the format is divided into:
YYY-MM-DD HH: MM: SS. Fff, fff representing microseconds, the accuracy is not limited, YYY-MM-DD HH: MM: SS can be directly applied without microseconds,
YYYY-MM-DD: the year-month-day of the year,
HH: MM: SS. Fff, fff stands for microsecond, the precision is not limited, HH: MM: SS can be directly applied without microsecond,
yyyyy: year of life
The Bool type is a char type, '0' represents false, and '1' represents true;
data is stored in the message standard format, messages are cached through the CMSP Topic and are exchanged in the same-place heterogeneous database, and messages are transmitted in different places through the CMSP SN and are exchanged in the different-place heterogeneous database.
In the above process, a message in a standard format at least contains data of a heterogeneous database, or multiple pieces of data can be stored in a message standard format, and data of different structure tables can be stored in different CMSP topics, each CMSP Server can support hundreds of thousands of topics, and the message is cached through the CMSP topics and exchanged in the same heterogeneous database, and when the data of the heterogeneous databases are exchanged, the data is transmitted from one place to another place or from one computer to another computer by using the sending function of the CMSP SN node. The data in external files such as CSV, JSON, XML can be converted into a standard format, a third party application can also directly generate the standard format, and other applications can read the standard format from the CMSP Topic and convert the standard format into a required database format so as to achieve the aim of heterogeneous database exchange.
When the system of the invention is used for heterogeneous database data exchange, the specific process is as follows:
the CMSP platform stores the data to be exchanged among heterogeneous databases in a message standard format, and the content of the message standard format is as follows in sequence:
the message type, the source data type, the operation type of the message, the destination table name, the time zone of the source table, the number of columns in the message, the names of the columns, the types of the columns, the number of records and the record values are the same as those of table 1, wherein the information of each field can be seen in table 2: record N format can be seen in table 3, various field information can be seen in table 4,
the number of records is the same as the number of columns in the message, the recording sequence is consistent with the sequence of the columns, the recorded values represent the values of the columns, the values of the columns are divided into a numerical value type, a character type and a binary type, wherein the numerical value type is represented by a character string type, the numerical value type is divided into an integer type and a floating point type, the character string is used for representing the numerical value type, and the numerical value type is ended with \0';
the character type is ended with '\0', and for the time type with time meaning, the character type is used for representing, and the format is divided into:
YYY-MM-DD HH: MM: SS. Fff, fff representing microseconds, the accuracy is not limited, YYY-MM-DD HH: MM: SS can be directly applied without microseconds,
YYYY-MM-DD: the year-month-day of the year,
HH: MM: SS. Fff, fff stands for microsecond, the precision is not limited, HH: MM: SS can be directly applied without microsecond,
yyyyy: year of life
The Bool type is a char type, '0' represents false, and '1' represents true;
data is stored in the message standard format, messages are cached through the CMSP Topic and are exchanged in the same-place heterogeneous database, and messages are transmitted in different places through the CMSP SN and are exchanged in the different-place heterogeneous database.
In the above process, a message in a standard format of the CMSP platform at least contains data of a heterogeneous database, or a plurality of messages can be stored in a message standard format, and data of different structure tables can be stored in different CMSP topics for caching, each CMSP Server can support hundreds of thousands of topics, and the messages are cached through the CMSP topics and are exchanged in the same heterogeneous database, and when the heterogeneous databases exchange data, the data are transmitted from one place to another place or from one computer to another computer by using the sending function of the CMSP SN node. The data in external files such as CSV, JSON, XML can be converted into a standard format, a third party application can also directly generate the standard format, and other applications can read the standard format from the CMSP Topic and convert the standard format into a required database format so as to achieve the aim of heterogeneous database exchange.
The above-described embodiments are merely preferred embodiments for fully explaining the present invention, and the scope of the present invention is not limited thereto. Equivalent substitutions and modifications will occur to those skilled in the art based on the present invention, and are intended to be within the scope of the present invention. The protection scope of the invention is subject to the claims.
Claims (5)
1. A heterogeneous database data exchange method is characterized in that data to be exchanged among heterogeneous databases are stored in a message standard format, and the content of the message standard format is as follows in sequence:
message type, source data type, operation type of message, destination table name, time zone of source table, number of columns in message, name of columns, type of columns, number of records and record value; the number of records is the same as the number of columns in the message, the order of records is consistent with the order of columns, the record value represents the value of a column,
messages are cached through CMSP Topic and are exchanged in the same place and heterogeneous databases, messages with different standard formats are cached through different CMSP topics, and messages are transmitted in different places through CMSP SN and are exchanged in different places and heterogeneous databases.
2. The method of claim 1, wherein a message in a standard format comprises at least data from a heterogeneous database.
3. The method of claim 1, wherein the values of the columns are divided into a value type, a character type, and a binary type, wherein the value type is represented by a string type, and the value type is in turn divided into an integer type and a floating point type.
4. A heterogeneous database data exchange system is characterized by comprising a CMSP platform,
the CMSP platform stores the data to be exchanged among heterogeneous databases in a message standard format, and the content of the message standard format is as follows in sequence:
message type, source data type, operation type of message, destination table name, time zone of source table, number of columns in message, name of columns, type of columns, number of records and record value; the number of records is the same as the number of columns in the message, the order of records is consistent with the order of columns, the record value represents the value of a column,
the CMSP platform caches the messages through the CMSP Topic and performs the same-place heterogeneous database exchange, and caches the messages with different standard formats through the different CMSP topics, and transmits the messages in different places through the CMSP SN and performs the different-place heterogeneous database exchange.
5. The system of claim 4, wherein CSV data or JSON data or XML data in external files of the CMSP platform are also stored in a message standard format, cached by CMSP Topic and exchanged with the disparate databases, and transmitted by CMSP SN and exchanged with the disparate databases.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910904905.4A CN110659263B (en) | 2019-09-24 | 2019-09-24 | Heterogeneous database data exchange method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910904905.4A CN110659263B (en) | 2019-09-24 | 2019-09-24 | Heterogeneous database data exchange method |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110659263A CN110659263A (en) | 2020-01-07 |
| CN110659263B true CN110659263B (en) | 2023-06-20 |
Family
ID=69038854
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910904905.4A Active CN110659263B (en) | 2019-09-24 | 2019-09-24 | Heterogeneous database data exchange method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110659263B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US11762857B2 (en) * | 2022-02-18 | 2023-09-19 | Capital One Services, Llc | Methods and systems for searching data exchanges that comprise information on assets with non-homogenous functionality and non-standardized data descriptions |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110175213A (en) * | 2019-05-27 | 2019-08-27 | 浪潮软件集团有限公司 | A kind of oracle database synchronization system and method based on SCN mode |
Family Cites Families (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8626778B2 (en) * | 2010-07-23 | 2014-01-07 | Oracle International Corporation | System and method for conversion of JMS message data into database transactions for application to multiple heterogeneous databases |
| CN104506496B (en) * | 2014-12-10 | 2017-09-29 | 山大地纬软件股份有限公司 | The method of near-realtime data increment distribution based on Oracle Streams technologies |
| CN109684097B (en) * | 2018-12-21 | 2023-03-31 | 浪潮软件股份有限公司 | CMSP-based JSON data analysis and storage method |
| CN109739931B (en) * | 2018-12-21 | 2023-03-24 | 浪潮软件股份有限公司 | CMSP-based incremental synchronization method for MySQLBinllog log analysis |
| CN110222117A (en) * | 2019-05-23 | 2019-09-10 | 武汉达梦数据库有限公司 | A kind of data conversion synchronous method, equipment and the storage medium of heterogeneous database |
-
2019
- 2019-09-24 CN CN201910904905.4A patent/CN110659263B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110175213A (en) * | 2019-05-27 | 2019-08-27 | 浪潮软件集团有限公司 | A kind of oracle database synchronization system and method based on SCN mode |
Non-Patent Citations (1)
| Title |
|---|
| 魏中贺 ; 李少波 ; .基于云制造的数据挖掘研究.贵州大学学报(自然科学版).2015,(01),全文. * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110659263A (en) | 2020-01-07 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN104794123B (en) | A kind of method and device building NoSQL database indexes for semi-structured data | |
| CN109063196B (en) | Data processing method and device, electronic equipment and computer readable storage medium | |
| CN106682077B (en) | Mass time sequence data storage implementation method based on Hadoop technology | |
| CN109753502B (en) | Data acquisition method based on NiFi | |
| CN101247417B (en) | Double-layer metadata processing system and method | |
| TW201800967A (en) | Method and device for processing distributed streaming data | |
| CN111597160A (en) | Distributed database system, distributed data processing method and device | |
| CN110659263B (en) | Heterogeneous database data exchange method | |
| CN108595511B (en) | Diversified meteorological hydrological data classification storage processing method and system | |
| CN111291235A (en) | Metadata storage method and device based on time sequence database | |
| CN110895547B (en) | Multi-source heterogeneous database data synchronization system and method based on DB2 federation characteristics | |
| CN109213820A (en) | Method for realizing fusion use of multiple types of databases | |
| CN112328702B (en) | Data synchronization method and system | |
| CN108255621A (en) | A kind of MySQL incremental message analytic methods based on binlog | |
| CN109165262B (en) | Fragmentation clustering system and fragmentation method of relational large table | |
| CN115203159A (en) | Data storage method and device, computer equipment and storage medium | |
| CN1971599A (en) | Error monitoring method and system of software application | |
| CN112860661B (en) | Method and device for generating data sequence identification | |
| CN112131214A (en) | Method, system, equipment and storage medium for data writing and data query | |
| CN104636332A (en) | Mass class locator data storage method | |
| CN103516765A (en) | Storage method and system of social networking service background data | |
| IES20020505A2 (en) | A method of handling a data request | |
| CN105204776B (en) | Data processing method and device | |
| CN115712681A (en) | Method and system for realizing real-time data integration based on Flink CDC | |
| CN102597969A (en) | Database management device using key-value store with attributes, and key-value-store structure caching-device therefor |
Legal Events
| 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 |