List Impala tables that need invalidate/refresh

Invalidate Metadata:

As a workaround, create a shell script to do the below steps.

1. Using beeline, connect to a particular database and run show tables statement and save output data to a file.
2. Using impala-shell, connect to the same particular database and run show tables statement and save output data to another file.
3. Now compare both the file to remove the duplicates and get the unique tables list from the first file which is a list of tables which are only in hive but not in impala.

Note:

a. Instead of a particular database each at a time in 1 and 2 steps, you can loop over all databases and save the output to a file. Inside the loop itself, you can redirect and append the output files to another final output file with data in some format like database.table or database_table to get all tables from all databases into a single file. Finally, follow step 3.

b. The unique tables from the second output file after removing duplicates will be tables that are deleted in hive and invalidate metadata needs to be run in impala to remove them from the impala list.

c. Rename of a table in impala can be recognized by hive but vice-versa is not possible and invalidate metadata should be run for both old and new table names to remove and add respectively in impala. This applies to most operations not just rename of table.

Refresh:

Consider a text format table with 2 columns and 1 row data. Now suppose, a third column is added to that table in the beeline.

select * from table; ---gives 3 columns in beeline and 2 columns in impala since refresh is not run on impala for this table.

If we run compute stats in impala before running refresh in this case, then that newly added column from the beeline will be removed from the table schema in hive as well.

select * from table; ---gives 2 columns in beeline and 2 columns in impala since compute stats from impala deleted the extra column metadata of table although data resides in hdfs for that column. This might cause parsing issues in impala if the column is added somewhere in the middle or front instead of ending.

So it is advised to run REFRESH table name in impala right after adding a new column or doing any modifications in beeline for an existing table to not lose table schema as explained in the above scenario.

refresh table; ---Right after modification in hive run refresh in impala.

select * from table; ---gives 3 columns in beeline and 3 columns in impala since refresh is run before compute stats in impala.