Hive

最新推荐文章于 2023-04-19 14:07:30 发布

lucasmaluping

最新推荐文章于 2023-04-19 14:07:30 发布

阅读量360

点赞数

分类专栏： hive

本文链接：https://blog.csdn.net/lucasmaluping/article/details/105408426

版权

hive 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.什么是hive
1.1.hive基本思想
Hive是基于Hadoop的一个数据仓库工具(离线)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL操作（CRUD）功能。
在这里插入图片描述
1.2.为什么使用Hive
直接使用hadoop所面临的问题
人员学习成本太高
项目周期要求太短
MapReduce实现复杂查询逻辑开发难度太大 Spark

为什么要使用Hive
操作接口采用类SQL语法，提供快速开发的能力。
避免了去写MapReduce，减少开发人员的学习成本。
功能扩展很方便。
1.3.Hive的特点
可扩展
Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。

延展性
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

容错
良好的容错性，节点出现问题SQL仍可完成执行。

2.hive的基本架构
在这里插入图片描述
Jobtracker是hadoop1.x中的组件，它的功能相当于：
Resourcemanager+MRAppMaster

TaskTracker 相当于：
Nodemanager + yarnchild

3.hive安装
3.1.最简安装：用内嵌derby作为元数据库
准备工作：安装hive的机器上应该有HADOOP环境（安装目录，HADOOP_HOME环境变量）
安装：直接解压一个hive安装包即可
此时，安装的这个hive实例使用其内嵌的derby数据库作为记录元数据的数据库
此模式不便于让团队成员之间共享协作
3.2.标准安装：将mysql作为元数据库
3.2.1.mysql安装
①　上传mysql安装包
②　解压：
[root@mylove ~]# tar -xvf MySQL-5.6.26-1.linux_glibc2.5.x86_64.rpm-bundle.tar

③　安装mysql的server包
[root@mylove ~]# rpm -ivh MySQL-server-5.6.26-1.linux_glibc2.5.x86_64.rpm
依赖报错：
缺perl
yum install perl
安装完perl后，继续重新安装mysql-server
（可以配置一个本地yum源进行安装：
1、先在vmware中给这台虚拟机连接一个光盘镜像
2、挂在光驱到一个指定目录：mount -t iso9660 -o loop /dev/cdrom /mnt/cdrom
3、将yum的配置文件中baseURL指向/mnt/cdrom
）

[root@mylove ~]# rpm -ivh MySQL-server-5.6.26-1.linux_glibc2.5.x86_64.rpm
又出错：包冲突conflict with
移除老版本的冲突包：mysql-libs-5.1.73-3.el6_5.x86_64
[root@mylove ~]# rpm -e mysql-libs-5.1.73-3.el6_5.x86_64 --nodeps

继续重新安装mysql-server
[root@mylove ~]# rpm -ivh MySQL-server-5.6.26-1.linux_glibc2.5.x86_64.rpm

成功后，注意提示：里面有初始密码及如何改密码的信息
初始密码：/root/.mysql_secret
改密码脚本：/usr/bin/mysql_secure_installation

④　安装mysql的客户端包：
[root@mylove ~]# rpm -ivh MySQL-client-5.6.26-1.linux_glibc2.5.x86_64.rpm

⑤　启动mysql的服务端：
[root@mylove ~]# service mysql start
Starting MySQL. SUCCESS!

⑥　修改root的初始密码：
[root@mylove ~]# /usr/bin/mysql_secure_installation 按提示

⑦　测试：
用mysql命令行客户端登陆mysql服务器看能否成功
[root@mylove ~]# mysql -uroot -proot
mysql> show databases;

⑧　给root用户授予从任何机器上登陆mysql服务器的权限：
mysql> grant all privileges on . to ‘root’@’%’ identified by ‘你的密码’ with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意点：要让mysql可以远程登录访问
最直接测试方法：从windows上用Navicat去连接，能连，则可以，不能连，则要去mysql的机器上用命令行客户端进行授权：
在mysql的机器上,启动命令行客户端：
mysql -uroot -proot
mysql>grant all privileges on . to ‘root’@’%’ identified by ‘root的密码’ with grant option;
mysql>flush privileges;

3.2.2.hive的元数据库配置
vi conf/hive-site.xml

javax.jdo.option.ConnectionURL
jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true
JDBC connect string for a JDBC metastore

javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver Driver class name for a JDBC metastore javax.jdo.option.ConnectionUserName root username to use against metastore database javax.jdo.option.ConnectionPassword root password to use against metastore database

2、上传一个mysql的驱动jar包到hive的安装目录的lib中

3、配置HADOOP_HOME 和HIVE_HOME到系统环境变量中：/etc/profile
4、source /etc/profile
5、hive启动测试
然后用命令启动hive交互界面：
[root@hdp20-04 ~]# hive

4.hive使用方式
4.1.最基本使用方式
启动一个hive交互shell
bin/hive
hive>

设置一些基本参数，让hive使用起来更便捷，比如：
1、让提示符显示当前库：
hive>set hive.cli.print.current.db=true;
2、显示查询结果时显示字段名称：
hive>set hive.cli.print.header=true;

但是这样设置只对当前会话有效，重启hive会话后就失效，解决办法：
在linux的当前用户目录中~，编辑一个.hiverc文件，将参数写入其中：
vi .hiverc
set hive.cli.print.header=true;
set hive.cli.print.current.db=true;

