Hive 基操

Hive启动方式

1、Xshell直接输入:hive

[root@guo146 ~]# hive

2、beeline

//启动hiveserver2服务
[root@guo146 ~]# hive --service hiveserver2


//效果同上，错误输出将会和标准输出  输出到黑洞
[root@guo146 ~]# nohup hiveserver2 1>/dev/null 2>&1 &



[root@guo146 ~]# nohup hive --service metastore &

[root@guo146 ~]# nohup hive --service hiveserver2 &

//输入beeline进入beeline交互模式
[root@guo146 ~]# beeline -u jdbc:hive2://guo146:10000

 在hdfs上给文件执行，读写权限

[root@guo146 ~]# hdfs dfs -chmod -R 777 /tmp

 常用命令

0: jdbc:hive2://guo146:10000> show databases;

//查看当前所在的数据库
0: jdbc:hive2://guo146:10000> select current_database();

//查看数据库详细信息
0: jdbc:hive2://guo146:10000> describe database kb21;

//创建数据库
0: jdbc:hive2://guo146:10000> create database if not exists guo;

//查看当前数据库所有表
0: jdbc:hive2://guo146:10000> show tables;

//创建表
0: jdbc:hive2://guo146:10000> create table if not exists nj(id int,name string);

//查看表信息
0: jdbc:hive2://guo146:10000> describe nj;

//查看建表语句
0: jdbc:hive2://guo146:10000> show create table nj;

//删除表
0: jdbc:hive2://guo146:10000> drop table if exists nj;

//删除数据库
0: jdbc:hive2://guo146:10000> drop database if exists guo;

//强制删除数据库
0: jdbc:hive2://guo146:10000> drop database if exists guo cascade;

Hive集合类型

建表

静态表

//建表语句
create table student(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';


//加载本地数据
load data local inpath '/opt/stufile/student2.txt' into table student;
//加载HDFS文件,原文件将移动到student下
load data inpath '/student.txt' into table student;
//加载数据后覆盖原数据
load data inpath '/student.txt' overwrite  into table student;

分区表

//建表语句
create table student2(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
partitioned by (age int)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

//分区表插入数据
load data local inpath '/opt/stufile/student2.txt' into table student2 partition(age=20);
load data local inpath '/opt/stufile/student2.txt' into table student2 partition(age=30);

//查看分区表信息
0: jdbc:hive2://guo146:10000> show partitions studen2;

多字段分区表

//建表语句
create table student3(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
partitioned by (age int, gender string)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';

//分区表插入数据
load data local inpath '/opt/stufile/student2.txt' into table student3 partition(age=20,gender='boy');
load data local inpath '/opt/stufile/student2.txt' into table student3 partition(age=20,gender='girl');

//查看动态分区信息
show partitions student3;

外部表

//创建外部表
create external table studentwb1(
    id int,
    name string,
    likes array<string>,
    address map<string,string>
)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
location '/tmp/hivedata/student';

//插入数据
[root@guo146 ~]# hdfs dfs -put /opt/stufile/student2.txt /tmp/hivedata/student/