1.beeline是客户端和hiveserver2一起使用
2.hive的常用交互命令
(1)“-e”不进入hive的交互窗口执行sql语句
[atguigu@hadoop102 hive]$ bin/hive -e "select id from student;"
(2)“-f”执行脚本中sql语句
[atguigu@hadoop102 datas]$ touch hivef.sql
文件中写入正确的sql语句
select *from student;
执行文件中的sql语句
[atguigu@hadoop102 hive]$ bin/hive -f /opt/module/datas/hivef.sql
执行文件中的sql语句并将结果写入文件中
[atguigu@hadoop102 hive]$ bin/hive -f /opt/module/datas/hivef.sql > /opt/module/datas/hive_result.txt
3.参数配置方式
参数的配置三种方式
方式一:配置文件方式
默认配置文件:hive-default.xml
方式二:命令行参数方式
启动Hive时,可以在命令行添加-hiveconf param=value来设定参数。
例如:
[atguigu@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;
注意:仅对本次hive启动有效
查看参数设置:
hive (default)> set mapred.reduce.tasks;
方式三:参数声明方式
可以在HQL中使用SET关键字设定参数
例如:
hive (default)> set mapred.reduce.tasks=100;
注意:仅对本次hive启动有效。
查看参数设置
hive (default)> set mapred.reduce.tasks;
总结:
上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数,例如log4j相关的设定,必须用前两种方式设定,因为那些参数的读取在会话建立以前已经完成了。
4.DDL数据定义
创建数据库
查询数据库
修改数据库
删除数据库
创建表
分区表
修改表
删除表
5.DML数据操作
数据导入
数据导出
清楚表中数据
案例一:
1) 假设某表有如下一行,我们用JSON格式来表示其数据结构。在Hive下访问的格式为
{
"name": "songsong",
"friends": ["bingbing" , "lili"] , //列表Array,
"children": { //键值Map,
"xiao song": 18 ,
"xiaoxiao song": 19
}
"address": { //结构Struct,
"street": "hui long guan" ,
"city": "beijing"
}
}
2)基于上述数据结构,我们在Hive里创建对应的表,并导入数据。
创建本地测试文件test.txt
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
注意:MAP,STRUCT和ARRAY里的元素间关系都可以用同一个字符表示,这里用“_”。
3)Hive上创建测试表test
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
字段解释:
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP中的key与value的分隔符
lines terminated by '\n'; -- 行分隔符
4)导入文本数据到测试表
hive (default)> load data local inpath ‘/opt/module/datas/test.txt’into table test
5)访问三种集合列里的数据,以下分别是ARRAY,MAP,STRUCT的访问方式
hive (default)> select friends[1],children['xiao song'],address.city from test
where name="songsong";
OK
_c0 _c1 city
lili 18 beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)
案列二:
查询常用
1.select ename AS name, deptno dn from emp;
2.查询出所有员工的薪水后加1显示。
hive (default)> select sal +1 from emp;
3.常用函数
1.求总行数(count)
hive (default)> select count(*) cnt from emp;
2.求工资的最大值(max)
hive (default)> select max(sal) max_sal from emp;
3.求工资的最小值(min)
hive (default)> select min(sal) min_sal from emp;
4.求工资的总和(sum)
hive (default)> select sum(sal) sum_sal from emp;
5.求工资的平均值(avg)
hive (default)> select avg(sal) avg_sal from emp;
4.典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from emp limit 5;
5.where
1.使用WHERE子句,将不满足条件的行过滤掉
2.WHERE子句紧随FROM子句
查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
6.比较运算符(Between/In/ Is Null)
案例实操
(1)查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
(2)查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
(3)查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
(4)查询工资是1500或5000的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
7.Like和RLike
(1)查找以2开头薪水的员工信息
hive (default)> select * from emp where sal LIKE '2%';
(2)查找第二个数值为2的薪水的员工信息
hive (default)> select * from emp where sal LIKE '_2%';
(3)查找薪水中含有2的员工信息
hive (default)> select * from emp where sal RLIKE '[2]';
8.逻辑运算符(And/Or/Not)
案例实操
(1)查询薪水大于1000,部门是30
hive (default)> select * from emp where sal>1000 and deptno=30;
(2)查询薪水大于1000,或者部门是30
hive (default)> select * from emp where sal>1000 or deptno=30;
(3)查询除了20部门和30部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);
9.Group By语句
GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。
案例实操:
(1)计算emp表每个部门的平均工资
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
(2)计算emp每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by
t.deptno, t.job;
10.Having语句
1.having与where不同点
(1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。
(2)where后面不能写分组函数,而having后面可以使用分组函数。
(3)having只用于group by分组统计语句。
2.案例实操
(1)求每个部门的平均薪水大于2000的部门
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
avg_sal > 2000;
11.Join语句