Quicktostudy
11/18/2018   
Hive Tutorial
Home »  Hive » Hive Alter Table

Hive Alter Table


The ALTER command will only modify Hive metadata, NOT the data. Users should make sure the actual data conforms with the metadata definition manually.

Alter the table statements to rename the table in hive

hive> ALTER TABLE cte_employee RENAME TO c_employee;

Alter the table properties, such as comments in hive

hive> ALTER TABLE c_employee
> SET TBLPROPERTIES ('comment'='New name, comments');

Alter the table delimiter through SERDEPROPERTIES

hive> ALTER TABLE employee_internal SET
> SERDEPROPERTIES ('field.delim' = '$');

Alter the table file format in hive

hive> ALTER TABLE c_employee SET FILEFORMAT RCFILE;

Alter the table location, which must be a full URI of HDFS

hive> ALTER TABLE c_employee SET LOCATION '/user/quicktostudy/employee';

Alter the table enable/disable protection to NO_DROP, which prevents a table from being dropped, or OFFLINE, which prevents data (not metadata) in a table from being queried:

hive> ALTER TABLE c_employee ENABLE NO_DROP;
hive> ALTER TABLE c_employee DISABLE NO_DROP;
hive> ALTER TABLE c_employee ENABLE OFFLINE;
hive> ALTER TABLE c_employee DISABLE OFFLINE;

Alter the table concatenation to merge small files into larger files in hive

--Convert to the file format supported
hive> ALTER TABLE c_employee SET FILEFORMAT ORC;

--Concatenate files
hive> ALTER TABLE c_employee CONCATENATE;

--Convert to the regular file format
hive> ALTER TABLE c_employee SET FILEFORMAT TEXTFILE;

CONCATENATE

In Hive release 0.8.0, RCFile is added to support fast block-level merging of small RCFiles using the CONCATENATE command. In Hive release 0.14.0 ORC, the files that are added support fast stripe-level merging of small ORC files using the CONCATENATE command. Other file formats are not supported yet. In case of RCFiles, the merge happens at block level and ORC files merge at stripe level thereby avoiding the overhead of decompressing and decoding the data. MapReduce is triggered when performing concatenation.

Alter the columns data type in hive table

--Check column type before changes
hive> DESC employee_internal;

--Change column type and order
hive> ALTER TABLE employee_internal
> CHANGE name employee_name string AFTER sex_age;

--Verify the changes
hive> DESC employee_internal;

Alter the column type and order in hive table

hive> ALTER TABLE employee_internal CHANGE employee_name name string FIRST;

--Verify the changes
hive> DESC employee_internal;

Add/replace columns in hive table

--Add columns to the table
hive> ALTER TABLE c_employee ADD COLUMNS (work string);

--Verify the added columns
hive> DESC c_employee;

--Replace all columns
hive> ALTER TABLE c_employee REPLACE COLUMNS (name string);

--Verify the replaced all columns
hive> DESC c_employee;

Quick to study hive questions

How to add column in hive table ?
how to replace column in hive table?
how to alter the column type in hive table?
how to alter the column data type in hive table?
how to alter the table file format in hive?
how to alter the table properties in hive?
how to alter the table in hive?


Contact Us| About Us| Terms| Privacy Policies
Powered by Lorquins Technologies© 2017 QuickToStudy.com. All Rights Reserved