学习大数据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 [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

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 DELIMITED --数据格式

[FIELDS TERMINATED BY char] --列分隔

[COLLECTION ITEMS TERMINATED BY char] --复合数据 item 分隔

[MAP KEYS TERMINATED BY char] --复合数据 key 分隔

[LINES TERMINATED BY char]] --行分隔

[STORED AS file_format] --压缩格式

[LOCATION hdfs_path] --表数据文件存储路径

[TBLPROPERTIES (property_name=property_value, ...)] --内外部表转换

[AS select_statement]

--load

load data [local] inpath '数据的 path' [overwrite] into table student

[partition (partcol1=val1,…)];

--上传 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 [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list]

[ORDER BY col_list]

[CLUSTER BY col_list

| [DISTRIBUTE BY col_list] [SORT BY col_list]

]

[LIMIT number]

上机练习

1 student2 建表

1.1 数据清洗 1.2 数据导入

student2.txt 内容

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/'

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

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

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

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

才想起来hive不支持中文表明和列名，我坦白我是用达梦写的。因为我电脑内存只有8G不敢开hadoop，一开就很卡，内存占用高达99%。（下周我会换内存卡）