Database Reference
In-Depth Information
The new column
col3
is added after the existing (nonpartition) columns. The datafiles
are not updated, so queries will return
null
for all values of
col3
(unless of course
there were extra fields already present in the files). Because Hive does not permit updat-
ing existing records, you will need to arrange for the underlying files to be updated by an-
other mechanism. For this reason, it is more common to create a new table that defines
new columns and populates them using a
SELECT
statement.
Changing a column's metadata, such as a column's name or data type, is more straightfor-
ward, assuming that the old data type can be interpreted as the new data type.
To learn more about how to alter a table's structure, including adding and dropping parti-
tions, changing and replacing columns, and changing table and SerDe properties, see the
Dropping Tables
The
DROP TABLE
statement deletes the data and metadata for a table. In the case of ex-
ternal tables, only the metadata is deleted; the data is left untouched.
If you want to delete all the data in a table but keep the table definition, use
TRUNCATE
TABLE
. For example:
TRUNCATE TABLE my_table;
This doesn't work for external tables; instead, use
dfs -rmr
(from the Hive shell) to re-
move the external table directory directly.
In a similar vein, if you want to create a new, empty table with the same schema as anoth-
er table, then use the
LIKE
keyword:
CREATE TABLE new_table LIKE existing_table;
