資料庫之Hive概論和架構和基本操作

[root@node1 onekey]# cd /export/server/spark-2.3.0-bin-hadoop2.7/bin
[root@node1 bin]# ./beeline
Beeline version 1.2.1.spark2 by Apache Hive
beeline> !connect jdbc:hive2://node1:10000
Connecting to jdbc:hive2://node1.itcast.cn:10000
Enter username for jdbc:hive2://node1.itcast.cn:10000: root
Enter password for jdbc:hive2://node1.itcast.cn:10000: 直接回車
2021-01-08 14:34:24 INFO  Utils:310 - Supplied authorities: node1.itcast.cn:10000
2021-01-08 14:34:24 INFO  Utils:397 - Resolved authority: node1.itcast.cn:10000
2021-01-08 14:34:24 INFO  HiveConnection:203 - Will try to open client transport with JDBC Uri: jdbc:hive2://node1.itcast.cn:10000
Connected to: Spark SQL (version 2.3.0)
Driver: Hive JDBC (version 1.2.1.spark2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://node1.itcast.cn:10000> 。

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] 
[LOCATION hdfs_path]

user mytest; #選擇資料庫
create table stu(id int, name string); # 建立表
# 向表中插入資料
insert into stu values(1, 'test1');
insert into stu values(2, 'test2');
 
select * from stu; #查詢資料

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]
 
說明:
  LOCAL 表示從本地檔案系統載入,否則是從HDFS載入

#建立表，同時指定檔案的分隔符
create table if not exists stu2(id int ,name string) 
row format delimited fields terminated by 't' ;
#向表載入資料
load data local inpath '/export/data/hivedatas/stu.txt' into table stu2;

#建立表，同時指定檔案的分隔符
create table if not exists stu3(id int ,name string) 
row format delimited fields terminated by 't' ;
#向表載入資料
hadoop fs -mkdir -p /hivedatas 
cd /export/data/hivedatas 
hadoop fs –put stu.txt /hivedatas/ 
load data inpath '/hivedatas/stu.txt' into table stu3; 

#建立學生表
create external table student (sid string,sname string,sbirth string , ss       ex string) row format delimited fields terminated by ‘t' location ‘/hive_table/student‘;
 
#建立老師表
create external table teacher (tid string,tname string) row format delimited fields terminated by 't' location ‘/hive_table/teacher‘;

#給學生表新增資料 
load data local inpath '/export/data/hivedatas/student.txt' into table student; 
 
#給老師表新增資料，並覆蓋已有資料 
load data local inpath '/export/data/hivedatas/teacher.txt' overwrite into table teacher;
 
 
#查詢資料
select * from student; 
select * from teacher;

create table score(sid string,cid string, sscore int) partitioned by (month string) row format delimited fields terminated by 't';

load data local inpath '/export/data/hivedatas/score.txt' into table score partition (month='202006');

create table score2(sid string,cid string, sscore int) partitioned by (year string,month string, day string) row format delimited fields terminated by 't'; 

load data local inpath '/export/data/hivedatas/score.txt' into table score2 partition(year='2020',month='06',day='01');

show partitions score;

alter table score add partition(month='202008'); alter table score add partition(month='202009') partition(month = '202010');

alter table score drop partition(month = '202010');

zhangsan beijing,shanghai,tianjin,hangzhouwangwu changchun,chengdu,wuhan,beijin

create external table hive_array(name string, work_locations array<string>) row format delimited fields terminated by 't' collection items terminated by ','; 

load data local inpath '/export/data/hivedatas/array_data.txt' overwrite into table hive_array;

-- 查詢所有資料 select * from hive_array; -- 查詢loction陣列中第一個元素 select name, work_locations[0] location from hive_array; -- 查詢location陣列中元素的個數 select name, size(work_locations) location from hive_array; -- 查詢location陣列中包含tianjin的資訊 select * from hive_array where array_contains(work_locations,'tianjin');