CODEWITHSUNDEEP
sqlhadoophive
kyoungwon cho
kyoungwon cho

Reputation: 67

How do I add columns to a table created using serde when creating a Hive table?

hive> desc log23;
OK
col_name        data_type       comment
17/05/25 10:49:12 INFO mapred.FileInputFormat: Total input files to process : 1
host                    string                  from deserializer
remote_host             string                  from deserializer
remote_logname          string                  from deserializer
remote_user             string                  from deserializer
request_time            string                  from deserializer
request_method          string                  from deserializer
request_url             string                  from deserializer
first_line              string                  from deserializer
http_status             string                  from deserializer
bytes                   string                  from deserializer
referer                 string                  from deserializer
agent                   string                  from deserializer
Time taken: 0.049 seconds, Fetched: 12 row(s)

serializationLib:org.apache.hadoop.hive.contrib.serde2.RegexSerDe, parameters:{output.format.string=%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s %10$s %11$s %12$s, serialization.format=1, input.regex=([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (-|\[[^\]]*\]) "(.[A-Z]*) (.*) (.*)" (-|[0-9]*) (-|[0-9]*) "(.*)" "(.*)"})

hive> alter table log23 add columns (code string);

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 
Error: type expected at the position 0 of derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:derived from deserializer:<derived from deserializer:derived from deserializer:string but>'<' is found.`

I get an error like above failed. How do I add a column...?

Upvotes: 2

Views: 3079

Answers (3)

Zach
Zach

Reputation: 958

Unfortunately you cannot add columns if you've used serde. It is a known issue: https://issues.apache.org/jira/browse/HIVE-17713

Upvotes: 5

raghava
raghava

Reputation: 71

i tried the same but i am able to create a table and added the columns at the end:

create table log23 (host String, remote_host String); alter table log23 add columns(code String);

which is working with textfile format. please let me know if you are using different file format so that i try to replicate the use.

Upvotes: 0

Manish Saraf Bhardwaj
Manish Saraf Bhardwaj

Reputation: 1058

ADD COLUMNS lets you add new columns to the end of the existing columns but before the partition columns. This is supported for Avro backed tables as well, for Hive 0.14 and later.

REPLACE COLUMNS removes all existing columns and adds the new set of columns. This can be done only for tables with a native SerDe (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe). Refer to Hive SerDe for more information. REPLACE COLUMNS can also be used to drop columns. For example, "ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" will remove column 'c' from test_change's schema.

Upvotes: 0

Related Questions