4.2.启动hive服务使用
1最简单运行：bin/hiveserver2
netstat -nltp 查看10000端口为当前服务端口

2启动hive的服务：静默运行
1)练习后台运行：
#!/bin/bash
while true
do
echo ‘i am lucas…’
sleep 1
done

chmod +x lucas.sh

./lucas.sh &
&代表后台运行
停不了
ps -ef | grep lucas.sh 看进程号 kill -9 … 杀死进程

标准输出重定向：
./lucas.sh 1>/root/lucas.log 2>/root/lucas.err &
1代表正常日志 2代表错误日志
tail -f lucas.log 查看正在输出的日志文件

jobs 查看正在运行的程序
fg 1 从后台转变成前台运行
ctrl + c 停止运行

./lucas.sh 1>/dev/null 2>&1 &
/dev/null 黑洞，数据进去就被删除了

nohup ./lucas.sh 1>/dev/null 2>&1 &
nohup 当前用户即便退出登录程序也会在后台运行

[root@hdp20-04 hive-1.2.1]# bin/hiveserver2 -hiveconf hive.root.logger=DEBUG,console

-hiveconf hive.root.logger=DEBUG,console
启动hive服务的同时，把debug信息往控制台上打印

上述启动，会将这个服务启动在前台，如果要启动在后台，则命令如下：
nohup bin/hiveserver2 1>/dev/null 2>&1 &

客户端链接hive服务器：
启动成功后，可以在别的节点上用beeline去连接
方式（1）
[root@hdp20-04 hive-1.2.1]# bin/beeline 回车，进入beeline的命令界面
输入命令连接hiveserver2
beeline> !connect jdbc:hive2://mini1:10000
（mini1是mini1所启动的那台主机名，端口默认是10000）
方式（2）
启动时直接连接：
bin/beeline -u jdbc:hive2://hdp-8:10000 -n root

接下来就可以做正常sql查询了

ps -ef | group proname查看程序的进程
4.3.脚本化运行
大量的hive查询任务，如果用交互式shell来进行输入的话，显然效率及其低下，因此，生产中更多的是使用脚本化运行机制：
该机制的核心点是：hive可以用一次性命令的方式来执行给定的hql语句

[root@hdp20-04 ~]# hive -e “insert into table t_dest select * from t_src;”

然后，进一步，可以将上述命令写入shell脚本中，以便于脚本化运行hive任务，并控制、调度众多hive任务，示例如下：
hive -s -e
vi t_order_etl.sh
#!/bin/bash
hive -e “select * from zpark.teacher”
hive -e “select * from school.student”
hql=“create table zpark.t_bash as select * from school.student”
hive -e “$hql”

#!/bin/bash
hive -e “create table school.stu_name_count(name string, number int)”
hive -e “insert into school.stu_name_count select name, count(1) from school.student group by name”

如果要执行的hql语句特别复杂，那么，可以把hql语句写入一个文件：
vi x.hql
select * from db_order.t_order;
select count(1) from db_order.t_user;

然后，用hive -f /root/x.hql 来执行
-f 执行一个文件

5.hive建库建表与数据导入
5.1.建库
hive中有一个默认的库：
库名： default
库目录：hdfs://hdp-1:9000/user/hive/warehouse

在分布式文件系统中看不到以default为名字的数据库
如果在default数据库中创建table，则出现在user/hive/warehouse下

新建库：
create database db_order;
库建好后，在hdfs中会生成一个库目录：
hdfs://hdp-1:9000/user/hive/warehouse/db_order.db

5.2.建表
5.2.1.基本建表语句
use school;
create table teacher(id string,name string,gender string);
表建好后，会在所属的库目录中生成一个表目录
/user/hive/warehouse/school/teacher
只是，这样建表的话，hive会认为表数据文件中的字段分隔符为 ^A

正确的建表语句为：
create table classonedb.teacher(id string,name string,gender string)
row format delimited
fields terminated by ‘,’;

这样就指定了，我们的表数据文件中的字段分隔符为 “,”

vi testdata.dat
1,lucas,male
2,nimoo,female
3,jack,male

hadoop fs -put testdata.dat /user/hive/warehouse/school.db/teacher
select * from teacher 会查询到所有的数据
5.2.2.删除表
drop table t_order;
删除表的效果是：
hive会从元数据库中清除关于这个表的信息；
hive还会从hdfs中删除这个表的表目录；

5.2.3.内部表与外部表
内部表(MANAGED_TABLE)：表目录按照hive的规范来部署，位于hive的仓库目录/user/hive/warehouse中

外部表(EXTERNAL_TABLE)：表目录由建表用户自己指定

vi pv.log
1,lucas,male
2,nimoo,female
3,jack,male
:wq

在hdfs上创建目录
hadoop fs -mkdir -p /pvlog/pv/
将文件上传
hadoop fs -put pv.log /pvlog/pv/
后台启动hive服务
hiveserver2 1>info.log 2>&1 &

create external table t_access(ip string,url string,access_time string)
row format delimited
fields terminated by ‘,’
location ‘/access/log’;

create external table t_pvlog(id string, name string, gender string) row format delimited fields terminated by ‘,’ location ‘/pvlog/pv’;

