012 大数据之HIVE查询

012 大数据之HIVE查询 1、DBeaver连接HIVE查询实战 1.1、HiveServer2的相关知识

Hive架构之HiveServer2
Prerequisites: Have Hive installed and setup to run on Hadoop cluster.
HiveServer2 a.k.a HS2 is a second-generation Hive server that enables：
① Remote clients to execute queries against the Hive server;
② Multi-client concurrency and authentication;
③ Better supports for API client like JDBC and ODBC;
HiveServer2 Web UI： The Web UI is available at port 10002 (127.0.0.1:10002) by default.
Using Beeline CLI：beeline -u jdbc:hive2://127.0.0.1:10000 scott tiger

1.2、先启动hiveserver2

[atguigu@hadoop102 ~]$ hive --service hiveserver2
which: no hbase in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/module/jdk1.8.0_212/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/apache-zookeeper-3.5.7-bin/bin:/opt/module/apache-zookeeper-3.5.7-bin/sbin:/opt/module/jdk1.8.0_212/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/apache-hive-3.1.2-bin/bin:/home/atguigu/.local/bin:/home/atguigu/bin)
2022-01-06 21:43:58: Starting HiveServer2
Hive Session ID = b97cbe6e-89e9-40ac-8aa7-c9a3ad520828
Hive Session ID = b2cb844b-cc3a-4b5a-b8e9-5fe8e3927bee
Hive Session ID = f1cf9d83-85ff-4706-8c41-b07b1b9736fb
Hive Session ID = 238898b4-2d09-486a-a916-eaea4c0c38db

beeline 执行SQL语句不打印INFO

[atguigu@hadoop102 ~]$ hive --service hiveserver2 --hiveconf hive.server2.logging.operation.level=NONE
which: no hbase in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/module/jdk1.8.0_212/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/apache-zookeeper-3.5.7-bin/bin:/opt/module/apache-zookeeper-3.5.7-bin/sbin:/opt/module/jdk1.8.0_212/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/apache-hive-3.1.2-bin/bin:/home/atguigu/.local/bin:/home/atguigu/bin)
2022-01-06 22:48:13: Starting HiveServer2
Hive Session ID = b1f72f4e-1da5-496a-829b-9e38aae89306
Hive Session ID = 56beef0a-3792-42f3-aa93-427a436c85ae
Hive Session ID = 706d2870-2b2b-4437-a18a-3debbac17a3a
Hive Session ID = 9ec9ce6b-8cea-43e7-af0f-135116682fc1
[atguigu@hadoop102 ~]$ beeline -u jdbc:hive2://hadoop102:10000 -n atguigu
Connecting to jdbc:hive2://hadoop102:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive
0: jdbc:hive2://hadoop102:10000> select true;
+-------+
|  _c0  |
+-------+
| true  |
+-------+
1 row selected (3.203 seconds)

1.3、DBeaver安装好后填写连接信息，测试连通性

1.4、执行查询语句

# 其他双目运算符只要有一个参数为NULL则结果为NULL，<=>特殊对待
0: jdbc:hive2://hadoop102:10000> SELECT NULL<=>NULL;
+-------+
|  _c0  |
+-------+
| true  |
+-------+
1 row selected (0.161 seconds)
0: jdbc:hive2://hadoop102:10000> SELECT NULL<=>1;
+--------+
|  _c0   |
+--------+
| false  |
+--------+
1 row selected (0.137 seconds)

LIKE 表示模糊查询，RLIKE 可以使用Java的正则表达式来模糊查询

0: jdbc:hive2://hadoop102:10000> select * from emp where ename LIKE 'A%';
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
| emp.empno  | emp.ename  |  emp.job  | emp.mgr  | emp.hiredate  | emp.sal  | emp.comm  | emp.deptno  |
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
| 7499       | ALLEN      | SALESMAN  | 7698     | 1981-2-20     | 1600.0   | 300.0     | 30          |
| 7876       | ADAMS      | CLERK     | 7788     | 1987-5-23     | 1100.0   | NULL      | 20          |
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
2 rows selected (0.252 seconds)
0: jdbc:hive2://hadoop102:10000> select * from emp where ename LIKE '_A%';
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
| emp.empno  | emp.ename  |  emp.job  | emp.mgr  | emp.hiredate  | emp.sal  | emp.comm  | emp.deptno  |
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
| 7521       | WARD       | SALESMAN  | 7698     | 1981-2-22     | 1250.0   | 500.0     | 30          |
| 7654       | MARTIN     | SALESMAN  | 7698     | 1981-9-28     | 1250.0   | 1400.0    | 30          |
| 7900       | JAMES      | CLERK     | 7698     | 1981-12-3     | 950.0    | NULL      | 30          |
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
3 rows selected (0.197 seconds)
0: jdbc:hive2://hadoop102:10000> select * from emp where ename  RLIKE '[A]';
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
| emp.empno  | emp.ename  |  emp.job  | emp.mgr  | emp.hiredate  | emp.sal  | emp.comm  | emp.deptno  |
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
| 7499       | ALLEN      | SALESMAN  | 7698     | 1981-2-20     | 1600.0   | 300.0     | 30          |
| 7521       | WARD       | SALESMAN  | 7698     | 1981-2-22     | 1250.0   | 500.0     | 30          |
| 7654       | MARTIN     | SALESMAN  | 7698     | 1981-9-28     | 1250.0   | 1400.0    | 30          |
| 7698       | BLAKE      | MANAGER   | 7839     | 1981-5-1      | 2850.0   | NULL      | 30          |
| 7782       | CLARK      | MANAGER   | 7839     | 1981-6-9      | 2450.0   | NULL      | 10          |
| 7876       | ADAMS      | CLERK     | 7788     | 1987-5-23     | 1100.0   | NULL      | 20          |
| 7900       | JAMES      | CLERK     | 7698     | 1981-12-3     | 950.0    | NULL      | 30          |
+------------+------------+-----------+----------+---------------+----------+-----------+-------------+
7 rows selected (0.228 seconds)