利用hive的Create_timeAccesstime做数据质量管理

-- You need to run the following command:

describe formatted <your_table_name>;
-- Or if you need this information about a particular partition:

describe formatted <your_table_name> partition (<partition_field>=<value>);

show create table  someyewu_application_feature_sub_order;

createtab_stmt
CREATE TABLE ` someyewu_application_feature_sub_order`(
  `order_id` bigint COMMENT '订单id',
  `parent_order_id` bigint COMMENT '父订单id',
  `uid` bigint COMMENT '客户id',
  `create_time` bigint COMMENT '订单创建时间',
  `price_amt` decimal(20,2) COMMENT '消费分期本金',
  `monthly_installment_payment_amt` decimal(20,2) COMMENT '月供',
  `periods` int COMMENT '分期期数',
  `pay_offs` bigint COMMENT '还清次数',
  `province` string COMMENT '下单时GPS城市',
  `city` string COMMENT '下单时GPS地区',
  `login_ip` string COMMENT '下单IP',
  `device_id` string COMMENT '登录设备号',
  `etl_time` string COMMENT '数据仓库创建时间')
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://nameservice1/user/hive/warehouse/ads.db/ someyewu_application_feature_sub_order'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='true',
  'numFiles'='265',
  'numRows'='104786514',
  'rawDataSize'='1362224682',
  'totalSize'='9395667259',
  'transient_lastDdlTime'='1593554048') -- Your time zone: Wednesday, July 1, 2020 5:54:08 AM GMT+08:00

set hive.security.authorization.sqlstd.confwhitelist.append;
set hive.security.authorization.sqlstd.confwhitelist;

set hive.exec.pre.hooks;
set hive.security.authorization.sqlstd.confwhitelist;
set hive.security.authorization.sqlstd.confwhitelist.append;
set hive.security.authorization.sqlstd.confwhitelist=hive.exec.pre.hooks;
set hive.exec.pre.hooks=org.apache.hadoop.hive.ql.hooks.UpdateInputAccessTimeHook$PreExec;
 show table extended like  "f_tedcom_installmentdb_a_20190825" ;
DESCRIBE formatted   f_tedcom_installmentdb_a_20190825 ;
 SELECT * FROM ods.f_tedcom_installmentdb_a_20190825 LIMIT 100;
 show create table f_tedcom_installmentdb_a_20190825 ;

show table extended in dwd like tedcom_hardware_wifi_new_details ;
show create table tedcom_hardware_wifi_new_details ;
SELECT * from  tedcom_hardware_wifi_new_details  limit 100 ;

set hive.security.authorization.sqlstd.confwhitelist.append=hive\.exec\.pre\.hooks
use recommend;



-- 设置属性
set hive.security.authorization.sqlstd.confwhitelist=hive.exec.pre.hooks;

set hive.exec.pre.hooks=org.apache.hadoop.hive.ql.hooks.UpdateInputAccessTimeHook$PreExec;
--这个属性不能再运行时修改。

-- 查看属性值
set hive.security.authorization.sqlstd.confwhitelist;
hive.security.authorization.sqlstd.confwhitelist=hive\.auto\..*|hive\.cbo\..*|hive\.convert\..*|hive\.exec\.dynamic\.partition.*|hive\.exec\..*\.dynamic\.partitions\..*|hive\.exec\.compress\..*|hive\.exec\.infer\..*|hive\.exec\.mode.local\..*|hive\.exec\.orc\..*|hive\.fetch.task\..*|hive\.hbase\..*|hive\.index\..*|hive\.index\..*|hive\.intermediate\..*|hive\.join\..*|hive\.limit\..*|hive\.mapjoin\..*|hive\.merge\..*|hive\.optimize\..*|hive\.orc\..*|hive\.outerjoin\..*|hive\.ppd\..*|hive\.prewarm\..*|hive\.skewjoin\..*|hive\.smbjoin\..*|hive\.stats\..*|hive\.tez\..*|hive\.vectorized\..*|mapred\.map\..*|mapred\.reduce\..*|mapred\.output\.compression\.codec|mapreduce\.job\.reduce\.slowstart\.completedmaps|mapreduce\.job\.queuename|mapreduce\.input\.fileinputformat\.split\.minsize|mapreduce\.map\..*|mapreduce\.reduce\..*



show table extended in ads like  someyewu_application_feature_sub_order;
show create table  someyewu_application_feature_sub_order;

DESCRIBE formatted   f_tedcom_installmentdb_a_20190825 ;  --执行这个语句会更新LastAccessTime: 同时反映到数据库
col_name	data_type	comment
# col_name            	data_type           	comment
	NULL	NULL
item_id	bigint
sku_id	bigint
count	bigint
etl_time	string
	NULL	NULL
# Detailed Table Information	NULL	NULL
Database:           	ods                 	NULL
Owner:              	hive                	NULL
CreateTime:         	Mon Aug 26 07:49:51 UTC 2019	NULL
LastAccessTime:     	Tue Jul 07 02:50:16 UTC 2020	NULL
Protect Mode:       	None                	NULL
Retention:          	0                   	NULL
Location:           	hdfs://nameservice1/user/hive/warehouse/ods.db/f_tedcom_installmentdb_a_20190825	NULL
Table Type:         	MANAGED_TABLE       	NULL
Table Parameters:	NULL	NULL
	COLUMN_STATS_ACCURATE	false
	numFiles            	0
	numRows             	-1
	rawDataSize         	-1
	totalSize           	0
	transient_lastDdlTime	1594090216
	NULL	NULL
# Storage Information	NULL	NULL
SerDe Library:      	org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe	NULL
InputFormat:        	org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat	NULL
OutputFormat:       	org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat	NULL
Compressed:         	No                  	NULL
Num Buckets:        	-1                  	NULL
Bucket Columns:     	[]                  	NULL
Sort Columns:       	[]                  	NULL
Storage Desc Params:	NULL	NULL
	serialization.format	1



 show create table f_tedcom_installmentdb_a_20190825 ; --  会更新transient_lastDdlTime  同时更新数据库LAST_ACCESS_TIME
createtab_stmt
CREATE TABLE `f_tedcom_installmentdb_a_20190825`(
  `item_id` bigint,
  `sku_id` bigint,
  `count` bigint,
  `etl_time` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://nameservice1/user/hive/warehouse/ods.db/f_tedcom_installmentdb_a_20190825'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'numFiles'='0',
  'numRows'='-1',
  'rawDataSize'='-1',
  'totalSize'='0',
  'transient_lastDdlTime'='1594090281')  --2020/7/7 上午10:51:21

 SELECT * FROM ods.f_tedcom_installmentdb_a_20190825 LIMIT 100; -- 会更新数据库LAST_ACCESS_TIME

SELECT
  from_unixtime(CREATE_TIME),
  from_unixtime(LAST_ACCESS_TIME),
  t.OWNER,
  t.TBL_NAME,
  TBL_ID ,
  d.NAME
FROM
  hive.TBLS t,
  hive.DBS d
where
  t.DB_ID = d.DB_ID
  and t.TBL_NAME = "f_tedcom_installmentdb_a_20190825" ;
| from_unixtime(CREATE_TIME ) | from_unixtime(LAST_ACCESS_TIME) | OWNER | TBL_NAME                           | TBL_ID | NAME |
|:----------------------------|:--------------------------------|:------|:-----------------------------------|:-------|:-----|
| 2019-08-26 07:49:51.0       | 2020-07-07 03:06:03.0           | hive  | f_tedcom_installmentdb_a_20190825 | 8267   | ods  |