Hive的基本语法与使用
苟有恒,何必三更眠五更起;
最无益,莫过一日曝十日寒。
知识点01:yarn回顾
-
TextInputFormat中分片的规则是什么?
-
规则:判断文件大小是否满足128M的1.1
-
满足:128M一个分片
-
不满足:整体成为一个分片
-
公式
max(minSplit,min(MaxSplit,BlockSize))
-
-
什么是Reduce Join,特点和应用场景是什么?
-
思想:将两个文件的关联字段作为K2,利用Shuffle过程中的分组实现两个文件的关联
-
特点:必须经过Shuffle过程,Reduce端实现join
-
场景:大文件 join 大文件
-
-
什么是Map Join,特点和应用场景是什么?
-
思想:将小文件的整个内容放入分布式缓存中,在每台节点上让大文件的每个部分都与完整的小文件进行关联
-
特点:不需要经过shuffle,Map端实现Join
-
场景:小 join 大,小 join 小
-
-
YARN的架构是什么?
-
分布式主从架构
-
ResourceManager
- 管理从节点:心跳机制
- 资源管理和任务调度
- 接客
-
NodeManager
- 负责管理自己所在节点的资源,执行主节点分配的任务
-
-
常见的调度机制有哪些,各自的特点和区别是什么?
-
FIFO:单队列,队列内部是FIFO,不能并行和并发
-
Capacity:多队列,队列内部是FIFO,能并行
-
Fair:多列列,队列内部是公平共享资源,能并行和并发
-
-
什么是Hive,有什么功能,应用场景是什么?
- Hive本质:一种支持SQL的Hadoop客户端
- 功能
- 一:将HDFS文件映射成表的数据
- 构建数据仓库
- 二:将SQL转换为MR,提交给YARN
- 实现分布式计算:替代品:Presto、Impala、SparkSQL……
- 一:将HDFS文件映射成表的数据
- 应用
- 用于构建数据仓库
知识点02:今日目标
- Hive的功能是怎么实现的?【重点】
- 如何能将HDFS的文件映射成表的数据?
- 如何能将SQL转换为MapReduce的程序?
- Hive元数据的管理==【掌握】==
- 元数据存储
- 元数据使用
- 元数据服务
- Hive基本使用
- 客户端
- SQL脚本
- 命令、日志配置
- HiveSQL语法:HQL**【重点】**
- DDL
- DML
- DQL
- Hive中表的分类
- 管理表
- 临时表
- 外部表
知识点03:Hive与HDFS的数据映射
-
引入:SQL对表进行操作,Hive是如何知道表对应的HDFS数据是哪个的?
-
目标:掌握Hive与HDFS数据映射关系
-
路径
- step1:Hive对象与HDFS的关系
- step2:元数据映射
-
实施
-
Hive对象与HDFS的关系
-
数据库:每个数据库会在HDFS中对应一个目录
- 目录的名字:数据库名.db
-
-
- 表:每张表会在数据库对应的目录下建立一个与表同名的目录
- 表的数据:映射的是HDFS上的文件
-
元数据映射
-
当执行SQL语句,Hive如何知道这个表对应的HDFS的数据是什么?
select * from wordcount;
-
所有Hive中数据库、表与HDFS的映射关系都存储在元数据中,Hive服务端会读取元数据找到这张表对应的HDFS数据
-
过程
- 先检索数据库的信息
-
- 再检索表的信息
- 通过表的SD_ID来获取这张表映射的HDFS的地址
- 将整个表的目录中所有数据进行读取并返回
-
小结
- Hive与HDFS的映射关系怎么实现的?
- 将Hive中对象与HDFS映射记录在元数据中
- 数据库:HDFS目录
- 表:HDFS目录
- 表中的数据:HDFS文件
知识点04:Hive转换MapReduce过程
-
引入:SQL对表进行操作,Hive是如何转换为MapReduce程序的?
-
目标:了解Hive转换MapReduce的过程
-
路径
- step1:功能映射
- step2:执行解析
-
实施
-
功能映射
-
SQL语法
select 1 from 2 where 3 group by 4 having 5 order by 6 limit 7
- 1-决定了结果中的列的内容
-
MapReduce阶段
- Input、Map、Shuffle、Reduce、Output
-
映射
MapReduce SQL Input 2 Map 1,2 Shuffle 4,6 Reduce 5,7 Output 将SQL结果保存
-
-
执行解析
select region,count(*) as numb from tb_house where region != '浦东' group by region order by numb;
-
查看执行计划:explain
explain select region,count(*) as numb from tb_house where region != '浦东' group by region order by numb;
-
-
-
小结
- 了解SQL语法与MapReduce的基本映射关系即可
知识点05:元数据:metastore功能与存储
-
引入:Hive的元数据的功能是什么?元数据都存储在什么位置?
-
目标:了解metastore的功能与存储方式
-
路径
- step1:metastore的功能
- step2:metastore的存储方式
-
实施
-
metastore的功能
- Hive中的元数据记录了Hive中所有对象信息
- 数据库信息
- 表的信息
- 字段的信息
- 重点记录了Hive表与HDFS文件的映射关系
- 每次创建表关联文件:Hive都会自动创建表的元数据
- 每次查询表的数据:Hive都会从元数据中获取表对应的HDFS信息
- Hive中的元数据记录了Hive中所有对象信息
-
metastore的存储三种方式
-
方式
- 嵌入式数据库:Local/Embedded Metastore Database (Derby)
- 存储在derby中
- 本地数据库
- 存储在MySQL中,直接访问
- 远程Metastore服务
- 存储在MySQL中,但是是通过一个进程来进行访问
- 嵌入式数据库:Local/Embedded Metastore Database (Derby)
-
位置
-
默认位置:Hive自带的Derby数据库
- 缺点:不能共享,不能启动多个实例,一般不用
-
自定义位置:自定义将元数据存储在其他数据库中
- 类型:MySQL、Oracle、PostGrepSQL,工作中一般存储在MySQL中
-
-
-
-
小结
- metastore的功能是什么?
- 存储Hive中所有对象的信息:数据库、表、列
- 存储Hive中表与HDFS的映射关系
- metastore存储在什么位置?
- 默认位置:derby数据库
- 自定义位置:MySQL中
- metastore的功能是什么?
知识点06:元数据:metastore共享问题
-
引入:实际工作中如果不使用Hive本身做计算处理,外部的计算系统如何获取Hive中的表的信息?
-
目标:了解Hive中的元数据共享问题
-
路径
- step1:工作中的应用场景
- step2:面临的问题
-
实施
- 工作中的应用场景
- 不使用Hive来实现数据仓库中的分布式计算
- 使用替代品:SparkSQL、Impala、Presto
- 更快,性能更好,语法都兼容Hive的语法
- 面临的问题
- 问题1:如果用SparkSQL来处理Hive数据仓库中的表,SparkSQL怎么知道Hive中有哪些表?
- 解决:让SparkSQL读取Hive元数据就可以了
- 问题2:如何SparkSQL获取了Hive的元数据,SparkSQL怎么知道这个元数据的含义是什么?
- 解决:解析元数据的含义
- 问题3:如果多个框架都需要访问Hive的元数据,每个框架都封装解析代码,就非常冗余
- 问题1:如果用SparkSQL来处理Hive数据仓库中的表,SparkSQL怎么知道Hive中有哪些表?
- 工作中的应用场景
-
小结
- metastore共享的问题是什么?
- 大家都需要访问元数据,但是只有Hive知道元数据是什么含义,怎么解决
知识点07:元数据:metastore服务
-
引入:如何解决元数据共享的问题?
-
目标:掌握metastore服务的功能与使用
-
路径
- step1:metastore的功能
- step2:metastore的配置
- step3:metastore的启动
-
实施
-
metastore的功能
- 元数据共享服务
-
step1:专门负责管理Hive的元数据
- step2:接受所有需要访问元数据的请求
-
- **注意:一旦配置了Metastore,必须先启动metastore,再启动Hive的服务端**
-
metastore的配置
- 修改hive-site.xml
<property> <name>hive.metastore.uris</name> <value>thrift://node3:9083</value> </property>
-
metastore的启动
-
先启动Metastore服务
hive --service metastore
-
- 再启动Hive的服务端和客户端
```
hive
```
-
小结
-
metastore服务的功能是什么?
- 实现元数据共享
-
metastore服务如何启动?
hive --service metastore
-
知识点08:Hive客户端与服务端
-
目标:掌握Hive 客户端的功能及应用场景
-
路径
- step1:Hive shell
- step2:Beeline与hiveserver2
- step3:JDBC
-
实施
-
Hive shell
- 功能:Hive的特殊客户端,启动时会自动包含启动服务端
- 命令
- hive
- 特点
- 服务端和客户端一体
- 交互性不太友好
- 应用
- 用于封装SQL脚本
-
Beeline与hiveserver2
-
功能
- beeline:纯客户端
-
hiveserver2:Hive中独立的服务端进程
-
命令
-
beeline:启动Hive纯客户端
beeline -u jdbc地址 -n 用户名 -p 密码 beeline !connect jdbc地址 用户名 密码
-
hiveserver2:启动Hive服务端
hive --service hiveserver2 或者 hiveserver2
-
启动测试
-
先启动metastore
hive --service metastore
- 端口:9083
-
再启动Hive服务端
hiveserver2
- 端口:10000
-
-
-
配置
-
修改Hadoop用户授权
-
关闭HDFS和YARN
stop-dfs.sh stop-yarn.sh
-
修改core-site.xml
cd /export/server/hadoop-2.7.5/etc/hadoop/ vim core-site.xml
<property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value> </property> <property> <name>hadoop.proxyuser.root.groups</name> <value>*</value> </property>
-
分发
scp core-site.xml node2:$PWD scp core-site.xml node3:$PWD
-
启动HDFS和YARN
start-dfs.sh start-yarn.sh
-
-
复制standalone包
cd /export/server/hive-2.1.0-bin cp jdbc/hive-jdbc-2.1.0-standalone.jar lib/
-
-
启动
-
方式一:直接进入
beeline !connect jdbc:hive2://node3:10000 root 123456
-
-
-
- 方式二:直接连接
```
beeline -u jdbc:hive2://node3:10000 -n root -p 123456
```
- 退出:!quit
- 特点
- 交互性比较友好
- 应用
- 一般用于交互式的查询
-
JDBC
-
语法
-
基本与MySQL的JDBC是一致的
//step1:指定驱动类 //step2:构建连接对象 //step3:构建SQL对象 //step4:执行SQL获取结果
-
-
测试
package bigdata.itcast.cn.hive.client.jdbc; import java.sql.SQLException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.sql.DriverManager; public class HiveJdbcClient { private static String driverName = "org.apache.hive.jdbc.HiveDriver"; /** * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { try { Class.forName(driverName); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); System.exit(1); } //replace "hive" here with the name of the user the queries should run as Connection con = DriverManager.getConnection("jdbc:hive2://node3:10000/default", "root", "123456"); Statement stmt = con.createStatement(); String tableName = "tb_house"; String sql = "select region,t_price,s_price from " + tableName +" limit 100"; System.out.println("Running: " + sql); ResultSet res = stmt.executeQuery(sql); while (res.next()) { System.out.println(res.getString(1) + "\t" + res.getInt(2)+ "\t" + res.getInt(3)); } } }
-
应用
- 一般用于封装交互式的程序:Navicat、DataGrip
-
-
小结
-
Hive的常见客户端以及各自的应用场景是什么?
-
客户端
- Hive Shell:客户端与服务端是一体的,用于封装SQL脚本
- Beeline:交互性比较友好,单纯的客户端,必须搭配服务端来实现,一般用于交互式开发测试
- JDBC:原生的JDBC的接口代码,一般用于封装交互式的客户端,我们一般不用
-
服务端
-
hiveserver2:10000
hive --service hiveserver2 hiveserver2
-
metastore:9083
-
-
-
知识点09:启动脚本与SQL脚本
-
引入:工作中如何封装SQL脚本实现SQL脚本的自动化运行?
-
目标:掌握HIve的SQL脚本的封装
-
路径
- step1:封装Hive服务端的启动脚本
- step2:HiveSQL脚本的封装
- step3:SQL脚本中传递变量
-
实施
-
封装Hive服务端的启动脚本
-
创建日志目录
mkdir /export/server/hive-2.1.0-bin/logs
- DEBUG:详细的日志级别
- INFO:显示的信息会包含主要的日志信息
- WARN:只记录警告级别的日志
- ERROR:只记录错误级别的日志
-
Metastore启动脚本
vim /export/server/hive-2.1.0-bin/bin/start-metastore.sh
#!/bin/bash #HIVE_HOME HIVE_HOME=/export/server/hive-2.1.0-bin #run metastore $HIVE_HOME/bin/hive --service metastore >> $HIVE_HOME/logs/metastore.log 2>&1 &
-
HiveServer2启动脚本
vim /export/server/hive-2.1.0-bin/bin/start-hiveserver2.sh
#!/bin/bash #HIVE_HOME HIVE_HOME=/export/server/hive-2.1.0-bin #run hiveserver2 $HIVE_HOME/bin/hiveserver2 >> $HIVE_HOME/logs/hiveserver2.log 2>&1 &
-
Beeline启动脚本
vim /export/server/hive-2.1.0-bin/bin/start-beeline.sh
#!/bin/bash #HIVE_HOME HIVE_HOME=/export/server/hive-2.1.0-bin #run beeline $HIVE_HOME/bin/beeline -u jdbc:hive2://node3:10000 -n root -p 123456
-
修改权限
chmod u+x /export/server/hive-2.1.0-bin/bin/start-*
-
-
-
HiveSQL脚本的封装
-
需求:每天00:01分自动对昨天的数据做分析
select count(*) from table where daystr = '2021-05-01';
-
问题1:每天的0点01分自动执行,怎么实现?
-
Linux Crontab:定时任务
* * * * * Linux command
-
实现
01 00 * * * hive -e 'select count(*) from table where daystr = '2021-05-01';'
-
-
问题2:怎么让Hive的SQL语句在Linux的命令行执行?
-
解决:利用Hive Shell的客户端来实现,查看客户端的用法
[root@node3 ~]# hive -H usage: hive -d,--define <key=value> Variable subsitution to apply to hive commands. e.g. -d A=B or --define A=B --database <databasename> Specify the database to use -e <quoted-query-string> SQL from command line -f <filename> SQL from files -H,--help Print help information --hiveconf <property=value> Use value for given property --hivevar <key=value> Variable subsitution to apply to hive commands. e.g. --hivevar A=B -i <filename> Initialization SQL file -S,--silent Silent mode in interactive shell -v,--verbose Verbose mode (echo executed SQL to the console)
-
-e:执行命令行中的SQL语句
hive -e 'show databases;'
- 直接执行命令行中提供的SQL语句
- 应用:要执行比较少的单条SQL语句
-
-f:执行一个SQL文件
-
创建一个SQL文件:vim /export/data/hive.sql
show databases; use default; select region,s_price,area from tb_house limit 10;
-
执行SQL文件
hive -f /export/data/hive.sql
-
-
-
工作中的使用
-
定时任务
01 00 * * * bash /export/data/exec.sh
-
exec.sh
#!/bin/bash #定义变量 HIVE_HOME=/export/server/hive-2.1.0-bin #运行SQL语句 #$HIVE_HOME/bin/hive -e 'show databases;' $HIVE_HOME/bin/hive -f /export/data/hive.sql
-
-
-
-
SQL脚本中传递变量
-
问题:如果运行的SQL文件,SQL文件中的SQL语句中的参数是动态变化的,如何解决?
-
解决:通过–hiveconf,将Shell脚本中变量转换为一个Hive中的变量
-
–hiveconf:用于定义Hive中属性的值或者定义Hive中的变量
-
shell脚本
#!/bin/bash #获取昨天的日期 yesterday=`date -d '-1 day' +%Y%m%d` #定义变量 HIVE_HOME=/export/server/hive-2.1.0-bin #运行SQL语句 #$HIVE_HOME/bin/hive -e 'select count(*) from table where daystr = '${yesterday}';' $HIVE_HOME/bin/hive --hiveconf yester=${yesterday} -f /export/data/hive.sql
-
SQL文件
select count(*) from table where daystr = '${hiveconf:yester}';
-
-
-
小结
- 如何封装Hive的SQL脚本?
- 在Linux中执行SQL语句
- -e:执行命令行中的单条SQL语句
- -f:执行多条SQL语句封装的SQL文件
- 变量传递
- –hiveconf
- 在Linux中执行SQL语句
- 如何封装Hive的SQL脚本?
知识点10:常用命令与日志配置
-
目标:掌握基本常用命令及实现日志存储配置
-
路径
- step1:常用命令
- step2:日志存储配置
-
实施
-
常用命令
- dfs:用于直接在Hive执行HDFS的操作
-
- set:查看或者临时修改【只在当前的会话窗口有效】Hive中属性的值
- hive.exec.mode.local.auto:Hive中的本地模式
- 默认关闭,测试时可以开启,对于小数据量的MR程序运行的比较快,不提交给YARN
- 限制
- MapTask的个数不允许超过4个
- ReduceTask个数不允许超过1个
- 输入数据量不允许超过128M
- add:添加jar包或者文件到Hive的环境变量中
```
add jar xxx.jar;
add file xxx
```
- list:列举添加的文件或者jar包
```
list files
list jars
```
- delete:删除添加的文件或者jar包
-
日志存储配置
-
重命名日志配置文件
cd /export/server/hive-2.1.0-bin/conf/ mv hive-log4j2.properties.template hive-log4j2.properties
-
修改配置
vim hive-log4j2.properties
#修改第24行 property.hive.log.dir = /export/server/hive-2.1.0-bin/logs
-
重启Hive的服务端
-
-
小结
- 了解常用命令并实现日志配置即可
知识点11:HQL语法:DDL
-
目标:掌握HQL语法中的DDL命令
-
路径
- step1:数据库管理
- step2:数据表管理
-
实施
-
Hive官网语法文档:https://cwiki.apache.org/confluence/display/Hive/LanguageManual
-
数据库管理
-
列举
show databases;
-
-
- 创建
```sql
create database [if not exists ] dbname [comment] [location]
```
- comment:数据库的注释
- location:用于指定这个数据库对应的HDFS目录是谁
- 目录默认的位置:/user/hive/warehouse/dbname.db
- 如果不指定,数据库目录就在默认的目录下
- 举个栗子
```sql
create database db_emp1;
dfs -mkdir /testdb;
create database db_emp2 location '/testdb';
```
- 切换
```sql
use dbname;
```
- 删除
```sql
drop database [if exists] dbname [cascade];
```
- cascade:用于删除非空的数据库
-
数据表管理
-
列举
show tables; show tables in dbname;
-
- 创建
- 语法
```sql
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
(
col1Name col1Type [COMMENT col_comment],
co21Name col2Type [COMMENT col_comment],
co31Name col3Type [COMMENT col_comment],
co41Name col4Type [COMMENT col_comment],
co51Name col5Type [COMMENT col_comment],
……
coN1Name colNType [COMMENT col_comment]
)
[PARTITIONED BY (col_name data_type ...)] --分区表结构
[CLUSTERED BY (col_name...) [SORTED BY (col_name ...)] INTO N BUCKETS] --分桶表结构
[ROW FORMAT row_format] -- 指定数据文件的分隔符
row format delimited fields terminated by '列的分隔符' -- 列的分隔符,默认为\001
lines terminated by '行的分隔符' --行的分隔符,默认\n
[STORED AS file_format] -- 指定文件的存储格式
[LOCATION hdfs_path] -- 用于指定表的目录所在位置,默认表的目录在数据库的目录下面
```
- 流程
- step1:先确认数据的格式
- 数据的分隔符是什么
- 有几列
- 每一列的类型是什么
- step2:再按照创建表的语法去建表
- 注意:数据的格式决定了表的结构
- **==方式一:普通方式==**
- 功能:一般用于创建一张表加载数据文件,将文件构建表结构
- 先查看员工数据文件格式
- 列
```
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
员工id
员工姓名
职位
领导id
入职日期
薪水
奖金
部门编号
```
- 分隔符
- 制表符
- 创建员工表
```sql
create database db_emp;
use db_emp;
create table tb_emp(
empno string,
ename string,
job string,
managerid string,
hiredate string,
salary double,
jiangjin double,
deptno string
) row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/export/data/emp.txt' into table tb_emp;
```
- 查看部门表的数据内容
- 数据
```
10,ACCOUNTING,NEW YORK
部门编号
部门名称
部门位置
```
```
- 分隔符
- 逗号
```
- 创建部门表
```sql
create table tb_dept(
deptno string,
dname string,
loc string
)
row format delimited fields terminated by ',';
--加载数据
load data local inpath '/export/data/dept.txt' into table tb_dept;
```
- **方式二:将Select语句的结果保存到一张新表中**
- 关键字:as
- 语法
```sql
create table newTablename as select ……
```
```sql
create table tb_emp_as as select empno,ename,salary,deptno from tb_emp;
```
- **方式三:复制表的结构到一张新表中**
- 关键字:like
- 语法
```sql
create table newTablename like oldTableName;
```
```sql
create table tb_emp_like like tb_emp;
```
- 注意:只复制表结构,不复制数据内容
- 删除表
```sql
drop table [if exists] tbname;
```
- 查看表
```sql
#查看表的结构
desc tbname;
#查看表的元数据
desc formatted tbname;
```
- 清空表
```sql
truncate tbname;
```
-
小结
- 基本与MySQL数据库的语法一致
知识点12:HQL语法:DML
-
目标:掌握HQL语法中的DML命令
-
路径
- step1:删除与修改
- step2:加载文件load
- step3:插入数据insert
-
实施
-
删除与修改
-
MySQL:增删改【行级】
- insert
- delete
- update
-
Hive是否支持对数据的行级事务操作?
-
支持
-
insert
INSERT INTO TABLE tablename VALUES values_row [, values_row ...] insert into table values(1,zhangsan)
- Hive数据仓库中的数据来源
- 已经存在的大量数据
- 分析的结果:Select语句的结果
- Hive数据仓库中的数据来源
-
delete
DELETE FROM tablename [WHERE expression]
-
update
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
-
-
Hive中对于行级的事务支持有严格的要求:表的结构必须为桶表,数据文件类型必须为orc类型
-
工作中是否有对应的使用的场景?
- 不用这样的操作,没有对应的场景
- 如果有这样场景
- 用户5月1号下了一个订单,订单状态为支付成功
- 用户5月2号提交退款申请,订单状态为退款成功
-
-
加载文件load
-
应用:用于将数据文件关联到Hive的表中
-
本质:将文件直接放入表对应的HDFS目录下
-
-
- 可以的
- 语法
```
load data [local] inpath 'filePath' [overwrite] into tbname;
```
- local:表示加载本地文件,如果不加local,加载的是HDFS文件
- hdfs文件:移动了这个文件到表的目录下
- 本地文件:上传了本地文件到HDFS对应的表的目录下
- overwrite:覆盖表中所有的内容
-
插入数据insert
-
功能:将SQL语句的结果保存到一张已存在的表中或者目录中
-
语法
-
保存到已存在的表中:经常使用用于保存分析的结果
INSERT OVERWRITE|INTO TABLE tablename1 select_statement1 FROM from_statement; | FROM from_statement INSERT OVERWRITE|INTO TABLE tablename1 select_statement1 ;
-
OVERWRITE:覆盖
-
INTO:追加
-
实现
insert into table tb_emp_like select * from tb_emp limit 3;
-
-
保存到目录中:一般用于测试或者导出SQL语句的结果到文件中
INSERT OVERWRITE [LOCAL] DIRECTORY 目录 【指定分隔符】 select……
- local:不加表示保存到HDFS的目录中,加了就是本地目录
insert overwrite local directory '/export/data/insert' row format delimited fields terminated by ',' select * from tb_emp limit 5;
-
-
-
小结
- Hive中DML的命令有哪些?
- 支持行级的事务命令:insert、update、delete,但是不用
- 加载数据命令
- load:将一个已存在的文件关联到表中
- insert:保存SQL的结果
- Hive中DML的命令有哪些?
知识点13:HQL语法:DQL
-
目标:掌握HQL语法中的DQL命令
-
实施
-
基本查询
-
查询每个员工的编号、姓名、薪水及部门编号
select empno,ename,salary,deptno from tb_emp;
-
-
-
过滤查询
-
查询薪资大于2000的所有员工的姓名及薪水和部门编号
select ename,salary,deptno from tb_emp where salary > 2000;
-
-
分组查询
-
查询每个部门的人数
select deptno,count(*) as numb from tb_emp group by deptno;
-
-
排序查询
-
查询所有部门人数超过3人的部门编号并按照人数降序排序
select deptno,count(*) as numb from tb_emp group by deptno having numb > 3 order by numb desc;
-
-
关联查询
-
查询所有员工的姓名、部门编号和部门名称
select a.ename, a.deptno, b.dname from tb_emp a join tb_dept b on a.deptno = b.deptno;
-
-
子查询
-
查询除SALES部门以外的所有部门的员工信息
select * from tb_emp where deptno not in (select deptno from tb_dept where dname = 'SALES');
-
- 子查询的另外一种写法
```
with t1 as (
select deptno from tb_dept where dname = 'SALES'
)
select * from t1 ;
```
-
小结
- 基本与MySQL中的语法一致
知识点14:表的分类:管理表
-
目标:掌握管理表的特点及应用场景
-
实施
-
语法
create table ……
-
-
特点
- 不手动删除,管理表一直存在
- 删除表时,元数据与HDFS映射的表的目录一起被删除
-
应用
- Hive中默认的表类型
-
小结
- 管理表的特点是什么?
- 不会自动删除
- 删除时候,既删除元数据也删除数据
- 管理表的特点是什么?
知识点15:表的分类:临时表
-
目标:了解临时表的特点及应用场景
-
实施
-
语法
create temporary table ……
-
特点
- 表是临时存在,如果客户端一旦断开,表会自动被删除,类似于ZK中的临时节点
- 是一种特殊的管理表,这种管理表的生命周期伴随客户端的
-
应用
- 用于存放临时数据
-
测试
create temporary table tb_emp_tmp( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t'; load data local inpath '/export/data/emp.txt' into table tb_emp_tmp;
-
-
小结
- 临时表的特点是什么?
- 如果客户端断开,客户端创建的临时表将自动删除
- 临时表的特点是什么?
知识点16:表的分类:外部表
-
目标:掌握外部表的特点及应用场景
-
实施
-
语法
create external table ……
-
特点
- 在删除表时,只删除元数据,数据是不会被删除
-
应用
- 工作中大部分的表都是外部表类型
-
测试
-
举例:现在有一份数据文件,要基于这个文件做两件事情
-
数据分析师:构建一张表,做数据分析
create external table tb_emp_ext1( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t' location '/user/hive/warehouse/db_emp.db/tb_emp';
-
算法工程师:构建一张表,做用户画像
create external table tb_emp_ext2( empno string, ename string, job string, managerid string, hiredate string, salary double, jiangjin double, deptno string ) row format delimited fields terminated by '\t' location '/user/hive/warehouse/db_emp.db/tb_emp';
-
算法工程师,用完了,将表删除
drop table tb_emp_ext2;
-
-
-
小结
- 外部表的特点是什么?
- 应用:用于保证数据安全,或者用于多张表共用一份数据,互不影响
- 特点:只删除元数据,不删除数据
- 外部表的特点是什么?
附录一:Hive Maven依赖
<properties>
<hadoop.version>2.7.5</hadoop.version>
<mysql.version>5.1.38</mysql.version>
<hive.version>2.1.0</hive.version>
</properties>
<dependencies>
<!--引入单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- Hadoop Client 依赖 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>${hadoop.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>${hadoop.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>${hadoop.version}</version>
</dependency>
<!-- MySQL Client 依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!-- Hive依赖 -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>${hive.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-common</artifactId>
<version>${hive.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-cli</artifactId>
<version>${hive.version}</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>${hive.version}</version>
</dependency>
</dependencies>