Advantages, scenarios, and usage of the enhanced E-R feature-Data Management-阿里云帮助中心

The enhanced Entity Relationship (E-R) feature of Data Management DMS visualizes the relationships among data assets in your databases. This feature is built on core capabilities, such as a proprietary unified Catalog metadata acquisition engine, a column-level operator lineage parsing engine that analyzes the associations, dependencies, and impacts between fields, and data asset knowledge graph construction. This topic describes the advantages, scenarios, and usage of the enhanced E-R feature.

Prerequisites

Supported database instance types: RDS MySQL, RDS PostgreSQL, RDS MariaDB, PolarDB for MySQL, PolarDB for PostgreSQL, AnalyticDB for MySQL, and AnalyticDB for PostgreSQL.
The database instance is in one of the following regions: China (Hangzhou), China (Beijing), China (Shanghai), China (Shenzhen), China (Zhangjiakou), China (Qingdao), China (Chengdu), China (Hohhot), China (Heyuan), China (Ulanqab), China (Guangzhou), China (Nanjing-Local Region-decommissioning), or China (Fuzhou-Local Region-decommissioning).
The control mode of the database instance is Security Collaboration. For more information about control modes, see Control modes.

Background information

An Entity Relationship Diagram (E-R diagram) is a model that describes entities, properties, and relationships. During the database schema design phase, you can draw an E-R diagram. Then, you can convert the E-R diagram into multiple database tables that store primary and foreign keys. An E-R diagram effectively describes the relationships within business data and the dependencies and associations between database tables and fields. These relationships help you incorporate the logical relationships of business or database storage into the database design.

As businesses grow, the performance of database foreign keys can decrease. Many systems now manage business dependencies at the application level. This means foreign key information is often not stored in the database design. This makes it difficult to understand the logical relationships between data tables by examining the database's foreign keys. This also makes it harder to map the true logical relationships between your tables.

The enhanced E-R feature of Data Management DMS clearly displays the associations, dependencies, and impacts among business data, database tables, and fields. It also incorporates these relationships into the database design as logical relationships in the table schema.

Scenarios

Quickly understand the relationships between data in a database.
Export enhanced E-R diagrams to use as materials for project data design.
View how sensitive data is transmitted to prevent data leaks after secondary processing.
Identify the risks of data changes early by viewing data dependencies.
Use data associations to help build wide tables in a data warehouse.
View data transformation relationships to understand the data transformation pipeline.
Identify cold and hot data assets in the database by viewing data reference relationships.

Notes

You can view the E-R diagram one day (T+1) after you enable this feature.
The enhanced E-R feature only detects information about tables and fields that are entered into or changed in DMS. It displays the associations, impacts, and dependencies among them on the page. The feature cannot detect information entered or changed from other sources, such as program code.

Billing

This feature is free of charge. However, it is only available for instances that use the Security Collaboration control mode. The Security Collaboration control mode is a paid service.

Advantages

The enhanced E-R feature of DMS differs from traditional E-R diagram construction methods. In addition to traditional E-R capabilities, it uses a proprietary unified Catalog metadata acquisition engine, a column-level operator lineage parser, and data asset knowledge graph construction capabilities. These capabilities help you discover more potential relationships among data assets.
DMS has built an asset graph with over tens of billions of data nodes and relationships. It integrates various data asset business scenarios and provides related query and visualization services.

zichantupu

Usage

Traditional E-R

DMS provides traditional E-R capabilities and can build E-R diagrams based on physical foreign keys in a database. The following steps describe how to create a traditional E-R diagram.

Log in to DMS 5.0.
On the home page, click Database Instances on the left and select the target instance from the instance list.
Click the target instance and then double-click the target database name to go to the SQL Console page.

On the SQL Console page, create test data. The tables are ods_huiyuan_t1 (t1), ods_huiyuan_t2 (t2), ods_huiyuan_t3 (t3), ods_huiyuan_t4 (t4), and ods_huiyuan_fk. To do this, execute the following SQL statements:

Sample SQL

