Hadoop系列-Hive數(shù)據(jù)庫常見SQL命令

前幾天寫的Hive的安裝辈讶，這個是Hadoop的一種數(shù)據(jù)倉庫，支持多種文件格式可以直接導入數(shù)據(jù)庫的操作，十分方便哈雏。今天把剛學習的Hive的常用數(shù)據(jù)庫SQL命令整理出來，大家共同學習，如果有什么問題大家在評論來問我裳瘪。

一土浸、數(shù)據(jù)庫相關命令

--默認創(chuàng)建數(shù)據(jù)，路徑默認放到/user/hive/warehouse中
create database if not exists myhive;
--指定目錄創(chuàng)建數(shù)據(jù)庫
create database myhive2 location '/myhive2';
--查看數(shù)據(jù)庫信息
desc database myhive;
OK
myhive      hdfs://master:9000/user/hive/warehouse/myhive.db    root    USER    
Time taken: 0.026 seconds, Fetched: 1 row(s)
--刪除數(shù)據(jù)庫彭羹，加上cascade的話連表一起刪除黄伊，不帶會刪除失敗報錯
drop database myhive cascade;

二、創(chuàng)建內部表

--建表派殷，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;
--插入數(shù)據(jù)拓轻，實際下面的方式不推薦使用，因為每次操作都會創(chuàng)建一個文件虱黄，比如下面兩個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查看文件命令可以看到具體的文件內容悦即，其中每條數(shù)據(jù)一個文件
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讀取本地數(shù)據(jù)文件（linux文件）數(shù)據(jù)導入
CREATE TABLE test (
   id int,name string
) row format delimited fields terminated by '\t';
--編寫測試數(shù)據(jù)文件，注意數(shù)據(jù)中間不是空格橱乱，而是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數(shù)據(jù)文件數(shù)據(jù)導入
CREATE TABLE test2 (
   id int,name string
) row format delimited fields terminated by '\t';
--編寫測試數(shù)據(jù)文件，注意數(shù)據(jù)中間不是空格危纫，而是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;
--導入完成后种蝶，會發(fā)現(xiàn)/hivedatas下的文件被移動到了/user/hive/warehouse/myhive.db/test2下契耿，也就是直接到了表目錄下

三、創(chuàng)建外部表

--實際上是可以共享的螃征，比較安全搪桂，另外一般指定存儲位置
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';
--編輯好文件數(shù)據(jù)，通過load導入
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_external.txt' into table test_external;
--注意增加兩個一個overwrite參數(shù)盯滚，表示會覆蓋原有數(shù)據(jù)
load data local inpath '/usr/local/apache-hive-1.2.2-bin/test_external2.txt' overwrite into table test_external2;
select * from test_external;
--可以看到數(shù)據(jù)
OK
1   jj  dd  s
2   ee  aa  oo
3   sdsd    sd  asd
--可以退出hive查看數(shù)據(jù)文件
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';
再次查看表數(shù)據(jù)
select * from test_external;
--可以看到數(shù)據(jù)魄藕，說明當drop表后内列，文件并沒有刪除，重新建立表指定同樣的文件后背率，表數(shù)據(jù)就恢復了
OK
1   jj  dd  s
2   ee  aa  oo
3   sdsd    sd  asd

四话瞧、一級分區(qū)表
其實就是把不同的數(shù)據(jù)放在不同的目錄下

--準備數(shù)據(jù)文件并保存
vi /usr/local/apache-hive-1.2.2-bin/test_partition.txt
1   jj  100
2   ee  99
3   sdsd    98
--進入HIVE嫩与，建表SQL，一級分區(qū)表移稳，按照月分區(qū)
CREATE TABLE test_partition (
   id int,name string,score int
) partitioned by (month string) row format delimited fields terminated by '\t';
--導入數(shù)據(jù)到month 202203,202204分區(qū)
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)
--單獨查詢一個分區(qū)
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)
--同時查詢兩個分區(qū)蕴纳，但是這種涉及到復雜文件計算了，效率很低个粱，不推薦這種查詢
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

五翻翩、多級分區(qū)表
就是通過多個字段設置分區(qū)

--進入HIVE都许，建表SQL，多級分區(qū)表嫂冻，按照月分區(qū)
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';
--導入數(shù)據(jù)到分區(qū)數(shù)據(jù)
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)
--也可以指定分區(qū)查詢
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

六、分區(qū)表的其他操作

--查看分區(qū)
show partitions test_partition;
--輸出
OK
month=202203
month=202204
Time taken: 0.123 seconds, Fetched: 2 row(s)
--添加分區(qū)
alter table test_partition add partition(month='202205');
alter table test_partition add partition(month='202206') partition(month='202207');
--再次查看分區(qū)
show partitions test_partition;
OK
month=202203
month=202204
month=202205
month=202206
month=202207
Time taken: 0.103 seconds, Fetched: 5 row(s)
--刪除分區(qū)
alter table test_partition drop partition(month='202207');
--再次查看分區(qū)
show partitions test_partition;
OK
month=202203
month=202204
month=202205
month=202206
Time taken: 0.124 seconds, Fetched: 4 row(s)
--查看多級分區(qū)
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)
--添加多級分區(qū)
alter table test_partition2 add partition(year='2022',month='03',day='13');
--再次查看多級分區(qū)
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)

謝各位的閱讀桨仿，謝謝您動動手指點贊睛低，萬分感謝各位。另外以下是我之前寫過的文章服傍，感興趣的可以點進去繼續(xù)閱讀钱雷。

歷史文章