[Ask for help] How to “create table as ” in HIVE using avro format?

[Mengwei Shi <sm...@gmail.com>]

Hi,



i met some problems in using HIVE to process avro formatted files. Could
anyone help me?



Basically, I have an avro-formatted HIVE table defined as follows:



create external table if not exists AvroTestTable

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 '<mylocation>'

tblproperties ('avro.schema.literal'='{

    "name": "AvroTestTable",

    "type": "record",

  "fields" : [ {

    "name" : "Id",

    "type" : [ "int", "null" ]

  }, {

    "name" : "Subject",

    "type" : [ "string", "null" ]

  }

    ]}'

);



Now i want a second (temporary and internal) table whose data comes from
the first table. my query is this:



create table if not exists DerivedTable

row format serde 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'

as select Subject from AvroTestTable;



this would give me error:

FAILED: SemanticException Line 0:-1 Cannot insert into target table because
column number/types are different 'TOK_TMP_FILE': Table insclause-0 has 2
columns, but query has 1 columns.


If my tables were stored as textfile, then i can "create table B as select
whatever from A" without specifying the schema for table B.

But when my tables are in avro format, this seems to be not working. Where
could the problem be?



Thanks.