Hadoop系列-Hive数据库常见SQL命令

前几天写的Hive的安装，这个是Hadoop的一种数据仓库，支持多种文件格式可以直接导入数据库的操作，十分方便。今天把刚学习的Hive的常用数据库SQL命令整理出来，大家共同学习，如果有什么问题大家在评论来问我。

一、数据库相关命令

--默认创建数据，路径默认放到/user/hive/warehouse中
create database if not exists myhive;
--指定目录创建数据库
create database myhive2 location '/myhive2';
--查看数据库信息
desc database myhive;
OK
myhive      hdfs://master:9000/user/hive/warehouse/myhive.db    root    USER    
Time taken: 0.026 seconds, Fetched: 1 row(s)
--删除数据库，加上cascade的话连表一起删除，不带会删除失败报错
drop database myhive cascade;

二、创建内部表

--建表，external是外部表，不带的是内部表，表示不对外开放的表也就是管理表，下面的是内部表
CREATE TABLE `T_TEST_HIVE` (
   `ID` BIGINT COMMENT 'PRIMARY KEY',
   `TEST_VARCHAR` VARCHAR(50) ,
   `TEST_BIGINT` BIGINT,
   `TEST_DATETIME` TIMESTAMP
);
--查看database下的所有表
show tables;
--查看表
desc T_TEST_HIVE;
--查看表详细信息
desc formatted T_TEST_HIVE;
--删除表
drop table T_TEST_HIVE;
--插入数据，实际下面的方式不推荐使用，因为每次操作都会创建一个文件，比如下面两个SQL实际上写入了两个文件
insert into T_TEST_HIVE values (1,'nnn',1,null);
insert into T_TEST_HIVE values (2,'nnn2',2,null);
--这个时候可以到http://localhost:50070/explorer.html#/查看文件
--通过haddop查看文件命令可以看到具体的文件内容，其中每条数据一个文件
hadoop fs -cat /user/hive/warehouse/myhive.db/t_test_hive/000000_0
hadoop fs -cat /user/hive/warehouse/myhive.db/t_test_hive/000000_0_copy_1
--通过load读取本地数据文件（linux文件）数据导入
CREATE TABLE test (
   id int,name string
) row format delimited fields terminated by '\t';
--编写测试数据文件，注意数据中间不是空格，而是type健，建议vi之后，手动敲一下
vi /usr/local/apache-hive-1.2.2-bin/test.txt
1   zhangsan
2   lisi
--编辑完成后，通过load命令导入
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test.txt' into table test;
--这种操作实际上是直接把这个txt直接copy到hdfs对应的表目录下了，可以在hdfs上看到这个txt文件

--通过load读取HDFS数据文件数据导入
CREATE TABLE test2 (
   id int,name string
) row format delimited fields terminated by '\t';
--编写测试数据文件，注意数据中间不是空格，而是type健，建议vi之后，手动敲一下
vi /usr/local/apache-hive-1.2.2-bin/test2.txt
3   zhao3
4   wu4
--通过命令上传到hdfs中
hadoop fs -mkdir -p /hivedatas
hadoop fs -put /usr/local/apache-hive-1.2.2-bin/test2.txt /hivedatas/
load data inpath '/hivedatas/test2.txt' into table test2;
--导入完成后，会发现/hivedatas下的文件被移动到了/user/hive/warehouse/myhive.db/test2下，也就是直接到了表目录下

三、创建外部表

--实际上是可以共享的，比较安全，另外一般指定存储位置
CREATE external TABLE test_external (
   id int,name string,birth string,sex string
) row format delimited fields terminated by '\t' location '/hive_table/test_external';
 
CREATE external TABLE test_external2 (
   id int,name string
) row format delimited fields terminated by '\t' location '/hive_table/test_external2';
--编辑好文件数据，通过load导入
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_external.txt' into table test_external;
--注意增加两个一个overwrite参数，表示会覆盖原有数据
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_external2.txt' overwrite into table test_external2;
select * from test_external;
--可以看到数据
OK
1   jj  dd  s
2   ee  aa  oo
3   sdsd    sd  asd
--可以退出hive查看数据文件
hadoop fs -cat /hive_table/test_external/test_external.txt
--可以看到有内容输出
1   jj  dd  s
2   ee  aa  oo
3   sdsd    sd  asd
--这个时候重新进入hive删除下表
drop table test_external;
--再次推出hive，查看文件
hadoop fs -cat /hive_table/test_external/test_external.txt
--可以看到有内容输出，说明表文件没有删除
1   jj  dd  s
2   ee  aa  oo
3   sdsd    sd  asd
--重新建立表
CREATE external TABLE test_external (
   id int,name string,birth string,sex string
) row format delimited fields terminated by '\t' location '/hive_table/test_external';
再次查看表数据
select * from test_external;
--可以看到数据，说明当drop表后，文件并没有删除，重新建立表指定同样的文件后，表数据就恢复了
OK
1   jj  dd  s
2   ee  aa  oo
3   sdsd    sd  asd

四、一级分区表
其实就是把不同的数据放在不同的目录下

