hive 元数据管理系统
·
https://mp.weixin.qq.com/s/ufeZC32bdgdcMo-0WQK7-Q
https://github.com/jacksoup/hiveMetaMgr
Neo4j 整合SpringBoot
https://mp.weixin.qq.com/s/CvKgZolGsNkfwuAcjay-YQ
https://gitee.com/divenswu/HiveSqlBloodFigure?_from=gitee_search
—>>> 开源 sql 解析
g6 展示血缘图
https://blog.csdn.net/github_35631540/article/details/126470804
https://blog.csdn.net/weixin_60068942/article/details/126731481
—>>> 类似sqlflow 的血缘展示
hive-site.xml
<property>
<name>hive.exec.post.hooks</name>
<value>org.apache.hadoop.hive.ql.hooks.LineageLogger</value>
</property>
{
"version":"1.0",
"user":"root",
"timestamp":1691136912,
"duration":97490,
"jobIds":[
"job_1689296418570_2651"
],
"engine":"mr",
"database":"default",
"hash":"dad0f0bd815675f1993aadead12c7aef",
"queryText":"insert into aabbcc select * from ods_v2xrtdata where day='2023-07-19' and hour='09'",
"edges":[
{
"sources":[
3
],
"targets":[
0
],
"edgeType":"PROJECTION"
},
{
"sources":[
],
"targets":[
1
],
"expression":"'2023-07-19'",
"edgeType":"PROJECTION"
},
{
"sources":[
],
"targets":[
2
],
"expression":"'09'",
"edgeType":"PROJECTION"
},
{
"sources":[
4,
5
],
"targets":[
0,
1,
2
],
"expression":"((ods_v2xrtdata.day = '2023-07-19') and (ods_v2xrtdata.hour = '09'))",
"edgeType":"PREDICATE"
}
],
"vertices":[
{
"id":0,
"vertexType":"COLUMN",
"vertexId":"default.aabbcc.content"
},
{
"id":1,
"vertexType":"COLUMN",
"vertexId":"default.aabbcc.day"
},
{
"id":2,
"vertexType":"COLUMN",
"vertexId":"default.aabbcc.hour"
},
{
"id":3,
"vertexType":"COLUMN",
"vertexId":"default.ods_v2xrtdata.content"
},
{
"id":4,
"vertexType":"COLUMN",
"vertexId":"default.ods_v2xrtdata.day"
},
{
"id":5,
"vertexType":"COLUMN",
"vertexId":"default.ods_v2xrtdata.hour"
}
]
}
–>>> hive插件获取表级别、字段级别血缘关系
### hive-site.xml ###
<property>
<name>hive.metastore.pre.event.listeners</name>
<value>org.data.meta.hive.listener.MetaStorePreAuditListener</value>
</property>
<property>
<name>hive.metastore.event.listeners</name>
<value>org.data.meta.hive.listener.MetaStoreListener</value>
</property>
<property>
<name>hive.exec.post.hooks</name>
<value>org.apache.hadoop.hive.ql.hooks.LineageLogger,org.data.meta.hive.hook.LineageLoggerHook</value>
</property>
####
{
"database":"default",
"duration":58452,
"engine":"mr",
"hash":"87ba9bdfea70193c880d708cf18cb7de",
"jobIds":[
"job_1689296418570_3131"
],
"queryText":"insert into table tmp_test_a select tmp.ss,tmpb.s2 from ( select session_id,kv[\u0027CC\u0027] as ss from EXPOSURE where day\u003d20190101) tmp left join tmp_test_b tmpb on tmp.session_id\u003dtmpb.s1",
"timestamp":1691390987,
"user":"root",
"userGroupNames":[
"root"
],
"version":"1.0",
"columnLineages":[
{
"srcDatabase":"default",
"destDatabase":"default",
"expression":"exposure.kv[\u0027CC\u0027]",
"edgeType":"PROJECTION",
"sources":[
{
"table":"exposure",
"column":"kv"
}
],
"targets":[
{
"table":"tmp_test_a",
"column":"s1"
}
]
},
{
"srcDatabase":"default",
"destDatabase":"default",
"edgeType":"PROJECTION",
"sources":[
{
"table":"tmp_test_b",
"column":"s2"
}
],
"targets":[
{
"table":"tmp_test_a",
"column":"s2"
}
]
},
{
"srcDatabase":"default",
"destDatabase":"default",
"expression":"(UDFToDouble(exposure.day) \u003d 2.0190101E7)",
"edgeType":"PREDICATE",
"sources":[
{
"table":"exposure",
"column":"day"
}
],
"targets":[
{
"table":"tmp_test_a",
"column":"s1"
},
{
"table":"tmp_test_a",
"column":"s2"
}
]
},
{
"srcDatabase":"default",
"destDatabase":"default",
"expression":"(exposure.session_id \u003d tmpb.s1)",
"edgeType":"PREDICATE",
"sources":[
{
"table":"exposure",
"column":"session_id"
},
{
"table":"tmp_test_b",
"column":"s1"
}
],
"targets":[
{
"table":"tmp_test_a",
"column":"s1"
},
{
"table":"tmp_test_a",
"column":"s2"
}
]
}
],
"tableLineages":[
{
"srcDatabase":"default",
"destDatabase":"default",
"srcTable":"tmp_test_b",
"destTable":"tmp_test_a"
},
{
"srcDatabase":"default",
"destDatabase":"default",
"srcTable":"exposure",
"destTable":"tmp_test_a"
}
]
}
// hql 血缘保存
private void saveColumnLineage2DB(LineageHookInfo lineageHookInfo) throws ClassNotFoundException, SQLException {
SimpleDateFormat time_sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
time_sdf.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
// 获取数据库连接
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://10.0.2.37:32326/metastore?useSSL=false", "root", "123456");
//
PreparedStatement ps = conn.prepareStatement("insert into t_table_column_dependency values (?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE db = VALUES(db) and tname = VALUES(tname) and col = VALUES(col) and parent_db = values(parent_db) and parent_tname = values(parent_tname) and parent_col = values(parent_col)");
List<ColumnLineage> columnLineages = lineageHookInfo.getColumnLineages();
for(ColumnLineage columnLineage : columnLineages){
for (LineageTableColumn sourceLineageTableColumn : columnLineage.getSources()){
for (LineageTableColumn targetLineageTableColumn : columnLineage.getTargets()){
int i = 1;
// ps.executeUpdate();
ps.setString(i, columnLineage.getSrcDatabase());
ps.setString(++i, targetLineageTableColumn.getTable());
ps.setString(++i, targetLineageTableColumn.getColumn());
ps.setString(++i, columnLineage.getDestDatabase());
ps.setString(++i, sourceLineageTableColumn.getTable());
ps.setString(++i, sourceLineageTableColumn.getColumn());
ps.setString(++i, columnLineage.getEdgeType().toString());
ps.setString(++i, columnLineage.getExpression());
ps.setString(++i, time_sdf.format(new Date(System.currentTimeMillis())));
ps.setString(++i, time_sdf.format(new Date(System.currentTimeMillis())));
ps.addBatch();
}
}
}
ps.executeBatch();
}
CREATE TABLE `t_table_column_dependency` (
`db` varchar(50) NOT NULL COMMENT '输出表数据库名',
`tname` varchar(50) NOT NULL COMMENT '输出表数据表名',
`col` varchar(50) NOT NULL COMMENT '输出表数字段名',
`parent_db` varchar(50) COMMENT '依赖表数据库名',
`parent_tname` varchar(50) COMMENT '依赖表数据表名',
`parent_col` varchar(50) COMMENT '依赖表数据字段名',
`ptype` varchar(50) COMMENT '类型',
`expr` varchar(255) COMMENT '表达式',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`db`, `tname`, `col`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
---->>> 基于Hive内部接口实现的Hive数据血缘的介绍
Flinksql 数据血缘
https://mp.weixin.qq.com/s/ub3_so6q-0N78Sri9CVUew
https://github.com/HamaWhiteGG/flink-sql-lineage
问题记录
dolphinscheduler 元数据采集 hive beeline ClassNotFoundException
root@hive-server-deployment-service-0:/opt/hive/auxlib# pwd
/opt/hive/auxlib
root@hive-server-deployment-service-0:/opt/hive/auxlib# ls -l
total 60
-rw-r--r--. 1 root root 58629 Aug 8 13:28 hive-hook-plugin-1.0-SNAPSHOT.jar
root@hive-server-deployment-service-0:/opt/hive/auxlib# echo $HIVE_AUX_JARS_PATH
/opt/hive/auxlib/hive-hook-plugin-1.0-SNAPSHOT.jar
###
beeline> !connect jdbc:hive2://localhost:10000/default
Connecting to jdbc:hive2://localhost:10000/default
Enter username for jdbc:hive2://localhost:10000/default: root
Enter password for jdbc:hive2://localhost:10000/default: ******
Connected to: Apache Hive (version 2.3.2)
Driver: Hive JDBC (version 2.3.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000/default> show databases;
+-------------------+
| database_name |
+-------------------+
| default |
| euht_terminal_bj |
+-------------------+
2 rows selected (0.118 seconds)
0: jdbc:hive2://localhost:10000/default> insert into table tmp_test_a select tmp.ss,tmpb.s2 from ( select session_id,kv['CC'] as ss from EXPOSURE where day=20190101) tmp left join tmp_test_b tmpb on tmp.session_id=tmpb.s1;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop-2.7.4/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Execution log at: /tmp/root/root_20230808143848_1c295667-e275-4d33-8090-13f9176fe3c5.log
2023-08-08 14:38:54 Starting to launch local task to process map join; maximum memory = 477626368
2023-08-08 14:38:54 Dump the side-table for tag: 1 with group count: 0 into file: file:/tmp/root/102aeecd-4763-4b7a-a4e8-5a29e1118115/hive_2023-08-08_14-38-48_434_8340070157758471436-2/-local-10002/HashTable-Stage-4/MapJoin-mapfile01--.hashtable
2023-08-08 14:38:54 Uploaded 1 File to: file:/tmp/root/102aeecd-4763-4b7a-a4e8-5a29e1118115/hive_2023-08-08_14-38-48_434_8340070157758471436-2/-local-10002/HashTable-Stage-4/MapJoin-mapfile01--.hashtable (260 bytes)
2023-08-08 14:38:54 End of local task; Time Taken: 0.703 sec.
No rows affected (55.385 seconds)
####
mysql> delete from t_table_column_dependency;
Query OK, 8 rows affected (0.00 sec)
mysql> select * from t_table_column_dependency;
Empty set (0.00 sec)
mysql> select * from t_table_column_dependency;
+---------+------------+-----+-----------+--------------+------------+------------+-------------------------------------------+---------------------+---------------------+
| db | tname | col | parent_db | parent_tname | parent_col | ptype | expr | create_time | update_time |
+---------+------------+-----+-----------+--------------+------------+------------+-------------------------------------------+---------------------+---------------------+
| default | tmp_test_a | s1 | default | exposure | day | PREDICATE | (UDFToDouble(exposure.day) = 2.0190101E7) | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s1 | default | exposure | kv | PROJECTION | exposure.kv['CC'] | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s1 | default | exposure | session_id | PREDICATE | (exposure.session_id = tmpb.s1) | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s1 | default | tmp_test_b | s1 | PREDICATE | (exposure.session_id = tmpb.s1) | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s2 | default | exposure | day | PREDICATE | (UDFToDouble(exposure.day) = 2.0190101E7) | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s2 | default | exposure | session_id | PREDICATE | (exposure.session_id = tmpb.s1) | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s2 | default | tmp_test_b | s1 | PREDICATE | (exposure.session_id = tmpb.s1) | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
| default | tmp_test_a | s2 | default | tmp_test_b | s2 | PROJECTION | NULL | 2023-08-08 14:39:43 | 2023-08-08 14:39:43 |
+---------+------------+-----+-----------+--------------+------------+------------+-------------------------------------------+---------------------+---------------------+
8 rows in set (0.00 sec)
mysql>
更多推荐
0
0- 0
已为社区贡献7条内容
所有评论(0)