Search code examples
hadoophivedruidexternal-tables

alter table/add columns in non native table in hive

I created a hive table with a storage handler and now I want to add a column to that table but it gives me below error:

[Code: 10134, SQL State: 42000]  Error while compiling statement: FAILED: 
SemanticException [Error 10134]: ALTER TABLE can only be used for [ADDPROPS, 
DROPPROPS] to a non-native table

As per the hive documentation any hive table you create with storage handler is non native table.

Here's a link https://cwiki.apache.org/confluence/display/Hive/StorageHandlers

There is a JIRA case for enhancement is open with Apache for the same. https://issues.apache.org/jira/browse/HIVE-1240

For ex, I am using Druid Storage Handler in my case.

I created a hive table using:

CREATE TABLE druid_table_1
(`__time` TIMESTAMP, `dimension1` STRING, `metric1` int)
STORED BY 'org.apache.hadoop.hive.druid.DruidStorageHandler';

and then I am trying to add a column:

ALTER TABLE druid_table_1 ADD COLUMNS (`dimension2` STRING);

With above approach I am getting an error.

Is there any other way to add a column to non native tables in hive without recreating it?

Solution

  • Patch is available in HDP 2.5+ from Hortonworks. Support for ADD columns has been added in ALTER statement.

    Column can be added into druid table using ALTER table DDL in hive. ALTER TABLE ADD COLUMNS (col_name data_type)

    There is no need to specify partition spec as these are druid backed hive tables and partition/storage is maintained by druid.