通过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;
最新文章
- 在Eclipse中对包进行增删改查
- C++学习心得
- 纯代码TableView自适应高度(很老的使用方法)
- eclipse luna maven搭建spring mvc
- lintcode: 旋转图像
- Leetcode 104. Maximum Depth of Binary Tree(二叉树的最大深度)
- c 输入两个数,第一个数决定一个nXn的矩阵,第二个数决定从1开始赋值,赋值的上限 (MD花了半天时间,思路不对害死人)
- 第一次用上 Android Studio 2.3 过程及错误解决
- VTL(Velocity Templates Language,即Velocity模板语言)初识语法总结
- Qt之移动硬盘热插拔监控
- 修改 Docker 的 daemon.json后启动失败
- 第五章 服务容错保护:Spring Cloud Hystrix
- 服务链路追踪---Sleuth
- mongodb数据导入导出
- Python开发【第五篇】:模块
- ELF文件加载与动态链接(二)
- 机器学习入门-文本数据-构造Tf-idf词袋模型(词频和逆文档频率) 1.TfidfVectorizer(构造tf-idf词袋模型)
- Zabbix 3.0编译安装
- LED点阵书写显示
- [na]完全理解icmp协议