Exclude particular fields from SELECT queries in Hive

Hive is a high level language to analyse large volumes of data. The easiest way to select specific columns in Hive query is by specifying the column name in the select statement.

SELECT col1, col3, col4 .... 
FROM Table1;

But imagine your table contains many columns (i.e : more than 100 columns) and you need to only exclude a few columns in the select statement. Therefore, Hive query should be able to select all the columns excluding the defined columns in the query. To achieve it you need to follow these steps.

In ‘hive-site.xml’ add the following configuration,


and execute the query as

SELECT `(extract_date)?+.+` FROM <TABLE_NAME>;

with appropriate column and table name.

i.e Think you need to exclude only ‘transaction_date’ column in a select statement from a ‘cart’ table. Then the query will be,

SELECT `( transaction_date)?+.+`
FROM cart;