外部表和内部表的特性差别：
1、内部表的目录在hive的仓库目录中 VS 外部表的目录由用户指定
2、drop一个内部表时：hive会清除相关元数据，并删除表数据目录
3、drop一个外部表时：hive只会清除相关元数据；

一个hive的数据仓库，最底层的表，一定是来自于外部系统，为了不影响外部系统的工作逻辑，在hive中可建external表来映射这些外部系统产生的数据目录；
然后，后续的etl操作，产生的各种表建议用managed_table

5.2.4.分区表
分区表的实质是：在表目录中为数据文件创建分区子目录，以便于在查询时，MR程序可以针对分区子目录中的数据进行处理，缩减读取数据的范围。

比如，网站每天产生的浏览记录，浏览记录应该建一个表来存放，但是，有时候，我们可能只需要对某一天的浏览记录进行分析
这时，就可以将这个表建为分区表，每天的数据导入其中的一个分区；
当然，每日的分区目录，应该有一个目录名（分区字段）

一个分区字段的实例：
vi personv.log.28

192.168.33.2,http://www.sina.com/a, 2019-05-30 20:08:24
192.168.33.3,http://www.sina.com/b, 2019-05-30 21:08:24
192.168.33.4,http://www.sina.com/a, 2019-05-30 22:08:24
192.168.33.5,http://www.sina.com/c, 2019-05-30 23:08:24
192.168.33.2,http://www.sina.com/a, 2019-05-30 24:08:24
192.168.33.7,http://www.sina.com/b, 2019-05-30 20:09:24
192.168.33.2,http://www.sina.com/a, 2019-05-30 20:12:24

cp personv.log personv.log.27
:%s/28/27/g 替换所有的28成27

进到hive中：
load data local inpath ‘/root/hivetest/personv.log.27’ into table t_pv_log partition(day=‘20190527’);
注意：此处的local代表的是hive服务所在的机器

select * from t_pv_log

load data local inpath ‘/root/hivetest/personv.log.28’ into table t_pv_log partition(day=‘20190528’);

select * from t_pv_log

查询http://www.baidu.com/a 这个页面在28号这一天总共被访问的次数
select count(1) from t_pv_log where url=‘http://www.sina.com/a’ and day=‘20190528’;

示例如下：
1、创建带分区的表
create table zpark.t_access(ip string,url string,access_time string)
partitioned by(dt string)
row format delimited
fields terminated by ‘,’;
注意：分区字段不能是表定义中的已存在字段

2、向分区中导入数据
load data local inpath ‘/root/data/personv.log.28’ into table zpark.t_access partition(dt=‘20190528’);
load data local inpath ‘/root/data/personv.log.29’ into table zpark.t_access partition(dt=‘20190529’);

3、针对分区数据进行查询
a、统计5月28号的总PV：
select count(*) from t_access where dt=‘20190528’;
实质：就是将分区字段当成表字段来用，就可以使用where子句指定分区了

b、统计表中所有数据总的PV：
select count(*) from t_access;
实质：不指定分区条件即可

多个分区字段示例
建表：
create table t_partition(id int,name string,age int)
partitioned by(department string,sex string,howold int)
row format delimited fields terminated by ‘,’;

导数据：
load data local inpath ‘/root/p1.dat’ into table t_partition partition(department=‘IT’,sex=‘male’,howold=20);

5.2.5.CTAS建表语法
可以通过已存在表来建表：
1、create table t_user_2 like t_user;
新建的t_user_2表结构定义与源表t_user一致，但是没有数据
desc查看一下

2、在建表的同时插入数据
create table info2
as
select count(*) from info;
info2会根据select查询的字段来建表，同时将查询的结果插入新表中

5.3.数据导入导出
5.3.1.将数据文件导入hive的表
方式1：导入数据的一种方式：
手动用hdfs命令，将文件放入表目录；
hadoop fs -put 本地文件的路径 /…

方式2：在hive的交互式shell中用hive命令来导入本地数据到表目录
hive>load data local inpath ‘/root/order.data.2’ into table t_order;

方式3：用hive命令导入hdfs中的数据文件到表目录
hive>load data inpath ‘/access.log.2017-08-06.log’ into table t_access partition(dt=‘20170806’);

注意：导本地文件和导HDFS文件的区别：
本地文件导入表：复制
hdfs文件导入表：移动

5.3.2.将hive表中的数据导出到指定路径的文件
1、将hive表中的数据导入HDFS的文件
insert overwrite directory ‘/root/access-data’
row format delimited fields terminated by ‘,’
select * from t_access;

2、将hive表中的数据导入本地磁盘文件
insert overwrite local directory ‘/root/access-data’
row format delimited fields terminated by ‘,’
select * from t_access limit 100000;

5.3.3.hive文件格式
HIVE支持很多种文件格式： SEQUENCE FILE | TEXT FILE | PARQUET FILE | RC FILE
create table t_pq(movie string,rate int) stored as textfile;
create table t_pq(movie string,rate int) stored as sequencefile;
create table t_pq(movie string,rate int) stored as parquetfile;

演示：
1、先建一个存储文本文件的表
create table t_access_text(ip string,url string,access_time string)
row format delimited fields terminated by ‘,’
stored as textfile;

