hive 连接查询sql对比效率

最新推荐文章于 2024-11-26 11:59:09 发布
最新推荐文章于 2024-11-26 11:59:09 发布
阅读量633 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 大数据 数据库
原文链接：http://www.cnblogs.com/mrcharles/p/4731715.html
本文通过具体实例介绍如何使用Hive SQL进行表的创建、数据导入及各种查询操作，包括子查询、分组等高级功能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

准备4个表

从mysql 导出excel 转换为txt

创建hive 表的导入文件

create table bdqn_student(
sno int,
sname string,
sbirthdate string,
sgender string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

create table bdqn_teacher(
tno int,
tname string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

create table bdqn_course(
cno int,
cname string,
tno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

create table bdqn_score(
sno int,
cno int,
score string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;

Time taken: 4.246 seconds, Fetched: 1 row(s)
hive> create table bdqn_student(

sno int,
sname string,
sbirthdate string,
sgender string);
OK
Time taken: 0.583 seconds
hive> create table bdqn_teacher(
tno int,
tname string);
OK
Time taken: 0.106 seconds
hive> create table bdqn_course(
cno int,
cname string,
tno int);
OK
Time taken: 0.105 seconds
hive>
create table bdqn_score(
sno int,
cno int,
score string);
OK
Time taken: 0.094 seconds

Time taken: 0.094 seconds
hive> show tables;
OK
bdqn_course
bdqn_score
bdqn_student
bdqn_teacher
ncdc
Time taken: 0.021 seconds, Fetched: 5 row(s)

一共四个表

load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/course.txt’ into table bdqn_course
load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/student.txt’ into table bdqn_student
load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/teacher.txt’ into table bdqn_teacher
load data local inpath ‘/opt/hadoop/hadoopDATA/sql_Query_do_not_delete/score.txt’ into table bdqn_score

中文乱码问题解决：

解决方法：
1、修改远程linux机器的配置
[root@rhel ~]#vi /etc/sysconfig/i18n
把LANG改成支持UTF-8的字符集
如： LANG=”zh_CN.UTF-8″ 或者是 LANG=”en_US.UTF-8″ 本文修改为后者
2、修改Secure CRT的Session Options
Options->Session Options->Appearance->Font->新宋体 字符集：中文GB2312 ->Character encoding 为UTF-8
3、OK.

查询：
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（提示：子查询，分组）
select st.sname, ascore from bdqn_student st join
(select sno,avg(score) ascore from bdqn_score group by sno having avg(score)>=60) sc on sc.sno=st.sno

hive> select st.sname, ascore from bdqn_student st join

(select sno,avg(score) ascore from bdqn_score group by sno having avg(score)>=60) sc on sc.sno=st.sno;
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507050950_0007, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0007
Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0007
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2015-07-06 15:46:11,004 Stage-2 map = 0%, reduce = 0%
2015-07-06 15:46:15,029 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:16,034 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:17,040 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:18,046 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:19,051 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:20,057 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:21,063 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:22,068 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:23,074 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:24,079 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.86 sec
2015-07-06 15:46:25,090 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 5.08 sec
2015-07-06 15:46:26,096 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 5.08 sec
2015-07-06 15:46:27,102 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 5.08 sec
2015-07-06 15:46:28,108 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 5.08 sec
MapReduce Total cumulative CPU time: 5 seconds 80 msec
Ended Job = job_201507050950_0007
Launching Job 2 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507050950_0008, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0008
Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0008
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2015-07-06 15:46:35,818 Stage-1 map = 0%, reduce = 0%
2015-07-06 15:46:39,836 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 1.85 sec
2015-07-06 15:46:40,841 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 1.85 sec
2015-07-06 15:46:41,848 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.69 sec
2015-07-06 15:46:42,853 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.69 sec
2015-07-06 15:46:43,859 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.69 sec
2015-07-06 15:46:44,864 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.69 sec
2015-07-06 15:46:45,869 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.69 sec
2015-07-06 15:46:46,875 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.69 sec
2015-07-06 15:46:47,880 Stage-1 map = 100%, reduce = 33%, Cumulative CPU 3.69 sec
2015-07-06 15:46:48,888 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 6.73 sec
2015-07-06 15:46:49,894 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 6.73 sec
2015-07-06 15:46:50,900 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 6.73 sec
2015-07-06 15:46:51,906 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 6.73 sec
MapReduce Total cumulative CPU time: 6 seconds 730 msec
Ended Job = job_201507050950_0008
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 Cumulative CPU: 5.08 sec HDFS Read: 377 HDFS Write: 226 SUCCESS
Job 1: Map: 2 Reduce: 1 Cumulative CPU: 6.73 sec HDFS Read: 1109 HDFS Write: 73 SUCCESS
Total MapReduce CPU Time Spent: 11 seconds 810 msec
OK
赵雷 89.66666666666667
钱电 70.0
孙风 80.0
周梅 81.5
郑竹 93.5
Time taken: 51.375 seconds, Fetched: 5 row(s)

Hive只支持在FROM子句中使用子查询，子查询必须有名字，并且列必须唯一：SELECT … FROM(subquery) name …

这个如果要写成mapred的话，将会非常复杂，但是一个简单的子查询就搞定啦。也可以看到，其实这个查询是有两个job的。

3. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select st.sname, ascore ,sum from bdqn_student st join
(select sno,sum(score) ascore,count(*) sum from bdqn_score group by sno) sc on sc.sno=st.sno

hive> select st.sname, ascore ,sum from bdqn_student st join

(select sno,sum(score) ascore,count(*) sum from bdqn_score group by sno) sc on sc.sno=st.sno
;
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507050950_0009, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0009
Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0009
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
2015-07-06 16:00:40,162 Stage-2 map = 0%, reduce = 0%
2015-07-06 16:00:43,179 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:44,184 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:45,189 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:46,194 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:47,199 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:48,205 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:49,210 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:50,215 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 1.65 sec
2015-07-06 16:00:51,220 Stage-2 map = 100%, reduce = 33%, Cumulative CPU 1.65 sec
2015-07-06 16:00:52,225 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.57 sec
2015-07-06 16:00:53,231 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.57 sec
2015-07-06 16:00:54,236 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.57 sec
2015-07-06 16:00:55,242 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.57 sec
MapReduce Total cumulative CPU time: 4 seconds 570 msec
Ended Job = job_201507050950_0009
Launching Job 2 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507050950_0010, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0010
Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0010
Hadoop job information for Stage-1: number of mappers: 2; number of reducers: 1
2015-07-06 16:01:01,938 Stage-1 map = 0%, reduce = 0%
2015-07-06 16:01:04,952 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 1.27 sec
2015-07-06 16:01:05,957 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 1.27 sec
2015-07-06 16:01:06,962 Stage-1 map = 50%, reduce = 0%, Cumulative CPU 1.27 sec
2015-07-06 16:01:07,967 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:08,972 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:09,978 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:10,983 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:11,988 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:12,993 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:13,999 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.64 sec
2015-07-06 16:01:15,005 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 5.52 sec
2015-07-06 16:01:16,011 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 5.52 sec
2015-07-06 16:01:17,016 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 5.52 sec
MapReduce Total cumulative CPU time: 5 seconds 520 msec
Ended Job = job_201507050950_0010
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 Cumulative CPU: 4.57 sec HDFS Read: 377 HDFS Write: 285 SUCCESS
Job 1: Map: 2 Reduce: 1 Cumulative CPU: 5.52 sec HDFS Read: 1170 HDFS Write: 104 SUCCESS
Total MapReduce CPU Time Spent: 10 seconds 90 msec
OK
赵雷 269.0 3
钱电 210.0 3
孙风 240.0 3
李云 100.0 3
周梅 163.0 2
吴兰 65.0 2
郑竹 187.0 2
Time taken: 44.616 seconds, Fetched: 7 row(s)

8. 查询没有学全所有课程的同学的信息

select * from bdqn_student st join (
select sno, count() from bdqn_score group by sno having count()<>3) temp on temp.sno=st.sno

Time taken: 44.616 seconds, Fetched: 7 row(s)
hive>

select * from bdqn_student st join (
select sno, count() from bdqn_score group by sno having count()<>3) temp on temp.sno=st.sno;
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507050950_0011, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0011
Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0011
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2015-07-06 16:05:29,038 Stage-1 map = 0%, reduce = 0%
2015-07-06 16:05:32,051 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:33,057 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:34,062 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:35,067 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:36,072 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:37,077 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:38,082 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:39,088 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.21 sec
2015-07-06 16:05:40,093 Stage-1 map = 100%, reduce = 33%, Cumulative CPU 1.21 sec
2015-07-06 16:05:41,098 Stage-1 map = 100%, reduce = 33%, Cumulative CPU 1.21 sec
2015-07-06 16:05:42,103 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 4.63 sec
2015-07-06 16:05:43,109 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 4.63 sec
2015-07-06 16:05:44,115 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 4.63 sec
MapReduce Total cumulative CPU time: 4 seconds 630 msec
Ended Job = job_201507050950_0011
Launching Job 2 out of 2
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201507050950_0012, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201507050950_0012
Kill Command = /opt/hadoop/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201507050950_0012
Hadoop job information for Stage-2: number of mappers: 2; number of reducers: 1
2015-07-06 16:05:51,818 Stage-2 map = 0%, reduce = 0%
2015-07-06 16:05:54,833 Stage-2 map = 50%, reduce = 0%, Cumulative CPU 1.0 sec
2015-07-06 16:05:55,838 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:05:56,844 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:05:57,849 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:05:58,854 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:05:59,859 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:06:00,865 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:06:01,870 Stage-2 map = 100%, reduce = 0%, Cumulative CPU 2.06 sec
2015-07-06 16:06:02,875 Stage-2 map = 100%, reduce = 33%, Cumulative CPU 2.06 sec
2015-07-06 16:06:03,881 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.92 sec
2015-07-06 16:06:04,887 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.92 sec
2015-07-06 16:06:05,893 Stage-2 map = 100%, reduce = 100%, Cumulative CPU 4.92 sec
MapReduce Total cumulative CPU time: 4 seconds 920 msec
Ended Job = job_201507050950_0012
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 Cumulative CPU: 4.63 sec HDFS Read: 377 HDFS Write: 153 SUCCESS
Job 1: Map: 2 Reduce: 1 Cumulative CPU: 4.92 sec HDFS Read: 1038 HDFS Write: 79 SUCCESS
Total MapReduce CPU Time Spent: 9 seconds 550 msec
OK
5 周梅 1991/12/1 女 5 2
6 吴兰 1992/3/1 女 6 2
7 郑竹 1989/7/1 女 7 2
Time taken: 43.597 seconds, Fetched: 3 row(s)

版权声明：本文为博主原创文章，未经博主允许不得转载。

转载于:https://www.cnblogs.com/mrcharles/p/4731715.html

Flink SQL Hive Connector使用场景
数据与算法架构提升之路专栏
11-01 1804
本文基于FLink1.16讨论，Apache Flink Hive Connector提供了Flink与Hive的集成，主要用于在Flink中读取和写入Hive数据
HIVE SQL 代码优化总结(容易踩坑的地方)
lingan_Hong的博客
07-14 2264
hive的出现降低了Hadoop的使用门槛，但是同时由于代码的编写不规范导致出现数据倾斜、高IO、笛卡尔积、运行耗时长等问题，所以要对HQL进行优化。
hive原理及查询优化
09-30
学习掌握hive原理及查询优化的必备之书学习掌握hive原理及查询优化的必备之书
Hive查询效率详解
刘李404not_found的博客
08-27 2240
只查了十几条数据，用了16.497秒，效率还是很低。 之后又查询了几次，即使查询数据量达到一百万条，总用时也是在15秒左右徘徊 多次实验的结果，可见Hive查询的时间是由MapReduce决定的，而不是由查询的数据量影响的。 数据量 MapReduce耗时 总耗时 其他 10条 5.85 sec 13.756 sec 7.906 sec 10,000条 5.94 sec 1...
HiveSQL查询优化
www_sg_com的博客
07-10 648
1.优化之分区 对数据进行分区，可以将数据以一种符合逻辑的方式进行组织（比如分层存储），同时极大提高查询性能。 在创建表的时候，根据后续查询需求用'PARTITIOED BY( )'对数据进行合理的分区，下面我们根据‘province’和‘city’进行对数据进行分区分区：CREATE TABLE IF NOT EXICTS tbl_per_info (no INT, name STRING,
大数据开发之Hive如何提高查询效率
m0_58371965的博客
12-31 1732
今天分享一下Hive如何提升查询效率。Hive作为最大数据培训常用的数仓计算引擎，是我们必备的技能，但是很多人只是会写Hql，并不会优化，也不知道如何提升查询效率，今天分享8条军规： 1、开启FetchTask 一个简单的查询语句，是指一个没有函数、排序等功能的语句，当开启一个Fetch Task功能，就执行一个简单的查询语句不会生成MapRreduce作业，而是直接使用FetchTask，从hdfs文件系统中进行查询输出数据，从而提高效率。 设置的方式： Hive.fetch.task.c
[Hive]Hive中表连接的优化，加快查询速度
热门推荐
TOMACAT的博客
08-15 4万+
1、多表连接的执行顺序和MapReduce job优化 select a.ymd ,a.price_close ,b.price_close ,c.price_close from stocks a join stocks b on a.ymd = b.ymd join stocks c on a.ymd = c.ymd where ...
Hive SQL进阶：掌握这些SQL查询技巧，让你的数据报告更加精准和高效！
数据与算法架构提升之路专栏
08-05 1162
这份文档提供了一系列的SQL查询示例，用于解决数据分析中的常见问题，如用户活跃度、留存率、销售统计等。通过这些示例，用户可以学习如何使用SQL进行高效的数据查询和分析。
HiveSQL优化
AGI爱好者_数仓&可视化&数据分析工程师_Vae’伯乐‘
11-26 1967
将那些产生倾斜的key和对应v2的数据, 从当前这个MR中移出去, 单独找一个MR来处理即可, 处理后, 和之前的MR进行汇总结果即可。候, 将其提前配置设置好即可, 在后续运行的时候, 程序会自动将设置的key的数据单独找一个MR来进行处理即可, 处。当前这个k2的数据存在数据倾斜, 自动将其剔除, 交由给一个单独的MR来处理即可,两个MR处理完成后, 将结果基于。思路: 在执行MR的时候, 会动态统计每一个 k2的值出现重复的次数, 当这个重复的次数达到一定的阈值后, 认为。
浅谈Hive SQL的优化
Code_zhu的博客
02-17 6662
目前团队的数据处理都在Hadoop集群上， 一是因为需要处理的数据量都是亿级的，这种规模的数据适合用Hadoop集群并行处理； 二是免除了分库分表给查询处理上带来的麻烦。Hive是基于Hadoop的一个数据仓库工具，它将存储在HDFS上的结构化的文件映射成一张关系型数据库表，提供简单的SQL查询功能。本文结合Hive SQL的运行原理谈一谈Hive SQL的优化问题。 1. 数据过滤应尽早做，同时只选择所需要的列 这个原则在传统的RDMS数据中应该也适用，因为数据提前过滤了之后进行join或者其他操
Hive知识之连接查询
恋恋风尘的博客
04-09 620
前言：Hive的查询语句和SQL语句非常类似，如果有SQL基础，学习Hive的查询语句会比较容易。 可参考SQL的连接查询： https://blog.youkuaiyun.com/weixin_42384784/article/details/105333951 1、UNION ALL和UNION 联合的字段名称和字段顺序需要一致 没有连接条件，只是数据叠加 UNION后的表作为子查询时，需要进行重命名 ...
Hive数据压缩前后查询效率对比
Jibo12138的博客
10-19 751
场景：为了优化通过spark sql查询hive中数据的查询效率，在创建表时需要指定压缩方式，将数据压缩，以此提高查询效率。 第一步，查看先前数据占用空间情况： 先前为了比较hive与es的查询效率，因此在数据表中存了恒定的一亿条数据，先查询一下在不进行压缩的情况下数据占用的空间情况，在hadoop的bin目录下执行命令如下： ./hadoop fs -ls /hive-site配置文件下hive.metastore.warehouse.dir的路径|awk -F ' ' '{pri...
Hive基本概念
努力努力再努力L
09-16 323
Hive基本概念 目录 什么是Hive Hive的优缺点 Hive架构原理 Hive和数据库比较 1. 什么是Hive Hive：由 Facebook 开源用于解决海量结构化日志的数据统计框架。 Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供了类 SQL 查询功能。 本质是：将 HQL 转化成 MapReduce 程序。 Hive 处...
提升Hive效能：实用技巧与最佳实践
引用原创内容标明出处即可
12-21 1万+
我们深入探讨了Hive的多个关键方面，包括查询性能优化、表结构管理、HiveQL编写技巧，以及性能监控和调优。每个部分都提供了实用的技巧和建议，旨在帮助大家更有效地使用Hive来处理和分析大规模数据。
hive视图和mysql视图有什么区别,hive和mysql的区别是什么
weixin_39864601的博客
03-19 364
hive和mysql的区别是什么hive和mysql的区别有：1、查询语言不同：hive是hql语言，mysql是sql语句；2、数据存储位置不同：hive是把数据存储在hdfs上，而mysql数据是存储在自己的系统中；3、数据格式：hive数据格式可以用户自定义，mysql有自己的系统定义格式；4、数据更新：hive不支持数据更新，只可以读，不可以写，而sql支持数据更新；5、索引：hive没有...
Hive(十六)having和排序
技术博客
08-14 1491
有别于order by ，sort by可以指定reducer的个数，然后再对reducer里面的数据再排序，也可以得到全局的排序结果。distribute by 一般是配合sort by 使用的。利用distribute by对数据进行分类，然后再在每一个分类中对数据进行排序.如果distribute by和sort by的字段一致，那么可以写成cluster by。order by 是最常用的一种排序，全局排序，所有的数据会在一个reducer上面进行排序，所以一般使用这个函数进行排序的时候速度较慢。
总结：Hive
w2009211777的专栏
06-28 1721
交易ID：交易日期：客户IDproduct_id：产品IDamount：交易金额在处理TB级甚至更大规模数据时，Hive通过分布式存储和计算展示了其显著的优势。相较于传统的关系型数据库如MySQL，Hive能够更加高效地处理大规模数据查询，具有更高的扩展性和容错能力。
为什么要用Hive？
百老讲数据
08-08 793
1、搭建Hadoop平台+Hive，计算使用mapreduce2、多大数据可以称大数据？对于谁来说？本文对于MySQL来说，另外不从功能特性等方面比较，就单单比较数据查询时间。
hive mysql 效率 对比_hive 连接查询sql对比效率
weixin_42355421的博客
01-19 810
准备4个表从mysql 导出excel 转换为txt创建hive 表的导入文件create table bdqn_student(sno int,sname string,sbirthdate string,sgender string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ STORED AS TEXTFILE;create table b...
hivesql和 sql
最新发布
03-14
<think>好的，用户想比较HiveSQL与传统SQL的区别以及各自的用法。首先，我需要回忆HiveSQL和传统SQL的基本概念。HiveSQL是用于Hadoop生态系统的数据仓库工具，而传统SQL通常指关系型数据库如MySQL、PostgreSQL等使用的SQL。 接下来，用户提到的引用内容可能涉及兼容性、功能支持和扩展性。引用1提到Hive Metastore的兼容性和不同版本的支持，引用2提到Impala的SQL支持，包括UDF和UDA，引用3提到ACID特性。这些点可能在比较中用到。 首先，从架构设计开始。HiveSQL基于Hadoop，适合处理大数据，而传统SQL用于OLTP。然后，数据规模方面，Hive处理PB级数据，传统SQL处理GB到TB级。延迟方面，Hive可能有较高的延迟，传统SQL响应更快。 功能差异方面，HiveSQL支持UDF/UDAF，如引用2中的Impala也支持这些，但Hive可能更多依赖MapReduce或Tez。传统SQL的事务支持更完善，如引用3提到的ACID在Hive中可能有限，特别是旧版本。 语法差异，比如Hive的分区、分桶，而传统SQL可能有不同的优化方式。数据类型方面，Hive支持复杂类型如数组，传统SQL更偏向标量类型，如引用2中的STRING、TIMESTAMP等。 部署环境方面，Hive需要Hadoop生态系统，传统SQL独立部署。扩展性方面，Hive水平扩展容易，传统SQL垂直扩展为主。 使用场景方面，Hive适合大数据分析、批处理，传统SQL适合事务处理、实时查询。例如，引用1提到Spark SQL与Hive Metastore的集成，说明Hive在大数据生态中的位置。 可能需要检查Hive是否支持ACID，根据引用3，Hive在较新版本支持，但传统SQL如MySQL更早支持。此外，Hive的UDF和传统SQL的UDF实现方式可能不同，如引用2提到Impala的UDF用C++/Java，而传统SQL可能用不同语言。 用户可能需要了解何时选择HiveSQL，何时用传统SQL。比如大数据处理选Hive，高频事务用传统SQL。同时，Hive的优化方法如分区、索引与传统SQL的索引机制不同。 最后，生成相关问题时要覆盖性能优化、应用场景、功能对比、事务处理等，帮助用户深入理解。</think>### HiveSQL与传统SQL对比分析 #### 1. **架构设计与应用场景** - **HiveSQL** 基于Hadoop生态系统设计，面向大数据分析场景（如数仓、ETL），适合处理$PB$级数据。底层依赖MapReduce/Tez/Spark计算框架，通过元数据管理（Hive Metastore）实现表结构映射[^1]。 **典型场景**：日志分析、离线批处理、大规模数据聚合。 - **传统SQL** 服务于关系型数据库（如MySQL、Oracle），面向OLTP（联机事务处理），处理$GB$到$TB$级数据，强调低延迟和高并发。 **典型场景**：电商交易、金融账户管理、实时查询。 #### 2. **功能差异** | **特性** | **HiveSQL** | **传统SQL** | |------------------|--------------------------------------|--------------------------------| | **事务支持** | 有限ACID（需配置，新版本支持更完善）[^3] | 完整ACID支持 | | **索引机制** | 支持分区、分桶，较少使用B树索引 | B树、哈希索引优化查询 | | **扩展性** | 水平扩展（依赖HDFS） | 垂直扩展为主 | | **UDF/UDAF** | 支持Java/Python等扩展[^1][^2] | 支持存储过程、触发器 | | **延迟** | 分钟级至小时级 | 毫秒级至秒级 | #### 3. **语法与数据类型** - **HiveSQL特有语法** - 分区表：`CREATE TABLE ... PARTITIONED BY (dt STRING)` - 分桶：`CLUSTERED BY (id) INTO 10 BUCKETS` - 复杂数据类型：`ARRAY<STRING>`, `MAP<STRING, INT>` - 示例： ```sql SELECT TRANSFORM(col1) USING 'python_script.py' FROM table; ``` - **传统SQL特性** - 多表JOIN优化（如嵌套循环、哈希连接） - 严格的事务控制：`BEGIN TRANSACTION; ... COMMIT;` - 数据类型以标量为主（如`VARCHAR(255)`、`DECIMAL(10,2)`）[^2] #### 4. **性能优化对比** - **HiveSQL优化** - 数据分区减少扫描范围 - 向量化查询（Hive 0.13+） - Tez引擎替代MapReduce提升DAG执行效率 - **传统SQL优化** - 索引选择性分析 - 查询执行计划调优（如EXPLAIN命令） - 内存缓存机制（如InnoDB Buffer Pool） #### 5. **部署与生态** - **HiveSQL依赖** Hadoop集群（HDFS+YARN）、元数据存储（如MySQL或Derby）[^1] 可集成Spark、HBase等组件实现混合计算。 - **传统SQL依赖** 独立数据库服务器（如Oracle RAC、MySQL主从架构） 生态工具侧重备份恢复（如mysqldump）、监控（如Prometheus）。 --- ###
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值