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.
解决方案
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.