导入文本数据到表中：
load data local inpath ‘/root/access-data/000000_0’ into table t_access_text;

2、建一个存储sequence file文件的表：
create table t_access_seq(ip string,url string,access_time string)
stored as sequencefile;

从文本表中查询数据插入sequencefile表中，生成数据文件就是sequencefile格式的了：
insert into t_access_seq
select * from t_access_text;

3、建一个存储parquet file文件的表：
create table t_access_parq(ip string,url string,access_time string)
stored as parquetfile;

5.4.数据类型
5.4.1.数字类型
java:
boolean char byte short int long float double
hive:

TINYINT (1-byte signed integer, from -128 to 127)
SMALLINT (2-byte signed integer, from -32,768 to 32,767)

INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)

BIGINT (8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
FLOAT (4-byte single precision floating point number)
DOUBLE (8-byte double precision floating point number)

示例：
create table t_test(a string ,b int,c bigint,d float,e double,f tinyint,g smallint)

5.4.2.日期时间类型
TIMESTAMP (Note: Only available starting with Hive 0.8.0)
DATE (Note: Only available starting with Hive 0.12.0)

示例，假如有以下数据文件：
1,zhangsan,1985-06-30
2,lisi,1986-07-10
3,wangwu,1985-08-09
那么，就可以建一个表来对数据进行映射
create table t_customer(id int,name string,birthday date)
row format delimited fields terminated by ‘,’;
然后导入数据
load data local inpath ‘/root/customer.dat’ into table t_customer;
然后，就可以正确查询

5.4.3.字符串类型
STRING
VARCHAR (Note: Only available starting with Hive 0.12.0)
CHAR (Note: Only available starting with Hive 0.13.0)

5.4.4.混杂类型
BOOLEAN
BINARY (Note: Only available starting with Hive 0.8.0)

5.4.5.复合类型
array数组类型
arrays: ARRAY<data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)

示例：array类型的应用
假如有如下数据需要用hive的表去映射：
战狼2,吴京:吴刚:龙母,2017-08-16
三生三世十里桃花,刘亦菲:痒痒,2017-08-20
设想：如果主演信息用一个数组来映射比较方便

建表：
create table movie(name string,actors array,show date)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

导入数据：
load data local inpath ‘/root/movie.dat’ into table t_movie;

查询：
select * from t_movie;
select name,actors[0] from t_movie;
select name,actors from movie where array_contains(actors,‘吴刚’); --包含吴刚
select name,size(actors) as actor_num from movie;
map类型
maps: MAP<primitive_type, data_type> (Note: negative values and non-constant expressions are allowed as of Hive 0.14.)

1)假如有以下数据：
1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28
2,lisi,father:mayun#mother:huangyi#brother:guanyu,22
3,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29
4,mayun,father:mayongzhen#mother:angelababy,26

可以用一个map类型来对上述数据中的家庭成员进行描述
2)建表语句：
create table family(id int,name string,relation map<string,string>,age int)
row format delimited fields terminated by ‘,’
collection items terminated by ‘#’
map keys terminated by ‘:’;

load data…

3)查询
select * from t_person;

取map字段的指定key的值

select id,name,relation [‘father’] as father from t_person;

取map字段的所有key

select id,name,map_keys(relation) as relation from t_person;

取map字段的所有value

select id,name,map_values(relation) from t_person;
select id,name,map_values(relation)[0] from t_person;

综合：查询有brother的用户信息

select id,name,brother
from
(select id,name,relation[‘brother’] as brother from family) tmp
where brother is not null;

struct类型
structs: STRUCT<col_name : data_type, …>

1)假如有如下数据：
1,zhangsan,18:male:beijing
2,lisi,28:female:shanghai

其中的用户信息包含：年龄：整数，性别：字符串，地址：字符串
设想用一个字段来描述整个用户信息，可以采用struct

2)建表：
create table zpark.t_info(id int,name string,info structage:int,gender:string,addr:string)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

3)查询
select * from person;
select id,name,info.age from person;

5.5.修改表定义
仅修改Hive元数据，不会触动表中的数据，用户需要确定实际的数据布局符合元数据的定义。

修改表名：
ALTER TABLE table_name RENAME TO new_table_name

示例：alter table t_1 rename to t_x;

修改分区表中分区字段的值：
alter table t_partition partition(department=‘xiangsheng’,sex=‘male’,howold=20) rename to partition(department=‘1’,sex=‘1’,howold=20);

添加分区：
alter table t_partition add partition (department=‘2’,sex=‘0’,howold=40);

删除分区表中分区字段的数据：
alter table t_partition drop partition (department=‘2’,sex=‘2’,howold=24);

修改表的文件格式定义：
ALTER TABLE table_name [PARTITION partitionSpec] SET FILEFORMAT file_format

alter table t_partition partition(department=‘2’,sex=‘0’,howold=40 ) set fileformat sequencefile;

修改列名定义：
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENTcol_comment] [FIRST|(AFTER column_name)]

alter table t_user change price jiage float first;

增加/替换列：
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type[COMMENT col_comment], …)

alter table t_user add columns (sex string,addr string);
alter table t_user replace columns (id string,age int,price float);
替换列会用（）中的列替换原来的所有列（替换时候要小心）
6.hive查询语法
create table t_a(name string, numb int)
row format delimited
fields terminated by ‘,’;

