Basic hive operations

1. DDL operations

1. Create a table

Create internal table

create table if not exists mytable(sid int, sname string )row format delimited fields terminated by  ‘,‘  stored as textfile;

Create internal table

create external table if not exists pageview(pageid int, page_url string )
row format delimited  fields terminated by ‘,‘
location ‘hdfs://192.168.184.131:9000/user/hive/warehouse/‘;

Before adding data to a partitioned table, you must store the table separately.

1 create table if exists invites (id int , name string  )2 partitioned by (ds string)3 row format delimited fields terminated by ‘,‘  lines terminated by ‘\n‘ stored as textfile;

When you create a bucket-based table, the table is integrated according to the attribute consistency after the data is input.

1 // enable binning. Set hive. Enforce. bucketing = true;

Hive can be further organized into buckets for each table or partition, that is, the bucket is a more fine-grained data range division. Hive is used to split a column into buckets. Hive uses the hash of column values, and then the remainder of the number of buckets determines the bucket in which the record is stored. The benefit of Bucket sharding is higher query processing efficiency. Make sampling more efficient.

When querying from the bucket table, hive calculates and analyzes the data stored in the bucket based on the bucket field, and then directly retrieves data from the corresponding bucket, this improves the efficiency.

1 create table student (id int , name string )
2 clustered by (id) sorted by(name) into 4 buckets
3 row format delimited fields terminated by ‘,‘;

 

2. modify a table

Add Partition

1 alter table student_p add partition(part=‘a‘) partition (part=‘b‘);

Delete Partition

alter table student_p drop partition(part=‘a‘);

Rename a table

alter table table_name rename to new_table_name;

Add Column

alter table student add columns (sex  string);

Update Columns

 

Basic hive operations