In Hive, how can I add a column only if that column does not exist?

Question

I would like to add a new column to a table, but only if that column does not already exist.

This works if the column does not exist:

ALTER TABLE MyTable ADD COLUMNS (mycolumn string);

But when I execute it a second time, I get an error.

Column 'mycolumn' exists

When I try to use the "IF NOT EXISTS" syntax that is supported for CREATE TABLE and ADD PARTITION, I get a syntax error:

ALTER TABLE MyTable ADD IF NOT EXISTS COLUMNS (mycolumn string);
FAILED: ParseException line 3:42 required (...)+ loop did not match anything at input 'COLUMNS' in add partition statement

What I need is something that can execute itempotently so I can run my query whether this column exists or not.

Answer 1

You can partially work it around, by setting the hive.cli.errors.ignore flag. In this case hive CLI will force the execution of further queries even when queries on the way fail.

In this example:

SET hive.cli.errors.ignore=true;
ALTER TABLE MyTable ADD COLUMNS (mycolumn string);
ALTER TABLE MyTable ADD COLUMNS (mycolumn string);
ALTER TABLE MyTable ADD COLUMNS (mycolumn2 string);

hive will execute all queries, even though there'll be an error in the second query.

Answer 2

Well there is no direct way to do that. I mean through a single query. There are two other ways:

1.) Using JDBC:

1.1) Do describe on the table name.
1.2) You will get a list of columns in result set.
1.3) Check if your columns exists or not by iterating through the result set.

2.) Using hive Metastore client:

2.1) Create a object of HiveMetastoreClient
2.2) HiveMetastoreClient.getFields(<>db_name, <table_name>).get(index).getName() will give you the column name.
2.3) Check if your column exists of not by comparing the list.

Hope it helps...!!!