Data transformation conflicts and the processing of large objects in the process of transformation

Data Transformation conflict and processing

Data Transformation Conflict:

In the process of data conversion, it is very difficult to achieve strict equivalence conversion. You must determine the various grammatical and semantic conflicts that exist in the two models, which may include:

(1) Naming conflict: The identifier of the source data source may be a reserved word in the destination data source.

(2) format conflict: The same data type may have different presentation methods and semantic differences.

(3) Structural conflicts: If the data definition models between the two DBMS are different, such as the relational model and the hierarchy model, you will need to redefine the entity attributes and contacts to prevent the loss of property or contact information.

(4) Type conflict: There is a discrepancy between the precision of the same data type in different databases.

(5) Other conflicts: the large object types of different databases have different constraints, and there are some special types. An error occurs when a field with more than one text or image is in a table in SQL Server. Oracle also does not allow blobs and long types in one table to be more than one.

Conflict Handling methods:

For conflicts in the above data transformations, the corresponding conflicts can be handled.

For naming conflicts, you can first examine the reserved words in the data source, establish a set of reserved words, and rename the names in the reserved words as needed.

For format conflicts, special handling of specific types can be performed based on the data type of the corresponding data source from the driver of the data source, depending on the ODBC SQL type. For character data that contains "'" characters, it is necessary to use the escape character for special processing in the data conversion process, otherwise it will be mistaken for a string separator.

For precision conflicts of the same data type for different databases, type conversions combine the ODBC SQL type and precision to determine the mapping relationship between the source data type and the target data type. Identify the data type in the destination data source that most closely matches the precision of the source data source type as the default mapping relationship.

Data types are matched in the conversion process, and date data is best converted to character type and processed separately according to different target data sources. If you use the To_date function in Oracle, and FoxPro use the Ctod function to convert a string of date format to a date.

For the text, image type in SQL Server, you need to make a selection when converting, or mirror text to VARCHAR2 (4000), or a long type, but only one in a long table. The Oracle,text type can be imaged as a CLOB type, and image can be mirrored as a blob. The CLOB type can have more than one column in an Oracle table.

Reading metadata for a data source

Metadata type of the data source

Meta-data types metadata information purpose

Data source connection Information database name, drive, connection source data source and destination data source

Server, DSN name,

Data source description, user name, etc.

Table information table name, table owner, for creating tables in Data transformation

Table patterns, table types

Column information column name, type, width, for table creation and column mapping in Data transformation

accuracy, scale, or null

Type information type name, maximum column width, for table creation and type mapping in Data transformation

Maximum minimum scale,

Prefix characters,

Whether to accept empty,

Keyword list

Key information primary key name, primary key column, for conversion of table patterns in Data transformation

Foreign key name, foreign key column,

Foreign Key Association columns

Other object information: index information, stored procedure information, permission information, etc. for conversion of database objects in Data transformation.