通过otter元数据表获取有用的信息

获取数据源相关信息

原始数据：

+----+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| ID | NAME | TYPE | PROPERTIES | GMT_CREATE | GMT_MODIFIED |
+----+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
| 1 | 101 | MYSQL | {"driver":"com.mysql.jdbc.Driver","encode":"UTF8","id":1,"name":"101","password":"root","type":"MYSQL","url":"jdbc:mysql://5.5.5.103:3306","username":"root"} | 2018-09-03 10:15:29 | 2018-12-04 21:23:30 |
| 2 | 102 | MYSQL | {"driver":"com.mysql.jdbc.Driver","encode":"UTF8","id":2,"name":"102","password":"root","type":"MYSQL","url":"jdbc:mysql://5.5.5.102:3306","username":"root"} | 2018-09-03 10:16:03 | 2018-10-12 15:11:40 |
| 3 | 103 | MYSQL | {"driver":"com.mysql.jdbc.Driver","encode":"UTF8","name":"103","password":"root","type":"MYSQL","url":"jdbc:mysql://5.5.5.103:3306","username":"root"} | 2018-12-05 13:18:37 | 2018-12-05 13:18:37 |
+----+------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

Mysql5.7支持json语法查询如下：
select
  id,
  name,
  substring_index(
    substring_index(PROPERTIES ->> '$.url', '//', -1),
    ':',
    1
  ) as ip,
  substring_index(
    substring_index(PROPERTIES ->> '$.url', '//', -1),
    ':',
    -1
  ) as port,
  PROPERTIES ->> '$.username' as username,
  PROPERTIES ->> '$.password' as 'password'
from
  DATA_MEDIA_SOURCE;

+----+------+-----------+------+----------+----------+
| id | name | ip        | port | username | password |
+----+------+-----------+------+----------+----------+
| 1  | 101  | 5.5.5.103 | 3306 | root     | root     |
| 2  | 102  | 5.5.5.102 | 3306 | root     | root     |
| 3  | 103  | 5.5.5.103 | 3306 | root     | root     |
+----+------+-----------+------+----------+----------+
3 rows in set (0.00 sec)

 

如果是mysql5.7之前的版本，需要通过字符串截取获取，类似：

 

SELECT
distinct C.NAME AS 'CHANNEL名字',
P.NAME AS 'PIPELINE名字',
replace(
SUBSTR(
P.PARAMETERS,
INSTR(P.PARAMETERS, '"destinationName":') + LENGTH('"destinationName":'),
INSTR(P.PARAMETERS, ',"dryRun"') - INSTR(P.PARAMETERS, '"destinationName":') - LENGTH('"destinationName":')
),
'"',
''
) as canal_name,
N.IP AS '节点IP',
N.NAME AS '节点NAME'
FROM
NODE N,
PIPELINE_NODE_RELATION PN,
PIPELINE P,
CHANNEL C
WHERE
N.ID = PN.NODE_ID
AND PN.PIPELINE_ID = P.ID
AND P.CHANNEL_ID = C.ID
order by 5;