CN112800150A - Automatic mapping and table building method for heterogeneous database - Google Patents

Abstract

The invention relates to the field of computers, and particularly provides an automatic mapping and table building method for a heterogeneous database, which comprises the following steps: s1, determining the mapping relation between the different types of database field types and the JDBC standard types; s2, obtaining a table structure of the original table, data types of fields and each field in the table, and mapping the table structure of the original table to the JDBC standard type according to the mapping relation determined in the step S1; s3, according to the type of the target table and the determined mapping relation in the step S1, mapping the JDBC standard type to a specific database field type, and generating a corresponding SQL statement of the target table according to the mapped table structure. Compared with the prior art, the method can automatically establish the table, reduce the complexity of establishing the heterogeneous database model, improve the efficiency and have good popularization value.

Description

Automatic mapping and table building method for heterogeneous database

Technical Field

The invention relates to the field of computers, and particularly provides an automatic mapping and table building method for a heterogeneous database.

Background

With the rapid development of modern information technology, the world has already crossed the internet + big data era, and the rapid advance of artificial intelligence technology is also the result of the big data development in recent years. Information is profoundly changing people's thinking, production, and lifestyle, and more industries are beginning to focus on data and use the data to enable business. In recent years, various enterprise data management application platforms are rapidly started and developed, and one-stop services such as data integration, data development, data science, data management, data service and data visualization are provided.

In the process of processing a large data platform, how to provide high-speed and stable data migration between rich heterogeneous data sources in a complex network environment and data synchronization in a complex business background has become an indispensable capability.

The industry has a variety of relational databases that support the JDBC protocol in large part. JDBC (Java Database Connectivity, JDBC for short) is an application program interface in Java language that specifies how a client program accesses a Database, provides methods such as querying and updating data in a Database, and provides a unified model, and we generally say JDBC is relational Database oriented.

In the implementation of the JDBC protocol, there are java.sql.types, and unified database types are defined in the java.sql.types, and these types almost cover all database types, and any database implementing the JDBC protocol complies with the definition.

But the types, syntax, etc. of data differ between different databases. Migration of a heterogeneous database in a traditional data integration system (as shown in fig. 2) cannot automatically map a table, and a common method is to firstly determine a source database structure, clarify data types of fields and each field in a data table and the table, and generate an SQL statement; then, manually modifying the SQL statement according to the type of the target database and the corresponding grammar, and creating a table by a third-party tool (as shown in FIG. 3); and finally, synchronizing the table to the corresponding database. The steps are extremely time consuming and inefficient.

Disclosure of Invention

Aiming at the defects of the prior art, the invention provides the automatic mapping and table building method for the heterogeneous database with strong practicability.

The technical scheme adopted by the invention for solving the technical problems is as follows:

an automatic mapping and table building method for a heterogeneous database comprises the following steps:

s1, determining the mapping relation between the different types of database field types and the JDBC standard types;

s2, obtaining a table structure of the original table, data types of fields and each field in the table, and mapping the table structure of the original table to the JDBC standard type according to the mapping relation determined in the step S1;

s3, according to the type of the target table and the determined mapping relation in the step S1, mapping the JDBC standard type to a specific database field type, and generating a corresponding SQL statement of the target table according to the mapped table structure.

Further, in step S1, a table field type mapping relationship between the source database Mysql, the target database Oracle and the JDBC standard type is determined.

Further, JDBC standard types include BIT, TINYINT, SMALLINT, INTERER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, CHAR, VARCHAR, LONGVARCHAR, CLOB, and BLOB.

Preferably, Mysql database field types include VARCHAR, CHAR, DATE, DATETIME, TIME, TIMESTAMP, INT, INTEGER, DOUBLE, FLOAT, TEXT, BIT, TINYINT, and SMALLINT.

Preferably, the Oracle database field types include: VARCHAR2, CHAR, DATE, TIMETAMMP, NUMBER, BLOB, CLOB, and LONG.

Further, in step S1, the JDBC standard type is mapped to the target library Oracle table field type, and the mapping relationship is obtained by reading the mapping file, where the mapping file adopts a json format.

Further, the mapping file adopts json format, and the content is as follows:

the value corresponding to dbToStandard is an object and represents the mapping relation from the field type of the source database table to the JDBC standard type;

the value corresponding to the standardToDb represents the mapping relation of the JDBC standard type to various target database table field types.

Further, in step S3, the standard type is mapped to the target library Oracle to obtain a field of the target library table, generate a corresponding SQL statement, and execute the SQL statement in the target database to automatically build a table.

Compared with the prior art, the automatic mapping and table building method for the heterogeneous database has the following outstanding advantages that:

the method and the device automatically map the table model between the heterogeneous databases, can automatically establish the table, reduce the complexity of establishing the heterogeneous database model and improve the efficiency.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to these drawings without creative efforts.

