Shulou(Shulou.com)06/01 Report--

This article mainly shows you "Hive how to modify the table", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Hive how to modify the table" this article.

Note: most table properties can be modified through the ALTER TABLE statement, which modifies the metadata but not the data itself

* rename the table

Eg: ALTER TABLE app RENAME TO user

* add, modify and delete table partitions

-ALTER TABLE tablename ADD PARTITION... Statement is used to add a new partition to a table (usually an external table)

Eg: ALTER TABLE app ADD IF NOT EXISTS

PARTITION (timetype=hour, clct_day='2018-07-26') LOCATION'/ data/test/app/hour/'2018-07-26''

PARTITION (timetype=hour, clct_day='2018-07-27') LOCATION'/ data/test/app/hour/'2018-07-27'

PARTITION (timetype=hour, clct_day='2018-07-28') LOCATION'/ data/test/app/hour/'2018-07-28'

...

-- move the location to modify the path of a partition

Eg: ALTER TABLE app PARTITION (timetype=hour, clct_day='2018-07-26')

SET LOCATION'/ home/data/app/hour/'2018-07-26'

This command does not transfer the data from the old route, nor does it delete the old data.

-- Delete partition

Eg: ALTER TABLE app DROP IF EXISTS PARTITION (timetype=hour, clct_day='2018-07-26')

Note: for administrative tables, even if the partition is added using the ALTER TABLE...ADD PARTITION statement, the data in the partition will be deleted along with the metadata information.

For external tables, the data in the partition will not be deleted

* modify column information

Rename a field and modify its location, type, or comment

Eg: ALTER TABLE app

CHANGE COLUMN hour time_h INT

COMMENT 'THE hours part of the timestamp'

AFTER uv

Note: even if the field name or field type has not changed, you need to fully specify the old field name and give the new field name and the new field type.

In this example, after we move the field to the uv field, if we want to move to the first location, we just need to replace the AFTER other_ column clause with the FIRST keyword.

As usual, this command only modifies the metadata information, such as moving fields, then the data should also match the new pattern

* add columns

We can add a new field before the partition field to the existing field.

Eg:ALTER TABLE app ADD COLUMNS (

Appversion STRING COMMENT 'Application version'

Nettype STRING COMMENT 'logining application with nettype')

* delete or replace columns

-- remove all previous fields and reassign new fields

Eg:ALTER TABLE app REPLACE COLUMNS (

Time int

Name string

Message string)

Parsing: this statement actually renames the previous hour field and removes the field pv,uv from the original table, adding the message field. Because it is an ALTER statement, only the metadata information of the table has changed.

* modify table properties

-- you can add additional table properties or modify existing table properties, but you cannot delete them

Eg:ALTER TABLE app SET TBLPROPERTIES (

'notes'='this column is always NULL')

* modify storage properties

There are several ALTER TABLE statements to modify the storage format and SerDe properties

Eg:ALTER TABLE app

PARTITION (timetype string,clct_day string)

SET FILEFORMAT SEQUENCEFILE

Parsing: the above statement changes the storage format of a partition to SEQUENCEFILE. If it is a partition table, you need to use the PARTITION clause.

The above is all the contents of the article "how to modify the Table in Hive". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.