hive 创建表_hive基础建表（1）

#

#1.创建数据库
hive>create database myhive1;
#2.使用数据库
hive>use myhive1;
#3.查询现在使用的数据库
hive>select current_database;
#4.创建表结构
hive>create table student(id int, name string, sex string, age int, department string) 
    >row format delimited fields terminated by ",";
#5.向表中加载数据
hive>load data local inpath 'students.txt' into table student;
#6.查询表
hive>select * from student;
#7.查询表结构
hive> describe student;
#8.查询详细的表结构
hive>describe formatted student;
#9.cast建立子表
hive>create table student_ctas as select * from student where id <95012;
 

#语法
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.创建默认的内部表
create table student(id int, name string, sex string, age int,department string) row format delimited fields terminated by ",";
2.外部表 
create external table student_ext
(id int, name string, sex string, age int,department string) row format delimited fields terminated by "," location "/hive/student";
3.分区表
create external table student_ptn(id int, name string, sex string, age int,department string)
. . . . . . . . . . . . . . .> partitioned by (city string)
. . . . . . . . . . . . . . .> row format delimited fields terminated by ","
. . . . . . . . . . . . . . .> location "/hive/student_ptn";
#如果某张表是分区表。那么每个分区的定义，其实就表现为了这张表的数据存储目录下的一个子目录
如果是分区表。那么数据文件一定要存储在某个分区中，而不能直接存储在表中。
4.分桶表
0: jdbc:hive2://hadoop3:10000> create external table student_bck(id int, name string, sex string, age int,department string)
. . . . . . . . . . . . . . .> clustered by (id) sorted by (id asc, name desc) into 4 buckets
. . . . . . . . . . . . . . .> row format delimited fields terminated by ","
. . . . . . . . . . . . . . .> location "/hive/student_bck";
5.使用CTAS创建表
作用： 就是从一个查询SQL的结果来创建一个表进行存储
create table student_ctas as select * from student where id < 95012;
6.复制表结构
create table student_copy like student;

（1）查看表列表
查看当前使用的数据库中有哪些表：show tables;
查看非当前使用的数据库中有哪些表：show tables in myhive;
查看数据库中以xxx开头的表：show tables like 'student_c*'
（2）查看表的详细信息
查看表的信息: desc student;
查看表的详细信息（格式不友好）:desc extended student;
查看表的详细信息（格式友好）: desc formatted student;
查看分区信息:show partitions student_ptn;
（3）查看表的详细建表语句
show create table student_ptn;