FIG. 1 is a flow chart diagram of a method for automatically mapping and building a table of a heterogeneous database;

FIG. 2 is a diagram illustrating a conventional data integration transformation mapping in an automatic mapping and table building method for heterogeneous databases;

FIG. 3 is a schematic diagram of a conventional data integration modeling in an automatic mapping and table building method for heterogeneous databases.

Detailed Description

The present invention will be described in further detail with reference to specific embodiments in order to better understand the technical solutions of the present invention. It is to be understood that the described embodiments are merely exemplary of the invention, and not restrictive of the full scope of the invention. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

A preferred embodiment is given below:

in modeling heterogeneous databases, the most important is the mapping of field types such as tables. For mapping of field types of heterogeneous databases, the following mapping strategies are generally adopted in the conventional data integration system:

(1) determining a source database table structure, determining data types of fields and data types in the data table and the table, and generating an SQL statement;

(2) and (4) artificially modifying the SQL statement according to the type and the corresponding grammar of the target database table so as to generate the SQL statement of the target database table, and then building the table through a third-party tool.

This strategy has the following drawbacks:

(1) when the data of the field of the source database is more, the workload of modifying the SQL statement according to the type of the target database table and the corresponding grammar is huge and the efficiency is extremely low;

(2) the syntaxes of different types of source databases and target databases are different, and the table field mapping rules are correspondingly different, resulting in poor expansion capability.

As shown in fig. 1-3, an automatic mapping and table building method for heterogeneous databases in this embodiment includes the following steps:

s1, determining the mapping relation between the field types of the different types of database tables and the JDBC standard types;

s2, acquiring a table structure of the original table: mapping the table structure of the original table to the JDBC standard type according to the mapping relation determined in S1;

and S3, mapping the JDBC standard type to a specific database table field type according to the target table type and the mapping relation determined in S1, and generating a corresponding target table SQL statement according to the mapped table structure.

Taking the migration of Mysql database to Oracle database as an example to illustrate the implementation, the method comprises the following steps:

s1, determining the table field type mapping relation among the source database Mysql, the target database Oracle and the JDBC standard types.

JDBC standard types include: BIT, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, CHAR, VARCHAR, LONGVARCHAR, CLOB, BLOB, etc.

Common field types for the Mysql database include: VARCHAR, CHAR, DATE, DATETIME, TIME, TIMETAMMP, INT, INTEGER, DOUBLE, FLOAT, TEXT, BIT, TINYINT, SMALLINT, and the like.

Common field types of an Oracle database include: VARCHAR2, CHAR, DATE, TIMETAMMP, NUMBER, BLOB, CLOB, LONG, etc.

The mapping of the source library Mysql table field type to JDBC standard type is shown in the following table:

mysql field type	JDBC standard type
		VARchar	VARchar
char	VARchar
		DATE	DATE
DATETIME	DATE
		TIME	TIME
TIMESTAMP	TIMESTAMP
		INT	INTEGER
INTEGER	INTEGER
		DOUBLE	DOUBLE
FLOAT	FLOAT
		TEXT	CLOB
BIT	BIT
		TINYINT	TINYINT
SMALLINT	SMALLINT

The mapping of the JDBC standard type to the target library Oracle table field type is shown in the following table:

JDBC standard type	Oracle field type
		VARchar	VARchar2
DATE	DATE
		TIME	DATE
TIMESTAMP	TIMESTAMP
		INTEGER	NUMBER
DOUBLE	NUMBER
		FLOAT	NUMBER
BLOB	BLOB
		CLOB	CLOB
BIT	NUMBER
		TINYINT	NUMBER
SMALLINT	NUMBER

In practical implementation, the mapping relationship is obtained by reading a mapping file, the mapping file adopts a json format, and the content is as follows:

the value corresponding to the key "dbToStandard" is an object (the enclosed part on the right side of the "dbToStandard" key colon { }), and represents the mapping relationship from the field types of the source database tables to the JDBC standard types. For example, the key "Mysql" represents the source database Mysql whose corresponding value is an object ("Mysql" the right side of the key colon { }) and represents a specific field type mapping, e.g., the source database Mysql field type "VARCHAR" maps to JDBC standard type "VARCHAR" and the source database Mysql field type "char" maps to JDBC standard type "VARCHAR".

Similarly, the value corresponding to the key "standardtoddb" is an object ("the part enclosed by" the right side of the colon "{ }") representing the mapping relationship of JDBC standard types to various types of target database table field types. For example, the key "Oracle" represents the target database Oracle, and the corresponding value is an object ("Oracle" key colon right side { } enclosed portion) which represents a specific field type mapping, for example, the JDBC standard type "VARCHAR" is mapped to the target database Oracle field type "VARCHAR2", and the JDBC standard type "DATE" is mapped to the target database Oracle field type "DATE".