/*Create tables*/
CREATE TABLE `ods_huiyuan_t1` (
  `id` int NOT NULL AUTO_INCREMENT comment 'User ID',
  `user_name` varchar(255) DEFAULT NULL comment 'Username',
  `english_name` varchar(255) DEFAULT NULL comment 'User's English name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member table 1'

CREATE TABLE `ods_huiyuan_t2` (
  `id` int NOT NULL AUTO_INCREMENT comment 'User ID',
  `user_name` varchar(255) DEFAULT NULL comment 'Username',
  `tel_number` varchar(255) DEFAULT NULL comment 'User's phone number',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member table 2'

CREATE TABLE `ods_huiyuan_t3` (
  `id` int NOT NULL AUTO_INCREMENT comment 'User ID',
  `birth_date` varchar(255) DEFAULT NULL comment 'Date of birth',
  `age` varchar(255) DEFAULT NULL comment 'User's age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member table 3'

CREATE TABLE `ods_huiyuan_t4` (
  `id` int NOT NULL AUTO_INCREMENT comment 'User ID',
  `address` varchar(255) DEFAULT NULL comment 'User's address',
  `postcode` varchar(255) DEFAULT NULL comment 'Postal code',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member table 4'

CREATE TABLE `ods_huiyuan_fk` (
  `id` int NOT NULL AUTO_INCREMENT comment 'Auto-increment ID',
  `user_id` int NOT NULL comment 'User ID',
  `school` varchar(255) DEFAULT NULL comment 'User's graduated school',
  PRIMARY KEY (`id`),
  CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES ods_huiyuan_t1(id)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member foreign key test table'

/*Insert data*/
INSERT INTO `ods_huiyuan_t1` (`id`,`user_name`,`english_name`) 
VALUES (1,'Tang Xue',' Caspar  Jiracek'),
(2,'Fu Qiangjun',' Willem  Skatcher'),
(3,'Ma Jie',' Maje  Pennell');

INSERT INTO `ods_huiyuan_t2` (`id`,`user_name`,`tel_number`) 
VALUES (1,'Tang Xue','1640116****'),
(2,'Fu Qiangjun','1640116****'),
(3,'Ma Jie','1970429****');

INSERT INTO `ods_huiyuan_t3` (`id`,`birth_date`,`age`) 
VALUES (1,'19870718','35'),
(2,'19900608','32'),
(3,'19870101','35');

INSERT INTO `ods_huiyuan_t4` (`id`,`address`,`postcode`) 
VALUES (1,'Room 0000, Unit 11, Chuozhenliu Community, No. 4987 Shufeng Road, Liaohe City, Jinbang Province','842768'),
(2,'Room 0000, Unit 5, Lanzan Community, No. 5238 Mianwohan Road, Jingzhou City, Handong Province','520241'),
(3,'Room 0000, Unit 11, Huazanglang Community, No. 514 Piyuan Road, Donggu City, Ningxi Province','432298');

INSERT INTO `ods_huiyuan_fk` (`id`,`user_id`,`school`) 
VALUES (10001,1,'China Agricultural University'),
(10002,2,'Central South University'),
(10003,3,'Nanjing Agricultural University');

One day later (T+1), you can view the enhanced E-R diagram on the SQL Console page. For more information, see View the enhanced E-R diagram.

In addition, DMS provides capabilities for column-level operator lineage parsing, task orchestration. You can use these three capabilities to implement the enhanced E-R feature of DMS. The following sections describe these three methods and their procedures.

E-R based on column-level operator lineage parsing

E-R diagrams based on column-level operator lineage parsing are built using the proprietary column-level operator lineage parser of DMS. This method can incorporate the associations, dependencies, and impacts between fields into the E-R graph. The following steps describe how to use this capability to create an enhanced E-R diagram.

On the SQL Console page, associate the IDs of the ods_huiyuan_t1 and ods_huiyuan_t3 tables to generate a wide table by executing the following SQL statement:
Sample SQL
```
/*Field-to-field association*/
SELECT * FROM ods_huiyuan_t1 t1 join `ods_huiyuan_t3` t3 on t1.id = t3.id;
```

On the SQL Console page, perform data transformation on t4 to generate a new table named depend by executing the following SQL statements:

Sample SQL

/*Field-to-field dependency*/
CREATE TABLE `ods_huiyuan_depend` (
  `id` int NOT NULL AUTO_INCREMENT comment 'User ID',
  `col1` varchar(255) DEFAULT NULL comment 'Association result 1',
  `col2` varchar(255) DEFAULT NULL comment 'Association result 2',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member dependency test table';

insert into `ods_huiyuan_depend` (`id`,`col1`, `col2` )  select `id` , `address` , `postcode`  from `ods_huiyuan_t4`  where  id=1;

select * from ods_huiyuan_depend;

On the SQL Console page, perform data transformation on t2 and t3, associate the transformed data, and then generate a new table named influ by executing the following SQL statements:

Sample SQL

/*Field-to-field impact*/
CREATE TABLE `ods_huiyuan_influ` (
  `id` int NOT NULL AUTO_INCREMENT comment 'User ID',
  `col1` varchar(255) DEFAULT NULL comment 'Association result 1',
  `col2` varchar(255) DEFAULT NULL comment 'Association result 2',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COMMENT='Member impact test table';

INSERT INTO ods_huiyuan_influ (`id`, `col1`, `col2`)
SELECT a.id,a.user_name,b.birth_date
FROM (
    SELECT `id`,`user_name`,`tel_number`
    FROM `ods_huiyuan_t2`
  ) a
  LEFT OUTER JOIN (
    SELECT `id`,`birth_date`,`age`
    FROM `ods_huiyuan_t3`
    WHERE `age` > 32
  ) b ON a.id = b.id;

select * from ods_huiyuan_influ;

One day later (T+1), you can view the enhanced E-R diagram on the SQL Console page. For more information, see View the enhanced E-R diagram.

The following sample SQL statement and table explain the principles and capabilities of E-R diagrams that are based on column-level operator lineage parsing, including the association, impact, and dependency relationships between fields.

CREATE TABLE user_trade AS
SELECT  a.user_id
        ,a.user_name
        ,a.gender
        ,b.amt
        ,b.cnt
FROM    (
            SELECT  user_id,user_name,gender
            FROM    user
            WHERE   user_type = 'taobao'
        ) a
LEFT OUTER JOIN    (
                       SELECT  user_id,sum(amt) AS amt,sum(1) as cnt
                       FROM    trade
                       WHERE   is_pay = 1
                       GROUP BY user_id
                   ) b
ON      a.user_id = b.user_id;

Relationship type	Description	Example
Association between fields	The join condition between tables contains a field association.	In the sample SQL statement, the user_id field in the user table is associated with the user_id field in the trade table.
Dependency between fields	The source table and field from which the destination field originates.	In the sample SQL statement, the user_id field in the user_trade table originates from the user_id field in the user table.
Impact between fields	The impact of a WHERE clause field in the source table on a field in the destination table.	In the sample SQL statement, the is_pay field in the trade table impacts the amt field in the user_trade table. The condition is `trade.is_pay=1`.

E-R based on task orchestration

E-R diagrams based on task orchestration use the scheduling lineage feature of task orchestration to build the asset graph. The following steps describe how to use this capability to create an enhanced E-R diagram.

Use the task orchestration feature of DMS to create a single-instance SQL node and set the scheduled time to 00:00 every day.

In the SQL editor of the task node, enter the following SQL statements:

Sample SQL

/*DMS task orchestration, scheduled to run once a day*/
drop table if exists ods_huiyuan_df; 
create table IF NOT EXISTS `ods_huiyuan_df`  
AS  
SELECT `user_name`,`tel_number` 
FROM `ods_huiyuan_t2`

One day later (T+1), you can view the enhanced E-R diagram on the SQL Console page. For more information, see View the enhanced E-R diagram.

View the enhanced E-R diagram

Log in to DMS 5.0.
On the home page, click Database Instances on the left and select the target instance from the instance list.
Click the target instance and then double-click the target database name to go to the SQL Console page.
In the upper-right corner of the page, click the icon to go to the E-R Analysis page and view the enhanced E-R diagram.
On the E-R Analysis page, you can perform the following operations:
- Click a table to view its details.
- Click an edge between tables to view the relationship details.