create table t_b(name string, nick string)
row format delimited
fields terminated by ‘,’;

load data local inpath ‘/root/data/a.txt’ into table t_a;
load data local inpath ‘/root/data/b.txt’ into table t_b;

—各类join
—1.内连接：
—笛卡尔积
select a., b.
from t_a a join t_b b;

select t_a., t_b. from t_a join t_b;
±--------±--------±--------±--------±-+
| a.name | a.numb | b.name | b.nick |
±--------±--------±--------±--------±-+
| a | 1 | a | xx |
| b | 2 | a | xx |
| c | 3 | a | xx |
| d | 4 | a | xx |
| a | 1 | b | yy |
| b | 2 | b | yy |
| c | 3 | b | yy |
| d | 4 | b | yy |
| a | 1 | d | zz |
| b | 2 | d | zz |
| c | 3 | d | zz |
| d | 4 | d | zz |
| a | 1 | e | pp |
| b | 2 | e | pp |
| c | 3 | e | pp |
| d | 4 | e | pp |
±--------±--------±--------±--------±-+
—指定join条件
select a., b.
from t_a a join t_b b on a.name = b.name;

±--------±--------±--------±--------±-+
| a.name | a.numb | b.name | b.nick |
±--------±--------±--------±--------±-+
| a | 1 | a | xx |
| b | 2 | b | yy |
| d | 4 | d | zz |
±--------±--------±--------±--------±-+

—2.左外链接（左连接）
select a.,b.
from t_a a left outer join t_b b;
±--------±--------±--------±--------±-+
| a.name | a.numb | b.name | b.nick |
±--------±--------±--------±--------±-+
| a | 1 | a | xx |
| a | 1 | b | yy |
| a | 1 | d | zz |
| a | 1 | e | pp |
| b | 2 | a | xx |
| b | 2 | b | yy |
| b | 2 | d | zz |
| b | 2 | e | pp |
| c | 3 | a | xx |
| c | 3 | b | yy |
| c | 3 | d | zz |
| c | 3 | e | pp |
| d | 4 | a | xx |
| d | 4 | b | yy |
| d | 4 | d | zz |
| d | 4 | e | pp |
±--------±--------±--------±--------±-+
select a.,b.
from t_a a left outer join t_b b on a.name=b.name;

—3.右外链接（右连接）
select a.,b.
from t_a a right outer join t_b b;

select a.,b.
from t_a a right outer join t_b b on a.name=b.name;

—4.全外链接（全连接）
不加on的条件就是把inner join的结果反转
select a.,b.
from t_a a full outer join t_b b on a.name=b.name;
±--------±--------±--------±--------±-+
| a.name | a.numb | b.name | b.nick |
±--------±--------±--------±--------±-+
| a | 1 | a | xx |
| b | 2 | b | yy |
| c | 3 | NULL | NULL |
| d | 4 | d | zz |
| NULL | NULL | e | pp |
±--------±--------±--------±--------±-+
—5.左半链接
select a.,b.
from t_a a left semi join t_b b on a.name=b.name;
只在hive中有，适用于即在a中有，也在b中有。

提示：在做小数据量查询测试时，可以让hive将mrjob提交给本地运行器运行，可以在hive会话中设置如下参数：
hive> set hive.exec.mode.local.auto=true;
6.1.基本查询示例
select * from t_access;
select count(*) from t_access;
select max(ip) from t_access;
6.2.条件查询
select * from t_access where access_time<‘2017-08-06 15:30:20’
select * from t_access where access_time<‘2017-08-06 16:30:20’ and ip>‘192.168.33.3’;
6.3.join关联查询示例
假如有a.txt文件
a,1
b,2
c,3
d,4

假如有b.txt文件
a,xx
b,yy
d,zz
e,pp

进行各种join查询：
1、inner join（join）
select
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
join t_b b
on a.name=b.name

结果：
±-------±-------±-------±-------±-+
| aname | anumb | bname | bnick |
±-------±-------±-------±-------±-+
| a | 1 | a | xx |
| b | 2 | b | yy |
| d | 4 | d | zz |
±-------±-------±-------±-------±-+

2、left outer join（left join）
select
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
left outer join t_b b
on a.name=b.name

结果：

3、right outer join（right join）
select
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
right outer join t_b b
on a.name=b.name

结果：

4、full outer join（full join）
select
a.name as aname,
a.numb as anumb,
b.name as bname,
b.nick as bnick
from t_a a
full join t_b b
on a.name=b.name;

结果：

6.4.left semi join
hive中不支持exist/IN子查询，可以用left semi join来实现同样的效果：
select
a.name as aname,
a.numb as anumb
from t_a a
left semi join t_b b
on a.name=b.name;

结果：

注意： left semi join的 select子句中，不能有右表的字段
6.5.group by分组聚合
有group by 那个group by 会在select、update、alter等等之前运行

select dt,count(*),max(ip) as cnt from t_access group by dt;

select dt,count(*),max(ip) as cnt from t_access group by dt having dt>‘20170804’;
先groupby 再having，再select

select
dt,count(*),max(ip) as cnt
from t_access
where url=‘http://www.sina.com/b’
group by dt having dt>‘20190804’;

注意：一旦有group by子句，那么，在select子句中就不能有（分组字段，聚合函数）以外的字段

