hive表的操作

hive表的创建：

	CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
	 [(col_name data_type [COMMENT col_comment], ...)]
	 [COMMENT table_comment]
	 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
	 [CLUSTERED BY (col_name, col_name, ...)
	 [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
	 [ROW FORMAT row_format]
	 [STORED AS file_format]
	 [LOCATION hdfs_path]

建表语句解析：

1）EXTERNAL 创建外部表需要指定的关键字，不指定则默认创建的是内部表
2）if not exits 建表的时候指定，避免报错
  if exits 删除表的时候指定，避免报错
3）comment 添加描述信息
4）PARTITIONED BY:指定分区字段（分区字段一定不能在表的原始建表字段中）
5)[CLUSTERED BY(col_name,......) 指定分桶字段，分桶字段一定在建表字段中的某一个或几个[SORTED BY (col_name [ASC|DESC], ....)]
每一个桶中的排序规则 INTO num_buckets BUCKETS]指定分桶的个数
6）ROW FORMAT row_format 指定分割符
7）STORED AS file_format 指定hive表中的原始数据的存储格式
8）LOCATION hdfs_path 指定hive表中的数据的存储目录

建表语句实例：
1）创建一个内部表student_managed

create table if not exists student_managed(id int,name string,sex string,age int,department string) row 
format delimited fields terminated by ',' location '/user/mytable/stu';

2）创建一个外部表student_external

create external table student_external(id int,name string,sex string,age int,department string)
 row format delimited fields terminated by ',';

3)创建一个分区表

分区字段department
外部分区表student_ptn： 
create external table if not exists student_ptn(id int,name string,sex string,age int) partitioned
 by(department string) row format delimited fields terminated by ',';

4）创建一个分桶表-- 按照age asc，指定分桶字段：id

create external table if not exists student_buk(id int,name string,sex string,age int,department string)
 clustered by (id) sorted by (age asc) into 3 buckets row format delimited fields terminated by ',';

5）进行表复制

create table if not exists student_copy like student_external;

6）ctas进行建表

create table .....  as   select ....   from .....where .....

表的修改操作：
修改表名：

		alter table student_copy rename to stu_copy;

修改表的列信息：
1）增加列

		alter table stu_copy add columns (salary double);

2)替换列

		将所有的列替换为新给的列
		alter table stu_copy replace columns(test01 int,test02 string);

3）修改列
修改列名

		alter table stu_copy change test01 aa01 int;

修改列的类型

		alter table stu_copy change aa01 aa01 string;
		alter table stu_copy change aa01 aa01 int;
		删除列：不支持

4）修改表的分区信息：针对分区表的
1）增加/添加表的分区

		add partition(分区信息)
		alter table student_ptn add partition(department="CS");
		一次性添加多个分区
		alter table student_ptn add partition(department="CS") partition(department="") partition(department="");

2)修改分区

			1）添加分区的时候使用location关键字进行指定
			alter table student_ptn add partition(department="test") location '/user/hive/hivehome/test_ptn';
			2)针对已经添加的分区   可以手动修改存储目录 
			alter table student_ptn partition(department="test") set location '/user/hive/hivehome/test_ptn';

3)删除分区 ***

			alter table student_ptn drop partition(department="test");

查看表列表信息

	show tables;
	show tables in db;
	show tables like '';
	show partitions table_name;

查看表的建表语句：

	show create table tablename;

最全的建表语句：

	CREATE EXTERNAL TABLE `student_ptn`(
	  `id` int, 
	  `name` string, 
	  `sex` string, 
	  `age` int)
	PARTITIONED BY ( 
	  `department` string)
	ROW FORMAT SERDE 
	  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
	WITH SERDEPROPERTIES ( 
	  'field.delim'=',', 
	  'serialization.format'=',') 
	STORED AS INPUTFORMAT 
	  'org.apache.hadoop.mapred.TextInputFormat' 
	OUTPUTFORMAT 
	  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
	LOCATION
	  'hdfs://hadoop01:9000/user/hive/hivehome/bd1807.db/student_ptn'
	TBLPROPERTIES (
	  'transient_lastDdlTime'='1539840706')

查看表的信息：

	desc tablename;
	desc formatted tablename;

删除表：

	drop table tablename;

清空表：

	truncate table table_name;

查看表分区：

	show partitions student_ptn partition(department="IS");