Databases Reference
In-Depth Information
[COMMENT column_comment]
[FIRST|AFTER column_name]
The argument of the
ALTER TABLE
for a column change needs to appear in the exact order as
shown. The arguments in square brackets (
[]
) are optional but everything else needs to be included
in the correct sequence for the command to work. As a side effect, this means you need to state the
same column name in succession twice when you don't intend to rename it but intend to change
only its properties. Look at the
author
column in the previous example to see how this impacts
the command. Hive supports primitive and complex data types. Complex types can be modeled in
Hive using maps, arrays, or a struct. In the example just illustrated, the column is modifi ed to hold
an
ARRAY
of values. The
ARRAY
needs an additional type defi nition for its elements. Elements of an
ARRAY
type cannot contain data of two different types. In the case of the
author
column, the
ARRAY
contains only
STRING
type.
Next, you may want to store information on publications like short stories, magazines, and others
in addition to books and so you may consider renaming the table to
published_contents
instead.
You could do that as follows:
ALTER TABLE books RENAME TO published_contents;
Available for
download on
Wrox.com
hive_examples.txt
Running
DESCRIBE TABLE
on
published_contents
produces the following output:
hive> DESCRIBE published_contents;
OK
isbn int
title string
author array<string> multi-valued
category string
Time taken: 0.136 seconds
Available for
download on
Wrox.com
hive_examples.txt
Obviously, running
DESCRIBE
TABLE
on
books
now returns an error:
hive> DESCRIBE books;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
Available for
download on
Wrox.com
hive_examples.txt
Next, I walk through a more complete example to illustrate Hive's querying capabilities. Because
the
published_contents
and
users
tables may not be needed in the rest of this chapter, I could
drop those tables as follows:
DROP TABLE published_contents;
DROP TABLE users;
