Hive特殊查询&Beeline

特殊查询

Grouping set
Grouping sets是union的简写
Cubes 立方体
WITH CUBE
列出元素的所有组合情况
Rollups 筒
with ROLLUP
依次少一个
Grouping__ID function
对于每一列，如果该列已在该行中聚合，则为结果集中的一行生成“1”值，否则值为“0”
Grouping_id其实就是所统计各列二进制和
Groupping

示例

官方表结构
create external table key_val
(
key string,
value string
)
-- 记录行的分隔符
row format delimited
-- 列的分隔符
fields terminated by ','
-- 存储文件的格式;textfile是默认的,写与不写都是一样的
stored as textfile ;
官方数据:(group.txt)
1,NULL
1,1
2,2
3,3
3,NULL
4,5
官方的例子：
SELECT key, value GROUPING__ID, count(*)
FROM key_val
GROUP BY key, value WITH ROLLUP;
SELECT key, value, GROUPING__ID, count(*)
FROM key_val
GROUP BY key, value
GROUPING SETS ( (key, value), (key), ( ));
SELECT key, value, count(*)
FROM key_val
GROUP BY key, value
Union
SELECT key, null,count(*)
FROM key_val
GROUP BY key, null
Union
SELECT value, null, count(*)
FROM key_val
GROUP BY value, null;
SELECT key, value,
GROUPING__ID, count(*)
FROM key_val
GROUP BY key, value;
SELECT key, null,count(*)
FROM key_val
GROUP BY key, null;
SELECT value, null, count(*)
FROM key_val
GROUP BY value, null;
SELECT null, null, SUM( *) FROM key_val;
常见的SQL语句函数
UDF、UDTF、UDAF
select concat(id,'---------',name) from a_dynasty;
www.cmd5.com
bin/hive -e 'select * from mydata.a_dynasty';
bin/hive --help

Beeline–java程序连接hive

先启动服务器端：nohup bin/hiveserver2 &
再启动客户端：bin/beeline
连接客户端：
Jdbc的url;
# /木有跟上数据库,一定要切换数据库(use 数据库的名字);默认是default;
!connect
!connect jdbc:hive2://node7-4:10000 root 123456
!connect jdbc:hive2://node7-4:10000/mydata root 123456
服务器端配置:(conf/hive-site.xml)
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.56.1/hive-demo?createDatabaseIfNotExist=true&amp;useSSL=false</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
apache-hive-bin
hive-jdbc-3.1.1-standalone.jar

UDF–UDTF–UDAF

库：mydata 表：a_dynasty
运算符--字符串相加
0: jdbc:hive2://node7-4:10000> select concat(name,"--->",capital) from a_dynasty;
0: jdbc:hive2://node7-4:10000> select name || capital from a_dynasty;
----------------------------------------------------------------------------
时间戳
0: jdbc:hive2://node7-4:10000> select current_timestamp();
UDTF:User Defined Table-Generating(表格生成) Functions ;输入是一条,输出是多条
0: jdbc:hive2://node7-4:10000> select explode (array('A','B','C'));
===========================================
DFS HDFS中的命令
0: jdbc:hive2://node7-4:10000> dfs -cat /hw/psn_3/data/data.txt;
----------------------------------------------------------------------------
HIVE客户端常用命令
bin/hive
set
!shell命令
dfs hdfs命令
source sql文件的路径
----------------------------------------------------------------------------
先启动一个hive客户端;执行一个sql语句,退出hive客户端
[root@node7-2 apache-hive]# bin/hive -e 'select * from mydata.a_dynasty'
------------------------------------------------------------------------------
要执行多个sql语句,将多个sql语句写到一个sql文件中
demo.sql
use mydata;
select * from a_dynasty;
bin/hive -f '/root/demo.sql'
----------------------------------------------------------------------------
[root@node7-2 apache-hive]# bin/hive -S -f '/root/demo.sql'
----------------------------------------------------------------------------
[root@node7-2 apache-hive]# bin/hive -S -e 'select * from mydata.a_king;'
----------------------------------------------------------------------------
[root@node7-2 apache-hive]# bin/hive -S -f /root/demo.sql >> /root/data.txt

最后

以上就是心灵美书包为你收集整理的Hive特殊查询&Beeline的全部内容，希望文章能够帮你解决Hive特殊查询&Beeline所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。