为什么where必须写在group by的前面，为什么group by后面的条件只能用having

因为，where是用于在真正执行查询逻辑之前过滤数据用的
having是对group by聚合之后的结果进行再过滤；

上述语句的执行逻辑：
1、where过滤不满足条件的数据
2、用聚合函数和group by进行数据运算聚合，得到聚合结果
3、用having条件过滤掉聚合结果中不满足条件的数据

6.6.子查询
select id,name,father
from
(select id,name,family_members[‘brother’] as father from t_person) tmp
where father is not null;

7.hive函数使用
小技巧：测试函数的用法，可以专门准备一个专门的dual表
create table dual(x string);
insert into table dual values(’’);

其实：直接用常量来测试函数即可
select substr(“abcdefg”,1,3);//数据库中的角标从1开始

hive的所有函数手册：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inTable-GeneratingFunctions(UDTF)
7.1.常用内置函数
7.1.1.类型转换函数
select cast(“5” as int) from dual;
create table t_test
as
select cast(“6” as int);

select cast(“2017-08-03” as date) ;

select current_timestamp --hive中的时间戳
select unix_timestamp —unix的时间戳 lang类型
select cast(current_timestamp as date);

示例：
1 1995-05-05 13:30:59 1200.3
2 1994-04-05 13:30:59 2200
3 1996-06-01 12:20:30 80000.5
create table t_fun(id string,birthday string,salary string)
row format delimited fields terminated by ‘,’;

select id,cast(birthday as date) as bir,cast(salary as float) from t_fun;

7.1.2.数学运算+函数
select round(5.4) from dual; ## 5 四舍五入
select round(5.1345,3) from dual; ##5.135 保留3位小数
select ceil(5.4) from dual; // select ceiling(5.4) from dual; ## 6
ceil天花板（向上取整） floor地板（向下取整）
select floor(5.4) from dual; ## 5
select abs(-5.4) from dual; ## 5.4
select greatest(3,5,6) from dual; ## 6 最大值（1个字段报错）
select least(3,5,6) from dual;

示例：
有表如下：

select greatest(cast(s1 as double),cast(s2 as double),cast(s3 as double)) from t_fun2;
结果：
±--------±-+
| _c0 |
±--------±-+
| 2000.0 |
| 9800.0 |
±--------±-+

select max(age) from t_person; 聚合函数
select min(age) from t_person; 聚合函数

7.1.3.字符串函数
substr(string, int start) ## 截取子串
substring(string, int start)
示例：select substr(“abcdefg”,2) from dual;

substr(string, int start, int len)
substring(string, int start, int len)
示例：select substr(“abcdefg”,2,3) from dual;

concat(string A, string B…) ## 拼接字符串
concat_ws(string SEP, string A, string B…) --sep：分隔符
示例：select concat(“ab”,“xy”) from dual;
select concat_ws(".",“192”,“168”,“33”,“44”) from dual;

length(string A)
示例：select length(“192.168.33.44”) from dual;

split(string str, string pat)
示例：select split(“192.168.33.44”,".") from dual; 错误的，因为.号是正则语法中的特定字符
select split(“192.168.33.44”,"\.") from dual;

upper(string str) ##转大写

7.1.4.时间函数
select current_timestamp;
select current_date;

取当前时间的毫秒数时间戳

select unix_timestamp();

unix时间戳转字符串

from_unixtime(bigint unixtime[, string format])
示例：select from_unixtime(unix_timestamp());
select from_unixtime(unix_timestamp(),“yyyy/MM/dd HH:mm:ss”);

字符串转unix时间戳

unix_timestamp(string date, string pattern)
示例： select unix_timestamp(“2017-08-10 17:50:30”);
select unix_timestamp(“2017/08/10 17:50:30”,“yyyy/MM/dd HH:mm:ss”);

将字符串转成日期date

select to_date(“2017-09-17 16:58:32”);

7.1.5.表生成函数
行转列函数：explode()
假如有以下数据：
1,zhangsan,huaxue:wuli:shuxue:yuwen
2,lisi,huaxue:wuli:shuxue:yuwen
3,wangwu,huaxue:wuli:shuxue:yuwen
映射成一张表：
create table subject(id int,name string,subjects array)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

使用explode()对数组字段“炸裂”

然后，我们利用这个explode的结果，来求去重的课程：
select distinct tmp.sub
from
(select explode(subjects) as sub from subject) tmp;

表生成函数lateral view
select id,name,tmp.sub
from subject lateral view explode(subjects) tmp as sub;

理解： lateral view 相当于两个表在join
左表：是原表
右表：是explode(某个集合字段)之后产生的表
而且：这个join只在同一行的数据间进行

那样，可以方便做更多的查询：
比如，查询选修了生物课的同学
select a.id,a.name,a.sub from
(select id,name,tmp.sub as sub from t_stu_subject lateral view explode(subjects) tmp as sub) a
where sub=‘生物’;

7.1.6.集合函数
array_contains(Array, value) 返回boolean值

示例：
select moive_name,array_contains(actors,‘吴刚’) from t_movie;
select array_contains(array(‘a’,‘b’,‘c’),‘c’) from dual;

sort_array(Array) 返回排序后的数组

