学习大数据DAY39 基于 hive 的 SQL语句
目录hive 介绍以及应用
上机训练
hive 介绍以及应用
--查看数据库 show database db_hive --过滤查看数据库 show databases like 'db_hive*'; --查看详情 desc database db_hive desc database extended db_hive; --查看表 show tables; --查看表列详情 desc dept; --查看表所有具体信息 desc extended emp; show formatted emp; --查看分区信息 show partitions emp; -- 创建数据库 CREATE DATABASE database_name CREATE TABLE table_name --内部表 [(col_name data_type , ...)] --数据类型 , ...)] -- 分区表 , ...)] INTO num_buckets BUCKETS] -- 分桶表 --列分隔 --复合数据 item 分隔 --复合数据 key 分隔 ] --行分隔 --压缩格式 --表数据文件存储路径 --内外部表转换 --load load data inpath '数据的 path' into table student ; --上传 hdfs dfs -put /opt/module/hive/datas/student.txt /user/atguigu/hive; --插入数据 insert into table student_par values(1,'wangwu'),(2,'zhaoliu'); --覆盖插入并且使用效果集进行插入 insert overwrite table student_par select id, name from student ; insert overwrite local directory '数据的 path' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' dql_command; --hadoop 导出 dfs -get /user/hive/warehouse/student/student.txt /opt/module/datas/export/student3.txt; --hive shell 导出 bin/hive -e 'select * from default.student;' > /opt/module/hive/datas/export/student4.txt; --export 导出 export table default.student to '/user/hive/warehouse/export/student'; SELECT select_expr, select_expr, ... FROM table_reference ] 上机训练
1 student2 建表 1.1 数据洗濯1.2 数据导入 student2.txt 内容 https://i-blog.csdnimg.cn/direct/83599844fa5244df9c5634ad6aa00392.png Shell 脚本洗濯： sed 's/{//g' /root/student2.txt \
| sed 's/"//g' \
| sed 's/://g' \
| sed 's/name//g' \
| sed 's/id//g' \
| sed 's/age//g' \
| sed 's/score//g' \
| sed 's/语文//g' \
| sed 's/数学//g' \
| sed 's/英语//g' \
| sed 's/sex//g' \
| sed 's/|/,/g' \
| sed 's/}//g' \
| sed 's/ //g' \
| sed 's/class_//g' > /root/student2new.txt hive 导入数据： create table if not exists students2
(
name varchar(5),
id bigint,
class_name varchar(10),
age int,
语文 tinyint,
数学 tinyint,
英语 tinyint,
constraint score unique(语文,数学,英语),
sex varchar(1)
)
row format delimitedfields terminated by ','
lines terminated by '\n'
location '/emp/' https://i-blog.csdnimg.cn/direct/90aac9e402864aa2ac0b5a5886b13b41.png 2.student2 指标盘算 2.1 统计各班 语文平均分,数学平均分,英语平均分 语文最高分,英语最高分, 数学最高分, select class_name,avg(语文) as 语文平均分,avg(数学) as 数学平均分,avg(英语)
as 英语平均分,
max(语文) as 语文最高分,max(数学) as 数学最高分,max(英语) as 英语最高分
from students2
group by class_name2.2 统计各班男生语文最高分,男生数学最高分,男生英语最高分, 女生语文最
高分, 女生数学最高分, 女生英语最高分
select class_name,sex,
avg(语文) as 语文平均分,avg(数学) as 数学平均分,avg(英语) as 英语平均分,
max(语文) as 语文最高分,max(数学) as 数学最高分,max(英语) as 英语最高分
from students2
group by class_name,sex https://i-blog.csdnimg.cn/direct/bec18347e0c143f29d33414f4f890ef8.png 2.2 统计各班男生语文最高分,男生数学最高分,男生英语最高分, 女生语文最 高分, 女生数学最高分, 女生英语最高分 select class_name,sex,
avg(语文) as 语文平均分,avg(数学) as 数学平均分,avg(英语) as 英语平均分,
max(语文) as 语文最高分,max(数学) as 数学最高分,max(英语) as 英语最高分
from students2
group by class_name,sex https://i-blog.csdnimg.cn/direct/d8f976e71d5a4331ad45c03454d42c1d.png
2.3 统计每人平均分,总分,三科中的最高分,最高分科目名称,在本班的总分排 名(有并列),判断是否在平均分线以上 --存储总分平均分
drop table if EXISTS 总分平均分;create table if not exists 总分平均分
(
score float
);
insert into 总分平均分
select avg(语文+数学+英语) from
students2;
select name,class_name,(语文+数学+英语)/3 as 三科平均分,(语文+
数学+英语) as 总分,
case when 语文>=数学 and 语文>=英语 then
'语文'
when 数学>=语文 and 数学>=英语 then
'数学'
when 英语>=数学 and 英语>=语文 then
'英语'
end as 三科最高分的科目,
case when 语文>=数学 and 语文>=英语 then 语文
when 数学>=语文 and 数学>=英语 then 数学
when 英语>=数学 and 英语>=语文 then 英语
end as 三科最高分,
rank()over(partition by class_name order by (语文+数学+英语)
desc ) as 总分在本班的排名,
case when (语文+数学+英语) >= (select score from 总分平均分) then
'是'
when (语文+数学+英语)<(select score from 总分平均分) then
'否'
end as 是否在平均分线以上
from students2
order by (语文+数学+英语) desc;2.4 统计每个学生总分并降序排序 ,显示本人总分和上一个学生分数的差值
select name,语文+数学+英语 as 总分,
case when (lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
desc)-(语文+数学+英语))<0 then
'无'
else lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
desc)-(语文+数学+英语)
end as 与上一个学生分数的差值
from students2
order by 语文+数学+英语 desc https://i-blog.csdnimg.cn/direct/0fe48d625ad545eea2a837a1c9a0c372.png 2.4 统计每个学生总分并降序排序 ,表现本人总分和上一个学生分数的差值 select name,语文+数学+英语 as 总分,
case when (lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
desc)-(语文+数学+英语))<0 then
'无'
else lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
desc)-(语文+数学+英语)
end as 与上一个学生分数的差值
from students2
order by 语文+数学+英语 desc https://i-blog.csdnimg.cn/direct/64a4a889a08546b5befe1cf4d641d7ce.png
才想起来hive不支持中文表明和列名，我坦白我是用达梦写的。因为我电脑内存只有8G不敢开hadoop，一开就很卡，内存占用高达99%。（下周我会换内存卡）
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]