You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Julian Keppel <ju...@gmail.com> on 2015/06/09 15:46:09 UTC

Alter table statement for "create table like"-tables

I use Hive Version 1.1.0 in Cloudera CDH 5.4.0.

I have created an external table:

CREATE EXTERNAL TABLE *tableA*
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS
    INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
    OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION 'location'
TBLPROPERTIES ('avro.schema.url'=schema_location');

Now I wanted to create a managed table with exactly the same columns except
of one additional column (a generated hash key). The only idea I had was to
create the table with: CREATE TABLE *tableB* LIKE *tableA*;

And then add the additional column with: ALTER TABLE *tableB* ADD COLUMNS
(new_column INT);

The statements run without any errors or exceptions (even in the log files
under /var/log/hive) but the new column doesn't appear.

What am I doing wrong? Or is this not possible? What other ideas do you
have for my use case?

Thank you in advance for your help!

Re: Alter table statement for "create table like"-tables

Posted by Julian Keppel <ju...@gmail.com>.
I tried it with an external table with csv format now. This worked
properly. So it really seems to has to do with avro as data format.

The Jira ticket https://issues.apache.org/jira/browse/HIVE-7446 was closed
and the corresponding patch was added to release 0.14. Should I open a new
ticket?

2015-06-16 20:24 GMT+02:00 Jason Dere <jd...@hortonworks.com>:

>  Probably has to do with the fact that it is an Avro table.  I don't have
> any experience using Avro, but maybe take a look at
> https://issues.apache.org/jira/browse/HIVE-7446 for some of the issues
> described there, or maybe look at the test that was added for that Jira.
>
>
>  On Jun 16, 2015, at 2:42 AM, Julian Keppel <ju...@gmail.com>
> wrote:
>
>  *Push* Does no one have an idea or hit similar issues?
>
> 2015-06-09 15:46 GMT+02:00 Julian Keppel <ju...@gmail.com>:
>
>> I use Hive Version 1.1.0 in Cloudera CDH 5.4.0.
>>
>>  I have created an external table:
>>
>>  CREATE EXTERNAL TABLE *tableA*
>> ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
>> STORED AS
>>     INPUTFORMAT
>> 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
>>     OUTPUTFORMAT
>> 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
>> LOCATION 'location'
>> TBLPROPERTIES ('avro.schema.url'=schema_location');
>>
>>  Now I wanted to create a managed table with exactly the same columns
>> except of one additional column (a generated hash key). The only idea I had
>> was to create the table with: CREATE TABLE *tableB* LIKE *tableA*;
>>
>>  And then add the additional column with: ALTER TABLE *tableB* ADD
>> COLUMNS (new_column INT);
>>
>>  The statements run without any errors or exceptions (even in the log
>> files under /var/log/hive) but the new column doesn't appear.
>>
>>  What am I doing wrong? Or is this not possible? What other ideas do you
>> have for my use case?
>>
>>  Thank you in advance for your help!
>>
>
>
>

Re: Alter table statement for "create table like"-tables

Posted by Jason Dere <jd...@hortonworks.com>.
Probably has to do with the fact that it is an Avro table.  I don't have any experience using Avro, but maybe take a look at https://issues.apache.org/jira/browse/HIVE-7446 for some of the issues described there, or maybe look at the test that was added for that Jira.


On Jun 16, 2015, at 2:42 AM, Julian Keppel <ju...@gmail.com>> wrote:

*Push* Does no one have an idea or hit similar issues?

2015-06-09 15:46 GMT+02:00 Julian Keppel <ju...@gmail.com>>:
I use Hive Version 1.1.0 in Cloudera CDH 5.4.0.

I have created an external table:

CREATE EXTERNAL TABLE *tableA*
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS
    INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION 'location'
TBLPROPERTIES ('avro.schema.url'=schema_location');

Now I wanted to create a managed table with exactly the same columns except of one additional column (a generated hash key). The only idea I had was to create the table with: CREATE TABLE *tableB* LIKE *tableA*;

And then add the additional column with: ALTER TABLE *tableB* ADD COLUMNS (new_column INT);

The statements run without any errors or exceptions (even in the log files under /var/log/hive) but the new column doesn't appear.

What am I doing wrong? Or is this not possible? What other ideas do you have for my use case?

Thank you in advance for your help!

Re: Alter table statement for "create table like"-tables

Posted by Julian Keppel <ju...@gmail.com>.
*Push* Does no one have an idea or hit similar issues?

2015-06-09 15:46 GMT+02:00 Julian Keppel <ju...@gmail.com>:

> I use Hive Version 1.1.0 in Cloudera CDH 5.4.0.
>
> I have created an external table:
>
> CREATE EXTERNAL TABLE *tableA*
> ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
> STORED AS
>     INPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
>     OUTPUTFORMAT
> 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
> LOCATION 'location'
> TBLPROPERTIES ('avro.schema.url'=schema_location');
>
> Now I wanted to create a managed table with exactly the same columns
> except of one additional column (a generated hash key). The only idea I had
> was to create the table with: CREATE TABLE *tableB* LIKE *tableA*;
>
> And then add the additional column with: ALTER TABLE *tableB* ADD COLUMNS
> (new_column INT);
>
> The statements run without any errors or exceptions (even in the log files
> under /var/log/hive) but the new column doesn't appear.
>
> What am I doing wrong? Or is this not possible? What other ideas do you
> have for my use case?
>
> Thank you in advance for your help!
>