Introduction to database comparison
The NineData database comparison function supports the consistency comparison between two data sources. In case of inconsistency, it also generates SQL job to correct the inconsistency. User can execute the SQL job on the target side to achieve data and schema consistency.
Features
Schema Comparison : Schema comparison is the comparison of metadata (MetaData), which is used to compare whether the definitions of objects in the two databases are consistent. For example, compare whether the columns, indexes, and constraints (primary keys, foreign keys, and constraints) in the two tables are the same.
Data Comparison : Data comparison is the comparison of user data (UserData), which is used to compare whether the data in the two databases are consistent. The feature is usually for purpose of data consistency, in case such as, backup and recovery, data migration, and data synchronization.
Supported Datasources
| Source Datasource | Target Datasource | Data Comparison | Schema Comparison |
|---|---|---|---|
| MySQL | MySQL | ✔️ | ✔️ |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Elasticsearch | - | - | |
| Kafka | - | - | |
| Greenplum | ✔️ | - | |
| Redshift | - | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| TiDB | ✔️ | - | |
| Hive | ✔️ | - | |
| ADB PostgreSQL | - | - | |
| PostgreSQL | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | ✔️ | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| TiDB | ✔️ | - | |
| Hive | ✔️ | - | |
| ADB PostgreSQL | ✔️ | - | |
| Sybase | ✔️ | - | |
| Oracle | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | ✔️ | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| TiDB | ✔️ | - | |
| Hive | ✔️ | - | |
| ADB PostgreSQL | ✔️ | - | |
| SQL Server | SQL Server | ✔️ | ✔️ |
| Doris | ✔️ | - | |
| StarRocks | ✔️ | - | |
| MongoDB | MongoDB | ✔️ | - |
| Redis | Redis | ✔️ | - |
| Kafka | MySQL | - | - |
| ClickHouse | - | - | |
| Kafka | - | - | |
| SingleStore | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| StarRocks | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| ClickHouse | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SelectDB | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| Greenplum | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Doris | ✔️ | - | |
| SelectDB | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| StarRocks | ✔️ | - | |
| SingleStore | ✔️ | - | |
| TiDB | ✔️ | - | |
| Hive | ✔️ | - | |
| ADB PostgreSQL | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| TiDB | ✔️ | - | |
| ADB PostgreSQL | ✔️ | - | |
| Sybase | PostgreSQL | ✔️ | - |
| TiDB | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| ClickHouse | ✔️ | - | |
| Greenplum | ✔️ | - | |
| TiDB | ✔️ | - | |
| Hive | ✔️ | - | |
| ADB PostgreSQL | ✔️ | - | |
| Hive | MySQL | ✔️ | - |
| PostgreSQL | ✔️ | - | |
| Oracle | ✔️ | - | |
| Greenplum | ✔️ | - | |
| TiDB | ✔️ | - | |
| Hive | - | - | |
| DWS | DWS | ✔️ | - |
| 达梦 | 达梦 | ✔️ | - |
| DB2 | DB2 | ✔️ | - |
| OB-Oracle | OB-Oracle | ✔️ | - |
Use case
| Use case | Description |
|---|---|
| Database schema consistency across departments and regions | When enterprise users manage multi-projects or multiple databases of the same type, due to the dispersion or independence of organizations or teams, changes in database structures and objects are not synchronized to all data centers or sub-projects in time, and application errors are reported only after they are discovered. NineData Schema Comparison service regularly compares the metadata between the source and target instances to ensure the consistency of the data structure of all databases, and provides repair suggestions to avoid business interruption. |
| Cross-region, cross-cloud data verification | In order to achieve low-latency reading and writing at the nearest location, and the ability of databases to be active and reused, enterprises usually build data replication and synchronization between different regions or cloud platforms. However, due to the limitations of the system architecture and other factors, data inconsistency may occur, so periodic data verification is essential. In the case of a huge amount of data, full comparison is expensive and inefficient. NineData Data and schema comparison service is based on group mixed inspection and fast hashing , which provides efficient data consistency check. At the same time, for inconsistencies, NineData provides SQL change scripts, which can be executed on the target side to correct the inconsistency. |
| Integrity of data during ELT/ETL process | Both classic ETL and ELT in the cloud-native era require data from multiple data sources to be imported into a data warehouse to support OLAP and BI analysis. However, the source and destination are usually heterogeneous, and data consistency may be affected during data aggregation and schema transformation. NineData Data and schema comparison service supports data comparison between homogeneous and heterogeneous databases, which greatly reduces the risk of data inconsistency in the process. |