Database Reference
In-Depth Information
SELECT year, COUNT(1)
WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9)
GROUP BY year;
There is a single source table (
records2
), but three tables to hold the results from three
different queries over the source.
CREATE TABLE...AS SELECT
It's often very convenient to store the output of a Hive query in a new table, perhaps be-
cause it is too large to be dumped to the console or because there are further processing
steps to carry out on the result.
The new table's column definitions are derived from the columns retrieved by the
SELECT
clause. In the following query, the
target
table has two columns named
col1
and
col2
whose types are the same as the ones in the
source
table:
CREATE TABLE target
AS
SELECT col1, col2
FROM source;
A CTAS operation is atomic, so if the
SELECT
query fails for some reason, the table is
not created.
Altering Tables
Because Hive uses the schema-on-read approach, it's flexible in permitting a table's defin-
ition to change after the table has been created. The general caveat, however, is that in
many cases, it is up to you to ensure that the data is changed to reflect the new structure.
You can rename a table using the
ALTER TABLE
statement:
ALTER TABLE source RENAME TO target;
In addition to updating the table metadata,
ALTER TABLE
moves the underlying table
directory so that it reflects the new name. In the current example,
/user/hive/warehouse/
source
is renamed to
/user/hive/warehouse/target
. (An external table's underlying direct-
ory is not moved; only the metadata is updated.)
Hive allows you to change the definition for columns, add new columns, or even replace
all existing columns in a table with a new set.
For example, consider adding a new column:
ALTER TABLE target ADD COLUMNS (col3 STRING);
