学习大数据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 脚本洗濯：   

1. sed 's/{//g' /root/student2.txt \
2. | sed 's/"//g' \
3. | sed 's/://g' \
4. | sed 's/name//g' \
5. | sed 's/id//g' \
6. | sed 's/age//g' \
7. | sed 's/score//g' \
8. | sed 's/语文//g' \
9. | sed 's/数学//g' \
10. | sed 's/英语//g' \
11. | sed 's/sex//g' \
12. | sed 's/|/,/g' \
13. | sed 's/}//g' \
14. | sed 's/ //g' \
15. | sed 's/class_//g' > /root/student2new.txt

复制代码

   hive 导入数据：   

1. create table if not exists students2
2. (
3. name varchar(5),
4. id bigint,
5. class_name varchar(10),
6. age int,
7. 语文 tinyint,
8. 数学 tinyint,
9. 英语 tinyint,
10. constraint score unique(语文,数学,英语),
11. sex varchar(1)
12. )
13. row format delimitedfields terminated by ','
14. lines terminated by '\n'
15. location '/emp/'

复制代码

   

    2.student2 指标盘算     2.1 统计各班 语文平均分,数学平均分,英语平均分 语文最高分,英语最高分,     数学最高分,   

1. select class_name,avg(语文) as 语文平均分,avg(数学) as 数学平均分,avg(英语)
2. as 英语平均分,
3. max(语文) as 语文最高分,max(数学) as 数学最高分,max(英语) as 英语最高分
4. from students2
5. group by class_name2.2 统计各班男生语文最高分,男生数学最高分,男生英语最高分, 女生语文最
6. 高分, 女生数学最高分, 女生英语最高分
7. select class_name,sex,
8. avg(语文) as 语文平均分,avg(数学) as 数学平均分,avg(英语) as 英语平均分,
9. max(语文) as 语文最高分,max(数学) as 数学最高分,max(英语) as 英语最高分
10. from students2
11. group by class_name,sex

复制代码

   

       2.2 统计各班男生语文最高分,男生数学最高分,男生英语最高分, 女生语文最        高分, 女生数学最高分, 女生英语最高分      

1. select class_name,sex,
2. avg(语文) as 语文平均分,avg(数学) as 数学平均分,avg(英语) as 英语平均分,
3. max(语文) as 语文最高分,max(数学) as 数学最高分,max(英语) as 英语最高分
4. from students2
5. group by class_name,sex

复制代码

  

      2.3 统计每人平均分,总分,三科中的最高分,最高分科目名称,在本班的总分排     名(有并列),判断是否在平均分线以上   

1. --存储总分平均分
2. drop table if EXISTS 总分平均分;create table if not exists 总分平均分
3. (
4. score float
5. );
6. insert into 总分平均分
7. select avg(语文+数学+英语) from
8. students2;
9. select name,class_name,(语文+数学+英语)/3 as 三科平均分,(语文+
10. 数学+英语) as 总分,
11. case when 语文>=数学 and 语文>=英语 then
12. '语文'
13. when 数学>=语文 and 数学>=英语 then
14. '数学'
15. when 英语>=数学 and 英语>=语文 then
16. '英语'
17. end as 三科最高分的科目,
18. case when 语文>=数学 and 语文>=英语 then 语文
19. when 数学>=语文 and 数学>=英语 then 数学
20. when 英语>=数学 and 英语>=语文 then 英语
21. end as 三科最高分,
22. rank()over(partition by class_name order by (语文+数学+英语)
23. desc ) as 总分在本班的排名,
24. case when (语文+数学+英语) >= (select score from 总分平均分) then
25. '是'
26. when (语文+数学+英语)<(select score from 总分平均分) then
27. '否'
28. end as 是否在平均分线以上
29. from students2
30. order by (语文+数学+英语) desc;2.4 统计每个学生总分并降序排序 ,显示本人总分和上一个学生分数的差值
31. select name,语文+数学+英语 as 总分,
32. case when (lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
33. desc)-(语文+数学+英语))<0 then
34. '无'
35. else lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
36. desc)-(语文+数学+英语)
37. end as 与上一个学生分数的差值
38. from students2
39. order by 语文+数学+英语 desc

复制代码

   

       2.4 统计每个学生总分并降序排序 ,表现本人总分和上一个学生分数的差值      

1. select name,语文+数学+英语 as 总分,
2. case when (lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
3. desc)-(语文+数学+英语))<0 then
4. '无'
5. else lag(语文+数学+英语,1,0)over(order by 语文+数学+英语
6. desc)-(语文+数学+英语)
7. end as 与上一个学生分数的差值
8. from students2
9. order by 语文+数学+英语 desc

复制代码

  

       才想起来hive不支持中文表明和列名，我坦白我是用达梦写的。因为我电脑内存只有8G不敢开hadoop，一开就很卡，内存占用高达99%。（下周我会换内存卡）
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。