示例：
select sort_array(array(‘c’,‘b’,‘a’)) from dual;
select ‘haha’,sort_array(array(‘c’,‘b’,‘a’)) as xx from (select 0) tmp;

size(Array) 返回一个int值

示例：
select moive_name,size(actors) as actor_number from t_movie;

size(Map<K.V>) 返回一个int值
map_keys(Map<K.V>) 返回一个数组
map_values(Map<K.V>) 返回一个数组

7.1.7.条件控制函数
swich()
case 1:

break
case 2:

break;
case when
语法：
CASE [ expression ]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
WHEN conditionn THEN resultn
ELSE result
END

vi person
1,zhangsan,18:male:beijing
2,lisi,28:female:shanghai
–建表：
create table person(id int,name string,info structage:int,sex:string,addr:string)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;
示例：
select id,name,
case
when info.age<20 then ‘youngth’
when info.age>20 and info.age<30 then ‘zhongnian’
else ‘old’
end
from animal;

IF
select id,name,if(info.age>25,‘worker’,‘student’) from animal; 三目运算

select name,if(array_contains(actors,‘吴刚’),‘好电影’,‘坏电影’) from movie;

7.1.8.json解析函数：表生成函数
json_tuple函数
示例：
select json_tuple(json,‘movie’,‘rate’,‘timeStamp’,‘uid’) as(movie,rate,ts,uid) from t_ratingjson;
产生结果：

利用json_tuple从原始json数据表中，etl出一个详细信息表：
create table t_rate
as
select
uid,
movie,
rate,
year(from_unixtime(cast(ts as bigint))) as year,
month(from_unixtime(cast(ts as bigint))) as month,
day(from_unixtime(cast(ts as bigint))) as day,
hour(from_unixtime(cast(ts as bigint))) as hour,
minute(from_unixtime(cast(ts as bigint))) as minute,
from_unixtime(cast(ts as bigint)) as ts
from
(select
json_tuple(rateinfo,‘movie’,‘rate’,‘timeStamp’,‘uid’) as(movie,rate,ts,uid)
from t_json) tmp
;

7.1.9.分析函数：row_number() over()——分组TOPN
需求
有如下数据：
1,18,a,male
2,19,b,male
3,22,c,female
4,16,d,female
5,30,e,male
6,26,f,female

需要查询出每种性别中年龄最大的2条数据

实现：
ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。
ROW_NUMBER()
说明：返回结果集分区内行的序列号，每个分区的第一行从1开始。
语法：ROW_NUMBER () OVER ([ <partition_by_clause> ] <order_by_clause>) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。

order by 根据哪个字段排序
partiton by 分区

使用row_number函数，对表中的数据按照性别分组，按照年龄倒序排序并进行编号标记

hql代码：
select id,age,name,gender,
row_number() over(partition by gender order by age desc) as rank
from person

产生结果：

8.一、窗口函数基本概念
Mysql8.0支持窗口函数，也称为分析函数，窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果
使用方法：配合聚合窗口函数一起使用，例如SUM/AVG/COUNT/MAX/MIN等等
关键字：over partition by
8.1.1.2、演示
例如我们现在有这样一张数据表

原始数据表
我现在想求每个国家profit的总和，常用的做法是使用聚合函数sum

sum
但是问题来了，我现在想求sum的同时，展示出其他字段的信息比如year，product等，如果是正常的sql需要进行一些额外的操作，但是有了窗口函数一切就变得不一样了，如下图，飒~

窗口函数
这里的关键字是over，partition by 是按照某个字段分区的意思，可以理解为分组 group by
partition by如果不写代表使用整个数据集分区，相当于没有group by
附加：1. 上面的场景partition by不写，结果应该是什么？
2. 其他聚合函数同理，这里就列举了。

9.二、专用窗口函数
9.1.1.1、首先简单列举一些常用的专用窗口函数
获取数据排名的：ROW_NUMBER() RAND() DEBSE_RANK() PERCENT_RANK()
获取分组内的第一名或者最后一名等 FIRST_VALUE() LAST_VALUE() LEAD() LAG()
累计分布：CUME_DIST() NTH_VALUE() NTILE()
9.1.2.2、演示

数据列表
现在我们想求出val的排名，如下图

row_number
这个函数有什么用处呢？现在我来说一个经典的面试场景，求所有学生中每门功课成绩的前三名，那这个时候我们就可以是使用窗口函数来解决，sql如下(脑补表结构)
select * from
(select row_number() over (partition by lesson order by score desc) 排名,* from sc) t
where 排名<=3
在上述sql中我们使用lesson分组，并且使用分数降序，这是每个学生会有一个排名，然后小于等于三即可。
附：当我们后续做最近最基本的数据分析这些窗口函数的时候会有很大的用处。
场景：淘宝新上线了100个商品，需要推给某用户群体，每个用户推荐最符合的10个商品，匹配规则是：商品有标签，用户对商品有对应的动作（购买，购物车，浏览，收藏），
结果是，每个用户对每个商品能求出一个分数，然后按照用户分组，每个商品取前十个的用户

然后，利用上面的结果，查询出rank<=2的即为最终需求
select id,age,name,gender
from
(select id,age,name,gender,
row_number() over(partition by gender order by age desc) as rank
from person) tmp
where tmp.rank<=2;

练习：求出电影评分数据中，每个用户评分最高的topn条数据

