007-hadoop Hive SQL Syntax 2-Modify table structure

First, the table

Change table name: ALTER TABLE table_name RENAME to New_table_name

to increase the metadata information for a table: ALTER TABLE table_name SET tblproperties table_properties table_properties::[property_name = Property_value ...]

Users can add metadata to the table using this command

Second, column

table to add a column: ALTER TABLE pokes ADD COLUMNS (New_col INT);

Add a column and add a column field Comment: ALTER TABLE invites Add COLUMNS (new_col2 INT COMMENT ' a COMMENT ');

Add/Update column: ALTER TABLE table_name add| REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

　　Add is a new field, and the field position is behind all columns (before the partition column)
Replace represents all the fields in the replacement table.

Delete column: Drop TABLE pokes;

Modify: ALTER TABLE table_name Change [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [first| After column_name]

Allows you to change column names, data types, comments, column locations, or any combination of them

Third, partition

Add Partition:

ALTER TABLE table_name ADD [IF not EXISTS] partition_spec [location ' Location1 '] partition_spec [location ' Location2 '] ...
Partition_spec:
: PARTITION (Partition_col = partition_col_value, Partition_col = Partiton_col_value, ...)

Delete partition: ALTER TABLE table_name DROP partition_spec, Partition_spec,...

Iv. changing table file format and organization

ALTER TABLE table_name SET fileformat File_format
ALTER TABLE table_name CLUSTERED by (userid) SORTED by (viewtime) to num_buckets buckets

This command modifies the physical storage properties of the table

V. Create/delete views

CREATE VIEW [IF not EXISTS] view_name [(column_name [COMMENT column_comment], ...)] [COMMENT View_comment] [Tblproperties (property_name = Property_value, ...)] As SELECT

• Add views
• If a table name is not provided, the name of the view column is automatically generated by the defined select expression
• If you modify the properties of the base table, the view does not show that the invalid query will fail
• View is read-only and cannot be used Load/insert/alter
Drop VIEW view_name
• Delete views

Vi. Creating a Database

　　CREATE DATABASE Name

Vii. Display of commands

Show tables;
show databases;
show partitions;
Show functions
Describe extended table_name dot col_name

007-hadoop Hive SQL Syntax 2-Modify table structure