![6497281bb28b4014e5acee95d4ae45c2.png](https://img-blog.csdnimg.cn/img_convert/6497281bb28b4014e5acee95d4ae45c2.png)
1、如何在虚拟机centou os上进入hive的编辑页面,并对其进行设置?
![9eff8c8abd892dc6c204f16880bf28e5.png](https://img-blog.csdnimg.cn/img_convert/9eff8c8abd892dc6c204f16880bf28e5.png)
![998bb63c2e0c567afcef6b037aa1ef78.png](https://img-blog.csdnimg.cn/img_convert/998bb63c2e0c567afcef6b037aa1ef78.png)
![1987d386b9ee38138c04a8d4e69a5304.png](https://img-blog.csdnimg.cn/img_convert/1987d386b9ee38138c04a8d4e69a5304.png)
![50c657e30b833c65b57f08d428f47dc6.png](https://img-blog.csdnimg.cn/img_convert/50c657e30b833c65b57f08d428f47dc6.png)
#
2、hive建表
1)在创建表之前首先另起窗口建立student.txt
![863df0f0824f9f706cce626674137d21.png](https://img-blog.csdnimg.cn/img_convert/863df0f0824f9f706cce626674137d21.png)
vi students.txt进入后将数据录入,insert录入建,esc退出建,:wq保存并退出。
![f4bede7dda076da638a4073a868acc0e.png](https://img-blog.csdnimg.cn/img_convert/f4bede7dda076da638a4073a868acc0e.png)
2)创建表
#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;
3)创建表的几种方式
#语法
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]
创建表的8种方式
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;
4)查看表
(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;
Hive学习之路 (六)Hive SQL之数据类型和存储格式
Hive学习之路 (七)Hive的DDL操作