hive 连接查询sql对比效率

最新推荐文章于 2024-11-26 11:59:09 发布
最新推荐文章于 2024-11-26 11:59:09 发布
阅读量631 收藏
点赞数
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 2263
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 1728
今天分享一下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 1159
这份文档提供了一系列的SQL查询示例，用于解决数据分析中的常见问题，如用户活跃度、留存率、销售统计等。通过这些示例，用户可以学习如何使用SQL进行高效的数据查询和分析。
HiveSQL优化
AGI爱好者_数仓&可视化&数据分析工程师_Vae’伯乐‘
11-26 1965
将那些产生倾斜的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性能优化
06-02
hive查询优化实践总结 optimizing hive queries， 详细描述了一些经典hive查询优化方法
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
w2009211777的专栏
06-28 1720
交易ID：交易日期：客户IDproduct_id：产品IDamount：交易金额在处理TB级甚至更大规模数据时，Hive通过分布式存储和计算展示了其显著的优势。相较于传统的关系型数据库如MySQL，Hive能够更加高效地处理大规模数据查询，具有更高的扩展性和容错能力。
大数据培训Hive 提高查询效率的方法
weixin_45695430的博客
03-28 1470
以下文章来源于数据社 1、开启FetchTask 一个简单的查询语句，是指一个没有函数、排序等功能的语句，当开启一个Fetch Task功能，就执行一个简单的查询语句不会生成MapRreduce作业，而是直接使用FetchTask，从hdfs文件系统中进行查询输出数据，从而提高效率。 设置的方式： Hive.fetch.task.conversion 默认为minimal 修改配置文件hive-site.xml <property> <name>hive.fetch.task
Hive与Mysql数据库比较
h304812440的博客
07-26 6260
经常会被一些不懂大数据的开发问： Hive是什么？Hive与传统型数据库有什么区别？ Hive是一个基于Hadoop的数据仓库平台。 Hive定义了一个类似于SQL的查询语言：HQL，能够将用户编写的QL转化为相应的Mapreduce程序基于Hadoop执行。 今天就以Mysql为例，简单比较一下两者区别。 查询语言 HQL SQL 数据存储位置 HDFS...
hive mysql 效率 对比_hive 连接查询sql对比效率
weixin_42355421的博客
01-19 809
准备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对比分析 #### 1. **架构设计与应用场景** - **HiveSQL** 基于Hadoop生态系统设计，面向大数据分析场景（如数仓、ETL），适合处理$PB$级数据。底层依赖MapReduce/Tez/Spark计算框架...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值