关键字:Hive SELECT、ORDER BY、SORT BY、DISTRIBUTE BY、CLUSTER BY、Hive子查询、Hive虚拟列
八、Hive的查询语句SELECT
在所有的数据库系统中,SELECT语句是使用最多,也最复杂的一块,Hive中的查询语句SELECT支持的语法当然也比较复杂,本文只能尽力去介绍。
8.1 基础查询语法
Hive中的SELECT基础语法和标准SQL语法基本一致,支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等;
语法如下:
[WITH CommonTableExpression (, CommonTableExpression)*] SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]
下面介绍Hive中比较特殊的一些查询语法。
8.2 ORDER BY和SORT BY
ORDER BY用于全局排序,就是对指定的所有排序键进行全局排序,使用ORDER BY的查询语句,最后会用一个Reduce Task来完成全局排序。
SORT BY用于分区内排序,即每个Reduce任务内排序。
看下面的例子:
原表数据为:
hive> select * from lxw1234_com; OK 5 3 6 2 9 8 1
使用ORDER BY
hive> select * from lxw1234_com order by id; 1 2 3 5 6 8 9
使用SORT BY
hive> set mapred.reduce.tasks=2;
hive> select * from lxw1234_com sort by id;
2
5
6
9
1
3
8
设定了2个reduce,从结果可以看出,每个reduce内做了排序。
如果reduce数为1,那么ORDER BY和SORT BY的结果是一样的:
hive> set mapred.reduce.tasks=1; hive> select * from lxw1234_com sort by id; 1 2 3 5 6 8 9
真实业务环境中,我们的需求大多需要使用ORDER BY全局排序来完成。
8.3 DISTRIBUTE BY和CLUSTER BY
distribute by:按照指定的字段或表达式对数据进行划分,输出到对应的Reduce或者文件中。
cluster by:除了兼具distribute by的功能,还兼具sort by的排序功能。
##distribute by
hive> set mapred.reduce.tasks=2; hive>INSERT overwrite LOCAL directory '/tmp/lxw1234/' SELECT id FROM lxw1234_com distribute BY id;
执行后在本地的/tmp/lxw1234目录中生成了000000_0和000001_0两个文件:
cat 000000_0
8
2
6
cat 000001_0 ##id%2=1的记录
1
9
3
5
##cluster by
hive> set mapred.reduce.tasks=2; hive> INSERT overwrite LOCAL directory '/tmp/lxw1234/' SELECT id FROM lxw1234_com CLUSTER BY id; cat 000000_0 2 6 8 cat 000001_0 1 3 5 9
注意:使用cluster by之后,每个文件中的id都进行了排序,而distribute by没有。
8.4 子查询
子查询和标准SQL中的子查询语法和用法基本一致,需要注意的是,Hive中如果是从一个子查询进行SELECT查询,那么子查询必须设置一个别名。
SELECT col FROM ( SELECT a+b AS col FROM t1 ) t2
另外,从Hive0.13开始,在WHERE子句中也支持子查询,比如:
SELECT * FROM A WHERE A.a IN (SELECT foo FROM B); SELECT A FROM T1 WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
还有一种将子查询作为一个表的语法,叫做Common Table Expression(CTE):
with q1 as (select * from src where key= '5'), q2 as (select * from src s2 where key = '4') select * from q1 union all select * from q2; with q1 as ( select key, value from src where key = '5') from q1 insert overwrite table s1 select *;
8.5 虚拟列
Hive查询中有两个虚拟列:
INPUT__FILE__NAME:数据对应的HDFS文件名;
BLOCK__OFFSET__INSIDE__FILE:该行记录在文件中的偏移量;
hive> select id,INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE from lxw1234_com; 5 hdfs://cdh5/tmp/lxw1234/1.txt 0 3 hdfs://cdh5/tmp/lxw1234/1.txt 2 6 hdfs://cdh5/tmp/lxw1234/1.txt 4 2 hdfs://cdh5/tmp/lxw1234/1.txt 6 9 hdfs://cdh5/tmp/lxw1234/1.txt 8 8 hdfs://cdh5/tmp/lxw1234/1.txt 10 1 hdfs://cdh5/tmp/lxw1234/1.txt 12
Hive查询中还包括:
Hive的内置操作和函数: http://superlxw1234.iteye.com/admin/blogs/2216500
Hive窗口分析函数:http://superlxw1234.iteye.com/admin/blogs/2205770
相关推荐
1.上传tar包 2.解压 tar -zxvf hive-1.2.1.tar.gz 3.安装mysql数据库 推荐yum 在线安装 4.配置hive (a)配置HIVE_HOME环境变量 vi conf/hive-env.sh ... bin/hive -e 'select * from t_test'
Antlr是一种语言识别的工具,可以用来构造领域语言。 使用antlr需要我们提前定义好识别字符流的词法规则和...比如 select a as date from mytable 这个date不添加转义会报错的,但是该处如果添加 “ | KW_DATE ” dat
HQL 中对查询语句的解释、优化、生成查询计划是由 Hive 完成的 所有的数据都是存储在 Hadoop 中 查询计划被转化为 MapReduce 任务,在 Hadoop 中执行(有些查询没有 MR 任 务,如: select * from table ) ...
查询语句语法: [WITH CommonTable[removed], CommonTableExpression)*] Only available starting with Hive 0.13.0) SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_...
本文主要给大家介绍了关于Mysql元数据生成Hive建表语句注释脚本的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍: 最近在将数据从Mysql 等其他关系型数据库 抽取到Hive 表中时,需要同步...
hive>insert overwrite table tongji select '2017-07-09',tab1.pv,tab2.uv,tab3.vv,t
查询语句语法: [WITH CommonTable[removed], CommonTableExpression)*] (Note: Only available starting with Hive 0.13.0) SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference ...
删除hive-exec与hadoop-common的maven依赖,使得项目更加的轻量级。 重构项目代码,优化解析,修复无字段血缘时,不能获取表血缘的BUG。 规范化接口输入输出,血缘图均为自定义实体,方便进行JSON序列化。 新增接口...
28.2 查询语句(SELECT...ORDER BY) 103 28.2.1 ORDER BY查询语句实例 103 28.2.2 JDBC ORDER BY 查询语句实例 104 28.3 查询语句(GROUP BY) 104 28.3.1 GROUP BY查询语句实例 104 28.3.2 JDBC GROUP BY查询语句实例 ...
先说说思路:1是直接从mysql输出至hive中,另外一个是先将数据搞到hdfs中,再load一下,当然这只是textfile存储格式而言,若是ORC等其他列式存储的话建议先搞一个textfile格式的中间表,再insert into table select ...
这些SELECT 语句的复杂程度超过大多数实际的OLTP 应用,一个SELECT 执行时间少则几十秒,多则达15 小时以上,22 个查询语句执行一遍需数个小时。(附:) 2.步骤 (0)搭建环境,安装四种软件:Spark,Hive,Cassandra...
目录 第一部分 Spark学习 ....................................................................................................................... 6 第1章 Spark介绍 .........................................
数据库审计是技术之⼀,技术主要包括:数据库漏扫、、、、。 ⿊客的⾏为,可以通过数据库审计发现。 数据库审计系统能⼲什么? 性能监控 SQL吞吐、会话监控、性能瓶颈 攻击预警 对漏洞攻击、SQL注⼊、敏感语句、...
ireport数据报表分析平台,目标:1、可以连接oracle、mysql、hive、spark等数据源(hive 和spark 还未整合进来,oracle和mysql多数据源同时使用,还有问题。);2、可以通过界面进行点击选择,生产报表SQL语句。界面...