Geek Answers Handbook

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

I would like to add a new column to the table, but only if this column does not exist yet.

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 message.

Column 'mycolumn' exists

When I try to use the "IF NOT EXISTS" syntax, which 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

I need something that can be done itempotently, so I can run my query if this column exists or not.

+4
source share
2 answers

, hive.cli.errors.ignore. CLI CLI , .

:

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 , .

+2

. . :

1.) 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.) hive Metastore:

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.

, ...!!!

+1

More articles:


All Articles