How to check if a column exists in Impala table?

Question

I have created an external table in Impala. I am writing a shell script which checks whether a particular column exists in that table.

We can do this in MySql using the following query.

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name'

But, in Impala, how can we achieve this?

Answer 1

Unfortunately you cannot query the schema metadata in Impala. You can use describe table (see the documentation) and check the output.

Answer 2

There is SHOW Statement in Cloudera Impala:

The SHOW statement is a flexible way to get information about different types of Impala objects.

For your use case you can use SHOW COLUMN STATS Statement, e.g.:

SHOW COLUMN STATS myTableName

It will return following information:

+------------------------+--------+------------------+--------+----------+----------+
| Column                 | Type   | #Distinct Values | #Nulls | Max Size | Avg Size |
+------------------------+--------+------------------+--------+----------+----------+
| my_column_id           | INT    | -1               | -1     | 4        | 4        |
| my_string_column_name  | STRING | -1               | -1     | -1       | -1       |
| some_column_name       | INT    | -1               | -1     | 4        | 4        |
...