S2, acquiring a table structure of the original table: and (3) mapping the table structure of the original table to the JDBC standard type according to the mapping relation determined in the step (1).

The original table tt _ info is used in this example, and the table structure is as follows:

CREATE TABLE`tt_info`(

`tt_info_id`BIGINT(20)NOT NULL AUTO_INCREMENT,

`tt_info_serial_no`VARCHAR(40),

`created_date`DATETIME DEFAULT CURRENT_TIMESTAMP,

`updated_by`VARCHAR(64),

`version_val`INT(11)DEFAULT NULL,

`record_ind`CHAR(1),

PRIMARY KEY(`tt_info_id`),

)ENGINE＝InnoDB DEFAULT CHARSET＝utf8；

according to the mapping relationship (mapping the source library Mysql to the JDBC standard type) in step S1, the following fields and their type mapping results can be obtained:

field(s)	Source table field type	JDBC standard type
			tt_info_id	BIGINT	BIGINT
tt_info_serial_no	VARchar	VARchar
			created_date	DATETIME	DATE
updated_by	VARchar	VARchar
			version_val	INT	INTEGER
record_ind	char	VARchar

S3, mapping the JDBC standard type to a specific database table field type according to the target table type and the mapping relation determined in the step 1, and generating a corresponding target table SQL statement according to the mapped table structure.

According to the mapping relationship (standard type mapping to target library Oracle) in step S1, the fields and types of the target library table can be obtained:

field(s)	JDBC standard type	Target table field type
			tt_info_id	BIGINT	NUMBER
tt_info_serial_no	VARchar	VARchar2
			created_date	DATE	DATE
updated_by	VARchar	VARchar2
			version_val	INTEGER	NUMBER
record_ind	VARchar	VARchar2

Then generate the corresponding SQL statement:

CREATE TABLE`tt_info`(

`tt_info_id`NUMBER(20)NOT NULL AUTO_INCREMENT,

`tt_info_serial_no`VARCHAR2(40),

`created_date`DATE DEFAULT CURRENT_TIMESTAMP,

`updated_by`VARCHAR(64),

`version_val`NUMBER(11)DEFAULT NULL,

`record_ind`VARCHAR2(1),

PRIMARY KEY(`tt_info_id`),

)；

then, SQL sentences are executed in the target database to automatically build tables.

The above embodiments are only specific ones of the present invention, and the scope of the present invention includes but is not limited to the above embodiments, and any suitable changes or substitutions that are made by a person of ordinary skill in the art and are in accordance with the claims of the present invention.

Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that changes, modifications, substitutions and alterations can be made in these embodiments without departing from the principles and spirit of the invention, the scope of which is defined in the appended claims and their equivalents.

Claims (8)

1. An automatic mapping and table building method for a heterogeneous database is characterized by comprising the following steps:

s1, determining the mapping relation between the different types of database field types and the JDBC standard types;

s2, obtaining a table structure of the original table, data types of fields and each field in the table, and mapping the table structure of the original table to the JDBC standard type according to the mapping relation determined in the step S1;

s3, according to the type of the target table and the determined mapping relation in the step S1, mapping the JDBC standard type to a specific database field type, and generating a corresponding SQL statement of the target table according to the mapped table structure.

2. The method for automatically mapping and building the table of the heterogeneous database according to claim 1, wherein in step S1, the table field type mapping relationship between the source database Mysql, the target database Oracle and the JDBC standard type is determined.

3. The method of claim 2, wherein the JDBC standard types include BIT, TINYINT, SMALLINT, INTERER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, CHAR, VARCHAR, LONGVARCHAR, CLOB and BLOB.

4. The method of claim 3, wherein the Mysql database field types include VARCHAR, CHAR, DATE, DATETIME, TIME, TIMETEMP, INT, INTEGER, DOUBLE, FLOAT, TEXT, BIT, TINYINT, and SMALLINT.

5. The method of claim 4, wherein the Oracle database field types include: VARCHAR2, CHAR, DATE, TIMETAMMP, NUMBER, BLOB, CLOB, and LONG.

6. The method for automatically mapping and building the table of the heterogeneous database according to claim 5, wherein in step S1, the JDBC standard type is mapped to the Oracle table field type of the target library, and the mapping relationship is obtained by reading the mapping file, wherein the mapping file adopts json format.

7. The method for automatically mapping and building the table of the heterogeneous database according to claim 1, wherein the mapping file adopts json format and comprises the following contents:

the value corresponding to dbToStandard is an object and represents the mapping relation from the field type of the source database table to the JDBC standard type;

the value corresponding to the standardToDb represents the mapping relation of the JDBC standard type to various target database table field types.

8. The method of claim 1, wherein in step S3, the standard type is mapped to a target library Oracle, a field of the target library table is obtained, a corresponding SQL statement is generated, and the SQL statement is executed on the target database for automatic table creation.

Images