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 03ad025ada7 HIVE-22628: Add locks and transactions tables from sys db
to information_schema (Akshat Mathur, reviewed by Denys Kuzmenko)
03ad025ada7 is described below
commit 03ad025ada776c0d359124c6342615f1983c1a94
Author: Akshat <[email protected]>
AuthorDate: Thu Jan 12 21:23:18 2023 +0530
HIVE-22628: Add locks and transactions tables from sys db to
information_schema (Akshat Mathur, reviewed by Denys Kuzmenko)
Closes #3921
---
.../upgrade/hive/hive-schema-4.0.0.hive.sql | 86 +++++++++++++++++++++-
ql/src/test/queries/clientpositive/sysdb.q | 4 +
.../results/clientpositive/llap/resourceplan.q.out | 8 ++
.../llap/strict_managed_tables_sysdb.q.out | 12 +++
.../test/results/clientpositive/llap/sysdb.q.out | 85 ++++++++++++++++++++-
5 files changed, 191 insertions(+), 4 deletions(-)
diff --git a/metastore/scripts/upgrade/hive/hive-schema-4.0.0.hive.sql
b/metastore/scripts/upgrade/hive/hive-schema-4.0.0.hive.sql
index 9f006523aab..577778c6d62 100644
--- a/metastore/scripts/upgrade/hive/hive-schema-4.0.0.hive.sql
+++ b/metastore/scripts/upgrade/hive/hive-schema-4.0.0.hive.sql
@@ -2015,6 +2015,88 @@ JOIN
WHERE
SE.SCHEDULED_QUERY_ID=SQ.SCHEDULED_QUERY_ID;
+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
+ `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`
+FROM `SYS`.`TRANSACTIONS` AS TXN JOIN `sys`.`TBLS` T ON (TXN.`TC_TABLE` =
T.`TBL_NAME`)
+ JOIN `sys`.`DBS` D ON (TXN.`TC_DATABASE` =
D.`NAME`)
+ LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID`
= P.`TBL_ID`)
+WHERE
+ (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL
+ AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER'
+ OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR
P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))
+ AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer());
-
-
+CREATE OR REPLACE VIEW `LOCKS` (
+ `LOCK_EXT_ID`,
+ `LOCK_INT_ID`,
+ `TXNID`,
+ `DB`,
+ `TABLE`,
+ `PARTITION`,
+ `LOCK_STATE`,
+ `LOCK_TYPE`,
+ `LAST_HEARTBEAT`,
+ `ACQUIRED_AT`,
+ `USER`,
+ `HOST`,
+ `HEARTBEAT_COUNT`,
+ `AGENT_INFO`,
+ `BLOCKEDBY_EXT_ID`,
+ `BLOCKEDBY_INT_ID`
+) AS
+SELECT DISTINCT
+ `LOCK_EXT_ID`,
+ `LOCK_INT_ID`,
+ `TXNID`,
+ `DB`,
+ `TABLE`,
+ `PARTITION`,
+ `LOCK_STATE`,
+ `LOCK_TYPE`,
+ `LAST_HEARTBEAT`,
+ `ACQUIRED_AT`,
+ `USER`,
+ `HOST`,
+ `HEARTBEAT_COUNT`,
+ `AGENT_INFO`,
+ `BLOCKEDBY_EXT_ID`,
+ `BLOCKEDBY_INT_ID`
+FROM SYS.`LOCKS` AS L JOIN `sys`.`TBLS` T ON (L.`TABLE` = T.`TBL_NAME`)
+ JOIN `sys`.`DBS` D ON (L.`DB` = D.`NAME`)
+ LEFT JOIN `sys`.`TBL_PRIVS` P ON (T.`TBL_ID` =
P.`TBL_ID`)
+WHERE
+ (NOT restrict_information_schema() OR P.`TBL_ID` IS NOT NULL
+ AND (P.`PRINCIPAL_NAME`=current_user() AND P.`PRINCIPAL_TYPE`='USER'
+ OR ((array_contains(current_groups(), P.`PRINCIPAL_NAME`) OR
P.`PRINCIPAL_NAME` = 'public') AND P.`PRINCIPAL_TYPE`='GROUP'))
+ AND P.`TBL_PRIV`='SELECT' AND P.`AUTHORIZER`=current_authorizer());
diff --git a/ql/src/test/queries/clientpositive/sysdb.q
b/ql/src/test/queries/clientpositive/sysdb.q
index cec1819a27f..4ecd1881850 100644
--- a/ql/src/test/queries/clientpositive/sysdb.q
+++ b/ql/src/test/queries/clientpositive/sysdb.q
@@ -139,3 +139,7 @@ select * from COLUMN_PRIVILEGES order by GRANTOR, GRANTEE,
TABLE_SCHEMA, TABLE_N
select TABLE_SCHEMA, TABLE_NAME from views order by TABLE_SCHEMA, TABLE_NAME;
select * from compactions;
+
+select TXN_ID, STATE, AGENT_INFO, META_INFO, HEARTBEAT_COUNT, TYPE,
TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID from
TRANSACTIONS;
+
+select * from LOCKS;
diff --git a/ql/src/test/results/clientpositive/llap/resourceplan.q.out
b/ql/src/test/results/clientpositive/llap/resourceplan.q.out
index 0a07df4baf2..cd9c57dd31d 100644
--- a/ql/src/test/results/clientpositive/llap/resourceplan.q.out
+++ b/ql/src/test/results/clientpositive/llap/resourceplan.q.out
@@ -78,6 +78,10 @@ information_schema compactions
hive_test_user USER DELETE true -1 hive_test_us
information_schema compactions hive_test_user USER
INSERT true -1 hive_test_user
information_schema compactions hive_test_user USER
SELECT true -1 hive_test_user
information_schema compactions hive_test_user USER
UPDATE true -1 hive_test_user
+information_schema locks hive_test_user USER DELETE
true -1 hive_test_user
+information_schema locks hive_test_user USER INSERT
true -1 hive_test_user
+information_schema locks hive_test_user USER SELECT
true -1 hive_test_user
+information_schema locks hive_test_user USER UPDATE
true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER DELETE true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER INSERT true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER SELECT true -1 hive_test_user
@@ -98,6 +102,10 @@ information_schema tables hive_test_user
USER DELETE true -1 hive_test_user
information_schema tables hive_test_user USER INSERT
true -1 hive_test_user
information_schema tables hive_test_user USER SELECT
true -1 hive_test_user
information_schema tables hive_test_user USER UPDATE
true -1 hive_test_user
+information_schema transactions hive_test_user USER
DELETE true -1 hive_test_user
+information_schema transactions hive_test_user USER
INSERT true -1 hive_test_user
+information_schema transactions hive_test_user USER
SELECT true -1 hive_test_user
+information_schema transactions hive_test_user USER
UPDATE true -1 hive_test_user
information_schema views hive_test_user USER DELETE
true -1 hive_test_user
information_schema views hive_test_user USER INSERT
true -1 hive_test_user
information_schema views hive_test_user USER SELECT
true -1 hive_test_user
diff --git
a/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out
b/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out
index 6555050676a..e509d588687 100644
--- a/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out
+++ b/ql/src/test/results/clientpositive/llap/strict_managed_tables_sysdb.q.out
@@ -168,6 +168,10 @@ information_schema compactions
hive_test_user USER DELETE true -1 hive_test_us
information_schema compactions hive_test_user USER
INSERT true -1 hive_test_user
information_schema compactions hive_test_user USER
SELECT true -1 hive_test_user
information_schema compactions hive_test_user USER
UPDATE true -1 hive_test_user
+information_schema locks hive_test_user USER DELETE
true -1 hive_test_user
+information_schema locks hive_test_user USER INSERT
true -1 hive_test_user
+information_schema locks hive_test_user USER SELECT
true -1 hive_test_user
+information_schema locks hive_test_user USER UPDATE
true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER DELETE true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER INSERT true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER SELECT true -1 hive_test_user
@@ -188,6 +192,10 @@ information_schema tables hive_test_user
USER DELETE true -1 hive_test_user
information_schema tables hive_test_user USER INSERT
true -1 hive_test_user
information_schema tables hive_test_user USER SELECT
true -1 hive_test_user
information_schema tables hive_test_user USER UPDATE
true -1 hive_test_user
+information_schema transactions hive_test_user USER
DELETE true -1 hive_test_user
+information_schema transactions hive_test_user USER
INSERT true -1 hive_test_user
+information_schema transactions hive_test_user USER
SELECT true -1 hive_test_user
+information_schema transactions hive_test_user USER
UPDATE true -1 hive_test_user
information_schema views hive_test_user USER DELETE
true -1 hive_test_user
information_schema views hive_test_user USER INSERT
true -1 hive_test_user
information_schema views hive_test_user USER SELECT
true -1 hive_test_user
@@ -600,11 +608,13 @@ PREHOOK: Output: database:information_schema
PREHOOK: Output: information_schema@column_privileges
PREHOOK: Output: information_schema@columns
PREHOOK: Output: information_schema@compactions
+PREHOOK: Output: information_schema@locks
PREHOOK: Output: information_schema@scheduled_executions
PREHOOK: Output: information_schema@scheduled_queries
PREHOOK: Output: information_schema@schemata
PREHOOK: Output: information_schema@table_privileges
PREHOOK: Output: information_schema@tables
+PREHOOK: Output: information_schema@transactions
PREHOOK: Output: information_schema@views
POSTHOOK: query: DROP DATABASE IF EXISTS INFORMATION_SCHEMA CASCADE
POSTHOOK: type: DROPDATABASE
@@ -613,9 +623,11 @@ POSTHOOK: Output: database:information_schema
POSTHOOK: Output: information_schema@column_privileges
POSTHOOK: Output: information_schema@columns
POSTHOOK: Output: information_schema@compactions
+POSTHOOK: Output: information_schema@locks
POSTHOOK: Output: information_schema@scheduled_executions
POSTHOOK: Output: information_schema@scheduled_queries
POSTHOOK: Output: information_schema@schemata
POSTHOOK: Output: information_schema@table_privileges
POSTHOOK: Output: information_schema@tables
+POSTHOOK: Output: information_schema@transactions
POSTHOOK: Output: information_schema@views
diff --git a/ql/src/test/results/clientpositive/llap/sysdb.q.out
b/ql/src/test/results/clientpositive/llap/sysdb.q.out
index fb98a090b99..616b57a4274 100644
--- a/ql/src/test/results/clientpositive/llap/sysdb.q.out
+++ b/ql/src/test/results/clientpositive/llap/sysdb.q.out
@@ -124,6 +124,10 @@ information_schema compactions
hive_test_user USER DELETE true -1 hive_test_us
information_schema compactions hive_test_user USER
INSERT true -1 hive_test_user
information_schema compactions hive_test_user USER
SELECT true -1 hive_test_user
information_schema compactions hive_test_user USER
UPDATE true -1 hive_test_user
+information_schema locks hive_test_user USER DELETE
true -1 hive_test_user
+information_schema locks hive_test_user USER INSERT
true -1 hive_test_user
+information_schema locks hive_test_user USER SELECT
true -1 hive_test_user
+information_schema locks hive_test_user USER UPDATE
true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER DELETE true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER INSERT true -1 hive_test_user
information_schema scheduled_executions hive_test_user
USER SELECT true -1 hive_test_user
@@ -144,6 +148,10 @@ information_schema tables hive_test_user
USER DELETE true -1 hive_test_user
information_schema tables hive_test_user USER INSERT
true -1 hive_test_user
information_schema tables hive_test_user USER SELECT
true -1 hive_test_user
information_schema tables hive_test_user USER UPDATE
true -1 hive_test_user
+information_schema transactions hive_test_user USER
DELETE true -1 hive_test_user
+information_schema transactions hive_test_user USER
INSERT true -1 hive_test_user
+information_schema transactions hive_test_user USER
SELECT true -1 hive_test_user
+information_schema transactions hive_test_user USER
UPDATE true -1 hive_test_user
information_schema views hive_test_user USER DELETE
true -1 hive_test_user
information_schema views hive_test_user USER INSERT
true -1 hive_test_user
information_schema views hive_test_user USER SELECT
true -1 hive_test_user
@@ -641,20 +649,36 @@ key_constraints parent_tbl_id
key_constraints position
key_constraints update_rule
locks acquired_at
+locks acquired_at
+locks agent_info
locks agent_info
locks blockedby_ext_id
+locks blockedby_ext_id
+locks blockedby_int_id
locks blockedby_int_id
locks db
+locks db
+locks heartbeat_count
locks heartbeat_count
locks host
+locks host
locks last_heartbeat
+locks last_heartbeat
+locks lock_ext_id
locks lock_ext_id
locks lock_int_id
+locks lock_int_id
+locks lock_state
locks lock_state
locks lock_type
+locks lock_type
+locks partition
locks partition
locks table
+locks table
locks txnid
+locks txnid
+locks user
locks user
min_history_level mhl_min_open_txnid
min_history_level mhl_txnid
@@ -953,19 +977,34 @@ tbls tbl_type
tbls view_expanded_text
tbls view_original_text
transactions agent_info
+transactions agent_info
+transactions heartbeat_count
transactions heartbeat_count
transactions host
+transactions host
+transactions last_heartbeat
transactions last_heartbeat
transactions meta_info
+transactions meta_info
transactions started
+transactions started
+transactions state
transactions state
transactions tc_database
+transactions tc_database
transactions tc_operation_type
+transactions tc_operation_type
+transactions tc_partition
transactions tc_partition
transactions tc_table
+transactions tc_table
transactions tc_writeid
+transactions tc_writeid
+transactions txn_id
transactions txn_id
transactions type
+transactions type
+transactions user
transactions user
txn_components tc_database
txn_components tc_operation_type
@@ -1032,9 +1071,9 @@ POSTHOOK: Input: sys@columns_v2
#### A masked pattern was here ####
a decimal(10,2) 0
acquired_at string 9
+acquired_at string 9
action_expression string 4
active_execution_id bigint 8
-active_execution_id bigint 8
PREHOOK: query: select param_key, param_value from database_params order by
param_key, param_value limit 5
PREHOOK: type: QUERY
PREHOOK: Input: sys@database_params
@@ -1266,7 +1305,7 @@ POSTHOOK: query: select count(*) from sds
POSTHOOK: type: QUERY
POSTHOOK: Input: sys@sds
#### A masked pattern was here ####
-76
+78
PREHOOK: query: select param_key, param_value from sd_params order by
param_key, param_value limit 5
PREHOOK: type: QUERY
PREHOOK: Input: sys@sd_params
@@ -1641,11 +1680,13 @@ default default srcbucket BASE_TABLE NULL
NULL NULL NULL NULL YES NO NULL
default information_schema column_privileges VIEW NULL
NULL NULL NULL NULL NO NO NULL
default information_schema columns VIEW NULL NULL NULL
NULL NULL NO NO NULL
default information_schema compactions VIEW NULL NULL
NULL NULL NULL NO NO NULL
+default information_schema locks VIEW NULL NULL NULL
NULL NULL NO NO NULL
default information_schema scheduled_executions VIEW NULL
NULL NULL NULL NULL NO NO NULL
default information_schema scheduled_queries VIEW NULL
NULL NULL NULL NULL NO NO NULL
default information_schema schemata VIEW NULL NULL
NULL NULL NULL NO NO NULL
default information_schema table_privileges VIEW NULL
NULL NULL NULL NULL NO NO NULL
default information_schema tables VIEW NULL NULL NULL
NULL NULL NO NO NULL
+default information_schema transactions VIEW NULL NULL
NULL NULL NULL NO NO NULL
default information_schema views VIEW NULL NULL NULL
NULL NULL NO NO NULL
default sys bucketing_cols BASE_TABLE NULL NULL NULL
NULL NULL YES NO NULL
default sys cds BASE_TABLE NULL NULL NULL NULL
NULL YES NO NULL
@@ -1790,11 +1831,13 @@ POSTHOOK: Input: sys@tbls
information_schema column_privileges
information_schema columns
information_schema compactions
+information_schema locks
information_schema scheduled_executions
information_schema scheduled_queries
information_schema schemata
information_schema table_privileges
information_schema tables
+information_schema transactions
information_schema views
sys compactions
sys locks
@@ -1825,3 +1868,41 @@ POSTHOOK: Input: sys@tbls
#### A masked pattern was here ####
1 default default scr_txn NULL major initiated NULL NULL
NULL #Masked# NULL NULL NULL NULL NULL NULL NULL
NULL NULL #Masked# manual 4.0.0-SNAPSHOT NULL default NULL
2 default default scr_txn_2 NULL minor initiated NULL
NULL NULL #Masked# NULL NULL NULL NULL NULL NULL
NULL NULL NULL #Masked# manual 4.0.0-SNAPSHOT NULL default
NULL
+PREHOOK: query: select TXN_ID, STATE, AGENT_INFO, META_INFO, HEARTBEAT_COUNT,
TYPE, TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID from
TRANSACTIONS
+PREHOOK: type: QUERY
+PREHOOK: Input: information_schema@transactions
+PREHOOK: Input: sys@dbs
+PREHOOK: Input: sys@tbl_privs
+PREHOOK: Input: sys@tbls
+PREHOOK: Input: sys@transactions
+PREHOOK: Input: sys@txn_components
+PREHOOK: Input: sys@txns
+#### A masked pattern was here ####
+POSTHOOK: query: select TXN_ID, STATE, AGENT_INFO, META_INFO, HEARTBEAT_COUNT,
TYPE, TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID from
TRANSACTIONS
+POSTHOOK: type: QUERY
+POSTHOOK: Input: information_schema@transactions
+POSTHOOK: Input: sys@dbs
+POSTHOOK: Input: sys@tbl_privs
+POSTHOOK: Input: sys@tbls
+POSTHOOK: Input: sys@transactions
+POSTHOOK: Input: sys@txn_components
+POSTHOOK: Input: sys@txns
+#### A masked pattern was here ####
+PREHOOK: query: select * from LOCKS
+PREHOOK: type: QUERY
+PREHOOK: Input: information_schema@locks
+PREHOOK: Input: sys@dbs
+PREHOOK: Input: sys@hive_locks
+PREHOOK: Input: sys@locks
+PREHOOK: Input: sys@tbl_privs
+PREHOOK: Input: sys@tbls
+#### A masked pattern was here ####
+POSTHOOK: query: select * from LOCKS
+POSTHOOK: type: QUERY
+POSTHOOK: Input: information_schema@locks
+POSTHOOK: Input: sys@dbs
+POSTHOOK: Input: sys@hive_locks
+POSTHOOK: Input: sys@locks
+POSTHOOK: Input: sys@tbl_privs
+POSTHOOK: Input: sys@tbls
+#### A masked pattern was here ####
