Hive学习
Hive操作
Hive有两种客户端工具
Beeline、Hive命令行(CLI)
两种模式
命令行模式和交互模式
命令行启动
直接通过
hive
就可进入
启动Beeline
hive --service hiveserver2
等待服务启动
再启动一个xhell会话,输入
beeline -u jdbc:hive2://192.168.153.145:10000
即可进入
可能无法在创建的表内插入数据,会报错
显示tmp目录没有权限
给予tmp目录权限
hdfs dfs -chmod -R 777 /tmp
即可插入数据
静默开启Beeline
nohup hiveserver2 1>/dev/null 2>&1 &
1>/dev/null 把标准输出,输出到黑洞
2>1 标准错误输出也重定向到1,也输出到黑洞
Tips
hive模式只能在安装了hive的本地服务器使用,Beeline模式能够在别的服务器远程访问
安装网络工具,防止出错
yum -y install net-tools
使用命令查询10000端口占用情况
netstat -nltp | grep 10000
datagrip连接hive
新建
下载driver
即可连接成功
命令
查看表信息
describe 表名;
查看数据库详细信息
describe database default;
查看当前所在的数据库
select current_database();
建库建表
create database if not exists aabb;
create table if not exists aatb(id int, name string);
查看建表结构
show create table aatb;
删除表、库
drop table if exists aatb;
drop database if exists aabb;//当库不为空时会报错
drop database if exists bigdata cascade;//强制删库!!
表
静态表
加载本地数据
将已有文件插入表内
文件名student.txt放在/opt目录下
1,xiaoming1,lol-book-movie,beijing:bdqn-nanjing:zhongbo
2,xiaoming2,lol-book-movie,beijing:bdqn-nanjing:zhongbo
3,xiaoming3,lol-book-movie,beijing:bdqn-nanjing:zhongbo
4,xiaoming4,lol-book-movie,beijing:bdqn-nanjing:zhongbo
5,xiaoming5,lol-movie,beijing:bdqn-nanjing:zhongbo
6,xiaoming6,book-movie,beijing:bdqn-nanjing:zhongbo
7,xiaoming7,lol-book,beijing:bdqn-nanjing:zhongbo
8,xiaoming8,lol-book,beijing:bdqn-nanjing:zhongbo
9,xiaoming9,lol-book-movie,beijing:bdqn-nanjing:zhongbo
建立一张表
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/student.txt' into table student;
加载hdfs数据
将hdfs的文件加载到student中
load data inpath '/student.txt' into table student;
load data inpath '/student.txt' overwrite into table student;//覆写原表数据
此操作会将hdfs内的文件用掉,warehouse会生成响应文件
分区表
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/student.txt' into table student2 partition(age=20);
分区多一个age=20的目录
查看分区表中分区的内容
show partitions student2;
多分区
方便查找 where
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';
查看分区表中分区的内容
show partitions student3;
多分区,有几个分区,hdfs多几个子目录
外部表(常用,安全)
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';
将本地数据传进去
hdfs dfs -put ./student.txt /tmp/hivedata/student