ToB企服应用市场:ToB评测及商务社交产业平台

标题: 学习大数据DAY39 基于 hive 的 SQL语句 [打印本页]
作者: 慢吞云雾缓吐愁    时间: 2024-10-20 22:55
标题: 学习大数据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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4