last access time of a table is showing zero

  • 0

last access time of a table is showing zero

If you many hundreds or thousands tables and you want to know when was the last time your hive table accessed then you can run following mysql query in mysql under hive database.

mysql> use hive;

mysql> select TBL_NAME,LAST_ACCESS_TIME from TBLS where DB_ID=<db_id>;
+—————————————————————————————————-+——————+
| TBL_NAME | LAST_ACCESS_TIME |
+—————————————————————————————————-+——————+
| df_nov_4 | 0 |
| google_feed_20151111 | 0 |
| null_recos | 0 |
| taxonomy_20160107 | 0 |

Note : but unfortunately due to bug https://issues.apache.org/jira/browse/HIVE-2526 you can not do that without making some configuration changes like follow.

1. From Ambari > Hive > Advanced > Custom hive-site, edit (if it exists) or add a new property:
hive.security.authorization.sqlstd.confwhitelist=hive\.exec\.pre\.hooks

2. From Ambari > Hive > Advanced > General, edit hive.exec.pre.hooks property and append the following to the end of the field value (comma separated):
org.apache.hadoop.hive.ql.hooks.UpdateInputAccessTimeHook$PreExec

3. Restart the affected Hive services after saving the config changes in Ambari.

4. After the restart, try running the query (select TBL_NAME,LAST_ACCESS_TIME from TBLS where DB_ID=<db_id>;) again and this time you should be able to see the last access time.

 

I hope it will help you to get your work done, feel free to give your valuable feedback.