9.2.自定义函数
9.2.1.需求：
需要对json数据表中的json数据写一个自定义函数，用于传入一个json，返回一个数据值的数组

json原始数据表：3，7

需要做ETL操作，将json数据变成普通表数据，插入另一个表中：

9.2.2.实现步骤：
1、开发JAVA的UDF类
public class ParseJson extends UDF{
// 重载：有相同的方法名，不同的参数类型或个数
// 重写：有相同的方法名，形同的参数类型或个数
// 重载：返回值类型和参数类型及个数，完全由用户自己决定
// 本处需求是：给一个字符串，返回一个数组
public String[] evaluate(String json) {

	String[] split = json.split("\"");
	String[] res = new String[]{split[3],split[7],split[11],split[15]};
	return res;

}

}

2、打jar包

在eclipse中使用export即可

3、上传jar包到运行hive所在的linux机器

4、在hive中创建临时函数：
在hive的提示符中：classpath
hive> add jar /root/hivetest/classonejson.jar;
然后，在hive的提示符中，创建一个临时函数：
hive>CREATE TEMPORARY FUNCTION myjson AS ‘com.zpark.json.JsonTest’;

5、开发hql语句，利用自定义函数，从原始表中抽取数据插入新表

注：临时函数只在一次hive会话中有效，重启会话后就无效

如果需要经常使用该自定义函数，可以考虑创建永久函数：
拷贝jar包到hive的类路径中：
cp wc.jar apps/hive-1.2.1/lib/
创建了：
create function pfuncx as ‘com.doit.hive.udf.UserInfoParser’;

删除函数：
DROP TEMPORARY FUNCTION [IF EXISTS] function_name
DROP FUNCTION[IF EXISTS] function_name

综合查询案例
10.1.用hql来做wordcount
有以下文本文件：
hello tom hello jim
hello rose hello tom
tom love rose rose love jim
jim love tom love is what
what is love

需要用hive做wordcount
– 建表映射
create table t_wc(sentence string);

– 导入数据
load data local inpath ‘/root/hivetest/xx.txt’ into table t_wc;
select split(sentence, ‘ ‘) from t_wc;

hql答案：
SELECT word
,count(1) as cnts
FROM (
SELECT explode(split(sentence, ’ ')) AS word
FROM t_wc
) tmp
GROUP BY word
order by cnts desc
;
10.2.级联报表查询
有如下数据：

A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
C,2015-01,10
C,2015-01,20
A,2015-02,4
A,2015-02,6
C,2015-02,30
C,2015-02,10
B,2015-02,10
B,2015-02,5
A,2015-03,14
A,2015-03,6
B,2015-03,20
B,2015-03,25
C,2015-03,10
C,2015-03,20

建表映射：
create table t_access_times(username string,month string,counts int)
row format delimited fields terminated by ‘,’;

需要要开发hql脚本，来统计出如下累计报表：
用户月份月总额累计到当月的总额
A 2015-01 33 33
A 2015-02 10 43
A 2015-03 30 73
B 2015-01 30 30
B 2015-02 15 45

答案：

create table t_access_times(username string,month string,counts int)
row format delimited fields terminated by ‘,’;

load data local inpath ‘/root/hivetest/accumulate.dat’ into table t_access_times;

1、第一步，先求个用户的月总金额
select username,month,sum(counts) as amount from t_access_times group by username,month;

create table t_access_amount as select username,month,sum(counts) as amount from t_access_times group by username,month;

±----------±---------±-----±-+
| username | month | salary |
±----------±---------±-----±-+
| A | 2015-01 | 33 |
| A | 2015-02 | 10 |
| A | 2015-03 | 20 |
| B | 2015-01 | 30 |
| B | 2015-02 | 15 |
| B | 2015-03 | 45 |
| C | 2015-01 | 30 |
| C | 2015-02 | 40 |
| C | 2015-03 | 30 |
±----------±---------±-----±-+
2、第二步，将月总金额表自己连接自己连接
select A.,B. FROM
(select username,month,sum(counts) from t_access_times group by username,month) A
inner join
(select username,month,sum(counts) from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month;
±------------±---------±----------±------------±---------±----------±-+
| a.username | a.month | a.salary | b.username | b.month | b.salary |
±------------±---------±----------±------------±---------±----------±-+
| A | 2015-01 | 33 | A | 2015-01 | 33 |
| A | 2015-01 | 33 | A | 2015-02 | 10 |
| A | 2015-02 | 10 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-02 | 10 |
| B | 2015-01 | 30 | B | 2015-01 | 30 |
| B | 2015-01 | 30 | B | 2015-02 | 15 |
| B | 2015-02 | 15 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-02 | 15 |
±------------±---------±----------±------------±---------±----------±-+
create table tmp

第3步：
select auname,amonth,acnts,sum(bcnts)
from tmp
group by auname,amonth,acnts;
得到最终结果

当然，也可以把整个逻辑过程写成一个SQL语句：

select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from
(select username,month,sum(salary) as salary from t_access_times group by username,month) A
inner join
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;

–窗口分析函数
结合row_number() over()窗口分析函数讲解，都是窗口分析函数
select username,month,amount,
sum(amount) over(partition by username order by month rows between unbounded preceding and current row) as accumulate
from t_access_amount;