This is an automated email from the ASF dual-hosted git repository.
dkuzmenko pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new d06957f254e HIVE-26480: Improve SYS.COMPACTIONS and SYS.TRANSACTIONS
views (Laszlo Vegh, reviewed by Denys Kuzmenko)
d06957f254e is described below
commit d06957f254e026e719f30027d161264be43386b0
Author: veghlaci05 <[email protected]>
AuthorDate: Wed Aug 31 11:15:53 2022 +0200
HIVE-26480: Improve SYS.COMPACTIONS and SYS.TRANSACTIONS views (Laszlo
Vegh, reviewed by Denys Kuzmenko)
Closes #3529
---
.../hive/hive-schema-4.0.0-alpha-2.hive.sql | 18 ++++----
...upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.hive.sql | 51 +++++++++++++++++++---
ql/src/test/queries/clientpositive/sysdb.q | 2 +-
3 files changed, 54 insertions(+), 17 deletions(-)
diff --git a/metastore/scripts/upgrade/hive/hive-schema-4.0.0-alpha-2.hive.sql
b/metastore/scripts/upgrade/hive/hive-schema-4.0.0-alpha-2.hive.sql
index c1d16486a53..815379a2479 100644
--- a/metastore/scripts/upgrade/hive/hive-schema-4.0.0-alpha-2.hive.sql
+++ b/metastore/scripts/upgrade/hive/hive-schema-4.0.0-alpha-2.hive.sql
@@ -1221,15 +1221,15 @@ SELECT
CASE WHEN CC_WORKER_ID IS NULL THEN cast (null as string) ELSE
split(CC_WORKER_ID,"-")[0] END,
CASE WHEN CC_WORKER_ID IS NULL THEN cast (null as string) ELSE
split(CC_WORKER_ID,"-")[size(split(CC_WORKER_ID,"-"))-1] END,
CC_WORKER_VERSION,
- CC_ENQUEUE_TIME,
- CC_START,
+ FROM_UNIXTIME(CC_ENQUEUE_TIME DIV 1000),
+ FROM_UNIXTIME(CC_START DIV 1000),
CASE WHEN CC_END IS NULL THEN cast (null as string) ELSE CC_END-CC_START END,
CC_HADOOP_JOB_ID,
CC_RUN_AS,
CC_ERROR_MESSAGE,
CC_NEXT_TXN_ID,
CC_TXN_ID,
- CC_COMMIT_TIME,
+ FROM_UNIXTIME(CC_COMMIT_TIME DIV 1000),
CC_HIGHEST_WRITE_ID,
CASE WHEN CC_INITIATOR_ID IS NULL THEN cast (null as string) ELSE
split(CC_INITIATOR_ID,"-")[0] END,
CASE WHEN CC_INITIATOR_ID IS NULL THEN cast (null as string) ELSE
split(CC_INITIATOR_ID,"-")[size(split(CC_INITIATOR_ID,"-"))-1] END,
@@ -1249,20 +1249,20 @@ SELECT
CASE WHEN CQ_WORKER_ID IS NULL THEN NULL ELSE split(CQ_WORKER_ID,"-")[0] END,
CASE WHEN CQ_WORKER_ID IS NULL THEN NULL ELSE
split(CQ_WORKER_ID,"-")[size(split(CQ_WORKER_ID,"-"))-1] END,
CQ_WORKER_VERSION,
- CQ_ENQUEUE_TIME,
- CQ_START,
+ FROM_UNIXTIME(CQ_ENQUEUE_TIME DIV 1000),
+ FROM_UNIXTIME(CQ_START DIV 1000),
cast (null as string),
CQ_HADOOP_JOB_ID,
CQ_RUN_AS,
CQ_ERROR_MESSAGE,
CQ_NEXT_TXN_ID,
CQ_TXN_ID,
- CQ_COMMIT_TIME,
+ FROM_UNIXTIME(CQ_COMMIT_TIME DIV 1000),
CQ_HIGHEST_WRITE_ID,
CASE WHEN CQ_INITIATOR_ID IS NULL THEN NULL ELSE
split(CQ_INITIATOR_ID,"-")[0] END,
CASE WHEN CQ_INITIATOR_ID IS NULL THEN NULL ELSE
split(CQ_INITIATOR_ID,"-")[size(split(CQ_INITIATOR_ID,"-"))-1] END,
CQ_INITIATOR_VERSION,
- CQ_CLEANER_START,
+ FROM_UNIXTIME(CQ_CLEANER_START DIV 1000),
CQ_TBLPROPERTIES
FROM COMPACTION_QUEUE;
@@ -1398,8 +1398,8 @@ CREATE OR REPLACE VIEW `TRANSACTIONS` (
SELECT DISTINCT
T.`TXN_ID`,
CASE WHEN T.`TXN_STATE` = 'o' THEN 'open' WHEN T.`TXN_STATE` = 'a' THEN
'aborted' WHEN T.`TXN_STATE` = 'c' THEN 'commited' ELSE 'UNKNOWN' END AS
TXN_STATE,
- FROM_UNIXTIME(T.`TXN_STARTED`) AS TXN_STARTED,
- FROM_UNIXTIME(T.`TXN_LAST_HEARTBEAT`) AS TXN_LAST_HEARTBEAT,
+ FROM_UNIXTIME(T.`TXN_STARTED` DIV 1000) AS TXN_STARTED,
+ FROM_UNIXTIME(T.`TXN_LAST_HEARTBEAT` DIV 1000) AS TXN_LAST_HEARTBEAT,
T.`TXN_USER`,
T.`TXN_HOST`,
T.`TXN_AGENT_INFO`,
diff --git
a/metastore/scripts/upgrade/hive/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.hive.sql
b/metastore/scripts/upgrade/hive/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.hive.sql
index ac520136821..8c5f299804a 100644
---
a/metastore/scripts/upgrade/hive/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.hive.sql
+++
b/metastore/scripts/upgrade/hive/upgrade-4.0.0-alpha-1-to-4.0.0-alpha-2.hive.sql
@@ -125,6 +125,7 @@ FROM \"COMPLETED_COMPACTIONS\"
"
);
+--HIVE-26480
CREATE OR REPLACE VIEW `COMPACTIONS`
(
`C_ID`,
@@ -165,15 +166,15 @@ SELECT
CASE WHEN CC_WORKER_ID IS NULL THEN cast (null as string) ELSE
split(CC_WORKER_ID,"-")[0] END,
CASE WHEN CC_WORKER_ID IS NULL THEN cast (null as string) ELSE
split(CC_WORKER_ID,"-")[size(split(CC_WORKER_ID,"-"))-1] END,
CC_WORKER_VERSION,
- CC_ENQUEUE_TIME,
- CC_START,
+ FROM_UNIXTIME(CC_ENQUEUE_TIME DIV 1000),
+ FROM_UNIXTIME(CC_START DIV 1000),
CASE WHEN CC_END IS NULL THEN cast (null as string) ELSE CC_END-CC_START
END,
CC_HADOOP_JOB_ID,
CC_RUN_AS,
CC_ERROR_MESSAGE,
CC_NEXT_TXN_ID,
CC_TXN_ID,
- CC_COMMIT_TIME,
+ FROM_UNIXTIME(CC_COMMIT_TIME DIV 1000),
CC_HIGHEST_WRITE_ID,
CASE WHEN CC_INITIATOR_ID IS NULL THEN cast (null as string) ELSE
split(CC_INITIATOR_ID,"-")[0] END,
CASE WHEN CC_INITIATOR_ID IS NULL THEN cast (null as string) ELSE
split(CC_INITIATOR_ID,"-")[size(split(CC_INITIATOR_ID,"-"))-1] END,
@@ -193,23 +194,59 @@ SELECT
CASE WHEN CQ_WORKER_ID IS NULL THEN NULL ELSE split(CQ_WORKER_ID,"-")[0]
END,
CASE WHEN CQ_WORKER_ID IS NULL THEN NULL ELSE
split(CQ_WORKER_ID,"-")[size(split(CQ_WORKER_ID,"-"))-1] END,
CQ_WORKER_VERSION,
- CQ_ENQUEUE_TIME,
- CQ_START,
+ FROM_UNIXTIME(CQ_ENQUEUE_TIME DIV 1000),
+ FROM_UNIXTIME(CQ_START DIV 1000),
cast (null as string),
CQ_HADOOP_JOB_ID,
CQ_RUN_AS,
CQ_ERROR_MESSAGE,
CQ_NEXT_TXN_ID,
CQ_TXN_ID,
- CQ_COMMIT_TIME,
+ FROM_UNIXTIME(CQ_COMMIT_TIME DIV 1000),
CQ_HIGHEST_WRITE_ID,
CASE WHEN CQ_INITIATOR_ID IS NULL THEN NULL ELSE
split(CQ_INITIATOR_ID,"-")[0] END,
CASE WHEN CQ_INITIATOR_ID IS NULL THEN NULL ELSE
split(CQ_INITIATOR_ID,"-")[size(split(CQ_INITIATOR_ID,"-"))-1] END,
CQ_INITIATOR_VERSION,
- CQ_CLEANER_START,
+ FROM_UNIXTIME(CQ_CLEANER_START DIV 1000),
CQ_TBLPROPERTIES
FROM COMPACTION_QUEUE;
+CREATE OR REPLACE VIEW `TRANSACTIONS` (
+ `TXN_ID`,
+ `STATE`,
+ `STARTED`,
+ `LAST_HEARTBEAT`,
+ `USER`,
+ `HOST`,
+ `AGENT_INFO`,
+ `META_INFO`,
+ `HEARTBEAT_COUNT`,
+ `TYPE`,
+ `TC_DATABASE`,
+ `TC_TABLE`,
+ `TC_PARTITION`,
+ `TC_OPERATION_TYPE`,
+ `TC_WRITEID`
+) AS
+SELECT DISTINCT
+ T.`TXN_ID`,
+ CASE WHEN T.`TXN_STATE` = 'o' THEN 'open' WHEN T.`TXN_STATE` = 'a' THEN
'aborted' WHEN T.`TXN_STATE` = 'c' THEN 'commited' ELSE 'UNKNOWN' END AS
TXN_STATE,
+ FROM_UNIXTIME(T.`TXN_STARTED` DIV 1000) AS TXN_STARTED,
+ FROM_UNIXTIME(T.`TXN_LAST_HEARTBEAT` DIV 1000) AS TXN_LAST_HEARTBEAT,
+ T.`TXN_USER`,
+ T.`TXN_HOST`,
+ T.`TXN_AGENT_INFO`,
+ T.`TXN_META_INFO`,
+ T.`TXN_HEARTBEAT_COUNT`,
+ CASE WHEN T.`TXN_TYPE` = 0 THEN 'DEFAULT' WHEN T.`TXN_TYPE` = 1 THEN
'REPL_CREATED' WHEN T.`TXN_TYPE` = 2 THEN 'READ_ONLY' WHEN T.`TXN_TYPE` = 3
THEN 'COMPACTION' END AS TXN_TYPE,
+ TC.`TC_DATABASE`,
+ TC.`TC_TABLE`,
+ TC.`TC_PARTITION`,
+ CASE WHEN TC.`TC_OPERATION_TYPE` = 's' THEN 'SELECT' WHEN
TC.`TC_OPERATION_TYPE` = 'i' THEN 'INSERT' WHEN TC.`TC_OPERATION_TYPE` = 'u'
THEN 'UPDATE' WHEN TC.`TC_OPERATION_TYPE` = 'c' THEN 'COMPACT' END AS
OPERATION_TYPE,
+ TC.`TC_WRITEID`
+FROM `SYS`.`TXNS` AS T
+ LEFT JOIN `SYS`.`TXN_COMPONENTS` AS TC ON T.`TXN_ID` = TC.`TC_TXNID`;
+
USE INFORMATION_SCHEMA;
create or replace view SCHEDULED_EXECUTIONS as
diff --git a/ql/src/test/queries/clientpositive/sysdb.q
b/ql/src/test/queries/clientpositive/sysdb.q
index 4495a2e6ce0..cec1819a27f 100644
--- a/ql/src/test/queries/clientpositive/sysdb.q
+++ b/ql/src/test/queries/clientpositive/sysdb.q
@@ -1,7 +1,7 @@
--! qt:dataset:src,part,srcbucket:ONLY
--! qt:sysdb
-- Mask the enqueue time which is based on current time
---!
qt:replace:/(initiated\s+NULL\s+NULL\s+NULL\s+)[0-9]*(\s+NULL)/$1#Masked#$2/
+--! qt:replace:/(initiated\s+NULL\s+NULL\s+NULL\s+)[0-9\-]*
[0-9:]*(\s+NULL)/$1#Masked#$2/
-- Mask the hostname in compaction view
--! qt:replace:/(NULL\s+)[\S]*(\s+manual)/$1#Masked#$2/