--准备数据文件并保存
vi /usr/local/apache-hive-1.2.2-bin/test_partition.txt
1   jj  100
2   ee  99
3   sdsd    98
--进入HIVE，建表SQL，一级分区表，按照月分区
CREATE TABLE test_partition (
   id int,name string,score int
) partitioned by (month string) row format delimited fields terminated by '\t';
--导入数据到month 202203,202204分区
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_partition.txt' into table test_partition partition (month='202203');
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_partition.txt' into table test_partition partition (month='202204');
--通过SQL语句查看
select * from test_partition;
OK
1   jj  100 202203
2   ee  99  202203
3   sdsd    98  202203
1   jj  100 202204
2   ee  99  202204
3   sdsd    98  202204
Time taken: 0.118 seconds, Fetched: 6 row(s)
--单独查询一个分区
select * from test_partition where month = '202203';
OK
1   jj  100 202203
2   ee  99  202203
3   sdsd    98  202203
Time taken: 0.511 seconds, Fetched: 3 row(s)
--同时查询两个分区，但是这种涉及到复杂文件计算了，效率很低，不推荐这种查询
select * from test_partition where month = '202203' union all select * from test_partition where month = '202204';
OK
1   jj  100 202203
1   jj  100 202204
2   ee  99  202203
2   ee  99  202204
3   sdsd    98  202203
3   sdsd    98  202204
Time taken: 51.013 seconds, Fetched: 6 row(s)
--退出Hive后，通过cat命令看到hadoop存在了以下两个文件
hadoop fs -cat /user/hive/warehouse/myhive.db/test_partition/month=202203/test_partition.txt
--输出
1   jj  100
2   ee  99
3   sdsd    98
hadoop fs -cat /user/hive/warehouse/myhive.db/test_partition/month=202204/test_partition.txt
--输出
1   jj  100
2   ee  99
3   sdsd    98

五、多级分区表
就是通过多个字段设置分区

--进入HIVE，建表SQL，多级分区表，按照月分区
CREATE TABLE test_partition2 (
   id int,name string,score int
) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';
--导入数据到分区数据
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_partition.txt' into table test_partition2
partition (year='2022',month='03',day='11');
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_partition.txt' into table test_partition2
partition (year='2022',month='03',day='12');
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_partition.txt' into table test_partition2
partition (year='2022',month='04',day='11');
--查询SQL
select * from test_partition2;
OK
1   jj  100 2022    03  11
2   ee  99  2022    03  11
3   sdsd    98  2022    03  11
Time taken: 0.107 seconds, Fetched: 3 row(s)
--也可以指定分区查询
select * from test_partition2 where year = '2022';
OK
1   jj  100 2022    03  11
2   ee  99  2022    03  11
3   sdsd    98  2022    03  11
1   jj  100 2022    03  12
2   ee  99  2022    03  12
3   sdsd    98  2022    03  12
1   jj  100 2022    04  11
2   ee  99  2022    04  11
3   sdsd    98  2022    04  11
select * from test_partition2 where day = '11';
OK
1   jj  100 2022    03  11
2   ee  99  2022    03  11
3   sdsd    98  2022    03  11
1   jj  100 2022    04  11
2   ee  99  2022    04  11
3   sdsd    98  2022    04  11
--退出Hive后，通过cat命令看到hadoop存在了以下目录的文件
hadoop fs -cat /user/hive/warehouse/myhive.db/test_partition2/year=2022/month=03/day=11/test_partition.txt
1   jj  100
2   ee  99
3   sdsd    98
hadoop fs -cat /user/hive/warehouse/myhive.db/test_partition2/year=2022/month=03/day=12/test_partition.txt
1   jj  100
2   ee  99
3   sdsd    98
hadoop fs -cat /user/hive/warehouse/myhive.db/test_partition2/year=2022/month=04/day=11/test_partition.txt
1   jj  100
2   ee  99
3   sdsd    98

六、分区表的其他操作

--查看分区
show partitions test_partition;
--输出
OK
month=202203
month=202204
Time taken: 0.123 seconds, Fetched: 2 row(s)
--添加分区
alter table test_partition add partition(month='202205');
alter table test_partition add partition(month='202206') partition(month='202207');
--再次查看分区
show partitions test_partition;
OK
month=202203
month=202204
month=202205
month=202206
month=202207
Time taken: 0.103 seconds, Fetched: 5 row(s)
--删除分区
alter table test_partition drop partition(month='202207');
--再次查看分区
show partitions test_partition;
OK
month=202203
month=202204
month=202205
month=202206
Time taken: 0.124 seconds, Fetched: 4 row(s)
--查看多级分区
show partitions test_partition2;
OK
year=2022/month=03/day=11
year=2022/month=03/day=12
year=2022/month=04/day=11
Time taken: 0.115 seconds, Fetched: 3 row(s)
--添加多级分区
alter table test_partition2 add partition(year='2022',month='03',day='13');
--再次查看多级分区
show partitions test_partition2;
OK
year=2022/month=03/day=11
year=2022/month=03/day=12
year=2022/month=03/day=13
year=2022/month=04/day=11
Time taken: 0.109 seconds, Fetched: 4 row(s)

谢各位的阅读，谢谢您动动手指点赞，万分感谢各位。另外以下是我之前写过的文章，感兴趣的可以点进去继续阅读。

历史文章